ＳＵＭＩＦ、ＤＳＵＭ関数で条件にあった合計を求める
　


　条件を付けて個数、合計を求める

今回はIF関数の第2回目として、IF関数の機能を兼ねた単独関数についてご紹介しましょう。条件を付けて求めるにはIF関数です。しかし、更に算出した値に対して合計や個数を求める場合には、IF関数にSUM関数やCOUNTA関数をネストしなければならない・・・そんな必要はありません。エクセルには、そんな時のためにSUMIF関数やCOUNTIF関数が用意されています。
SUMIF関数とは、文字通り、SUM関数とIF関数の機能を兼ねた関数です。つまり、条件を付けて合計を求めてくれる便利な関数なのです。そして、COUNTIF関数もCOUNT(COUNTA)関数とIF関数の機能を兼ねているため、条件を付けて個数を求めてくれます。まさに一石二鳥の関数なのです。
ただし、これらの関数は、条件を1つしか指定できません。複数の条件を付けたい場合には、DSUM、DCOUNT関数を利用しましょう。AND、OR条件を利用した複雑な複数の条件でも、条件範囲を選択するだけで、一発で集計できます。
さらに、2回に渡ってご紹介しました、IF、COUNTIF、DSUM関数全てを組み合わせると、それぞれの関数の特徴が最大限に生かされた計算結果を導き出す事ができます。

　

今回の関数

数学/三角関数
 　サムイフ
　条件にあったセルの値を合計する
　
書　式 ＝SUMIF（範囲、検索条件[,合計範囲]）
　合計を求めるSUMと条件を設定するIFが組み合わさった関数。条件にあったセルだけを抽出し、合計を求めることができる。ここでは大人の場合と子供の場合の手数料(K列)を抽出し、それぞれ合計額を出している。


数学/三角関数
 　カウント　イフ
　条件にあったセルの個数を数える
　
書　式 ＝COUNTIF(範囲,検索条件)
　セル範囲に含まれる数値の個数を数えるCOUNTとIFが組み合わさった関数。これによって条件にあったセルの個数を数えることができる。ここではＭ列に「毎月抽選でプレゼント」と入っているセルの個数を数えている。



[1]条件を付けて合計を求める

セルに条件を付けて合計を求める方法には、3通りある。
1つの条件で合計を求めるには、SUMIF関数、複数の条件で求めるには、DSUM関数、条件付き合計式ウィザードを利用する。それぞれの違いについてご説明しよう。


1つの条件で合計を求める

1つの条件で素早く合計を求めたいならSUMIF関数を使おう。

【サンプルデータ　シート20-1】

  1. 条件を付けたいセルを選択

  2. 「数学/三角」関数から「SUMIF」関数を選択する。

　

  1. 範囲には、条件に設定する値を含む範囲を選択（コピーする為に絶対参照にしておく）
    【 $H$5:$H$14 】

  2. 検索条件には、条件を入力
    【 "大人" 】

  3. 合計範囲には、合計する範囲を選択する。
    【 $K$5:$K$14 】

  4. 「OK」ボタンをクリックすると、大人の利用料が求められる。

　

  1. 数式をコピーし

  2. 数式バーで、「大人」を「子供」に変更し、確定すると、子供の利用料が求められる

　

複数の条件で合計を求める

複数の条件を付けて合計を求めるなら、条件付き合計式ウィザード、DSUM関数を使おう。それぞれの違いについて詳しくご紹介。

◇■　AND条件のみ　■◇

条件付き合計式ウィザードは、ウィザードに従って操作していくだけで、複数の条件が付けられる。しかし、AND条件しか付ける事ができない。
また、リスト内にセルの結合や、ユーザー定義で表示形式を設定している場合は、うまく認識されないので、利用する前に、それぞれを外しておく事が必要。

Cコースを利用している大人の利用料計を求める

【サンプルデータ　シート20-2】

[ツール(T)] メニューから [アドイン(I)] を選択し、（このときCD-ROMから必要なデータをインストールするように求められる事もあります。指示に従ってCD-ROMをセットしてください）

　1.　[条件付き合計式ウィザード] にチェックを入れて

