集計用のブックの中に商品マスタのシートが入っていて、それを参照して集計しているのですが、
複数の部署で使用するブック全てにそのシートが入っています。
商品マスタに変更がある度に、全ブックのシートのメンテナンスが必要なので、
商品マスタを別ブックにして、そこを参照するようにしたいのですが、問題点があります。
商品マスタは以下の様になっています。(実際の内容は書けないので例として変えてます)
野菜、大根、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で質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) IFERROR、SMALL関数について 2 2022/08/22 23:40
- Excel(エクセル) Excel VBAプルダウンの値を変えながら2枚ずつ印刷する方法? 4 2022/05/27 13:04
- 経済 100円ショップは悪か? 庶民の味方か? 賃金をほぼ上げない企業が悪か? 8 2022/04/02 13:13
- Excel(エクセル) Excel関数 情報引用する方法 4 2022/07/31 20:59
- Visual Basic(VBA) vbaマクロについて 【1.csv】をもとに【商品.csv】に有るものを【有り.csv】として名前を 1 2023/05/18 07:58
- Visual Basic(VBA) vbaマクロについて 次のようなマクロを組みたいです。 自分は初心者なので全くわかりません。 詳しく 8 2023/05/18 18:38
- その他(ニュース・時事問題) 都市部の無人販売店での万引き事件はビジネス方式に問題があるのでは。 8 2022/06/11 20:00
- Excel(エクセル) Excelのプルダウンメニューの内容を人によって可変する方法 2 2023/03/28 14:52
- Excel(エクセル) Excel 関数 vlookupなどの使い方について質問です。 シート1に品番、商品名、単価、発注条 6 2022/06/15 19:16
- Amazon amazonで、自分が希望する値段ぴったりの商品を見つける方法ってありますか? 3 2022/07/31 02:28
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
EXCELで他のファイルデータをドロップダウンさせるには?
その他(コンピューター・テクノロジー)
-
別ブックから入力規則でリスト作成(他ブックの参照を使用できない)
Excel(エクセル)
-
Excelについて
Excel(エクセル)
-
-
4
【エクセル】プルダウン設定のシートを複製コピーする方法
Excel(エクセル)
-
5
EXCEL:入力規則の「リスト」は別シートを参照できない
Excel(エクセル)
-
6
セルの入力規制で、別ブックにあるデータから参照する。
その他(ビジネス・キャリア)
-
7
別ファイルを開かず、INDIRECT関数を使用せずに、別ファイルのデータを求めたい
Excel(エクセル)
-
8
エクセルの名前の定義を他のファイルにエクスポートする方法?
Excel(エクセル)
-
9
リストボックスの項目に、他のブックのセルの値を設定するには?
Access(アクセス)
-
10
VBAでブックを非表示で開いて処理して閉じる方法
Excel(エクセル)
-
11
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
12
Excelで質問です。ListBoxの項目を別bookから読込むには
その他(Microsoft Office)
-
13
Excel VBA、 別ブックの最終行セルへのコピー&値ペースト
Visual Basic(VBA)
-
14
エクセル、 名前の定義に関数を使用すると参照できない
Excel(エクセル)
-
15
Excelの行をコピーして貼り付けても、 ドロップダウンが反映されません。 なぜでしょうか? シート
Excel(エクセル)
-
16
【エクセル】ドロップダウンリスト(入力規則)の参照元を別シートに作るには?
Excel(エクセル)
-
17
シートを保護しても入力規則を使えるようにしたい
Excel(エクセル)
-
18
Application.ScreenUpdating = Falseが効きません
Visual Basic(VBA)
-
19
シート全体を他のブックのシートとリンクさせたい
Excel(エクセル)
-
20
リンク先のファイルを開かなくても、値が読み込めるようにできますか?(SUMIFSを使ってます)
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel関数-文字列で自動作成さ...
-
エクセルの関数について教えて...
-
Excelデータをコピペして、ペー...
-
職場の人から聞かれており、こ...
-
ユーザー定義関数をアドイン登...
-
Excelで50個のセルに同じ文字を...
-
スプレッドシート、Excelでの数...
-
Microsoft Officeの中古は信用...
-
エクセルで不等号記号(≠)が上に...
-
スプレッドシートで使う数式を...
-
エクセルでの特別な文字を上に...
-
エクセル日付 文字列の関数がエ...
-
A列とB列を参照してC列に連番を...
-
エクセルVBA、別ブックへ転記す...
-
各ページの1番上の表示について
-
エクセルでセルに標準で入力さ...
-
EXCELの質問です 119から足した...
-
pdfの表をexcelにはりつけて計...
-
Excelのif関数で文字が見えなく...
-
【マクロ】アクティブセルにブ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報
具体的に見せて頂きありがとうございます。
ゆっくり読みながら確認していきたいと思います。
結果を含めてお礼させて頂きますので、取り急ぎ。