プルダウンのリストを部分検索で絞り込めるように作る【エクセル関数】

プルダウンの選択肢が多すぎると選ぶのが大変なので、指定した文字「◯◯を含む」のような部分検索欄を設けて、その条件に該当する値だけをプルダウンに表示させる方法です。

少し分かりづらいと思いますが、やたらと長い構文やレアな関数は使わず、比較的メジャーな関数の特性を活かした形になっています。

使用する主な関数

プルダウンリストの部分検索

実際には商品リストや管理番号などで活用するイメージですが、ここでは都道府県を例として説明します。

使用するエクセルファイルは1つで、シートは2つ使います。プルダウンリストの元となるデータを入れておくデータベース用シートと、もう1つは実際に部分検索やプルダウンを選択する入力用シートです。

エクセルの中身

データベース用シートには、実際にプルダウンの元となるリストの一覧（ここでは都道府県）と、仕掛け用の関数をいくつか作ってあります。

B3の関数【No】※下のセルまでコピー

=IF($D3="〇",COUNTIFS($D$3:$D3,"〇"),"")

【説明】同じ行のD列に◯がある場合、その◯が「上から何個目の〇か」を返す

C列【都道府県】

【説明】都道府県の一覧（関数なし）

D3の関数【対象】※下のセルまでコピー

=IF(COUNTIFS($C3,$G$1)>=1,"〇","")

【説明】同じ行にある都道府県（C列）に部分検索の文字（G1）が含まれていたら◯を表示する

G1の関数【部分検索】

="*"&入力!$C$2&"*"

【説明】「入力」シートの部分検索欄に入力された文字を「＊」で囲む

入力用シートには、実際にプルダウンで都道府県を選択する列、部分検索ワードの入力用セル、プルダウン選択候補の一覧などを作っておきます。

C2【部分検索の入力欄】

【説明】部分検索したい文字を入力するためのセル（関数なし）

C列 ※C5より下【都道府県プルダウン】

【説明】プルダウンリストから都道府県を選択する列（入力規則でリスト設定 / 範囲はJ5:J14）

I列【No】

【説明】プルダウンに表示させる最大数まで1から連番を入れておく（関数なし）

J5の関数【候補】※下のセルまでコピー

=IFERROR(VLOOKUP($I5,データベース!$B:$C,2,0),"-")

【説明】「データベース」シートのB列を参照して、番号の右にある都道府県を返す

都道府県を例として説明しましたが、実際には似たような名称が多数存在するリストなど、絞り込みに向かないデータもあると思います。

一概にこの方法がベストだとは思いませんが、このページに辿り着く方は「なぜ私が？」と関係ない仕事まで頼まれてしまい、やらなくていいのに結局は調べてやってしまうような方が多いと思いますので、上手くアレンジしてご活用いただけると思います。

入力用シートのJ列に「候補」の列がありますが、候補対象は都道府県を表示して対象外は「-」を表示させています。最初はハイフンではなく空欄にしていましたが、画像のようにプルダウンリストが下寄りに表示されてしまいました。ハイフンに限らず何らかの文字を入れておけば上寄せで表示されると思います。

また、この候補リストは「データベース」シートに置きたかったのですが、理由は忘れましたが上手くいかず仕方なく「入力」シートに移動しました。

大勢の人がこのエクセルを使用する場合、勝手に行を追加したり削除したりで関数が崩れる可能性があるので、シート保護などの対策が必要です。

この仕組みで一番注目したいポイントは「データベース」シートB列のナンバリング用の関数です。

関数をよく使う人にとっての$は「はいはいはい分かってます」という記号ですが、忙しい時や疲れている時は考えすぎて訳が分からなくなることもあります。

このCOUNTIFSの$の使い方、特に最後の「$D3」は簡単なようで難しい、難しそうで実はシンプル。為になる使い方です。

=IF($D3="〇",COUNTIFS($D$3:$D3,"〇"),"")