Home »
エクセル練習問題:目次 »
条件付きの合計の計算

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

スポンサードリンク

(追記) (追記ここまで)

更新:2025年3月15日;作成:2012年4月18日

問題1 topへ

問題1:「上田青果」への販売額の合計をG3セルに表示しなさい。問題1の解答

B C D E F G

2 販売先商品名販売額販売先販売額合計

3 井上商事りんご 52,000 上田青果

4 上田青果りんご 65,000

5 井上商事みかん 78,000

6 上田青果みかん 43,000

7 井上商事りんご 45,000

8 上田青果バナナ 57,000

9 井上商事バナナ 49,000

10 上田青果りんご 80,000
問題2:「上田青果」へ「りんご」の販売額の合計をH3セルに表示しなさい。問題2の解答

B C D E F G H

2 販売先商品名販売額販売先商品名販売額合計

3 井上商事りんご 52,000 上田青果りんご

4 上田青果りんご 65,000

5 井上商事みかん 78,000

6 上田青果みかん 43,000

7 井上商事りんご 45,000

8 上田青果バナナ 57,000

9 井上商事バナナ 49,000

10 上田青果りんご 80,000
問題3:「りんご」と「みかん」の販売額合計をF3セルに求めなさい。問題3の解答

B C D E F

2 販売先商品名販売額りんごとみかんの販売額合計

3 井上商事りんご 52,000

4 上田青果りんご 65,000

5 井上商事みかん 78,000

6 上田青果みかん 43,000

7 井上商事りんご 45,000

8 上田青果バナナ 57,000

9 井上商事バナナ 49,000

10 上田青果りんご 80,000
問題4:2025年5月の販売額の合計を求めなさい。問題4の解答

B C D E F

2 年月日商品名販売額 5月の総販売額

3 2025年4月2日りんご 52,000

4 2025年4月3日りんご 65,000

5 2025年4月4日みかん 78,000

6 2025年5月7日みかん 43,000

7 2025年5月8日りんご 45,000

8 2025年5月9日バナナ 57,000

9 2025年6月1日バナナ 49,000

10 2025年6月2日りんご 80,000
問題5:下図のF:I列のようなクロス集計表を作成しなさい問題5の解答
- クロス集計の問題はエクセル練習問題:クロス集計表の作成もご覧ください。
B C D E F G H I

2 年月日商品名販売額

3 2025年4月2日りんご 52,000 バナナみかんりんご

4 2025年4月3日りんご 65,000 4月 0 78,000 117,000

5 2025年4月4日みかん 78,000 5月 57,000 43,000 45,000

6 2025年5月7日みかん 43,000 6月 49,000 0 80,000

7 2025年5月8日りんご 45,000

8 2025年5月9日バナナ 57,000

9 2025年6月1日バナナ 49,000

10 2025年6月2日りんご 80,000

(追記) (追記ここまで)

問題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関数で求める Filter関数を使って求める(Excel2021以降)

SUMPRODUCT関数で求める配列数式で求める

SUMIF関数で求める

数式の例:一つの条件での合計はSUMIF関数を使うことができます。
- 構文:=SUMIF(範囲, 検索条件, [合計範囲])
  SUMIF関数に説明がありますので参照してください。
G3セルに =SUMIF(B3:B10,F3,D3:D10) と入力します。
複数の条件での合計はSUMIFS関数を使いますが、1つの条件でも使用できます。
- 構文:=SUMIFS(合計対象範囲,条件範囲,条件,...)
  SUMIFS関数に説明がありますので参照してください。
G3セルに =SUMIFS(D3:D10,B3:B10,F3) と入力します。
SUMIF関数とは引数の順番が違うので注意が必要です。

Filter関数を使って求める

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

SUMPRODUCT関数で求める

構文:=SUMPRODUCT(配列1,[配列2],[配列3],・・・)
SUMPRODUCT関数に説明がありますので参照してください。
G3セルに =SUMPRODUCT((B3:B10=F3)*(D3:D10)) と入力します。
SUMPRODUCT関数の計算過程は下図のような感じです。
(B3:B10=F3)の部分は論理値(TRUE/FALSE)を返すので、(D3:D10)との積にして数値の1または0を返すようにして、計算しています。
I列には =B3=$F3ドルが入力され、TRUE/FALSEが返されています。
K列では =D3*I3 と販売額を掛け合わせています。TRUE/FALSEは演算すると、ワークシートでは1/0として計算されます。
この結果の合計が計算結果として得られています。

配列数式で求める

