トップページ > Excel エクセル関数 > SUMPRODUCT関数 | 配列の対応する要素間の積を計算し、その合計を返します。

SUMPRODUCT関数

SUMPRODUCT関数 | 配列の対応する要素間の積を計算し、その合計を返します。


SUMPRODUCT関数
種類 数学/三角関数
できること 配列の対応する要素間の積を計算し、その合計を返します。
書式 SUMPRODUCT(配列1配列2)
配列1
(必須)
計算対象となる配列を入力します。
配列2 複数の配列を指定することが出来ます。
個々の配列の行数と列数を等しくする必要があります。等しくない場合は、エラー値となります。

下記で紹介するSUMPRODUCT関数の使い方 その【2】【3】で扱う複数条件に合致するデータの合計とカウントは、ちょっと難しいですが、かなり役に立ちます。
エクセル2007以降であれば、複数条件に対応するSUMIFS関数とCOUNTIFS関数が出て来ましたが、エクセル2003以前のバージョンを使っているのであれば、このSUMPRODUCT関数を使った方法は知っておいて、損はありません。

SUMPRODUCT関数の使い方 その【1】単価x個数 を合計する。

SUMPRODUCT関数 使い方 その1

=SUMPRODUCT(B2:B11,C2:C11)
説明 上図のように、各品名毎の単価と個数から、全体の合計金額が幾らになるのかを計算する場合、各品名毎の小計を 単価x個数 で一旦求め(セルD2:D11)、その小計を合計する(セルD12)、という方法が考えられます。

SUMPRODUCT関数を使うと、小計の計算を挟むこと無く、計算式一つで済ますことが出来ます。

例では、配列1に単価データとしてセルB2:B11、配列2に個数データとしてセルC2:C11を指定しています。
これにより、SUMPRODUCT関数は、配列1の一つ目であるセルB2と配列2の一つ目であるセルC2を掛け、積を求めます。次に二つ目のセルB3とセルC3の積、三つ目のセルB4とセルC4の積と、セルB11とセルC11の積まで続いていきます。
そして、最後に、求めた積を合計した値を返します。

計算式で表すと下記のように長~い式になるのですが、これをSUMPRODUCT関数ひとつで求めることが出来るというわけです。

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)+(B10*C10)+(B11*C11)

このように対応するセルを掛けあわせていくので、配列の行数・列数が等しくないとちゃんと計算が出来ずエラー値となるわけです。納得。

SUMPRODUCT関数の使い方 その【2】 SUMIF関数で出来ない複数の検索条件で合計する。

SUMPRODUCT関数 使い方 その2

=SUMPRODUCT((A2:A11=”東京青果”)*(B2:B11=”りんご”)*(C2:C11))
説明 SUMIF関数では検索条件に合致するデータの合計を計算することが出来ましたが、一つの検索条件にのみ対応し、複数の検索条件で計算することは出来ません。
しかし、例のようにSUMPRODUCT関数を用いると、複数の検索条件に合致するデータの合計を取得することが出来ます。

この式では、A列の売上先が「東京青果」で且つB列の品名が「りんご」となっている売上金額C列の合計を求めています。

具体的には、(A2=”東京青果”)*(B2=”りんご”)*(C2)、(A3=”東京青果”)*(B3=”りんご”)*(C3)、………、(A11=”東京青果”)*(B11=”りんご”)*(C11)と、一行毎に計算を行ない、その合計が返されます。


まずは、一行目の (A2=”東京青果”)*(B2=”りんご”)*(C2) ですが、分解して見てみると、

・A2=”東京青果”
 この式では、セルA2が「東京青果」と等しいかどうか、を判定しています。セルA2は「東京青果」となっていますので、論理値「TRUE」が入ります。
 そして、計算式の中に入っている論理値は数値に変換されますので、論理値「TRUE」は「1」となり、最終的には「1」という計算結果となります。

・B2=”りんご”
 この式では、セルB2が「りんご」と等しいかどうか、を判定しています。セルB2は「キウイ」となっていますので、論理値「FALSE」が入ります。
 そして、計算式の中に入っている論理値は数値に変換されますので、論理値「FALSE」は「0」となり、最終的には「0」という計算結果となります。

・C2
 ここには、「東京青果」宛の「キウイ」の売上金額である「200」が入っています。

その結果、(A2=”東京青果”)*(B2=”りんご”)*(C2) は、 1 * 0 * 200 という計算を行い、「0」という計算結果が求められます。


次に少し飛んで、5行目のセルに対して行う (A5=”東京青果”)*(B5=”りんご”)*(C5) という計算を見てましょう。

・A5=”東京青果”
 この式では、セルA5が「東京青果」と等しいかどうか、を判定しています。セルA5は「東京青果」となっていますので、論理値「TRUE」が入ります。
 そして、計算式の中に入っている論理値は数値に変換されますので、論理値「TRUE」は「1」となり、最終的には「1」という計算結果となります。

