Excelで掛け算した値を合計するSUMPRODUCT関数の使い方

「単価」や「数量」などの項目の和を求める計算では「SUM関数」を使用し、「単価」と「数量」の積を求めるような計算では「PRODUCT関数」を用います。

このようにそれぞれの計算を別途行うのではなく、「何らかの項目を掛け算して算出した複数の積を、合計するところまで一度に計算したい」ときに頼れる関数が、「SUMPRODUCT関数」です。

ここでは、SUMPRODUCT関数の意味や使い方、エラー対策など基本的な知識をご紹介します。

SUMPRODUCT関数とは

SUMPRODUCTとは、合計するという意味を持つSUMと、掛けるという意味を持つPRODUCTの2つから成る言葉です。

ExcelにおいてSUMPRODUCT関数とは、「引数として指定した配列の対応する要素同士の積を計算し、それぞれの積の結果の合計を算出する関数」です。式で示すと、「=SUMPRODUCT(配列1,配列2,……)」となります。

引数とは、「関数を用いた計算を行う際に指定する値(セルや数値)」です。また、配列とは「連続する複数のセルの集合」という意味です。

SUMPRODUCT関数の使い方

実際のSUMPRODUCT関数の使い方を、図を用いてご紹介していきます。

45_1

図1

上の図1の表は、SUMPRODUCT関数で、「売値/100g」「利益率」「仕入れ量」をそれぞれ計算して「利益額」を算出し、利益額の「全体合計」を導き出しています。

次に、具体的な計算式をひも解いていきます。

45_2

図2

図2では、SUMPRODUCT関数の式が数式バーに表示されています。

式は、①「売値/100g」(B列)、②「利益率」(C列)、③「仕入れ量」(D列)と3つの値があるので、「=SUMPRODUCT(B2:B5,C2:C5,D2:D5)」です。

計算の過程としては、「B2×C2×D2 , B3×C3×D3 , B4×C4×D4 , B5×C5×D5」です。「,」は「+」の意味なので、それぞれの利益額が最終的に合計されて全体合計が算出されます。

SUMPRODUCT関数で「エラー」が出るとき

SUMPRODUCT関数を使用しているときに、エラーになってしまい、「#VALUE!」が表示されるときがあります。

一般的に考えられる原因は、「引数に指定した配列の範囲が異なるため」です。

上記の図2で例を挙げると、「=SUMPRODUCT(B2:B5,C2:C6,D2:D8)」になってしまっているケースなどです。配列1は5行目まで参照していますが、配列2や配列3は6行目8行目まで参照してしまうため、行の範囲にズレが出てしまいます

このように、参照する列や行の範囲が引数に指定した配列同士で等しくなっていないエラーになります。

まとめ

SUM、PRODCUTと複数の関数によって複数の配列同士を計算するSUMPRODUCT関数は、Excelで処理負荷の高い関数と言えます。

例えば、表の列や行数が多く参照範囲の確認が億劫になり、「A1:A30」などピンポイントの値ではなく「A:A」など参照したい値がある列全体を範囲とします。すると、そもそも負荷の高い関数であることに加えて、ExcelがA列を大量に参照してしまうので、動作が重くなってしまいます

SUMPRODUCT関数の配列の箇所には、参照したい配列を正確に入力することがポイントです。

また、SUMPRODUCT関数は、応用次第でさまざまな使い方が可能です。応用方法の1つとして、「エクセルのSUMPRODUCT関数で複数条件を指定する方法」の記事も参照してみてください。


イベント