1. Home » 
2. エクセル練習問題：目次 » 
3. 条件付きの合計の計算

エクセル練習問題：条件付きの合計の計算

更新:2018/5/18;作成:2012/4/18

問題1の解答例　　　topへ

• 	B	C	D	E	F	G
  2	販売先	商品名	販売額		販売先	販売額合計
  3	井上商事	りんご	52,000		上田青果	245,000
  4	上田青果	りんご	65,000
  5	井上商事	みかん	78,000
  6	上田青果	みかん	43,000
  7	井上商事	りんご	45,000
  8	上田青果	バナナ	57,000
  9	井上商事	バナナ	49,000
  10	上田青果	りんご	80,000

SUMIF関数で求める

• 数式の例：一つの条件での合計はSUMIF関数を使うことができます。
  • 構文：=SUMIF(範囲, 検索条件, [合計範囲])
    SUMIF関数　に説明がありますので参照してください。
  G3セルに　=SUMIF(B3:B10,F3,D3:D10)　と入力します。
  

  	B	C	D	E	F	G
  2	販売先	商品名	販売額		販売先	販売額合計
  3	井上商事	りんご	52,000		上田青果	=SUMIF(B3:B10,F3,D3:D10)
  4	上田青果	りんご	65,000
  5	井上商事	みかん	78,000
  6	上田青果	みかん	43,000
  7	井上商事	りんご	45,000
  8	上田青果	バナナ	57,000
  9	井上商事	バナナ	49,000
  10	上田青果	りんご	80,000

1. [関数の挿入]から数式を作成するときの操作手順
   数式を入力する G3セルを選択して、関数の挿入ボタンをクリックします。
   
2. 関数の挿入 ダイアログボックスが表示されます。
   関数の分類で「すべて表示」を選択します。
   関数名で「SUMIF」を選択します。
   [OK]ボタンをクリックします。または、[Enter]キーを押します。
   
3. SUMIF関数の引数ダイアログボックスが表示されます。
   範囲に B3:B10　、検索条件に F3　、合計範囲に D3:D10　を入力します。
   
4. G3セルに　=SUMIF(B3:B10,F3,D3:D10)　と入力されました。
   

SUMPRODUCT関数で求める

• 構文：=SUMPRODUCT(配列1,[配列2],[配列3],・・・)
  SUMPRODUCT関数　に説明がありますので参照してください。
• G3セルに　=SUMPRODUCT((B3:B10=F3)*(D3:D10))　と入力します。
  (B3:B10=F3)の部分は論理値(TRUE/FALSE)を返すので、(D3:D10)との積にして数値の1または0を返すようにして、計算しています。
  

  	B	C	D	E	F	G
  2	販売先	商品名	販売額		販売先	販売額合計
  3	井上商事	りんご	52,000		上田青果	=SUMPRODUCT((B3:B10=F3)*(D3:D10))
  4	上田青果	りんご	65,000
  5	井上商事	みかん	78,000
  6	上田青果	みかん	43,000
  7	井上商事	りんご	45,000
  8	上田青果	バナナ	57,000
  9	井上商事	バナナ	49,000
  10	上田青果	りんご	80,000

• SUMPRODUCT関数の計算過程は下図のような感じです。
  I列には　=B3*$F$3　が入力され、TRUE/FALSEが返されています。
  K列では　=D3*I3　と販売額を掛け合わせています。TRUE/FALSEは演算すると、ワークシートでは1/0として計算されます。
  この結果の合計が計算結果として得られています。
  

配列数式で求める

• G3セルに　=SUM(IF(B3:B10=F3,D3:D10,0))　と入力して、[Ctrl]+[Shift]+[Enter]で数式の入力を確定します。
  数式は　{=SUM(IF(B3:B10=F3,D3:D10,0))}　となります。
  

Filter関数を使って求める

• Microsoft365ではFilter関数が使えます。SUM関数と組み合わせて条件付きの合計を計算することができます。
  G3セルに　=SUM(FILTER(D3:D10,B3:B10=F3))　と入力します。
  
  • =FILTER(D3:D10,B3:B10=F3)　で「上田青果」の販売額のセルが配列として抽出されます。
    この抽出された配列をSUM関数で合計するという仕組みです。
    FILTER関数については　FILTER関数でデータを抽出する　をご覧ください。
    

問題2の解答例　　　　topへ

