Excel Hack

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

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

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

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

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

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

作業時間:1分

表を作成する

表を作成する

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

もう一つシートを作成する

もう一つシートを作成する

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

任意の検索文字を入力しておく

任意の検索文字を入力しておく

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

選択肢のリストをテーブル化する

選択肢のリストをテーブル化する

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

OKボタンを押す

OKボタンを押す

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

FIND関数を入力する

FIND関数を入力する

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

SMALL関数を入力する

SMALL関数を入力する

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

選択肢がC列に抽出できる

選択肢がC列に抽出できる

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

プルダウンを設定する

プルダウンを設定する

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

元の値にOFFSET関数を入力する

元の値にOFFSET関数を入力する

「データの入力規則」ダイアログボックスが表示されます。「入力値の種類」でリストを選択し、「元の値」に=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(範囲,順位)です

OFFSET関数について

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

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

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

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

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

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

このページを見ている人におすすめの商品

Excel 最強の教科書[完全版] 【2nd Edition】

Excel 最強の教科書[完全版] 【2nd Edition】

SBクリエイティブ
360ページ

\Kindle本ビジネス書キャンペーン 最大70%OFF/Amazonで価格を見る

\FLASH COUPON 最大1,500円OFFクーポン/楽天市場で価格を見る

\Yahoo!プレミアム会員限定 最大50%OFFクーポン/Yahoo!ショッピングで価格を見る

ビッグセール情報

  • ビッグセール情報

コメント

この記事へのコメントをお寄せ下さい。

0 コメント
Inline Feedbacks
View all comments