Googleスプレッドシートでは出来ない？検索範囲で【ワイルドカード検索】する方法

データの集計や分析など、マーケターにとって表計算ソフト（スプレッドシート）は切っても切り離せない存在です。
表計算ソフトで有名なのはマイクロソフト社の「Excel」かと思いますが、WEBマーケティング業界ではGoogle AnalyticsやGoogle Search Consoleと親和性が高い「Googleスプレッドシート」を活用している企業も多いのではないでしょうか。

今回はそんなGoogleスプレッドシートを使って、検索範囲でワイルドカード検索（部分一致検索）をする方法を紹介したいと思います。

VLOOKUP関数を使って検索する

ExcelやGoogleスプレッドシートでは、大量のデータの中から必要なデータを抽出する際に、「VLOOKUP関数」が非常に便利な関数として用いられます。

例えば、

• 顧客一覧リストからID検索して「顧客情報」を表示する
• 都道府県リストから県名検索して「地方名」を表示する

などの場面で、VLOOKUP関数を利用して検索することが出来ます。

そんな便利なVLOOKUP関数ですが、検索範囲の中に検索値を含む場合にその結果を返したいのであれば、検索値にワイルドカードが使えます。

=VLOOKUP(“*”&検索値&”*”,検索範囲,列番号,検索方法)
=VLOOKUP("*"&B4&"*",E$4:F$6,2,FALSE)

しかし、その逆の検索範囲にワイルドカードを指定する場合は、VLOOKUP関数では思った検索結果を出すことが出来ません。

このような場合にVLOOKUP関数は使えないのでしょうか？

調べてみるとVLOOKUP関数には「あいまい検索」と言うものがあり、最後の引数を「TRUE」にする事で、完全一致していなくても近似値があれば表示してくれることが分かりました！
これを使えばワイルドカードのように検索ができるのではないでしょうか？

VLOOKUP関数の「あいまい検索」使ってみる

それではVLOOKUP関数の「あいまい検索」使うとどうなるか見てみましょう。

C6セルには地方名が出ていますが、実際の地方名と違うものが結果に出ています。どうして近い値で検索しているのに違う地方名が出るのでしょうか？

これはVLOOKUP関数の「あいまい検索」が、実際にはワイルドカード（部分一致）による検索ではなく、バイナリサーチ（二分探索）を活用した検索をしているためです。

「バイナリサーチ」は探索範囲を2分の1にするアルゴリズム

バイナリサーチとは、検索範囲の中央値を基準に、探索範囲を2分の1ずつ絞って効率的に探索を行うアルゴリズムです。

例えば、以下のようなリスト（配列）から8がある場所を探索したい場合、左から順番に調べたら8回目の探索で場所が判明します。

{1,2,3,4,5,6,7,8,9}

バイナリサーチでは中央値から調べるので、まず8が中央値の5よりも大きいか小さいかを調べます。8は5よりも大きいので以下のグループから8を探します。

{5,6,7,8,9}

先ほどと同じように8が中央値の7よりも大きいか小さいかを調べます。8は7よりも大きいので、以下のグループから8を探します。

{7,8,9}

このグループの中央値が探していた8と一致しているので、この方法であれば3回目の探索で場所が判明します。このようにリストが順番に並んでいる場合、バイナリサーチは範囲を絞るのに非常に有効なアルゴリズムです。

このアルゴリズムを活用することで、VLOOKUP関数の「あいまい検索」では高速な検索をすることができ、ある程度近い値まで絞り込むことができるので、近似値として結果を出しています。

ということで、「あいまい検索」では、本当の意味でワイルドカード検索が出来ているわけではありません。

LOOKUP関数とFIND関数を使った方法がある！

それでは諦めるしか無いんでしょうか…そんな時に参考になるサイトを見つけました。

VLOOKUP関数ではなく、LOOKUP関数とFIND関数をテクニカルに使った方法で、検索範囲にワイルドカードを指定した検索が可能になるという内容でした。

