【Excel】プルダウンのリストを別ブックから読み込めないですか？

集計用のブックの中に商品マスタのシートが入っていて、それを参照して集計しているのですが、
複数の部署で使用するブック全てにそのシートが入っています。
商品マスタに変更がある度に、全ブックのシートのメンテナンスが必要なので、
商品マスタを別ブックにして、そこを参照するようにしたいのですが、問題点があります。

商品マスタは以下の様になっています。(実際の内容は書けないので例として変えてます)
野菜、大根、100円
野菜、人参、100円
野菜、ピーマン、100円
野菜、キャベツ、100円
果物、みかん、100円
果物、すいか、100円
果物、りんご、100円
肉、豚肉、100円
肉、牛肉、100円
肉、鶏肉、100円

野菜、果物、肉の範囲それぞれに名前の定義で「野菜、果物、肉」と名前を付け、
Indirect関数を使って、
第1プルダウンで「果物」を選択すると、第2プルダウンでは「みかん、すいか、りんご」のみがプルダウンの選択肢になるようにしてます。

商品マスタが外部ブックになったとしても、
第1プルダウンの値によって、第2プルダウンが絞り込めるようにするにはどうしたら良いでしょうか？

また、現時点でも負荷がかかっている点として、
果物に1商品追加された場合(1行挿入)、
名前の定義の範囲が自動的に広がってくれません。
例えば、りんごの下に、イチゴを挿入しても、名前「果物」の範囲が「みかん、すいか、りんご」のままです。
VBAが必要なら改めて質問を作成して投稿しなおします。

詳しい方、よろしくお願いいたします。

質問者からの補足コメント

• 

  具体的に見せて頂きありがとうございます。
  ゆっくり読みながら確認していきたいと思います。
  結果を含めてお礼させて頂きますので、取り急ぎ。

  No.3の回答に寄せられた補足コメントです。 補足日時：2021/09/29 19:00

No.6 回答者： d-q-t-p 回答日時： 2021/09/30 14:25

> 元ブックの行、列追加削除で色々影響あると心配してました。

マスタにテーブル書式を設定しておいてクエリは「シート」ではなく
「テーブル」から作成すれば行挿入／削除は影響しません。

No.5 回答者： chonami 回答日時： 2021/09/29 13:04

私も№4さんのクエリで読み込みをお勧めしますね。

No.4 回答者： d-q-t-p 回答日時： 2021/09/29 12:51

入力規則のリストに外部データを直接参照させることは無理です。

今は全てのブックに「商品マスタ」シートがある状態なんですよね？
そのデータを 基になる商品マスタファイルから クエリで読み込んで
くる形に変更すれば他は特に何も変更しなくていいと思います。

この回答へのお礼

クエリで参照するんですね。なるほど。
全セルを参照する形だと、
元ブックの行、列追加削除で色々影響あると心配してました。
ありがとうございます。

お礼日時：2021/09/29 18:59

No.3 回答者： fujillin 回答日時： 2021/09/28 21:54

No1です。

テスト（できることの確認）を兼ねて、ごく簡単なものを作成してみました。

添付図の、A、B、C列はNo1のリンクの方法で、別ブックから連動して元データを取り込んでいます。
（値段がご提示と違ったり、追加の項目があるのは、元ブックを変更しても連動できていることの確認のため変えたものなので、お気になさらずに…）

これによって、配布された各ブックに、常に元データを反映したものが取り込まれることになります。
作業用に、E、F列にプルダウン用のリストが自動作成されるようにしてあります。
リスト1がプルダウン1、リスト2がプルダウン2に当たります。
ついでに、H1をプルダウン1、G1をプルダウン2にしてありますが、実際はプルダウンは別のところにあるでしょうし、E、F列も見えないようにしたければ、非表示にするとか別の列にしておくなどが考えられます。

以下、office365環境での関数を記述してありますのでご注意。
E3セルに
　=UNIQUE(FILTER(A:A,A:A<>"",1))
F3セルに
　=FILTER(B:B,A:A=H1,1)
　（↑H1は実際にはプルダウン1のセルアドレスを指定）
を入力すると、スピル機能で下方に該当する項目が表示されます。

更に、E2セルに
　=SUMPRODUCT((E3:E99<>"")*1)
を入力し、F2セルにコピペしてあります。

プルダウンの設定は、上記のリストを利用して、
プルダウン1は「元の値」に
　=OFFSET($E$3,,,$E$2)
を設定してあります。（プルダウン2も同様に設定します）


以上で、プルダウン1の選択内容に応じて、プルダウン2の選択内容が変わるようになります。
また、元データの分類項目が増減しても、自動的にプルダウン1もプルダウン2も連動するようになります。
元データのデータ順は順不問です。（必ずしもソートして整理されている必要はありません）
元データ内で、「分類-項目」の組み合わせが重複することはないものと仮定しています。
もしも、office365の関数が利用できない場合は、関数式が長くなりますが同様の処理を行うことで、同様の結果を得ることが可能です。

No.2 回答者： 銀鱗 回答日時： 2021/09/28 18:10

各ブックでマスターのブックを参照するシートを作っておいて、

その参照したシートをさらに参照させればよい話。

別のブックの範囲を指定することはできますけど、
別ブックの範囲を入力規則のリスト範囲として参照することはできないのです。

マスターのブックを参照しているシートが邪魔なら、作成後にそのシートを非表示にしておけば良いでしょう。
非表示でも演算に支障はありません。

No.1 回答者： fujillin 回答日時： 2021/09/28 18:08

こんばんは

＞商品マスタを別ブックにして、そこを参照するようにしたいのですが、
＞問題点があります。
利用する際に、自ブックに取り込んだ形にできれば、問題は解決するのではないでしょうか？
マスターの元ブックは、利用者全員がアクセス可能なサーバに置いておくなどが可能であれば、各シートにはそれの参照を作成しておきます。
例えば、ブックを開いた際に、接続を自動更新するような設定にしておけば、問題は解決しませんか？
（データ量によって、読み込みに若干の時間がかかるかもしれませんけれど…）
https://support.microsoft.com/ja-jp/office/excel …

＞名前の定義で「野菜、果物、肉」と名前を付け、
＞Indirect関数を使って、～～
A列～C列を当該データ専用にできるのなら、名前の定義を利用しなくても、プルダウンの仕組みを作ることは可能です。
（検索すれば、いろいろ方法は見つかると思います。）
第二プルダウンで分類が済んでいることから、B列の選択肢は10種類程度以下で済んでいるのであろうと推測します。
第一プルダウンで選択された値を元に、作業列に第二選択肢を抽出し、それをリストに反映させるような仕組みにしておけば、元データの第一項目の順に依存しないようにもなりますし、揮発性関数であるINDIRECTを用いずとも済むと思います。


＞VBAが必要なら改めて質問を作成して投稿しなおします。
VBAを利用してももちろん解決は可能だと思いますが、上記の方法は、エクセルの機能と関数等によって実現できる範囲で回答しています。

この回答へのお礼

ありがとうございます。

＞揮発性関数であるINDIRECTを用いず

現在調べてるところですが、どれもIndirectを使った方法ばかりです。
違うのといったら、Offset関数とMatch関数の組み合わせの方法ですが、
これで良いのか分からず、まだできそうにありません。
私の場合縦方向2列からプルダウンを絞りたいのですが、
この方法はマトリクス表形式だからできる方法？と疑問に思っております。

お礼日時：2021/09/28 18:31