エクセルのドロップダウンリスト（プルダウンリスト）って、便利なようですがリストが増えると選択するのが大変なんですよね。
入力候補が多数あると、入力したい値を探すのに一苦労です。
データを項目ごとに細分化しても限界があります。
ページコンテンツ
ドロップダウンリスト（プルダウンリスト）に予測候補（サジェスト機能）を表示！
GoogleやYahoo！、Bingで検索するときも、数文字入力すると予測候補が表示されますよね。
ところがエクセルには、不思議なことにこの機能がないのです。標準でついていても不思議ではないのですが、Excel2019でも365でもこの機能はまだ未実装です。
自分は普段VBA（ビジュアルベーシックアプリケーション）を利用して予測候補表示を利用していますが、今回はVBA（マクロ）を利用しないで数文字入力すると候補が絞られる予測候補表示の方法についてご紹介します。
ドロップダウンリスト（プルダウンリスト）は集計する側からすると便利な機能なのですが、入力する側からすると、リストが増えると地獄のような選択肢から選ぶ必要があります。
マウス操作よりキーボード操作を多用される方にとっては非常に役に立つ、関数を利用した予測候補表示の機能です。
予測候補表示（あいまい検索）は非常に便利！
こちらは動作仕様の紹介のために作成した東京新宿区の郵便番号一覧（抜粋）です。
機能紹介用にずいぶん絞りましたが、例えば「南蒲田」を選ぶ際には、22個のデータをスクロールする必要があります。
今回は例として「蒲田」と入力すると、文字列「蒲田」を含む候補だけがドロップダウンリスト（プルダウンリスト）に表示される機能（サジェスト機能）をVBAを利用せず、関数だけで表示する方法の紹介です。
また、入力する文字は、先頭、末尾または部分が合致していれば候補になります。
「調布」という文字を入力すると、文字列の真ん中にあっても候補として表示されます。
いかがですか？ドロップダウンリスト（プルダウンリスト）を入力する側としては非常に入力に優しい機能です。
それでは具体的にこの機能を利用する手順を紹介します。
予測候補表示（あいまい変換）に必要なシートは3つ
予測候補表示（あいまい変換）に必要なシートは3つあります。事前に準備が必要です。
具体的には、「予測候補表示に必要なデータベース」のシート、検索候補を表示する「検索候補」シートと最後に「入力フォーム」シートの3つです。
今回利用する関数は、「IF」「ISERROR」「VLOOKUP」「INDEX」「ROW」「SMALL」の6つの関数です。
難しく感じるかもしれませんが、マスターすればきっと超便利な予測候補表示（あいまい検索）対応、ドロップダウンリスト（プルダウンリスト）が完成します。
予測候補表示（あいまい変換）に必要なデータシート
まずは、普通の郵便番号データベースシートを用意します。「A列」に町名、「B列」に郵便番号です。
そして「C列」に数式を入力します。
ここで、ちょっと難しい関数を複数利用する必要があります。
C列（ここでは、データの個数である22個、C22セルまで）に下記の数式を入力します。
=IF(ISERROR(FIND(予測検索!$A$2,A1)),””,ROW)
上記のA1の部分は、C1セルに数式を入力して、セルのコピーをしてA22まで変化させてください。
関数の説明 IF関数
IF関数はご存知の方も多いと思いますが、「=if(論理式,論理式が正ならばどうするか,論理式が偽ならばどうするか）という、3つの引数が必要です。
関数の説明 ISERROR関数
IF関数の1つ目の引数にISERROR関数を利用しています。ISERROR関数は「true（真）」か「false（偽）」かの値を返します。ここでは、この後説明するFIND関数で文字列が見つかれば「true」を、見つからなければ「false」を引数として返します。
=iserror(論理式,真なら表示する値,偽なら表示する値)、となります。
今回は、エラー（文字列が見つからない）を真として””（空白）を表示、偽ならば（エラーでなければ）後で説明するROW関数てそのセルの行番号を表示する設定をしています。
関数の説明 FIND関数
FIND関数は、指定した文字列（ここでは、別シートの「予測検索」のA2セルに入力した文字列）があるかどうかを判定するために利用しています。
本来は、検索文字列が何文字目にあるかを返す関数ですが、今回は対象の文字列があるかどうかを判定するためだけに利用しますので、何文字目かの引数は省略しています。
=find(指定した文字列,検索対象セル（1行目はA1セル）)として、文字列が見つかれば何文字目かを返し（エラーにならない）、文字列が見つからなければエラーを返します。
=find(予測検索!$A$2,A1)で、「予測検索シートのA2セル」の文字列（蒲田）が「郵便番号」シートのA1セル（からA22セル）に見つかれば「true」、見つからなければ「false」ということで先ほどのISERROR関数と連動しています。
関数の説明 ROW関数
ROW関数は、その名の通り行番号を返します。引数はありません。
どのような処理をしているかを言葉で説明すると
=IF(ISERROR(FIND(予測検索!$A$2,A1)),””,ROW)の関数の意味を言葉で説明すると、以下のような感じです。
＝もし（エラーなら空白（””）にしてください、でも指定した文字列が対象セルから見つかればそのセルの行番号を教えてください）という感じです。
数文字入力した文字列が見つかれば行番号をC列に表示し、見つかれなければ空白にします。
「予測検索」シートに「蒲田」という文字を入力すると、A列に「蒲田」を含む行にはC列にその行番号を、含まない行は「」（空白）が表示されます。
これでデータベースシートは完成です。
候補を絞り込む検索候補シート
続いて、先ほどデータベースシートで目的の文字列を含むデータがあれば、行番号が小さい順に検索候補を表示する、「検索候補シート」の設定です。
「検索候補」シートには、「A列」に「=(INDEX(郵便番号!A:A,SMALL(郵便番号!C:C,ROW(A1)),1))」という数式を入れます。
関数の説明 INDEX関数
INDEX関数は、「=INDEX（参照範囲・ここでは郵便番号シートの地名,行番号,列番号）」という3つの引数を参照して値を返します。
参照範囲にしている「郵便番号」シートのA列で、指定する行のセル（行番号）、指定する列（ここでは1、すなわちA列）の値を表示する設定しています。指定する行番号は次に説明するSMALL関数で取得しています。
関数の説明 SMALL関数
SMALL関数は、名前の通り範囲の中で小さい方から何番目かの数値を指定し、その数値を取り出します。
先ほどの数式の一部、「SMALL(郵便番号!C:C,ROW(A1))」は、「郵便番号」シートC列から、「蒲田」が含まれるセルがあれば行数を表示させていますので、「検索候補」シートの「A1」には、「郵便番号」シートのC列で「ROW(A1)」（1番小さい値）を取得します（ここでは7という行番号）。
同様に「検索候補」シートのA2セルには「SMALL(郵便番号!C:C,ROW(A2))」 で2番目に小さい8行目の「蒲田本町」を、A3セルには「SMALL(郵便番号!C:C,ROW(A3))」と3番目に小さい12行目の「西蒲田」が表示されます。
「=(INDEX(郵便番号!A:A,SMALL(郵便番号!C:C,ROW(A1)),1))」の数式の意味を言葉で説明すると、「検索候補」シートのA1セルには「郵便番号」シートの中で「予測検索」シートのA2セルに入力された文字列を含むセルの行数で一番小さい行の地名を、「検索候補」シートのA2セルには、二番目に小さい行の地名を、とエラーが出るまで繰り返しています。
最後に入力フォームシート
「予測検索」シートの「A2」セルには、データの入力規則でセル範囲の名前「=町名」という名前を設定しています。
「データの入力規則」の「元の値」は、可変式データリストを使用しています。
名前の定義「町名」は、可変式リストを利用しています。
セルの名前を可変式（メンテナンスいらず）にする方法はこちらの記事をご覧ください。
今回は、可変式リストのカウントを、「郵便番号」シートのC列をカウントしています。
検索候補として表示される数をカウントすることによって、エラーのあるデータは表示されないようになっています。
エラーではない個数をカウントしていますので、上記の「#NUM!」エラー以降のデータは表示されません。可変式リストの便利なところですね。
最後の関数の解説 VLOOKUP関数と思わず使ったIFERROR関数
VLOOKUP関数は便利なのですが、ちょっとした勘が必要な関数でもあります。
今回は、「予測検索」シートの「B3」セルに利用しています。
ここに来てすみません、最後の最後に思わず「IFERROR関数」を使ってしまいました。
「予測変換」シートのB3セルには「=iferror(vlookup(a3,郵便番号,2,false,””)」という式を入力しています。
IFERROR関数は単純で「=IFERROR(論理式,エラーの場合の処理）」となります。
「ISERROR」関数よりも単純で、エラーでなければ最初の論理式の結果を表示、エラーの場合はどうするか（ここでは””、空白にする）を定義します。
今回は、VLOOKUP関数がエラーでないときはその値を返し、エラーのときは””（空白）にするという意味です。
改めて関数の解説 VLOOKUP関数
VLOOKUP関数は、「=VLOOKUP(検索する値（ここではA3セルに入力された値）,検索する範囲（ここでは町名と郵便番号の２列のリスト範囲）,リスト範囲の何列目のセルの値を表示するか（ここでは２列目B列）,FALSE（完全一致）かTRUE（最も近い値）)」と4つの引数を必要とします。
検索する範囲は名前で定義しています。A列が町名、B列が郵便番号となっています。
ということで、今回のVLOOKUP関数では、「予測変換」シートB3セルに入力された値と完全一致する値を「郵便番号」シートの値から検索し、完全一致したセルの行を検索し、その行から指定した列（2列目、B列）の値を表示するということになります。
これで完成です。お疲れさまでした。
やはりVBAと利用しないと限界がある
自分は普段はVBAを利用して予測候補を表示して利用しています。
今回の記事はVBA（マクロ）を利用しないで関数だけで予測候補表示（あいまい検索）を実現しました。
ただ、一つのセルだけで予測候補表示（あいまい検索）をすることは可能ですが、複数のセルに予測候補を表示させるためにはマクロを利用する必要がありそうです。
ぜひエクセル（Excel）の標準機能に追加を希望
エクセル365は進化し続けていますが、いまだに予測候補絞り込み機能はまだ実装されていません。
リストボックス（プルダウンリスト）で予測候補絞り込みの機能は自分も含む皆さんが切望している機能だと思います。
ウェブで検索するときはどの検索エンジンでも利用できるのですから、ぜひともエクセル（Excel）の標準機能として装備していただきたいものです。
今回の記事は、非常に複雑な処理をしています。できるだけ分かりやすく解説したつもりですが、不明な点がありましたらコメント欄にご質問をお願いいたします。
今回の記事は、こちらの記事を参考にさせていただきました。
コメント