Deprecated: The each() function is deprecated. This message will be suppressed on further calls in /home/zhenxiangba/zhenxiangba.com/public_html/phproxy-improved-master/index.php on line 456
条件付きの合計の計算:エクセル練習問題

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

スポンサードリンク

更新: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=$F$3　が入力され、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($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 とします。

SUMIFS関数で求める

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

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

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

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

スポンサードリンク

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

PageViewCounter
Counter
Since2006/2/27