sumproductに関して

質問

=SUMPRODUCT((B1:C10=E1:F10)*1)　という例示を見つけました。
内容は、B1:C10とE1:F10の範囲を1対１に対応させて、
同じ内容の数（セル）をカウントする。
というらしいです。

私がマニュアルを見たところではこのような書き方は出ていませんでした。
なぜこのような書き方が出来るのか分かりません。
何となくは分かるのですが。

なぜこのようなことが出来るのか、具体的に教えてください。

回答No.2

bunjii · ベストアンサー

>なぜこのような書き方が出来るのか分かりません。
SUMPRODUCT関数は配列の要素を合計する役割になっています。
提示の数式はB1:C10の20個の要素とE1:F10の20個の要素を比較して同じ値のとき１を、違う値のときに0を返しますので、その結果を合計すると同じ値の要素の数が分かります。

貼付画像はExcel 2013で検証した結果です。
H1=(B1=E1)*1
H1セルをコピーでクリップボードへ記憶させ、H1からI10へ貼り付けました。
K1=SUM(H1:I10)
L1=SUMPRODUCT((B1:C10=E1:F10)*1)
L1の数式は提示のものです。
H1からI10の数式をSUMPURODUCT関数の配列引数として与えることによって作業用のH1:I10セルを省略できると考えれば理解できるでしょう。

お礼

早速有り難うございました。

お二方の説明を聞いてハッキリしました。
何となくそうなのだろうとは思っていたのですが。

(1)B1=E1が移動ではなく、比較であり、false／trueが返る、
　ということ。

(2)sumproductではそれが　レンジ（配列）で、かつセル１個ずつに対応させて使える、
　ということ。

今後は他でもこの考え方が生かせると思います。
有り難うございました。

補足

実際にやって過程まで見せていただき、恐縮です。
よく分かりました。

こういう書き方が出来るのだということが分かれば、
十分納得です。
お世話になりました。

回答No.3

MarcoRossiItaly

=(b1=e1)*1 という数式をどこかのセルに記入すると、計算結果は 0 か 1 になるはずです（B、C 列にエラーがないとき）。同様に =(c1=f1)*1、=(b2=e2)*1、=(c2=f2)*1、…、=(b10=e10)*1、=(c10=f10)*1 という数式をどこかに記入しても、やはり 0 か 1 になります。

このように書いていくと、全部で 20 本の数式になりますよね。これらは、「=」を取り除いた部分である (b1=e1)*1 という値、(c10=f10)*1 という値などを求めて、各セルに表示させる数式なのです。

なお「*1」というのを取り除いて =b1=e1 をセルに記入した場合は、FALSE または TRUE という論理値が表示されるはずです。試してみてください。

さて、これら 20 個の値を 1 本の数式の中でまとめて表現するときは、(B1:C10=E1:F10)*1 という「配列」を書きます。上の 20 本の等式（論理式）を 1 本にまとめると、この形になります。配列を扱うことができる関数はいろいろ用意されているのですが、SUMPRODUCT は、配列の値を全部足してくれる関数です。したがって質問文の数式は、20 個の 0 または 1 の合計値を算出することになります。つまり、一致するセルをカウントしたことになります。

「*1」を省いた場合、SUMPRODUCT は基本的には TRUE や FALSE を足すことができない仕様なので、常に合計は 0 となってしまいます。

=SUMPRODUCT(B1:C10=E1:F10)　　……常に合計が 0

ところで、例えば 1*2、-1*3、3*4 という 3 つの値を足すときは、次式で求めることができますね。

=sum(1*2,-1*3,3*4)

これは、次のとおり書き換えることができます。同じ計算結果になります。

=sum({1,-1,3}*{2,3,4})

{1,-1,3} などは、配列定数と呼ばれています。参照ではなく配列定数のみから求められる配列を合計するときは、SUMPRODUCT を使ってもエラーにはなりませんが、SUM で足りるということです。

お礼

先ず、閉めきったアトまでお教えいただき感謝です。

そして、これまでの回答で、
=(b1=e1)*1 や、=(b1=e1)　はそういうものかとも分かりましたが、

さらに今回の教えで、
＞これら 20 個の値を 1 本の数式の中でまとめて表現するときは、
＞(B1:C10=E1:F10)*1 という「配列」を書きます。
　が分かりました。
　正直ここが分かりませんでした。

=sum(1*2,-1*3,3*4)　→　=sum({1,-1,3}*{2,3,4})　も眼から鱗です。

お陰で痒いところに手が届きスッキリしました。
ベストアンサーに出来ないのが残念ですが、深謝です。
重ね重ね有り難うございました。

回答No.1

tom04

こんばんは！

SUMPRODUCT関数そのものが配列数式ですので
そういった使い方が可能です。

式の説明は
B1～C10セルの配列（配置）とE1～F10セルの配列（配置）
を1セルずつ比較しますので、
仮に
B1=E1　の場合は「TRUE」・B1<>E1　の場合は「FALSE」が返ります。
同様にB2セルとE2セルは・・・？
B3セルとE3セルは・・・？
といった感じで、その計算を1セルずつ行っているのが
＞SUMPRODUCT(B1:C10=E1:F10)
の部分になります。

このままでは「TRUE」か「FALSE」が返るだけですので、
*1　として「TRUE」の数を合計した結果が表示されます。

※　この場合セル範囲の数が異なるとエラー（#N/A）が表示されます。m(_ _)m

お礼

早速有り難うございました。

お二方の説明を聞いてハッキリしました。
何となくそうなのだろうとは思っていたのですが。

(1)B1=E1が移動ではなく、比較であり、false／trueが返る、
　ということ。

(2)sumproductではそれが　レンジ（配列）で、かつセル１個ずつに対応させて使える、
　ということ。

今後は他でもこの考え方が生かせると思います。
有り難うございました。