検索できる高度なドロップダウンリスト後編

動画解説

動画版「Excelスキルアップ仕事術」です。
前回は入力されたカタカナの1文字を元にお客様情報の中から該当するお客様を抽出することができました。

それを今度はドロップダウンリストに設定します。また別シートの複数のセル範囲にドロップダウンリストを設定する場合に便利な方法も考えてみました。エクセルの技と関数がたくさんつまっています。
（サンプルファイルは、こちらです。サンプルファイルのダウンロード）

今回は次の内容を説明します。
1.取得した対象をドロップダウンリストに表示する。
前回までの内容では検索対象の「オ」のつく苗字があるかないか、そしてその対象文字列を抜き出してE列に表示するというところまででした。
今回は取得した対象をドロップダウンリストに表示するという内容です。
D 列にはその対象が出たのは何番目かということを表示しています。
E列では、 ROW関数で出した数字がD列ののどこにあるのかを、探させる数式を入れています。
INDEX関数でMATCH関数から受け取った行番号の内容をA列で探して表示します。
E列の表示結果にエラーを出さないためにエラー回避の数式に変更します。
=IFERROR(INDEX(A:A,MATCH(ROW(C1),D:D,0)),0)

これから検索文字と検索結果を表示するドロップダウンリストの式を考えていきます。
どのような数式なら可能か、余白で数式を試作していきます。
OFFSET関数を利用しますが、他のシートからの参照を考えて数式に、Sheet1！とつけてやります。
データの個数分移動します。データの個数はCOUNTIF関数を使って数えます。
OFFSET関数を使って、E列のデータを別セルに表示することができました。OFFSET関数の引数の行、列の代わりに、高さと幅を使った方法です。
0と余分なデータが表示されているのは、COUNTIFでタイトルの文字列も数えたからなので、-1してやります。

ドロップダウンリストを表示させたいセルを選択して、データの入力規則を表示します。
入力値の種類をリストにし、元の値のところに、先ほど余白で練習した時に入力した数式を、そっくりそのまま貼り付けてやります。
ここも重要です。検索用のカタカナが入力できるように、エラーメッセージタブを選択して、「無効なデータが入力されたらエラーメッセージを表示する」のチェックを外します。
その次に日本語入力タブを選択して、日本語入力を全角カタカナにします。これでカタカナの入力が可能になります
以上の設定が完了すると、このようにH2セルで、カタカナ入力でエと入力すると、苗字の2文字の中にエを含む人がリストとして表示されます。
検索対象を絞り込むには、苗字先頭１文字だけにしてやればよいです。
このように他のシートからも参照が可能です。
お名前を入力するセルでは、どの列を選択しても、カタカナで先頭文字を入力して検索できるようにしました。
上記の図のように、ドロップダウンリストを複数表示して検索可能にするには、以下のように数式を変更してください。
=IFERROR(SEARCH(INDIRECT(CELL("address")),LEFT(B2,2)),0)