　2.　[OK] ボタンをクリックする。

　

  1. [ツール(T)] メニューから[ウィザード(W)]−[条件付き合計式(C)]を選択する。

　

  1. 条件を付けて合計を求めたいリストを範囲選択します。
    【 '20-2'!$B$4:$K$14 】

  2. 「次へ」ボタンをクリックする。

　

  1. 合計を求める列に [利用料] を選択

  2. 条件を選択し

  3. [条件の追加(A)]ボタンをクリックし、1つずつ設定していく。

  4. [次へ]ボタンをクリック。

サンプルデータでは条件に 【 大人/子供=大人 】 【 Ｃ=○ 】 を入力。

　

  1. [単一のセルに数式だけをコピーする(C)]にチェック。

  2. [次へ]ボタンをクリック。

　

  1. 答えを算出したいセルを選択。
    【 I16 】 入力する場合は【 '20-2'!$I$16 】

  2. [完了]ボタンをクリックする。

　

  1. 設定された数式は、配列数式で設定されるので、数式を変更した場合は、[Ctrl]＋[Shift]＋[Enter]キーで確定する必要がある。

　

3年以上利用している子供の利用料計を求める
  1. 条件に利用する列の表示形式は外しておく。

  2. 条件を1つずつ選択し、同様にして設定する。
    サンプルデータでは、【 大人/子供=子供 】 と 【 利用年数>=3 】 を入力

　

知ッ得

「条件付き合計式ウィザード」は、ウィザードを指定するだけで、複数の条件がつけられる。しかし、AND条件しかつけることができない。また、リスト内にセルの結合や、ユーザー定義で表示形式を設定しているとうまく認識されない。利用する前に、それぞれを外しておくことが必要。
※　サンプルファイルは解除済みです。



◇■　AND、OR条件　■◇

DSUM関数は、条件範囲を選択するだけで、複数の条件を付けて合計を求めることができる。しかも、AND、OR条件はもちろんのこと、リストの結合を外す必要もないので素早く算出する事ができる。

A、Dコースを利用している大人または、
　　５年以上利用している子供の利用料計

【サンプルデータ　シート20-3】

  1. リストと同じフィールドをコピーし、条件を入力する。同じ行に入力すると、AND条件、違う行に入力すると、OR条件が設定される。
    ここでは、「Aコース、Dコースを利用している大人」または「子供で利用年数が5年以上」という条件を設定。

  2. 合計を求めたいセルを選択し、[挿入(I)]メニューから[関数(F)]を選択。

　

  1. 「データベース」関数から「DSUM」関数を選択する

　

  1. 「Datebase」には、リストを範囲選択
    【 B4:N14 】
  2. 「フィールド」には合計したフィールドを選択　
    【 K4 】

  3. 「Criteria」には条件を範囲選択
    【 B16:K18 】

  4. [OK] ボタンをクリックする。

　

  1. 答えが求められる。

サンプルデータの数式は、DSUM(B4:N14,K4,B16:K18)　となります。

　

知ッ得

M列「概要」には、IF関数を使い、利用年数が５年かつＡ〜Ｅコースまでで３コース以上選択しているなら、「毎月抽選でプレゼント」、それ以外なら「抽選無し」と表示するよう設定している。式は「=IF(AND(I5>=5,COUNTA(C5:G5)>=3),"毎月抽選でプレゼント","抽選なし")」


[2]条件を付けて個数を求める

セルに条件を付けて個数を求めるには「COUNTIF」、「DCOUNTA」関数を利用する。
1つの条件で求めるには、「COUNTIF」関数、複数の条件で求めるには、「DCOUNTA」関数を利用しよう。

◇■　1つの条件で個数を求める　■◇

個数を求めるには、「COUNT」、「COUNTA」関数を使う。しかし、条件を付けて個数を求めるのに、IF関数をネストする必要なんてない。「COUNTIF」関数1つで、条件を付けて個数を求める事ができる。ただし、1つだけしか条件を設定する事ができない。

抽選者の人数を求める

【サンプルデータ　シート20-4】

  1. 個数を求めたいセルを選択し、[挿入(I)]メニューから[関数(F)]を選択。

  2. 「統計」関数から「COUNTIF」関数を選択し、[OK]ボタンをクリック。



  1. 「範囲」には、条件が含まれる範囲を選択します。
    【M5:N14 】

  2. 「検索条件」には、求めたい条件を「""」で囲んで入力。
    【 "毎月抽選でプレゼント" 】

  3. [OK] ボタンをクリックする。



  1. 人数が求められる。

　


