VLOOKUP関数の課題を作っていて思ったのですけど、近似値一致のVLOOKUP関数って、元の表って初めから出来ているケースって限りなく少ないと思うのです。
例えば試験問題や、テキストだったらはじめから用意されていますけど、それってもしかして初めから自分で作るのがいいのではないかと思いました。
VLOOKUPの近似値一致のおさらい
VLOOKUP関数では、検索値を完全一致だけではなく、近似値一致で検索することができます。
例えば購入金額に対する付与ポイントや、点数に対する評価を求めるときに役立ちます。
近似値と言いますが、これは近い値ではなく、検索値よりも小さい数字で一番近い値です。
なので、次の表で値99を検索した場合、100ではなく、50のBになります。
書式はほぼ完全一致の場合と同じなのですが、一番最後だけFALSEではなく、TRUEになります。
=VLOOKUP(検索値,一覧表範囲,列番号,TRUE)
元表ははじめからあるのか
結局近似値一致させるための表は、いろいろ制限があります。
- VLOOKUP関数なので検索される範囲が最左列になければいけない
- 近似値一致の場合は検索値は昇順に並んでいなければならない
そして最も大事なのは、
- どこで区切るかは自分で設定するもの
ということです。
このようなことを総合的に考えると、近似値一致のVLOOKUP関数を覚えたというのは、この元表まで作れてはじめて言える話なのかなと思いました。
元表の作り方
そこで、元表の作り方ですが、いくつかのコツがあるということに気付きました。
例えば、10未満はA、20未満はB、30未満はCとします。
この場合、次のようになります。
10未満がAと言ってるのに、0から始まっています。
そしてその10ではBになっています。
このように、0からはじめて実際の日本語と一つずれて作る必要があります。
また、この表だと、日本語では30未満までしか規定されていないのですが、実際には20以上が入力されたらCになります。
30という設定がなくなってしまうのですね。
ここが間違えやすいポイントです。
では30未満の設定をするにはどうしたらいいのでしょう。
一番下に30以上の場合に出力する値を書く必要があります。
しかし、その値は規定はされていないので作りようがありません。
まず、そのオーバーした場合の値を考えるところから始めないといけません。
この場合、なにがいけないかというと、初めの日本語の設定の仕方なのです。
「10未満はA、20未満はB、30未満はCとします」
これは、「0以上はA、10以上はA、20以上はB、30以上はC」とする必要があるのです。
このように、近似値のVLOOKUP関数の一覧表は、未満ではなく以上で考えていくことが大事です。
また、大きな値から設定したくなることもあるでしょう。
80点以上は優秀、60点以上は可、40点以上は再試験、それ未満は不合格のように。
この場合も、はじめに日本語で小さい方から0をスタートにして、以上で組み立てていきます。
0以上は不合格、40点以上は再試験、60点以上は可、80点以上は優秀。
おそらく点数は100点が最大なので、80点から100点までが優秀となります。
まとめ
VLOOKUP関数の近似値一致の元表は自分でちゃんともとの日本語から作った方がいいというお話をしました。
作り方は、
- 日本語で「以上」で0スタートで小さい方から考える。
- それに合わせて表を作成する。
これだけです。
VLOOKUP関数の近似値一致自体があまり教えられていないような気もするのですが、教えている場合でも元表ありきで教えているので、そうではなく元表の設計からできるように教えないと覚えたことにならないのはないかと思いました。
近似値一致のVLOOKUP関数の一番勉強になるのは、
https://www.nta.go.jp/publication/pamph/gensen/zeigakuhyo2018/01.htm
このサイトにある、給与所得の源泉徴収税額表（月額表）を使って給与に対する源泉徴収額を求める方法だとおもうのですが、この表がまたVLOOKUP関数でそのまま使えないような形になっているので、どう変更したらいいかというのも考えながらやってみるといいかもしれません。
コメント