SUMPRODUCT($A$1:$A$10,$B$1:$B$10)とSUMPRODUCT($A$1:$A$10*$B$1:$B$10)

質問

エクセルのSUMPRODUCT関数についてお尋ねします。

=SUMPRODUCT($A$1:$A$10,$B$1:$B$10)
と
=SUMPRODUCT($A$1:$A$10*$B$1:$B$10)
の違いは何でしょうか？
両者とも同じ答えを返しますが、いろいろ試したところ、前者は範囲内に文字列があってもそれを無視して計算し、後者は文字列があればエラーになるようですが、その理解で正しいでしょうか？
正しいとすれば、なぜでしょうか？

回答No.3

Wendy02 · ベストアンサー

こんばんは。

配列計算をしているのは言うまでもありませんが、この、SUMPRODUCT は、必ず、内部のひとまとまりから計算するという性質を持っています。ただ、VBAのように左から時系列かどうかは、言語が違いますから、分かりません。しかし、この引数は、パラメータ配列になっています。そして、その引数それぞれの有効値を調べて、それを演算出来る値かどうか調べます。

文字列を入れてもエラーにならないのは、元々、そこに注釈などを入れるために考えられたものです。値を文字列か数値かを精査して演算するように出来ています。最初、SUM関数に対し、そのように作られました。プログラムとしては、初歩的なものですが、そのアイデアは、今日まで、踏襲しているようです。このオリジナルの仕組みを考えたのは、VisiCalc の開発者、ブルックリン氏です。昔々、本で読んだことがあります。 PRODUCT関数とも共通です。3つの関数は、同じような仕様を持っています。

こちらが、本来の使い方です。

=SUMPRODUCT($A$1:$A$10,$B$1:$B$10)

つまり、
$A$1:$A$10　と　$B$1:$B$10　の値を別々に取得しています。

では、
=SUMPRODUCT($A$1:$A$10,$B$1:$B$11)

とすると、なぜ、エラーが出るかというと、$B$11 に対応する数値が、NULL値（有効な値がない）だからです。それは、全体に反映させてしまいます。（理由は分かると思いますが、プログラムが途中で、エラーで止まってしまうからです）

ところが、こちらは、

=SUMPRODUCT($A$1:$A$10*$B$1:$B$10)

$A$1:$A$10*$B$1:$B$10　は、分割出来ません。したがって、この数式の中でのエラーは、そのまま、元の数式に反映されます。

ご自分で、ユーザー定義関数を作ってみれば分かります。VBAでも、同じように作ることが可能です。一度、試してみるとよいです。

お礼

> $A$1:$A$10*$B$1:$B$10　は、分割出来ません。したがって、この数式の中でのエラーは、そのまま、元の数式に反映されます。

なるほど、=SUMPRODUCT($A$1:$A$10*$B$1:$B$10)では=SUMPRODUCT(配列)なんですね。
=SUMPRODUCT($A$1:$A$10,$B$1:$B$10)ではじめて、=SUMPRODUCT(配列1,配列2)ということですね。

Wendy02さま、いつも有難うございます。

回答No.4

taocat

お師匠さん、こんばんは。

ヘルプの読み込みがあまいですぞ。(笑）

●ヘルプによると、数値以外の「要素」は、０とみなす、とあります。
それを踏まえて。。。。

＿＿＿＿＿Ａ＿＿＿Ｂ＿＿＿＿
１＿＿＿＿３＿＿＿１０＿＿＿
２＿＿＿＿あ＿＿＿２０＿＿＿
３＿＿＿＿２＿＿＿３０＿＿＿

----------------------------------------------------------------
（１）SUMPRODUCT(A1:A3　，　B1:B3)
　　　SUMPRODUCT(配列１　，配列２）

配列１＊　配列２
　３　＊　１０　　＝３００
　０　＊　２０　　＝０
　２　＊　３０　　＝６００

ヘルプより、配列１の２番目の要素「あ」は、０として計算しているので、エラーはでない。

-----------------------------------------------------------------

（２）SUMPRODUCT(A1:A3　＊　B1:B3）
　　　SUMPRODUCT(　　配列１　　　）

　　　　　　　　　配列１
　３　＊　１０　＝３００
　あ　＊　２０　＝#VALUE
　２　＊　３０　＝６００

この場合は掛け算の段階ではまだ「配列の要素」ではないので個々の掛け算は通常の法則でされ「あ＊２０」はエラーとなる。
そしてそれらの結果が配列の要素になるが、配列がひとつなのでそれ以上掛け算するものがないので
ここでもヘルプは当てはまらず通常の法則で合計されるため結果としてエラーになる。
-----------------------------------------------------------------

たぶんこんなことではないかと、←　おい、おい。(^^;;;

何れにしろ、ヘルプの、数値以外の▲要素▲、という文言がポイントだと思われます。

以上です。

お礼

taocatさま、お久しぶりでございます。
なるほどよく理解できました。
有難うございました。

回答No.2

cafe_au_lait

自分も試した範囲でですが・・・。

SUMPRODUCT関数は文字列を0と見なしますが、エラー値を含む場合はエラーとなります。
後者は文字列がある場合、カッコ内の計算過程でエラー値が生じます。そのためSUMPRODUCT関数もエラー値を返します。
前者でも範囲内に#VALUE!や#N/Aを含む場合はエラーとなります。

1 5
2 2
3 4
4 2
5 3
6 a
#N/A 1
8 1
9 2
10 8

前者:#N/A
後者:#VALUE!

お礼

ありがとうございます。
そうか、エラー値は両方ともエラーになるんですね。
勉強になりました。

回答No.1

mshr1962

前者はSUM関数と同じで、文字は0と見なして計算します。
後者は文字で乗算してるので#VALUE!ですね。
内容に関してはあなたの考え通りです。
SUMPRODUCT関数は範囲の積の和を求めるので、
エラーがでなければ同じ結果ですね。

こんな使用法もあります。
A列が10を超える値の時のA列とB列の積の和
=SUMPRODUCT(($A$1:$A$10>10)*($A$1:$A$10),$B$1:$B$10)
A列が"合格"という文字のB列とC列の積の和
=SUMPRODUCT(($A$1:$A$10="合格")*($B$1:$B$10)*($C$1:$C$10))
A列とB列の合計とC列の積の和
=SUMPRODUCT($A$1:$A$10+$B$1:$B$10,$C$1:$C$10)

お礼

ありがとうございます。
勉強になりました。