Excelでグループ別の順位を取得する方法 No.1 IFとCOUNTのパズル

■　要望

先日、「Excelでグループ毎の順位を出したいんだけど、どうすれば良いだろうか？」って問合せを受けました。

単純な順位ならRANK関数で一発なんですけど、グループ別だとちょっと厄介ですね。まぁ、やり方は何個かありそうですが．．．

テスト用に、こんな表を用意しました。


これを元に、グループ別の金額ランキングを作っていきます。

■　IFとCOUNTIFの組合せ

まず、グループの昇順、金額の降順に並べ替えます。


なお、グループは降順でも良く、金額は降順で固定です。まぁ、金額の少ない順のランキングなら昇順になるのですが。

ここから、IF関数とCOUNTIF関数、そして表の並びを利用して順位を求めていきます。先頭行（D3）だと概念が伝わりにくかったので、上から２番目（D4）に式を突っ込んでいきます。

まず、集計の基準になるグループ列に対して、自分の行（４行目）と、一つ上の行（３行目）が一致しているかを判定するIF関数を書きます。


グループ順に並んでいるので、「自分の一つ上が同じであれば同じグループ」となります。

同じグループであった場合、金額列に対して自分の行（４行目）と、一つ上の行（３行目）が一致しているかを判定するIF関数を書きます。


さて、上の二つの条件を満たすって事は、自分のひとつ上の行は「同じグループで同じ金額」です。つまり「同じ順位」な訳ですね。

であれば、この条件を満たした場合は、自分の一つ上のセル（D3）の値（順位）を表示すれば良いって事になります。


では、条件を満たさない場合はどうするかってなりますが、ここでCOUNTIF関数の出番です。

前提として、表はグループ、金額順に並んでいます。であれば「自分より上の行で、自分と同じグループの件数」が、そのまま順位と考えて良いはずです。

COUNTIF関数の範囲はグループ列にします。行の始点は、データの先頭行（３行目）を絶対参照で指定します。終点は「自分の行にします」。んで、検索条件を自分の行（４行目）にします。終点と自分の行は相対参照のままです。


終点を「相対参照で自分の行にするって」ってのがコツですね。これで計算式をコピーするだけで「自分より上の行で、同じグループの件数」が取得できます。

最後に、最初に指定した条件を満たさなかった場合の処理を書きます。


グループ列の比較で、自分の一つ上の行と値が違うって事は、つまりグループが変わったって事です。表はグループ、金額の順に並んでいるので、グループが変わった行は１位になります。って事で、式の最後は１を指定しています。

後は、作った計算式をコピペして完成です。

■　後書き

実は、並べ替えの後にCOUNIF関数を仕込むだけでも順位は取得できます。ただし、比較対象（ここでは金額）の値が同じ時の表現を工夫するためにIF関数を付け足しています。

こんな感じになっちゃうんですよ。


順位２って列を追加して、ここにはCOUNTIF関数だけを書きます。金額列の値が同じであっても、順位が増えている事が分るかと思います。

普通、順位を求める時は「同率**位」って表現ですよね。って事で、少し工夫が必要でした。

次回は、SUMPRODUCT関数で同じことをやってみます。これはこれで概念が理解しにくい関数なんですが、今回の方法よりはスマートです。なんと、事前に表の並べ替えが不要なんです。

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

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

--- blog end ---