Excelで検索専用シートを作る方法｜元データを編集せず一覧から検索する

Excelで顧客名簿や商品一覧、在庫表などを管理していると、

「一覧から必要なデータだけ簡単に検索したい」
「検索する人には元データを触らせたくない」
「Excelに検索ボックスのような画面を作れない？」

と思ったことはありませんか。

そんなときは、「元データを保存するシート」と「検索だけを行う専用シート」の2つに分けておくと便利です。

検索専用シートに名前や商品名を入力すると、一致したデータだけ表示されるようにしておけば、元の一覧を直接操作する必要がありません。

この記事では、Excelで検索専用シートを作り、元データを編集せずに一覧から検索する方法を紹介します。
※この記事では、FILTER関数とSEARCH関数を利用します。FILTER関数はMicrosoft 365、Excel 2024、Excel 2021などで使用できます。

INDEX

Excelで検索専用シートを作る仕組み
Excelで検索専用シート（検索画面）を作る
FILTERとSEARCHで一致するデータを表示する
1. FILTER関数の詳しい使い方を知りたい場合
検索欄だけ入力できるようにシートを保護する
元データも編集させたくない場合
SEARCH関数は「一部の文字で検索」するときに便利
1件だけ検索したいならXLOOKUPも使える
まとめ

Excelで検索専用シートを作る仕組み

今回作るExcelファイルは、次のような構成です。

■元データシート

管理番号	商品名	分類	担当者
001	りんごジュース	飲料	田中
002	みかんジュース	飲料	佐藤
003	りんごジャム	食品	鈴木

■検索シート

検索欄に、「りんご」と入力すると、

・りんごジュース
・りんごジャム

など、「りんご」が含まれるデータだけを表示

商品一覧だけでなく、

・顧客名簿
・社員一覧
・在庫一覧
・書籍一覧
・管理台帳

などにも応用できます。

Excelで検索専用シート（検索画面）を作る

新しいシートを追加して、シート名を「検索」などに変更します。
今回はこのシートを、一覧データを検索するための検索画面として使います。

今回はB2セルを検索ボックスとして使います。

たとえば、

・A2セル：検索
・B2セル：検索したい文字を入力
・A5:D5：管理番号・商品名・分類・担当者
・A6セル以降：検索結果

という配置にしておくと分かりやすいです。

FILTERとSEARCHで一致するデータを表示する

検索結果を表示するA6セルへ、次の数式を入力します。

=IF($B$2="","",FILTER(元データ!A2:D1000,(ISNUMBER(SEARCH($B$2,元データ!B2:B1000))+ISNUMBER(SEARCH($B$2,元データ!C2:C1000))+ISNUMBER(SEARCH($B$2,元データ!D2:D1000)))>0,"該当するデータがありません"))

これでB2セルに入力した文字が、

・商品名
・分類
・担当者

のどこかに含まれていれば、その行が検索結果として表示されます。

たとえばB2セルへ、

りんご

と入力すると、「りんごジュース」「りんごジャム」などが一覧表示されます。

完全に同じ文字でなくても検索できるため、商品名や担当者名の一部だけ分かっている場合にも便利です。

FILTER関数の詳しい使い方を知りたい場合

今回の記事では、FILTER関数そのものではなく、検索専用画面を作る方法を中心に紹介しています。

👉FILTER関数の基本や複数条件での抽出方法を詳しく知りたい場合は、こちらの記事も参考にしてください。

検索欄だけ入力できるようにシートを保護する

このままでも検索できますが、検索結果や数式を誤って削除してしまう可能性があります。

そこで、検索するB2セルだけ入力できる状態にして、ほかのセルを保護しておくと安心です。

■検索欄だけ入力できるようにシート保護をかける方法
1.セルB2（検索欄）を右クリックし、［セルの書式設定］を開く
　↓
2.［保護］タブを選択し、［ロック］のチェックを外して［OK］をクリック
　↓
3.［校閲］タブから［シートの保護］をクリック
　↓
4.許可する操作は［ロックされていないセル範囲の選択］だけを残し、［OK］をクリック