社内SEの徒然なる日記

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

■ SUMPRODUCT関数

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

「複数の条件を指定して合計を表示する」って事なら理解できるのですが...
Excelのグループ別順位(SUMPRODUCT:分析) (0)

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

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

■ boolean(真偽値)

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

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

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

さて、ここで前回使った表を使って、もう一度考えてみます。
Excelのグループ別順位(SUMPRODUCT:分析) (1)

①②の枠内が条件に一致する範囲です。この結果を真偽値にして、さらに数値で表現するとこうなります。
Excelのグループ別順位(SUMPRODUCT:分析) (2)

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

上記のように、1行目と2行目が1(True)、その他の行が0(False)になりました。

■ 要素の積の合計

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

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

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

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

となる訳です。

■ 後書き

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

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

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


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

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

--- blog end ---

スポンサードリンク

PageTop

コメント


管理者にだけ表示を許可する