会員名簿や商品のデータベースなどで、同じデータが重複していたことはありませんか?

重複したデータを目で見て探し出すのは大変な作業です。

そんなときに便利なのは、エクセルの「重複の削除」という機能です。

基準項目をチェックするだけで、重なったデータをすばやく削除してくれます。

重複したデータを削除する方法

下の名簿の中の重複したデータを削除しましょう

img20151029_01

 

1.表の項目をひとつ選択し、[データ]タグ→「重複の削除」ボタンクリックします。

 

img20151029_02

↓ ↓ ↓

img20151029_02-2

 

2.「重複の削除」ウインドウが開くので、表示されている項目の「No.」のチェックをはずして「OK」ボタンをクリックします。

(「氏名」「年齢」「性別」「生年月日」が一致すると重複してるとみなされます)

img20151029_03

 

3.重複するデータの数残ったデータの数が表示されるので、確認をしたら「OK」をクリックします。

img20151029_04

 

3つのデータが削除されました。

img20151029_09

 

<削除されたデータを知りたい場合>

データが削除されていると、項目「No.」の上のセルとの数字の差が「2」以上になるので、「下のセル-上のセル」という数式で数字の差をもとめてみます。

 

1.セルF3に「=A3-A2」と半角入力し、「Enter」キーを押します。

img20151029_05

 

「1」と表示されました。

img20151029_06

 

2.F3セルの右下のフィルハンドルをダブルクリックして、下のセルに数式をコピーします。

img20151029_07

 

数字の差が「2」と「3」が、データが削除された箇所です。
消えているNo.をみてみると、No.16、No.24、No.25が削除されたデータとなります。

img20151029_08

 

 

前回の続きで、「2日」以降のセルに日付を表示させて予定表を完成させます。

前回→「年月を入力するだけで自動的に日付が表示される方法1(DATE関数)

方法は簡単で、「2日」のセルに簡単な数式を入れ、あとは残りのセルにコピー&ペーストするだけです。

年月を入力するだけで自動的に日付が表示される方法2

1.「11月1日」の下のセル(B6)を選択し、「=B5+1」と直接入力して[Enter]キーを押します。(「B5」の箇所は直接B5セルをクリックしてもOK)

img20151025_01

 

「11月1日」に1日をが足されて「11月2日」になりました。

img20151023_12

 

では、下のセルに数式をコピーしましょう。

3.「11月2日」のセル(B6)を選択し、セル右下のフィルハンドル(+)を下までドラッグします。

img20151023_13

 

下まで日付が表示されました。

img20151025_04

 

4.12月の予定表を作るときには、月の欄(セルF2)に12」と半角入力します。
また、翌年の1月のときは、年の欄(セルD2)に2016」、月の欄(セルF2)に1」と半角入力します。

img20151025_05

 

その場合、月末の欄が空白になるので、すぐ上のセルのフィルハンドルをドラッグして、数式をコピーしましょう。

img20151025_06

 

 

以前、同じ条件のデータを合計する方法(SUMIF関数)でカレンダーの商品別売上を求める方法をご紹介しました。

ではもし商品別でなく、カレンダーA4版、B4版、A3版と「大きさ別」で集計したいときは、どのようにすればよいでしょうか?

実はこのようなあいまいな条件を指定するときに、便利な記号があるのです。

その名も「ワイルドカード

今回はそのワイルドカード*」(半角アスタリスク) を使って、大きさ別の売上額をもとめてみましょう。

【ワイルドカードの使い方】
 「東*」…”東”で始まる文字列(例:北、方、海道など)
 「*東」…”東”で終わる文字列(例:北、関、南南など)
 「*東*」…”東”を含む文字列(”東”を含む文字ずべて)

 

あいまいな文字を検索条件にする方法(ワイルドカード)

B列の売上商品から、ワイルドカードを使って大きさ別の売上額を集計してみましょう。

「カレンダー○○版」という文字で始まる商品名を検索の条件として使います。

img20151008_01

 

1.売上額を表示したいセルを選択し、関数の挿入ボタン「fx」をクリックします。

img20151008_02

img20151001_02_1

 

2.関数の挿入ウインドウが開くので、[関数の分類(C)]から「数学/三角」を選び、[関数名(N)]から「SUMIF」を選び「OK」ボタンをクリックします。

img20151008_03