• 	B	C	D	E	F	G	H
  2	販売先	商品名	販売額		販売先	商品名	販売額合計
  3	井上商事	りんご	52,000		上田青果	りんご	145,000
  4	上田青果	りんご	65,000
  5	井上商事	みかん	78,000
  6	上田青果	みかん	43,000
  7	井上商事	りんご	45,000
  8	上田青果	バナナ	57,000
  9	井上商事	バナナ	49,000
  10	上田青果	りんご	80,000

SUMIFS関数で求める

1. 数式の例：複数条件での合計はSUMIFS関数を使います。（この関数はExcel2007以降で使用できます）
   • 構文：SUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)
     SUMIFS関数　に説明がありますので参照してください。
   H3セルに　=SUMIFS(D3:D10,B3:B10,F3,C3:C10,G3)　と入力します。
   

   	B	C	D	E	F	G	H
   2	販売先	商品名	販売額		販売先	商品名	販売額合計
   3	井上商事	りんご	52,000		上田青果	りんご	=SUMIFS(D3:D10,B3:B10,F3,C3:C10,G3)
   4	上田青果	りんご	65,000
   5	井上商事	みかん	78,000
   6	上田青果	みかん	43,000
   7	井上商事	りんご	45,000
   8	上田青果	バナナ	57,000
   9	井上商事	バナナ	49,000
   10	上田青果	りんご	80,000

   
   SUMIFS関数の引数ダイアログボックスを使用した場合は、合計対象範囲に D3:D10　、条件範囲1に B3:B10　、条件1に F3　、条件範囲2に C3:C10　　、条件2に　G3 を入力します。
   

DSUM関数で求める

1. この問題ではフィールド名がF2:G2に入力されているのでDSUM関数が使えます。
   Excel2003以前ではSUMIFS関数は使えませんが、DSUM関数が使えます。
   • 構文：=DSUM(データベース, フィールド, 検索条件)
     DSUM関数　に説明がありますので参照してください。
   H3セルに　=DSUM(B2:D10,3,F2:G3)　と入力します。
   

   	B	C	D	E	F	G	H
   2	販売先	商品名	販売額		販売先	商品名	販売額合計
   3	井上商事	りんご	52,000		上田青果	りんご	=DSUM(B2:D10,3,F2:G3)
   4	上田青果	りんご	65,000
   5	井上商事	みかん	78,000
   6	上田青果	みかん	43,000
   7	井上商事	りんご	45,000
   8	上田青果	バナナ	57,000
   9	井上商事	バナナ	49,000
   10	上田青果	りんご	80,000

   
   DSUM関数の引数ダイアログボックスを使用した場合は、データベースに B2:D10　、フィールドに 3　、条件に F2:G3 と入力します。
   

SUMPRODUCT関数で求める

1. SUMPRODUCT関数で計算することもできます。
   • 構文：=SUMPRODUCT(配列1,[配列2],[配列3],・・・)
     SUMPRODUCT関数　に説明がありますので参照してください。
   • (B3:B10=F3)と(C3:C10=G3)の部分は論理値(TRUE/FALSE)を返すので、積にして数値の1または0を返すようにしています。
   H3セルに　=SUMPRODUCT((B3:B10=F3)*(C3:C10=G3),D3:D10)　と入力します。
   

   	B	C	D	E	F	G	H
   2	販売先	商品名	販売額		販売先	商品名	販売額合計
   3	井上商事	りんご	52,000		上田青果	りんご	=SUMPRODUCT((B3:B10=F3)*(C3:C10=G3),D3:D10)
   4	上田青果	りんご	65,000
   5	井上商事	みかん	78,000
   6	上田青果	みかん	43,000
   7	井上商事	りんご	45,000
   8	上田青果	バナナ	57,000
   9	井上商事	バナナ	49,000
   10	上田青果	りんご	80,000

   
   SUMPRODUCT関数の引数ダイアログボックスでは、配列1に　(B3:B10=F3)*(C3:C10=G3)　、配列2に　D3:D10　と入力します。
   

FILTER関数で求める

• Microsoft365ではFilter関数が使えます。SUM関数と組み合わせて条件付きの合計を計算することができます。
  H3セルに　=SUM(FILTER(D3:D10,(B3:B10=F3)*(C3:C10=G3)))　と入力します。
  

• 	B	C	D	E	F
  2	販売先	商品名	販売額		りんごとみかんの販売額合計
  3	井上商事	りんご	52,000		363,000
  4	上田青果	りんご	65,000
  5	井上商事	みかん	78,000
  6	上田青果	みかん	43,000
  7	井上商事	りんご	45,000
  8	上田青果	バナナ	57,000
  9	井上商事	バナナ	49,000
  10	上田青果	りんご	80,000

