Excel関数で順位付けする方法をお探しですね。
広告
Excelで順位を自動計算！RANK.EQ/RANK.AVG関数を使いこなそう
テストの点数や営業成績、売上金額などに順位をつけたいとき、手作業で並べ替えて順位を入力していませんか？実はこの方法、ミスが起こりやすいうえに、データが増えるたびに更新が必要で大変です。
そこで便利なのが、順位を自動で計算してくれるRANK.EQ関数とRANK.AVG関数。
この記事では、基本的な使い方から同順位の扱い方、エラーが出たときの対処法まで、初心者の方にも分かりやすく説明していきます。
RANK.EQ / RANK.AVG関数って何？順位を自動でつける便利な関数
ExcelのRANK.EQ関数とRANK.AVG関数は、指定した数値がリストの中で何番目なのかを教えてくれる関数です。
例えば、B列に生徒の点数が並んでいるとき、それぞれの点数がクラスで何位なのかを自動で表示できます。
売上ランキングや営業成績、在庫の回転率、アンケート集計、スポーツの記録など、数値を比較して順位を知りたい場面でとても役立ちます。
順位をつけるというと、データを大きい順や小さい順に並べ替える操作を思い浮かべるかもしれませんね。
でも、RANK.EQ関数やRANK.AVG関数なら、表の並び順はそのままで順位だけを計算できるんです。
元の一覧表の順番を変えずに「この人は何位」「この商品は何番目に売れている」と確認できるので、名簿順や登録順を崩したくない資料でも安心して使えます。
基本の書き方は、RANK.EQ関数もRANK.AVG関数も同じです。
「=RANK.EQ(数値,参照,[順序])」または「=RANK.AVG(数値,参照,[順序])」という形で入力します。
「数値」には順位を知りたいセル、「参照」には順位をつけたい数値の範囲全体、「順序」には大きい値を上位にするか、小さい値を上位にするかを指定します。
順序に0を入力するか、何も入力しないと降順になり、点数や売上のように大きいほど上位になります。
順序に1など0以外の数値を入力すると昇順になり、タイムやコストのように小さいほど上位になります。
ちなみに、Excelには古いバージョンから使われているRANK関数もあります。
RANK関数はRANK.EQ関数とほぼ同じ動きをしますが、Excel 2010以降ではRANK.EQとRANK.AVGに分かれているので、これから新しく表を作るならRANK.EQかRANK.AVGを使った方が分かりやすいですよ。
古いExcelとの互換性を気にする場合はRANK関数を見かけることもありますが、今のExcelで順位のつけ方を学ぶなら、まずRANK.EQとRANK.AVGの違いを理解しておくと実務で迷わなくなります。
RANK.EQ関数の使い方：降順・昇順で順位を出してみよう
RANK.EQ関数は、同じ値がある場合に同じ順位を返してくれる、一般的なランキング向けの関数です。
例えば、B2からB7に点数が入力されていて、C2にB2の点数の順位を表示したいときは「=RANK.EQ(B2,$B$2:$B$7,0)」と入力します。
これで、B2の点数がB2からB7の範囲内で何番目に高いかが計算されます。
最後の「0」は降順を意味するので、点数が高いほど1位になります。
ここで大切なのが、参照範囲を「$B$2:$B$7」のように絶対参照にすることです。
絶対参照とは、数式をコピーしても参照範囲がずれないように固定する方法のこと。
もし「B2:B7」のように相対参照のまま下にコピーすると、次の行では「B3:B8」のように範囲がずれてしまって、正しい順位が出なくなってしまいます。
順位をつける対象範囲は、基本的に全員分・全商品分の範囲で固定する必要があるので、RANK.EQ関数では絶対参照を使うのが鉄則です。
一方、マラソンのタイムや製造コスト、待ち時間のように「小さい数値ほど良い」データでは、昇順で順位をつけます。
この場合は、順序の部分に1を指定して「=RANK.EQ(B2,$B$2:$B$7,1)」のように入力しましょう。
降順と昇順を間違えると、上位と下位が逆転してしまうので、何を評価したいデータなのかを先に確認することが大事です。
売上や点数は降順、タイムや不良件数は昇順というように、指標の意味に合わせて使い分けてくださいね。
RANK.EQ関数は、データを並べ替えなくても順位を出せますが、順位の列を作ったあとにフィルターや並べ替えを使うと、ランキング表としてさらに見やすくなります。
例えば、C列に順位を表示したうえでC列を昇順に並べ替えれば、1位から順番に並んだ表が完成します。
また、条件付き書式を使って1位から3位までのセルに色をつけると、上位データがひと目で分かる資料になりますよ。
関数で正確な順位を出して、Excelの表示機能で見やすく整えるという流れを覚えると、実務で使える表に仕上げやすくなります。
RANK.AVG関数との違い：同順位をどう扱うかがカギ
RANK.EQ関数とRANK.AVG関数の一番の違いは、同じ値が複数あるときの順位の返し方です。
RANK.EQ関数では、同じ値には同じ順位がつきます。
例えば、100点が2人いて最上位の場合、どちらも1位となり、次の人は3位になります。
これは一般的な競技や成績表でよく使われる順位のつけ方で、「同率1位が2人いるから2位は存在しない」という考え方ですね。
一方、RANK.AVG関数は、同順位になる位置の平均順位を返します。
例えば、100点が2人いて本来なら1位と2位に相当する場合、RANK.AVG関数では両方に「1.5位」が返されます。
また、85点が2人いて本来なら4位と5位に相当する場合は、どちらも「4.5位」になります。
小数の順位が表示されるので、普通のランキング表では少し見慣れない印象になるかもしれませんが、統計的な分析や平均順位を扱いたい場面では便利です。
どちらを使うべきかは、表の目的によって決めましょう。
営業成績やテスト結果の一覧で、同点者を同じ順位として扱いたいならRANK.EQ関数が自然です。
順位の数字が飛んでも、同順位があることをはっきり示せるので、表彰や評価の資料にも向いています。
一方、複数の評価項目を集計して平均順位を使いたい場合や、統計処理として順位の平均を扱いたい場合はRANK.AVG関数が適しています。
見た目の分かりやすさを優先するのか、分析上の意味を優先するのかで使い分けるといいですよ。
また、同点でも必ず1位、2位、3位のように重複しない順位をつけたい場合、RANK.EQ関数だけでは対応できません。
その場合は、COUNTIF関数を組み合わせて登場順で差をつける方法があります。
例えば、C2に「=RANK.EQ(B2,$B$2:$B$7,0)+COUNTIF($B$2:B2,B2)-1」と入力して下にコピーすると、同じ点数が複数ある場合でも、先に登場したデータを上位として連番にできます。
ただし、この方法はあくまで表の上からの順番で差をつけるだけなので、実務では「同点の場合は利益率が高い方を上位」「同点の場合は提出日時が早い方を上位」など、納得できる補助基準を用意することが大切です。
実務で使うコツとエラーが出たときの対処法
RANK.EQ関数やRANK.AVG関数を実務で使うときは、数式そのものだけでなく、表の作り方にも気をつけるとミスを減らせます。
まず、順位を求める参照範囲は必ず正しく指定して、オートフィルでコピーする場合は絶対参照にしましょう。
数式バーで範囲を選択してF4キーを押すと、相対参照から絶対参照に切り替えられるので、手入力で「$」をつけるより簡単です。
順位の結果がおかしいと感じたときは、まず参照範囲がずれていないか確認してみてください。
空欄が混ざる表では、RANK関数がエラーになったり意図しない結果を返したりすることがあります。
まだ点数や売上が入力されていない行まで順位を表示しようとすると、見た目にも分かりにくい表になってしまいます。
この場合はIF関数を組み合わせて「=IF(B2=””,””,RANK.EQ(B2,$B$2:$B$7,0))」のように入力すると、B2が空欄のときは順位欄も空欄にできます。
さらに、参照範囲に目的の数値が含まれていない場合は「#N/A」、数値として扱えない文字列が混ざっている場合は「#VALUE!」が出ることがあるので、データの形式もチェックしましょう。
エラー表示を資料上で見せたくない場合は、IFERROR関数を使う方法もあります。
例えば「=IFERROR(RANK.EQ(B2,$B$2:$B$7,0),””)」とすれば、エラーが発生したときに空欄を表示できます。
ただし、IFERRORはエラーを隠すだけで、原因を解決する関数ではありません。
参照範囲の指定ミスや文字列の混入があるままでは、正しい分析ができないので、最終的には元データを整えることが重要です。
特に、見た目は数字でも実際には文字列として保存されているケースがあるので、左寄せになっている数値や先頭にアポストロフィがついたデータには注意してください。
データが今後増える表では、Excelのテーブル機能を使うと管理しやすくなります。
対象範囲を選択してCtrl＋Tでテーブル化すると、新しい行を追加したときに数式や参照範囲が自動で拡張されやすくなります。
毎月の売上データや定期テストの成績表のように、行が増えることが前提の資料では、テーブル化しておくことで範囲の修正漏れを防げます。
さらに、フィルターや並べ替えも使いやすくなるので、順位を出したあとに上位だけを抽出したり、部門別に確認したりする作業もスムーズです。
RANK.EQ関数とRANK.AVG関数は、使い方自体はシンプルですが、同順位の扱い方、降順・昇順の指定、絶対参照、空欄処理を理解しているかどうかで表の完成度が大きく変わります。
まずはRANK.EQで一般的な順位づけを作って、平均順位が必要な場面ではRANK.AVGを選ぶと覚えておくと実用的です。
必要に応じてIF関数、IFERROR関数、COUNTIF関数、条件付き書式、フィルターなどと組み合わせれば、単なる順位一覧ではなく、更新しやすくて見やすいランキング表を作れます。
Excelでデータに順位をつける作業を効率化したいなら、RANK.EQ / RANK.AVG関数は最初に身につけておきたい基本スキルです。
広告
