【Excel/関数】大量データのVLOOKUPをウソみたいに早くする方法

大量データでVLOOKUPをかけると、スペックが信じられないくらい低い会社のパソコンだと「再計算〇％・・・」でトイレに行って戻ってきてもまだ終わってない。。。なんてことが。

ところがVLOOKUPで近似値を使うと、えっ？ウソ？と思うくらい一瞬で終わる、ということがわかりました！これはすばらしー。
※スペックの高いパソコンの場合、あんまりかわらないかも。スペックが高いかどうかわからないけど自宅のパソコンでやったら、100万行のVLOOKUPでも2分ほどで終わり、近似値を使ったやり方でも変わらなかった。
遅い。。。とお悩みの方は試してみてください！

１．VLOOKUPの近似値とは

そもそもVLOOKUPの近似値とは。
VLOOKUPの検索方法には2種類あります。「完全一致の値を検索」と「近似値を含めて検索」です。
VLOOKUP(検索値,テーブル範囲,持ってくる列,検索方法）と指定しますが、一番最後のところで指定します。通常は「完全一致」の「FALSE（または0）」を指定するのが一般的です。

検索方法のところにFalse(または0）を指定すると「完全一致の値を検索」、検索方法のところにTrue(または1）を指定すると「近似値を含めて検索」となります。

検索方法のところを省略すると「近似値を含めて検索」の方になります。
省略の仕方にもよって変わるので注意が必要ですが。
=VLOOKUP(D3,$A$3:$B$22,2)だと「近似値」
=VLOOKUP(D3,$A$3:$B$22,2,)だと「完全一致」
最後のカンマがあるかないかで違います。カンマがないと「省略された」とみなされ、カンマがあると「０が入った」とみなされるのかもしれません。

テーブルが昇順でないと、正しい答えを得られません。なんとも不可思議なめちゃくちゃ(と見える)な答えが返ってきます。ついつい忘れがちなので、注意！

VLOOKUPの近似値は「検索値以下の最大の値を返す」ということですが、私には意味が分かりません。例を見て何とか理解してみましょう。

近似値を使用したVLOOKUPの例です。
A・B列に年齢と年齢区分のテーブルがあります（キーの年齢で昇順になっています）
D列にランダムな年齢があり、E列にVLOOKUPの近似値を利用して年齢区分を持ってきます。

2行目の検索値は「28」です。
①テーブルの一番上から自分を探していきます。
②テーブルの2行目は「0」。自分より小さいです。次へ行きます。
③テーブルの3行目は「10」。自分より小さいです。次へ行きます。
④テーブルの4行目は「20」。自分より小さいです。次へ行きます。
⑤テーブルの5行目は「30」。自分を超えてしまいました。
⑥「30」は自分を超えているので、その一つ下の「20」でいいか、ということにします。
⑦キー「20」の2列目の値「20～29歳」を返します。

「検索値以下の最大の値」＝検索値が「28」の場合「30」だと自分より大きいので、「20」が自分以下の最大値、ということなのですね。
ちなみに実際の内部の挙動は上記①～⑥の動きではないそうです。でもこうしないと、理解できないので。。。

詳しくは、参考とさせていただいたこちらの記事に載っています。
https://curio-shiki.com/blog/excel/vlookup-true-approximate

２．近似値を使ってVLOOKUPの完全一致と同じ結果を得る方法

例を用いて、やってみましょう。（例のデータは大量ではないですが。。。）
A・B列にテーブルがあります。テーブルはA列のキーで昇順に並べてあります。
D列にキーがあり、VLOOKUPの近似値を使って、E列にテーブルからキーに対応する値を持ってきましょう。
①テーブルが昇順となっていることを確認します。昇順でなければ昇順に並べ替えを行います
②E列にVLOOKUPを入力します。
　今回は近似値のVLOOKUPとするため、検索方法を「TRUE（または１）」とします
　＝VLOOKUUP（D3,$A$3:$B$22,2,TRUE）

③完全一致と同じ結果を得るためには検索キーが一致していることを確認する計算式を追加し、キーが一致している場合のみVLOOKUPの答えを出力する

さて、上の図を見てみると、8行目の「25」のところに「U」が入っています。「25」はテーブルにないので、完全一致の場合は「#N/A」が返ってくるはずです。ここには値は返ってきてほしくありません。

そこでIF式をかまして、条件式を挿入し、処理を分岐します。
VLOOKUPの近似値で1列目（すなわちキー値）を持ってきて、その値が検索キーと一致した場合のみ、VLOOKUP近似値で2列目の値を持ってくる式を適用する、という式を作成します。

=IF(VLOOKUP(D3,$A$3:$B$22,1,TRUE)=D3,VLOOKUP(D3,$A$3:$B$22,2,TRUE),”なし”)

ややこしいですね。。。

ためしに、VLOOKUP近似値で1列目を持ってくる式に変更します。
すると、「25」の行は検索キーとは違う値、一番近い値の「20」が出力されていることが分かります。
そのほかの行は検索キーと同じ値が出力されています。

これを利用して式で条件分岐します。
IFの条件式；VLOOKUP１列目=検索キー
IFが真の場合；本当にやりたいVLOOKUP式
IFが偽の場合；検索キーがなかった時に出力したい値

8行目の「25」のことろはテーブルにキーがなかった場合に出力される「なし」が出力されました。

いかがでしたでしょうか？
けっこうややこしいのですが、慣れるとささっと入れられるようになります。

お読みいただきありがとうございました。
近似値の使いどころについてはこちらもどうぞ
【Excel/関数】VLOOKUPの近似値の使いどころ

エクセルをマスターしたい方にお勧めの書籍！