Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – マスタ更新ツール

Excel VBA
スポンサーリンク

ねらい：マスタを「手入力」から「差分反映の型」に変える

マスタ更新って、本当は一番慎重にやりたいところなのに、現場ではこうなりがちです。

新しいCSVを開く
古いマスタを開く
VLOOKUPやフィルタで差分を探す
コピペで上書きする（ときどきミスる）

ここで目指す「マスタ更新ツール」は、こういう状態から抜け出すための“型”です。

マスタは「Master」シートに1本持つ。
新しいデータは「Update」シートに読み込む。
キー列（ID）を基準にして、
　存在しないIDは「追加」
　存在するIDは「更新」
　（必要なら）マスタにだけ残っているIDは「削除候補」
をVBAで機械的に処理する。

この型を一度作っておけば、顧客マスタでも商品マスタでも、
「マスタ更新」はほぼ同じコードで回せるようになります。

設計の前提：マスタ側と更新側の“役割”をはっきり分ける

シート構成とキーの考え方

まず、シート構成を決めます。

Master シート
　現在運用中のマスタ。ここを書き換える。

Update シート
　システムや他部署からもらった「新しいデータ」。ここから反映する。

どちらのシートも、列構成は同じとします。

A列：ID（顧客ID・商品コードなど、一意なキー）
B列以降：名前・属性・金額など

マスタ更新の基本ルールはこうです。

Update にあるIDが Master にない → Master に「追加」
Update にあるIDが Master にもある → Master の該当行を「更新」
（必要なら）Master にあるが Update にない → 「削除候補」として別扱い

この「IDをキーにした突き合わせ」を、Dictionary＋配列で一気にやります。

コア部品：MasterをDictionaryに載せて、Updateをなめながら追加・更新する

マスタ更新のメインプロシージャ全体像

まずは、全体の流れを1本の Sub にまとめた形を見てください。

' ModMasterUpdate_Core.bas
Option Explicit

Public Sub RunMasterUpdate()
    Dim wsM As Worksheet, wsU As Worksheet
    Set wsM = ThisWorkbook.Worksheets("Master")
    Set wsU = ThisWorkbook.Worksheets("Update")
    
    Dim keyCol As Long
    keyCol = 1          ' ID列（A列）
    
    Dim headerRow As Long
    headerRow = 1       ' ヘッダ行
    
    Application.ScreenUpdating = False
    
    Call MasterUpdate_Apply(wsM, wsU, keyCol, headerRow)
    
    Application.ScreenUpdating = True
    
    MsgBox "マスタ更新が完了しました。", vbInformation
End Sub
VB

実際のロジックは MasterUpdate_Apply に切り出します。
ここでは、画面更新を止めてから処理し、終わったら戻す、という“お作法”も入れています。

MasterUpdate_Apply：追加・更新を一気にやる心臓部

