[EXCEL] リスト入力⑥ ２段階リストをINDIRECT関数で作る（正攻法）～入力表作成レッスン～

関連記事一覧
▶目次>様式/入力表の作成>プルダウンリスト
▶関連記事：プルダウンリスト

【まとめ】
・プルダウンリストで多数のデータから選ぶのは大変
（例：全国の市町村リスト一覧から該当市町村を選ぶ）
・２段階リストを作って絞り込めると楽
（例：都道府県を選ぶ ⇒ 都道府県内の市町村から選ぶ）
・２段階リストを作るには「タテヨコ表」が必要
（例：ヨコに都道府県名×タテに市町村名のリスト）
・リストに「名前」を付けてINDIRECT関数で呼び出す
＊リストを開いた際に常に一番上から選べるようにする方法あり（別記事）

＊私の不得意分野なので他サイトの参照をお勧めします
Googleで「エクセル　プルダウンリスト」を検索
note内で「エクセル　２段階リスト」を検索

【説明】

前記事では都道府県を選びやすくする方法（力技）をお示ししましたが、都道府県ならともかく、全国の市町村の中から該当の市町村を選ぶのは大変です。
そこはやはり２段階リストの出番。
２段階リストとはこんな感じのもの。

２段階リストを作るためには、前準備として、
横に都道府県が並び、
縦に都道府県ごとの市町村が並ぶリスト
を準備しておく必要があります。


元データは以下で紹介した総務省のデータ
[EXCEL] 総務省の「全国地方公共団体コード」を見て唖然！ トンデモ表だった？（と思うのは私だけ？）
こんな感じのもの。

これを元に都道府県別の市町村リストを作ります。
完成形はこれ。

こんな表、作るの面倒ですよね？（作ったけど）

普通にやれば、こんな手順
１　一番上に「北海道」と入れる
２　「北海道」の市町村を範囲指定してコピー
３　「北海道」の下に貼り付け

これを４７回やればいいだけです。１時間もかからないかな？
でも、４７回もやってらんない。
市町村の範囲指定を間違える恐れもあります（その方が怖い）。

なので、上の一覧は別の方法で作りました。
そしたら、５分かかりませんでした。
なんだ、楽ちんじゃん！

その方法はともかく、これで２段階プルダウンリストを作る準備が出来ました。
と思ったけど、まだ、です。

まずは、「都道府県×市町村」一覧に名前を付けます。

１ 一覧の選択

A1セルを選択 ⇒ Ctrl + A で一覧全体が範囲指定される
（北海道が一番下まで伸びる）。

２　Alt ⇒ M（数式） ⇒ C（選択範囲から作成）

下のダイアログ（小ウィンドウ）が出たら・・・

左端列の☑は外しておく。
つまり、上端行の都道府県名のみを名前として作成する。
「OK」を押すと・・・何も変わらない・・・。
「名前が付きました」とか、メッセージ位、出してくれてもいいのに。

でも、Alt ⇒ M（数式） ⇒M（名前の管理）と押すと・・・

都道府県別に名前が付いている（らしい）ことが分かる。

３　２段階リストの「都道府県」欄を設定

Alt ⇒ A ⇒ V ⇒ Vで「データの入力規則」を開いたら、「リスト」を選択

続いて、「元の値」の欄は、北海道から沖縄までの部分を選択

これで、「都道府県」をクリックすると（又は Alt＋↓ ）で都道府県名が表示される。

４　「市町村」の欄の設定

