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

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

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

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

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

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

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

 

 

本日はリクエストにお答えいたします。

毎月、月間予定表の日付を作るとなると、その都度、入力するのは面倒だと思いませんか?
そこで、年と月を入力するだけで一ヶ月の日付が自動的に表示される表を作成しました。

長くなるので2回に分けて説明しますね。

●1回目
DATE関数を使い、別のセルに入力されている「年」「月」「日」をあわせて表示させて、表示を「○月○日(○)」にいう形にします。

●2回目
「2日」以降のセルに「1日に1を足す」という数式を入れて完成させます。

実際にやってみるととても簡単なので、カレンダー以外でも活用してみてくださいね。

◇DATE関数…別のセルのデータを組み合わせて日付として表示する
DATE(“年”を示す数字かセルを指定,”月”を示す数字かセルを指定,”日”を示す数字かセルを指定)

例1:=DATE(2015,10,23)と入力すると →「2015/10/23」と表示される
例2:=DATE(A1,B2,C3)と入力すると、それぞれのセルに入力されている数字が日付形式で表示される

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

下の月間予定表を、年と月を入力するだけで自動的に一ヶ月の日付が表示されるように設定しましょう。

img20151023_01

 

1.年(セルD2)月(セルF2)にそれぞれ「2015」「11」と半角入力します。

img20151023_02

 

2.1日を表示するセル(B5)を選択し、関数の挿入ボタン「fx」をクリックします。

img20151023_03

img20151001_02_1

 

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

img20151023_04

 

4.「」として表示させるセル(D2)を選択します。

img20151023_05

img20151023_05-2

↓ ↓ ↓

img20151023_05-3

 

5.「月」の欄をクリックし、「」として表示させるセル(F2)を選択します。

img20151023_06

↓ ↓ ↓

img20151023_06-2

 

6.「日」の欄をクリックし、「1」と直接半角入力します。
「OK」ボタンをクリックします。

img20151023_07

 

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

img20151023_08

 

では、表示形式を「○月○日(○)」にしてみましょう。

7.リボンの「数値」の右下アイコンをクリックします。

img20151023_09

もしくは、右クリック→セルの書式設定(F)を選択します。

img20151023_17

 

8.セルの書式設定ウインドウが開くので、[分類(C)]から「ユーザー定義」を選び、[種類(T)]から「m”月”d”日”」を選びます。

img20151023_09-1

 

9.[種類(T)]のすぐ下の欄に表示されている「m”月”d”日”」のあとに「(aaa)」と直接半角入力し、「OK」ボタンをクリックします
(※aaaは日~土を表示するための形式)

img20151023_09-2

 

表示が「11月1日(日)」となりました。

img20151023_10

 

次回は、「2日」以降のセルに簡単な数式を入れて予定表を完成させます。

 

商品別、社員別など同じ条件のデータを合計する方法として、以前、SUMIF関数をご紹介しました。
では「地域別」かつ「商品別」などのように複数の条件がある場合はどのようにすればよいでしょう?

ということで今回は、複数の条件のデータを合計する方法、SUMIFS関数をご紹介します。
「SUMIF」に「S」がつくのでSUMIF関数の複数形のようなものですが、数式の順序が少し違うので気をつけてくださいね。

◇SUMIFS関数・・・合計対象範囲の中から、指定した範囲内の検索条件と同じデータをさがし、そのすべての条件を満たすデータに対する計をもとめます。
SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2,・・・・)

複数の条件のデータを合計する方法(SUMIFS関数)

下表の一覧から、
「ビジネス名刺の配送での売上」
「個人名刺の配送での売上」
ビジネス名刺の店頭での売上」
「個人名刺の店頭での売上」をそれぞれもとめてみましょう。

img20151016_01

<もとめ方>
まず、合計する範囲・売上額(E列)を指定。
条件範囲1に商品名(B列)、条件1に「ビジネス名刺(G3)」を指定、条件範囲2に納品(C列)、条件2に「配送(H2)」をそれぞれ指定します。

 

1.合計を表示したいセル(H3)を選択し、関数の挿入ボタン「fx」をクリックします。

img20151016_02

img20151001_02_1

 

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

img20151016_03

 

3.「合計対象範囲」欄をクリックし、セルE2からE15(売上額)を範囲選択します。

img20151016_04

img20151016_05

 

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

img20151016_05-2

 

5.次に「条件範囲1欄をクリックし、B2:B15(商品名)を範囲選択します。

img20151016_06

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

img20151016_06-2

 

6.次に「条件1欄をクリックし、セルG3(「ビジネス名刺」)を選択します。

img20151016_07

条件1」欄に「G3」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を3回押して絶対参照「$G3」にします。
(数式をコピーしたときに、列番号(G列 )が変わらないようにします)

img20151016_07-2

 

7.次に「条件範囲2欄をクリックし、C2:C15(納品)を範囲選択します。

img20151016_08

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

img20151016_08-2

 

8.次に「条件2欄をクリックし、セルH2(「配送」)を選択します。

img20151016_09

条件2」欄に「H2」と自動的に入力されるので、そのまま[F4]キー(ファンクションキー)を2回押して絶対参照「H$2」にします。
(数式をコピーしたときに、行番号(2行目 )が変わらないようにします)

