2015年05月23日

2つのドロップダウンリストを連動させて絞り込んだデータを選択させてみた(INDIRECT関数の応用)/Excel/データ

Excelで2つのドロップダウンリスト(コンボボックス)で、1つ目のドロップダウンリストのアイテムから2つ目のドロップダウンリストの表示アイテムを絞り込んで表示させて選択できるようにしてみます。
肝は、"INDIRECT関数"を使用することです。
20150523_00.jpg

まず、商品名と区分のテーブル(一覧表)があります。
1つ目のドロップダウンリストで区分を選択した時に、2つ目のドロップダウンリストにその区分に該当する商品名だけが表示されるようにしてみます。
20150523_01.JPG

必要なデータの準備
"区分"毎にアイテムの一覧表をつくるのですが、元のテーブルのデータが更新されることを考えて、ピボットテーブルを使ってレポートフィルタに"区分"を設定したピボットテーブルを1つのシートに区分の数だけコピーして全ての区分とそのアイテムの複数の表をつくります。
ピボットテーブルを使わなくても、とにかく区分毎の商品名の一覧表ができればOKです。
20150523_02.JPG

つなみに、ピボットテーブルのフィールドリストはこうなります。
20150523_03.JPG

ドロップダウンリスト作成の準備(範囲名の設定)
で次は、区分毎に集計されたアイテムの範囲に名前(区分名)をつけていきます。
"アルコール"のアイテムの範囲を選択して"名前ボックス"に"アルコール"と入力し「Enter」キーで確定します。
ドロップダウンリストの作り方はコチラに詳しく説明してあります。。
20150523_04.JPG

同様に"パン"も、、
んで、全ての区分毎のアイテムの範囲に区分名をつけていきます。
20150523_05.JPG

次は、区分を抽出したテーブルで区分のアイテムを選択して"区分"と名前をつけます。
区分の重複しないアイテムの抽出のやり方はこちらをご覧ください。
20150523_06.JPG

これで、準備完了です!

いよいよドロップダウンリストの作成です!
では、新しいシートで、セル"B4"にカーソルを置いて、「データ」、「データツール」、「データの入力規制」を選択します。
20150523_07.JPG

「データの入力規制」の画面が開きます。
入力値の種類:"リスト"を選択します。
元の値:"=区分"と入力します。
20150523_08.JPG

すると、区分のドロップダウンリストができました。
20150523_09.JPG

次は、いよいよこの区分と連動してアイテムを抽出する商品名のドロップダウンリストを作ります。
セル"C4"にカーソルを置いて同じように「データの入力規制」の画面を開きます。
入力値の種類:"リスト"を選択します。
元の値:"=INDIRECT(B4)"と入力します。
"INDIRECT関数"を使うことで、区分に表示されたアイテム(セルB4)がリスト名として元の値に引用することができます。
20150523_10.JPG

「OK」するとこんなメッセージが表示されますが「はい」で続行します。
20150523_11.JPG

で、この入力規制を設定したセルB4とC4を必要な範囲までコピーします。
20150523_12.JPG

区分にドロップダウンリストから"パン"を選択すると商品名のセルのドロップダウンリストには"パン"の商品名だけが表示されました。
20150523_13.JPG

"飲料"を選択すると飲料の商品名だけが表示されます。
20150523_14.JPG

と、このように区分と連動し絞り込まれた商品名のドロップダウンリストが完成しました!
20150523_15.JPG

データソース(元データ)の入力に応用して、間違いのないデータ入力に活用できますね。


関連ページ
ピボットテーブルを作ってみた(更新や、用語の説目も)
レポートフィルタで特定のアイテムのデータを抽出してみた
重複の無いデータを抽出する
ドロップダウンリスト(コンボボックス/選択ボックス)を作ってみた
INDIRECT関数でセルの位置や範囲をテキストで参照させてみた
posted by haku1569 at 19:17| Excelデータ | このブログの読者になる | 更新情報をチェックする