同じく　Alt ⇒ A ⇒ V ⇒ V　で「データの入力規則」を開き、
「リスト」を選ぶ。
「元の値」には
=indirect(b3　と入れる（小文字でいい。最後のカッコもなくてもいい。勝手に付く）

b3セルは、都道府県名が入る欄。
INDERCTとは、マイクロソフトのサイトだと

説明
指定される文字列への参照を返します。 セル参照はすぐに計算され、結果としてセルの内容が表示されます。 INDIRECT 関数を使うと、数式自体を変更しないで、数式内で使用しているセル参照を変更することができます。

書式
INDIRECT(参照文字列, [参照形式])

とのこと。なんだかよく分かりませんが、要は、
INDERCTのカッコの中の名前のリストを表する、ってことです。

ちなみに、この状態で「ＯＫ」を押すと、下のようなエラー表示が出ます。

なぜなら、「都道府県」の欄が空欄だから。

５　２段階リストの完成

でも、「都道府県」の欄で「北海道」を選ぶと・・・

「市町村」には、北海道内の市町村名一覧が出ます。

６　市町村欄をクリックしても空欄しか出ない？

しかし・・・
青森県を選んで、市町村欄をクリックすると・・・

何も出ません。
と思ったら、上にスクロールしたら出てきました。

どうやら、北海道の市町村数が多いので、それに引っ張られて下の方が出てしまうようです。
つまり、市町村数が少ない都道府県も、北海道と同じ範囲を選択しているから、といったらいいでしょうか？
*2025/11/19追記
一番下の空欄が選択されてしまうのは、北海道に引っ張られるからではありませんでした。市町村名を選ぼうとしているセルが空欄なので、初期値として青森県の列にある空欄を選んでしまうためでした。
これを防ぐためには、下の方法以外でも、意外と簡単に、リストを開いたら常に一番上から選べる方法、ありました。別記事にします。

７　空欄を表示させないためには？（Copilotに聞いてみた）

これを常に一番上（都道府県名のすぐ下に市町村）にするにはどうすればいか？
これ、結句面倒そう。
Copilotに聞いたら、以下の答え。

（質問）
横に都道府県名、各都道府県名の下に市町村名が並びまます。全部を一括範囲指定して、範囲選択で名前を作成し、上端行を名前にしました

（Copilot）
なるほど、構造がよく分かりました 👍
横に都道府県名が並び、その下に市町村名が縦に並ぶ」形で、範囲選択から「上端行を名前にする」で名前定義を作ったのですね。

この方法だと、各都道府県の名前範囲に「空白セル」まで含まれてしまうため、プルダウンに空欄が出てしまいます。そこで 動的な名前定義 に切り替えるのが解決策です。

✅ 解決方法（横並び＋縦リスト構造の場合）

1. 各都道府県の名前定義を修正

例えば「茨城」という名前範囲を作った場合、今は

コード
=Sheet1!$B$2:$B$50

のように固定範囲になっているはずです。 これを COUNTA関数＋OFFSET関数 に変えます。

例：

コード
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$100),1)

• $B$2 が市町村リストの開始セル

• COUNTAで「入力されている数」だけ範囲を広げる

• 空白セルは除外されるので、プルダウンに空欄が出なくなります

2. INDIRECT関数で呼び出す

2段階目のデータ検証はそのまま

コード
=INDIRECT($A$2)

のように「選択した都道府県名」を参照します。 このとき、各都道府県の名前定義が動的になっているので、空欄は表示されません。

3. ポイント

• 見出し行（都道府県名）を含めないようにする

• 各列の「市町村リスト部分」だけを動的範囲にする

• データが増えても自動調整されるので管理が楽になります

💡 もし「都道府県ごとに市町村の数がバラバラで、途中に空欄がある」場合でも、この方法なら空欄を除外できます。

とのこと。
しかし、各都道府県別にリスト化されている。

これを、どうやって以下みたいにしたらいいかが分からない。
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$100),1)

今回は、全都道府県を一気に名前を付けてリスト化したが、個々にやばいいのか？
それも面倒。

＊2025/11/19
途中でも書きましたが、リストを開いたら常に一番上から選べるようにする方法、ありました。意外と簡単かも。別記事にします。

＊記事書きました。
[EXCEL] リスト入力⑨ 都道府県⇒市町村の順で選択する２段階プルダウンリストで常に一番上を表示させる～入力表作成レッスン～