Excel：以下のようなことを実現できますか？(入力規制関係)

質問

以下のようなことをしたいと思っています。
できる限り、もとあるデータを使って、シンプルにできる方法を探しています。

【Sheet1】
A列、B列、C列
北海道、登別、001
北海道、札幌、002
北海道、函館、004
東京、港区、101
東京、品川区、103
東京、練馬区、105
東京、足立区、110
東京、新宿区、102
大阪、大阪市、310
大阪、泉市、315
大阪、梅田、311

【Sheet2】
A列、B列、C列
北海道▽、札幌▽、002

→Sheet2では入力規制で、A列で「北海道」を選ぶと、B列では「登別」、「札幌」、「函館」のみがリストに表示されるようにしたい。また、B列を選ぶと、C列にはB列で選んだ都市の番号(Sheet1のC列の番号)が自動で表示させたい。

データの数は、1000以上あります。
このようなことはできますか？
宜しくお願いします。

回答No.3

maruru01 · ベストアンサー

こんにちは。maruru01です。

データが多いと、配列数式は重くなりますので、作業列を使用した方法です。
Sheet1のD列ともう1列を作業列に使用します。

まず、Sheet1のD1に、

=IF(COUNTA(A1:B1)=2,A1 & "_" & B1,"")

と入力して、下の行へコピーします。
Sheet1のデータが増えていく可能性があるなら、予め下のほうまでコピーしておきましょう。
次に、No.1の方の1.と同じで、A列の重複なしのリストをどこかの列に抜き出し、それに名前を付けます。
（仮に「リストA」とします。）

Sheet2のA1の入力規則を、
[入力値の種類]：「リスト」
[元の値]：「=リストA」
に設定します。

Sheet2のB1を選択した状態で、メニューの[挿入]→[名前]→[定義]で、
[名前]：適当な名前（仮に「リストB」とする）
[参照範囲]：「=INDEX(Sheet1!$B:$B,MATCH(C1,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(C1,Sheet1!$A:$A,0)+COUNTIF(Sheet1!$A:$A,C1)-1)」
と設定して、[OK]

Sheet2のB1の入力規則を、
[入力値の種類]：「リスト」
[元の値]：「=リストB」
に設定します。

さらに、Sheet2のC1に、

=IF(COUNTA(A1:B1)=2,IF(COUNTIF(Sheet1!$D:$D,A1 & "_" & B1),INDEX(Sheet1!$C:$C,MATCH(A1 & "_" & B1,Sheet1!$D:$D,0)),""),"")

と入力します。

回答No.4

maruru01

No.3です。

1つ重要な条件を書き忘れました。
Sheet1の表は、A列で並べ替えられているのが条件です。
もう少し正確に言えば、A列の同じ値が固まっているということです。
質問欄の例ならOKで、

北海道
北海道
東京
北海道
・・・

のようにバラバラはダメということです。

回答No.2

misatoanna

＃1の訂正です。

入力規制ではなく、入力規則でした。

回答No.1

misatoanna

まず、Sheet1 の処理です。
1.フィルタオプションで、Ａ列のデータを重複を無視してＤ列に抜き出し、抜き出した
　データ範囲を選択して名前ボックスで「都道府県」と定義します
2.Ｂ列の北海道の市町村をすべて選択し、名前ボックスで「北海道」と定義します。
　以下すべての都道府県分を同様に処理します。

次に、Sheet2 に入力規制（リスト）を設定します。
1.セルA1： 元の値 =都道府県
2.セルB1： 元の値 =INDIRECT(A1)
3.セルC1： セルB1の内容から、VLOOKUP関数で抽出します。
　　　　　　　=VLOOKUP(B1,Sheet1!$B$1:$C$11,2,FALSE)

補足

早速の回答有難うございます。
下記2点ほど補足させていただきます。

(1)上記は都道府県を例にしましたが、実際はもっと膨大なデータを扱う為、Sheet1の2の処理手順は作業負荷が大きいのですが。

(2)また、VLOOKUPで抽出する方法ですが、もし大阪にも港区があり、東京にも港区がある場合があるので、何か別の方法で参照することはできないでしょうか？