もしくは、関数の挿入ウインドウが開いたときに表示されている[関数の分類(C)]の「最近使った関数」で、[関数名(N)]の中に使いたい関数があればそれを選んでもOKです。

img20151008_03-2

 

3.「範囲」欄をクリックし、セルB2からB23を範囲選択します。

img20151008_04

img20151008_04-2

 

4.「範囲」欄に「B2:B23」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を1回押して絶対参照にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

img20151008_04-3

 

5.「検索条件欄をクリックし、「“カレンダーA4版*”」と半角入力します。(「*」は半角アスタリスク)

img20151008_05

 

6.次に「合計範囲欄をクリックし、セルE2からE23を範囲選択します。

img20151008_06

 

7.「合計範囲」欄に「E2:E23」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を1回押して絶対参照にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

「OK」ボタンをクリックします。

img20151008_06-2

 

「カレンダーA4版」という文字で始まる商品の売上が集計されました。

img20151008_07

 

では、数式をコピーして他の大きさの商品の売上合計ももとめてみましょう。

8.コピーするセルの右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20151008_08

 

9.「カレンダーB4版」の売上額セルを選択し、数式の「A」を「B」に修正[Enter]キーを押します。

img20151008_09

 

同様に「カレンダーA3版」の売上額セル内の数式の「4」を「3」に修正[Enter]キーを押します。

img20151008_13

 

大きさ別の売上額がもとめられました。
正しい数式で計算されているかは、それぞれの表の合計額が同額であるかで確かめましょう。

img20151008_12

 

 

計算元のデータが入力されるまで、数式は入っていてもセルは空白状態にしておきたいことはありませんか?

ところが下表のように、項目が未入力の残高欄(E6、E7)に数式を入力しておくと・・・

img20151005_01

↓ ↓ ↓

img20151005_02

直前の計算結果(E5)が表示されてしまいます。

そこで今回は、データ(項目)が未入力のときはセル(残高)が空白になるように、IF関数を使って設定する方法をご紹介しましょう。

◇IF関数・・・IF(論理式、真の場合、偽の場合)
論理式を満たすときは真の場合を返し、満たさない場合は偽の場合を返す

 

数式の結果を表示しないで空白にする方法(IF関数)

今回使うIF関数の内容は以下のようになります。
・論理式…指定したセルが空白ならば
・真の場合…(論理式どおりなら)セルを空白にする
・偽の場合…(論理式と違うなら)計算式をもとめる

 

では、まずE3の計算式を削除してから、IF関数を使った数式を入力してE7まで数式をコピーしましょう。

1.E3を選択し、[Delete]キーを押してデータを削除します。

img20151005_03

 

2.関数の挿入ボタン「fx」をクリックします。

img20151005_04

 

3.関数の挿入ウインドウが開くので、[関数の分類(C)]から「論理」を選び、[関数名(N)]から「IF」を選び「OK」ボタンをクリックします。

img20151005_05

 

4.「論理式」欄をクリックし、セルB3をクリックし、続けて「=””と半角入力します。

img20151005_06

img20151005_07

※「”」間に文字列を入れないことで空白になります。

 

5.「真の場合」欄をクリックし、「””と入力します。

img20151005_08

 

6.「偽の場合」欄をクリックし、残高をもとめる計算式(E2+C3-D3)を入力します。
セル[E2]クリック→「+」入力→[C3]クリック→「」入力→[D3]クリックする。(全文を直接入力しても可)
最後に「OK」ボタンをクリックします。

img20151005_09

 

数式が入力されて残高が表示されました。

img20151005_10

 

下のセルまで数式をコピーしましょう。

7.コピーするセルの右下にマウスポインターをおいて、フィルハンドル(+)をセル[E7]までドラッグします。

img20151005_11

 

項目が未入力の残高欄が、数式は入力されてますが空白になりました。

img20151005_12

 

新規入力した場合、項目と入金(出金)を入力すると残高は自動的に計算表示されます。

img20151005_13

 

 

商品別の購入者数、売上額別の社員数など、ある条件を満たすセルの個数(データの数)をもとめたい場合は、COUNTIF関数を使ってみましょう。

範囲と条件を指定するだけで簡単にもとめられます。

◇COUNTIF関数・・・指定した範囲内の検索条件を満たすセルの個数をもとめます。

条件と一致するセルの個数をもとめる方法(COUNTIF関数)

下表の「人数別世帯数」をCOUNTIF関数を使ってもとめてみましょう。

img20150928_01

 

