エクセル：複数の条件からドロップダウンリスト作成

質問

エクセル：複数の条件からドロップダウンリスト作成

お知恵を借りられれば幸いです。
ドロップダウンリストを作りたいと考えております。

（参照元のデータ）
部屋Ａ
　　午前　午後　夜間
　　￥50　￥60　￥70
　　￥70　￥75　￥800
　　￥90　￥110　￥120

部屋B
　　午前　午後　夜間
　　￥90　￥110　￥120
　　￥650 ￥15　￥80
　　￥90　￥110　￥120

部屋Ｃ
・
・
・

（入力用シート）　　　　　　　　　　　　　　　　　　　
部屋名　　　　午前　　　　　　　　　午後　　　　　　　　　夜間
部屋A　※￥50～￥90のリスト　※￥60～￥110のリスト　　※￥70～￥120のリスト

上記の用に、元のデータを参照して
入力用シート「部屋名列」に部屋を入力すれば、午前、午後、夜間のリスト
が相応のものになるようにしたいのですが・・・。

元データの体裁はこだわっておりません。

よいお知恵があればお教え願えれば幸いです。

回答No.7

mitarashi · ベストアンサー

#6です。ひょっとして皆さんと同じ事の横着版を回答しただけなのかと心配になり、名前を付けないバージョンを試しにやってみました。ただ、元データ形式が必ずしも同じで無いので、いまいち分かりませんでした(^^;)
#5と同様の形式の元データの存在するシートの、部屋Ａ、部屋Ｂ．．．が入っている列全体に名前を付けます。（これは入力規則設定の際に、Sheet1!といった表現が認められないため）ここでは「部屋列」という名前を付けました。先に名前を付けないと記しましたが、この一個だけはシートをまたぐために必要です。

入力用シートのB2からD2の入力規則の元データに下記の式を設定します。
=INDEX(OFFSET(INDEX(部屋列,MATCH($A2,部屋列),1),0,1,3,3),,COLUMN()-1)
これはそのまま下方にコピーして通用するようにしてあります。

お礼

皆様回答ありがとうございました！

色々なやり方があったのですね。。

ほんとは皆様にベストアンサーをさしあげたいのですが、
もっとも手間が少なかったものをベストアンサーとさせて頂きました。

他のご意見も参考にさせて頂きます。

ありがとうございました。

回答No.6

mitarashi

VBA大好き人間ですが、トライしてみました。
名前を付ける一手間がかかりますが、式は簡単になります
元データが下記の様な体裁とします。（表示位置がずれますが、価格のところは３×３のマトリクスだとみなして下さい）この３×３の値段のセルに、「部屋Ａ」という名前を付けます。以下、部屋の数だけ名前を設定する必要があります。
部屋　　午前　午後　夜間
部屋Ａ　\50　　\60　　\70
　　　　\70　　\75　　\800
　　　　\90　　\110　\120

入力用シートのA2に、部屋Ａ、部屋Ｂ...を選択する入力規則が設定されているとして、
B2の入力規則の元の値のところに下記の式を入れます
=INDEX(INDIRECT($A$2),,1)
同様に、
C2の入力規則の元の値:=INDEX(INDIRECT($A$2),,2)
D2の入力規則の元の値:=INDEX(INDIRECT($A$2),,3)
という式を入れれば、お望みの事が実現できると存じます。

回答No.5

kagakusuki

今仮に、Sheet2に元データのリストをSheet2に作成しておき、Sheet1のセルにドロップダウンリストを表示させるものとします。

　まず、Sheet2のA1セルに

部屋名

と入力して下さい。
　次に、Sheet2のB1セルに次の数式を入力して下さい。

=IF((COLUMN(B:B)+1)/3>COUNTIF($A:$A,"><")-($A$1<>""),"",INDEX($A:$A,(COLUMN(B:B)+1)/3+1))

　次に、Sheet2のB2セルに次の数式を入力して下さい。

=IF(B1="","","午前")

　次に、Sheet2のC2セルに次の数式を入力して下さい。

=IF(B1="","","午後")

　次に、Sheet2のD2セルに次の数式を入力して下さい。

=IF(B1="","","夜間")

　そして、Sheet2のB1～D2の範囲をコピーして、同じ行のE列から右方向に向かって、部屋数の3倍を上回る列数となるまで貼り付けて下さい。

　次に、Sheet1の
A1セルに 部屋名
B1セルに 午前
C1セルに 午後
D1セルに 夜間
と入力して下さい。
　次に、Sheet1のA2セルを選択してから、以下の操作を行って下さい。

メニューの[データ]をクリック
　　↓
選択肢の[入力規則]をクリック
　　↓
現れた「データの入力規則」ウィンドウの「設定」タグをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中の「リスト」をクリック
　　↓
「元の値」欄の中に以下の数式を入力

=INDIRECT("Sheet2!A2:A"&MATCH("゜",INDIRECT("Sheet2!A:A"),-1))

　※"゜"の部分は半濁点
　　↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック

　次に、Sheet1のB2セルを選択してから、Sheet1のA2セル場合と同様の操作で、「元の値」欄の中に以下の数式を入力して、「データの入力規則」ウィンドウの[OK]ボタンをクリックして下さい。

