Excelでグループ別の順位を取得する方法 No.3 SUMPRODUCT関数（分析）

■　SUMPRODUCT関数

前回（Excelでグループ別の順位を取得する方法　No.2　SUMPRODUCT関数）は、SUMPRODUCT関数の使い方を書いてみたのですが、最後の結果がなぜ順位になるのか納得いきませんでした。

「複数の条件を指定して合計を表示する」って事なら理解できるのですが．．．


分類①＆分類②の合計表ですね。最初に条件を指定して、２つ目の配列で合計する範囲を設定しています。

これなら「条件を満たした行の指定した列の合計」って理解で良いのですが、合計する列が指定されなかった時に、どういう理屈をもって順位が表示されるのかが理解出来ませんでした。

■　boolean（真偽値）

上のサンプルもそうですが、このSUMPRODUCT関数ってのは複数の条件を「 (条件式) * (条件式) 」って繋ぐことで、複数の条件の論理積を判定する動作をしてくれるようなんです。

この条件式を満たすって言葉から「判定結果って内部的には真偽値を使っているのではないか」と推測します。

真偽値は、True（真）とFalse（偽）の二つの値を表現する訳ですが、機械的には1桁の数値データとも表現できます。つまり、True（真）は 1、False（偽）は 0 になるって考えます。そうすると、色々と見え方が変わってきます。

さて、ここで前回使った表を使って、もう一度考えてみます。


①②の枠内が条件に一致する範囲です。この結果を真偽値にして、さらに数値で表現するとこうなります。


元の計算式がこれです「=SUMPRODUCT(($B$3:$B$9=B3) * ($C$3:$C$9>=C3))」。
SUMPRODUCT関数は要素（行）の単位で計算を行うので、計算結果は下記の通りになります。


上記のように、１行目と２行目が１（True）、その他の行が０（False）になりました。

■　要素の積の合計

SUMPRODUCT関数の説明には「範囲または配列の対応する要素の積を合計した結果を返します。」と記述されています。

さて、「=SUMPRODUCT(($B$3:$B$9=B3) * ($C$3:$C$9>=C3))」では、配列は１つしか指定していません。そして、指定した配列の結果は上記の図の通りです。

この場合「要素の積」で計算する対象は無いので、直接「合計」が計算されます。

　1 + 1 + 0 + 0 + 0 + 0 + 0 = 2

となる訳です。

■　後書き

ここまで考えて、条件を指定した時のSUMPRODUCT関数の動作に納得しました。

式に条件を指定すると、範囲内の行が条件に一致すれば１、しなければ０となります。これを基準に要素（行）で積（掛け算）を求めると考えると、１に何を掛けても数値は変わらない、０を掛けると結果は常に０になる。

後は、この計算結果を積上げているって訳です。


前回：Excelでグループ別の順位を取得する方法　No.2　SUMPRODUCT関数
次回：Excelでグループ別の順位を取得する方法　No.4　余談

投稿記事の一覧：http://harikofu.web.fc2.com/

--- blog end ---