メインコンテンツへスキップ
見出し画像

[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列で選んだ都道府県」が入ります。
    そのため、都道府県を選んだ後、すぐに市町村を選ばず、後で市町村を選ぼうとしても、該当の市町村は出てきません。
    ・都道府県と市町村に不整合が生じる場合があります。
    市町村を選んだあと、都道府県名を変えても、市町村はそのままです。
    従って、都道府県にない市町村名が表示されたまま、ということが起きます。
    これはエラー表示をすることが可能です(詳細は別途)。

    以上です。
    マクロの作成が必要ですが、構造はシンプルです。
    ただし、マクロが動く、というだけで抵抗感を持つ人もいるかもしれませんし、環境が許さない場合があるかもしれません。






    あなたへのおすすめ