Excel業務カイゼンブログ

SUMPRODUCT関数について解説します。SUMPRODUCT関数は、掛け算（PRODUCT）の結果を合計（SUM）する関数です。この関数では範囲同士の掛け算ができます。複数の行を、それぞれ１行ずつ掛け算した値の総合計を求めます。

この表の合計とは、15000×2、35000×3、2500×4、50000×2、5000×1の計算結果を合計した値を求めます。これを実現する機能として、「配列数式」がありますが、配列数式はなじみがない人には少しハードルが高いです。

※配列数式の解説はこちら

その代替えとして、SUMPRODUCT関数を使うことができます。今回は、実際に操作するファイルがあります。こちらからダウンロードしてください。

SUMPRODUCT関数の基本

SUMPRODUCT関数の基本の書式は次の通りです。

この範囲をそれぞれ１行ずつ掛け算をし、掛け算したものを合計します。範囲は255個まで指定できます。範囲は縦１列でも横１行でもかまいません。どちらの場合でも、上のセルから、左のセルから１個目、2個目と順番に認識します。
ダウンロードしたファイルのステップ１シートをご覧ください。単価×販売数の合計額をセルG4に配列数式で求めてみましょう。まず、配列数式で求めてみます。配列数式では、範囲で計算できますので、次のような計算式となり、CtrlキーとShiftキーを押したままEnterキーを押して決定します。
=SUM(C4:C8*D4:D8)

すると、数式バー上では、計算式全体が｛ ｝で囲まれ、配列数式となり、掛け算の合計を求めます。

これと同じことをセルG7にSUMPRODUCT関数で計算してみましょう。計算式は次の通りで、配列数式ではないので、そのまま確定します。

今度は、３つの範囲で掛け算をしてみましょう。セルG13に配列数式で利益率を掛け合わせた利益額を計算してみます。次の計算式をCtrlキーとShiftキーを押したままEnterキーを押して決定します。

今度はセルG7にSUMPRODUCT関数で計算してみましょう。計算式は次の通りです。

3つ以上のケースでも、引数をどんどん足していくことで計算を繋いでいくことができます。

SUMPRODUCT関数のエラー

SUMPRODUCT関数では、普通の関数以上に気を付けることがあります。それぞれの範囲でセル数は同じにしないと#VALUEエラーになります。１つの範囲に２行以上かつ２列以上を指定した場合も縦方向の範囲なのか横方向の範囲なのか判断付かないために#VALUEエラーになります。このようなエラーが出たときは、使い方というよりも範囲の指定方法を見直しましょう。

SUMPRODUCT関数の応用

SUMPRODUCT関数は他の仕組みと組み合わせて使うと更に多くのことができます。今回は、論理式を組み合わせて考えてみましょう。
論理式とはIF関数と同じ考え方なのですが、Excelは「=C1>=3」という数式を扱えます。もしも、セルC1が3以上なら、真（TRUE）、そうでなければ偽（FALSE）という値を求めます。この数式を論理式と呼びます。
この答えのTRUEは数字にすると1で、FALSEは0です。ここで掛け算の仕組みをおさらいしましょう。掛け合わせる数字の中で0が一つでもあれば答えは0になること、1を掛けても数字は変わらないことを抑えておきます。判定して、その結果を0か1として、それを掛け合わせることで、ある条件に合ったものだけ掛け算して合計することができます。

ステップ２シートをご覧ください。上記の考え方を使った配列数式で販売数が３個以上の利益計をセルG4に求めてみましょう。計算式は次の通りで、CtrlキーとShiftキーを押したままEnterキーを押して決定します。

もしも、範囲の中の数字が3以上なら1、そうではなければ0となる論理式を計算式内に組み入れてあります。

同じことをセルG7にSUMPRODUCT関数で求めてみましょう。

もしも、範囲の中の数字が3以上なら1、そうではなければ0となる論理式の答えと個数の範囲との掛け算を計算式内に組み入れてあります。

まとめ

今回紹介した例では、SUMPRODUCT関数と配列数式では同じことができました。とても似た計算方法なので、どちらでもよいと思いがちですが、厳密にはできることとできないことがあります。

配列数式はまだ一般的ではないので、みんなが扱えるかというと難しいところがあるかもしれません。その点、SUMPRODUCT関数なら操作も簡単で、範囲もわかりやく入力できますが、配列数式と比べると例えば合計は求められますが平均や最大、最小値などは求めることができません。それぞれ特徴がありますので区別して使いましょう。ある条件に当てはまったもの合計であれば、どちらでも対応できますし、もっと簡単にSUMIF関数も使えます。使いやすい方を使いましょう。