VBA Dictionary 키 정규화: 8개 입력의 병합과 이름 충돌 감사

Posted on 5월 31, 202610월 13, 2026 by 최지원

거래처가 같은지 판단하는 키와, 결과를 담을 시트 이름은 서로 다른 자료입니다. 서울/영업과 서울:영업을 같은 회사로 병합하면 잘못된 결과입니다. 반대로 두 키를 그대로 두더라도 /와 :를 _로 바꾼 이름은 같아집니다. 이 글의 작업은 그 두 문제를 행을 복사하기 전에 표로 드러내는 것입니다.

기존 8개 가상 키와 이름 배정 알고리즘을 유지했습니다. 독립 Python 검사로 예상 이름·집계를 대조했고 Excel/VBA는 컴파일·실행하지 않았습니다. 첨부 XLSX에는 입력과 정답만 있으며 매크로는 별도 BAS입니다.

설명용 도해: 여덟 가상 키의 공백·대소문자 병합과 서로 다른 키의 시트명 충돌을 구분한 설명 도해입니다. Python 참조 검산에 따른 가상 기대값이며 실제 Excel/VBA 실행 화면이 아닙니다.

8행의 목적부터 구분합니다

practice-70.xlsx의 키입력!A1:B9가 입력입니다. 마지막 행은 A열 ID로 찾으므로 모든 입력 행에 ID가 있어야 합니다. B열만 아래에 더 넣어도 처리 범위는 늘어나지 않습니다.

ID	입력 문자열	예상 판정
K01	`알파상사`	첫 그룹
K02	`알파상사`	K01에 병합
K03	`ALPHA`	첫 그룹
K04	`alpha`	K03에 병합
K05	`서울/영업`	새 그룹과 기본 이름
K06	`서울:영업`	다른 그룹, 이름에 `_2` 필요
K07	`동일접두어_123456789012345678901_A`	31자로 자른 기본 이름
K08	`동일접두어_123456789012345678901_B`	다른 그룹, 잘린 이름 충돌

K01은 길이 잘림 예제가 아닙니다. 길이 문제는 K07·K08에서 검사합니다. 정규화는 앞뒤 일반 공백을 지우고 연속 일반 공백을 하나로 줄입니다. 탭·줄바꿈·NBSP까지 없애는 함수가 아닙니다. vbTextCompare의 모든 언어권 정렬을 Python이 흉내 냈다고 주장하지 않으며, 이번 대소문자 검산은 ASCII ALPHA 두 값으로 한정합니다.

병합은 Dictionary, 이름은 별도 목록으로

Dictionary는 키와 항목의 쌍을 저장합니다. groups에는 정규화 키와 최종 이름을 넣고 usedNames에는 이미 이번 계획에서 배정한 이름을 넣습니다. 키가 이미 들어 있는 Dictionary의 CompareMode를 바꾸면 오류가 납니다. 그래서 두 Dictionary를 만들자마자 비교 모드를 설정합니다.

워크시트 탭 이름은 31자를 넘을 수 없습니다. 이 코드의 이름 정제는 분리_를 붙이고 :, 역슬래시, /, ?, *, [, ]를 밑줄로 치환한 뒤 자르는 방식입니다. 충돌하면 접미사를 붙일 공간만큼 본문을 다시 줄입니다. 예를 들어 K08의 예상 최종 이름은 분리_동일접두어_12345678901234567890_2입니다.

설명용 도해: 서울/영업과 서울:영업의 키는 유지하면서 같은 기본 이름에 접미사 _2를 붙이는 이름 계획 기대값 도해입니다. Python 참조 검산에 따른 가상 기대값이며 실제 Excel/VBA 실행 화면이 아닙니다.

쓰기 대상과 적용 범위: 키검산 한 장

매크로는 키검산을 만들거나 그 시트 전체를 지웁니다. 그 이름의 업무용 시트가 있는 통합 문서에서 실행하면 안 됩니다. 새 사본에만 실행하며 예상키검산과 검산은 정답·기록용으로 남겨 둡니다. 그룹별 시트는 생성하지 않습니다.

이름 계획은 기존 통합 문서의 시트·차트 이름과 비교하지 않습니다. 끝 작은따옴표 등 모든 이름 금지 조건을 검증하는 함수도 아닙니다. 따라서 “충돌 2건 해결”은 이 여덟 입력의 계획 내부 결과입니다. 실제 시트 생성 단계에서는 기존 이름·모든 Excel 이름 조건을 다시 검사해야 합니다.

