Excel。VLOOKUP関数の盲点。降順の以上の範囲では検索できないのでどうしよう。
＜VLOOKUP関数とINDEX+MATCH関数＞
多分岐処理をしたいときに、IF関数のネストではネストが多くなりすぎて、VLOOKUP関数を使うやり方があるのですが、ある点に注意しないと、
算出することができません。
次の表があります。
点数によって、ランク分けしたいわけですね。
ランク分けの表は同じシートに用意しました。
A列は、表示形式のユーザー定義を使って、
”以上”を表示できるようにしています。
ただし、点数が降順になっていますね。
D2をクリックしてVLOOKUP関数ダイアログボックスを表示しましょう。
検索値は、C2
それぞれの点数ですね。この点数がどのランクなのかを知りたいわけですね。
範囲には、$A$14:$B$18
ランク表ですね。見出し行は含まないで範囲選択します。
また、オートフィルで数式をコピーしますので、
絶対参照も忘れずに設定しましょう。
列番号は、2
範囲で選択した列で、検索したい列は左から2列目にありますので、2。
検索方法は、TRUE。または、1でもOKですね。
完全一致ではなくて、「内輪の数」なので、TRUEを使います。
それでは、OKボタンをクリックして、
オートフィルを使って数式をコピーしてみましょう。
なんと、検索がうまくできていませんね。
これは、範囲で選択したランク表が降順だったのが原因なのです。
検索範囲の表は、昇順でないとVLOOKUP関数は機能してくれません。
念のため、昇順の表を使ってみたらどうなるのでしょうか？
D13:E18に昇順にしたランク表を用意しました。
D列は、先ほどと同様に、表示形式のユーザー定義を使って、
”以上”と表示させています。
では、E2にVLOOKUP関数ダイアログボックスを表示して数式を作っていきます。
範囲が、$D$14:$E$18になっているだけで、先ほどと変わっていませんね。
E2の数式は、
=VLOOKUP(C2,$D$14:$E$18,2,TRUE)
すると、昇順にした範囲だと、問題なくランクを検出してくれていますね。
ということで、VLOOKUP関数で多分岐処理を行う場合には、
範囲の表を昇順にしておくことを忘れないようにしましょう。
となるのですが、もし、降順の表の場合はどうしたらいいのでしょうか？
【範囲が降順の表】
範囲の表が降順だった場合ですが、残念ながらVLOOKUP関数は使えませんので、別の方法でトライしていきます。
A13:B18の表を、「以上」という表から、80以上ではなくて、
80以上100以下とか0以上19以下というように、
その区間を表すようにアレンジしていきます。
なんで、このC列を用意する必要があるのかというと、
点数がどこの範囲に該当しているのかを見つけるために、
MATCH関数を使うからです。
このMATCH関数の引数に昇順と降順が影響する箇所があるので、
今回は降順の表のため、C列を用意する必要があるのです。
では、わかりやすいようにMATCH関数をF2に作ってきます。
MATCH関数ダイアログボックスでもいいのですが、
意味を確認したいので、手入力で数式を作ります。
検索値は、C2
検査範囲は、C14:C18。絶対参照も忘れないようにしましょう。
そして、ここがポイントの照合の種類
1の以下は、検査範囲が昇順で配置しておく必要があるので、
今回は、「-1以上」を使わないといけません。
検索値以上とするは、C列が必要だったわけです。
F2の数式は
=MATCH(C2,$C$14:$C$18,-1)
オートフィルを使って数式をコピーしてみましょう。
最後は、算出されたMATCH関数を元に、
INDEX関数を使ってD2:D11にランクを算出していきます。
D2をクリックして、INDEX関数ダイアログボックスを表示していくと、
引数の選択ダイアログボックスが表示されますので、領域番号は不要なので、
『配列,行番号,列番号』を選択してOKボタンをクリックしましょう。
INDEX関数ダイアログボックスが表示されますので、
引数を設定していきましょう。
配列は、$B$14:$B$18
配列といっても、抽出したいランクがある範囲ですね。
行番号は、F2
MATCH関数で算出した結果を使っています。
配列で選択した範囲の中の行を見に行くという指示をしています。
列番号は、1
配列の範囲が1列なので、省略してもいいのですが、
わかりやすいように1列目を見に行くという指示をしています。
つまり、配列の範囲の、1列F2行のデータを抽出するという意味になります。
それでは、OKボタンをクリックしましょう。
このように、降順の表でも算出することができましたね。
なお、D2の数式は、
=INDEX($B$14:$B$18,F2,1)
となっていますが、今回はわかりやすいように、
MATCH関数をF列に事前に算出させていますが、
当然、一つにまとめることができます。まとめた数式は、
=INDEX($B$14:$B$18,MATCH(C2,$C$14:$C$18,-1),1)
確かに降順の表を使っても抽出することはできますが、
昇順の表を用意するほうが楽な気もしますので、
どうしても、昇順の表が用意できない場合は、
このような方法もある程度でいいような気もしますね。
