Excel。XLOOKUP関数をつかうと、楽に、上位3位の点数に該当する氏名を抽出できます。
＜SMALL+ROW関数&XLOOKUP関数＞
最高値を算出するには、MAX関数を使いますし、2位や3位など指定の順位を算出するには、LARGE関数をつかいます。
ただ、困ったことに、数値そのものしか算出することができません。
どういうことかというと、次の表があります。
AB列の表は、ある競技の時間の表です。
上位3位までのタイムのいい人を抽出したいわけです。
作業としては、最初にD列を算出します。
数値が小さい上位3名までなので、SMALL関数を使えばOKです。
D2の数式を確認してみましょう。
=SMALL($B$2:$B$11,ROW(A1))
引数のROW(A1)は何を意味似ているのかというと、ROW(A1)としないで、1位を算出したいので、「1」と設定したら、オートフィルで数式をコピーしても、「1」のままなので、「2」「3」と修正しないといけません。
ROW関数をつかって、数式を修正する手間を省いています。
SMALL関数を使うことで、上位1～3位までのデータを算出することはできるのですが、そのデータの他のフィールドを抽出することは、SMALL関数ではできません。
垂直の表になっているので、VLOOKUP関数を使えば、抽出することができるのではと考えるかもしれませんが、残念ながら、今回のような表からでは、VLOOKUP関数で、別のフィールドのデータを抽出することはできません。
なぜならば、VLOOKUP関数は、抽出する範囲の一番左側が、検索値でなければなりません。
つまり、今回は、参加者・時間という並びになっていますので、VLOOKUP関数が使えないわけです。
時間・参加者ならばVLOOKUP関数が使えます。
このような表に対応するには、INDEX+MATCH関数をつかうことで、抽出することはできるのですが、なかなか面倒な関数の組み合わせです。
ところが、近年追加された「XLOOKUP関数」を使うと、とても簡単に問題を解決してデータを抽出することができます。
E2をクリックして、XLOOKUP関数をつかった数式を作っていきます。
なお、XLOOKUP関数ですが、XLOOKUP関数ダイアログボックスで数式をつくると、どのように引数を設定したらわかりにくいので、出来ることならば、手入力で数式をつくるほうがいいように思えます。
なので、今回は、手入力で、XLOOKUP関数を作っていきます。
最初は、検索値ですが、これは、D2の時間が該当しますので、「D2」と入力したいところですが、Office365のExcelやXLOOKUP関数が使えるようになったExcel2019には、「スピル機能」があるので、「D2:D4」と入力します。
検索範囲ですが、検索値が所属しているフィールド（列）ですから、B2:B11。
オートフィルで数式をコピーするのではなく、「スピル機能」によって数式がコピー（スピル）されますので、絶対参照は不要です。
戻り範囲ですが、これは、抽出したいものですから、参加者の列ですね。
A2:A11と入力します。
ここも、絶対参照は不要です。
見つからない場合は、空白にしたいので「””（ダブルコーテーション×2）」
一致モードは、完全一致なので「0」
検索モードは、データの上部から下部へと検索しますので「1」
Enterキーで数式を確定すると、スピル機能によって、数式が設定されます。
E2の数式は、
=XLOOKUP(D2:D4,B2:B11,A2:A11,"",0,1)
です。
このように、XLOOKUP関数は、VLOOKUP関数よりも、融通がききやすいので、使ってみると便利です。
なお、「スピル機能」がよくわからない場合は、旧来通り、絶対参照をつかった数式をつくり、オートフィルで数式をコピーしてもOKですよ。
その場合の数式は、
=XLOOKUP(D2,$B$2:$B$11,$A$2:$A$11,"",0,1)
です。
新しく加わった機能や関数を、取り入れてみると、改善できるものとかあるかもしれませんね。
