セルの入力規制で、ドロップダウンリストを別ブックにあるデータから参照する。
セルの入力規制で、別ブックにあるデータからA1の結果によりA2にドロップダウンリストを表示させたいのですが。
例えば、下記のデータがブック1にあるとします
A B
1 ?社 商品1
2 B社 商品3
3 B社 商品1
4 C社 商品2
5 A社 商品2
ブック2のセルA1で、ドロップダウンリストから A社 B社 C社 のいずれかを選択すると
ブック2のセルA2では、A社 B社 C社の行に対応した(A社だったら商品1 商品2、B社だったら商品1 商品3、C社だったら商品2)ドロップダウンリストを表示させたいのですが、どうすれば良いのでしょうか?
A1のドロップダウンリストはINDIRECTでできたのですが,A2の元の値に入れる関数がわかりません。
No.2ベストアンサー
- 回答日時:
ご希望の表になるかどうか分かりませんが、
今回もD列から表を作っていきます。
項目名をD1から右に
A社、A社担当、B社、B社担当、C社、C社担当 とI1まで入力します。
次に、D2に
=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)))
F2に
=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)))
H2に
=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つ右のセルと必要分下にコピーします。
次に、A社ブロック(D1からE1と適当に下何行か)を選択し、挿入→名前→定義→名前に「A社」と入れて追加。B社、C社も同様に名前を付けます。
次にブック2の方ですが、
A1セルには入力規則でA社、B社、C社と選択できるようにし、A2セルでA1セルを参照して商品名を選択するようにする、と言うところまでは今までと同じです。
A3セルに、
=VLOOKUP(A2,INDIRECT("[1.xls]Sheet1!"&A1),2,0)
と入力すると、A1、A2を選択するとA3に担当者の名前が出てくるかと思います。
今回も完璧に出来ました。
本当にありがとうございます。
お礼ついでにもう少し助けて頂けますでしょうか。
何度も何度もお世話になりっぱなしで本当に恐縮しております。
Book1
A B C
1 A社 商品2 田中
2 B社 商品1 山田
3 A社 商品1 鈴木
4 C社 商品3 山田
5 B社 商品4 加藤
上記データを元に下記のような参照用の一覧表を作りたいのですが
A B C
1 A社 商品2 商品1
2 田中 鈴木
3 B社 商品1 商品4
4 山田 加藤
5 C社 商品3
山田
Book2のC3セルへの抽出はVLOOKUPをHLOOKUPに変えることで出来そうです。
次にBook1のA1:A5セルのデータを下記のように無重複に抽出(別シートに)する方法はフィルタオプションを使うしかないのでしょうか?
A
1 A社
2 B社
3 C社
さらに、無重複に取り出したデータを一行おきに並べる方法はありますか?
(参照用の一覧表のA1:A5セルのように)
本当に何度もお世話になりっぱなしで申し訳ありません。
何かお礼出来る方法があれば良いのですが。
No.1
- 回答日時:
えと、前回のとはレイアウトが違うという事でしょうか。
こういった場合は、別で参照用の一覧表を作る必要があります。
手入力で作ると・・・やっぱり面倒ですよねぇ。
シート2のA1セル用にも使用できるように横に伸びていく形で一覧表を作ってみます。
(但し、配列数式を使いますので少し重くなります。)
ブック1の空いている列に社名のリストを作ります。
今回はD列とします。
D1からD3にA社、B社、C社と入力します。
次にE1に
=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
これで、1行ずつ名前が付きます。
ここまでくれば、前回の質問と同様の手順でリストを作成できるはずです。
この回答への補足
いつもいつも大変ありがとうございます。完璧にできました。
ちなみに参照用の一覧表は縦に伸びていく形でもできるのでしょうか?
また、さらに下記のような抽出もできますか。
Book1
A B C
1 A社 商品1 田中
2 B社 商品3 青木
3 B社 商品1 田中
4 C社 商品2 鈴木
5 A社 商品2 木村
上記データを元に下記BookBに抽出する
A B C
1 A社 商品2 木村
上記A1,B1にそれぞれの入力規制のドロップダウンリストから、A1="A社"、B1="商品2"を選択した時に
(ここまでは出来ています。)
C1に"木村"を抽出するにはどうしたらよいのでしょうか?
(C1の抽出はドロップダウンリストではなくダイレクトです。)
A1にB社を選んだ時には、B1では "商品3"、"商品1"、が選択できるので
C1にはそれぞれ "青木"、"田中"、を抽出したいのですが。
何度もお手数をお掛けしますがよろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) VBAで同フォルダ内の別ブックを開かず参照して条件の一致する行の指定セルを抽出するには? 1 2022/07/21 19:29
- Excel(エクセル) excelで検索した商品の画像(ネットワーク上の)を表示させたい。 3 2023/06/28 00:32
- Excel(エクセル) エクセルの書式設定の表示形式で設定した文字を文字列としてコピーしたい 1 2022/12/21 10:41
- Excel(エクセル) エクセルで値ではなく関数を参照する方法 6 2023/03/19 00:50
- Visual Basic(VBA) VBA ドロップダウンリストを残して値のみクリア 2 2022/10/27 05:42
- Visual Basic(VBA) access count数を変数に格納 2 2022/03/30 19:21
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- Visual Basic(VBA) VBAコードで質問があります 2 2022/10/20 15:27
- その他(IT・Webサービス) 高速処理可能な表計算ソフトについて ExcelやGoogleスプレッドシートのような表計算ソフトで、 2 2023/04/29 16:06
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
このインバウンドで思う所は・...
-
やりがいを感じてもいいのか
-
タイミーで未経験者歓迎とも、...
-
75
-
10年後の日本、現在とどのあた...
-
ハーバードビジネススクール卒...
-
役職が複数ある場合の紹介の仕...
-
職を転々とするか、フリーターか
-
転売とせどりの違いを教えて下...
-
気付きました。
-
仕事で使う体力はどうすれば、...
-
自分は今内定の会社入ったら社...
-
高校生です。高級車乗って豪邸...
-
仕分けの仕方がわかる方、教え...
-
30歳で月給32万(残業0の場合の...
-
平均年収700(平均年齢34)って書...
-
40代半ばの底辺会社員から小金...
-
湘南地域で1番大きい会社、年収...
-
2025年から定年が65歳に義務付...
-
エンジニアは所詮ドカタだと言...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
今、解雇を迫られています、、
-
電動ヘリコプターをなぜ執拗に...
-
会社への診断書の提出について
-
役職が複数ある場合の紹介の仕...
-
自分は今内定の会社入ったら社...
-
日本に来る外国人から入国税と...
-
仕事
-
歴史は繰り返す? こんなニュー...
-
40代半ば男です。 腰を据えて同...
-
職場で自分と同じ苗字の女性に...
-
今、色々な物の「出張買取」と...
-
仕事
-
株式の暴落する日は、強風の日...
-
企業で電話対応されている方に質問
-
仕事で使う体力はどうすれば、...
-
会社の弔事対応業務の流れについて
-
買わなきゃ損だの、今だけ!だ...
-
50歳で新しい部署に
-
地元の零細物流会社で働き始め...
-
日本はG7を辞退すべきでは? 20...
おすすめ情報