화면 갱신과 이벤트는 시작 값을 기억했다가 종료 경로에서 그 값으로 복원합니다. 입력이 잘못돼 일부 감사 행만 써진 경우 그 표를 자동 되돌리지 않습니다. 결과를 읽을 때 완료 메시지와 모든 행을 함께 확인해야 합니다.

전체 모듈

BAS는 UTF-8로 저장했습니다. Windows VBA 편집기로 가져왔을 때 한글 시트명·메시지가 깨지면 실행하지 않습니다. UTF-8로 연 원문의 Attribute 메타데이터 줄을 제외하고 새 표준 모듈에 붙여 넣은 뒤 키입력·키검산 문자열을 확인하세요. 가져오기와 붙여넣기 중 하나만 사용합니다.

첫 Attribute VB_Name 줄은 BAS 가져오기용 메타데이터입니다. BAS를 가져올 때는 그대로 두고, 코드 창에 직접 붙여넣을 때는 이 첫 줄만 제외합니다.

Attribute VB_Name = "DictionaryKeyAudit"
Option Explicit

Private Const INPUT_SHEET As String = "키입력"
Private Const AUDIT_SHEET As String = "키검산"
Private Const OUTPUT_PREFIX As String = "분리_"

Public Sub AuditDictionaryKeys()
    Dim inputWs As Worksheet, auditWs As Worksheet
    Dim groups As Object, usedNames As Object
    Dim lastRow As Long, rowNo As Long, auditRow As Long
    Dim rawKey As String, normalizedKey As String
    Dim baseName As String, finalName As String, decision As String
    Dim mergedCount As Long, collisionCount As Long, renamed As Boolean
    Dim oldScreen As Boolean, oldEvents As Boolean

    oldScreen = Application.ScreenUpdating
    oldEvents = Application.EnableEvents
    On Error GoTo Fail
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Set inputWs = ThisWorkbook.Worksheets(INPUT_SHEET)
    lastRow = inputWs.Cells(inputWs.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Err.Raise vbObjectError + 701, , "키입력 시트에 검사할 행이 없습니다."

    Set groups = CreateObject("Scripting.Dictionary")
    groups.CompareMode = vbTextCompare
    Set usedNames = CreateObject("Scripting.Dictionary")
    usedNames.CompareMode = vbTextCompare

    Set auditWs = GetOrCreateSheet(AUDIT_SHEET)
    auditWs.Cells.Clear
    auditWs.Range("A1:F1").Value = Array("ID", "원본 키", "정규화 키", "기본 시트명", "최종 시트명", "판정")
    auditRow = 2

    For rowNo = 2 To lastRow
        rawKey = CStr(inputWs.Cells(rowNo, "B").Value2)
        normalizedKey = NormalizeKey(rawKey)
        If Len(normalizedKey) = 0 Then
            auditWs.Cells(auditRow, "A").Value = inputWs.Cells(rowNo, "A").Value2
            auditWs.Cells(auditRow, "B").Value = rawKey
            auditWs.Cells(auditRow, "F").Value = "빈 키·처리 보류"
        Else
            baseName = SafeSheetName(OUTPUT_PREFIX & normalizedKey)
            If groups.Exists(normalizedKey) Then
                finalName = CStr(groups(normalizedKey))
                decision = "기존 키에 병합"
                mergedCount = mergedCount + 1
            Else
                renamed = False
                finalName = MakeUniqueName(baseName, usedNames, renamed)
                groups.Add normalizedKey, finalName
                usedNames.Add finalName, True
                If renamed Then
                    decision = "새 키·이름 충돌 해결"
                    collisionCount = collisionCount + 1
                Else
                    decision = "새 키"
                End If
            End If
            auditWs.Cells(auditRow, "A").Value = inputWs.Cells(rowNo, "A").Value2
            auditWs.Cells(auditRow, "B").Value = rawKey
            auditWs.Cells(auditRow, "C").Value = normalizedKey
            auditWs.Cells(auditRow, "D").Value = baseName
            auditWs.Cells(auditRow, "E").Value = finalName
            auditWs.Cells(auditRow, "F").Value = decision
        End If
        auditRow = auditRow + 1
    Next rowNo

    auditWs.Range("H1:I5").ClearContents
    auditWs.Range("H1").Value = "입력 행": auditWs.Range("I1").Value = lastRow - 1
    auditWs.Range("H2").Value = "고유 키": auditWs.Range("I2").Value = groups.Count
    auditWs.Range("H3").Value = "최종 시트명": auditWs.Range("I3").Value = usedNames.Count
    auditWs.Range("H4").Value = "병합 행": auditWs.Range("I4").Value = mergedCount
    auditWs.Range("H5").Value = "시트명 충돌": auditWs.Range("I5").Value = collisionCount
    auditWs.Columns("A:I").AutoFit
    MsgBox "검산 완료: 고유 키 " & groups.Count & "개, 병합 " & mergedCount & _
           "건, 시트명 충돌 " & collisionCount & "건", vbInformation

CleanExit:
    Application.EnableEvents = oldEvents
    Application.ScreenUpdating = oldScreen
    Exit Sub
Fail:
    MsgBox "키 검산 중단 " & Err.Number & ": " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

Private Function NormalizeKey(ByVal value As String) As String
    value = Trim$(value)
    Do While InStr(value, "  ") > 0
        value = Replace(value, "  ", " ")
    Loop
    NormalizeKey = value
End Function

Private Function SafeSheetName(ByVal value As String) As String
    Dim bad As Variant, token As Variant
    bad = Array(":", "\", "/", "?", "*", "[", "]")
    For Each token In bad
        value = Replace(value, CStr(token), "_")
    Next token
    value = Trim$(value)
    If Len(value) = 0 Then value = "빈값"
    SafeSheetName = Left$(value, 31)
End Function

Private Function MakeUniqueName(ByVal baseName As String, ByVal usedNames As Object, _
                                ByRef renamed As Boolean) As String
    Dim candidate As String, suffix As String, suffixNo As Long
    candidate = baseName
    suffixNo = 1
    renamed = False
    Do While usedNames.Exists(candidate)
        suffixNo = suffixNo + 1
        suffix = "_" & CStr(suffixNo)
        candidate = Left$(baseName, 31 - Len(suffix)) & suffix
        renamed = True
    Loop
    MakeUniqueName = candidate
End Function

Private Function GetOrCreateSheet(ByVal sheetName As String) As Worksheet
    On Error Resume Next
    Set GetOrCreateSheet = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
    If GetOrCreateSheet Is Nothing Then
        Set GetOrCreateSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        GetOrCreateSheet.Name = sheetName
    End If
End Function

키 감사 사본에서 확인하는 순서

practice-70.xlsx를 새 dictionary-key-audit.xlsm 사본으로 저장합니다.
키입력 A1:B9와 키검산이라는 업무용 시트가 없는지 확인합니다.
DictionaryKeyAudit.bas를 가져오거나 UTF-8 원문을 표준 모듈에 붙이고 컴파일을 확인합니다.
AuditDictionaryKeys를 실행하고 I1:I5가 8, 6, 6, 2, 2인지 확인합니다.
A2:F9를 예상키검산과 대조하고 실제 집계를 검산 C열에 입력합니다.
빈 키 테스트는 별도 사본에서 B2를 지운 뒤 실행하고 보류 표시를 확인합니다.

검증 결과: 개수와 행별 이름을 함께 봅니다

가상 입력의 예상 요약은 키검산!I1:I5에서 순서대로 8, 6, 6, 2, 2입니다. 고유 그룹 수와 최종 이름 수가 모두 6이어야 합니다. 예상키검산!A2:F9는 전체 정답이므로, 집계만 맞고 K06이 K05로 병합되는 오류를 놓치지 않게 행별로 대조합니다.

Python 검사는 8행의 각 열과 이름 길이·유일성, 빈 키의 보류, 공백 병합을 확인했습니다. 이 결과는 VBA 실행이 아닙니다. Excel 실제 집계는 검산!C2:C5에 직접 입력하도록 비워 두었습니다.

설명용 도해: 8·6·6·2·2 집계와 긴 이름의 29자 접미사 처리 및 실제 실행 전 주의사항을 보여 주는 기대값 도해입니다. Python 참조 검산에 따른 가상 기대값이며 실제 Excel/VBA 실행 화면이 아닙니다.

실패와 복구의 경계

키입력 시트를 못 찾으면 시트 이름을 고치고 새 사본에서 실행합니다. 빈 키는 해당 행의 F열에 보류로 표시되며 그룹 수에 포함되지 않습니다. 원본을 확인해 키를 입력한 뒤 다시 감사합니다. CStr로 바꿀 수 없는 오류 셀은 실행을 중단시킬 수 있으므로, ID·키 열의 오류부터 해결합니다.

이름 지정이나 쓰기 중 실패하면 만들어진 키검산 또는 일부 결과가 남을 수 있습니다. 자동 삭제·원자적 복구를 약속하지 않습니다. 해당 사본을 완료 결과로 사용하지 말고 복원용 XLSX부터 다시 시작하세요. 실제 거래 분리나 파일 저장, 처리 속도 측정은 이 모듈의 역할이 아닙니다.