
VLOOKUPの近似一致(近似値)・あいまい検索って何に使うの?
VLOOKUPの近似一致(近似値)・あいまい検索って何に使うの?
意外と使える!?VLOOKUPの近似一致・あいまい検索について、その仕様と活用方法をまとめました。
VLOOKUPを使っている方なら一度は疑問になる近似一致、あいまい検索ですが
大抵の人はVLOOKUPを学ぶときに「最後の引数の指定はFALSE(または0)」と習っていることでしょう。
それはそのはず、この指定であればどんな条件下であろうと「検索値と完全に一致」したものを結果として出してくれるのです。
また、近似一致・あいまい検索という言葉の雰囲気から、文字列を含むで検索が出来るのでは?とか思いがちですが、それも間違いです。
文字列含むで検索したいときは、別の方法がありますのでコチラご覧ください。
※参考:VLOOKUPを"文字列を含む"で行う方法
では、最後の引数をTRUE(または1,または空白)としたときにどのような結果を生み出すのか
またその驚くべき使用方法を解説します。
近似一致(あいまい検索)を行うときの前提条件
実はVLOOKUPの近似一致(近似値)・あいまい検索の仕様で検索範囲については
必ず「昇順」にしてあることが前提条件となります!
早くもめんどくさい条件が出てきたのでもう使う気が失せたかもしれませんが、
有効な使い道があるかもしれません。最後まで必読を!
もう一つのデメリット…
これは私が実際に使いこなそうと必死になっていたときに気が付いた最大のデメリットですが
検索範囲が日本語文字列の場合、意図した動作をしない可能性があります。。。
おそらくですが、日本語文字列の場合昇順をしても昇順になりきらないのではと考えています。
確実に使えないというわけではないのですが、そういったちょっとした問題も発生します。
ただ、今回私が使用する方法はその問題も回避する方法ですので安心してください。
これは超メリット!計算がめっちゃ高速早い!
VLOOKUPを普段使っている方の不満は「データが大量になるとめっちゃ遅くなる」
誰もがつまづく超デメリットだと思いますが、これが解決出来ちゃいます。
実はこの方法、ネットでも話題の「高速VLOOKUP」と言われている方法になります。
昇順にしているというのがEXCELの計算で高速処理を生み出しているのだと思われます。
それではやってみましょう
=VLOOKUP(D2,$A$2:$B$3828,2,TRUE)
正常に取得出来ているようです。
これを下に伸ばします。
すると…
あれ、E3とE4に同じ結果が…
実はこれが近似一致(あいまい検索)の仕様になります。
条件に合致したものがない場合、その一つ前の値を結果に出します。
今回はわざと"a00003"を除いていたため、一つ上の"a00002"が条件に引っかかったのです。
一つ前の値を結果に出すって、使い道あるの?と思った人が多いと思いますが
実は私も全然使い道が分りませんw
高速処理ができても、完全に一致したものを返さないのなら使えないじゃん
って思いますが、これを解決する方法を紹介します。
高速VLOOKUP
=IF(VLOOKUP(D2,$A$2:$A$3828,1,1)=D2,VLOOKUP(D2,$A$2:$B$3828,2,1),NA())
数式長い!とあきらめないでください。これでも高速なんです。
IF文で先に同じ検索値を取得出来ているか確認し、同じ検索値を取得してる場合だけに結果を返すようにしています。
これで先ほど近似値を取得してしまっていたE4セルが#N/Aになりました。
また、これであれば先ほどデメリットとして挙げた「日本語文字列だと上手く動作しない」の回避策にもなっています。
ちなみにこのVLOOKUP、どのくらい高速かというと…
20万行から2万行分取得する際に通常のVLOOKUPは30秒かかるところが一瞬!
くらい早いです。
VLOOKUPの近似一致(近似値)・あいまい検索について
その使用方法をご紹介させていただきました。
関連コンテンツ
文字列抽出・変更・置換系トピック
EXCEL 置換関数にワイルドカードが使えない!代わりの方法を考えました。
EXCELで文字列の記号を全て削除!記号を一覧にしてSUBSTITUTE関数で一括置換しよう
EXCELで改行を削除(置換)して消す方法/実はSUBSTITUTE関数でも出来るんです!
EXCELで不要な空白(文字列前後の空白や連続した空白)はtrim関数で削除!
検索系トピック
VLOOKUPで複数範囲の検索を行う方法/VLOOKUPを繋げるしかない?
VLOOKUPエラーの原因!N/A,REF,VALUE,NAMEになるのはなぜ?
VLOOKUPでのエラー(#N/A)の対処はIFやIFERRORを使って解決しよう!
VLOOKUPの近似一致(近似値)・あいまい検索って何に使うの?
EXCELで条件に一致したデータを検索&抽出するならVLOOKUP関数
VLOOKUPを横にコピー!複数列をまとめて抽出するために列番号をずらす方法
VLOOKUPで左側(右から左に)の値を取得する方法はあるのか
VLOOKUPで複数結果を全て抽出・表示する方法/重複したときに2番目以降も抽出できる?
SUMPRODUCT関数では文字列を抽出出来ない/条件に合致した文字列を抽出するならVLOOKUP
集計・ピボット系トピック
SUMIFSで合計範囲を可変にするならOFFSET関数を使いこなそう
COUNTIFを含む条件でカウントする方法/ワイルドカード(アスタリスク)を使って含む条件を行おう
SUMIFを複数条件で集計/2つ以上の条件で集計したいときはSUMIFS関数で!
COUNTIFで空白以外(空白ではないセル)のカウントを行う方法
AVERAGEで0以外を集計するならAVERAGEIFで!エクセル平均集計を自在に
AVERAGEIFを複数条件で平均値を算出したいならAVERAGEIFSで
エクセル集計関数を極める!データ集計(合計)、個数カウント、条件付き集計などまとめて紹介!
EXCELで条件付きの合計を算出する時はSUMではなくSUMIFまたはSUMFISで
EXCELで期間集計(日付や範囲指定での抽出合計)ならSUMIFS関数
COUNTIF,COUNTIFSの使い方を徹底解説!条件の個数カウントを極めよう
エクセル 掛け算の関数PRODUCT関数/SUM関数の掛け算版があった!
EXCELの月別集計(日別データを月別に集計する方法)はSUMIFS関数で
SUMIF(SUMIFS)の計算が重い・遅いときは集計の鉄板ピボットテーブル?
ピボットテーブルの初期設定(デフォルト)が変えられない!私なりの解決策
VLOOKUPで合計(集計)は出せない!VLOOKUPの代わりにあの関数で条件付き合計を出そう!
EXCELで時間帯別に集計する方法/日時データから時間を抽出
マクロで自動化!簡易ツールの作成やVBAの活用例を紹介
エクセル シート名の一括変更や置換はVBAマクロじゃなきゃ無理なの?
複数のCSVファイルを結合(マージ)!エクセルVBAを使用したCSV結合ツールの作り方
EXCEL フォルダ内のブック(ファイル)を結合(統合)/VBAマクロで複数ファイルをまとめる!
EXCELで複数画像を一括挿入貼り付け!VBA(マクロ)で作っちゃいました。
エクセル マクロ(VBA)で複数のグラフを自動作成!コード教えます。
EXCEL 全シート名を取得し一覧に!VBAを利用して一瞬で解決
EXCEL シート毎にファイル分割保存する方法/マクロ(VBA)で解決!
EXCELで複数ファイルを集計するツール/マクロ(VBA)を使って作ってみました。
EXCELマクロ(VBA)って何?とりあえず試しに使ってみよう
その他EXCELテクニック
エクセル表の簡単な作り方/罫線や色もつけてシンプルかつ鮮やかに!
エクセル計算で参照セルを固定!コピーする際にズレない【絶対参照】
エクセル シート移動のショートカットはCtl+PageDownまたはCtl+PageUp
エクセル 一番下まで移動はスクロールだと大変?キーボードでの瞬間移動方法を紹介!
エクセルでユニークなリスト(重複をなくしたリスト)をちゃちゃっと作る方法
エクセルで1から10まで連番を入力する方法/オートフィル機能を使ってみよう
エクセルプルダウンリストを極める!設定・作成方法から応用技まで紹介
EXCEL プルダウン(リスト)を連動させる二つの方法/入力規則を使いこなせ!
エクセルでエラーを表示しない方法/ISERROR関数とIFERROR関数で解決
EXCEL リンクでシート間を楽々移動!ハイパーリンクで別シートへ移動
IF関数でワイルドカード(アスタリスク)は使えない?IF関数で含むを条件にする方法
EXCEL 時間の足し算(計算)ってどうやるの?時・分・秒の計算方法まとめました
EXCELで重複したデータをカウント!連番をつけ重複データを見つける方法
エクセルで行または列を固定し、スクロールさせずに常に見える状態にする方法
EXCELファイルサイズが大きい!重い!容量を軽く小さくする方法はある?
EXCEL 列の数字(番号)取得はCOLUMN関数で/英語アルファベットの取得方法は?
EXCELでフィルタした行を削除するのが重い(遅い)時の解決方法
EXCELでシート名の参照なんて出来たの!?indirect関数の使い方
エクセル家計簿を自作してみました(無料でダウンロードもできます)
エクセル練習問題(テスト)VLOOKUPや集計関数使えますか?
コラム
あなたのエクセルスキルはどのくらい?エクセルが使える人、出来る人のレベルとは
★管理人おすすめ(無料です!エクセルファイルをダウンロードしてやってみよー)
エクセル練習問題(テスト)VLOOKUPや集計関数使えますか?
★人気記事 TOP10
エクセルプルダウンリストを極める!設定・作成方法から応用技まで紹介
EXCELで期間集計(日付や範囲指定での抽出合計)ならSUMIFS関数
EXCEL 置換関数にワイルドカードが使えない!代わりの方法を考えました。
VLOOKUPの近似一致(近似値)・あいまい検索って何に使うの?
EXCELファイルサイズが大きい!重い!容量を軽く小さくする方法はある?
★新着記事
エクセル表の簡単な作り方/罫線や色もつけてシンプルかつ鮮やかに!
VLOOKUPで複数範囲の検索を行う方法/VLOOKUPを繋げるしかない?
エクセル計算で参照セルを固定!コピーする際にズレない【絶対参照】
エクセル練習問題(テスト)VLOOKUPや集計関数使えますか?
エクセル シート移動のショートカットはCtl+PageDownまたはCtl+PageUp
VLOOKUPエラーの原因!N/A,REF,VALUE,NAMEになるのはなぜ?
エクセル シート名の一括変更や置換はVBAマクロじゃなきゃ無理なの?
エクセル 一番下まで移動はスクロールだと大変?キーボードでの瞬間移動方法を紹介!
EXCELでお困りの方へ

当サイトでは皆さまがEXCEL作業で行き詰った問題を解決するべく王道のテクニックからちょっと特殊なテクニックまで 様々な中〜上級技・プチテクニックを公開し、少しでも皆様の業務効率改善に役立てていただければと思い、ネタをまとめています。
EXCELの作業で悩み・お困りごとはありませんか?
集計に時間がかかり過ぎ。効率化をしたい。などなど日頃から奮闘しているあなたに少しでも参考になる情報を与えることが出来れば幸いです。
質問・相談なども受け付けておりますが、最近忙しくて返事が遅れてしまいます。。。
サイト内の内容についてのご質問は優先的に受け付けておりますが、それ以外のご相談などはお断りすることもありますのでご了承ください。
※サンプルのエクセルファイルなど添付していただくと回答もスムーズに行える場合が多いです。
