複数条件での絞り込み集計

「ある条件でリストから値を集計する」にはSUMIF関数を使用しますが、残念ながら、このSUMIF関数は「ひとつの条件」による集計しかできません。 「ある条件に該当し、さらに別の条件にも該当する値を集計する」のように集計する値を複数の条件によって絞り込んでいく場合には通常データベース関数としてDSUM関数が用意されていますが、Criteriaやフィールドの作成が少しわずらわしいですし、この設定のため計算のためだけにいくつかのセル範囲を使用してしまうので敬遠する人が多いようです。
このようなクロス計算には「ピボットテーブル」を使用したり、「オートフィルタ」とSUBTOTAL関数の組み合わせが一般的ですが、独自のレイアウトに集計数値だけを織り込みたい場合に関数で数値を求める場合もまた考えられます。
そこで、DSUM関数を使用しない場合の例をご紹介します。

SUMPRODUCT関数を使用した例

「積和」を求めるSUMPRODUCT関数を利用して目的の数値を集計することができます。
ここではその例と計算の原理を紹介します。
次の表を例に見てみます。


例

	A	B	C	D
1	売上月度	店舗	品目	売上金額
2	4	A	あ	22300
3	4	B	あ	18700
4	4	C	あ	14500
5	4	A	い	17950
6	5	B	あ	44000
7	5	C	い	35000
8	5	A	う	12000
9	5	B	い	13400

◆計算式の例

上記の表から「B店舗」の「あ」品目の売上集計を求める場合には次のような数式が考えられます。
=SUMPRODUCT((B2:B9="B")*1,(C2:C9="あ")*1,D2:D9)
この式は上記の表に当てはめた場合、「62700」が結果としてかえります。

◆計算の原理

SUMPRODUCT関数＝積和の計算

SUMPRODUCT関数は「積和」を求める関数です。 「積和」とは 「配列の対応する要素どうしを掛け算し、さらにそれを合計」することです。 次のような計算の事を指します。

SUMPRODUCT({3,9},{2,3})
=　3*2　+　9*3
=　33

この例では数値の配列同士の積和を求めています。この場合、SUMPRODUCT関数の引数{3,9}、{2,3}は それぞれ定数による「配列（行列）」です。 {3,9}を第一配列、{2,3}を第二配列とすると、
（第一配列の一項目目×第二配列の一項目目）　＋　（第一配列の二項目目×第二配列の二項目目）
と言う計算をしています。このように、SUMPRODUCT関数は本来は「数学関数」に分類され、 「数値の行列の積和を求める」ためのものですが、「論理値の配列」も計算に使用できるため 「複数条件によるカウント・集計」に応用することが出来ます。

論理式

セルB1に「=A1=1」と入力してみます。 A1が「1」であれば「TRUE」、「1」でなければ「FALSE」がB1の数式の結果としてセルに表示されます。 このように命題に対して「TRUE（真）」「FALSE（偽）」の結果が返ってくる式を「論理式」と言います （条件判断をする式なので「条件式」と呼ぶ場合もあり）。 論理式から導かれる「TRUE」「FALSE」の結果を「論理値」といいます。 SUMPRODUCT関数で「複数条件によるカウント・集計」を行う場合は「論理値の配列」を利用します。

論理値の配列

論理値の配列とはどういうものか、実際に見てみましょう。 下記の表を例とします。 E列には各行のB列のセルが"B"という店舗名かどうかを判断する論理式が入力されています。

【例１−１】

	A	B	C	D	E
1	売上月度	店舗	品目	売上金額	店舗の照合
2	4	A	あ	22300	=B2="B"
3	4	B	あ	18700	=B3="B"
4	4	C	あ	14500	=B4="B"
5	4	A	い	17950	=B5="B"
6	5	B	あ	44000	=B6="B"
7	5	C	い	35000	=B7="B"
8	5	A	う	12000	=B8="B"
9	5	B	い	13400	=B9="B"

計算結果は下記のようにB列が"B"である行には「TRUE」、 それ以外のものには「FALSE」が返ります。

【例１−２】

	A	B	C	D	E
1	売上月度	店舗	品目	売上金額	店舗の照合
2	4	A	あ	22300	FALSE
3	4	B	あ	18700	TRUE
4	4	C	あ	14500	FALSE
5	4	A	い	17950	FALSE
6	5	B	あ	44000	TRUE
7	5	C	い	35000	FALSE
8	5	A	う	12000	FALSE
9	5	B	い	13400	TRUE

では、試しに、E2へ「=B2:B9="B"」と入力してみてください。 その結果はE2に入力している限りでは「FALSE」になります。

【例１−３】

	A	B	C	D	E
