Excel。XLOOKUP関数だと簡単！降順の表から検索するならINDEX+MATCH関数の苦労から解放?!
＜INDEX+MATCH関数・XLOOKUP関数＞
次のような合計値に応じてランクを決定する表があります。
数値をもとにして、ランクの表から検索するので、「VLOOKUP関数」を使うように思われますが、このケース。
VLOOKUP関数では、対応することができません。
C2:C10を一度クリアして、VLOOKUP関数で算出してみましょう。
C2には、
=VLOOKUP(B2,$F$2:$G$7,1,TRUE)
という数式になるはずです。
しかし、「#N/A」というエラーが表示されてしまいます。
検索値は、B2だし、範囲も問題ない。
抽出したいのは、ランクなので、列番号は「1」。
完全一致ではなく、近似値なので、「TRUE」と大丈夫なような気がしますが、VLOOKUP関数のルールを逸脱しています。
１つ目は、
検索値の左側に抽出したいデータがある。
今回はG列の合計の左にランクがある。
抽出したいものが検索値の右側にないとVLOOKUP関数を使うことができません。
では、F列とG列を入れ替えればいいのかというと…
C2の数式も合わせて次のように修正します。
=VLOOKUP(B2,$F$2:$G$7,2,TRUE)
オートフィルを使って数式をコピーしたところ、エラーのセルがあります。
実は、まだVLOOKUP関数のルールから逸脱しています。
2つ目として、範囲のF2:G7の表ですが、今回のような数値の場合は、
【昇順】でなければいけません。
商品コードや氏名の場合は、ルール上よろしくありませんが、完全一致のケースが多く、検索されます。
なので、次のようにF列を昇順にすると、問題なく検索してくれます。
しかしながら、通常数値の高い順でリストをつくるだろうし、Sが何点以上というように表を閲覧するはずです。
よって、最初の表では、VLOOKUP関数を使うことはできないわけです。
では、どのようにしたらいいのかというと、このケースでの定番「
INDEX+MATCH関数」をつかうことで、検索することができます。
さらに、F1:G7の表だけではINDEX+MATCH関数でも、検索することはできません。
H列の「～まで」という数値を用意する必要があります。
B2に、INDEX+MATCH関数で数式を作ってみましょう。
=INDEX($F$2:$F$7,MATCH(B2,$H$2:$H$7,-1),1)
これで、検索することができるのですが、数式が非常に「難解」ですね。
数式の説明をしてきます。
MATCH関数がどのような動きをしているのか確認してみましょう。
D2の数式は、
=MATCH(B2,$I$2:$I$7,-1)
検索値は、B2で198を指します。
検索範囲は、$I$2:$I$7
照合は、「-1」とします。「-1」は、検索値以上の最小値を検索します。
すると、上から3コ目が該当するので、3と算出されました。
それを受けて、E2のINDEX関数を確認しましょう。
=INDEX($G$2:$G$7,D2)
G2:G7の範囲で、上から、D2、つまり、3番目を検索するので、「B」を検索してくれるわけです。
このように、検索する表をそのまま使用して、検索することは容易ではありませんでした。
しかし、Office365のExcel Insiderで搭載された、「スピル」そして、新しく登場した【
XLOOKUP関数】をつかうと、INDEX+MATCH関数のような苦労はせずにすむようになりました。
D2にXLOOKUP関数の計算式をつくっていきます。
=XLOOKUP(B2:B10,G2:G7,F2:F7,,-1,1)
これで、あっという間に算出してくれます。
範囲の表が、どうのこうのというような条件はありません。
XLOOKUP関数は、スピル機能を使う関数なので、オートフィルを使って数式をコピーする必要はありません。
また、絶対参照で範囲の表を固定する必要もありません。
それでは、数式を説明しましょう。
まず、XLOOKUP関数は、
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
という形式になっています。
検索値は、B2:B10。
合計点がどのランクなのか検索するので、B2:B10と設定します。
検索範囲は、G2:G7。検索値に該当するのを検索する範囲です。
戻り範囲は、F2:F7。検索結果の範囲です。
このように、検索範囲と戻り範囲を別々に設定できるようになったので、検索範囲を範囲の表の一列目にないといけないというルールから解放されたわけです。
見つからない場合は、今回はないので、省略。
一致モードは、完全一致かどうか決めるところです。
VLOOKUP関数では省略すると、「近似値」になってしまいましたが、XLOOKUP関数では、省略すると「完全一致」になります。
今回は、完全一致または、次に小さい項目の「-1」を設定します。
1件目は198なので、198の次に小さい項目は、190が該当しますので、Bを検索するわけです。
最後の、
検索モードは、リストの上から検索させるので、先頭から末尾へ検索の「1」を選択します。
この検索モードによって、VLOOKUP関数だと、範囲の表を昇順にしておかないといけないというルールから解放されました。
このように、XLOOKUP関数は、現場で大きなインパクトを与える関数になるのかもしれませんね。
