
[EXCEL] リスト入力⑫ マクロ使用 都道府県⇒市町村選択の2段階リスト別解2 (タテヨコ表不要&複数セル対応版)~入力表作成レッスン~
▶ こちらを先にどうぞ プルダウンリスト シンプル基本版
▶目次 > 様式/入力表の作成 > プルダウンリスト
▶関連記事:プルダウンリスト
【まとめ】
・FILTER関数を使って都道府県名から市町村名を抽出する。
・通常は1セルでしか機能しないが、複数セルで使えるようにマクロを持ちいて、選んだ市町村名をFILTER関数の対象となるセルへ張り付ける。
【説明】
先日、こんな記事を書きました(この記事、かなり前に書いたままでした)。
[EXCEL] リスト入力⑩ 都道府県⇒市町村選択(タテヨコ表不要版・1セルのみ有効)~入力表作成レッスン~
都道府県×市町村のタテヨコ表も「名前の定義」もINDIRECT関数も不要で、都道府県⇒市町村の2段階プルダウンリストが出来る、というものです。
ただし、1セル限定、という制約がありました。

該当の市町村のみがリストに出ます
ただし1セル限定でしか機能しません
今回は、複数セルに適用する方法です。
前回記事ではできなかった「市町村入力欄が縦にいくつも並ぶ入力表」でも使えます。
*本記事の方法以外にもやり方は色々あります。OFFSET関数等を使った方法が一番多く紹介されているかもしれません。各都道府県に該当する市町村の数を把握して、都道府県の始まりから市町村数分だけをリスト化する、というパターンが多いかと思います。今回はこれとは全く違うアプローチです。
どちらがいい悪いというものではありません。
また、私の素人発想なので、落とし穴があるかもしれませんが、ご寛恕ください。
冒頭記事と重複する部分がありますが、ご容赦ください。
1 都道府県・市町村一覧を「データ表」に張り付ける。
データは、いつも通り、総務省の市町村コード表を使います。
地方行政のデジタル化|全国地方公共団体コード - 総務省
「都道府県コード及び市区町村コード」
(令和6年1月1日更新)Excelファイル
シート名を「市町村シート」にし、「都道府県名(漢字)」「市町村名(漢字)」の「(漢字)」は消しておきます(政令指定都市のシートは削除)。
「(カナ)」はグループ化して隠しておきます。
表頭(項目名)の下に1行空欄行を追加しておきます。
2行目のどこかを選択⇒ Ctrl + Shift + : ⇒「挿入」ダイアログが開く ⇒ 「行全体」を選択 ⇒ Enter


*都道府県リストを作る際に「空白欄」を初期値とするためです。
*2行目を選択 ⇒ Ctrl+SHift+; でも、1行挿入されます。
以下の操作は、本来は別シートに作るべきですが、説明のため同一シート上で展開していきます。
2 都道府県リストを作る
*本来は「リスト」シートを作り、そこで展開する。
① G1セルに「都道府県」と入れる
② G1セルに 以下を入れる。
=UNIQUE(B:B)
*G1セルに =UNI と入れる⇒関数候補が出る⇒ ↓で「UNIQUE」を選択 ⇒ Tabで決定 ⇒ B列をクリック ⇒ ENTER

③都道府県一覧が出る(重複は排除されている)
④「0」を非表示にする
*列全体を選択⇒「セルの書式選択」(ctrl+1)⇒「表示形式」⇒「ユーザー定義」⇒「種類」に「#」を入れる


「0」が消える。

3 入力欄を作る・・・本来は別シートにする
J,Hに「都道府県」「市町村」の入力欄を作る

4 都道府県の入力欄にリストを作る
①J2セルを選択
②「セルの入力規則」を開く(Alt⇒A⇒V⇒V)

③以下を選択(入力)
・入力値の値:リスト
・元の値:=$G:$G (都道府県のリスト)

④J2セル右の▼をクリック(または Alt+↓)で都道府県リストが出る。

5 該当の市町村一覧リストを作る
①H2セルに以下を入れる。
=FILTER(C:C,B:B=J2)
②「0」が並ぶ。

③「0」を「セルの書式設定」で非表示にする。
*列全体を選択⇒「セルの書式選択」(Ctrl+1) ⇒「表示形式」⇒「ユーザー定義」⇒「種類」に「#」を入れる(2④参照)
④ J2セルで都道府県を選ぶと、該当の市町村が表示される。

6 市町村選択欄にリストを表示させる
① K2セルを選択
②「セルの入力規則」を開く(Alt ⇒ A ⇒ V ⇒ V)

③以下を選択(入力)
・入力値の値:リスト
・元の値:=$H:$H (市町村のリスト)

④ 該当の市町村が表示される

⑤ 都道府県を変えれば、該当の市町村リストも変わる

ここまでは、前回と同じです。
JとKの列の下に都道府県・市町村の入力欄を作り、都道府県を選んでも・・・

