集計用のブックの中に商品マスタのシートが入っていて、それを参照して集計しているのですが、
複数の部署で使用するブック全てにそのシートが入っています。
商品マスタに変更がある度に、全ブックのシートのメンテナンスが必要なので、
商品マスタを別ブックにして、そこを参照するようにしたいのですが、問題点があります。
商品マスタは以下の様になっています。(実際の内容は書けないので例として変えてます)
野菜、大根、100円
野菜、人参、100円
野菜、ピーマン、100円
野菜、キャベツ、100円
果物、みかん、100円
果物、すいか、100円
果物、りんご、100円
肉、豚肉、100円
肉、牛肉、100円
肉、鶏肉、100円
野菜、果物、肉の範囲それぞれに名前の定義で「野菜、果物、肉」と名前を付け、
Indirect関数を使って、
第1プルダウンで「果物」を選択すると、第2プルダウンでは「みかん、すいか、りんご」のみがプルダウンの選択肢になるようにしてます。
商品マスタが外部ブックになったとしても、
第1プルダウンの値によって、第2プルダウンが絞り込めるようにするにはどうしたら良いでしょうか?
また、現時点でも負荷がかかっている点として、
果物に1商品追加された場合(1行挿入)、
名前の定義の範囲が自動的に広がってくれません。
例えば、りんごの下に、イチゴを挿入しても、名前「果物」の範囲が「みかん、すいか、りんご」のままです。
VBAが必要なら改めて質問を作成して投稿しなおします。
詳しい方、よろしくお願いいたします。
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.6
- 回答日時:
> 元ブックの行、列追加削除で色々影響あると心配してました。
マスタにテーブル書式を設定しておいてクエリは「シート」ではなく
「テーブル」から作成すれば行挿入/削除は影響しません。
No.4
- 回答日時:
入力規則のリストに外部データを直接参照させることは無理です。
今は全てのブックに「商品マスタ」シートがある状態なんですよね?
そのデータを 基になる商品マスタファイルから クエリで読み込んで
くる形に変更すれば他は特に何も変更しなくていいと思います。
クエリで参照するんですね。なるほど。
全セルを参照する形だと、
元ブックの行、列追加削除で色々影響あると心配してました。
ありがとうございます。
No.3
- 回答日時:
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
- 回答日時:
各ブックでマスターのブックを参照するシートを作っておいて、
その参照したシートをさらに参照させればよい話。
別のブックの範囲を指定することはできますけど、
別ブックの範囲を入力規則のリスト範囲として参照することはできないのです。
マスターのブックを参照しているシートが邪魔なら、作成後にそのシートを非表示にしておけば良いでしょう。
非表示でも演算に支障はありません。
No.1
- 回答日時:
こんばんは
>商品マスタを別ブックにして、そこを参照するようにしたいのですが、
>問題点があります。
利用する際に、自ブックに取り込んだ形にできれば、問題は解決するのではないでしょうか?
マスターの元ブックは、利用者全員がアクセス可能なサーバに置いておくなどが可能であれば、各シートにはそれの参照を作成しておきます。
例えば、ブックを開いた際に、接続を自動更新するような設定にしておけば、問題は解決しませんか?
(データ量によって、読み込みに若干の時間がかかるかもしれませんけれど…)
https://support.microsoft.com/ja-jp/office/excel …
>名前の定義で「野菜、果物、肉」と名前を付け、
>Indirect関数を使って、~~
A列~C列を当該データ専用にできるのなら、名前の定義を利用しなくても、プルダウンの仕組みを作ることは可能です。
(検索すれば、いろいろ方法は見つかると思います。)
第二プルダウンで分類が済んでいることから、B列の選択肢は10種類程度以下で済んでいるのであろうと推測します。
第一プルダウンで選択された値を元に、作業列に第二選択肢を抽出し、それをリストに反映させるような仕組みにしておけば、元データの第一項目の順に依存しないようにもなりますし、揮発性関数であるINDIRECTを用いずとも済むと思います。
>VBAが必要なら改めて質問を作成して投稿しなおします。
VBAを利用してももちろん解決は可能だと思いますが、上記の方法は、エクセルの機能と関数等によって実現できる範囲で回答しています。
ありがとうございます。
>揮発性関数であるINDIRECTを用いず
現在調べてるところですが、どれもIndirectを使った方法ばかりです。
違うのといったら、Offset関数とMatch関数の組み合わせの方法ですが、
これで良いのか分からず、まだできそうにありません。
私の場合縦方向2列からプルダウンを絞りたいのですが、
この方法はマトリクス表形式だからできる方法?と疑問に思っております。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
歩いた自慢大会
「めちゃくちゃ歩いたエピソード」を教えてください。 長時間でも長距離でも結構です。
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
EXCELで他のファイルデータをドロップダウンさせるには?
その他(コンピューター・テクノロジー)
-
別ブックから入力規則でリスト作成(他ブックの参照を使用できない)
Excel(エクセル)
-
Excelについて
Excel(エクセル)
-
-
4
別ファイルを開かず、INDIRECT関数を使用せずに、別ファイルのデータを求めたい
Excel(エクセル)
-
5
セルの入力規制で、別ブックにあるデータから参照する。
その他(ビジネス・キャリア)
-
6
EXCEL:入力規則の「リスト」は別シートを参照できない
Excel(エクセル)
-
7
【エクセル】ドロップダウンリスト(入力規則)の参照元を別シートに作るには?
Excel(エクセル)
-
8
エクセルの名前の定義を他のファイルにエクスポートする方法?
Excel(エクセル)
-
9
VBAでブックを非表示で開いて処理して閉じる方法
Excel(エクセル)
-
10
エクセル:マクロ「Application.CutCopyMode = False」って?
Excel(エクセル)
-
11
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
12
エクセル、 名前の定義に関数を使用すると参照できない
Excel(エクセル)
-
13
【マクロ】【VBA】別ブックへのデータ転記について
Excel(エクセル)
-
14
Excelの行をコピーして貼り付けても、 ドロップダウンが反映されません。 なぜでしょうか? シート
Excel(エクセル)
-
15
EXCEL VBAで全選択範囲の解除
Excel(エクセル)
-
16
Excelでセル参照したとき、書式も一緒に持ってくるには?
Windows Vista・XP
-
17
【エクセル】プルダウン設定のシートを複製コピーする方法
Excel(エクセル)
-
18
エクセルで他のBookのデータを入力規則のリストに…
Excel(エクセル)
-
19
リストボックスの項目に、他のブックのセルの値を設定するには?
Access(アクセス)
-
20
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
タイムスタンプとテキストから...
-
エクセルの文字が途中から消える
-
エクセルの質問です。 F列からL...
-
ワークシートに出現したこの画...
-
Excelの警告について
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
Excelでの文字色
-
マクロの処理が遅くなった
-
シートの情報を別のシートへま...
-
OFFSET関数を使用したいのです...
-
エクセルの数式バーのフォント...
-
エクセルデーターから必要な項...
-
Excelについて教えてください。...
-
SUBTOTALは、参照された数字で...
-
エクセルの「条件付き書式」を...
-
Excelの関数について このよう...
-
エクセル。金額から「円」を除...
-
Excelの数字の前に入っている空...
-
エクセルの問題です。絶対値の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報
具体的に見せて頂きありがとうございます。
ゆっくり読みながら確認していきたいと思います。
結果を含めてお礼させて頂きますので、取り急ぎ。