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 SUMPRODUCT関数の使い方:Excel関数
Home »
エクセル関数一覧表 »
Excel関数の目次 »
SUMPRODUCT関数の使い方
更新:2025/2/15;作成:2005/12/2
サムプロダクト =SUMPRODUCT(配列1,[配列2],[配列3],・・・）
配列の対応する要素間の積をまず計算し、さらにその和を返します。 SUMPRODUCT関数で複数条件の件数をカウントしたり、合計を出すことができます。
SUMPRODUCT関数を用いて、1行/1列おきに計算することもできます。
SUMPRODUCT関数の計算概念 Topへ
SUMPRODUCT関数は引数の配列の対応する要素間の積をまず計算し、その計算結果の合計を求めます。
【問題】売上合計金額を求めなさい。
B
C
D
2
商品名
単価
個数
3
パソコン
198,000
10
4
プリンタ
37,000
5
5
モニター
58,000
10
6
7
合計金額
2,745,000
問題1の解答例
単価×個数 で金額をE列に求めますが、この問題では金額欄がありません。
=SUMPRODUCT(C3:C5,D3:D5) と入力します。
数式の計算過程の説明
Excel for Microsoft365,Excel2021以降のように Spillが使える場合
SUMPRODUCT関数を使い、複数条件でカウントする Topへ
【問題2】 商品名「みかん」 サイズ「S」の件数をカウントしなさい。
B
C
D
2
商品名
サイズ
件数
3
みかん
S
4
4
5
商品名
サイズ
6
みかん
S
7
みかん
M
8
みかん
S
9
バナナ
S
10
みかん
M
11
みかん
S
12
みかん
S
【問題2の解答例】
SUMPRODUCT関数では配列の計算ができるので、論理式*論理式でAND条件の件数をカウントします。=SUMPRODUCT( (B6:B12=B3) * (C6:C12=C3) ) と入力します。 =SUMPRODUCT( (B6:B12=B3) * (C6:C12=C3) ) の計算過程の概念
B3="みかん",C3="S"を数式に代入します。 =SUMPRODUCT({TRUE ;TRUE ;TRUE ;FALSE ;TRUE ;TRUE ;TRUE }TRUE ;FALSE ;TRUE ;TRUE ;FALSE ;TRUE ;TRUE })
配列内の各要素を順番にかけ合わせていきます。上の配列内の 赤*赤、緑*緑 ・・・といった具合になります。
={TRUE *TRUE ,TRUE *FALSE ,TRUE *TRUE ,FALSE *TRUE ,TRUE *FALSE ,TRUE *TRUE ,TRUE *TRUE }
=1+0+1+0+0+1+1
=4
この場合の配列要素は「TRUE*TRUE」のような論理積になっていますので、
表で示すと、下表の各行の計算結果を合計していることになります。
（B6:B12="みかん"）*(C6:C12="S"）の計算経過
各経過での計算結果
6
(B6="みかん"）*(C6="S")
TRUE*TRUE = 1
7
(B7="みかん"）*(C7="S")
TRUE*FALSE = 0
8
(B8="みかん"）*(C8="S")
TRUE*TRUE = 1
9
(B9="みかん"）*(C9="S")
FALSE*TRUE = 0
10
(B10="みかん"）*(C10="S")
TRUE*FALSE = 0
11
(B11="みかん"）*(C11="S")
TRUE*TRUE = 1
12
(B12="みかん"）*(C12="S")
TRUE*TRUE = 1
合計
4
=SUMPRODUCT(B6:B12="みかん",C6:C12="S" ) では計算できない理由
=SUMPRODUCT( B6:B12="みかん" , C6:C12="S" ) とすると「0」となり、計算できません。数値以外の配列要素は、0であると見なされる ためです。
この配列の要素は論理値（TRUE/FALSE)なので全て「0」と見なされる ので、計算結果は「0」となります。
B6:B12="みかん"
C6:C12="S"
SUMPRODUCT(B6:B12="みかん",C6:C12="S")
6
TRUE
TRUE
0 * 0 = 0
7
TRUE
FALSE
0 * 0 = 0
8
TRUE
TRUE
0 * 0 = 0
9
FALSE
TRUE
0 * 0 = 0
10
TRUE
FALSE
0 * 0 = 0
11
TRUE
TRUE
0 * 0 = 0
12
TRUE
TRUE
0 * 0 = 0
合計
0
なお、=SUMPRODUCT((B6:B12="みかん")*1 ,(C6:C12="S")*1 )として、論理値を数値に変えることで計算ができます。
TRUE*1=1、FALSE*1=0となるため、(B6:B12="みかん")*1の部分は{1;1;1;0;1;1;1}、(C6:C12="S")*1の部分は{1;0;1;1;0;1;1}といった配列になります。
(B6:B12="みかん")*1
(C6:C12="S")*1
SUMPRODUCT((B6:B12="みかん")*1,(C6:C12="S")*1)
6
1
1
1 * 1 = 1
7
1
0
1 * 0 = 0
8
1
1
1 * 1 = 1
9
0
1
0 * 1 = 0
10
1
0
1 * 0 = 0
11
1
1
1 * 1 = 1
12
1
1
1 * 1 = 1
合計
4
論理値の計算について
ExcelのワークシートではTRUE=1 FALSE=0 として計算されます。 逆に数値を論理値にするとき、0はFALSEとなりますが、1,2・・・とか0以外はTRUEと見なされます。
Excel for Microsoft365,Excel2021以降のように Spillが使える場合は、=SUM( (B6:B12=B3) * (C6:C12=C3) ) で求めることができます。
ちなみに、以前のバージョンでは、この数式を[Ctrl]+[Shift]+[Enter]で配列数式として入力します。
SUMPRODUCT関数で複数条件での合計を求める Topへ
【問題】 商品名「みかん」 サイズ「S」の金額を求めなさい。
B
C
D
2
商品名
サイズ
金額
3
みかん
S
6,400
4
5
商品名
サイズ
金額
6
みかん
S
1,000
7
みかん
M
1,200
8
みかん
S
2,100
9
バナナ
S
3,000
10
みかん
M
2,500
11
みかん
S
1,200
12
みかん
S
2,100
【解答例】
条件を数式に書き込む場合=SUMPRODUCT( (B6:B12= "みかん" )*(C6:C12= "S" ) ,(D6:D12))
条件がセルに入力されている場合：B3,C3セルを参照する場合=SUMPRODUCT( (B6:B12= B3 )*(C6:C12= C3 ) ,(D6:D12))
=SUMPRODUCT( (B6:B12= B3 )*(C6:C12= C3 )* (D6:D12)) でもOKです。
SUMPRODUCT関数での計算過程の説明
数式 『=SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"),(D6:D12))』を以下の表で分解してみます。
B列に「B6:B12="みかん"」を満足するセルはTRUE、満足しないセルはFALSE となります。
C列に「C6:C12="S"」を満足するセルはTRUE、満足しないセルはFALSE となります。
=SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"),(D6:D12))
B
C
D
E
2
商品名
サイズ
金額
3
みかん
S
6,400
4
5
商品名
サイズ
金額
6
TRUE
TRUE
1,000
(1*1)*1,000=1,000
7
TRUE
FALSE
1,200
(1*0)*1,200=0
8
TRUE
TRUE
2,100
(1*1)*2,100=2,100
9
FALSE
TRUE
3,000
(0*1)*3,000=0
10
TRUE
FALSE
2,500
(1*0)*2,500=0
11
TRUE
TRUE
1,200
(1*1)*1,200=1,200
12
TRUE
TRUE
2,100
(1*1)*2,100=2,100
13
合計→
6,400
(注意) SUMPRODUCT関数では、セル範囲を「A:A」のように列全体を指定した場合、エラーとなります。Excel for Microsoft365,Excel2021以降のように Spillが使える場合は、=SUM((B6:B12=B3)*(C6:C12=C3)*D6:D12) で求めることができます。
SUMPRODUCT関数で一行おきに合計する Topへ
【問題】 収入と支出が1行ごとに入力されています。収入と支出の金額を求めなさい。この例では、SUMIF関数で求めるのが簡単ですが、SUMPRODUCT関数の一例ということで書いています。
B
C
2
収入
2,000
3
支出
1,500
4
収入
2,500
5
支出
1,200
6
収入
3,700
7
支出
2,000
8
収入
1,400
9
支出
4,200
10
収入
3,500
11
支出
2,500
12
総収入
13,100
13
総支出
11,400
【解答例】
偶数は2で割ると余りが0である。奇数は2で割ると余りが1である。といった条件を利用します。 収入(偶数行)の合計
C12セル：=SUMPRODUCT((MOD(ROW (C2:C11),2)=0 )*C2:C11)ROW (C2:C11),2)=0 )の部分は論理値(TRUE/FALSE)が返されますので、(MOD(ROW (C2:C11),2)=0 )*C2:C11と積(掛け算)で求めています。
(注) C列に文字列が混ざって入力されているような場合、下のようなことが考えられます。
論理値が返る部分を数値にする。*1 ,C2:C11)
MOD(ROW(C2:C11)-1 ,2)として、対象セルの行番号を2で割った時に1が返るように小細工をします。 -1 ,2),C2:C11)
偶数のときMOD(ROW(C2:C11),2)は0となるので、-1とすることで1となります。逆の奇数なら-1で0となります。
支出(奇数行)の合計
C13セル：=SUMPRODUCT((MOD(ROW (C2:C11),2)=1 )*C2:C11)
Excel for Microsoft365,Excel2021以降のように Filter関数やSpillが使える場合は、=SUM( FILTER(C2:C11, MOD(ROW(C2:C11),2)=0 ) ) =SUM( FILTER(C2:C11, MOD(ROW(C2:C11),2)=1 ) ) で求めることができます。FILTER関数でデータを抽出する をご覧ください。
SUMPRODUCT関数を使い、複数条件でテータを取り出す Topへ
【問題】 B14セルに商品名、C14セルにメーカー名を入力したら、D14セルで型番を求めなさい。
B
C
D
2
商品名
メーカー名
型番
3
32インチ
SOMY
SO3201
4
32インチ
MINIBISI
MI3201
5
32インチ
TOBIBA
TO3201
6
32インチ
SAMISUN
SA3201
7
40インチ
SOMY
SO4002
8
40インチ
MINIBISI
MI4002
9
40インチ
TOBIBA
TO4002
10
40インチ
SAMISUN
SA4002
11
12
13
商品名
メーカー名
型番
14
40インチ
SOMY
SO4002
【解答例】
条件はSUMPRODUCT((B3:B10=B14)*(C3:C10=C14))で一致しているか否かを調べることができます。
D14セル =INDEX(D3:D10,SUMPRODUCT((B3:B10=B14)*(C3:C10=C14)*ROW(A1:A8)))
なお、MATCH関数とINDEX関数の組み合わせでも求めることができます。
掲示板で表の行の欠落と数式の間違いをご指摘いただきました。 (2013/8/6)
Excel for Microsoft365 (Office365)のように Filter関数やSpillが使える場合は、=FILTER(D3:D10,(B3:B10=B14)*(C3:C10=C14)) で求めることができます。FILTER関数でデータを抽出する をご覧ください。XLOOKUP関数で範囲や配列を検索する をご覧ください。
SUMPRODUCT関数で割り算、引き算、足し算の和を求める Topへ
SUMPRODUCT関数は配列の掛け算(積)の和を求める関数として有名ですが、割り算の和、引き算の和、足し算の和を求めることもできます。 割り算の和を求める/ C5:C9) としています。 引き算の和- C5:C9) としています。 足し算の和+ C4:C8) としています。
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数一覧表 »
SUMPRODUCT関数の使い方
PageViewCounter Since2006/2/27