img20151016_09-2

 

これで「ビジネス名刺の配送での売上」が求められました。

img20151016_10

 

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

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

img20151016_11

 

「個人名刺の配送での売上」がもとめられました。

img20151016_11-2

 

10.セルH3を選択し[Ctlr]キー+「C」でコピーをします。

img20151016_12

貼り付けたい先のセルを指定し、[Enter]キーを押します。

img20151016_12-2

 

「ビジネス名刺の店頭での売上」と「個人名刺の店頭での売上」がもとめられました。

img20151016_13

 

◎おまけ
数式のカッコ内をクリックすると、指定している箇所が色別で表示されます。
どこを指定しているかを確認したいときに利用すると便利ですよ。

img20151016_14

 

 

以前に、条件と一致するセルの個数をもとめる方法(COUNTIF関数)をご紹介しました。

COUNTIF関数は指定できる条件が1つですが、今回ご紹介するCOUNTIFS関数は複数の条件を指定できます。

”「COUNTIF」に「S」がついた複数形”と考えると覚えやすいでしょう。

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

下表のグループ1、グループ2それぞれの条件を満たす人数(セルの数)を関数を使ってもとめてみましょう。

グループ1の条件は「30歳以上」と「男」、グループ2の条件は「30歳以上」と「女」です。

 

1.人数を表示するセル(F2)を選択し、関数の挿入ボタン「fx」をクリックします。

 

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

img20151012_03

 

まず、検索条件範囲1で「年齢」を指定して、そこから検索条件の「30(歳)以上」を指定します。

3.「検索条件範囲1」欄をクリックし、B2からB26をドラッグします。

img20151012_04

img20151012_05

 

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

img20151012_05-2

 

5.「検索条件1」欄に「>=30」(”30(歳)以上”の意味)と直接入力し、「OK」ボタンをクリックします。

img20151012_06

——————————–
【参考】
●と等しい…=●
●以上…>=●
●以下…<=●
●より大きい(上)…>●
●未満、より小さい(下)…<●
●と等しくない…<>●
——————————–

 

次に、検索条件範囲2で「性別」を指定して、そこから検索条件の「男」を指定します。

6.「検索条件範囲2」欄をクリックし、C2からC26をドラッグします。

img20151012_07

 

7.「検索条件範囲2」欄に「C2:C26」と自動的に入力されます。
そのまま[F4]キー(ファンクションキー)を1回押して絶対参照「$C$2:$C$26」にします。
(数式をコピーしたときに、範囲が変わらないために絶対参照にします)

img20151012_07.-2

 

8.「検索条件2」欄クリックしてセルE3(男)を選択し、「OK」ボタンをクリックします。

img20151012_09

※「男」と入力されたセルがなくて指定できない場合は、検索条件欄に直接「男」と入力します

img20151012_14

 

グループ1の「30歳以上」の「男」を満たす人数(セルの数)が表示されました。

 

では、数式をコピーをしてグループ2を満たす人数ももとめてみましょう。

9.条件1の人数セル(F2)を選択し、[Ctrl]キー+「C」を押します。(コピー)

 

10.条件2の人数セル(F7)を選択し、[Enter]キーを押します。(貼り付け)

関数式の「男」と指定したセル(E3)が「女」のセル(E8)に変わっているのを確かめましょう。

グループ2の「30歳以上」の「女」を満たす人数は「5」と表示されました。

 

結果は、グループ1が10(人)、グループ2が5(人)となります。

 

 

以前、同じ条件のデータを合計する方法(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

 

 

商品別、社員別、科目別など、項目別にそれぞれの合計をだしたいときはありませんか?
そんなときにはSUMIF関数を使ってみましょう。
商品別の数量や金額をもとめるときにとっても便利ですよ!

◇SUMIF関数・・・指定した範囲内の検索条件と同じデータをさがし、そのデータに対する合計範囲の計をもとめます。

同じ条件のデータを合計する方法(SUMIF関数)

下表の商品別売上額をもとめてみましょう。

売上商品名(B列)から商品名(H列の各々)を検索して、 売上額(E列)から商品名(H列の各々)別の売上だけを合計します。

img20151001_01

 

1.合計を表示したいセル(I2)を選択し、関数の挿入ボタン「fx」をクリックします。

img20151001_02

img20151001_02_1

 

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

img20151001_03

 

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

img20151001_04

img20151001_04_1

 

 

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

img20151001_04_2

 

5.「検索条件欄をクリックし、セルH2を選択します。

img20151001_05_1

 

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

img20151001_06_1

 

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

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

img20151001_06_2

 

セルH2(商品名:カレンダーA4版)の合計がもとめられました。

img20151001_07

img20151001_07_1

売上商品名の範囲($B$2:$B23)から、「カレンダーA4版」(H2)を検索して、
合計する範囲 ($E$2:$E23)から「カレンダーA4版」(H2)の売上だけを合計しました。

 

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

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

img20151001_08

全商品の売上額がもとめられました。

 

正しい数式で計算されているかは、それぞれの表の合計額が同額であるかで確かめましょう。

img20151001_09