Public Sub MasterUpdate_Apply(ByVal wsMaster As Worksheet, _
                              ByVal wsUpdate As Worksheet, _
                              ByVal keyCol As Long, _
                              ByVal headerRow As Long)
    Dim lastRowM As Long, lastRowU As Long, lastCol As Long
    
    lastRowM = wsMaster.Cells(wsMaster.Rows.Count, keyCol).End(xlUp).Row
    lastRowU = wsUpdate.Cells(wsUpdate.Rows.Count, keyCol).End(xlUp).Row
    lastCol = wsMaster.Cells(headerRow, wsMaster.Columns.Count).End(xlToLeft).Column
    
    If lastRowU <= headerRow Then
        MsgBox "Updateシートにデータ行がありません。", vbInformation
        Exit Sub
    End If
    
    Dim dictM As Object
    Set dictM = CreateObject("Scripting.Dictionary")
    dictM.CompareMode = 1
    
    Dim r As Long
    Dim key As String
    
    For r = headerRow + 1 To lastRowM
        key = CStr(wsMaster.Cells(r, keyCol).Value)
        If key <> "" Then
            If Not dictM.Exists(key) Then
                dictM.Add key, r
            End If
        End If
    Next
    
    Dim arrU As Variant
    arrU = wsUpdate.Range(wsUpdate.Cells(headerRow + 1, 1), wsUpdate.Cells(lastRowU, lastCol)).Value
    
    Dim addedCount As Long, updatedCount As Long
    addedCount = 0
    updatedCount = 0
    
    Dim nextRowM As Long
    nextRowM = IIf(lastRowM < headerRow + 1, headerRow + 1, lastRowM + 1)
    
    Dim i As Long
    For i = 1 To UBound(arrU, 1)
        key = CStr(arrU(i, keyCol))
        If key <> "" Then
            If dictM.Exists(key) Then
                Dim rowM As Long
                rowM = dictM(key)
                
                Dim c As Long
                For c = 1 To lastCol
                    wsMaster.Cells(rowM, c).Value = arrU(i, c)
                Next
                updatedCount = updatedCount + 1
            Else
                Dim c2 As Long
                For c2 = 1 To lastCol
                    wsMaster.Cells(nextRowM, c2).Value = arrU(i, c2)
                Next
                dictM.Add key, nextRowM
                nextRowM = nextRowM + 1
                addedCount = addedCount + 1
            End If
        End If
    Next
    
    wsMaster.Cells(headerRow, lastCol + 2).Value = "最終更新日時"
    wsMaster.Cells(headerRow + 1, lastCol + 2).Resize(nextRowM - headerRow - 1, 1).Value = Now
    
    MsgBox "更新：" & updatedCount & "件 / 追加：" & addedCount & "件", vbInformation
End Sub
VB

ここが「マスタ更新ツール」の核です。
重要なポイントを順にかみ砕きます。

Master の ID → 行番号を Dictionary に載せておく
　これで、「このIDはマスタの何行目か」が一発で分かるようになります。

Update 側は配列に読み込んでからループする
　セルを1つずつ読むより圧倒的に速くなります。

Update の1行ごとに
　IDが空ならスキップ
　IDが Master に存在する → その行を丸ごと上書き（更新）
　IDが Master に存在しない → Master の末尾に1行追加（追加）

追加・更新の件数をカウントして、最後にメッセージで出す
　「ちゃんと動いた感」が出るのと、ざっくりしたチェックにもなります。

最後に「最終更新日時」列を付けているのもポイントです。
いつこのマスタを更新したのかが一目で分かるようになります（もちろん不要なら外してOKです）。

例題：顧客マスタを「新しいCSV」から更新する

想定するマスタの構造

Master シート：

A列：CustomerID
B列：CustomerName
C列：Address
D列：Tel

Update シートにも、同じ列構成で新しいデータが入っているとします。
（Update は、システムから出力されたCSVを貼り付けたもの、というイメージ）

この状態で RunMasterUpdate を実行すると、

Update にある CustomerID が Master にもあれば、その行が丸ごと上書きされる。
Update にだけある CustomerID は、Master の末尾に新規行として追加される。
Master にだけ残っている CustomerID は、そのまま残る（削除はしない）。

という動きになります。

例えば、

Master に C001～C010
Update に C003～C012

という状態なら、

C001, C002 は Master に残る（Update にはないが削除はしない）
C003～C010 は Update の内容で上書きされる
C011, C012 は Master に新規追加される

という結果になります。

応用1：削除候補を洗い出す（Masterにだけ残っているID）

「UpdateにいないID」を別シートに出す

運用によっては、「UpdateにいないIDは、もう使われていないから削除候補にしたい」というケースもあります。
いきなり削除するのは怖いので、まずは「削除候補一覧」として別シートに出すのが現実的です。

MasterUpdate_Apply の後に、次のような処理を足すイメージです。