1.世帯数をもとめるセル(E6)を選択し、関数の挿入ボタン「fx」をクリックします。

img20150928_01-1

 

2.関数の挿入ウインドウが開くので、[関数の分類(C)]から「総計」を選び、[関数名(N)]から「COUNTIF」を選び「OK」ボタンをクリックします。

img20150928_02

 

3.「範囲」欄をクリックし、B2からB13をドラッグします。

img20150928_03

↓ ↓ ↓

img20150928_04

 

4.「範囲」欄に「B2:B13」と自動的に入力されます。
そのまま[F4]キー(ファンクションキー)を1回押して絶対参照にします。
(数式をコピーしたときに、範囲が変わらないように絶対参照にします)

img20150928_05

 

5.次に「検索条件」欄をクリック→D6を選択します。「OK」ボタンをクリックします。

img20150928_06

 

ほかにも、「2」と直接入力する方法もあります。
(検索条件が文字列の場合は「”」(ダブルクォーテーション)で囲みます。)

img20150928_06-1

 

人数が2の世帯数の数がもとめられました。

【数式の意味】
「B2からB13」の範囲の中で、「D6」(または「2」)と同じデータの数をかぞえます。

img20150928_07

 

数式をコピーして、人数が「3」「4」の世帯数をもとめてみましょう。

6.コピーするセルの右下にマウスポインターをおいて、フィルハンドル(+)を「4」の世帯数のセルまでドラッグします。

img20150928_08

コピーされました。

img20150928_09

 

「5以上」の世帯数をもとめる場合は、検索条件に注意しましょう。

7.「検索条件」欄に「>=5」と直接入力し、「OK」ボタンをクリックします。

img20150928_10

【参考】
「>=○」は「○以上」「<=○」は「○以下」をあらわします。

 

すべての人数別世帯数がもとめられました。

img20150928_11

 

 

範囲内の合計は、SUM関数を使ってもとめるのが一般的です。

では累計をもとめたいときはどのようにすればよいでしょうか?

実はこの場合もSUM関数を使います。最初の数値を絶対参照にするのがポイントです。
(絶対参照についての詳細はこちらをご覧ください。)

累計をもとめる方法(SUM関数・絶対参照)

下表の累計をSUM関数を使ってもとめてみましょう。

img20150921_01

 

1.累計をもとめたい一番上のセル(D2)を選択します。

img20150921_02

 

