Introduction
Ver. all 2013 2016 2019 365
エクセルでのSUMPRODUCT関数の使い方です。
使い方の例を２つに分けて紹介します。
SUMPRODUCT関数・・・指定した範囲内を乗算した後、それらの合計を返す
=SUMPRODUCT（配列1,［配列2］,［配列3］）
- 配列1・・・計算対象の最初の配列を指定（必須）
- ［配列2］,［配列3］, … ・・・他の配列を指定（任意）
いろんな計算を「SUM」してくれる関数だにゃ
SUMPRODUCT関数の使い方①
SUMPRODUCT関数が、他の関数と異なる部分としては、引数として配列を使用することでしょう。
Excelでの「配列」とは、簡単に言うと一つの箱の中に、他の箱を用意することです。
下のようなイメージです。
とりあえず使ってみましょう。
まず、下の「A2」「B2」「C2」をSUMPRODUCT関数の引数として指定してみます。
関数を入れる「E2」をクリックして、［関数の挿入］ボタンをクリック。
［関数の分類］を［数学/三角］か［すべて表示］に切り替えて、SUMPRODUCT関数を選びます。
SUMPRODUCT関数・・・指定した範囲内を乗算した後、それらの合計を返す
=SUMPRODUCT（配列1,［配列2］,［配列3］）
- 配列1・・・計算対象の最初の配列を指定（必須）
- ［配列2］,［配列3］, … ・・・他の配列を指定（任意）
引数には、配列を指定します。
通常、配列は広い範囲を指定して使用するものなのですが、今回はあえて１セルづつ指定してみます。
結果は、「24」と表示されます。
これは、「2」×「3」×「4」の計算の結果です。
ん？
であれば、普通に掛け算を使用すれば良いのでは・・・。となりますね。
ということで、通常このような使い方はしません。
実際には広い範囲を指定して使用されます。
次は、SUMPRODUCT関数の引数にそれぞれ広い範囲を指定しました。
SUMPRODUCT関数に広い範囲を指定した場合は「配列」として扱われます。
配列とは、一つの範囲のそれぞれのデータを個別に箱に入れたものと想像してください。
今回の場合の関数の動きは下のようになります。
それぞれの行ごとに掛け算をして、最後にその結果を足し算する。
結果は下のように「24」と表示されます。
（1×2×3）+（1×2×3）+（1×2×3）+（1×2×3）= 6+6+6+6 = 24
「それぞれを掛け算した後に、足し算する」ということですね。
ちなみに、下のように引数（配列）を横に指定した場合は、
結果は「98」になります。
（1×1×1×1）+（2×2×2×2）+（3×3×3×3）= 1+16+ 81 = 98
SUMPRODUCT関数の使い方②
SUMPRODUCT関数は、それぞれの配列を乗算（掛け算）ではなく、除算（割り算）、加算（足し算）、減算（引き算）で使用することもできます。
その際は、
SUMPRODUCT（配列1,［配列2］,［配列3］）
上の「,」の部分を、割り算「/」、足し算「+」、引き算「-」に置き換えて使用します。
例えば、下のようにそれぞれの範囲を「-」で指定した場合は、
（1-2-3）+（1-2-3）+（1-2-3）+（1-2-3）＝ -4 + -4 + -4 + -4 ＝ -16
となります。
SUMPRODUCT関数の使用例
実際の使用例を紹介します。
例えば下の表。
「単価」×「数量」で「売上」を計算して、最後にそれらを、SUM関数を使用して「合計」として算出しています。
よくある形ですね。
もし、「売上」の場所は必要なく、全体の「合計」の値だけが欲しかった場合は、下のようにSUMPRODUCT関数を使用することで、
簡単に結果を出すことができます。「売上」の列が必要なくなりました。
また、条件を付けた計算も可能です。
下の表で、「店舗」が「新宿」で、「商品名」が「猫缶」の場所だけの「単価」×「数量」が欲しかったとします。
「店舗」は「B11」のセル参照を利用し、「商品名」の「猫缶」は直接、式の中に入力することにします。
入力する関数は下のようになります。
=SUMPRODUCT((B3:B8=B11)*(C3:C8=”猫缶”)*D3:D8*E3:E8)
数式バーに直接関数を入力しても良いですし、［関数の挿入］画面を使用する場合は、［配列1］の場所に式を全て入力します。
ポイントの１つ目は、条件を設定する場所は括弧で囲む必要がある事。
=SUMPRODUCT((B3:B8=B11)*(C3:C8=”猫缶”)*D3:D8*E3:E8)
ポイントの２つ目は、条件を掛け算で結ぶこと。
=SUMPRODUCT((B3:B8=B11)*(C3:C8=”猫缶”)*D3:D8*E3:E8)
これは、条件に当てはまる場合は「1」を返し、当てはまらない場合は「0」を返すためです。
そのため、掛け算の結果「1」の場所だけ結果が計算されることになります。
下のイメージを見ていただくと、「1」×「1」の場所だけ数値が残ることがイメージできると思います。
※「0」を一度でも掛け算した行は、計算結果が「0」になるということ。
また、計算をしたい場所（今回の場合は「単価」と「数量」）が掛け算でなかった場合は、それぞれの列に条件を指定する必要があります。
言葉で説明すると分かり難いですね。
例えば下の表の場合は、「D11」に「1月」から「２月」を減算（引き算）したものを表示したいのですが、
その式は下のようになります。
=SUMPRODUCT((B3:B8=B11)*(C3:C8=”猫缶”)*D3:D8-(B3:B8=B11)*(C3:C8=”猫缶”)*E3:E8)
「１月」「２月」それぞれの場所に、抽出する同じ条件を付けています。下の赤字の場所です。
=SUMPRODUCT((B3:B8=B11)*(C3:C8=”猫缶”)*D3:D8-(B3:B8=B11)*(C3:C8=”猫缶”)*E3:E8)
なぜ、掛け算ではない場合は、それぞれに条件を付ける必要があるかですが、下の図をご覧ください。
上の水色矢印は条件を満たしている場合ですが、その場所の計算は、
- 条件を最初だけ付けた場合・・・ 1 × 150 – 135 = -25
- 条件を両方付けた場合・・・ 1 × 150 – 1 × 135 = -25
この場合は、どちらも結果が同じで問題ありません。
しかし、下の条件を満たしていない緑色の場所ですが、
- 条件を最初だけ付けた場合・・・ 0 × 20 – 23 = -23
- 条件を両方付けた場合・・・ 0 × 20 – 0 × 20= 0
ということで、結果が異なります。最初だけ「0」を掛けた場合は、次の場所が「0-23」で計算されてしまうということです。
ややこしいですが、「掛け算以外を使用する場合は、全てに条件を付ける」と覚えておきましょう。
SUMPRODUCT関数を使用する際の注意点
SUMPRODUCT関数を使用する際に注意すべき点です。
- 引数となる配列は、行数と列数が等しくなる必要がある
簡単に言うと、関数で指定する複数の範囲は同じ行数、列数でないとエラーが表示されます。
下のように同じ範囲で設定します。
下のようにバラバラの行数になっている場合はエラーが表示されます。
もう一点。
- 数値以外は、「0」として扱われる
計算する範囲に文字列や空欄があった場合は、そのセルは「0」として扱われます。
まとめ
SUMPRODUCT関数は、乗算、除算、加算、減算など様々な計算結果を最後に合計してくれる関数です。
ポイントをまとめると下のようになります。
- それぞれを掛け算した後に、足し算する
- 引数の指定方法により、掛け算以外も可能
- 関数で指定する複数の範囲は同じ行数、列数でないとエラーが表示される
- 数値以外は、「0」として扱われる
SUMPRODUCTのSUMは合計する。PRODUCTには「産出する」という意味があります。
繋げると「合計」を「産出」するという感じ。
こんなイメージで覚えておくと良いのでは。
「SUM」を「PRODUCT」するにゃ