日本語入力ソフトとVBAの覚え書き

・各種日本語入力ソフトの辞書解説 ・ちょっと楽になるExcel VBA集

Excelの入力規則で2段リストを作る方法

f:id:dz_dzone:20170814233145j:plain

Excelでドロップダウンリストで入力をする「入力規則-リスト」が便利ですが、例えばリストを2つ用意して、1つめのドロップダウンリストの結果で2つめのドロップダウンリストを変化させることは可能でしょうか。答えは可能です。ここでは、このような2段リストの作り方を解説していきます。

 

図のようなシートを用意しました。1段目のリストは北東北3県、2段目のリストは各県の市を選択するものです。

f:id:dz_dzone:20180306094420j:plain

 

1段目のリストを作成する

リボン「データ」の「データツール」グループから「データの入力規則」を選択します。

f:id:dz_dzone:20180306101525j:plain

「入力値の種類」を「リスト」に設定します。

f:id:dz_dzone:20180306101535j:plain

「元の値」欄の一番右のアイコンをクリックし、画面上でドラッグして範囲選択します。もう一度右のアイコンをクリックします。

f:id:dz_dzone:20180306101546j:plain

「OK」をクリックして完了です。

f:id:dz_dzone:20180306101558j:plain

2段目のリストを作成する

見出しの下に行を追加する

2段目リストを作るために、見出し行の下に作業行を追加します。ここでは範囲選択肢から右クリックして「挿入」を選びます。

f:id:dz_dzone:20180306101612j:plain

「下方向にシフト」を選択して「OK」をクリックします。

f:id:dz_dzone:20180306101714j:plain

見出し行の下に行が追加されました。

f:id:dz_dzone:20180306101809j:plain

「市」のセル範囲を絶対指定で追加した行に書き込む

先ほど追加した行に、各県の「市」を示すセル範囲を絶対指定で入力します。下図では、セル範囲が「G4~G13」ですので、絶対指定「$G$4:$G$13」となります。

f:id:dz_dzone:20180306101821j:plain

絶対指定「$G$4:$G$13」をG3セルに入力します。

f:id:dz_dzone:20180306101940j:plain

同様に、岩手県、秋田県を入力します。

f:id:dz_dzone:20180306101953j:plain

※選択範囲アドレスをクリップボードにコピーするVBAコード

参考までに選択範囲アドレスをクリップボードにコピーするVBAを書いてみました。こちらの別記事を参照して下さい。

dz11.hatenadiary.jp

「市」に対する作業用セルを追加する

2段目リストを作成するためのもう一つの作業セルを作ります。下図のE7~E8です。

f:id:dz_dzone:20180306102005j:plain

HLOOKUP関数にて、検索値=1段目リストで選択した県(セル:C3)、検索範囲=「市」の見出し行と先ほど追加した行(セル範囲:$G$2:$I$3)とし、2行目(セル範囲)が戻り値となるよう設定します。

f:id:dz_dzone:20180306102108j:plain

2段目リストを設定する

C4セルの「データの入力規則」で、「元の値」欄に「=INDIRECT($E$8)」と入力します。これは先ほどのHLOOKUP関数の戻り値のセルに該当します。また、INDIRECT関数は指定したセルの内容をそのまま取得するものです。

f:id:dz_dzone:20180306102552j:plain

2段目リストの結果

下図のように、1段目リストの結果「秋田県」に対する「市」がリストに表示されました。

f:id:dz_dzone:20180306102946j:plain

「岩手県」の場合

f:id:dz_dzone:20180306103307j:plain

以上のように、2段リストが完成しました。応用すれば3段目以上のリストも作ることが出来ます。

 

にほんブログ村 IT技術ブログ VBAへ←記事を気に入って頂けたらポチッとしてもらえると励みになります。