エクセルで「キーワードを入れると、一致する行が一覧で出る」検索システム（検索窓）は、FILTER関数と SEARCH関数を組み合わせた式を1つ入れるだけで作れます。マクロ（VBA）は使いません。キーワードを書き換えると、結果がすぐに切り替わります。
この記事では、商品リストを部分一致で検索するテンプレート（無料）と、その式のしくみを、実際の画面で説明します。FILTER関数は Microsoft 365 と Excel 2021 以降で使えます。
練習用Excelファイル（無料ダウンロード）
「検索」シートのキーワード欄に商品名の一部を入れると、「商品リスト」シートから一致する商品が一覧で表示されます（文房具15品目の記入例入り・架空のデータ）。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
目次
検索システムの使い方
1
「検索」シートのキーワード欄（B3）に、探したい商品名の一部を入れます。
2
キーワードを変えると、結果がすぐに切り替わります。
3
一致する商品がないときは、「見つかりません」と表示されます。
検索システムの作り方
1. 元のデータをテーブルにする
「商品リスト」シートの表の中を選び、Ctrl＋T でテーブルにします。テーブル名は「テーブルデザイン」タブで「商品」にしています。テーブルにしておくと、商品を追加しても式を直す必要がありません。
2. 結果の左上のセルに FILTER関数を入れる
=IF(B3="","キーワードを入れてください",FILTER(商品,ISNUMBER(SEARCH(B3,商品[商品名])),"見つかりません"))| 部分 | 意味 |
|---|---|
SEARCH(B3,商品[商品名]) | 商品名の中にキーワードがあれば、その位置（数字）を返す。なければエラー |
ISNUMBER(…) | 数字なら TRUE（＝キーワードを含む） |
FILTER(商品,…,"見つかりません") | TRUE の行だけを取り出す。1件もなければ「見つかりません」 |
IF(B3="","キーワードを入れてください",…) | キーワードが空のときのメッセージ |
式は A7 に1つ入れるだけで、結果が下と右に自動で広がります（スピル）。FILTER関数のくわしい使い方はFILTER関数の使い方で説明しています。
3. 見つかった件数を表示する
=IF(B3="","",COUNTIF(商品[商品名],"*"&B3&"*"))「*」は「何文字でもよい」という意味のワイルドカードです。前後に付けると、キーワードを含む商品名の数を数えられます。
検索の注意点（実際に確かめた結果）
| キーワード | 結果 | 理由 |
|---|---|---|
| a4（小文字） | A4 と同じ4件 | SEARCH関数は大文字・小文字を区別しない |
| Ａ４（全角） | 0件 | 全角と半角は別の文字として扱われる |
| （空白） | 「キーワードを入れてください」 | IF関数で空のときを分けている |
全角・半角を気にせず検索したいときは、元のデータとキーワードを ASC関数で半角にそろえる方法があります（全角・半角の変換）。
うまくいかないときの確認表
| 症状 | 考えられる原因 | 対処 |
|---|---|---|
| 「#スピル!」と表示される | 結果を出す範囲に、ほかのデータが入っている | A7 の下と右のセルを空にする。 |
| 「#NAME?」と表示される | FILTER関数がない古いExcel | Microsoft 365 か Excel 2021 以降で使う。 |
| 追加した商品が出てこない | 元の表がテーブルになっていない | 元の表を Ctrl＋T でテーブルにする。 |
練習用Excelファイル（無料ダウンロード）
キーワードを入れるだけで部分一致の検索ができます。「商品リスト」シートを自分のデータに書き換えて使えます。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
コメント