2023/07/14エクセルのプルダウン内を検索する方法この記事では、エクセルのプルダウン内を検索する方法をご紹介します。プルダウン内の選択肢を任意の文字で検索して、該当する選択肢のみをリストとしてプルダウンに表示できます。リストが多くてプルダウンから選択肢を選ぶのが大変な場合などにお役立てください。今回は注文表を例に上げて、商品名のプルダウンを任意の文字で検索して選択肢の候補を表示する方法をご紹介します。今回は上の画像のような「注文表」シートを使い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(検索文字列,対象,[開始位置])です。上の画像の1が検索文字列、2が対象の引数として設定されています。ISERROR関数で、FIND関数がエラーかどうかを判定し、エラーの場合は空白を、該当する項目がある場合は何行目かにあるかという行数を返すように設定されています。注文表シートのG3で検索した文字を含む項目があるかどうか、ある場合はリストシートのA列の何行目にあるかを検索しています。上の画像はリストシートのC3セルに入力した数式の解説です。=INDEX(A:A,SMALL($B$2:$B$6,ROW(A1)))INDEX関数とSMALL関数とROW関数を組み合わせて、A列のリストの中でB列で示した数字に該当する行数にある項目を、昇順に並べるという数式を入力しています。数式の中心になるのは、INDEX関数とSMALL関数です。INDEX関数は指定した範囲から指定した行番号の値を返す関数で書式は、=INDEX(配列,行番号,[列番号])です。上の画像の1が配列、2を含むSMALL関数が行番号の引数になっています。1でA列すべてを選び、その中から行番号が小さい順にC列に抽出しています。行番号の引数に使ったSMALL関数は、指定した範囲の中でn番目に小さい値を返す関数で書式は、=SMALL(範囲,順位)です上の画像は注文表シートのH3セルに設定したプルダウンの「元の値」に入力したOFFSET関数です。[幅])で、参照の引数に基準となるセルを指定し、行数や列数などの引数で基準からどれくらい移動するかを指定する関数です。上の画像の1が参照の引数、2を含むCOUNT関数が高さの引数になっています。行数と列数は移動しないので0です。高さはC列に抽出した項目が何行分あるかを指定するものですが、行数は抽出した項目の数によって毎回変わるためCOUNT関数を使ってB列に表示した行数がいくつあるかを数えています。問題は解決できましたか?記事を読んでも問題が解決できなかった場合は、無料でAIに質問することができます。回答の精度は高めなので試してみましょう。インストラクターの経験をいかして、老若男女誰でも分かりやすい記事を書くように心がけています。パソコンやスマホ、Officeなどに苦手意識があってうまく使いこなせない方にも「やっぱり便利!」「楽しい!」と思っていただけますように。コメントこの記事へのコメントをお寄せ下さい。13:00大変参考になりました。検索ですが、G列全てで、この検索を使用するなら、どういう式になりますか?いろいろためしてみたのですが、スピルとなったり、困っています。15:04コメントいただきありがとうございます。たしかにセルの参照が絶対参照($G$3)になっているため、行を追加しても参照が更新されずにG3の値だけを見続けてしまいます。相対参照やG列全体を参照(G:G)にしてもおっしゃる通りスピルになってしまい、数式の範囲を変更するだけでは対応することは難しいようです。代替案として、「注文表」シートのそれぞれの行に対応する「リスト」シートを作成するという方法があります。具体的には、「リスト1」、「リスト2」、「リスト3」...というようにシートを作成し、それぞれのシートで対応する「注文表」シートの行を参照する方法です。この場合、新たに行を追加するたびに新しいシートを作成する必要がありますが、それぞれの行で異なるキーワードに基づいたプルダウンリストを作成することができます。「注文表」シートの行数が多い場合、上記は現実的ではないためVBAを使用することをおすすめします。ご質問に対する直接的な回答ではなく恐縮ですが、ご確認いただけますと幸いです。