複数の条件で合計する関数(SUMIFS関数)の使い方:Excel関数

スポンサードリンク


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

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 と入力します。
  3. Excel for Microsoft365 では動的配列数式を使えます。
    I3セルの数式を =SUM((D3:D22="女")*(F3:F22="B")*G3:G22+(D3:D22="男")*(F3:F22="A")*G3:G22) とすることができます。
  4. なお以前のバージョンでは、CSE数式(従来の配列数式)として使うことができます。
    I3セルに数式を =SUM((D3:D22="女")*(F3:F22="B")*G3:G22+(D3:D22="男")*(F3:F22="A")*G3:G22) と入力して、[Ctrl]+[Shift]+[Enter] で入力を確定します。

問題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に "りんご"
    と入力します。

  3. Excel for Microsoft365 では動的配列数式を使えます。
    G8セルの数式を =SUM((B3:B17>=F6)*(B3:B17<=H6)*(C3:C17="りんご")*D3:D17) とすることができます。
    G9セルの数式は =SUM((B3:B17>=F6)*(B3:B17<=H6)*(C3:C17="みかん")*D3:D17) とします。
  4. なお以前のバージョンでは、CSE数式(従来の配列数式)として使うことができます。
    G8セルに数式は =SUM((B3:B17>=F6)*(B3:B17<=H6)*(C3:C17="りんご")*D3:D17) と入力して、[Ctrl]+[Shift]+[Enter] で入力を確定します。
    G9セルの数式は =SUM((B3:B17>=F6)*(B3:B17<=H6)*(C3:C17="みかん")*D3:D17) と入力して、[Ctrl]+[Shift]+[Enter] で入力を確定します。

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

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

【解答例】

スポンサードリンク



よねさんのWordとExcelの小部屋 »  エクセル関数一覧表 »  複数の条件で合計する関数(SUMIFS関数)の使い方

PageViewCounter
Counter
Since2006/2/27