Office Hack

ExcelのVLOOKUP関数でワイルドカードを使って部分一致検索する

  • by kenji
  • Release
  • Update

VLOOKUP関数の検索を利用する際に、前方一致や部分一致、後方一致などあいまいな検索をしたいときがあります。

その場合は、ワイルドカードという「パターンにマッチする文字列を記号に置き換える」ことにより再現できます。

ここではよく利用される2つの記号を使った方法をお伝えします。

「*」文字数を決めないで任意の文字を検索

「*」は、検索する文字列の前後にある文字数を決めないで抽出することが可能です。とても便利な記号なのでよく利用されております。

ワイルドカードが使えない場合は?

設定に少し癖があります。正しい記載をしないと#N/Aエラーが出てしまいますのでご説明します。

検索値の前後に「*」を利用する場合、「*A1*」ではなく、「"*"&A1&"*"」と記載しましょう。*をダブルクオーテーションで囲み、&でつなぐことを忘れないでください。

部分一致検索

部分一致検索とは、検索する文字列の前後に「*」を付け加える(例:*りんご*)ことで、全体に対する部分一致で検索をすることができます。

部分一致検索の表

今回はD10の「つがる」を検索値とし、価格テーブルの「りんご -つがる-」の商品コードを抽出するVLOOKUP関数を設定します。

赤枠のとおり、つがるの前後には文字列が入っております。こちらを「*」のワイルドカードで指定します。

部分一致検索の記載方法

赤枠のとおり、つがるの前後に"*"を指定し、&で繋いでおります。

部分一致検索の結果

「りんご -つがる-」の商品コードを抽出することができました。

「つがる」の前方にはスペース含めて全角5文字、後方には全角1文字存在しておりますが、「*」は文字数を決めないで抽出が可能ですので、望んだ結果が出力されました。

前方一致検索

前方一致検索とは、検索する文字列の後ろに「*」を付け加える(例:りんご*)ことで、全体に対する前方一致で検索をすることができます。

前方一致検索のエラー確認

先にE10セルをご確認ください。#N/Aエラーが出ております。これは赤枠の前方一致の指定に対して、価格テーブルの文字列が前方にも後方にも存在しているため、エラーとなってしまいます。

前方一致検索の結果

B4セルの「つがる」より前にある文字を削除すれば、前方一致となりエラーが消えます。

後方一致検索

後方一致検索とは、検索する文字列の前に「*」を付け加える(例:*りんご)ことで、全体に対する後方一致で検索をすることができます。

後方一致検索のエラー確認

こちらもE10セルをご確認ください。#N/Aエラーが出ております。これは赤枠の後方一致の指定に対して、価格テーブルの文字列が前方にも後方にも存在しているため、エラーとなってしまいます。

後方一致検索の結果

B4セルの「つがる」より後にある文字を削除すれば、後方一致となりエラーが消えます。

「?」任意の1文字を検索

「?」は、任意の1文字を検索できます。???と指定すれば任意の3文字を検索できます。

?を使ったワイルドカード

今回はC11の「りんご」を検索値とし、「?」を6個つないだの商品コードを抽出するVLOOKUP関数を設定します。

りんごの後に6文字続いているのが存在していれば、その商品コードを抽出することができます。

6文字一致

商品コード「1001」が抽出されました。1001は「りんご -つがる-」であり、全角スペースを混ぜて6文字存在しております。・

5文字一致

「?」を1つ減らして、「?????」5つで検索すると「りんご -紅玉-」の商品番号「1002」がE11セルに抽出されます。

上が優先

B6セルに新たに「りんご -ふじ-」を追加しました。こちらも「?????」5つ検索の対象ですが、上にある行が優先のため「りんご -紅玉-」の商品番号「1002」が抽出対象となります。

VLOOKUP関数専門の本をご紹介

VLOOKUP関数をより深く勉強したい方はこちらの本でさらに理解を深めてみてはいかがでしょうか?実際の操作画面を示しながら、徹底的にわかりやすく解説したフルカラー入門書です。

VLOOKUP関数のツボとコツがゼッタイにわかる本

立山秀利(著)
206ページ

その他、様々なVLOOKUP関数の使い方











XLOOKUP関数も合わせてチェック!

VLOOKUP関数の後継として新しくXLOOKUP関数が発表されました。XLOOKUP関数の基本的な使い方を説明しています。

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。