今回は、順位（ランキング）を調べるときに役立つ関数について紹介していこう。データの「並べ替え」により順位を求める方法もあるが、同じ数値データが複数あると、少しだけ厄介な問題が発生する。こういった状況にも対応できるように関数RANKの使い方を覚えておくとよい。あわせてLARGEやSMALLといった関数の使い方も紹介しておこう。

ランキング（順位）を求めるオーソドックスな方法

数値の大きい順（または小さい順）に順位を付けて、ランキング形式の表を作成したい場合もあるだろう。そこで今回は、順位（ランキング）に関連する関数として、RANK、LARGE、SMALLといった関数の使い方を紹介していこう。

  • 順位を求める関数RANKとLARGE、SMALLの使い方

ここでは、令和5年に警察庁が調査した「自転車のヘルメット着用率」をもとに各都道府県のランキングを作成していく。

出典：警察庁「令和5年秋の全国交通安全運動の実施について」

  • 自転車用ヘルメット着用率の調査結果

まずは、関数を使わずにランキング表を作成してみる。この場合は「着用率」の大きい順にデータを並べ替えればよい。

  • 「着用率」の大きい順に並べ替え

続いて、「順位」の列に「1」、「2」の数値データを入力し、これらをオートフィルでコピーする。

  • 順位（1、2）を入力し、オートフィルでコピー

すると、「1、2、3、・・・」の連番データを作成することができる。これを各都道府県の「順位」とみなせばよい。

  • 各都道府県に順位を付けた表

たいていの場合、この方法で問題なくランキング表を作成できるが、状況によっては不具合が生じるケースもある。

今回の例をよく見ていくと、静岡県と鹿児島県の着用率はどちらも10.6％なのに、そのランキングは19位、20位という異なる順位になっていることに気付くと思う。

  • 同じ数値なのに順位が異なる不具合

本来であれば、両県とも「19位タイ」として扱うべきだが、残念ながら、そうはなっていない。順位を「連番データ」で付けた際に、起こるべくして起こる不具合といえるだろう。

ほかにも、東京都、京都府、長崎県（21位～23位）、奈良県、熊本県（28～29位）、北海道、千葉県（39～40位）など、「▲▲位タイ」として扱うべきデータが散見される。

47都道府県のように数十件程度のデータであれば、これらの不具合を手作業で修正していくことも不可能ではない。しかし、数百件、数千件という規模になると、そうもいかない。このような場合に備えて、関数RANKで順位を求める方法も覚えておくとよい。

関数RANKでランキング（順位）を求める

ということで、関数RANKの使い方を説明していこう。関数RANKの第1引数には「順位を調べたい数値」をセル参照などで指定する。続いて、すべての数値データが入力されている「セル範囲」を第2引数に指定する。

◆関数RANKの書式
=RANK(数値, 範囲, [順序])

第3引数は省略しても構わない。この場合、最も大きな数値を1位として、数値の大きい順に並べたときの順位が返される。なお、「100m走のタイム」などのように小さい数値ほど好成績なデータの順位を調べたいときは、第3引数に0（ゼロ）以外の値を指定しておく必要がある。

「自転車のヘルメット着用率」に話を戻して具体的な例を紹介していこう。今回の例では、D5～D51のセル範囲に「着用率」のデータが入力されている。よって、第2引数は「D5:D51」となる。オートフィルでコピーした際にセル範囲が変化しないように、この部分は絶対参照で指定するのが基本だ。第1引数には、順位を調べたい数値（D5セル）を指定する。

  • 関数RANKの入力

あとは、この関数RANKをオートフィルでコピーするだけ。これで各都道府県の順位を求められる。今回の例は、あらかじめ「着用率」の大きい順にデータを並べ替えてあるため、「1、2、3、・・・」という数値（順位）が並ぶことになる。

  • 関数RANKをオートフィルでコピーした様子

念のため、先ほど不具合が生じていた部分について詳しく見ていこう。着用率が10.6％の静岡県と鹿児島県は、どちらも「19位タイ」という結果になっている。

  • 数値が同じデータの順位

同様に、東京都、京都府、長崎県は「21位タイ」、奈良県、熊本県は「28位タイ」、北海道、千葉県は「39位タイ」という結果になっている。

このように関数RANKを使って順位を求めると、同じ数値データを「▲▲位タイ」として扱うことが可能となる。つまり、「連番データ」で順位を付けたときの不具合を解消できる訳だ。順位を求める際に役立つ関数として覚えておくとよいだろう。

