【Excel】ドロップダウンリストを連動させて自動入力させる

Excelのセル入力について、ドロップダウンから選択をさせる場合に、1つ目のドロップダウンの結果によって2つ目のドロップダウンの選択肢を変更したい場合があります。

例えば、都道府県を選択したら、その都道府県に紐づく市町村のみを2つ目のドロップダウンの選択肢としたい場合です。

↓東京都を選択したら、東京都の市だけが表示されるようにする

このような複数のドロップダウンの連動は、Web検索すると色々と情報があります。

ただ、2つ目のドロップダウンが先に選ばれた場合に、1つ目のドロップダウンの内容を自動で入力するといった連動については、あまり情報がありませんでした。

またドロップダウンの連動についても、セルの範囲に名前つけて対応する方式を紹介しているサイトばかりです。

個人的にExcelのセル範囲に名前をつけるのは好きではないので、名前をつけずに連動させる方法についても記載をしていきたいと思います。

MATCH関数とINDIRECT関数でドロップダウンを連動させる

例として、以下のような形で選択肢のマスタシートを作成して、そこから値を取得する形にしたいと思います。

選択肢のあるシートのシート名は「Sheet2」としました。

１つ目のドロップダウンリスト

まず、都道府県を入力させるためのドロップダウンリストの入力規則は以下のようになります。（データタブのデータの入力規則から設定します。）

=Sheet2!$1:$1

空白を無視するのチェックを外しています。

Sheet2!$A1:$C1みたいな形で、セルの範囲を直接指定してもよいです。ただ個人的には、上記のように行番号してにしてしまうほうが好きですね。

理由は列が増えたときに、式を変更する必要がないためです。

２つ目のドロップダウンリスト

次に市町村を入力するドロップダウンリストを作成します。

入力規則に入れる式は以下のようになります。

=INDIRECT("Sheet2!"&ADDRESS(2,MATCH(C2, Sheet2!$1:$1,0 )) & ":" & ADDRESS(99,MATCH(C2, Sheet2!$1:$1,0 )))

“C2″は都道府県が入力されているセルのアドレスになります。”Sheet2″は選択肢が設定されているシートの名前になります。

簡単に解説します。

まずMATCH関数を使用することで都道府県の値(C2)が何列目に存在しているかを確認することができます。

↓引数に神奈川県を与えてみる。神奈川県が2列目なので、2が返却される。

上記の2をADDRESS関数を使用して、列番号(アルファベット)に変換します。

↓神奈川県のアドレスがB1であることが返却される($は絶対参照の意味)

上記のADDEESS関数の引数の1番目を変更すると、返却されるアドレスの行数が変化するので、2行目から99行目を戻すようにします。

=ADDRESS(2,MATCH("神奈川県", Sheet2!$1:$1,0 )) & ":" & ADDRESS(99,MATCH("神奈川県", Sheet2!$1:$1,0 ))

↓上記式の結果

これで選択肢の範囲が取得できたので、INDIRECT関数を使用して文字列を式に変換します。

INDIRECT関数は引数に与えた文字列を式として評価できるようにする関数です。

自動入力させるようにする

次に、2つのドロップダウンリストを連動させて、自動入力を実装してみたいと思います。

具体的に今回は以下の機能を実装したいと思います。

• 市町村が変更されたら、都道府県を自動で選択させる
• 都道府県が変更されたら、市町村の選択を自動で解除する

前者はスピルなどを使用すれば実現可能かもしれませんが、後者はExcel関数だけでは難しそうなので、VBAを使用することにしました。

作成したコードが以下となります。

Private Const ADD_PREF = "C2" '都道府県のアドレス
Private Const ADD_CITY = "C3" '市町村のアドレス
Private Const MASTER_SHEET_NAME = "Sheet2" '選択肢のあるシートの名前

Private Sub Worksheet_Change(ByVal Target As Range)

    '無限ループしないようにイベントを止める
    Application.EnableEvents = False
    
    '変更されたのが都道府県のアドレスの場合
    If Target.Address(False, False) = ADD_PREF Then
        '市町村のアドレスの値を削除する
        ActiveSheet.Range(ADD_CITY).Value = ""
    End If
    
    '変更されたのが市町村のアドレスの場合
    If Target.Address(False, False) = ADD_CITY Then
        
        '選択肢が設定されているシートを取得する
        Dim masterSheet As Worksheet
        Set masterSheet = ThisWorkbook.Worksheets(MASTER_SHEET_NAME)
        
        '選択肢が設定されているシートにおける都道府県の列番号を取得
        Dim colPref As Integer
        colPref = masterSheet.Cells.Find(Target.Value).Column
        
        '都道府県を自動設定する
        ActiveSheet.Range(ADD_PREF).Value = masterSheet.Cells(1, colPref).Value
        
    End If
    
    'イベントを再開する
    Application.EnableEvents = True
    
End Sub

ワークシートのモジュールに対して、Worksheet_Changeという関数名を使用することで、セルの値が変更されたときに実行される関数を作成する事ができます。

このイベントは、ワークシート上の何れかのセルが変更された際に実行される関数となります。

関係ないセル変更時には何もしないようにするために、「もし、セルのアドレスが一致しているなら」というIF文を作成し、その中に実行したい処理を記載していきます。

また、Worksheet_Changeイベントでセルの値を変更すると無限ループが発生するため、Application.EnableEvents = Falseを指定して一時的にイベントの発生を中断させています。

次に、都道府県の入力規則を以下のように変更します。

=IF(C2="",Sheet2!$2:$99,INDIRECT("Sheet2!"&ADDRESS(2,MATCH(C2, Sheet2!$1:$1,0 )) & ":" & ADDRESS(99,MATCH(C2, Sheet2!$1:$1,0 ))))

上記の変更により、

• 都道府県の値が入力されていたら、その都道府県の市町村を表示
• 都道府県の値が入力されていなければ、全選択肢を表示

という動きになります。

自動入力と連動の完成品

上記作業により完成したドロップダウンリストの動きは以下の画像のようになります。

しっかりと連動できているので、ユーザーの入力の手間や誤入力を防ぐことができそうです。

一方で制限事項として以下が発生します。

「同じ市町村名があった場合に、県名の自動入力がバグる」

対策としては、市町村の選択肢を以下のようにするという感じでしょうか。

• 府中市(東京都)
• 府中市(広島県)

あとはVBAを使用する宿命として、アンドゥやリドゥが使えなくなるんですよね。

これはユーザビリティーがかなり下がるので悩ましいところです。