1	売上月度	店舗	品目	売上金額	店舗の照合
2	4	A	あ	22300	=B2:B9="B"
3	4	B	あ	18700
4	4	C	あ	14500
5	4	A	い	17950
6	5	B	あ	44000
7	5	C	い	35000
8	5	A	う	12000
9	5	B	い	13400

この状態でセルE2を選択し、数式バーに数式を表示します。 数式バー内でマウスの左ボタンを押しながらドラッグするなどして数式を範囲選択し、 数式の文字列を反転状態にします。

【数式バー１】

×ν〓	=B2:B9="B"

この状態で　　F9を押してみると、 範囲選択した部分の計算結果が下記のように表示されます。


【数式バー２】

×ν〓	={FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

さて、上記【数式バー２】で反転表示された部分と【例１−２】の表のE列の結果とを見比べてください。 TRUE、FALSEの登場する順番は同じになっていますね？ このようにセルは結果を一つだけ返すことが出来ますが、その内部では複数の計算結果を持つことが出来ます。 「{}」で囲まれた部分が「配列」と呼ばれる形でセル内で計算された結果、「論理値の配列」です。

作業セルを使って計算

作業セルを使って目的の計算をしてみると、SUMPRODUCT関数がどのような計算をしているかが、 わかりやすいかもしれません。 次の表のE列が「(B2:B9="B")*1」を、F列が「(C2:C9="あ")*1」をそれぞれ展開したものです。

【例２−１】

	B	C	D	E	F	G
1	店舗	品目	売上金額	第一配列	第二配列	それぞれの 項目の積
2	A	あ	22300	=(B2="B")*1	=(C2="あ")*1	=E2*F2*D2
3	B	あ	18700	=(B3="B")*1	=(C3="あ")*1	=E3*F3*D3
4	C	あ	14500	=(B4="B")*1	=(C4="あ")*1	=E4*F4*D4
5	A	い	17950	=(B5="B")*1	=(C5="あ")*1	=E5*F5*D5
6	B	あ	44000	=(B6="B")*1	=(C6="あ")*1	=E6*F6*D6
7	C	い	35000	=(B7="B")*1	=(C7="あ")*1	=E7*F7*D7
8	A	う	12000	=(B8="B")*1	=(C8="あ")*1	=E8*F8*D8
9	B	い	13400	=(B9="B")*1	=(C9="あ")*1	=E9*F9*D9
10						=SUM(G2:G9)

(B2:B9="B")*1	[B2:B9の値が"B"であるかどうか]
(C2:C9="あ")*1	[C2:C9の値が"あ"であるかどうか]

(B2:B9="B")、(C2:C9="あ")の配列はそれぞれ論理値（TRUEまたはFALSE）を返す配列です。 論理値に「１」を乗算することでそれぞれ次のように数値化できます。

TRUE　*　1	=　1
FALSE　*　1	=　0

G列で三つの配列のそれぞれの積を計算し、G10セルでG列の積を合計しています。 つまり、「積和」です。 実際にセルに返される結果は次のようになります。

【例２−２】

	B	C	D	E	F	G
1	店舗	品目	売上金額	第一配列	第二配列	それぞれの 項目の積
2	A	あ	22300	0	1	0
3	B	あ	18700	1	1	18700
4	C	あ	14500	0	1	0
5	A	い	17950	0	0	0
6	B	あ	44000	1	1	44000
7	C	い	35000	0	0	0
8	A	う	12000	0	0	0
9	B	い	13400	1	0	0
10						62700

【数式バー３】

×ν〓	=SUMPRODUCT( {0;1;0;0;1;0;0;1}, {1;1;1;0;1;0;0;0}, {22300;18700;14500;17950;44000;35000;35000;12000;13400})

このように、「B?="B"」「C?="あ"」の両方が「TRUE」になるD列の値だけが集計される結果になります。 これまでの例ではわかりやすいように論理値に「×１」して数値に変換して計算している形としていましたが、 「論理値×論理値」「論理値×数値」は数式内では数値に変換されることを利用して下記の式に書き換えても 同じ計算することが出来ます。なれている方はこちらの書き方をすることが多いようです。

=SUMPRODUCT((B2:B9="B")*(C2:C9="あ")*(D2:D9))

関数ヘルプ
SUMPRODUCT関数(HINT)


▲PAGETOP

配列数式を使用して集計

「配列」を計算する「配列数式」を利用して目的の数値を集計することができます。
ここではその例と計算の原理を紹介します。
前段SUMPRODUCTと同様、次の表を例に見てみます。





例

	A	B	C	D
1	売上月度	店舗	品目	売上金額
2	4	A	あ	22300
3	4	B	あ	18700
4	4	C	あ	14500
5	4	A	い	17950
6	5	B	あ	44000
7	5	C	い	35000
8	5	A	う	12000
9	5	B	い	13400

◆計算式の例

上記の表から「B店舗」の「あ」品目の売上集計を求める場合には次のような数式が考えられます。
=SUM((B2:B9="B")*(C2:C9="あ")*(D2:D9))
上記の数式を数式バーへ入力した後、 SHIFT＋CTRL＋ENTER
または
=SUM(IF((B2:B9="B")*(C2:C9="あ"),D2:D9))
上記の数式を数式バーへ入力した後、 SHIFT＋CTRL＋ENTER
このように入力した数式を SHIFT＋CTRL＋ENTERで確定した場合、数式バーには

【数式バー３】
×ν〓	{=SUM((B2:B9="B")*(C2:C9="あ")*(D2:D9))}
【数式バー４】
×ν〓	{=SUM(IF((B2:B9="B")*(C2:C9="あ"),D2:D9))}


のように中括弧で囲まれた状態で表示されます。これを配列数式（行列数式）と呼びます。 これら二つの式は上記の表に当てはめた場合、「62700」が結果としてかえります。

◆計算の原理

{=SUM((B2:B9="B")*(C2:C9="あ")*(D2:D9))}

こちらの数式は原理的にはSUMPRODUCT関数とほとんど同様の処理をしています。
つまり、次の三つの配列の「積和」を求めています。

(B2:B9="B")

[B2:B9の値が"B"であるかどうか]

(C2:C9="あ")

[C2:C9の値が"あ"であるかどうか]

D2:D9

[D2:D9の値の配列]

(B2:B9="B")、(C2:C9="あ")はそれぞれ論理値（TRUEまたはFALSE）の配列を返します。 ふたつのの配列を乗算することで「1」または「0」の一つの数値配列になり、 その「1」となる項目に対応するD2:D9の値がSUM関数で集計されます。 配列をシートへ展開した状況はSUMPRODUCT関数の場合と同様で、次のようになります。

	B	C	D	E	F	G
1	店舗	品目	売上金額	第一配列	第二配列	それぞれの項目の積
2	A	あ	22300	=(B2="B")	=(C2="あ")	=E2*F2*D2
3	B	あ	18700	=(B3="B")	=(C3="あ")	=E3*F3*D3
4	C	あ	14500	=(B4="B")	=(C4="あ")	=E4*F4*D4
5	A	い	17950	=(B5="B")	=(C5="あ")	=E5*F5*D5
6	B	あ	44000	=(B6="B")	=(C6="あ")	=E6*F6*D6
7	C	い	35000	=(B7="B")	=(C7="あ")	=E7*F7*D7
8	A	う	12000	=(B8="B")	=(C8="あ")	=E8*F8*D8
9	B	い	13400	=(B9="B")	=(C9="あ")	=E9*F9*D9
10						=SUM(G2:G9)
実際にセルに返される値は次のようになります。
	B	C	D	E	F	G
1	店舗	品目	売上金額	第一配列	第二配列	それぞれの項目の積
2	A	あ	22300	FALSE	TRUE	0
3	B	あ	18700	TRUE	TRUE	18700
4	C	あ	14500	FALSE	TRUE	0
5	A	い	17950	FALSE	FALSE	0
6	B	あ	44000	TRUE	TRUE	44000
7	C	い	35000	FALSE	FALSE	0
8	A	う	12000	FALSE	FALSE	0
9	B	い	13400	TRUE	FALSE	0
10						62700

配列数式はこのような計算を一つの数式中で実行している数式のことを言います。

{=SUM(IF((B2:B9="B")*(C2:C9="あ"),D2:D9))}

IF関数の書式からすると

{=SUM(IF((B2:B9="B")*(C2:C9="あ"),D2:D9,0))}

とするべきですが、IF関数の部分の「偽の時の処理」が省略されています。 IF関数の「条件」、「真の時の処理」の配列の内容をみてみると、次のようになります。

【数式バー３】

×ν〓	=SUM(IF( {0;1;0;0;1;0;0;0}, {22300;18700;14500;17950;44000;35000;35000;12000;13400})

この部分はIF関数の条件式なので、「0」の項目はFALSE、「1」の項目はTRUEとみなされ、D2:D9の項目で2項目目、5項目目がSUM関数の集計の対象となります。実際には、IF関数の部分は次のような配列になるので、この配列がSUM関数で集計されることになります。

【数式バー３】

×ν〓	=SUM({FALSE;18700;FALSE;FALSE;44000;FALSE;FALSE;FALSE})

関数ヘルプ
SUM関数(HINT)
IF関数(HINT)


　▲PAGETOP

---

≪前のページへ　　 困ったときのお部屋目次へ　　 次のページへ≫

---