Chiquilin Site■10.07.14_Excel：ドロップダウンリストを絞り込む１
ChiquilinSite
選択セルに入力規則のリスト（ドロップダウン）を設定する人は多いと思いますが データが増えてくるといちいち選択するのも一苦労になります。例えば頭の文字「あ」を入れたら あ行のデータだけに絞り込みたいと考えるのは自然な欲求だと思います。ただ実際それを行おうと思ったら 一定の手順を踏む必要があります。
単純な話ですが「元のリスト」に対して「絞り込んだ後のリスト」というものを別途作成する必要があります。その際 絞り込みの条件となる頭文字などは別途元データに１列追加して入力しておく必要があります。今回は例として「年齢早見表」を作ろうと思います。リストシートは下記のようなものになります。
■Ｆ１セル
=TODAY()
■D3セル
=DATEDIF(C3,リスト!F$1,"Y")
フィルハンドルをダブルクリック
ここは今回とはあまり関係ないので すんなりと流します。続いて早見表シートも作成しておきます。
ここで注意が必要なのは 入力規則のリスト以外の値を入力してもエラーが起きないようにしておくことです。でないと頭文字を入力しただけでエラーメッセージが出ますので。
Ｂ４:Ｂ１６セルを選択して[データ]→[入力規則]
→「エラーメッセージ」タブ
→[無効なデータが入力されたらエラー メッセージを表示する]の
チェックを外す
これは一応ですが
■C4セル：年齢計算
=IF(B4="","",IF(COUNTIF(リスト!A$3:A$202,B4),"",
VLOOKUP(B4,リスト!B$3:D$202,3,FALSE)))
フィルハンドルをダブルクリック
リストの設定は後でやります。とりあえず絞り込みリストの作成に移行します。
★その２：絞り込みリストの作成
絞り込みリストについては
抽出データを詰めて表示する に作り方を載せてますので詳細については省略します。尚 FILTER関数が使えるなら下記の手順はほぼいりません。
■E1セル
=INDEX(早見表!B:B,COUNTA(早見表!B:B)+1)
※入力順序が上から順々とは限らない場合は
=INDEX(早見表!B:B,CELL("row"))
■E3セル
=IF(AND(A3<>"",A3=E$1),E3+1,E3)
フィルハンドルをダブルクリック
■F3セル
=IF(E$2<ROW(F1),"",INDEX(B:B,MATCH(E$2-ROW(F1),E$2:E$201,-1)+1))
フィルハンドルをダブルクリック
E1セルの式は 早見表のリスト入力が順に下方向に入力するのを見越して入れています。例えば早見表シートが B5セルまで入力されている場合 次に頭文字が入力されるセルは B6セルにする必要があります。つまり常に最終行が絞り込みの対象となるようにしている訳です。
FILTER関数が使えるバージョンの場合
■F3セル
=IFERROR(FILTER(B:B,A:A=E1),"")
でいいです。この場合 後の範囲指定にスピル範囲演算子も使えます。
★その３：選択リストの作成
これでリストは完成。次は早見表シートに移動して実際の選択リストを作成します。
[挿入]→[名前]→[定義]
名 前：通常リスト
参照範囲：
=リスト!$B$3:INDEX(リスト!$B:$B,COUNTA(リスト!$B:$B)+1)
名 前：抽出リスト
参照範囲
=IF(リスト!$F$3="",通常リスト,
リスト!$F$3:INDEX(リスト!$F:$F,COUNTIF(リスト!$F:$F,"*?")+1))
B4:B16を選択した状態で [データ]→[入力規則]
[入力値の種類]を[リスト]
[元の値]を「=抽出リスト」にして[OK]
これで準備は完了です。実際に入力してみましょう。
「あ」と入力した状態で リスト選択の「▼」マークを押すと 元データの[読み]が「あ」のデータだけが絞り込んで表示されています。今回は頭文字（読み）を入力しない状態では 通常リストが表示されるようにしています。この絞り込みリストは工夫次第で 色んな作り方が可能です。
→参考ファイル(045.xlsx)
