エクセルの連動ドロップダウンリストについて教えてください

質問

エクセル2003です。
連動したドロップダウンリストを作りたいのですがデータに制約があります。データはsheet2にあり下記のような感じです。

A列　　　　　　　B列　　　　　C列
メーカーA　　商品名A　　価格A
メーカーA　　商品名B　　価格B
メーカーB　　商品名C　　価格C
メーカーB　　商品名D　　価格D

このデータをsheet1で使いたいのですが、総数で2000件以上あります。
メーカ数は40以上あります。A1セルでメーカー名を使って1回目の絞り込みをしてB１セルで
商品を選ぶとC1セルにその商品の価格が入るようにしたいのですが、
良い方法を教えてください。

できれば最初にメーカーを選ぶときにドロップダウンリストに重複して
同じメーカー名がでないようにフィルターもしたいです。

あと、データー自体は随時変更するのでデータはどんどん変わっていきます。よろしくお願いいたします。

回答No.2

tom04 · ベストアンサー

No．1です！
たびたびごめんなさい。
投稿した後で質問文を読み返してみました。

>あと、データー自体は随時変更するのでデータはどんどん変わっていきます。よろしくお願いいたします。

の部分を考慮に入れていませんでした、この場合いちいちフィルタをかけて貼り付けは大変ですので
自動でSheet3に商品名が表示する方法を考えてみました。
配列数式になってしまいますので、コンピュータの負担を考えるとあまりよろしくないと思いますが、
作業列ばかり増えても困りますので、敢えて配列数式にしています。
この画面からコピー＆ペーストしただけではエラーになると思います。
Sheet3のA2セルに貼り付け後、F2キーを押すか、数式バー内で一度クリックしてください。
編集可能になりますので、Shift＋Ctrl＋Enterキーで確定します。
数式の前後に｛　｝マークが入り配列数式になります。
Sheet3のA2セルに
=IF(SUMPRODUCT((Sheet2!$A$2:$A$2000=Sheet3!A$1)*(Sheet2!$B$2:$B$2000<>""))<ROW(A1),"",INDEX(Sheet2!$B$2:$B$2000,SMALL(IF(Sheet2!$A$2:$A$2000=Sheet3!A$1,ROW($A$1:$A$1999)),ROW(A1))))
として、Shift＋Ctrl＋Enterです。
これを列方向と行方向にコピーすると前回手作業で貼り付けしていた方法は必要なくなります。

名前定義の方法は前回同様です。

尚、前回メーカー名を表示させる場合数式をSheet3のA3セルに！と書いていましたが、
A1セルの間違いです。訂正してください。
そして、数式は2000行目まで対応できるようにしていますが、データがそれ以上だという事みたいですので、数式内の2000の部分をアレンジしてみてください。

今回も長々と書いてしまいました。
何度も失礼しました。m(__)m

お礼

ありがとうございます。早速試してみました。
うまく行きました、感動です。１週間近く悩んでいたのですっきりしました。
ただ一点だけですが、やはりデータ数が多いいため処理が遅くなって
しまいます。フリーズしたのかと思うほど重いです。とりあえず
sheet4を作ってsheet3の内容を形式指定貼付けで値だけコピーしました。
sheet3はデータが変わった時だけ再計算させるようにしておけば当面は
問題なく使えそうです。

回答No.1

tom04

こんばんは！
直接の回答にならないかもしれませんが・・・

質問を読ませてもらっていて、もしかして、メーカーによっては扱っていない商品もあるのではないか？と思いますので、

メーカーをリストで選択後、扱っていない商品を選択してもエラーになるような気がします。

そこで一つの案ですが、
↓の画像のようにSheet3にSheet2のデータをまとめてみました。
Sheet2に作業用の列を2列設けています。
（Sheet3にメーカーを重複なしに抽出するためと、メーカーが扱っている商品をメーカーの名前の下に表示させるためです。）

Sheet2の作業列D2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"")
E2セルに
=A2&B2
として、D2・E2セルを範囲指定し、E2セルのフィルハンドルでダブルクリック、又はオートフィルで下へコピー

そして、Sheet3のA3セルに
=IF(COUNT(Sheet2!$D$2:$D$2000)<COLUMN(A1),"",INDEX(Sheet2!$A$2:$A$2000,SMALL(Sheet2!$D$2:$D$2000,COLUMN(A1))))
という数式を入れ、ずぃ～～～！っと列方向にコピーします。
これでSheet2のメーカー名が重複なしに表示されます。
次にSheet2の表でメーカーごとにフィルタをかけ、表示されたものの商品名だけをコピーし、Sheet3のメーカーの下へ貼り付けていきます。
これを全てのメーカーで行います。
これでSheet3にメーカー名と扱っている商品名の一覧表が出来ますので
ここで名前定義します。
1行目（↓の画像ではA1～C1）を範囲指定　→　仮に「メーカー」と名前定義したとします。
当方使用のExcel2003の場合は
範囲指定後　→　メニュー　→　挿入　→　名前　→　定義　でOK
（範囲指定後直接名前ボックスに入力しても構いません）

そして、Sheet3のA2～A3セルを範囲指定　→　「ああ」と名前定義
B2～B3を範囲指定　→　「いい」と名前定義・・・
と最後まで名前定義していきます。
これで準備は出来ました。

Sheet1のA列を範囲指定　→　データ　→　入力規則　→　リストを選択
数式欄に
=メーカー　としてOK　これでA列はメーカーがリスト表示されます。
B列を範囲指定　→　同様にリストから数式欄に
=INDIRECT(A2)　とします。
これでA列で選択したメーカーが扱っている商品名だけがB列にリスト表示されます。

最後に価格のC2セルに
=IF(COUNTBLANK(A2:B2)>0,"",INDEX(Sheet2!$C$2:$C$2000,MATCH(A2&B2,Sheet2!$E$2:$E$2000,0)))
という数式を入れオートフィルで下へコピーすると
画像のような感じになります。

以上、大変長々と書きましたが
参考になれば幸いです。
他に良い方法があれば読み流してくださいね。m(__)m