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

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

更新:2024/5/5;作成: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)　と入力します。
  
• 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を返すようにして、計算しています。
  
• 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関数を使って求める

• Excel for Microsoft365,Excel2021では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関数で求める

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

DSUM関数で求める

• この問題ではフィールド名がF2:G2に入力されているのでDSUM関数が使えます。
  Excel2003以前ではSUMIFS関数は使えませんが、DSUM関数が使えます。
  • 構文：=DSUM(データベース, フィールド, 検索条件)
    DSUM関数　に説明がありますので参照してください。
  H3セルに　=DSUM(B2:D10,3,F2:G3)　と入力します。
  
• 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)　と入力します。
   
2. SUMPRODUCT関数の引数ダイアログボックスでは、
   配列1に　(B3:B10=F3)*(C3:C10=G3)
   配列2に　D3:D10　と入力します。
   

FILTER関数で求める

• Excel for Microsoft365、Excel2021ではFilter関数が使えます。
  SUM関数と組み合わせて条件付きの合計を計算することができます。
  H3セルに　=SUM(FILTER(D3:D10,(B3:B10=F3)*(C3:C10=G3)))　と入力します。
  • =FILTER(D3:D10,(B3:B10=F3)*(C3:C10=G3))で条件に一致するデータが抽出されます。
    そのデータをSUM関数で合計しています。
  　

• 	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. 「りんご」と「みかん」の合計の和で求めることができます。
   =SUMIF(C3:C10,"りんご",D3:D10)+SUMIF(C3:C10,"みかん",D3:D10)
   
2. 商品は「りんご」「みかん」「バナナ」の3種類ですので、すべての合計からバナナの合計額を差し引くことでも求めることができます。
   
3. 上記と同様に、「バナナでないものの合計」と考えると、SUMIF関数だけでも求めることができます。
   =SUMIF(C3:C10,"<>バナナ",D3:D10)
   

SUMPRODUCT関数で求める

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

FILTER関数で求める

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

問題4の解答例　　　　topへ

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

SUMIFS関数で求める

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

SUMIF関数で求める

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

SUMPRODUCT関数で求める

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

FILTER関数で求める

• Microsoft365ではFilter関数が使えます。SUM関数と組み合わせて条件付きの合計を計算することができます。
  F3セルに　=SUM(FILTER(D3:D10,MONTH(B3:B10)=5))　と入力します。
  
• 複数年のデータの場合は
  =SUM(FILTER(D3:D10,TEXT(B3:B10,"yyyy/m")="2024/5"))
  のような数式が考えられます。
  

問題5の解答例　　　　topへ

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

SUMIFS関数で求める

• G3セルに以下の数式を入力して、他のセルにコピーします。
  =SUMIFS($D$3:$D$10,
  $C$3:$C$10,G$3,
  $B$3:$B$10,">="&(DATE(2024,LEFT($F4,1),1)),
  $B$3:$B$10,"<"&(DATE(2024,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)))
  

SUMPRODUCT関数で求める

• G3セルに以下の数式を入力して、他のセルにコピーします。
  =SUMPRODUCT((TEXT($B$3:$B$10,"m月")=$F4)*($C$3:$C$10=G$3),$D$3:$D$10)
  
  月と商品名の論理積と販売額とをSUMPRODUCT関数で掛け合わせて、合計しています。
  下図は　４月*みかん　の計算過程を示しています。
  
• 

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

• 表示はちょっと変わりますが、ピボットテーブルで計算することができます。
  列に商品名、行に年月日（月）、Σ値に販売額の合計を配置します。
  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