Excel(エクセル)基本講座：SUMPRODUCT関数

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

• 配列の対応する要素間の積をまず計算し、さらにその和を返します。
  ただし、SUMPRODUCT関数は数値以外の配列要素は、0であると見なされます。
• SUMPRODUCT関数で複数条件の件数をカウントしたり、合計を出すことができます。
• SUMPRODUCT関数を用いて、1行/1列おきに計算することもできます。

配列の対応する要素間の積をまず計算し、さらにその合計を求める

• 【問題】売上合計金額を求めなさい。

  	B	C	D
  2	商品名	単価	個数
  3	パソコン	198,000	10
  4	プリンタ	37,000	5
  5	モニター	58,000	10
  6
  7		合計金額	2,745,000

• 【解答例】
  • 単価×個数　で金額をE列に求めますが、この問題では金額欄がありません。
  • 配列間の積の和を求めるSUMPRODUCT関数を使用すると、一度に計算ができます。

  	B	C	D
  2	商品名	単価	個数
  3	パソコン	198,000	10
  4	プリンタ	37,000	5
  5	モニター	58,000	10
  6
  7		合計金額	=SUMPRODUCT(C3:C5,D3:D5)

複数条件でカウントする

• 【問題】 商品名「みかん」　サイズ「S」の件数をカウントしなさい。

  • 	B	C	D
    2	商品名	サイズ	件数
    3	みかん	S	4
    4
    5	商品名	サイズ
    6	みかん	S
    7	みかん	M
    8	みかん	S
    9	バナナ	S
    10	みかん	M
    11	みかん	S
    12	みかん	S

• 【解答例】
  • SUMPRODUCT関数では配列の計算ができるので、論理式*論理式でAND条件の件数をカウントします。
  • =SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"))
    

    	（B6:B12="みかん"）*(C6:C12="S"）
    6	TRUE*TRUE = 1
    7	TRUE*FALSE = 0
    8	TRUE*TRUE = 1
    9	FALSE*TRUE = 0
    10	TRUE*FALSE = 0
    11	TRUE*TRUE = 1
    12	TRUE*TRUE = 1
    合計	4

    この場合の配列要素は「TRUE*TRUE」のような論理積になっていますので、
    論理値の積はExcelのワークシートではTRUE*TRUE=1　となり、それ以外は「0」となります。
    なお、この数式は配列が1個なのでSUM関数の配列式でも同じ結果を得ることができます。
    {=SUM((B6:B12="みかん")*(C6:C12="S"))}　とShift+Ctrl+Enterで配列数式にします。
    • =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と見なされます。

複数条件での合計を求める

• 【問題】 商品名「みかん」　サイズ「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))
  • 条件がセルに入力されている場合
    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」のようにセル範囲を指定します。

一行おきに合計する　　　　　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)

複数条件でテータを取り出す　　　　　Topへ

• 　【問題】 商品名とメーカー名を入力したら、型番を求めなさい。

  • 	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
    13	商品名	メーカー名	型番
    14	40インチ	SOMY	SO4002

• 【解答例】
  • 条件はSUMPRODUCT((B3:B10=B13)*(C3:C10=C13))で一致しているか否かを調べることができます。
    両者が一致したときのセル位置がわかれば、INDEX関数で求めることができますので、*ROW(A1:A8)を加えれば求めることができます。
    • D14セル　=INDEX(D3:D10,SUMPRODUCT((B3:B10=B13)*(C3:C10=C13)*ROW(A1:A8)))
  • なお、MATCH関数とINDEX関数の組み合わせでも求めることができます。
    • D14セル　=INDEX(D3:D10,MATCH(B13&C13,INDEX(B3:B10&C3:C10,),0))

よねさんのWordとExcelの小部屋｜Excel(エクセル)基本講座の総目次｜Excel(エクセル)関数一覧表｜SUMPRODUCT関数

PageViewCounter

Since2006/2/27