SUMIF関数を使って求める

1. 「りんご」と「みかん」の合計の和で求めることができます。
   

   	B	C	D	E	F
   2	販売先	商品名	販売額		りんごとみかんの販売額合計
   3	井上商事	りんご	52,000		=SUMIF(C3:C10,"りんご",D3:D10)+SUMIF(C3:C10,"みかん",D3:D10)
   4	上田青果	りんご	65,000
   5	井上商事	みかん	78,000
   6	上田青果	みかん	43,000
   7	井上商事	りんご	45,000
   8	上田青果	バナナ	57,000
   9	井上商事	バナナ	49,000
   10	上田青果	りんご	80,000

2. 商品は「りんご」「みかん」「バナナ」の3種類ですので、すべての合計からバナナの合計額を差し引くことでも求めることができます。

   	B	C	D	E	F
   2	販売先	商品名	販売額		りんごとみかんの販売額合計
   3	井上商事	りんご	52,000		=SUM(D3:D10)-SUMIF(C3:C10,"バナナ",D3:D10)
   4	上田青果	りんご	65,000
   5	井上商事	みかん	78,000
   6	上田青果	みかん	43,000
   7	井上商事	りんご	45,000
   8	上田青果	バナナ	57,000
   9	井上商事	バナナ	49,000
   10	上田青果	りんご	80,000

3. 上記と同様に、「バナナでないものの合計」と考えると、SUMIF関数だけでも求めることができます。
   =SUMIF(C3:C10,"<>バナナ",D3:D10)
   

   	B	C	D	E	F
   2	販売先	商品名	販売額		りんごとみかんの販売額合計
   3	井上商事	りんご	52,000		=SUMIF(C3:C10,"<>バナナ",D3:D10)
   4	上田青果	りんご	65,000
   5	井上商事	みかん	78,000
   6	上田青果	みかん	43,000
   7	井上商事	りんご	45,000
   8	上田青果	バナナ	57,000
   9	井上商事	バナナ	49,000
   10	上田青果	りんご	80,000

SUMPRODUCT関数で求める

1. 他にも「マンゴー」とかの商品があった場合は・・・・？
   SUMIFS関数はAND条件のときは使えますが、この問題では「りんご」または「みかん」の合計ですので適切ではありません。
   SUMPRODUCT関数を使ってみます。
   • 「りんご」または「みかん」の合計ですので(C3:C10="りんご")+(C3:C10="みかん")　と論理和にします。

   	B	C	D	E	F
   2	販売先	商品名	販売額		販売額合計
   3	井上商事	りんご	52,000		=SUMPRODUCT((C3:C10="りんご")+(C3:C10="みかん"),D3:D10)
   4	上田青果	りんご	65,000
   5	井上商事	みかん	78,000
   6	上田青果	みかん	43,000
   7	井上商事	りんご	45,000
   8	上田青果	バナナ	57,000
   9	井上商事	バナナ	49,000
   10	上田青果	りんご	80,000

FILTER関数で求める

• Microsoft365ではFilter関数が使えます。SUM関数と組み合わせて条件付きの合計を計算することができます。
  りんご OR みかん という条件は論理和で求めます。
  H3セルに　=SUM(FILTER(D3:D10,(C3:C10="りんご")+(C3:C10="みかん")))　と入力します。
  

問題4の解答例　　　　topへ

• 	B	C	D	E	F
  2	年月日	商品名	販売額		5月の総販売額
  3	2011/4/2	りんご	52,000		145,000
  4	2011/4/3	りんご	65,000
  5	2011/4/4	みかん	78,000
  6	2011/5/7	みかん	43,000
  7	2011/5/8	りんご	45,000
  8	2011/5/9	バナナ	57,000
  9	2011/6/1	バナナ	49,000
  10	2011/6/2	りんご	80,000

SUMIFS関数で求める

• 2011年5月という条件は「2011/6/1未満　かつ　2011/5/1以上」と考えると、SUMIFS関数が使えます。
  なお、SUMIFS関数はExcel2007以降で使用できます。Excel2003以前では使用できません。
  数式は　=SUMIFS(D3:D10,B3:B10,"<2011/6/1",B3:B10,">=2011/5/1")　とします。
  
• 日付部分にDATE関数を使うこともできます。
  関数を使った場合、未満とか以上は & を使ってつなぎます。
  =SUMIFS(D3:D10,B3:B10,"<"&DATE(2011,6,1),B3:B10,">="&DATE(2011,5,1))
  

SUMIF関数で求める

