エクセルでプルダウン式の検索窓を作る方法

この記事では、エクセルでプルダウン式の検索窓を作る方法をご紹介します。

複数の関数を組み合わせることで、検索した結果のみをプルダウンの選択肢に表示することができるようになります。

関数の詳しい説明もご紹介していますので、数式をアレンジして使用する際にお役立てください。

エクセルでプルダウン式の検索窓を作る方法

今回は都道府県リストから検索文字が含まれる都道府県名だけをプルダウンの選択肢として抽出する方法をご紹介します。

今回は上の画像のような表を使用します。A3セルに入力した検索文字をC列から検索し、該当する都道府県名のみをA5セルのプルダウンに抽出するように設定します。あらかじめA3セルに任意の検索文字（例：山）を入力しておきます。

D3セルにCOUNTIF関数を入力します。C列の都道府県名の中にA3セルに入力した検索文字が含まれる都道府県名があるかどうかを検索するので、=COUNTIF($C$3:C3,"*"&$A$3&"*")を入力しEnterを押します。数式の詳細については下記「プルダウン式の検索窓を作るための数式について」セクションをご紹介しています。

COUNTIF関数をD列のセルにコピーします。D3セルの右下にあるフィルハンドルを下に向かってドラッグします。

次に検索文字が含まれる都道府県名だけを抽出する数式として、=IFERROR(INDEX(C:C,MATCH(ROW(1:1),D:D,0),1),")をF3セルに入力しEnterを押します。関数の詳細については、下記「エクセルでプルダウン式の検索窓を作る方法」セクションをご確認ください。

関数をF列のセルにコピーします。F3セルの右下にあるフィルハンドルを下に向かってドラッグします。

F列を確認するとA3セルに入力した検索文字が含まれる都道府県名が抽出されていることが分かります。次にプルダウンを設定します。プルダウンを設定したいセル（例：A5セル）、データタブ、「データの入力規則」の順に選択します。

「データの入力規則」ダイアログボックスが表示されます。「入力値の種類」でリストを選択し、「元の値」に=OFFSET(F3,0,0,COUNTIF(F:F,"?*")-1,1)と入力してOKボタンを押します。数式はOFFSET関数でF3セルで空白以外のセル、つまり文字が入っているセルを抽出するという意味です。数式の詳細は下記「エクセルでプルダウン式の検索窓を作る方法」セクションをご参照ください。

これでプルダウンの選択肢を抽出する設定ができました。A5セルのプルダウンの三角ボタンを押すと、F列の検索結果が選択肢に抽出されていることが確認できます。

A3セルに別の選択肢（例：宮）を入力すると、該当する検索結果がプルダウンの選択肢に反映されます。

今回ご紹介した方法は任意の文字で検索できるため、例えばA3セルに「崎」と入力すると「長崎県」「宮崎県」が抽出されます。

プルダウン式の検索窓を作るための数式について

上記「エクセルでプルダウン式の検索窓を作る方法」セクションでは、複数の関数を組み合わせた数式を使用します。

以下に数式の詳細を記載しますので、数式をアレンジする際の参考にしてください。

上の画像はA3セルに入力する「=COUNTIF($C$3:C3,"*"&$A$3&"*")」という数式の解説です。

=COUNTIF($C$3:C3,"*"&$A$3&"*")

COUNTIF関数は指定した範囲の中で検索したい値がいくつあるかを数える関数で、書式は=COUNTIF(範囲, 検索条件)です。

上の画像の①が範囲、②が検索条件の引数になります。

①は「$C$3:C3」とすることで行数が増えてもオートフィルでコピーすることができます。

また、②を「"*"&$A$3&"*"」とすることでA3セルで指定した検索文字が都道府県名のどこかに入っていれば抽出することができるようにできます。

*（アスタリスク）はワイルドカードと呼ばれるものです。「"*"&$A$3&"*"」のように&で「*」を追加することで、A3セルに入力した検索文字が単語のどこにあっても抽出されるように設定できます。

もしも「A3セルの検索文字が先頭にくる都道府県名を抽出したい」という場合は「$A$3&"*"」と入力しましょう。

上の画像はF3セルに入力する「=IFERROR(INDEX(C:C,MATCH(ROW(1:1),D:D,0),1),")」という数式の解説です。

=IFERROR(INDEX(C:C,MATCH(ROW(1:1),D:D,0),1),")

IFERROR関数、INDEX関数、MATCH関数、ROW関数が組み合わさっています。

中心になるのはINDEX関数です。INDEX関数は指定された行と列が交差する位置にあるセルを返す関数で、書式は=INDEX(配列,行番号,[列番号],[領域番号])です。

今回はINDEX関数の行番号②のように、行番号の引数にMATCH関数とROW関数が使われています。「MATCH(ROW(1:1),D:D,0)」はD列の1行目から数えて「1」が最初に出てくるセルの行番号を数えるという意味になります。

上の画像はデータの入力規則の「元の値」に入力する「=OFFSET(F3,0,0,COUNTIF(F:F,"?*")-1,1)」という数式の解説です。

=OFFSET(F3,0,0,COUNTIF(F:F,"?*")-1,1)

OFFSET関数は指定したセル/セル範囲から指定された行数と列数分移動した位置にあるセル/セル範囲を返す関数で、書式は=OFFSET(参照,行数,列数,[高さ], [幅])です。

上の画像の①が参照、②が行数、③が列数、④が高さ、⑤が幅の引数になっています。

①は抽出するスタートのセルを選択します。

行数と列数に変更はないので②と③は0です。

④の高さの引数は結果として返したい行数を指定します。COUNTIF関数を使って「F列の中で値が入力されているセル」の数を数えています。今回だと「4」になりますが、「-1」を入力することで見出しの行を含めず「F3セルから3行分」という意味になります。

「?*」はワイルドカードと呼ばれる任意の文字列を記号で表したもので、?（クエスチョンマーク）は任意の1文字、*（アスタリスク）は0文字以上の任意の文字列という意味です。

「?*」は「1文字以上の任意の文字列」という意味で、1文字以上の文字列が入っているセルを数えるように設定してあります。

⑤は幅の引数は結果として返したい列数を指定します。今回はF列のみですので1列分ということで「1」を入力します。