Copilotで列番号を動的管理｜階層連動VBAの実務改善 | 40代シングルマザー派遣社員。正社員に転職するか悩む。

1 前回コードの問題点
2 今回やること
3 実際のCopilotとのやり取り
- 3.1 利用者 → Copilot
- 3.2 Copilot → 利用者
4 この修正の本質
5 なぜDictionaryを使うのか
6 実務でのインパクト
7 次回やること
8 まとめ
9 リンク

前回コードの問題点

前回は

階層連動
条件付きユニーク化
結合条件での抽出

ここまで完成しました。

正直、この時点で「動く仕組み」はできています。

ただし、実務ではここで必ず問題が出ます。

それが列に依存しすぎているです。

例えば今回のコードは

D列＝調査票名
E列＝項目名
F列＝品目名
G列＝アイテム名

と「固定」されています。

一見問題なさそうですが、実務ではこうなります。

列の順番が変わる
新しい列が追加される
レイアウトが変更される

この瞬間に、全部壊れます。これが現場で一番多いトラブルです。

なので今回は列番号を動的に管理していきます。

今回取り扱うデータも経済産業省の公開データ「生産動態統計年報（2024年）」です。

今回やること

やることはシンプルです。

Dictionaryで項目名 → 列番号を管理
列番号のハードコーディングを廃止
可読性を維持したまま柔軟化

この一手で「壊れにくいコード」になります。

実際のCopilotとのやり取り

利用者 → Copilot

既存の階層連動コンボボックスのコードを改善してください。

■修正内容
・列番号（D,E,F,G）を固定せず、項目名から動的に取得する
・1行目のヘッダーから列番号を特定する
・Dictionaryで「項目名 → 列番号」を管理
・既存のロジック（階層連動・ユニーク化）は維持

■対象項目
・調査票名
・項目名
・品目名
・アイテム名

■その他
・可読性重視でコメント付き
・コードは省略せず全文提示

Copilot → 利用者

以下に、列番号を動的に管理するよう改善した完全版コードを提示します。

Option Explicit
'---------------------------------------------
' 列名 → 列番号 を保持する Dictionary
'---------------------------------------------
Private ColIndex As Object   'Dictionary

'---------------------------------------------
' フォーム初期化：列番号取得 → 調査票名のみロード
'---------------------------------------------
Private Sub UserForm_Initialize()

    '列番号をヘッダーから取得
    Set ColIndex = CreateObject("Scripting.Dictionary")
    Call InitColumnIndex

    '最上位の調査票名をロード
    Load調査票名
End Sub

'---------------------------------------------
' ヘッダー行（1行目）から列番号を取得
'---------------------------------------------
Private Sub InitColumnIndex()
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim i As Long
    Dim header As String

    Set ws = ThisWorkbook.Worksheets("data")
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    '1行目のヘッダーを走査して Dictionary に登録
    For i = 1 To lastCol
        header = Trim(ws.Cells(1, i).Value)
        If header <> "" Then
            If Not ColIndex.Exists(header) Then
                ColIndex.Add header, i
            End If
        End If
    Next i
End Sub

'---------------------------------------------
' 調査票名変更 → 項目名を再ロード
'---------------------------------------------
Private Sub cmb調査票名_Change()
    cmb項目名.Clear
    cmb品目名.Clear
    cmbアイテム名.Clear

    If cmb調査票名.ListIndex >= 0 Then
        Load項目名 cmb調査票名.Value
    End If
End Sub

'---------------------------------------------
' 項目名変更 → 品目名を再ロード
'---------------------------------------------
Private Sub cmb項目名_Change()
    cmb品目名.Clear
    cmbアイテム名.Clear

    If cmb調査票名.ListIndex >= 0 And cmb項目名.ListIndex >= 0 Then
        Load品目名 cmb調査票名.Value, cmb項目名.Value
    End If
End Sub

'---------------------------------------------
' 品目名変更 → アイテム名を再ロード
'---------------------------------------------
Private Sub cmb品目名_Change()
    cmbアイテム名.Clear

    If cmb調査票名.ListIndex >= 0 _
       And cmb項目名.ListIndex >= 0 _
       And cmb品目名.ListIndex >= 0 Then

        Loadアイテム名 cmb調査票名.Value, cmb項目名.Value, cmb品目名.Value
    End If
End Sub