=LOOKUP(0,0/FIND(検索範囲,検索値),対応範囲)

本来、FIND関数はFIND(検索値,検索範囲)と言う使い方をしますが、FIND(検索範囲,検索値)とすることで、内部的にはエラーを含む配列が返ってくるようになります。

例えば、以下シートのC4セルの式を紐解いていきます。

まずFIND関数に注目して

=FIND(検索範囲,検索値)
=FIND(E$4:E$18,B4)

この例だと結果は、

{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,5,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

という配列になります。11番目に5が入り、それ以外がエラーの配列ですね。
ここで言う5は「愛知県」という言葉が出てくるのが左から5文字目という意味です。

※上の画像では「#N/A」というエラー表示になっていますが、なぜエラー表示になってしまっているは後ほど解説します。

配列内容は以下のC4:C18セルのイメージです。

その配列結果を0から割るので、

=0/FIND(検索範囲,検索値)
=0/FIND(E$4:E$18,B4)

の結果は、

{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

という配列になります。配列内容は以下のC4:C18セルのイメージです。

最後にLOOKUP関数の動きを見ていきましょう。

=LOOKUP(検索値,検索範囲,対応範囲)
=LOOKUP(0,0/FIND(E$4:E$18,B4),F$4:F$18)

検索値は0なので「検索範囲から0を探す」という意味になります。

検索範囲は、

{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

という配列（リスト）なので「配列（リスト）から0がある箇所を探す」という意味になります。

対応範囲は地方名リストなので、これら全てを読み解くと「配列（リスト）から0がある箇所と同じ箇所の、F列の内容を返す」という意味になります。

今回の場合は0が11番目にあるので、F4:F18セルの中で11番目のF14セルの値が返されて「中部地方」が返ってくるわけです。

しかし、ここでスプレッドシートに戻って見てみましょう。

参考サイトによれば、Excelだとちゃんと表示されるはずの地方名が、Googleスプレッドシートだと「#N/A」というエラー表示になってしまっています。

Googleスプレッドシートだと「#N/A」が出てしまう理由

これはExcelとGoogleスプレッドシートでは、配列の扱いが若干異なることが要因になっています。

Excelでは数式が入ったセルを選択して、Ctrl+Shift+Enterキーを押すことで「配列数式（{}で囲まれた数式）」に変換することができ、セルの中で配列を扱うことが出来ます。

今回のFIND(検索範囲,検索値)という計算でも、Excelであれば配列数式としてそのまま計算してくれるのですが、Googleスプレッドシートではそのまま計算してくれません。

しかし、ここまで来て諦めるしか無いのか…と落胆する必要はありません。

ARRAYFORMULA関数を使用する

GoogleスプレッドシートではARRAYFORMULA関数という「配列関数」が存在して、その関数で囲うことでセル内で配列を扱うことが出来ます。

今回はLOOKUP関数の「検索範囲」で配列を扱えれば良いので、

=LOOKUP(0,ARRAYFORMULA(0/FIND(E$4:E$18,B4)),F$4:F$18)

とすることで、Excelと同じように処理することが出来ます。

そして完成版がこちらです！！

ちゃんと住所から正しい地方名が出力されていますね。

これによって検索範囲でワイルドカード（部分一致）をすることができるようになるので、

• 住所から地方名を割り出す

などができるようになります。
さらに、これを応用することで

• WEBサイトから問合せがあった場合に、住所によって担当支店を自動振り分けする
• WEBサイトから問合せがあった場合に、住所によって担当者に自動メール配信する

など、様々な運用が自動化できるようになります。

今回はバイナリサーチ（二分探索）や配列関数の話で、結構な文量となってしまったため、応用編のメール配信自動化などは次回以降にまとめたいと思います。

以上、初心者が躓きがちなVLOOKUP関数の「あいまい検索」の簡単な解説と、検索範囲にワイルドカード（部分一致）検索を使用する方法を紹介しました。