エクセルには、表から値を探し出し、その右側にある内容を表示する事ができる、VLOOKUP関数というものがあります。（下記記事参照）
そしてこのVLOOKUP関数には、注意書きがあります。あまり気にしてる人は多くないと思いますが。
テーブルは昇順で並べ替えておく必要があります。
なぜこのような注意書きがあるのかというと、VLOOKUP関数が表から値を探す際、表の上から順に探しにいく。という動きをしているためです。
どういう事なのかを実験しつつ、どういった問題が起こりうるのかなども考えてみましょう。
また、VLOOKUP関数の式は=VLOOKUP(検索値,範囲,列番号,検索方法)となっており、検索方法にはTRUE（完全一致）とFALSE（近似値）の2種類がありますので、それぞれの特徴についてもお話していきます。
検索方法FALSE（完全一致）
VLOOKUP関数を使う際、多くの場合はこのFALSE（完全一致）を使うことになるでしょう。
なので、まずはこのFALSEの動作について実験していきます。
FALSEの挙動
検索値を表の上から探し出して、その右側にある値を結果とします。
どういう事が順を追ってみていきましょう。
まずは普通に使ってみます。
表からセイウチ太郎を探してエサ代を表示するという、いたってシンプルな使い方ですね。
次は、セイウチ太郎を探す際、本当に表の上から順に探しているのかを実験してみましょう。
どういう実験かというと、検索先の表に、複数のセイウチ太郎を用意し、何番目のセイウチ太郎が選ばれるのかという内容。
結果は、表にセイウチ太郎が複数名いても、VLOOKUP関数が拾ってくるセイウチ太郎は、上から探して1番目に見つけたものでした。
昇順で並べなくてはいけない？
注意書きにあった、テーブルは昇順で並べ替えておく必要があります。の一文についてですが、仕組みさえわかっていれば、FALSE（完全一致）での検索の場合は、絶対順守ではありません。
むしろ下図のように、使い方次第では昇順の逆、降順にする事で有効活用できる場合もあります。
このケースで年月を昇順にしてしまったら、逆に一番古い情報、2022/9の4,000,000円という結果になってしまいます。
このように、仕組みさえわかっていれば、絶対に昇順にしなくてはいけない・・・というわけじゃないって事ですね。
ただしこれは完全一致で検索した場合のお話で、近似値検索になるとまた話は変わってきます。
検索方法TRUE（近似値）
ちょっと隠れがちな存在ですが、覚えていると、いざという時にとても便利な近似値検索について。
TRUEの挙動
基本的な考えは同じで、検索値を、表の上から順に探しだすという動きをします。
まず完全一致の時と同様、検索値と同じ値がちゃんと表にもある場合。
続いて近似値を活かすために、表中に存在しない値を探す場合。
たとえば61点を近似値検索するとどうなるか試してみましょう。
60点のがんばれが結果になりました。
61の近似値という事で、一番近い60点が選ばれたのかな・・・と思われそうですが、実際は違います。
どういう事かを試すために、60に近い61ではなく、80に近い79を検索してみましょう。
79が一番近い表中の数値は80ですが、80点のもういっぽは選ばれず、60点のがんばれが結果となりました。
なぜこうなるのかは、上から順に探し出すという動きが関わってきます。
エクセルがどういう挙動をしてこの結果になったのか、順を追ってみていきましょう。
ここでVLOOKUP関数の注意書きにある、テーブルは昇順で並べ替えておく必要があります。という一文が大きく影響してきます。
ここまでの時点でエクセルとしては、79を上から探していたら80を見つけた。小さい順に並んでいるのだから、この先に79は存在しない。と判断します。
そして最後に、なぜ60点が79点の近似値と判断されたのかというと・・・
80の先にはもう79は存在しない。
つまり79の近似値は80よりも手前にいるはず・・・
そうか60点！お前が79点の近似値だな！
となります。
えぇ・・・って思う人もいるでしょうが、しょうがないじゃない、そう動いてるんだもの。
試しに昇順ソートを崩して、80点と60点を入れ替えてみましょう。
ほら、80を見つけた1つ手前の0点を近似値として拾ってきたのだから、間違いないです。
理解していれば便利
このように、検索方法TRUE（近似一致）の場合、ちょっとクセの強い動きをします。
ですが、仕組みさえわかってしまえば、けっこう実用的なんです。
実際にここまでの例で使ってきた表がまさに、点数次第で評価を分けるというもので、私自身、これに近い使い方をよくしています。
わざわざIF関数で、59以下ならC、79以下ならB・・・みたいに、やたらと長い式組んでる人もいますからね。
近似値検索の場合は昇順ソートをすべき
完全一致の場合は、昇順ソートは絶対に必要というわけではないと書きました。
ただし近似値検索の場合、表の上から検索値と一致または検索値より大きい値を探し、大きい値の場合は一つ手前を近似値とする。という挙動をしているため、何か意図が無い限り、昇順ソートは必須と言えます。
誤って降順ソートしてしまうと、全く思った結果にならないという事も。
ね？
数値以外の場合も基本は同じ
検索値が数値の場合はわかりやすいのですが、それ以外の場合も考え方は同じです。
Bより大きいのはCだから手前のAを拾うみたいな。
セイウチ太郎（本 よりセイウチ太郎（本名）の方が大きいから手前のセイウチ太郎（偽名）を拾うみたいな。
まとめ
検索方法がFALSEの時は状況次第、TRUEの時は昇順ソートしよう。
コメント