Excelのドロップダウンに候補が多いと、目的の項目を探すだけで時間がかかりますよね。特に顧客名、商品名、部署名などが数十件を超えると、スクロール操作は意外なストレスになります。
Excelでドロップダウンを検索・絞り込みしたい場合は、検索語を入力するセルと、FILTER関数で絞り込んだ候補一覧を用意し、その一覧を入力規則のリストに指定する方法が便利です。さらに、分類を選ぶと候補が変わる連動ドロップダウンも作れます。
結論：検索用セル＋FILTER関数＋入力規則で絞り込みます
この方法は、Microsoft 365やExcel 2021以降で使えるFILTER関数、UNIQUE関数、スピル機能を利用します。たとえばA列に商品名があり、G2に「コーヒー」と入力すると、H列には「コーヒー豆」「コーヒーフィルター」など、文字を含む商品名だけが表示されます。
この仕組みでできること
- 候補が多いリストをキーワードで絞り込む
- 入力した文字を含む項目だけをドロップダウンに表示する
- 商品分類や部署などの選択に応じて候補を切り替える
- 元データを更新したときに候補リストへ反映する
なお、Excelの入力規則による通常のドロップダウンは、すべてのバージョンで検索ボックスが表示されるわけではありません。そのため、確実に検索・絞り込みをしたいときは、これから紹介する数式を使う方法がおすすめです。
検索して絞り込めるドロップダウンの作り方
ここでは、A2:A100に商品名の一覧があるケースで説明します。検索語はG2、絞り込み結果はH2から表示し、実際に選択するドロップダウンはE2に作成します。
1．元になる候補リストを用意する
- A1に「商品名」などの見出しを入力します。
- A2以降に、ドロップダウンで選ばせたい項目を1件ずつ入力します。
- 空白行や余計なスペースが多い場合は、できるだけ整理しておきます。
候補に同じ商品名が何度もある場合でも、後でUNIQUE関数を使うため、ドロップダウンには重複しない一覧を表示できます。
2．検索語を入力するセルを作る
- G1に「検索」と入力します。
- G2を検索用セルとして使います。
- G2には、探したい文字の一部を入力します。たとえば「東京」「赤」「コーヒー」のように入力します。
検索語を空欄にしたときは、元データの候補をすべて表示したいですよね。その場合は、次の数式を使うと便利です。
=SORT(UNIQUE(FILTER($A$2:$A$100,IF($G$2="",TRUE,ISNUMBER(SEARCH($G$2,$A$2:$A$100))),"該当なし")))
SEARCH関数は、指定した文字がセル内に含まれているかを調べます。大文字・小文字を区別せずに検索したいときに使いやすい関数です。SORT関数は候補を並べ替え、UNIQUE関数は重複を取り除きます。
3．絞り込み結果の数式を入力する
- H1に「絞り込み候補」と入力します。
- H2をクリックします。
- 次の数式を入力してEnterキーを押します。
=SORT(UNIQUE(FILTER($A$2:$A$100,IF($G$2="",TRUE,ISNUMBER(SEARCH($G$2,$A$2:$A$100))),"該当なし")))
H2から下へ候補が自動的に広がれば成功です。このように数式の結果が複数セルへ自動表示される動きを、スピルと呼びます。
4．絞り込み結果をドロップダウンに登録する
- ドロップダウンを表示したいE2をクリックします。
- リボンの「データ」タブを開きます。
- 「データの入力規則」をクリックします。
- 「設定」タブの「入力値の種類」で「リスト」を選びます。
- 「元の値」に=H2#と入力します。
- 「OK」をクリックします。
E2の右側に表示される矢印をクリックすると、G2の検索語に一致した候補だけが表示されます。G2の文字を変えれば、E2の候補も自動で切り替わります。
分類を選んで候補を絞る連動ドロップダウンの作り方
検索だけでなく、「部署を選んだら担当者だけを出したい」「カテゴリを選んだら該当商品だけを出したい」というケースも多いですね。この場合は、1つ目のドロップダウンの選択値を条件にしてFILTER関数を使います。
たとえばA列にカテゴリ、B列に商品名があるとします。E2でカテゴリを選び、F2で商品名を選ぶ形にします。
- E2にカテゴリ選択用のドロップダウンを作ります。カテゴリ一覧をH2にスピル表示し、入力規則の元の値を「=H2#」にします。
- I2に次の数式を入力します。
=SORT(UNIQUE(FILTER($B$2:$B$100,$A$2:$A$100=$E$2,"該当なし"))) - F2をクリックし、「データ」タブから「データの入力規則」を開きます。
- 入力値の種類を「リスト」にし、元の値へ=I2#と入力します。
- E2でカテゴリを選び、F2の候補が切り替わるか確認します。
さらに検索も組み合わせたい場合は、カテゴリ条件と検索条件を両方FILTER関数へ指定します。たとえば検索語をG2に入力するなら、数式は次の形です。
=SORT(UNIQUE(FILTER($B$2:$B$100,($A$2:$A$100=$E$2)*IF($G$2="",TRUE,ISNUMBER(SEARCH($G$2,$B$2:$B$100))),"該当なし")))
これで、カテゴリを選んだうえで、検索語に一致する商品だけをドロップダウンに表示できます。
うまくいかない場合のチェックポイント
「元の値がエラーになります」と表示される
- =H2#のように、スピル範囲演算子の#を付けているか確認します。
- H2の数式が正しく計算され、候補が下方向へ表示されているか確認します。
- 入力規則の「元の値」へ、直接「H2#」ではなく「=H2#」と入力します。
- Excelのバージョンが古く、スピル機能に対応していない可能性があります。
FILTER関数で「#SPILL!」エラーになる
H2の下や右に、すでに文字や数式が入っていると、スピル結果を広げられず「#SPILL!」が表示されます。H2の下にあるセルを空欄にしてから、もう一度数式を確認しましょう。結合セルがある場合もスピルを妨げるため、結合を解除してください。
検索しても候補が出ない
- 検索対象のA列に、見えないスペースが入っていないか確認します。
- 検索語のセルG2を参照する数式になっているか確認します。
- FILTER関数の対象範囲と条件範囲の行数が一致しているか確認します。たとえばA2:A100に対してA2:A99を条件にするとエラーになります。
- 検索語が完全に違っている場合は「該当なし」と表示されます。部分一致で探せるよう、短いキーワードでも試してみましょう。
ドロップダウンの矢印が表示されない
「データの入力規則」の設定画面で、「セル内ドロップダウン」が有効になっているか確認してください。また、シートが保護されている場合や、対象セルが結合セルになっている場合は設定しにくいことがあります。
FILTER関数やUNIQUE関数が使えない
Excel 2019以前など、動的配列関数に対応していないバージョンでは、この方法はそのまま使えません。その場合は、元データをテーブル化してフィルター機能を使う、候補数を減らして通常の入力規則リストにする、といった運用が現実的です。職場のExcelバージョンが混在しているときは、共有するファイルを開く環境も確認しておくと安心ですよ。
最後に覚えたい時短ワザ
候補リストが増え続けるファイルでは、元データをテーブル化しておくと管理がラクになります。元データ内をクリックしてCtrlキー＋Tを押し、「先頭行をテーブルの見出しとして使用する」にチェックを入れてください。新しいデータを追加したときに範囲を把握しやすくなります。
また、ドロップダウンで候補を選んだ後に同じ操作を繰り返すなら、コピー＆貼り付けよりCtrlキー＋Dで上のセルをコピーする方法も便利です。検索用セルへ移動したいときはCtrlキー＋Gを押し、参照先にG2と入力するとすぐ移動できます。
検索用セル、FILTER関数、入力規則の3つを組み合わせれば、大量の候補から必要な項目を探す作業をかなり短縮できます。まずは小さな一覧で試して、使いやすい位置に検索セルとドロップダウンを配置してみましょう。
