Excelのシートに検索ボックスを作る ～セルにキーワード入力するだけで部分一致検索！

部分一致でヒットしたデータの一覧を表示させてみよう

　表の中から特定のデータを取り出したい場合、フィルター機能を使うことが多いですよね。チェックボックスをON/OFFして、データを絞り込む操作には慣れているでしょう。少し応用して「テキストフィルター」を利用すると、指定した文字列を含むデータを抽出することもできます。

フィルターボタンからの操作では、任意の文字列を含んだデータを絞り込むことも可能

例えば「シューズ」と入力して［OK］をクリックする

商品名に「シューズ」を含むデータを抽出できる

　しかし、フィルターをかけた後、毎回手動でクリアするのは少し手間ですよね。セルに入力した文字列を基に検索結果を抽出できたら便利だと思いませんか？　今回は、関数を組み合わせて簡易的な“検索ボックス”を作成するテクニックを紹介します。

FILTER関数で元の表を参照する

　ここでは［商品マスタ］シートに商品の情報がまとめられているとします。別のワークシートから参照して該当するデータを抽出する場合、FILTER関数がよく使われます。ただし、完全一致しか対応していないため、“部分一致”には使えません。以下の例では該当するデータがないと判断されて「該当なし」と表示されてしまいました。

操作中のシートは［検索］シート。元データは［商品マスタ］シートにまとめてある。セルA5に「=FILTER(商品マスタ!A2:D101,商品マスタ!B2:B101=検索シート!B2,"該当なし")」と入力してあるが、結果は「該当なし」と表示されてしまった

FILTER関数の構文。引数［配列］には対象のセル範囲を指定する。［含む］には［配列］から抽出する条件を指定する。［空の場合］は条件に一致するデータがない場合に表示する値を指定する（省略可能）

　「シューズ」という商品は存在しないため、この結果に問題はありませんが、「シューズ」という文字列を含むデータをすべて抽出してほしいですよね。このような場合に「ISNUMBER関数」と「SEARCH関数」が役立ちます。

ISNUMBER関数とSEARCH関数で部分一致を見分ける

　結論から先に言うと、以下のように数式を書き換えるだけで“部分一致”が実現します。

　先に、SEARCH関数とISNUMBER関数の構文を見ておきましょう。SEARCH関数は、指定した文字列が対象の何文字目にあるかを調べます。該当する文字列がなければエラーとなります。ISNUMBER関数はセルの内容が数値かどうかを判定します。

ISNUMBER関数の構文。［テストの対象］が数値かどうかを調べる。［テストの対象］が数値なら「TRUE」、数値以外なら「FALSE」を返す

SEARCH関数の構文。［検索文字列］が［対象］の文字列の中で先頭から何文字目にあるかを調べる

　つまり、指定した文字列が対象に存在しているなら、SEARCH関数の結果は数値となり、ISNUMBER関数の結果は「TRUE」となります。ここでは、［商品マスタ］シートのセルB2～B101（商品マスタ!B2:B101）が対象となるので、結果はスピル（配列）となり、該当するデータがFILTER関数で抽出されるわけです。

セルB5の数式を「=FILTER(商品マスタ!A2:D101,ISNUMBER(SEARCH(B2,商品マスタ!B2:B101)),"該当なし")」と書き換える

セルB2に入力した「シューズ」に該当する商品が抽出される

検索条件を変更すると結果も変化する

　文字列を含むデータをサッと抽出できるこの方法は、商品検索やアンケート結果の確認など、さまざまなシーンに応用できます。覚えておいて損はありませんよ。