エクセルのプルダウン内を検索する方法

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

プルダウン内の選択肢を任意の文字で検索して、該当する選択肢のみをリストとしてプルダウンに表示できます。

リストが多くてプルダウンから選択肢を選ぶのが大変な場合などにお役立てください。

エクセルのプルダウン内を検索する方法

今回は注文表を例に上げて、商品名のプルダウンを任意の文字で検索して選択肢の候補を表示する方法をご紹介します。

まずは表を作成します。今回は上の画像のような「注文表」シートを使いH3セルにプルダウンを設定します。そのプルダウンを、G3セルの「商品検索」のセルに入力した任意の文字で検索し該当する選択肢のみがH3セルに候補として表示されるように設定します。

もう一つ新しいシート（例：リスト）を作成し、上の画像のA列のようにプルダウンの選択肢として表示するリストを入力しておきます。

操作を分かりやすくするためにあらかじめ注文表シートのG3セルに任意の文字（例：パ）を入力しておきましょう。

まずは選択肢のリストをテーブル化します。リスト（例：A1セルからA6セル）、挿入タブ、テーブルの順に選択します。

「テーブルの作成」ダイアログボックスが表示されます。「先頭行をテーブルの見出しとして使用する」にチェックマークを入れて、OKボタンを押します。

これでリストをテーブル化することができました。次にB2セルに、注文表シートのG3セルに入力した任意の文字が商品名の中に含まれるかどうかをチェックする数式を入力します。B2セルに=IF(ISERROR(FIND(注文表!$G$3,A2)),"",ROW())と入力してEnterを押します。数式の詳細については下記「プルダウン内を検索するための数式について」セクションをご確認ください。

A列をテーブル化しているためB列に入力した数式が自動的に他のセルにもコピーされました。次に、B列に表示した行数に対応するA列の項目をC列に抽出するように数式を入力します。C3セルを選択し、=INDEX(A:A,SMALL($B$2:$B$6,ROW(A1)))と入力してEnterを押します。数式の詳細については下記「プルダウン内を検索するための数式について」セクションをご確認ください。

これでC列に選択肢を抽出することができました。今回は先ほど注文表シートのG3セルに「パ」と入力しているため、A列から「パ」の文字がある項目だけが抽出されています。

次に注文表シートに移動してプルダウンの設定を行います。プルダウンを設定したいセル（例：H3セル）、データタブ、データの入力規則の順に選択します。

「データの入力規則」ダイアログボックスが表示されます。「入力値の種類」でリストを選択し、「元の値」に=OFFSET(リスト!C2,0,0,COUNT(リスト!B:B))と入力しEnterを押します。OFFSET関数でリストシートのC列に抽出した項目をプルダウンの選択肢として表示するように設定しています。数式の詳細については下記「プルダウン内を検索するための数式について」セクションをご確認ください。

これで設定が完了しました。H3セルのプルダウンをセル右側の三角ボタンを押して表示させてみると、G3セルに入力した「パ」が含まれる選択肢のみが表示されていることが分かります。

試しに、G3セルに「キ」と入力してみます。H3セルのプルダウンをセル右側の三角ボタンを押して表示させてみると、「キ」が含まれる選択肢のみが表示されていることが確認できます。これでプルダウン内を検索して、該当する選択肢のみを表示させる設定ができました。

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

上記「エクセルのプルダウン内を検索する方法」セクションでご紹介した方法ではプルダウン内を検索できるようにする設定のために複数の関数を組み合わせた数式を使用します。

以下では各数式の詳細をご紹介しますので、数式をアレンジする際に参考にしてみてください。

上の画像はリストシートのB2セルに入力した数式の解説です。

=IF(ISERROR(FIND(注文表!$G$3,A2)),",ROW())

IF関数とISERROR関数とFIND関数とROW関数を組み合わせて、リストシートのA列にある項目の中で注文表シートのG3セルに入力した任意の文字が含まれている項目は何行目にあるかを数字で表しています。

数式の中心となるFIND関数は検索文字列がセルの値に含まれているかどうか、含まれているなら何文字目かを数値で返す関数で引数は、=FIND(検索文字列,対象,[開始位置])です。

上の画像の①が検索文字列、②が対象の引数として設定されています。

またISERROR関数で、FIND関数がエラーかどうかを判定し、エラーの場合は空白を、該当する項目がある場合は何行目かにあるかという行数を返すように設定されています。

つまり、注文表シートのG3で検索した文字を含む項目があるかどうか、ある場合はリストシートのA列の何行目にあるかを検索しています。

上の画像はリストシートのC3セルに入力した数式の解説です。

=INDEX(A:A,SMALL($B$2:$B$6,ROW(A1)))

INDEX関数とSMALL関数とROW関数を組み合わせて、A列のリストの中でB列で示した数字に該当する行数にある項目を、昇順に並べるという数式を入力しています。

数式の中心になるのは、INDEX関数とSMALL関数です。

INDEX関数は指定した範囲から指定した行番号の値を返す関数で書式は、=INDEX(配列,行番号,[列番号])です。

上の画像の①が配列、②を含むSMALL関数が行番号の引数になっています。

①でA列すべてを選び、その中から行番号が小さい順にC列に抽出しています。

行番号の引数に使ったSMALL関数は、指定した範囲の中でn番目に小さい値を返す関数で書式は、=SMALL(範囲,順位)です

上の画像は注文表シートのH3セルに設定したプルダウンの「元の値」に入力したOFFSET関数です。

=OFFSET(リスト!C2,0,0,COUNT(リスト!B:B))

OFFSET関数の書式は=OFFSET(参照,行数,列数,[高さ], [幅])で、参照の引数に基準となるセルを指定し、行数や列数などの引数で基準からどれくらい移動するかを指定する関数です。

上の画像の①が参照の引数、②を含むCOUNT関数が高さの引数になっています。

行数と列数は移動しないので0です。

高さはC列に抽出した項目が何行分あるかを指定するものですが、行数は抽出した項目の数によって毎回変わるためCOUNT関数を使ってB列に表示した行数がいくつあるかを数えています。