SUMPRODUCT関数条件ごとの順位、グループごとの順位を出す~Excel 2013

条件ごとの順位をだす

条件ごとの順位やグループごとの順位を出す方法を紹介します。

SUMPRODUCT関数
SUMIF

SUMPRODUCT関数 2013Excel関数技BEST 52回

条件ごとの順位、グループごとの順位を出す

表の項目内にある条件やグループごとに順位を出し、ランキングを表示したい時には、RANK関数ではなく、SUMPRODUCT関数を使います。
少し上級者向けの使い方ですが、式の意味を説明しますので、ぜひ利用してみてください。
Excelバージョン:Excel2016201320102007200320022000
(サンプルファイルは、こちらから 2013関数技52回サンプルデータ

条件別の順位をだすにはSUMPRODUCT関数

以下のサンプルの表で、スタイルの列を条件としてグループごとの順位を表示したい場合を考えましょう。
SUMPRODUCT関数で条件別の順位をだす
D2セルに、次の式を入力します。
=SUMPRODUCT(($B$2:$B$13=B2)*($C$2:$C$13>C2))+1
$B$2:$B$13=B2:$B$2:$B$13の中でB2に等しい
SUMPRODUCT関数で条件別の順位をだす2
入力した式を下までコピーすると、スタイルという条件によって、グループごとに「レトロ」内での順位、「カントリー」内での順位が表示されています。
SUMPRODUCT関数で条件別の順位をだす3
フィルタを使わなくても、グループ内の順位が表示されるので、便利ですね。
この数式の仕組みは、SUMPRODUCT関数の「引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。」という定義を論理積に使っているということなのです。(ヘルプには書かれてないですが・・・)
数式の考え方を説明しておきますね。

SUMPRODUCT関数数式の意味

D2セルに入力された数式
=SUMPRODUCT(($B$2:$B$13=B2)*($C$2:$C$13>C2))+1
この式の+1を除外して
=SUMPRODUCT(($B$2:$B$13=B2)*($C$2:$C$13>C2))
の部分だけ考えてみましょう。
SUMPRODUCT関数の数式のうち前半の式($B$2:$B$13=B2)だけを見ると、
これはB2:B13セルのうち、B2セルと同じセル、つまりサンプルのレトログループを指定しています。
後半の式($C$2:$C$13>C2))は、C2セルより大きい値がTRUE=1となります。あとは合計しているわけです。
SUMPRODUCT関数で条件別の順位をだす4
D2セルの式と同様に、D3セルの式以降、それぞれ同じようにTRUE=1とFALSE=0の積をあとで合計しています。
SUMPRODUCT関数で条件別の順位をだす5
説明を簡単にするために、+1を式から除外していましたが、これだと、合計が0位~3位となるので式に+1をしています。
=SUMPRODUCT(($B$2:$B$13=B13)*($C$2:$C$13>C13))+1
この式は、論理積の和であるということを理解できれば、式を
=SUMPRODUCT(($B$2:$B$13=B13)*($C$2:$C$13>=C13))
としてもいいと思います。
今日の講義は以上です。お疲れ様でした。
もっと知りたいSUMPRODUCT関数の使い方