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

スポンサードリンク


作成:2007/9/11;更新:2023/10/13

複数条件での合計(SUMIFS関数) ワイルドカードを使った数式

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


【問題1】下図のように男と女の各血液型別に得点の合計を求めなさい。

【問題1の解答例】

スピルが利用できる場合(Excel for Microsoft365 、Excel2021など)

SUMIFS関数を使った数式の入力手順

  1. 数式を入力する J3セルを選択します。
    =su と入力すると、su を含む関数のリストが表示されます。
    [↓]キーを4回押して SUMIFS を選択し、[Tab]キーを押して入力します。[Enter]キーではありません。
  2. G3 と入力して、[F4]キーを押して $G$3 と絶対参照にします。
    :G22 と入力して、[F4]キーを押して $G$22 と絶対参照にします。
    ,(カンマ)を入力して、=SUMIFS($G$3:$G$22, とします。
  3. 条件範囲1は $D$3:$D$22 と絶対参照とします。
    ポイントは 条件1は I3 ですが、数式を下と右へコピーしますので 列番号を固定して $13 と複合参照にします。
    =SUMIFS($G$3:$G$22,$D$3:$D$22,$I3,
  4. 条件範囲2は $F$3:$F$22 と絶対参照とします。
    ポイントは 条件2は J2 ですが、数式を下と右へコピーしますので 行番号を固定して J$2 と複合参照にします。
    =SUMIFS($G$3:$G$22,$D$3:$D$22,$I3,$F$3:$F$22,J$2)
    [Ctrl]+[Enter]で数式の入力を確定します。アクティブセルが J3セルのままになります。
    なお、[Enter]で確定すると、アクティブセルが J4セルとなり、次の操作に人手間必要となるからです。
  5. J3セルのフィルハンドルを右方向へドラッグして数式をコピーします。
  6. J3:M3セルを選択した状態で、フィルハンドルを下方向へドラッグして、数式をコピーします。
  7. 完成しました。

【問題2】年齢が30歳代の得点の合計を求めなさい。

【問題2の解答例】

スピルが利用できる場合(Excel for Microsoft365 、Excel2021など)

OR条件の計算例

  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) となります。

スピルが利用できる場合(Excel for Microsoft365 、Excel2021など)

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

【問題4】下表の 2017/1/20 から 2017/2/20 までの りんご の売上数の合計を求めなさい。
B C D
2 日付 品種 売上数
3 2017/1/10 みかん 36
4 2017/1/14 りんご 45
5 2017/1/18 みかん 26
6 2017/1/22 りんご 22
7 2017/1/26 みかん 24
8 2017/1/30 りんご 29
9 2017/2/3 みかん 45
10 2017/2/7 りんご 11
11 2017/2/11 みかん 11
12 2017/2/15 りんご 19
13 2017/2/19 みかん 17
14 2017/2/23 りんご 15
15 2017/2/27 みかん 50
16 2017/3/3 りんご 36
17 2017/3/7 みかん 12
  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,"りんご") となります。
  2. 下図のように期間がセルに入力してあり、期間をセル参照する場合は ">="&セル番地 といった書き方をします。
    数式は =SUMIFS(D3:D17,B3:B17,">="&F6,B3:B17,"<="&H6,C3:C17,"りんご") となります。
    下図では絶対参照になっていますが、りんごとみかんを数式で指定していて数式はコピーしませんので相対参照でOKです。

スピルが利用できる場合(Excel for Microsoft365 、Excel2021など)

  1. 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) とします。
  2. なお以前のバージョンでは、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 文字
 *は任意の数の文字
  ※ ?や*の検索には ~(チルダ) を使用します。

スピルが利用できる場合(Excel for Microsoft365 、Excel2021など)

スポンサードリンク



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

PageViewCounter
Counter
Since2006/2/27