社内SEの徒然なる日記

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

■ 要望

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

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

テスト用に、こんな表を用意しました。
Excelのグループ別順位(パズル) (0)

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

■ IFとCOUNTIFの組合せ

まず、グループの昇順、金額の降順に並べ替えます。
Excelのグループ別順位(パズル) (1)

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

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

まず、集計の基準になるグループ列に対して、自分の行(4行目)と、一つ上の行(3行目)が一致しているかを判定するIF関数を書きます。
Excelのグループ別順位(パズル) (2)

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

同じグループであった場合、金額列に対して自分の行(4行目)と、一つ上の行(3行目)が一致しているかを判定するIF関数を書きます。
Excelのグループ別順位(パズル) (3)

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

であれば、この条件を満たした場合は、自分の一つ上のセル(D3)の値(順位)を表示すれば良いって事になります。
Excelのグループ別順位(パズル) (4)

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

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

COUNTIF関数の範囲はグループ列にします。行の始点は、データの先頭行(3行目)を絶対参照で指定します。終点は「自分の行にします」。んで、検索条件を自分の行(4行目)にします。終点と自分の行は相対参照のままです。
Excelのグループ別順位(パズル) (5)

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

最後に、最初に指定した条件を満たさなかった場合の処理を書きます。
Excelのグループ別順位(パズル) (6)

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

後は、作った計算式をコピペして完成です。
Excelのグループ別順位(パズル) (7)

■ 後書き

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

こんな感じになっちゃうんですよ。
Excelのグループ別順位(パズル) (8)

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

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

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

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

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

--- blog end ---
スポンサードリンク

PageTop

コメント


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