• SUMIF関数でも計算することができます。
• 2011/5/1以降の合計を求めて、2011/6/1以降の合計を差し引けばよいと考えると、
  =SUMIF(B3:B10,">=2011/5/1",D3:D10)-SUMIF(B3:B10,">=2011/6/1",D3:D10)
  となります。
  
• 2011/6/1よりも前の合計から2011/5/1よりも前の合計を差し引くと考えることもできます。
  =SUMIF(B3:B10,"<2011/6/1",D3:D10)-SUMIF(B3:B10,"<2011/5/1",D3:D10)
  

SUMPRODUCT関数で求める

• 月数が5であるものの合計を求めればよいと考えたら、SUMPRODUCT関数が使えます。
• 1年分のデータしかないケースでは月数だけを調べて、
  =SUMPRODUCT((MONTH(B3:B10)=5)*D3:D10)
  とすることができます。
  
• 複数年のデータがあるケースでは、年と月を調べないといけなくなるので、
  =SUMPRODUCT((YEAR(B3:B10)=2011)*(MONTH(B3:B10)=5),D3:D10)
  とか、
  
  =SUMPRODUCT((TEXT(B3:B10,"yyyy/m")="2011/5")*D3:D10)
  といった数式も考えられます。
  

FILTER関数で求める

• Microsoft365ではFilter関数が使えます。SUM関数と組み合わせて条件付きの合計を計算することができます。
  F3セルに　=SUM(FILTER(D3:D10,MONTH(B3:B10)=5))　と入力します。
  

問題5の解答例　　　　topへ

• 	B	C	D	E	F	G	H	I
  2	年月日	商品名	販売額
  3	2011/4/2	りんご	52,000			バナナ	みかん	りんご
  4	2011/4/3	りんご	65,000		4月	0	78,000	117,000
  5	2011/4/4	みかん	78,000		5月	57,000	43,000	45,000
  6	2011/5/7	みかん	43,000		6月	49,000	0	80,000
  7	2011/5/8	りんご	45,000
  8	2011/5/9	バナナ	57,000
  9	2011/6/1	バナナ	49,000
  10	2011/6/2	りんご	80,000

SUMIFS関数で求める

• G3セルに以下の数式を入力して、他のセルにコピーします。
  =SUMIFS($D$3:$D$10,
  $C$3:$C$10,G$3,
  $B$3:$B$10,">="&(DATE(2011,LEFT($F4,1),1)),
  $B$3:$B$10,"<"&(DATE(2011,LEFT($F4,1)+1,1)))
  もしくは、G4:I6セルを選択して、数式バーに上記の数式を入力して、[Ctrl]+[Enter]で選択しているセルに同じ数式を入力します。
  • F列には4月〜6月と文字列が入力されているので、LEFT関数を使って 4〜6 の文字列を取り出します。
    DATE関数ではこの文字列を数値とみなして計算してくれます(エラーになりません)。
    5月〜7月は LEFT関数で求めた月に +1 としています。
  
• もしくは、SUBSTITUTE関数を使って 月 を削除する方法もあります。
  =SUMIFS($D$3:$D$10,
  $C$3:$C$10,G$3,
  $B$3:$B$10,">="&(DATE(2011,SUBSTITUTE($F4,"月",""),1)),
  $B$3:$B$10,"<"&(DATE(2011,SUBSTITUTE($F4,"月","")+1,1)))
  

ピボットテーブルで求める

• 表示はちょっと変わりますが、ピボットテーブルで計算することができます。
  列に商品名、行に年月日（月）、Σ値に販売額の合計を配置します。
  Excel2016以降では年月日に日付データを月にグループ化する必要がなくなりました。
  • Excel 2016ではピボットテーブルで日時の自動グループ化が行われるようになりました
  • Excel2013以前をお使いでグループ化の方法は ピボットテーブルの日付のグループ化　をご覧ください。
  

FILTER関数で求める

• Microsoft365ではFilter関数が使えます。SUM関数と組み合わせて条件付きの合計を計算することができます。
  G4セルに
  =SUM(FILTER($D$3:$D$10,((MONTH($B$3:$B$10)&"月"=$F4)*($C$3:$C$10=G$3)),0))
  と入力します。
  G4セルの数式を横方向(G3:I3セル)へオートフィルでコピーし、さらに、G3:I3セルを選択して、オートフィルで下方向へ数式をコピーします。
  ポイントは 0となる場合があるので、FILTER関数の引数[空の場合]を 0 とします。
  

スポンサードリンク

PageViewCounter

Since2006/2/27