Excelでプルダウンを使う際には、「検索機能があれば良いのに」と思うこともあるかもしれません。特にエクセルのデータが多い場合には、プルダウン内を検索できると、選びたい項目もより簡単に見つけられますよね。今回は、エクセルのドロップダウンリストの検索機能を作成する方法をご紹介します。
◎エクセルのプルダウンの予測変換リストを作成する方法[VBAなし]
エクセルのプルダウンが長いと、入力したい内容を探すのにスクロールする必要があり、余計に手間取るかもしれません。エクセルの入力補助でプルダウンを利用しているはずが見つからないとなっては逆効果ですよね。
そこで利用したいのが、Excelのプルダウンで検索した文字列を含む候補だけが表示される機能、サジェスト機能です。エクセルで途中まで入力するとプルダウンにある候補を予測して自動的に絞り込みができます。また、冒頭だけではなく言葉の途中にでも含まれている候補は全て表示されるのが特徴です。エクセルでの予測候補のドロップダウンリスト表示はVBAで行う方法もありますが、ここではマクロは使用せずにできるやり方を見ていきましょう。
◎Excelのドロップダウンリストで検索できるようにする方法①：ふりがなの設定
例では、エクセルで都道府県を選ぶドロップダウンリストを作成する際に一文字を検索すると絞り込みで頭文字が一致するところのみが出てくるようにしてみます。47都道府県あるのでExcelのドロップダウンリストでは検索しても途中から見えません。1文字目の一致に絞るだけでも随分と見やすくなりそうですよね。下準備として、項目を一列に並べておきます。ふりがなに変える必要がない場合は、以下は飛ばして次の項目に進みましょう。
エクセルのドロップダウンリストの検索機能は、プルダウンの参照に書かれている文字列を調べるため、読みで絞り込むことはできません。そこでまずは、漢字をひらがなorカタカナにする方法をみていきましょう。Excelのセル内にある文字列のふりがなを取得できるのがPHONETIC関数です。人名や地名などは漢字で入力されていることが多いですが、この関数を使うことによってエクセルでプルダウンリストを検索する際にひらがなやカタカナの読みで調べられます。
PHONETIC関数の書式は、『=PHONETIC (参照)』です。引数の部分はセル番地とセル範囲のどちらかを指定でき、その中にある文字列をふりがなにしてつなげて返します。たとえば、「北海道」と書かれたセルの隣にふりがなを出す場合には、C2に『=PHONETIC(B2) 』としました。
PHONETIC関数で出てくるふりがなの初期設定は、カタカナです。もしひらがなにしたい場合には、ふりがなの設定を変更しましょう。ここで、選択するセルは、ひらがなにしたいPHONETIC関数が書かれているセルではなく、元の漢字が書かれているセルであることに注意です。エクセル上部の『ホーム』⇒「フォント」内にある「ふりがなの表示/非表示」の右側のvマークをクリックして『ふりがなの設定(T)』を選択します。「ふりがなの設定」画面で「種類」を『ひらがな(H)』を選んで『OK』をクリックしましょう。
あとは、この書式を他のセルにもコピーをすれば第一段階は完成です。一列に並べておくとここでのコピーだけではなくその後の作業も楽に進められます。
関連記事
◎エクセルで予測候補のあるプルダウンの検索機能の作り方②：SEARCH関数で調べる
次に、Excelのプルダウンで検索ボックスに入力した内容が各リストの値に一致するかを調べる設定をします。使用するのはSEARCH関数で、プルダウン内に検索する値を入れた際に、対象のセル番地に指定した検索文字列の最初の文字が何番目にあるのかを表示する機能です。ふりがなを付けた隣のセルに『=SEARCH (プルダウンを作成するセル番地,検索するセル)』などと入力しましょう。
例では、プルダウンは別のページに作っているので『=SEARCH (プルダウン!$D$2) 』などとしました。後にコピーするときに検索文字列を同じセルから参照することを踏まえると、絶対参照にしてからコピーするのをおすすめします。また、大文字と小文字を区別したいときにはFIND関数で『=FIND (プルダウンを作成するセル番地,検索するセル)』とするのも同じことです。
ここで、もし検索文字列がない場合には#VALUEエラーが出てきます。「#VALUE! 」が出ることは、機能上は特に問題がありませんが、見た目が良くないと感じるかもしれません。その場合には、IF関数を使ってエラーが出た場合には空白になるように設定しましょう。使用するのは、IF関数とIFERROR関数です。
●エクセルのサジェスト機能を作成する際に便利な関数
IFERROR関数は、引数にエラーが表示されていた場合にそのエラーの代わりに別の値を表示できる機能です。書式は『=IFERROR(調べる値またはセル参照,代わりに表示する値)』で、通常は空白など非表示にします。今回は調べるセルに「SEARCH(プルダウンを作成するセル番地,検索するセル)」の結果を代入しました。エラーがなければひとつ前の式の結果がそのまま表示されます。
さらに、IF関数では空白ではないセル、つまりエクセルのプルダウンの予測候補に残っているリストが何行目にあるかを表示しましょう。書式は『=IF (左隣のセル<>””, ROW (左隣のセル),””)です。ROW関数とは、参照の行番号を表示する機能で、そのままコピーすると昇順の数字になるため、ドロップダウンリストのように順番はそのままで絞り込みたいときに利用できます。
IF関数のイメージや仕組みについてはこちらの記事でも詳しく触れていますので、あわせて参考にしていただけますと幸いです。
◎エクセルで検索できるプルダウンの作り方③：INDEX関数で絞り込み
エクセルのサジェスト機能を設定するのに次に使う関数は、インデックス関数です。INDEX関数はエクセル内の特定の行や列にある内容を返し、『=INDEX (配列,行番号,[列番号]) 』または『=INDEX (参照,行番号,[列番号], [領域番号]) 』という書式になります。「参照」はプルダウンの項目を一列に並べた列、「行番号」には『SMALL(IF関数の結果を記した列,ROW(A1)) 』と入力しましょう。SMALL関数は、指定した範囲の中で小さい方から数えた順位に該当する数値を求める機能です。数値を比較するため、引数は数値でなければなりません。そこで、先ほどの作業ではROW関数を使いセルが書かれている行番号の数値に置き換えていたのです。また、行で処理する関数のため、プルダウンは一列に並べていました。エラーを表示させないようしたい場合は、先ほどと同様に式の外側をIFERROR関数で囲み、『=B: B, SMALL (F: F, ROW(A1))),””) 』のようにするのがおすすめです。
◎エクセルでの予測候補のドロップダウンリスト表示［VBAなし］④：仕上げ
エクセルのプルダウンに検索機能のある数式を入力するのが最後の作業です。ドロップダウンリストの数式に『=OFFSET (一覧が表示されているシート名!セル番地,0,0, COUNT (一覧が表示されているシート名!SEARCH関数の式を書いた列)』と入力しましょう。OFFSET関数は『=OFFSET (参照,行数,列数,[高さ], [幅]) 』の書式で表され、OFFSET関数は、基準となるセルから行列分ずらした先のセルを参照します。1対1対応であれば行数と列数は0で問題ありません。さらに、高さ・幅を指定することで、そのセルの大きさを変更できます。今回高さでCOUNT関数を指定しているのは、絞り込んだ要素の数のみを取り出すためです。数値にするとエラーや空白もプルダウンに含まれてしまいますが、COUNT関数絞り込んだ要素数のみにすることで求めたい内容が出てくるようになりました。
◎まとめ
今回は、エクセルのプルダウンリストで検索ができるように設定する方法をご紹介しました。エクセルの予測候補が出るドロップダウンリストはVBAを使わなくてもできます。その際のサジェスト機能は関数が多めですが、一つずつ焦らずに設定して、便利なプルダウンを作成しましょう。最後までお読みいただきありがとうございました。
関連記事
コメント