Excel(エクセル)で指定した範囲を合計する|SUMPRODUCT関数の使い方

スポンサーリンク
スポンサーリンク
スポンサーリンク

Introduction

Ver. all 2013 2016 2019 365

エクセルでのSUMPRODUCT関数の使い方です。

使い方の例を2つに分けて紹介します。

 


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], … ・・・他の配列を指定(任意)

 

引数には、配列を指定します。

通常、配列は広い範囲を指定して使用するものなのですが、今回はあえて1セルづつ指定してみます。

結果は、「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]の場所に式を全て入力します。

ポイントの1つ目は、条件を設定する場所は括弧で囲む必要がある事。

=SUMPRODUCT((B3:B8=B11)*(C3:C8=”猫缶”)*D3:D8*E3:E8)

ポイントの2つ目は、条件を掛け算で結ぶこと。

=SUMPRODUCT((B3:B8=B11)*(C3:C8=”猫缶”)*D3:D8*E3:E8)

これは、条件に当てはまる場合は「1」を返し、当てはまらない場合は「0」を返すためです。

そのため、掛け算の結果「1」の場所だけ結果が計算されることになります。

下のイメージを見ていただくと、「1」×「1」の場所だけ数値が残ることがイメージできると思います。
※「0」を一度でも掛け算した行は、計算結果が「0」になるということ。

また、計算をしたい場所(今回の場合は「単価」と「数量」)が掛け算でなかった場合は、それぞれの列に条件を指定する必要があります。

言葉で説明すると分かり難いですね。

例えば下の表の場合は、「D11」に「1月」から「2月」を減算(引き算)したものを表示したいのですが、

その式は下のようになります。

=SUMPRODUCT((B3:B8=B11)*(C3:C8=”猫缶”)*D3:D8-(B3:B8=B11)*(C3:C8=”猫缶”)*E3:E8)

「1月」「2月」それぞれの場所に、抽出する同じ条件を付けています。下の赤字の場所です。

=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」として扱われる

 

SUMPRODUCTSUMは合計する。PRODUCTには「産出する」という意味があります。

繋げると「合計」を「産出」するという感じ。

こんなイメージで覚えておくと良いのでは。

 

「SUM」を「PRODUCT」するにゃ