SUMPRODUCT関数の使い方|配列の積を合計する

Excel(エクセル)SUMPRODUCT関数の使い方|配列の積を合計する
Excel関数

Excel(エクセル)SUMPRODUCT関数の使い方|配列の積を合計する

使用関数:SUMPRODUCT

SUMPRODUCTとは?

SUMPRODUCTの読み方と意味

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

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

SUMPRODUCTの引数は「配列」

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

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

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

SUMPRODUCT関数の基本的な計算

まず、基本的なSUMPRODUCT関数の操作を説明します。
「仕入れ単価」「セット個数」「掛け率」3つの配列を掛け算して、その積を合計してみましょう。
「仕入れ単価」「セット個数」「掛け率」3つの配列を掛け算して、その積を合計

3つの配列の各要素同士が掛け算された「積」の合計が表示されました。
3つの配列の各要素同士が掛け算された「積」の合計が表示された

SUMPRODUCT関数の使い方

配列の積を合計するという本来の使い方の他に、 論理値(TRUE:トゥルー、FALSE:フォールス)を利用した複数条件の指定によってSUMIFS、COUNTIFSと同じ使い方ができます。
その場合は =SUMPRODUCT((セル範囲1=条件1)*(セル範囲2=”条件2″)) という書式になります。
(セル範囲1=条件1)*(セル範囲2=”条件2″)が丸ごと引数の「配列1」に入るのがポイントです。

論理値を利用したSUMPRODUCT関数で0になるエラー

(セル範囲1=条件1)*(セル範囲2=”条件2″)でなく、(セル範囲1=条件1),(セル範囲2=”条件2″)にしてしまうのはよくある間違いです。
「配列1」と「配列2」にそれぞれ入力して乗算すれば同じでは?と思うかもしれませんが、SUMPRODUCTは(セル範囲1=条件1)を数値とは認識せず、数値でない配列要素は全て「0」と見做します。
(セル範囲=条件)を四則演算で計算させて数値化することで論理値を利用した複数条件の指定ができます。

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

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

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

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

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

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

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

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

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

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

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

3つの条件の中で一つでも条件に合致しない値があれば、その要素の積(掛け算の答え)は「0」になり、3条件が全て合う要素の積は「1」になります。。
3条件のTRUEとFALSEから1と0が割り振られる

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

最終的な計算式はこうなります。
=SUMPRODUCT((B2:B15=”鈴木”)*(C2:C15=”電話”)*(D2:D15=”5回以上”),F2:F15)
SUMPRODUCT関数の複数条件の配列を掛け算して、その積を足し算する計算式と答え

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

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

SUMPRODUCT関数で「OR条件」を計算できます。「担当者が田中」「来店回数が2~4回」をOR条件で計算します。
SUMPRODUCT関数で「担当者が田中」「来店回数が2~4回」をOR条件にして計算

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

数式は=SUMPRODUCT((B2:B15=”田中”)+(C2:C15=”2~4回目”),E2:E15)となります。
ここから返る値は、両方の条件を満たす=「2」、片方だけの条件を満たす=「1」、条件に合致しない=「0」です。「2」の要素の金額との積は倍になり、計算が合いません。
SUMPRODUCT関数の第一引数「配列1」にOR条件を指定した数式を作る
そこで、もう一つSUMPRODUCT関数の数式を作ります。

上記と同じ条件をアスタリスクで掛け算した式をSUMPRODUCT関数の第一引数「配列1」に入れます。「配列2」はORの式と同じ。
AND条件をアスタリスクで掛け算した式をSUMPRODUCT関数の第一引数「配列1」に入力
両方の条件を満たす場合だけ「1」が返り、その他は全て「0」になります。OR条件の式は両方の条件を満たす=「2」ですから、ORの計算式からANDの計算式を引き算すればOK。

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

SUMPRODUCT関数の応用

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)
一行おきに並べられた表で、男性の合計をSUMPRODUCT+ISEVEN+ROWで集計

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

この計算は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の商品の価格と販売個数のデータ表

商品A・B・Cの単純な平均価格(=AVERAGE(B2:B4))は以下の通りです。
商品A・B・Cの単純な平均価格
この値には販売された数量が考慮されていません。商品Cが他の商品の5倍前後売れている事実を「重み」として加えた平均価格「加重平均」を計算します。

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

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

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

重複するデータを調べるだけなら、COUNTIF関数で簡単に求められます。
下図の名簿から重複する名前を割り出すために、=COUNTIF(A2:A16,A2:A16)という数式を入れました。
COUNTIFで名簿の重複する名前をカウント
最初のセルの「3」は同じ名前が3つあるということで、この重複する3セルには全て「3」が表示されています。これを「1」とカウントして、名簿の実際の人数を求めます。
(同姓同名はここでは考慮に入れていません ^ ^;)

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

逆数は、1/COUNTIF(A2:A16,A2:A16)で計算できるので、これをSUMPRODUCTの第一引数に入れればOK。
最終的な書式は「=SUMPRODUCT(1/COUNTIF(A2:A16,A2:A16))」です。
=SUMPRODUCT(1/COUNTIF(A2:A16,A2:A16)) 重複データを1件にまとめてカウント

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制作」のセル番地は行側を固定させておきます。
SUMPRODUCTの第一引数に($A$2:$A$12=$G2)*($B$2:$B$12=H$1)を入力

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

まとめ
  • SUMPRODUCTは配列の要素同士を掛け算して、その積を合計する関数です
  • SUMPRODUCT関数の引数は「配列1」「配列2」・・・
  • SUMPRODUCTの配列は行列が同数でなければなりません。行数と列数が違っているとエラー「#VALUE!」が返されます
  • SUMPRODUCT関数は論理値(TRUE、FALSE)を利用して複数条件を指定し、合計を求めることや個数をカウントすることもできます。SUMIFS、COUNTIFS関数の代用が可能です
  • 論理値を利用した数式ではSUMPRODUCTの「配列1」の中で(セル範囲1=条件1)*(セル範囲2=”条件2″)のように計算させて数値化させます。(セル範囲1=条件1),(セル範囲2=”条件2″)はエラーで、返される値が0になります
  • SUMPRODUCTとCOUNTIF、ROW、SUM、MODなど他の関数を組み合わせた使い方も多く、色々応用が利きます
タイトルとURLをコピーしました