概要
長くなってしまったプルダウンリストを任意のキーワードで絞り込んで表示させるExcelの技を紹介します。
特徴
・プルダウンリストに表示する項目を任意のキーワードで絞り込むことができる
・キーワードによる部分検索が可能
・項目追加の際は数式などの変更は一切必要なし(全て自動で反映)
・Excel2007をベースに作っているため、ほぼ全てのバージョンで対応可能
工夫した点・テクニックなど
(基本テクニック)
名前の定義や絶対参照、相対参照、複合参照、ワイルドカード、テーブル機能の活用など
特に複合参照については今回とても重要となります。
(応用テクニック)
みんな大好きVLOOKUP関数で複数の該当データを抽出し、OFFSET関数でリストの参照範囲を可変にしています。
複合参照とは
「$A$1:A1」のように、範囲選択の先頭を絶対参照、末尾を相対参照にすることでオートフィルした際に先頭位置を変えずに範囲のみを広げることが出来ます。
完成イメージ
サンプルファイルのダウンロード
数式や設定、作成手順を解説
サンプルファイルには以下２つのシートがあります。
入力用シートの作成
キーワード入力用のセル範囲に「ワード」という名前を定義します。
項目リストの作成
以下のように項目を入力し、表全体をテーブルに変換します。
テーブル名を「項目一覧」に変更し、絞り込み結果の表示用セル1行目に「結果」という名前を定義しておきます。
表全体をテーブルにしておくと、先頭行に数式を入力するだけで全体にオートフィルがかかり、手動で数式のコピー(オートフィル)をする手間が省けます。
また、新たな項目の追加など行数を増やした場合も自動で数式がコピーされます。
数式を入力する
1.「項目リスト」シートの作業列「A列」に以下数式を入力する
※複合参照を使用しています。
どのように表示されるかは項目リストの検索列を参照してください。
2.「項目リスト」シートの「絞り込み結果」表示用セル1行目に以下数式を入力する
※C列は分かりやすくする為あえて追加しましたが、実際は不要です。
また、INDEX+MATCH関数を使用すればA列の数式をD列に組み込むこともできます。(A列が不要になる)
連番を取得する関数については「ROWS($D$3:$D3)」の代わりに「ROW(A1)」または「ROW()-2」としてもOKです。
3.「入力用シート」の「ヒット数」表示用セルに以下数式を入力する
COUNTA関数は見た目が空白でも数式が入っていると「1」としてカウントします。
COUNTBLANK関数は数式が入っていても値が空白の場合は「1」としてカウントします。
つまり「実質行数 = COUNTA – COUNTBLANK」となります。
※上記数式により、INDEX+MATCH関数で数式を１つにまとめ、検索列(作業列)をなくした場合にも対応できるようにしましたが、今回はVLOOKUP関数で作業列を使用しているため、MAX関数で検索列から最大値を取得してもOKです。
4.「入力用シート」の「エラー回避用」セルに以下数式を入力する
5.「入力用シート」のデータ入力セルに以下の[データの入力規則]を設定する
キーワード検索でヒットしなかった場合は実質行数0となるが、OFFSET関数で指定する際にエラーとなるため、該当なしの場合は実質行数1としてエラーを回避しています。
終わりに
Excelには様々な制限があります。
VLOOKUP関数では最初または最後に該当したデータ１つしか抽出できません。
プルダウンリストは項目数が増えても最大8項目しか表示できず、スクロールしなければなりません。
しかし工夫次第で解決できることも多く、それを考える楽しさがあり、、まぁとどのつまりExcelは楽しいということです。
コメント