セルの入力規制で、ドロップダウンリストを別ブックにあるデータから参照す

質問

セルの入力規制で、ドロップダウンリストを別ブックにあるデータから参照する。
セルの入力規制で、別ブックにあるデータからA1の結果によりA2にドロップダウンリストを表示させたいのですが。

例えば、下記のデータがブック１にあるとします

　　A　　 B
1　?社　商品１
2　Ｂ社　商品３
3　Ｂ社　商品１
4　Ｃ社　商品２
5 Ａ社　商品２

ブック２のセルA1で、ドロップダウンリストから　Ａ社　Ｂ社　Ｃ社　のいずれかを選択すると
ブック２のセルA2では、Ａ社　Ｂ社　Ｃ社の行に対応した（Ａ社だったら商品１　商品２、Ｂ社だったら商品１　商品３、Ｃ社だったら商品２）ドロップダウンリストを表示させたいのですが、どうすれば良いのでしょうか？

A1のドロップダウンリストはINDIRECTでできたのですが,A2の元の値に入れる関数がわかりません。

回答No.2

chonami · ベストアンサー

ご希望の表になるかどうか分かりませんが、

今回もＤ列から表を作っていきます。
項目名をＤ1から右に
Ａ社、Ａ社担当、Ｂ社、Ｂ社担当、Ｃ社、Ｃ社担当　とI1まで入力します。

次に、Ｄ2に
=IF(COUNTIF($A$1:$A$30,$D$1)<ROW(A1),"",INDEX($A$1:$C$30,SMALL(IF($A$1:$A$30=$D$1,ROW($1:$30)),ROW(A1)),COLUMN(B1)))

Ｆ2に
=IF(COUNTIF($A$1:$A$30,$F$1)<ROW(C1),"",INDEX($A$1:$C$30,SMALL(IF($A$1:$A$30=$F$1,ROW($1:$30)),ROW(C1)),COLUMN(B1)))

Ｈ2に
=IF(COUNTIF($A$1:$A$30,$H$1)<ROW(E1),"",INDEX($A$1:$C$30,SMALL(IF($A$1:$A$30=$H$1,ROW($1:$30)),ROW(E1)),COLUMN(B1)))

と入力し、Ctrl+Shift+Enter。
それぞれ1つ右のセルと必要分下にコピーします。

次に、Ａ社ブロック（Ｄ1からＥ1と適当に下何行か）を選択し、挿入→名前→定義→名前に「Ａ社」と入れて追加。B社、C社も同様に名前を付けます。

次にブック2の方ですが、

A1セルには入力規則でＡ社、Ｂ社、Ｃ社と選択できるようにし、Ａ2セルでＡ1セルを参照して商品名を選択するようにする、と言うところまでは今までと同じです。
Ａ3セルに、

=VLOOKUP(A2,INDIRECT("[1.xls]Sheet1!"&A1),2,0)

と入力すると、A1、A2を選択するとA3に担当者の名前が出てくるかと思います。

お礼

今回も完璧に出来ました。
本当にありがとうございます。

お礼ついでにもう少し助けて頂けますでしょうか。
何度も何度もお世話になりっぱなしで本当に恐縮しております。

Book1

　　　A 　　　B 　　　 　C
１　A社　　商品２　　田中
２　B社　　商品１　　山田
３　A社　　商品１　　鈴木
４　C社　　商品３　　山田
５　B社　　商品４　　加藤

上記データを元に下記のような参照用の一覧表を作りたいのですが

　　　　A 　　　B 　　　　C
１　A社　　商品２　　商品１
２　　　　　　田中　　　鈴木
３　B社　　商品１　　商品４
４　　　　　　山田　　　加藤
５　C社　　商品３
　　　　　　　山田

Book2のC３セルへの抽出はVLOOKUPをHLOOKUPに変えることで出来そうです。

次にBook1のA1:A5セルのデータを下記のように無重複に抽出（別シートに）する方法はフィルタオプションを使うしかないのでしょうか？

　　 A
１　A社
２　B社
３　C社

さらに、無重複に取り出したデータを一行おきに並べる方法はありますか？
（参照用の一覧表のA1:A5セルのように）

本当に何度もお世話になりっぱなしで申し訳ありません。

何かお礼出来る方法があれば良いのですが。

回答No.1

chonami

えと、前回のとはレイアウトが違うという事でしょうか。

こういった場合は、別で参照用の一覧表を作る必要があります。
手入力で作ると・・・やっぱり面倒ですよねぇ。

シート2のＡ1セル用にも使用できるように横に伸びていく形で一覧表を作ってみます。
（但し、配列数式を使いますので少し重くなります。）

ブック1の空いている列に社名のリストを作ります。
今回はＤ列とします。
Ｄ1からＤ3にＡ社、Ｂ社、Ｃ社と入力します。
次にＥ1に
=IF(COUNTIF($A$1:$A$30,$D1)<COLUMN(A1),"",INDEX($A$1:$B$30,SMALL(IF($A$1:$A$30=$D1,ROW($1:$30)),COLUMN(A1)),2))
と入力し、Shift＋Ctrl＋Enterを押します。（数式が{}で囲まれます。）

これを、必要な分左と下にコピーします。

上記数式は元データが30行分のものですので、適宜変更して下さい。
COLUMN関数の引数は、触らないで下さい。

出てくる商品名は出現順になりますのでサンプルのデータでは数字が昇順になりませんが、まぁ実際は番号が入ってるわけじゃないですよね。

次に、D1から、適当な範囲（今後データが増えそうなくらいの範囲）を選んで選択します。
挿入→名前→作成→「左端列」にだけチェックを入れてOK
これで、１行ずつ名前が付きます。

ここまでくれば、前回の質問と同様の手順でリストを作成できるはずです。

補足

いつもいつも大変ありがとうございます。完璧にできました。
ちなみに参照用の一覧表は縦に伸びていく形でもできるのでしょうか？

また、さらに下記のような抽出もできますか。

Book1
　　A　　 B C
1 A社　商品１　 田中
2　Ｂ社　商品３　青木　
3　Ｂ社　商品１　田中
4　Ｃ社　商品２　鈴木
5 Ａ社　商品２　木村

上記データを元に下記BookBに抽出する

　　A　　　B　　　C
1 A社　商品２　木村

上記A1,B1にそれぞれの入力規制のドロップダウンリストから、A1="A社"、B1="商品２"を選択した時に
（ここまでは出来ています。）
C1に"木村"を抽出するにはどうしたらよいのでしょうか？
（C1の抽出はドロップダウンリストではなくダイレクトです。）

A1にB社を選んだ時には、B1では　"商品３"、"商品１"、が選択できるので
C1にはそれぞれ　"青木"、"田中"、を抽出したいのですが。

何度もお手数をお掛けしますがよろしくお願いします。