Excel for Microsoft365,Excel2021以降の動的配列数式が使える場合は
G3セルに =SUM(IF(B3:B10=F3,D3:D10,0)) と入力します。
Excel2019以前のバージョンでは Ctrl + Shift + Enter (CSE) 数式とします。
=SUM(IF(B3:B10=F3,D3:D10,0)) と入力して、[Ctrl]+[Shift]+[Enter]で数式の入力を確定します。
数式は {=SUM(IF(B3:B10=F3,D3:D10,0))} となります。

(追記) (追記ここまで)

問題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関数で求める SUMPRODUCT関数で求める

DSUM関数で求める FILTER関数で求める

SUMIFS関数で求める

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

DSUM関数で求める

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

SUMPRODUCT関数で求める

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) と入力します。

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関数で合計しています。

(追記) (追記ここまで)

問題3の解答例 topへ

OR条件で求める問題です。

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関数で求める FILTER関数で求める

SUMPRODUCT関数で求める

SUMIF関数を使って求める

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

FILTER関数で求める

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

SUMPRODUCT関数で求める

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

問題4の解答例 topへ

B C D E F

2 年月日商品名販売額 5月の総販売額

3 2025年4月2日りんご 52,000 145,000

4 2025年4月3日りんご 65,000

5 2025年4月4日みかん 78,000

6 2025年5月7日みかん 43,000

7 2025年5月8日りんご 45,000

8 2025年5月9日バナナ 57,000

9 2025年6月1日バナナ 49,000

10 2025年6月2日りんご 80,000

SUMIFS関数で求める SUMIF関数で求める

FILTER関数で求める SUMPRODUCT関数で求める

SUMIFS関数で求める

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

SUMIF関数で求める

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

FILTER関数で求める

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

SUMPRODUCT関数で求める

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

=SUMPRODUCT((TEXT(B3:B10,"yyyy/m")="2025/5")*D3:D10)
といった数式も考えられます。

(追記) (追記ここまで)

問題5の解答例 topへ

B C D E F G H I

2 年月日商品名販売額

3 2025年4月2日りんご 52,000 バナナみかんりんご

4 2025年4月3日りんご 65,000 4月 0 78,000 117,000

5 2025年4月4日みかん 78,000 5月 57,000 43,000 45,000

6 2025年5月7日みかん 43,000 6月 49,000 0 80,000

7 2025年5月8日りんご 45,000

8 2025年5月9日バナナ 57,000

9 2025年6月1日バナナ 49,000

10 2025年6月2日りんご 80,000

PIVOTBY関数で求める

Excel for Microsoft365ではPIVOTBY関数が使えるようになりました。
構文は下のように関数のヒントではまだ英文での表示になります。
=pivotby(row_fields,col_fields,values,function,
field_headers,row_total_depth,row_sort_order,col_total_depth,col_sort_order,filter_array,relative_to)
詳細な使い方は PIVOTBY関数でクロス集計表を作るをご覧ください。
PIVOTBY関数はピボットテーブルを関数で作成する感じです。
F3セルに =PIVOTBY(MONTH(B3:B10)&"月",C3:C10,D3:D10,SUM) と入力します。

FILTER関数で求める

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

SUMIFS関数で求める

G4セルに以下の数式を入力して、他のセルにコピーします。
=SUMIFS($D3ドル:$D10,ドル
$C3ドル:$C10,ドルG3,ドル
$B3ドル:$B10,ドル">="&(DATE(2025,LEFT($F4,1),1)),
$B3ドル:$B10,ドル"<"&(DATE(2025,LEFT($F4,1)+1,1)))
もしくは、G4:I6セルを選択して、数式バーに上記の数式を入力して、[Ctrl]+[Enter]で選択しているセルに同じ数式を入力します。
- F列には4月～6月と文字列が入力されているので、LEFT関数を使って 4～6 の文字列を取り出します。
  DATE関数ではこの文字列を数値とみなして計算してくれます(エラーになりません)。
  5月～7月は LEFT関数で求めた月に +1 としています。

SUMPRODUCT関数で求める

G4セルに以下の数式を入力して、他のセルにコピーします。
=SUMPRODUCT((TEXT($B3ドル:$B10,ドル"m月")=$F4)*($C3ドル:$C10ドル=G3ドル),$D3ドル:$D10ドル)

月と商品名の論理積と販売額とをSUMPRODUCT関数で掛け合わせて、合計しています。
下図は 4月*みかんの計算過程を示しています。

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

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

スポンサードリンク
(追記) (追記ここまで)

(追記) (追記ここまで)

Home|エクセル練習問題:目次|条件付きの合計の計算

PageViewCounter
Counter
Since2006/2/27