Public Sub ListDeleteCandidates(ByVal wsMaster As Worksheet, _
                                ByVal wsUpdate As Worksheet, _
                                ByVal keyCol As Long, _
                                ByVal headerRow As Long, _
                                ByVal outSheetName As String)
    Dim lastRowM As Long, lastRowU As Long
    lastRowM = wsMaster.Cells(wsMaster.Rows.Count, keyCol).End(xlUp).Row
    lastRowU = wsUpdate.Cells(wsUpdate.Rows.Count, keyCol).End(xlUp).Row
    
    Dim dictU As Object
    Set dictU = CreateObject("Scripting.Dictionary")
    dictU.CompareMode = 1
    
    Dim r As Long
    Dim key As String
    
    For r = headerRow + 1 To lastRowU
        key = CStr(wsUpdate.Cells(r, keyCol).Value)
        If key <> "" Then
            If Not dictU.Exists(key) Then
                dictU.Add key, True
            End If
        End If
    Next
    
    Dim wsOut As Worksheet
    On Error Resume Next
    Set wsOut = ThisWorkbook.Worksheets(outSheetName)
    On Error GoTo 0
    If wsOut Is Nothing Then
        Set wsOut = ThisWorkbook.Worksheets.Add
        wsOut.Name = outSheetName
    Else
        wsOut.Cells.Clear
    End If
    
    wsMaster.Rows(headerRow).Copy wsOut.Rows(1)
    Dim outRow As Long
    outRow = 2
    
    For r = headerRow + 1 To lastRowM
        key = CStr(wsMaster.Cells(r, keyCol).Value)
        If key <> "" Then
            If Not dictU.Exists(key) Then
                wsMaster.Rows(r).Copy wsOut.Rows(outRow)
                outRow = outRow + 1
            End If
        End If
    Next
    
    wsOut.Columns.AutoFit
    MsgBox "削除候補をシート「" & outSheetName & "」に出力しました。", vbInformation
End Sub
VB

これを RunMasterUpdate の最後に呼べば、

マスタ更新（追加・更新）
→ 削除候補一覧の出力

まで一気に流せます。

削除候補は、担当者が目で確認してから手動で削除する、という運用にすれば、
「間違って消した」がかなり防げます。

応用2：更新対象の列を絞る（全部は上書きしたくない場合）

「この列だけ更新したい」という現場のリアル

実務では、「名前や住所はマスタ側が正で、今回のUpdateでは金額だけ更新したい」みたいなケースもあります。
その場合、今のテンプレのように「行を丸ごと上書き」すると困ります。

そこで、「更新対象列の配列」を渡して、その列だけを上書きする版も用意できます。

更新部分だけ抜き出すと、こんなイメージです。

Public Sub MasterUpdate_Apply_Partial(ByVal wsMaster As Worksheet, _
                                      ByVal wsUpdate As Worksheet, _
                                      ByVal keyCol As Long, _
                                      ByVal headerRow As Long, _
                                      ByVal updateCols As Variant)
    ' Master側のDictionary作成までは同じなので省略
    
    ' ... dictM の作成まで同じと仮定
    
    Dim lastRowU As Long, lastCol As Long
    lastRowU = wsUpdate.Cells(wsUpdate.Rows.Count, keyCol).End(xlUp).Row
    lastCol = wsMaster.Cells(headerRow, wsMaster.Columns.Count).End(xlToLeft).Column
    
    Dim arrU As Variant
    arrU = wsUpdate.Range(wsUpdate.Cells(headerRow + 1, 1), wsUpdate.Cells(lastRowU, lastCol)).Value
    
    Dim i As Long
    Dim key As String
    Dim updatedCount As Long
    updatedCount = 0
    
    For i = 1 To UBound(arrU, 1)
        key = CStr(arrU(i, keyCol))
        If key <> "" Then
            If dictM.Exists(key) Then
                Dim rowM As Long
                rowM = dictM(key)
                
                Dim idx As Long
                For idx = LBound(updateCols) To UBound(updateCols)
                    Dim c As Long
                    c = updateCols(idx)
                    wsMaster.Cells(rowM, c).Value = arrU(i, c)
                Next
                updatedCount = updatedCount + 1
            End If
        End If
    Next
    
    MsgBox "部分更新：" & updatedCount & "件", vbInformation
