Chiquilin Site■10.07.14_Excel：ドロップダウンリストを絞り込む２
ChiquilinSite
今度は分類名を指定してリストを絞り込む方法で。こっちの方が一般的かもしれません。私はこのやり方嫌いなんで あまりお勧めしません。というのも作るのは簡単かもしれませんが 管理が若干面倒です。一回作ったら後メンテナンスの必要はほぼない という場合にはいいかもしれません。
リストシートを作成します。画像のようなものを作成します。
上端に項目名（分類名）を入力して 下方向にデータを入力します。本当はデータ数が同じなら簡単なんですが そういうことはあまりないでしょから １つずつリストの名前を定義していきます。
A1:A4セルを選択
◆Excel2007の場合：
[数式]→[定義された名前]の[選択範囲から作成]
◆Excel2003以前の場合：
[挿入]→[名前]→[作成]
[上端行]にのみチェックを入れて[OK]
これを B1:B7セル・C1:C6セル・D1:D5セルで繰り返す
はいこれでリストの定義付けが終わりました。確かに作るのは簡単です。
★その２：選択リストの作成
では入力表を作成します。といってもやることはこれだけですが……
入力表シートの A2:A10セルを選択した状態で
[データ]→[入力規則]
[入力値の種類]を[リスト]
[元の値]を「肉,野菜,果物,飲み物」として[OK]
入力表シートの B2:B10セルを選択した状態で
[データ]→[入力規則]
[入力値の種類]を[リスト]
[元の値]を「=INDIRECT($A2)」として[OK]
エラー表示などが出ても気にせず[OK]
これで準備は完了です。実際に入力してみましょう。
A2セルでリストから「肉」を選択すると B2のリストが「牛肉」「豚肉」「鶏肉」だけになっているはずです。
もしリストの範囲を可変にしたい場合は２つ方法があります。
ひとつはリストの参照に OFFSET関数を使う方法です。リストの範囲をひとつずつ名前定義する必要があります。
Ａ〜Ｄ列に１列ずつテーブル設定を入れる
[数式]→[名前の管理]で
名前「肉」／参照範囲「=リスト!$A:$A」
名前「野菜」／参照範囲「=リスト!$B:$B」
名前「果物」／参照範囲「=リスト!$C:$C」
名前「飲み物」／参照範囲「=リスト!$D:$D」
をそれぞれ新規作成。
入力表シートの B2:B10セルを選択した状態で
[データ]→[入力規則]
[入力値の種類]を[リスト]
[元の値]を「=OFFSET(INDIRECT($A2),1,,COUNTA(INDIRECT($A2))-1)」として[OK]
この方法だと範囲が後で変更になっても問題ありません。
★その３：ちょっと ひと工夫
これでも別にいいんですが このままだと入力表シートで「分類を選択しない場合」リストが表示されなくなります。分類を選択しない状態では 全ての品目を表示させた方が 使いやすいかもしれません。データの数が変動するのなら すっぱりリスト表を見直した方がいいかと思います。例えばリストシートを画像のように配置します。
ついでなので D列に重複しない分類リスト・E列にその個数を表示させます。ピボットテーブルで簡単に作れます。UNIQUE関数と COUNTIF関数でやってもいいですし もちろんクエリで抽出してもいいです。
[挿入]→[名前]→[定義]
名 前：分類リスト
参照範囲：=OFFSET(リスト!$D$2,,,COUNTA(リスト!$D:$D)-1)
→[OK]
次に 品名リストを下記のように作り変えます。
[挿入]→[名前]→[定義]
名 前：品名リスト
参照範囲：=IF(入力表!$A2="",OFFSET(リスト!$B$1,1,,COUNTA(リスト!$A:$A)-1),OFFSET(リスト!$B$1,MATCH(入力表!$A2,リスト!$A:$A,0)-1,,COUNTIF(リスト!$A:$A,入力表!$A2)))
→[OK]
以上で準備完了。入力シートの リストの元の値を変更します。
入力表シートの A2:A10セルを選択した状態で
[データ]→[入力規則]
[入力値の種類]を[リスト]
[元の値]を「=分類リスト」として[OK]
入力表シートの B2:B10セルを選択した状態で
[データ]→[入力規則]
[入力値の種類]を[リスト]
[元の値]を「=品名リスト」として[OK] (22:14 修正)
これでＯＫ。分類がない時は リストが全て表示されるようになります
ただし注意が必要なのは この方法を使う場合は リストシートは 常に分類列（Ａ列）を基準に昇順か降順でソートしておく必要があります。これをサボろうと思ったら
ドロップダウンリストを絞り込む１のように 別に抽出リストを作ることになります。
→参考ファイル(047.xlsx)
