SUMPRODUCT関数：配列の各要素の積の合計を取得する
SUMPRODUCT 関数は Excel で用意されている関数の一つで、引数に指定した複数の配列やセル範囲の対応する要素の積を計算しその合計を取得します。ここでは Excel における SUMPRODUCT 関数の使い方について解説します。
※ Excel の対応バージョン ： 365 web 2021 2019 2016 2013 2010 2007
(Last modified: )
目次
SUMPRODUCT関数の定義
SUMPRODUCT 関数は引数に指定した複数の配列やセル範囲の対応する要素の積を計算しその合計を取得します。
引数にはセル範囲または配列定数を指定します。引数に指定するセル範囲や配列は、行数と列数が同じ配列でなければなりません。( 1 つ目の配列が 2×3 の配列の場合は、 2 つ目以降の配列も 2×3 の配列でなければなりません)。
次のような 2×2 の 2 つの配列を SUMPRODUCT 関数の引数として指定すると、各配列の対応する要素の積を求め、その合計した数値が SUMPRODUCT 関数の戻り値として返されます。
SUMPRODUCT 関数では n×m のような行と列がそれぞれ複数の配列を引数に指定できますが、よく使われるのが次のような n×1 のような配列を引数に指定する場合です。
同じ行にある値を乗算した結果の合計を取得することができます。
例として 2 つの配列定数 {1,2;3,4} と {5,6;7,8} を引数に指定した場合、 SUMPRODUCT は 70 を返します。
=SUMPRODUCT({1,2;3,4},{5,6;7,8})
--> 70
これは 1*5 + 2*6 + 3*7 + 4*8 = 5 + 12 + 21 + 32 = 70 となるためです。
※ 配列定数の使い方については「配列定数の利用方法」を参照されてください。
SUMPRODUCT関数の使い方
それでは実際に SUMPRODUCT 関数を使ってみます。 Excel のシートに対象の数値を次のように入力しました。
結果を表示する D9 セルに次のように SUMPRODUCT 関数を入力します。 1 番目の引数にセル範囲 C3:C7 を指定し、 2 番目の引数にセル範囲 D3:D7 を指定しました。
=SUMPRODUCT(C3:C7,D3:D7)
Enter キーを押すと D9 セルには次のように表示されます。
引数に指定した 2 つのセル範囲のそれぞれ対応するセルに入力されている数値の積を計算し、その合計を取得した結果が表示されました。
SUMPRODUCT関数を使って条件に一致するデータの合計を取得する
SUMPRODUCT 関数を使用すると、指定した条件と一致するデータの合計を取得することができます。次のシートを見てください。
商品名と売上のデータが表示されています。この中から例えば "きゅうり" の売上の合計を取得してみます。結果を表示する C12 セルに次のような数式を入力しました。
=SUMPRODUCT(N(B3:B10="きゅうり"),C3:C10)
1 番目の配列として N(B3:B10="きゅうり") 、 2 番目の配列として C3:C10 を指定しています。 1 番目の配列では B3:B10="きゅうり" の部分で値が "きゅうり" であれば TRUE 、そうでなければ FALSE を取得します。そして N 関数を使って TRUE を 1 、 FALSE を 0 に変換します。これで 1 番目の配列は 1 か 0 の値が含まれる配列となりました。( N 関数を使う代わりに TRUE や FALSE に *1 しても 1 から 0 に変換できます)。
SUMPRODUCT 関数では 1 番目と 2 番目の配列の対応する値を乗算した結果を合計しますので、 "きゅうり" の売上は ×1 され、それ以外の売上は ×0 されるため、結果的に "きゅうり" の売上の合計だけを取得します。
Enter キーを押すと C12 セルには次のように表示されます。
このように SUMPRODUCT 関数を使用すると、指定した条件と一致するデータの合計を取得することができました。
SUMPRODUCT関数を使って複数の条件に一致するデータの合計を取得する
先ほどと基本的に同じですが複数の条件に一致するデータの合計を取得することができます。次のシートを見てください。
商品名と種類と売上のデータが表示されています。この中から例えば 商品名が "ラーメン" で種類が "塩" の売上の合計を取得してみます。結果を表示する D12 セルに次のような数式を入力しました。
=SUMPRODUCT((B3:B10="ラーメン")*(C3:C10="塩"),D3:D10)
1 番目の配列として (B3:B10="ラーメン")*(C3:C10="塩") 、 2 番目の配列として D3:D10 を指定しています。 1 番目の配列では (B3:B10="ラーメン") の部分で値が "ラーメン" であれば TRUE 、そうでなければ FALSE を取得します。また (C3:C10="塩") の部分で値が "塩" であれば TRUE 、そうでなければ FALSE を取得します。それぞれを * 演算子で乗算しますが TRUE*TRUE は 1 、それ以外の TRUE*FALSE や FALSE*FALSE は 0 となります。これで 1 番目の配列は 1 か 0 の値が含まれる配列となりました。(今回は論理値を乗算することで 1 か 0 に変換されているので N 関数は必要ありません)。
SUMPRODUCT 関数では 1 番目と 2 番目の配列の対応する値を乗算した結果を合計しますので、 "ラーメン" ＆ "塩" の売上は ×1 され、それ以外の売上は ×0 されるため、結果的に "ラーメン" & "塩" の売上の合計だけを取得します。
Enter キーを押すと D12 セルには次のように表示されます。
このように SUMPRODUCT 関数を使用すると、指定した複数の条件と一致するデータの合計を取得することができました。
SUMPRODUCT関数の便利な利用方法
SUMPRODUCT 関数の便利な利用方法を下記の記事で紹介しています。
-- --
Excel における SUMPRODUCT 関数の使い方について解説しました。
( Written by Tatsuo Ikura )
著者 / TATSUO IKURA
プログラミングや開発環境構築の解説サイトを運営しています。