以下のようなことをしたいと思っています。
できる限り、もとあるデータを使って、シンプルにできる方法を探しています。
【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.1
- 回答日時:
まず、Sheet1 の処理です。
1.フィルタオプションで、A列のデータを重複を無視してD列に抜き出し、抜き出した
データ範囲を選択して名前ボックスで「都道府県」と定義します
2.B列の北海道の市町村をすべて選択し、名前ボックスで「北海道」と定義します。
以下すべての都道府県分を同様に処理します。
次に、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で抽出する方法ですが、もし大阪にも港区があり、東京にも港区がある場合があるので、何か別の方法で参照することはできないでしょうか?
No.3ベストアンサー
- 回答日時:
こんにちは。
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
- 回答日時:
No.3です。
1つ重要な条件を書き忘れました。
Sheet1の表は、A列で並べ替えられているのが条件です。
もう少し正確に言えば、A列の同じ値が固まっているということです。
質問欄の例ならOKで、
北海道
北海道
東京
北海道
・・・
のようにバラバラはダメということです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 新幹線 北海道新幹線の速達列車の名前を予想してください。 私は、 速達型は、ぴりか号(上野、大宮、仙台、盛岡 4 2023/02/11 19:22
- Excel(エクセル) スプレットシートでA1~G1にデータが入っていて、 それを1列ずつ空けて表示する関数がわかる方いまし 4 2022/08/25 09:39
- PHP ファイルの書き込みについて教えて下さい。 1 2023/03/20 12:01
- Excel(エクセル) 【Excel】住所に郵便番号を付記する方法 3 2022/05/07 17:15
- 電車・路線・地下鉄 JRにフリー切符 こんな乗車、計画しましたが注意点は何でしょうか。 3 2022/11/22 12:37
- 郵便・宅配 北海道版の日刊スポーツとスポーツ報知を購入しましたが1週間以上たっても届きません。 1 2023/02/28 21:34
- 新幹線 東北北海道新幹線の列車名を東海道山陽新幹線と同じにするとしたら皆さんはどう思いますか? 停車駅は下記 8 2022/06/11 11:40
- 新幹線 203x年に開通する北海道新幹線ですか、全車グランクラス、カフェ、ラウンジ、個室付きで食事付き!おも 2 2023/02/25 16:21
- 新幹線 北海道新幹線が203x年に開業予定する北海道新幹線のグランクラスですが、東京、大宮、仙台、新青森、札 4 2023/07/09 10:57
- 新幹線 203x年に開業予定する北海道新幹線についてですが、東京、上野、大宮、仙台、盛岡、いわて沼宮内、二戸 1 2023/07/08 14:16
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
Microsoft1Officeの互換ソフト...
-
Excel ピボットテーブルで日付...
-
エクセル関数を教えてください
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
LOOKUP関数を使えばいいのでし...
-
エクセル 白黒印刷で白線を印刷...
-
【関数】先頭だけにある、半角...
-
【関数】適切な文字数の数字を...
-
Excelのチェックボックスの使い...
-
エクセルでの作業計算方法について
-
Excelのpivotについて質問です
-
WPS OFFICEでの縦書きについて
-
時間によってファイル名が変わ...
-
エクセルのセルに同じ大きさの...
-
Aというブックの1というシート...
-
エクセルの順位別一覧表の自動...
-
西暦や和暦の表示をyyyymmdd表...
-
【マクロ】エクセルにかいてあ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報