2.D2セルをクリックし「=SUM(」と入力します。
次に、C2セルをクリックし、[F4]キーを1回押して絶対参照$C$2」にします。

img20150921_03

 

3.「=SUM($C$2」の次をクリックし、続けて「:」と入力C2セルをクリック→「)」と入力します。
最後に[Enter]キーを押します。

img20150921_04

 

計算式が入力され、結果が表示されました。

img20150921_05

 

次は下のセルに計算式をコピーしましょう。

4.D2セルの右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20150921_06

 

下のセルにもコピーされて累計がもとめられました。

img20150921_07

 

次に、データを追加した場合のコピーの方法をご説明します。

1.6行目にデータを追加入力します。

img20150921_08

 

2.数式の入った一番下のセルを選択し、セルの右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20150921_09

 

追加したデータの累計がもとめられました。

img20150921_10

 

 

売上データを支店別に複数のシートに分けて作った場合、どのようにすれば手早く集計できるでしょうか?

連続して並んでいる複数のシートの同じセル位置のセルを参照することで簡単に集計することができます。

複数シートの同じ位置のセルデータを集計する方法

シート「本店」「札幌店」「横浜店」のそれぞれの売上額を、シート「全本支店」の売上額に集計してみましょう。

img20150914_01

 

1.集計するセルを選択します。

img20150914_02

 

2.[ホーム]タブ→[編集]→「ΣオートSUM」を選ぶと、セル内に「=SUM()」と自動的に表示されます。

img20150914_03

↓ ↓ ↓

img20150914_04

 

3.シート名「本店」をクリックし、集計を表示するセルと同じ位置のセル(ここではセルB2)をクリックします。

img20150914_05

 

4.「Shift」キーを押しながら、シート「横浜店」(集計したい範囲の最後のシート名)をクリックし、[Enter]キーを押します。

数式バーに「=SUM(’本店:横浜店’!B2)」と自動的に表示されます。

img20150914_06

 

シート「全本支店」のセルに各本支店の売上額が集計されました。

img20150914_07

次に、この数式を下のセルにコピーしましょう。

 

6.数式の入ったセルの右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20150914_08

 

全部のセルに数式がコピーされて集計されました。

img20150914_09

 

表を作ったときに、1行ごと交互に色がついていると見やすいですよね。

ただし、1行ごとに色をつけていくと手間がかかります。

そこでオートフィルオプションを使って簡単に色をつける方法をご紹介しましょう。

 

1行ごと交互に色をつける方法

下表に1行ずつ交互に色をつけてみましょう。

img20150910_01

 

1.色をつけたい行(A2~C2)を範囲選択します。

img20150910_02

 

2.[ホーム]タブ→[フォント]→「塗りつぶしの色」アイコンをクリックし、「ゴールド」を選択します。

img20150910_03

 

3.2行目に色がついたら、色のついていない下の行と2行(A2~C3)を範囲選択します。

img20150910_04

 

4.選択範囲の右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20150910_05

 

文字もコピーされるので、「オートフィルオプション」で書式だけをコピーします。

5.オートフィルオプションのアイコンをクリックします。

img20150910_06

 

6.「書式のみコピー(フィル)(F)」に◎チェックをつけます。

img20150910_07

 

1行ごと交互に色がつきました。

img20150910_08

 

●行を追加した場合

行を追加したときでも同じように色をつけることができます。

img20150910_09

 

1.色がついている最後の行と下の行の2行(A10~C11)を選択します。

img20150910_10

 

2.あとは、上記の4~6と同じ方法で書式のみをコピーします。

img20150910_11

↓ ↓ ↓ ↓

img20150910_12

↓ ↓ ↓ ↓

追加した行にも交互に色がつきました。

img20150910_13

 

「自動表示させた土日祝に色をつけたい」というリクエストがありましたので、今回から3回にわけて設定方法をご紹介したいと思います。

できるかぎり簡単な関数を使った方法にしていますので、トライしてみてください。

・1回目「曜日を自動で表示する方法」

・2回目「土日に色をつける方法」

・3回目「祝日に色をつける方法」

 

曜日を自動で表示する方法(TEXT関数)

下表の日付に対して隣のセルに曜日を自動で表示させてみましょう。

img20150831_01

 

1.曜日を表示させたいセル(C2)を選択します。

img20150831_02

 

2.そのセルに「=text(B2,”aaa”)」(半角)と入力し、[Enter]キーを押します。

※TEXT関数
 TEXT(値,表示形式)…指定した表示形式の文字列に変換する
表示形式:aaa…日~土、aaaa…日曜日~土曜日

img20150831_03

 

「金」と表示されました。

img20150831_04

 

次に数式を下のセルにコピーしましょう。

3.C2セルの右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20150831_05

 

数式を文字列にするためにコピー&ペーストをしましょう。

4.セルが選択された状態のままで右クリックをし、コピー(C)を選びます。([Ctrl]+[C]でも可能)

img20150831_06

 

5.同じ状態のままふたたび右クリックをし、[貼り付けのオプション:]→「値」アイコンをクリックします。

img20150831_07

 

曜日が文字として貼り付けられました。

img20150831_08

 

次回は「土日に色をつける方法」をご紹介します。

 

 

日付に対して曜日を表示させたいとき、自動で表示できると便利だと思いませんか?

表示形式で設定することで、日付と同じセル内に曜日を表示させることができます。

★日付と同じセル内に曜日を自動表示する方法★

下表の日付の横に曜日を自動で表示するように設定してみましょう。

img20150827_01

 

1.日付が入力してあるセルを選択(ここではB3セル)し、右クリック→「セルの書式設定(F)」を選択します。

img20150827_02

もしくは、[ホーム]タブ→[数値]グループの右下の「斜め矢印」をクリックします。

img20150824_06

2.セルの書式設定ウインドウが開くので、[表示形式]タブの[分類(C):]→「ユーザー定義」を選択し、[種類(I):]欄に表示されている文字の最後に「(aaa)」(※すべて半角)と追加入力します。
サンプル欄に「(水)」と表示されるのを確認したら、「OK」ボタンをクリックします。

img20150827_05

 

日付の横に曜日が自動表示されました。

img20150827_06

下のセルにも設定しましょう。

 

3.B3セルの右下にマウスポインターをおいて、フィルハンドル(+)をダブルクリックします。

img20150827_07

下のセルにも曜日が表示されました。

img20150827_08