重複している数値の順位を調べる
下図のような表があったとします。A列にはランダムな数値が入力されています。
A列の数値から「1番大きい数値」「2番目に大きい数値」「3番目に大きい数値」を調べてみましょう。なお、表記を統一したいので、これ以降「1番大きい数値」のことを「1番目に大きい数値」と呼びます。1番目に大きい数値を調べるにはMAX関数を使いますが、MAX関数で分かるのは「1番目に大きい数値」だけです。今回のように、2番目、3番目を調べることはできません。こんなときは、LARGE関数の出番です。LARGE関数は、引数に指定した「○番目に大きい数値」を調べられます。
ちなみに、LARGE関数の第2引数は「1→2→3」と変化させなければなりません。今回は3つですから手で修正するのも容易ですが、数が多いと煩雑です。こんなとき「1→2→3」を手入力するのではなく、自動的に変化させる方法をご紹介します。いろいろなやり方がありますけど、どれがいい、という話ではありません。ケースに応じて使い分けます。つまり、全部思いつくのがベストです。
「1→2→3」をセルに入力しておく方法
「1→2→3」があらかじめ、セルに入っていたら簡単です。だったら入れちゃいましょう。
こうなっていたら、LARGE関数の第2引数は、C列のセルを参照するだけです
でも、これだとC列が殺風景ですね。やはりここは「○番目の数値」と表示したいところ。でも、セルには数値を入れておきたい。だったら、表示形式を設定しましょう。
ROW関数を使う方法
ROW関数は、引数に何も指定しないと、そのROW関数が入力されているセルの"行番号"を返します。
「はぁ？そんなの何に使うの？」って思った方は、おそらくExcelのビギナーなのでしょう。このROW関数って、めっちゃくちゃ使うんですよ。経験のないあなたにはピンと来ないかもしれませんけど、いずれ大人になったら分かります。今回の「1→2→3」にも、このROW関数を使います。
ただし注意が必要です。「1番目に大きい数値」を調べるセルD2は"2行目"です。このままでは「2→3→4」となってしまいますので、ROW関数の結果から1を引きます。
なお、お勧めはしませんが、ROW関数を使って、次のようにトリッキーな方法も可能です。
スピルが使える環境でしたら、普通に入力するだけです。なお、上図はROW関数がスピってるので、LARGE関数で調べるセル範囲(ここではA2:A10)を絶対参照にする必要はありません。だって、数式をコピーしていないのですから。スピルに関しては、下記ページをご覧ください。
スピルが使えないExcelでしたら、あらかじめセル範囲D2:D4を選択してから、Ctrl + Shift + Enterで配列数式にしてください。
SEQUENCE関数を使う方法
ProPlusでしたら、SEQUENCE関数を使う手もあります。SEQUENCE関数は、指定した連番を返す関数です。
SEQUENCE関数に関しては、また別の機会に詳しく解説します。
数値が重複していたら
さて「○番目に大きい数値」を調べる話に戻りましょう。先のケースは、LARGE関数で上手くいきました。なぜなら「すべての数値が異なっていた」からです。しかし、実務のデータは、必ずしもそうとは限りません。下図のように、いくつかの数値が重複していたらどうでしょう。
先と同じようにLARGE関数を使ってみましょう。なお、数式の記述を簡素化するために、セル範囲A1:A10はテーブルにしています。テーブルの名前は「Data」です。また、セル範囲C2:C4は、実際には数値の「1」「2」「3」を入力しており、上でご紹介したように表示形式を設定しています。LARGE関数の結果は、次のとおりです。
1番目に大きい数値だけでなく、2番目も、3番目も、すべて90となりました。つまりLARGE関数は3つの「90」「90」「90」を、それぞれ"第1位""第2位""第3位"と判定したわけです。90の次に大きい数値である「80」は"第2位"ではなく、"第4位"となりました。オリンピックなどの競技で、同じ成績の選手が3人いたので、3人とも金メダルになったようなものです。このとき、次成績の選手は銀メダルではなく"第4位"です。
なるほど、オリンピックなどの順位だったら、ある意味これも納得です。しかし実務ではこういうとき、
1番目に大きい数値→90
2番目に大きい数値→80
3番目に大きい数値→70
としたいケースもあります。
さあ困りました。LARGE関数だけでは調べることができませんね。ここでは、やり方を2つご紹介します。
まず、ひとつめの方法。そもそも数値が重複しているから、こんなことになるんです。このリストから「重複を削除した、重複していないユニークリスト」を作成できれば、そのリストに対してLARGE関数を使うことで、「90→80→70」という望む結果を得られます。
ユニークリストを作るには、UNIQUE関数が便利です。UNIQUE関数は、ProPlusのExcelで追加されました。
UNIQUE関数の結果をLARGE関数に渡します。
もし、UNIQUE関数が実装されていないバージョンのExcelを使っているのでしたら、しかたありません、何とかがんばります。ただ、そうなると一発で求めることはできません。作業セルを使います。今回はB列を使いましょう。まず、A列に入力されている数値が「重複しているかどうか」を調べます。セルB2に「=COUNTIF($A$2:A2,A2)」と入力して、下方向にコピーします。実行すると、A列に入力されている数値が何個あるかが分かります。B列に表示されているのが、その個数です。
この件に関しては、下記のページで仕組みを詳しく解説しているので、そちらをご覧ください。
さて、こうして調べた数値の個数が、2や3など、つまり「1でなかったら」それは、2番目や3番目に登場した数値、つまり「重複している数値」ということです。今回欲しいのは、重複していないユニークリストです。ですから、B列の数値が1だったらA列を参照し、そうでなかったら何も表示しません。
このB列を、LARGE関数で調べます。
UNIQUE関数が実装される前は、こうして調べるしか手がありませんでした。本当にUNIQUE関数は便利です。まだUNIQUE関数が使えない方も、悲観することはありません。そのうち使えるようになりますよ。時間の問題です。以下はオマケです。UNIQUE関数だと作業セルを使わないで、重複しない順位を一発で求められます。ということは、これを条件付き書式に応用することも可能です。
上図は、3番目に大きい数値以上を赤色にしています。条件付き書式の条件には、構造化参照が使えないので、アドレスを記述しました。