1. Home » 
2. エクセル関数一覧表 » 
3. Excel関数の目次 » 
4. SUMPRODUCT関数の使い方

SUMPRODUCT関数の使い方:Excel関数

2021/11/29

　サムプロダクト
=SUMPRODUCT(配列1,[配列2],[配列3],・・・）

• 配列の対応する要素間の積をまず計算し、さらにその和を返します。
  ただし、SUMPRODUCT関数は数値以外の配列要素は、0であると見なされます。
• 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関数を使用して、一度に計算結果を求めることができます。
  D7セルに　=SUMPRODUCT(C3:C5,D3:D5)　と入力します。
  
  • 数式の計算過程の説明
    =SUMPRODUCT(C3:C5,D3:D5)
    =SUMPRODUCT({198000;37000;58000},{10;5;10})
    ={198000*10,37000*5,58000*10}　を合計する
    =2,745,000
• Excel for Microsoft365 (Office365)のように Spillが使える場合は、=SUM(C3:C5*D3:D5) で求めることができます。
  

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条件の件数をカウントします。
  (論理積には論理演算子　 * (アスタリスク)　を使います)
  D3セルに　=SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"))　と入力します。
  
• =SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"))の計算過程の概念
  • =SUMPRODUCT({(B6="みかん"）;(B7="みかん"）;(B8="みかん"）;(B9="みかん"）;(B10="みかん"）;(B11="みかん"）;(B12="みかん")}
    *{(C6="S");(C7="S");(C8="S");(C9="S");(C10="S");(C11="S");(C12="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」のような論理積になっていますので、
    論理値の積はExcelのワークシートではTRUE*TRUE=1*1=1　となり、それ以外の積は「0」となります。(FALSE=0なので)
    なお、この数式は配列が1個なのでSUM関数の配列式でも同じ結果を得ることができます。
    {=SUM((B6:B12="みかん")*(C6:C12="S"))}　とShift+Ctrl+Enterで配列数式にします。
• 表で示すと、下表の各行の計算結果を合計していることになります。
  

  	（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」となり、計算できません。
  これは、SUMPRODUCT関数は数値以外の配列要素は、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　として計算されます。
    (ちなみに、VBAではTRUE=-1となっています。)
  • 逆に数値を論理値にするとき、0はFALSEとなりますが、1,2・・・とか0以外はTRUEと見なされます。
• Excel for Microsoft365 (Office365)のように 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

【解答例】

• 条件を数式に書き込む場合
  D3セル　 =SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"),(D6:D12))
• 条件がセルに入力されている場合：B3,C3セルを参照する場合
  D3セル　 =SUMPRODUCT((B6:B12=B3)*(C6:C12=C3),(D6:D12))
  

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))
  =SUMPRODUCT(B列*C列,D列)
  Excelの関数ではTRUE=1　FALSE=0　として計算しますので、
  E列の計算結果を合計した結果が得られます。

• 	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」のように列全体を指定した場合、エラーとなります。
  「A1:A65536」のようにセル範囲を指定します。
• Excel for Microsoft365 (Office365)のように 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である。といった条件を利用します。
  この例では対象セルの行番号を2で割った時の余りが0なら偶数というわけです。
• 収入(偶数行)の合計
  • C12セル：=SUMPRODUCT((MOD(ROW(C2:C11),2)=0)*C2:C11)
    (MOD(ROW(C2:C11),2)=0)の部分は論理値(TRUE/FALSE)が返されますので、(MOD(ROW(C2:C11),2)=0)*C2:C11と積(掛け算)で求めています。
  • (注)C列に文字列が混ざって入力されているような場合、下のようなことが考えられます。
    • 論理値が返る部分を数値にする。
      =SUMPRODUCT((MOD(ROW(C2:C11),2)=0)*1,C2:C11)
    • MOD(ROW(C2:C11)-1,2)として、対象セルの行番号を2で割った時に1が返るように小細工をします。
      =SUMPRODUCT(MOD(ROW(C2:C11)-1,2),C2:C11)
      • 偶数のときMOD(ROW(C2:C11),2)は0となるので、-1とすることで1となります。逆の奇数なら-1で0となります。
• 支出(奇数行)の合計
  • C13セル：=SUMPRODUCT((MOD(ROW(C2:C11),2)=1)*C2:C11)
    または、 =SUMPRODUCT(MOD(ROW(C2:C11),2),C2:C11)
• Excel for Microsoft365 (Office365)のように Filter関数やSpillが使える場合は、
  =SUM(FILTER(C2:C11,MOD(ROW(C2:C11),2)=0))
  =SUM(FILTER(C2:C11,MOD(ROW(C2:C11),2)=1)) で求めることができます。
  FILTER関数の使い方は 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))で一致しているか否かを調べることができます。
  両者が一致したときのセル位置がわかれば、INDEX関数で求めることができますので、*ROW(A1:A8)を加えれば求めることができます。
  • D14セル　=INDEX(D3:D10,SUMPRODUCT((B3:B10=B14)*(C3:C10=C14)*ROW(A1:A8)))
  • なお、MATCH関数とINDEX関数の組み合わせでも求めることができます。
    D14セル　=INDEX(D3:D10,MATCH(B14&C14,INDEX(B3:B10&C3:C10,),0))
  • 掲示板で表の行の欠落と数式の間違いをご指摘いただきました。
    表の欠落の修正および数式中のB13をB14に,C13をC14に修正しました。
    ご指摘、ありがとうございました。(2013/8/6)
• 
• Excel for Microsoft365 (Office365)のように Filter関数やSpillが使える場合は、
  =FILTER(D3:D10,(B3:B10=B14)*(C3:C10=C14)) で求めることができます。
  FILTER関数の使い方は FILTER関数でデータを抽出する　をご覧ください。
  
  
  XLOOKUP関数を使って求めることもできます。
  =XLOOKUP(B14&C14,B3:B10&C3:C10,D3:D10)
  XLOOKUP関数の使い方は XLOOKUP関数で範囲や配列を検索する　をご覧ください。
  

SUMPRODUCT関数で割り算、引き算、足し算の和を求める　　　　Topへ

• SUMPRODUCT関数は配列の掛け算(積)の和を求める関数として有名ですが、割り算の和、引き算の和、足し算の和を求めることもできます。
  数式は配列引数を区切るコンマを、必要な算術演算子 (*、/、+、-) に置き換えます。
• 割り算の和を求める
  D3セルの数式は　=SUMPRODUCT(B4:B8/C4:C8)　としています。
  各配列の要素の割り算の結果をすべて足した値(和)が表示されています。
  
• 引き算の和
  D3セルの数式は　=SUMPRODUCT(B4:B8-C4:C8)　としています。
  各配列の要素の引き算の結果をすべて足した値(和)が表示されています。
  
• 足し算の和
  D3セルの数式は　=SUMPRODUCT(B4:B8+C4:C8)　としています。
  各配列の要素の足し算の結果をすべて足した値(和)が表示されています。
  

スポンサードリンク

PageViewCounter

Since2006/2/27