【Excel/関数】VLOOKUPの近似値の使いどころ

VLOOPUP関数は毎日のように使う。しかし、検索条件「完全一致しか使うことがなく「近似値」は使うことがない。いったいどうゆうときに使うんだろう。「近似値」って使う人いるの？何のためにあるの？とふと疑問に思った。

で、考えた結果、こんな時につかえるかな、と思いついた例を二つ書いてみます。

そもそもVLOOKUP関数とは、はこちらの記事を参照ください

下の図はVLOOKUPを指定したときのダイアログボックスです。

〇検索方法

①完全一致（0またはFALSEと指定）は検索値が完全に一致したときのみ値を返します。参照範囲を最初から最後まで探してなければエラー値「#N/A」を返します。

②近似値（正式名称は倫理値のようです。1またはTRUEと指定）は参照範囲を最初から探して、検索値と一致した対象があれば、その値を返し、参照範囲のキー値が検索値より大きくなった場合、ひとつ前の値を返します。

つまり、検索値を超えない最大値の値を返します。

近似値を使用する前提条件として検索値(キー値）が昇順に並んでいなければ、おかしな答えが返ってきます。

〇検索法を省略すると

検索方法を省略すると「近似値」になる、と思っていました。その通りなのですが、省略の書き方により違うようです。

= VLOOKUP ( A3 , $E$3:$F$10 , 2 , )　←最後のカンマありだと「0」とみなされ「完全一致」になる

= VLOOKUP ( A3 , $E$3:$F$10 , 2 )　←最後のカンマなしだと省略とみなされ「近似値」になる

年齢から対応する〇～〇才を５歳単位で持ってきたい場合。

参照テーブルは年齢を5歳ごとに昇順に並べたテーブルを用意しておきます。

A列の年齢をキーにして、対応するテーブル2列目の〇～〇才を参照テーブルから持ってきます。

B列にVLOOKUP式を入れます。

＝VLOOKUP( A2 , $E$3:$F$11 , 2, TRUE)

検索値：A列の年齢

参照範囲：E・F列の参照テーブルの範囲

列番号：2（Eから数えて２列目の「〇才代」を持ってくる）

検索方法：TRUE（近似値）

年齢が「12」の場合、参照テーブルの最初から探して

「0」は自分より小さいので次を探す

「5」は自分より小さいので次を探す

「10」は自分より小さいので次を探す

「15」は自分より大きい。ひとつ前の「10」が自分を超えない最大値になるので「10」の行の「10～14才」を返す

あー、けっこう便利かもしれない。年齢階層ごとの集計をするときとか使えそう。

たまーに「ア行ですよ」とか索引を付けたいときがある。

下の図では大学名のB列のふりがなの1文字目をキーにしてE・F列のあいうえお表からC列に「〇行」をもってきています。

＝VLOOKUP（LEFT(B2,1),$E$2:$F:$11,2,TRUE)

検索値：B列フリガナの左から1文字目（LEFT関数を使用）

参照範囲：E/F列のテーブル

列番号：２

検索方法：TRUE（近似値）

このような単純なあいうえお程度の場合うまくいきましたが、文字列を使用した近似値検索の場合、文字コードの順番が思ったのと違うことがある場合があるようで、思ったのと違う答えが返ってくることがあり注意が必要なようです。

こちらのページを参考とさせていただき勉強させていただきました。

ありがとうございました。

近似値を利用して、大量データのVLOOKUPを高速にする！のやり方はこちらをどうぞ
【Excel/関数】大量データのVLOOKUPをウソみたいに早くする方法

リンク