常に、一番のうえ(J2セル)で選んだ北海道の市町村しか出てきません。
これは、H列の市町村一覧はJ2の都道府県を表示しているからです。
これでは、複数セルでのリスト選択はできません。
ではどうするか?
関数(OFFSETなど)を使う方法もありますが、今回は「マクロ」を使います。
マクロの考え方
各セルで選んだ都道府県で、H列の市町村一覧が出来ればいい(フィルターが掛かればいい)
⇒ つまり、=FILTER(C:C,B:B=J2) の「J2」の値が変わればいい
⇒しかし、J2セルが変わるのは困るので、上の式が参照にするセルを変えておく(J2の「北海道」がかわってしまってはいけない)。
=FILTER(C:C,B:B=J2) を
=FILTER(C:C,B:B=I2) にしておく。
J2ではなくI2セルを参照する。
*リストとして選択するデータとは違うところを指定しておきます。
現在、I2は空欄なので、H列には何も出ません。

*都道府県を入れる欄に応じて、上の式のJ2に該当するセルの番地を、都道府県を入れる欄のセルに変える方法もあります(J3で都道府県を選ぶなら、上の式はJ2ではなくJ3を参照するよう自動で変わるようにする、というもの)。関数で出来そうですが、今回はパス。
とりあえず、I2セルはJ2セルと同じ =J2 としておきます。
これにより、J2セルで北海道を選ぶと、I2にも北海道が入り、G列で北海度でフィルターが掛かり、H列には北海道の市町村一覧が表示されます。
=FILTER(C:C,B:B=I2) のC列は市町村名、B列は都道府県名です(下図ではB列、C列は見てていません)。

さて、ここからが問題です。
J2セル以外(J3以下のセル)で都道府県を選んだ際に、選んだ都道府県名がI2セルに入れば、H列には該当の市町村が表示されます。
すると、K列(K3以下のセル)にも該当の市町村リストが表示され、該当の都道府県の市町村を選ぶことが出来ます。
ややこしいでしょうか?
これは、例えばJ3セルで都道府県を選んだら、その都道府県名をコピーして、I2セルに「手作業」で張り付ければ、可能です。
でも、そんなの面倒です。
ではどうするか?
ここでマクロを使います。
Copilotにマクロを作成してもらう
J列の各欄で都道府県を選んだら、選んだ都道府県名をI2に張り付ける、というマクロを考えます。
しかし、今の私はマクロを自力で作れません。
ですので、Copioltに頼みます。
依頼文(プロンプト・指示文)は、なるべく具体的にします。
とはいえ、簡単です。
(指示文の例)
・「都道府県名貼り付け」という名前のマクロを作りたい。
・J列の各セルでプルダウンリストからデータを選んだら、そのデータの値をI2セルに張り付ける。
これだけです。
(生成されたマクロ)
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitHandler' 変更されたセルがJ列かどうかを確認
If Not Intersect(Target, Me.Columns("J")) Is Nothing Then
' 入力規則がリストかどうかを確認
If Not Target.Validation Is Nothing Then
If Target.Validation.Type = xlValidateList Then
Application.EnableEvents = False
' 選んだ値を I2 セルに貼り付け(値のみ)
Me.Range("I2").Value = Target.Value
End If
End If
End If
ExitHandler:
Application.EnableEvents = True
End Subマクロをファイルに張り付ける
① Alt+F11
② マクロを貼り付けたいシートを選ぶ
*シートが出なければ「表示」⇒「プロジェクトエクスプローラー」
③ マクロを貼り付ける

④ 保存(Ctrl + S)して、Excelに戻る(ツールバーの一番左のExcelマークをクリック)
⑤ マクロ有効ブック(.xlsm)形式で保存する。
マクロが有効か確認する
① I2の数式は削除しておく

② J2セルで都道府県を選ぶ(例:石川県
③すぐにI2セルに同じ都道府県名が表示される

④ K2セルをクリックする
⑤ J2で選んだ都道府県(石川県)に該当する市町村名が表示される。

⑥ J2とK2セルを下に貼り付ける(Ctrl+D)・・・ここでは既に貼り付け済み
⑦ J3セルで都道府県を選ぶ(例:富山県)
⑧ I2セルにJ3セルの都道府県が表示される。

K3セルをクリックすると、J3セルの都道府県(富山県)に該当する市町村リストが表示される。

★注意★
・I2セルは、「最後にJ列で選んだ都道府県」が入ります。
そのため、都道府県を選んだ後、すぐに市町村を選ばず、後で市町村を選ぼうとしても、該当の市町村は出てきません。
・都道府県と市町村に不整合が生じる場合があります。
市町村を選んだあと、都道府県名を変えても、市町村はそのままです。
従って、都道府県にない市町村名が表示されたまま、ということが起きます。
これはエラー表示をすることが可能です(詳細は別途)。
以上です。
マクロの作成が必要ですが、構造はシンプルです。
ただし、マクロが動く、というだけで抵抗感を持つ人もいるかもしれませんし、環境が許さない場合があるかもしれません。