SUMPRODUCTの読み方と意味

SUMPRODUCTは、サムプロダクトと読みます。

SUMは総計・和を意味し、 PRODUCTは数学で「積（掛け算の答え）」を意味します。数値を掛け算し、その合計を求めます。
論理値（TRUE、FALSE）を利用して複数条件を指定し、合計を求めることや個数をカウントすることもできます。SUMIFS、COUNTIFS関数を使う場面での代用が可能な関数です。
仕組みが少し複雑のなので取っつきにくいかもしれませんが、いろいろと応用が利く関数で、覚えておくと便利です。

SUMPRODUCTの引数は「配列」

SUMPRODUCT関数は「数式」タブ→「関数ライブラリ」→「数学/三角」に格納されています。「数学/三角」のメニューを一番下までスクロールして出てきます。

SUMPRODUCTの関数の引数ダイアログボックスを開いてみましょう。
引数は全て「配列」です。エクセルにおける配列とは連なる複数のセル範囲です。SUMPRODUCTでは配列の中にある要素（セルの値）同士を掛け合わせることになります。

引数には行/列が同数の配列を指定する必要があります。

SUMPRODUCT関数の計算式　→　=SUMPRODUCT(配列1,配列2,配列3…)

SUMPRODUCT関数で複数条件のデータをカウントする

以下の名簿から「30代」の「女性」を抽出してカウントしてみましょう。

IF関数でお馴染みのTRUEとFALSEは真偽を判定する値で、条件に合致すれば「1」を、合致しなければ「0」を返します。

• 「30代」は(C2:C14=30)
  「30」なら「TRUE」の「1」、そうでなければ「FALSE」の「0」が返る
• 「女性」は(E2:E14=”女性”)
  「女性」なら「TRUE」の「1」、そうでなければ「FALSE」の「0」が返る

この２つの条件をアスタリスクで掛け算した式をSUMPRODUCT関数の第一引数「配列1」に入れます。
(C2:C14=30)*(E2:E14=”女性”)

条件を掛け算するのはどうしてでしょう？
「0」で掛け算すれば答えは「0」。つまり、２つの条件の内、どちらか条件に合致しない値があれば、その要素の積（掛け算の答え）は「0」になります。
条件が２つとも合う要素の積は「1」。この「1」の数がカウントされます。

SUMPRODUCTで複数条件を抽出して合計する

論理値（TRUE：トゥルー、FALSE：フォールス）を利用して、複数条件でデータを合計する方法です。

ヘアサロンのデータ表で「担当者が鈴木」「予約媒体が電話」「来店回数が５回以上」の３つの条件で料金を抽出して合計してみましょう。

• 「担当者が鈴木」は(B2:B15=”鈴木”)
  「鈴木」なら「TRUE」の「1」、そうでなければ「FALSE」の「0」が返る
• 「予約媒体が電話」は(C2:C15=”電話”)
  「電話」なら「TRUE」の「1」、そうでなければ「FALSE」の「0」が返る
• 「来店回数が５回以上」は(D2:D15=”５回以上”)
  「５回以上」なら「TRUE」の「1」、そうでなければ「FALSE」の「0」が返る

この３つの条件をアスタリスクで掛け算した式をSUMPRODUCT関数の第一引数「配列1」に入れます。
(B2:B15=”鈴木”)*(C2:C15=”電話”)*(D2:D15=”５回以上”)

３つの条件の中で一つでも条件に合致しない値があれば、その要素の積（掛け算の答え）は「0」になり、３条件が全て合う要素の積は「1」になります。。

「配列1」で抽出した「0」と「1」の各要素と「配列2」の金額が掛け算され、その積が足し算されます。
「0」と金額を掛け算すれば答えは「0」。
「1」と金額を掛け算すれば答えは金額の値がそのまま返ります。
３条件が全て合う要素の金額だけが抽出されて足し算されるわけです。

最終的な計算式はこうなります。
=SUMPRODUCT((B2:B15=”鈴木”)*(C2:C15=”電話”)*(D2:D15=”５回以上”),F2:F15)

この３条件が、～かつ～かつ～の「AND条件」だということは分かると思います。では、SUMPRODUCT関数で「OR条件」の指定はできるでしょうか？

SUMPRODUCTで「OR条件」を指定して計算する

SUMPRODUCT関数で「OR条件」を計算できます。「担当者が田中」「来店回数が２～４回」をOR条件で計算します。

前章でSUMPRODUCT関数の第一引数「配列1」に「AND条件」の数式を入れたように、(B2:B15=”鈴木”)+(B2:B15=”２～４回”)と入力します。ポイントは両方の条件を和を求める演算子「+」で足し算すること。「配列2」は前章と同じに金額のセル範囲を指定します。

数式は=SUMPRODUCT((B2:B15=”田中”)+(C2:C15=”２～４回目”),E2:E15)となります。
ここから返る値は、両方の条件を満たす＝「2」、片方だけの条件を満たす＝「1」、条件に合致しない＝「0」です。「2」の要素の金額との積は倍になり、計算が合いません。

そこで、もう一つSUMPRODUCT関数の数式を作ります。

上記と同じ条件をアスタリスクで掛け算した式をSUMPRODUCT関数の第一引数「配列1」に入れます。「配列2」はORの式と同じ。

