以前に、条件と一致するセルの個数をもとめる方法(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

 

 

商品別、社員別、科目別など、項目別にそれぞれの合計をだしたいときはありませんか?
そんなときには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

 

 

商品別の購入者数、売上額別の社員数など、ある条件を満たすセルの個数(データの数)をもとめたい場合は、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