◇■　複数の条件で個数を求める　■◇

複数の条件で、個数を求めたいなら、「DCOUNTA」関数を利用しよう。条件を範囲選択するだけで、複数の条件で、個数を求める事ができる。

お客様番号が2で始まる抽選者の人数を求める

条件を入力し、（サンプルデータは入力済み）

  1. 人数を求めたいセルを選択し、[挿入(I)]メニューから[関数(F)]を選択。

  2. 「データベース」関数から「DCOUNTA」関数を選択し、[OK] ボタンをクリック。

　

  1. 「Datebase」には、リストを範囲選択　
    【 B4:N14 】

  2. 「フィールド」には、条件の値を含むフィールドを選択
    【 M4 】

  3. 「Criteria」には、条件を範囲選択する。
    【 B18:H19 】

  4. [OK] ボタンをクリックする。



  1. 抽選者の人数が求められる。

　

◇■　COUNTIF関数を2つ使えば、複数の条件でもOK　■◇

2つの検索条件が、同じフィールド（列）であれば、COUNTIF関数を２つ使えば、2つの条件で個数を求める事ができる。

お客様番号が2で始まる人数を求める
  1. 人数を求めたいセルをクリックし、数式を入力していきます。

  2. １つ目のCOUNTIF関数で、20000以上の人数を求め　
    【 COUNTIF(B5:B14,">=20000") 】

  3. ２つ目のCOUNTIF関数で、30000以上の人数を求める。
    【 COUNTIF(B5:B14,">=30000") 】

つまり、20000以上の人数から、30000以上の人数を引けば、20000〜29999までの人数を求める事ができるというわけである。　
【 COUNTIF(B5:B14,">=20000")-COUNTIF(B5:B14,">=30000") 】

　

[3]IF、COUNTIF、DSUM 関数
　　　　　　　　　　　全てをネストする

２回に分けてご紹介してきた、IF関数、COUNTIF関数、DSUM関数全てをネストして使うと、エラー値を出さずに不一致データの処理が行え、一致データに条件を付けて算出する事ができる。
COUNTIF関数の答えが０の場合、つまり不一致の場合、セルに表示させる値を、そうでない場合、つまり、一致したデータの場合、セルに表示させる値をIF関数でそれぞれ、真の場合と儀の場合に設定すればよいわけである。
ここでは、未登録番号を入力すると、「未登録です」というコメントを表示させ、登録番号の場合は、利用料を集計するという数式を設定する。

◇■　1つの条件で個数を求める　■◇

個数を求めるには、「COUNT」、「COUNTA」関数を使う。しかし、条件を付けて個数を求めるのに、IF関数をネストする必要なんてない。「COUNTIF」関数1つで、条件を付けて個数を求める事ができる。ただし、1つだけしか条件を設定する事ができない。

【サンプルデータ　シート20-5】

  1. IF関数の条件欄には、Mの5番地に入力した番号が、登録番号欄に1つもない、つまり0である場合という数式をCOUNIF関数で設定する。
    【 COUNTIF(M9:M20,M5)=0 】
  2. 真の場合には、個数が0であった場合、セルに表示させる値を入力する。
    【 "未登録です" 】
  3. 偽の場合には、個数が1つでもあった場合、Mの5番地に入力した番号の利用料の合計を算出するので、DSUM関数で、条件をMの5番地として設定する。
    【 DSUM(B4:K20,K4,M4:M5) 】

サンプルデータの式は以下のようになります。
　=IF(COUNTIF(M9:M20,M5)=0,"未登録です",DSUM(B4:K20,K4,M4:M5))

　

今回の表示形式
お客様番号を誤って入力しないように、桁数を間違えた場合は、セルに「5桁入力必須」と表示され、登録されていない番号が入力された場合は、「未登録番号」と表示されるように、前回と同様、表示形式に条件を付けてみよう。

  1. 条件は、[　]で囲み、条件にあった場合にセルに表示させる値を続けて入力する。条件の区切りは、「；」を入力する。

サンプルデータでは20-5シートのM5セルの書式設定に、[<10000]5桁入力必須;[>50000]未登録番号と設定。