2023年06月26日

INDIRECT関数を使ってドロップダウンリストで絞り込む / データの入力規制 / Excel

みなさんこんにちわー!

INDIRECT関数と組み合わせることでドロップダウンリストで絞り込みを行うことが出来ます。

では、早速実演といきましょう!

まず、下の様なテーブルがあります。
食品の名前が並んでいますが、

“分類”に応じて、“飲み物”  “パン” のテーブルが出来ています。
まずこれらを、データの入力規制のリストとして扱うので、リスト(テーブル)の名前を設定します。

まず、左端の “分類” のテーブルを選択して、左上の選択範囲が表示された窓に「分類」と入力して「Enter」します。

WS000007-1.JPG


次に真ん中の “飲み物” のテーブルは「飲み物」

WS000008-1.JPG

右端の “パン” のテーブルは「パン」と命名します。

WS000009-1.JPG


で、別の絞り込みを実施するページに下の様な表を作って、

WS000010-1.JPG


分類の列を選択したら、「データ」で「データの入力規制」を選択します。

WS000011-1.JPG


で、“入力値の種類” を「リスト」、“元の値” に「=分類」と入力して「OK」します。

WS000013-1.JPG


すると、、、
分類の列の空白セルを選択すると右側に矢印が現れてクリックすると分類名(“飲み物”、 “パン”)が選択できるようになります。
いわゆる、これが、ドロップダウンリストっちゅーやつです。

WS000015.JPG

で、こっからが、キモです。
今度は同じようにデータの入力規制でドロップダウンリストを作成し品名を選択するようにするのですが、
その時に、リストの元の値(“=テーブルの範囲名”)を左側のセルの分類名を使用してしまおうっていうわけです。
つまり、分類名に応じてドロップダウンリストに表示するテーブルの範囲名を変えてしまうということです。


では、品名の入力範囲を選択して、

WS000016-1.JPG

「データ」で「データの入力規制」を選択します。
で、“入力値の種類” を「リスト」、“元の値” に=INDIRECT(B5)と入力します。
この関数は数式や関数の中の文字列を参照するセルのテキストデータを代入させることが出来ます。

つまり、“INDIRECT(B5)” は隣のB列にドロップダウンリストから表示された分類名が代入され
元の値が “=飲み物” や “=パン” になったりします。

WS000018-1.JPG

実際にやってみると
分類に “飲み物” を選択すると

WS000020-1.JPG

品名のドロップダウンリストは “飲み物” のテーブルが表示されます

WS000021-1.JPG

次に分類に “パン” を選択すると

WS000022-1.JPG

品名のドロップダウンリストは “パン” のテーブルが表示されます。

と、いうことで、データの入力規制のドロップダウンリストで絞り込みができました。

今回は、分類別のテーブルを手入力で作成していきましたが、
分類と品名が混在する一覧表(マスター)からピボットテーブルを使用して分類名毎のテーブルを作成したのが
こちらになります。








posted by haku1569 at 18:57| Excelデータ | このブログの読者になる | 更新情報をチェックする

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


以上!



20150523_00.jpg


関連ページ

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