両方の条件を満たす場合だけ「1」が返り、その他は全て「0」になります。OR条件の式は両方の条件を満たす＝「2」ですから、ORの計算式からANDの計算式を引き算すればOK。

最終的な式は=SUMPRODUCT((B2:B15=”田中”)+(C2:C15=”２～４回目”),E2:E15)–SUMPRODUCT((B2:B15=”田中”)*(C2:C15=”２～４回目”),E2:E15)です。

SUMPRODUCTとISEVEN・ISODD（またはMOD）・ROWを組み合わせて一行おきのデータを合計

総合点の数値が「男性」「女性」一行おきに並べられた表で、男性の合計、女性の合計をそれぞれ集計します。

ISEVENとISODDは偶数と奇数を判断して論理値を返す関数です。
この関数に行番号を求めるROW関数を入れ子して奇数行・偶数行をTRUE「1」とFALSE「0」に振り分けます。

「男性」のデータは偶数行にあるのでISEVEN関数を使います。ISEVENの引数は「数値」だけ。ここに行番号を求めるROW(A2:A11)を入力し、ISEVEN(ROW(A2:A11))とします。
この式を更にSUMPRODUCTの「配列1」に入れ子して「総合点」のセル範囲と掛け合わせます。
=SUMPRODUCT(ISEVEN(ROW(A2:A11))*B2:B11)

「男性」の数式をコピーして「女性」の集計セルにペーストし、「ISEVEN」を「ISODD」に書き換えればOK。

この計算はSUMPRODUCTとMOD、ROWの組み合わせでもできます。行番号を「2」で割り算して余りが「0」なら偶数行、「1」なら奇数行となります。
「男性」の集計セルに入れる数式：　=SUMPRODUCT((MOD(ROW(A2:A11),2)=0)*$B$2:$B$11)
「女性」の集計セルに入れる数式：　=SUMPRODUCT((MOD(ROW(B2:B11),2)=1)*$B$2:$B$11)

SUMPRODUCTとSUMを組み合わせて加重平均を求める

「加重平均」とはデータごとの重みを加えて算出する平均値のことです。
単に加算して個数で割って出した平均値は実態に則したデータとは言えません。これを、より有用な数値に補正するための計算を行います。

A・B・Cの商品の価格と販売個数のデータから加重平均を割り出してみましょう。

商品A・B・Cの単純な平均価格（=AVERAGE(B2:B4)）は以下の通りです。

この値には販売された数量が考慮されていません。商品Cが他の商品の５倍前後売れている事実を「重み」として加えた平均価格「加重平均」を計算します。

まず、SUMPRODUCTの基本的な計算で「販売価格」×「販売数」を求めて加算します。

このSUMPRODUCTの数式を「販売総数」で割り算します。

より実態を反映させた加重平均が計算されました。
販売数の多い商品Cの価格に近い値になっていますね。

SUMPRODUCTとCOUNTIFを組み合わせて重複データを１件にまとめてカウント

重複するデータを調べるだけなら、COUNTIF関数で簡単に求められます。
下図の名簿から重複する名前を割り出すために、=COUNTIF(A2:A16,A2:A16)という数式を入れました。

最初のセルの「3」は同じ名前が３つあるということで、この重複する３セルには全て「3」が表示されています。これを「1」とカウントして、名簿の実際の人数を求めます。
（同姓同名はここでは考慮に入れていません ^ ^;）

そこで、=COUNTIF(A2:A16,A2:A16)でカウントされた数値の逆数を求め、それをSUMPRODUCTで足し算します。
「3」の逆数は「3分の1」、「2」の逆数は「2分の1」、「1」の逆数は「1」。つまり、重複が３つなら「3分の1」×3で「1」、重複が２つなら「2分の1」×2で「1」、重複しないデータもそのまま「1」になります。これを足し算すれば名簿の正確な人数がカウントできます。

逆数は、1/COUNTIF(A2:A16,A2:A16)で計算できるので、これをSUMPRODUCTの第一引数に入れればOK。
最終的な書式は「=SUMPRODUCT(1/COUNTIF(A2:A16,A2:A16))」です。

SUMPRODUCT関数で行列の交わるクロス集計表を作る

売上を記録した表データから、担当者ごと、商品ごとに売上金額を集計するクロス表を作ります。（ピボットテーブルで作れば簡単ですが、SUMPRODUCT関数の使い方を知る意味で役立ちそうなので解説しておきます）

• 「担当者」のセル範囲で「「担当者A」なら」という条件を　($A$2:$A$12=$G2)　とします
• 「商品」のセル範囲で「「WEB制作」なら」という条件を　($B$2:$B$12=H$1)　とします

縦の見出しに「担当者」の名前を並べ、横の見出しに「商品」の名前を並べたクロス集計表の枠組みを作ります。

最初の集計セルに上記の2つの条件をアスタリスクで掛け算させた式($A$2:$A$12=$G2)*($B$2:$B$12=H$1)をSUMPRODUCT関数の引数「配列1」に入力し、「配列2」に元表の金額のセル範囲を指定します。

「配列1」の「担当者」「商品」のセル範囲と「配列2」の金額のセル範囲はF4キーで絶対参照に、イコール「=」の後の「担当者A」のセル番地は列側を固定、「WEB制作」のセル番地は行側を固定させておきます。

縦横にオートフィルコピーしてクロス集計表の完成です。