Excel(エクセル)基本講座:条件付き合計の関数(SUMIF関数の使い方,SUMIFS関数の使い方)

スポンサードリンク


条件付き合計(SUMIF関数) 複数条件での合計(SUMIFS関数) ワイルドカード

条件付き合計
 サムイフ
=SUMIF(検索範囲,検索条件,合計範囲)
検索範囲で検索条件と一致する行の合計範囲の値を合計します。


【解答1例】

【解答2例】

【解答3例】

【解答4例】

【解答例5】

  1. 2017/1/20 から 2017/2/20 まで という期間の条件は 2017/1/20以上 2017/2/20以下 という2つの条件を満たす必要がありますが、SUMIF関数では求めることができません。Excel2007以降ならSUMIFS関数で複数の条件を指定できます。
    よって、SUMIF関数では 2017/1/20以上の日付の売上合計から 2017/2/20より後の売上合計を差し引くことで求めます。
    「2017/1/20以上」は ">=2017/1/20" 、「2017/2/20より後」は ">2017/2/20" と書きます。
    下図では 絶対参照にしていますが、この数式は1ヶ所のみなので相対参照でOKです。
    H7セルの数式は =SUMIF(B3:B17,">=2017/1/20",D3:D17)-SUMIF(B3:B17,">2017/2/20",D3:D17) となります。

    SUMIF関数の引数ダイアログボックスでは、範囲に B3:B17 、検索条件に ">=2017/1/20" 、合計範囲に D3:D17 と入力しました。

    2つ目のSUMIF関数の引数ダイアログボックスでは、範囲に B3:B17 、検索条件に ">2017/2/20" 、合計範囲に D3:D17 と入力しました。
  2. 日付をセル参照する場合は ">="&セル番地 といった具合に書きます。
    数式は =SUMIF(B3:B17,">="&F4,D3:D17)-SUMIF(B3:B17,">"&F5,D3:D17) となります。

    SUMIF関数の引数ダイアログボックスでは、範囲に B3:B17 、検索条件に ">="&F4 、合計範囲に D3:D17 と入力しました。

    2つ目のSUMIF関数の引数ダイアログボックスでは、範囲に B3:B17 、検索条件に ">"&F5 、合計範囲に D3:D17 と入力しました。

SUMIFS関数:複数の検索条件を満たすセルの値を合計します。 Topへ
サム イフズ
SUMIFS(合計対象範囲,検索条件範囲1,検索条件1,検索条件範囲2,検索条件2...)
  Excel2007で追加された関数です。


【解答6例】

【解答7例】

【解答8例】

【解答9例】

  1. 「女」の「B」または「男」の「A」の条件を一つにまとめることができませんので、それぞれの条件で求めてから合計します。
    「女」の「B」は SUMIFS(G3:G22,D3:D22,"女",F3:F22,"B") 
    「男」の「A」は SUMIFS(G3:G22,D3:D22,"男",F3:F22,"A")
    でそれぞれ求めることができます。
    よって数式は =SUMIFS(G3:G22,D3:D22,"女",F3:F22,"B")+SUMIFS(G3:G22,D3:D22,"男",F3:F22,"A") としました。
    合計はSUM関数で求めることができますので、 =SUM(SUMIFS(G3:G22,D3:D22,"女",F3:F22,"B"),SUMIFS(G3:G22,D3:D22,"男",F3:F22,"A")) とすることもできます。
  2. SUMPRODUCT関数を使って求めることもできます。
    AND条件は乗算、OR条件は和算で論理式を組みたてます。
    女かつBは ((D3:D22="女")*(F3:F22="B")
    男かつAは (D3:D22="男")*(F3:F22="A")
    この2つの条件はOR条件なので +(プラス) します (D3:D22="女")*(F3:F22="B")+(D3:D22="男")*(F3:F22="A")
    よって数式は =SUMPRODUCT((D3:D22="女")*(F3:F22="B")+(D3:D22="男")*(F3:F22="A"),G3:G22) となります。
    SUMPRODUCT関数の引数ダイアログボックスでは 配列1に (D3:D22="女")*(F3:F22="B")+(D3:D22="男")*(F3:F22="A") 、配列2に G3:G22 と入力します。

【解答10例】

  1. 日付の期間を条件に書くときは ">=2017/1/20" のように " ダブルクォーテーションでくくります。
    「2017/1/20 から 2017/2/20 まで」 という条件は 2017/1/20以上 2017/2/20以下 となりますので、">=2017/1/20"  "<=2017/2/20" と書きます。
    数式は =SUMIFS(D3:D17,B3:B17,">=2017/1/20",B3:B17,"<=2017/2/20",C3:C17,"りんご") となります。
    SUMIFS関数の引数ダイアログでは下図のように設定します。
    合計対象範囲に D3:D17
    条件範囲1に B3:B17 、条件1に ">=2017/1/20" 、条件範囲2に B3:B17 、条件2に "<=2017/2/20"
    条件範囲3に C3:C17 、条件1に "りんご"
    と入力します。

  2. 下図のように期間がセルに入力してあり、期間をセル参照する場合は ">="&セル番地 といった書き方をします。
    数式は =SUMIFS(D3:D17,B3:B17,">="&F6,B3:B17,"<="&H6,C3:C17,"りんご") となります。
    下図では絶対参照になっていますが、りんごとみかんを数式で指定していて数式はコピーしませんので相対参照でOKです。

    SUMIFS関数の引数ダイアログでは下図のように設定します。
    合計対象範囲に D3:D17
    条件範囲1に B3:B17 、条件1に ">="&F6 、条件範囲2に B3:B17 、条件2に "<="&H6
    条件範囲3に C3:C17 、条件1に "りんご"
    と入力します。

ワイルドカード文字   文字列の検索、置換時に使用します     topへ
 ?は任意の 1 文字
  例)鹿児島県や鹿児島市を指定したい場合1文字(県と市)だけ異なるので、「鹿児島?」とします。
 *は任意の数の文字
  例)鹿児島県 宮崎県 など 県を含む文字を指定したい場合、鹿児島(3文字)と宮崎(2文字)が異なるので「*県」とします。
  ※ ?や*の検索には ~(チルダ) を使用します。

【問題】下表の中の「PTA会費」、「児童会費」、「父兄会費」を集計表の【会費】欄に計算しなさい。

【解答例】

スポンサードリンク



よねさんのWordとExcelの小部屋 »  エクセル関数一覧表 »  条件付き合計の関数(SUMIF関数の使い方,SUMIFS関数の使い方)

PageViewCounter
Counter
Since2006/2/27