Excelでランキング表を作る方法。RANK関数とVLOOKUP関数を使え⁈

こんにちは。Excelを使いこなす会社員、ユージーン(@Eugene_no2)です!

会社で仕事をしていると、売上データなど、Excelでランキング表を作る、といった作業が必要になることがあるでしょう。

Excelでランキング表を作るには、フィルタ機能を使うパターンや、RANK関数などの関数を駆使するパターンなど、複数の方法があり、それぞれにメリットがあります。

ということで今回は、Excelランキング表の作り方を解説します。

Excelのフィルタ機能を使ってランキング表を作る方法

まず、多くの人がやっているであろう方法、それがExcelのフィルタ機能を使ってランキング表を作る方法です。

今回は国勢調査の平成28年の推計人口のデータを使いたいと思います。元データはこちらから

Excelには「フィルタ」と呼ばれる機能があり、ランキングにしたい数値の列で、降順にソート(=大きいものから順に並べ替えること)ができます。

フィルタ機能を起動するには、データの見出し行を選択した状態で、「フィルタ」を選択します。

次に、ソートをかけたい列のプルダウンから「降順」を選択すればOKです。

このフィルタ機能は、簡単に並び替えができるので非常に便利な反面、ソートをかけたり、その後コピーして表に貼り付けたりと「手作業」が多く発生します。

手作業が多く発生すると、ミスの原因になるだけでなく、例えば定期的に同じランキング表を作るような場合、毎回同じ作業をしなければならず、結局非効率になる場合があります。

Excel関数を使ってランキング表を作る方法

一方で、Excelの関数を使ってランキング表を作る方法があります。この記事で紹介したいのは、むしろこっちです。

関数を使ってランキング表を作ると、最初は正直少し時間がかかりますが、2回目以降は元データを貼り替えるだけで、ランキング表が自動的に完成する、といった芸当も可能となり、ゆくゆくは作業にかかる時間も逆転して効率的になるでしょう。

関数に苦手意識のある人には、少し難しく感じられるかも知れませんが、なるべく丁寧に解説していきます。

まず、RANK関数を使って順位を判別する

まず手始めにやるべきは、RANK関数を使って、順位を判別すること

RANK関数とは、指定した範囲の中で、指定したセルが何番目に大きいか、という順位を教えてくれる便利な関数です。

=RANK(数値,参照)
(訳:数値参照の中で何番目に大きいかを教えて)

後ほどVLOOKUP関数の検索値として使うため、元データよりも左側に新しく列を挿入して、順位を判別するようにしてください。つまりは、こういうこと。

この例では、A6セルに入れる関数(総人口の順位)は、普通に考えれば、

=RANK(C6,$C$6:$C$52)

ということになります。しかし、わたしならちょっと工夫して、次のようにします。

RANK(C6,$C$6:$C$52)+COUNTIF(C6:$C$52,C6)-1

この記事の本筋ではないので、読み飛ばしていただいても結構ですが、この後半部分の
COUNTIF関数-1
は何かというと、ここより下に同じ数字がないかチェックして、ある場合はその分数字を足せ、と言っているのです。

なぜこんなことをするかといえば、RANK関数では、同じ数字が入ってた場合、同じ順位(同率)が返されるから。

絶対に同順が発生しないランキングなら問題ないんですが、仮に同じ順位が発生してしまうと、後のちランキング表にするときに不都合が生じるので、同じ順位が入らないための工夫が必要なんです。

詳しくは、こちらの記事で解説しています。

合わせて読みたい RANK関数で同じ順位が存在しないようにする方法
ExcelのRANK関数では、数値が同じなら同じ順位が返されますが、同じ順位が出てくると困ることもあります。ここでは同順位が出てこないようする方法を解説

これで第1ステップである、順位の判別が完了です。

VLOOKUP関数でランキング表を作成

順位の判別ができたら、続いてはランキング表の作成です。ランキング表を作るには、VLOOKUP関数を使います。

=VLOOKUP(検索値,範囲,列番号,検索方法)
(訳:範囲の1列目から検索値を探して、そこから列番号目の値を返せ)

ここで、順位を検索値にして、必要な情報(商品名や売上金額など)を引っ張ってくるのです。

例えば、H3セルに入る関数は、次のようになります。

=VLOOKUP(G3,A:E,2,FALSE)

ちなみに、VLOOKUP関数で引っ張ってくるべきものが複数列ある場合は、「列番号」の部分を工夫すると、一気にデータを取得することができます。詳しくは、こちら。

合わせて読みたい VLOOKUP関数で複数列のデータを一気に取得する方法
VLOOKUP関数の応用活用術。列番号を記載することで、複数列を一気に取得する方法を解説。

これでランキング表を完成させることが出来ました。

同率を同じ順位として扱うためにはIF関数を使おう

先ほど同じ順位が出てこないように工夫をしましたが、その工夫をしないと、順位が通し番号にならないので、ランキング表に欠損が出てしまいます。

(例えば、同率3位があった場合、RANK関数では、「4」位が出てきません。)

しかし、逆に、このままだと、同率であるにも関わらず、順位には順序がついてしまう、という状況が起きてしまいます。

もしそれが嫌な場合、つまり、数値が同率なら同じ順位としたい場合には、更にIF関数を使う方法があります。

VLOOKUP関数の検索値用の順位と、ランキング表用の順位を分けて用意すればいいのです。

さて、この場合、H3セルにどのような関数を入れるべきか、分かりますか?例えば、こうです。

=IF(J2=J3,G3-1,G3)

つまり、数値が一つ上と同じ場合には検索用の順位ー1を、異なる場合は検索用の順位と同じ順位を、という命令をしているのです。

これによって、同順位の場合は、ランキングに順序がつかないようになりましたね。

さて、理解できましたでしょうか?

今回は、Excelでランキング表を作る2つの方法、特に、RANK関数とVLOOKUP関数を使った賢いやり方について解説しました。

この方法であれば、例えば期間の異なるデータでランキング表を作る場合、元となっているデータを貼り替えるだけで、既に新しいランキング表が完成している状態に!

最初は少し手間に感じるかも知れませんが、一度作業ファイルを作ってしまえば、2回目以降が楽チンなのです。

同様に、VLOOKUP関数を使って定型フォーマットのレポートを超効率的に作成するオススメの方法をこちらでご紹介。

合わせて読みたい Excel・VLOOKUP関数を駆使して定型レポートを超効率的に完成させる方法
VLOOKUP関数を駆使して、決まった形式のデータから、欲しいデータを半自動的にピックアップする方法を解説。

Excelを使いこなしたい方向けの役立ち情報、関数活用術をご紹介しています。仕事でExcel使う方はぜひどうぞ!

合わせて読みたい Excelの便利な活用術・実践的な工夫・小技まとめ
当ブログではたびたび、他のサイトには載っていないような、Excelの便利な活用方法や、実践的な工夫・小技などを紹介しています。