・B5=”りんご”
 この式では、セルB5が「りんご」と等しいかどうか、を判定しています。セルB5は「りんご」となっていますので、論理値「TRUE」が入ります。
 そして、計算式の中に入っている論理値は数値に変換されますので、論理値「TRUE」は「1」となり、最終的には「1」という計算結果となります。

・C5
 ここには、「東京青果」宛の「りんご」の売上金額である「330」が入っています。

その結果、(A5=”東京青果”)*(B5=”りんご”)*(C5) は、 1 * 1 * 330 という計算を行い、「330」という計算結果となり、条件に合う合計金額を抽出出来ています。


このように、以下の様な計算が行われ、その結果を合計し、A列の売上先が「東京青果」で且つB列の品名が「りんご」となっている売上金額C列の合計が「740」と求めることが出来ました。

(A2=”東京青果”)*(B2=”りんご”)*(C2) = 0
(A3=”東京青果”)*(B3=”りんご”)*(C3) = 0
(A4=”東京青果”)*(B4=”りんご”)*(C4) = 0
(A5=”東京青果”)*(B5=”りんご”)*(C5) = 330
(A6=”東京青果”)*(B6=”りんご”)*(C6) = 0
(A7=”東京青果”)*(B7=”りんご”)*(C7) = 0
(A8=”東京青果”)*(B8=”りんご”)*(C8) = 0
(A9=”東京青果”)*(B9=”りんご”)*(C9) = 410
(A10=”東京青果”)*(B10=”りんご”)*(C10) = 0
(A11=”東京青果”)*(B11=”りんご”)*(C11) = 0


もしかすると、
=SUMPRODUCT((A2:A11=”東京青果”),(B2:B11=”りんご”),(C2:C11))
の計算式でも問題無いのではないか、と思われた方がいるかもしれません。

しかし、この数式の計算結果は、「0」となり、正しく計算されません。

SUMPRODUCT関数において、数値以外の配列要素は「0」であると見なされるため、 条件式である (A2:A11=”東京青果”) と (B2:B11=”りんご”) が「0」と見なされるため、
SUMPRODUCT((A2:A11=”東京青果”),(B2:B11=”りんご”),(C2:C11))
= SUMPRODUCT(0,0,(C2:C11))
となり、計算結果が「0」となってしまいます。

これを回避するには、論理値が返される条件式を数値に変換する為に、[1」を掛けるか、N関数で変換します。

まとめると、
=SUMPRODUCT((A2:A11=”東京青果”)*(B2:B11=”りんご”)*(C2:C11))
の他に、
=SUMPRODUCT((A2:A11=”東京青果”)*1,(B2:B11=”りんご”)*1,(C2:C11))
=SUMPRODUCT(N(A2:A11=”東京青果”),N(B2:B11=”りんご”),(C2:C11))
の式でも同じ結果を得ることが出来ます。

SUMPRODUCT関数の使い方 その【3】 COUNTIF関数で出来ない複数の検索条件でカウントする。

SUMPRODUCT関数 使い方 その3

=SUMPRODUCT((A2:A11=”東京青果”)*(B2:B11=”りんご”))
説明 その2の例にあるように、条件に合致するデータの合計を計算出来るということは、条件に合致するデータの個数をカウントすることも出来ます。

COUNTIF関数では検索条件に合致するデータの個数を数えることが出来ますが、一つの検索条件にのみ対応し、複数の検索条件でカウント出来ません。
しかし、例のようにSUMPRODUCT関数を用いると、複数の検索条件に合致するデータの個数をカウントすることが出来ます。

この式では、A列の売上先が「東京青果」で且つB列の品名が「りんご」となっている売上データの個数を求めています。


その【2】の式から、C列の売上金額を抜いただけですので、詳しい解説は省略しますが、以下の計算が行われ、その結果が合計されるので、A列の売上先が「東京青果」で且つB列の品名が「りんご」となっている売上データの個数が「2」と求めることが出来ます。

(A2=”東京青果”)*(B2=”りんご”) = 0
(A3=”東京青果”)*(B3=”りんご”) = 0
(A4=”東京青果”)*(B4=”りんご”) = 0
(A5=”東京青果”)*(B5=”りんご”) = 1
(A6=”東京青果”)*(B6=”りんご”) = 0
(A7=”東京青果”)*(B7=”りんご”) = 0
(A8=”東京青果”)*(B8=”りんご”) = 0
(A9=”東京青果”)*(B9=”りんご”) = 1
(A10=”東京青果”)*(B10=”りんご”) = 0
(A11=”東京青果”)*(B11=”りんご”) = 0

Tagsカテゴリー:

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

*

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

トラックバックURL

http://excel.jiten.org/function/sumproduct/trackback/