なお、データを大きい順（または小さい順）に並べ替えていなかった場合でも、関数RANKで順位を求めることが可能だ。

  • データを並べ替えずに関数RANKで順位を求めた場合

このような利点があることも、あわせて覚えておくとよい。

関数LARGE、SMALLで各順位の数値を取得する

続いては、関数LARGEと関数SMALLの使い方を紹介していこう。LARGEは「指定した範囲の中でN番目に大きいデータ」、SMALLは「指定した範囲の中でN番目に小さいデータ」を取得する関数となる。

◆関数LARGEの書式
=LARGE(範囲, 順位)

◆関数SMALLの書式
=SMALL(範囲, 順位)

いずれも、第1引数に「セル範囲」、第2引数にN番目の「数値」を指定すればよい。これらの関数を使って「ベスト5」や「ワースト5」などの表を作成することも可能だ。

たとえば、「1番目に大きいデータ」を取得するときは、以下の図のように関数LARGEを記述すればよい。オートフィルで関数をコピーしたときに「セル範囲」が変化しないように、第1引数は絶対参照で指定している。

  • 関数LARGEの入力

今回の例では、N番目（第2引数）をセル参照で指定しているので、この関数LARGEをコピーすると、着用率「ベスト5」の数値を自動取得できる。

  • 関数LARGEをオートフィルでコピーでコピーした様子

同様に、関数SMALLを使って着用率「ワースト5」を自動取得することも可能だ。

  • 関数SMALLの入力

  • 関数SMALLをオートフィルでコピーでコピーした様子

ただし、取得されるのは「着用率」の数値データだけで、「どの都道府県か？」までは把握できない。都道府県のデータを取得するには、LOOKUP系の関数などを使用する必要がある。

各順位に対応する都道府県名を取得するには？

ということで、続いては「ベスト5」や「ワースト5」に該当する「都道府県」を取得していこう。LOOKUP系の関数の使い方を知っていれば、これは特に難しい問題ではないはずだ。

たとえば、着用率ベスト1の59.9％（H5セル）に該当する「都道府県」をXLOOKUPで取得するときは、以下のように関数を記述すればよい。

  • 関数XLOOKUPの入力

同様に、各数値に該当する「都道府県」をXLOOKUPで取得していくと、以下の図のような結果が得られる。

  • 関数XLOOKUPをオートフィルでコピーでコピーした様子

これで状況を把握しやすいランキング表にしあげることができた。ただし、このときも「▲▲位タイ」のデータが存在している可能性に注意しなければならない。試しに、同様の手順で「ワースト10」までデータを取得してみると、ワースト8位と9位がどちらも「北海道」になってしまう、という不具合が発生する。

  • ワースト10位まで取得した場合

これは、LOOKUP系関数の仕様によるものだ。LOOKUP系の関数は「最初に見つかった該当データを1つだけ取得する」という仕様になっている。着用率が6.4％の都道府県を上から順番に探していくと、最初に「北海道」が該当する。よって、ワースト8位は「北海道」となる。ワースト9位も着用率は6.4％なので、それに該当する都道府県は「北海道」となる。

このような不具合を避けるために、関数FILTERを使って「都道府県」を取得するのも一つの有効な手段となる。

  • 関数FILTERを使った「都道府県」の取得

関数FILTERは、指定した条件に合うデータをすべて取得できるのが特徴だ。ワースト8位の「着用率6.4％」で検索した場合、「北海道」と「千葉県」の両方が取得されることになる。これらデータを表示するには2つのセルが必要になるので、I20セルの結果が「#スピル!」のエラーになる。I21セルも同様だ。

  • 関数FILTERをオートフィルでコピーでコピーした様子

このように「#スピル!」のエラーが発生したときは、下側にある関数FILTERを削除してあげればよい。

今回の例では、I21セルに入力されている関数FILTERを削除すると、あふれたデータを表示する「空白セル」が確保され、2件目の該当データとなる「千葉県」を表示できる。それにあわせてワーストの順位を「9」→「8」に修正すると、正しいランキング表に仕上げられる。

  • 順位タイへの対応

関数FILTERが実装されているExcel 2021（またはMicrosoft 365）でしか使えないテクニックとなるが、このような手法があることも覚えておくと役に立つだろう。