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データ | このブログの読者になる | 更新情報をチェックする

2014年06月08日

ドロップダウンリスト(コンボボックス/選択ボックス)を作ってみた(データ)/Excel/データ

Excelでドロップダウンリスト(データ選択ボックス)からあらかじめ設定してあるリストからデータを選択入力してみます。
選択ボックスには“ドロップボックス”と“コンボボックス”がありますが、Excelではこの機能は特にどちらの名前も付いていません。「データの入力規則」となっているだけです。
このページでは“ドロップダウンリスト”と呼ぶことにします。。
01_ドロップダウンリスト_Excel.jpg

例えば受注データがあって、個別の商品名に対して分類をするために“区分”というフィールドにドロップダウンリストを適用します。
20140608-101.JPG

別のシートにドロップダウンリストに使用する商品分類のリストを作ります。
リストの範囲が後からも分かりやすいように罫線を描いておきます(描かなくても構いません)。
それから範囲に付けた名前が後から分かりやすいように“区分”を入力しておきます。(これも無くても構いません)。
範囲の名前は、元データのフィールドの名前を同じにしておいた方が後から分かりやすいと思います。(これまた、全く関係無い名前でも構いません)
20140608-102.JPG

罫線で囲んだ範囲をカーソルで選択します。
ここで、リストの最後に空白行があるのが肝になります。
空白行を含むと、リスト以外のテキストも入力可能になります。空白行が無いとリスト以外のテキストは入力できなくなります。入力をリストだけに完全に制限したい場合は空白を含まずに範囲指定してください。
MicroSoftによると、リスト以外にテキスト入力が出来る場合を“コンボボックス”、リストからの選択しかできない場合を“ドロップダウンリスト”と明確に区別しています。
20140608-103.JPG

範囲指定したら、メニュー下の「名前ボックス」に範囲名(ここでは)“区分”を入力して「Enter」キーを押します。すると、“区分”が名前ボックスの中央に表示されます。範囲名は何でも構いません。
それから、必ずEnterキーで確定させてください!
リストに付けた名前を後から確認するには、指定した範囲を同じ範囲を選択した時に名前ボックスに指定した範囲名が表示されます。範囲が少しでもずれていると表示されません。なので、わざわざ最初に罫線で囲ったり、名前を入力したりしているわけです。。。
20140608-104.JPG

元データのシートに戻って、区分のフィールド(列)全体を範囲選択します。
20140608-105.JPG

「データ」から「データの入力規制」を選択します。
20140608-106.JPG

「データの入力規制」の画面が開きます。
入力の種類に“リスト”を選択します。
元の値に“=区分”と入力します。
(ドロップダウンリストの範囲に付けた名前を入力します)
「OK」します。
20140608-107.JPG

すると、区分のフィールドの空白セルにカーソルを置くと▼マークが表示され、クリックすると選択リストが表示されます。
20140608-108.JPG

リスト範囲に空白セルを含んでおくとリスト以外に自由にテキスト入力できますが、リスト範囲に空白セルを含まないと、選択肢以外を入力しようとすると×メッセージが現れます。
入力を厳しく制限する場合に適してるでしょう。
20140608-109.JPG

ドロップダウンリストの解除の方法も説明しておきましょう。
ドロップダウンリストを設定したフィールド“区分”を選択し、「データ」から「データの入力規制」を選択します。
「データの入力規制」の画面が開きます。ここで、左下の「全てクリア」のボタンをクリックします。
20140608-110.JPG

以上!

さて、次は2つのドロップダウンリストを連動させて、1つ目のドロップダウンリストで選択したアイテムによって絞り込まれたアイテムを2つ目のドロップダウンリストに表示させてみましょう!
20150523_00.jpg


関連ページ
posted by haku1569 at 19:29| Excelデータ | このブログの読者になる | 更新情報をチェックする