End Sub
VB

呼び出し側では、例えば

Dim cols As Variant
cols = Array(4, 5)   ' D列とE列だけ更新したい、など
Call MasterUpdate_Apply_Partial(wsM, wsU, 1, 1, cols)
VB

のようにして、「どの列を更新対象にするか」を柔軟に変えられます。

ここでの重要ポイントは、「マスタのどの情報が“正”なのか」を業務として決めておくことです。
VBAは、そのルールを忠実に実行するだけです。

応用3：更新ログ（監査ログ）と組み合わせる

マスタ更新は、後から「誰がいつ何を変えたか」を問われやすい処理です。
以前作った監査ログテンプレと組み合わせると、かなり“実務っぽい”ツールになります。

例えば、更新ループの中で、

更新前の値を文字列にまとめる
更新後の値も文字列にまとめる
WriteAuditLog "MASTER_UPDATE", "Master", "ID=" & key, beforeStr, afterStr, "OK", ""

のように呼べば、「このIDのこの行が、こう変わった」という履歴を残せます。

全部の列をログに入れると重くなるので、

キー
重要な2～3列（名前・金額など）

くらいに絞ってログに残す、というバランスが現実的です。

重要ポイントの深掘り：マスタ更新で絶対に外したくないこと

「マスタの元データは必ずバックアップを取る」を徹底する

どれだけテストしても、「想定外のデータ」が来るとバグは起こり得ます。
マスタ更新ツールを回す前に、Master シート（またはブック）を丸ごとコピーしておくのは、ほぼ義務だと思ってください。

運用として、

更新前に「Master_yyyymmdd.xlsx」として保存しておく
もしくは Master シートを「Master_backup」としてコピーしておく

という一手間をルール化しておくと、精神的にもかなり楽になります。

キー列の空白・重複は「更新前にチェック」しておく

ID列（キー列）に空白や重複があると、マスタ更新ロジックは一気に不安定になります。

空白ID → スキップするしかない（どこに反映していいか分からない）
重複ID → どの行を更新すべきか曖昧になる

なので、マスタ更新ツールとセットで、

Update シートのキー列に空白や重複がないかをチェックする
Master シート側も、定期的に重複検査を回しておく

という“前提条件チェック”をテンプレ化しておくと、事故が減ります。

「マスタの正しさ」はVBAではなく“業務ルール”で担保する

マスタ更新ツールは、あくまで「Update の内容を Master に反映する機械」です。
Update の中身が間違っていれば、そのまま間違いを広げます。

だからこそ、

Update の元データはどこから来ているのか
誰がいつ作ったものなのか
どのタイミングでマスタに反映するのか

といった“業務の流れ”を整理した上で、
その流れの中に「マスタ更新ツール」を組み込む、という意識が大事です。

まとめ：マスタ更新も「Master／Update／キー／更新ルール」を型にしてしまえば怖くない

マスタ更新ツールの本質は、次の4つをはっきり決めて、それをVBAに落とすことです。

Master シート：どこを“正”とするか
Update シート：どこから新しい情報が来るか
キー列：何をもって「同じレコード」とみなすか
更新ルール：行ごと上書きか、一部列だけか、削除はどう扱うか

Dictionary＋配列を使った MasterUpdate_Apply を一度テンプレとして作っておけば、

顧客マスタ
商品マスタ
社員マスタ
コード表（部署コード・区分コードなど）

どんなマスタでも、シート名とキー列、更新ルールを変えるだけで流用できます。

スポンサーリンク
タイトルとURLをコピーしました