=OFFSET(INDIRECT("Sheet2!A3:A"&MATCH(99^9,OFFSET(INDIRECT("Sheet2!A:A"),,MATCH($A2,INDIRECT("Sheet2!1:1"),0)-1))),,MATCH($A2,INDIRECT("Sheet2!1:1"),0)+COLUMN(A:A)-2)

　次に、Sheet1のB2セルをコピーして、Sheet1のC2セルとD2セルに貼り付けて下さい。

　次に、Sheet2のA2以下に、
A2セルに　　　部屋A
A3セルに　　　部屋B
A4セルに　　　部屋C
　 ・　　　　　　・
　 ・　　　　　　・
　 ・　　　　　　・
という具合に、各部屋名を入力して下さい。
　この時、部屋の順序は順不同でも構いませんが、部屋名を入力しているセルの間に、空欄のセルが無い様にして下さい。
　すると、B1、E1、H1・・・・セルに、部屋名が自動的に表示され、2列目には午前、午後、夜間という表示が自動的に行われますから、
3行目以下に各部屋の金額を入力して下さい。

　これでSheet1の
A2セルには各部屋名のドロップダウンリスト、
B2セルにはA2セルで選択した部屋の、午前の金額のドロップダウンリスト、
C2セルにはA2セルで選択した部屋の、午後の金額のドロップダウンリスト、
D2セルにはA2セルで選択した部屋の、夜間の金額のドロップダウンリスト、
が設定される筈です。

　もし、3行目以下にも同様の入力規則を設定する場合は、Sheet1のA2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

＃２，３です
＃２の修正
>>=INDEX((A3:C5,E3:G5,I3:K5),・・・
複合参照にするの忘れてた。列番号固定ですね。

＃２，３
さらに、INDEXの領域番号は不要でした m(_ _"m)
#領域番号を使ってみたいお年頃だったのだろうか^.^）

リストの作りの数式部分だけ。＃２添付図参照
(名前の定義や入力規則は設定してください。)

B10セルへ
=INDEX(A$3:E$5,,MATCH($A10,$1:$1,0))
右へ下へオートフィル

解説
MATCH関数で、A10セルの部屋を検索。
INDEX関数の行番号を省略することで、列を返す。
(右へオートフィルで対象範囲が変わる)
セルを選択→[F2]→[F9]とすることで目的のリストとなっていることを確認してください。
[Enter]で確定せず、[Esc]で抜けます。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

>>入力範囲は カット＆ペーストで良いかと思います。
ウソでした。というより、もうひと手間。
=INDEX((Sheet1!A$3:A$5,Sheet1!E$3:E$5),,,(3+MATCH(Sheet1!$A2,Sheet1!$1:$1,0))/4)
ではMATCHの検索値が異なってしまうようなので
=INDEX((Sheet1!A$3:A$5,Sheet1!E$3:E$5),,,(3+MATCH($A2,Sheet1!$1:$1,0))/4)
と編集してあげる必要があるようです。
(列Aは省略)

回答No.2

某HN クロメート(Chromate)（@CoalTar）

1. B10セル選択
2. [Ctrl]+[F3]名前の定義
　 名前 リスト
　 参照範囲 =INDEX((A$3:A$5,E$3:E$5),,,(3+MATCH($A10,$1:$1,0))/4)
3. 入力規則
　 リスト =リスト
細かいところは添付画像で。

入力範囲は カット＆ペーストで良いかと思います。
部屋Cも入れるとINDEXの第一引数は
=INDEX((A3:C5,E3:G5,I3:K5),・・・
となるでしょう

回答No.1

tom04

こんばんは！
一例です。無理やりって感じになりますけど・・・

Sheet1にリスト表示させたい場合はそれぞれを名前定義しておく必要がありますので、
↓の画像では下側がSheet2です。
その表に少し手を加えておきます。

午前・午後・夜間の前にそれぞれ部屋の1文字を付けて表を作っています。
A列は単にSheet1の部屋のリストにするための表ですのであまり深く考えなくて良いと思います。

当方使用のExcel2003の場合です。
まず、A1～A4を範囲指定　→　メニュー　→　挿入　→　名前　→　作成　→　上端行　を選択し、OK
これでA2～A4の範囲が「部屋」という名前定義されました。
同様に画像の薄い緑色部をそれぞれの列の名前定義の「名前」にするようにします。
Sheet2のC1～C4を範囲指定　→　挿入　→・・・（中略）・・・→　上端行　を選択し、OK
これでSheet2のC2～C4が「A部屋」と名前定義されました。
この作業を表のすべてで行っていきます。
これができれば後は簡単です。

Sheet1のA2セルをアクティブにし　メニュー　→　データ　→　入力規則　→　リストから「元の値」の欄に
=部屋
としてOK

次にSheet1のB2～D2を範囲指定　→　メニュー　→　データ　→　入力規則　→　リストの「元の値」の欄に
=INDIRECT(RIGHT($A2,1)&B1:D1)
としてOK

これでA2でリスト表示された部屋のそれぞれの「午前・午後・夜間」のリスト表示が可能になると思います。
尚、画像では部屋名の右一文字を表に加えていますが、二文字つけても構いません。
その場合B2～D2のリストの「元の値」の数式は
=INDIRECT(RIGHT($A2,2)&B1:D1)
となりますので、その辺はお作りになる表の部屋名によってアレンジしてみてください。
（付け加える文字数は統一しておきます）

以上、参考になれば良いのですが
他に良い方法がればごめんなさいね。m(__)m