'===========================================================
'  調査票名のロード（最上位）
'===========================================================
Private Sub Load調査票名()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dic As Object
    Dim i As Long
    Dim col調査票 As Long
    Dim val As String

    Set ws = ThisWorkbook.Worksheets("data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set dic = CreateObject("Scripting.Dictionary")

    '列番号を Dictionary から取得
    col調査票 = ColIndex("調査票名")

    '調査票名をユニーク化
    For i = 2 To lastRow
        val = Trim(ws.Cells(i, col調査票).Value)
        If val <> "" Then
            If Not dic.Exists(val) Then dic.Add val, True
        End If
    Next i

    cmb調査票名.List = dic.Keys
End Sub

'===========================================================
'  項目名のロード（調査票名 → 項目名）
'===========================================================
Private Sub Load項目名(ByVal 調査票 As String)
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dic As Object
    Dim i As Long
    Dim col調査票 As Long, col項目 As Long
    Dim val As String

    Set ws = ThisWorkbook.Worksheets("data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set dic = CreateObject("Scripting.Dictionary")

    col調査票 = ColIndex("調査票名")
    col項目 = ColIndex("項目名")

    For i = 2 To lastRow
        If Trim(ws.Cells(i, col調査票).Value) = 調査票 Then
            val = Trim(ws.Cells(i, col項目).Value)
            If val <> "" Then
                If Not dic.Exists(val) Then dic.Add val, True
            End If
        End If
    Next i

    cmb項目名.List = dic.Keys
End Sub

'===========================================================
'  品目名のロード（調査票名 × 項目名 → 品目名）
'===========================================================
Private Sub Load品目名(ByVal 調査票 As String, ByVal 項目 As String)
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dic As Object
    Dim i As Long
    Dim col調査票 As Long, col項目 As Long, col品目 As Long
    Dim val As String

    Set ws = ThisWorkbook.Worksheets("data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set dic = CreateObject("Scripting.Dictionary")

    col調査票 = ColIndex("調査票名")
    col項目 = ColIndex("項目名")
    col品目 = ColIndex("品目名")

    For i = 2 To lastRow
        If Trim(ws.Cells(i, col調査票).Value) = 調査票 _
           And Trim(ws.Cells(i, col項目).Value) = 項目 Then

            val = Trim(ws.Cells(i, col品目).Value)
            If val <> "" Then
                If Not dic.Exists(val) Then dic.Add val, True
            End If
        End If
    Next i

    cmb品目名.List = dic.Keys
End Sub

'===========================================================
'  アイテム名のロード（調査票名 × 項目名 × 品目名 → アイテム名）
'===========================================================
Private Sub Loadアイテム名(ByVal 調査票 As String, _
                           ByVal 項目 As String, _
                           ByVal 品目 As String)

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dic As Object
    Dim i As Long
    Dim col調査票 As Long, col項目 As Long, col品目 As Long, colアイテム As Long
    Dim val As String

    Set ws = ThisWorkbook.Worksheets("data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set dic = CreateObject("Scripting.Dictionary")

    col調査票 = ColIndex("調査票名")
    col項目 = ColIndex("項目名")
    col品目 = ColIndex("品目名")
    colアイテム = ColIndex("アイテム名")

    For i = 2 To lastRow
        If Trim(ws.Cells(i, col調査票).Value) = 調査票 _
           And Trim(ws.Cells(i, col項目).Value) = 項目 _
           And Trim(ws.Cells(i, col品目).Value) = 品目 Then

            val = Trim(ws.Cells(i, colアイテム).Value)
            If val <> "" Then
                If Not dic.Exists(val) Then dic.Add val, True
            End If
        End If
    Next i

    cmbアイテム名.List = dic.Keys
End Sub

この修正の本質

ここが一番重要です。

今回の本質は、列依存を排除したです。

今までは「ws.Cells(i, “D”)」のように書いていました。

これは「列が固定されている前提」です。

今回からは「colMap(“調査票名”)」

つまり、意味で列を取得しています。

これにより

列が移動しても壊れない
列が追加されても対応できる
メンテナンスが圧倒的に楽

になります。

なぜDictionaryを使うのか

ここも重要です。

Dictionaryは

キーで高速アクセスできる
構造がシンプル
拡張しやすい

今回のように項目名 → 列番号の管理には最適です。

実務では「名前で管理する」これが基本です。

実務でのインパクト

この修正、かなり効きます。

Excelレイアウト変更に強くなる
修正コストが激減する
他の人が見ても理解しやすい

そして何より「長く使えるコードになる」ここが大きいです。

次回やること

次回以降

並び順の統一
UI改善（選択してください）
高速化（配列化）

を追加していきます。

ここまでやると、完全に業務アプリレベルになります。

まとめ

今回は列番号の動的管理を追加しました。

やっていることはシンプルですが、実務ではかなり重要な改善です。

Excelは、構造が変わる前提で作る必要があります。

今回のように、列を「番号」ではなく「意味」で扱う。

これができるようになると、一気に実務レベルに入ります。

そして今回もポイントは同じです。

自分でコードを書いていない

仕様を整理
プロンプトを書く

これだけです。

ただし、ここからは「改善力」が求められます。

■■■スポンサーリンク■■■

リンク

■■■スポンサーリンク■■■