VBA 거래처별 시트 분리: 원본 행 번호까지 맞춰 9행 검산하기

Posted on 5월 7, 202610월 14, 2026 by 최지원

거래처별 시트가 세 개 생겨도 분리가 맞았다고 결론 내릴 수는 없습니다. 원본의 서로 다른 두 행이 같은 금액이면, 한 행을 누락하고 다른 행을 두 번 복사해도 총액이 맞기 때문입니다. 이번 작업의 완료 조건은 9개 원본 행이 각자의 결과 위치에 정확히 한 번씩 나타나는 것입니다.

예제의 회사와 거래는 모두 가상입니다. 원본과 올바른 분리 알고리즘은 유지하고, 결과를 승인할 때 확인할 순서를 새로 구성했습니다. Python으로 금액·행 배치·실패 조건을 검산했으며 Microsoft Excel에서 VBA를 컴파일하거나 실행하지 않았습니다.

원본 9행을 알파상사4행405000원·베타유통3행272000원·감마테크2행217000원으로 분리하고 원본 행번호를 보존하는 도해. 실제 앱 실행 화면이 아닌 설명 도해이며 Microsoft Excel·VBA·Outlook의 실행이나 성공을 확인한 증거가 아닙니다.

결과 시트와 전달 파일을 구분합니다

이 버튼이 만드는 것은 같은 통합 문서 안의 분리_알파상사 같은 결과 시트입니다. 거래처별 첨부파일을 만들거나 받는 사람을 선택하지 않습니다. 원본·검산·다른 거래처 결과가 남은 통합 문서 전체를 한 거래처에 전달하지 마세요. 다른 시트를 숨기는 것도 받는 사람별 파일 분리가 아닙니다.

이 예제는 거래처명으로 그룹을 만들며 앞뒤 공백을 정리해 비교합니다. 같은 표기의 상호라도 실제 거래처 코드나 수신처가 다를 수 있는 업무라면, 이름만으로 묶지 말고 입력과 분리 기준부터 따로 정하세요. 시트 이름 충돌 검사는 받는 사람을 식별하는 검사가 아닙니다.

원장의 행을 거래처별로 나누고 원본 행 번호까지 대조하려면 이 글의 시트 분리 절차를 사용합니다. 화면에서 한 거래처만 필터해도 이 코드는 숨김·필터 행을 모두 읽습니다.
이미 나눈 시트를 각각 XLSX로 저장하려면 거래처별 SaveAs 예제에서 파일 단위 검산과 배포 전 검토를 확인합니다. 그 코드는 Customer_ 접두사만 선택하므로 이 글의 분리_ 결과를 그대로 이어 내보내는 코드는 아닙니다. 두 매크로를 연결하려면 대상 시트·열·파일명 규칙부터 별도 사본에서 검토해야 합니다.
인쇄 모양을 확인해 전달하려면 Report 한 시트 PDF 예제를 봅니다. 받는 사람이 Excel 수식을 계속 계산해야 하는 업무인지도 먼저 정하세요.

먼저 894000원의 구성부터 고정합니다

practice-63.xlsx의 원본!A1:C10을 사용합니다. E열 안내는 입력 범위가 아닙니다.

워크시트 행	거래처	품목	금액
2	알파상사	USB 허브	120000
3	베타유통	HDMI 케이블	98000
4	알파상사	무선 키보드	85000
5	감마테크	모니터 거치대	125000
6	베타유통	USB 케이블	74000
7	알파상사	무선 마우스	90000
8	감마테크	노트북 받침대	92000
9	베타유통	멀티탭	100000
10	알파상사	키보드 받침대	110000

가상 예제의 알파상사는 원본 2·4·7·10행을 순서대로 받아 4행, 405000원입니다. 이 예제의 베타유통은 3·6·9행이므로 3행, 272000원이고 감마테크는 5·8행이므로 2행, 217000원입니다. 405000+272000+217000=894000이며 전체 데이터 행은 9개입니다. 결과 A:C는 원본 값, D열은 원본 워크시트의 행 번호입니다. D열을 결과 순번으로 바꾸면 대조 기준이 사라집니다.

같은 거래처의 두 품목이 각각 50000원인 반례도 ZIP 테스트에 있습니다. 이 가상 반례에서 [품목A, 품목B]를 [품목A, 품목A]로 잘못 복사한 결과는 여전히 2행·100000원입니다. 행 번호의 중복과 품목 대조에서만 오류가 드러납니다.

사본과 입력 검사

매크로 없는 XLSX를 customer-split-demo.xlsm 같은 별도 사본으로 저장하고, 그 파일에 split_by_customer.bas를 표준 모듈로 가져옵니다. 개인용 매크로 통합 문서에 넣으면 ThisWorkbook이 가리키는 대상이 달라집니다. 다음 전체 모듈을 복사하는 방법과 BAS 가져오기 중 한 가지를 사용하세요.

원본의 머리글은 거래처·품목·금액입니다. A:C 전체에서 마지막 행을 찾으므로 A10이 비었지만 B10:C10이 남아 있는 꼬리 행도 검사합니다. 거래처·품목은 비어 있지 않은 텍스트이고 금액은 실제 숫자여야 합니다. 숫자 문자열, 불리언, 빈 금액, 오류 셀과 병합 셀은 거부합니다. 금액의 음수·소수는 허용하는 코드이므로 이 자료를 정수 매출만 받는 검증기로 설명하지 않습니다.

텍스트의 제어 문자와 첫 글자 =, +, -, @, 작은따옴표도 거부합니다. 실제 상호가 여기에 해당하면 이름을 임의 변형하지 말고 입력 정책을 먼저 바꿔야 합니다. 숨김·필터 행도 읽으며 입력 상한 50000행은 예제 제한이지 속도 측정 결과가 아닙니다.

입력·결과 이름 충돌을 검사하고 시트 생성 뒤 복구 목록에 등록한 다음 이름을 지정하는 순서 도해. 실제 앱 실행 화면이 아닌 설명 도해이며 Microsoft Excel·VBA·Outlook의 실행이나 성공을 확인한 증거가 아닙니다.

결과 이름과 복구 목록을 따로 관리합니다

워크시트 이름은 31자를 넘을 수 없습니다. 이 코드는 분리_ 접두사를 붙이고 금지 문자를 밑줄로 치환한 뒤 길이를 자르고 끝의 작은따옴표를 제거합니다. 거래처 A/B와 A:B는 같은 결과 이름 분리_A_B가 되므로, 시트를 만들기 전에 중단합니다. 차트 시트까지 포함한 기존 Sheets 이름과도 충돌을 검사합니다.

Worksheets.Add로 만든 새 시트는 활성 시트가 됩니다. 그래서 활성 시트에 기대지 않고 반환된 output 객체를 사용합니다. 코드의 순서는 생성 → created.Add output → 이름 지정입니다. 이름 지정부터 하면 그 지점의 오류로 복구 목록에 없는 새 시트가 남을 수 있습니다.

Value2는 셀 값을 읽고 쓰는 Variant 속성입니다. 여기서는 값 복사가 목적이며 원본의 수식·서식·표 개체를 그대로 복제하지 않습니다. 원본 수식의 계산 상태나 날짜를 금액으로 잘못 넣은 경우까지 이 코드가 업무 의미를 판별해 주지는 않습니다.

실행할 전체 모듈

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

Attribute VB_Name = "CustomerSplit"
Option Explicit

Private Const MAX_DATA_ROWS As Long = 50000

Public Sub SplitByCustomer()
    SplitCore 0
End Sub

Public Sub CheckRollbackOnCopy()
    ' Run only in a separate practice copy with no result-name collisions.
    SplitCore 2
End Sub

Private Function SourceName() As String
    SourceName = ChrW(&HC6D0) & ChrW(&HBCF8)
End Function

Private Function CustomerHeader() As String
    CustomerHeader = ChrW(&HAC70) & ChrW(&HB798) & ChrW(&HCC98)
End Function

Private Function ProductHeader() As String
    ProductHeader = ChrW(&HD488) & ChrW(&HBAA9)
End Function

Private Function AmountHeader() As String
    AmountHeader = ChrW(&HAE08) & ChrW(&HC561)
End Function

Private Function RowHeader() As String
    RowHeader = ChrW(&HC6D0) & ChrW(&HBCF8) & ChrW(&HD589)
End Function

Private Function ResultPrefix() As String
    ResultPrefix = ChrW(&HBD84) & ChrW(&HB9AC) & "_"
End Function

Private Function NumericCell(ByVal value As Variant) As Boolean
    Select Case VarType(value)
        Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal
            NumericCell = True
        Case Else
            NumericCell = False
    End Select
End Function

Private Sub CheckText(ByVal value As Variant, ByVal address As String)
    Dim text As String, first As String, n As Long
    If VarType(value) <> vbString Then
        Err.Raise vbObjectError + 101, , "Expected text at " & address
    End If
    text = CStr(value)
    If Len(Trim$(text)) = 0 Then
        Err.Raise vbObjectError + 102, , "Empty text at " & address
    End If
    For n = 1 To Len(text)
        If AscW(Mid$(text, n, 1)) >= 0 And AscW(Mid$(text, n, 1)) < 32 Then
            Err.Raise vbObjectError + 103, , "Control character at " & address
        End If
    Next n
    first = Left$(LTrim$(text), 1)
    Select Case first
        Case "=", "+", "-", "@", "'"
            Err.Raise vbObjectError + 104, , "Formula-like text is outside this practice input policy: " & address
    End Select
End Sub

Private Function SafeName(ByVal customer As String) As String
    Dim result As String, bad As Variant, item As Variant
    result = ResultPrefix() & Trim$(customer)
    bad = Array("/", "\", "?", "*", ":", "[", "]")
    For Each item In bad
        result = Replace(result, CStr(item), "_")
    Next item
    result = Left$(result, 31)
    Do While Len(result) > 0
        If Right$(result, 1) <> "'" Then Exit Do
        result = Left$(result, Len(result) - 1)
    Loop
    If Len(result) = 0 Then Err.Raise vbObjectError + 105, , "Empty result sheet name"
    SafeName = result
End Function

Private Function ExistingSheet(ByVal book As Workbook, ByVal name As String) As Boolean
    Dim sheet As Object
    For Each sheet In book.Sheets
        If StrComp(sheet.Name, name, vbTextCompare) = 0 Then
            ExistingSheet = True
            Exit Function
        End If
    Next sheet
End Function

Private Sub SplitCore(ByVal failAfterCreate As Long)
    Dim book As Workbook, source As Worksheet, output As Worksheet, undoSheet As Worksheet
    Dim lastCell As Range, cell As Range, originalActive As Object
    Dim data As Variant, outputData As Variant, actual As Variant
    Dim names() As String, sheetNames() As String, seen() As Long
    Dim groups As Collection, rows As Collection, created As Collection
    Dim lastRow As Long, groupCount As Long, groupIndex As Long
    Dim r As Long, c As Long, g As Long, i As Long, j As Long, sourceRow As Long
    Dim customer As String, rawIndex As Double, failure As String, rollbackFailures As String
    Dim oldScreen As Boolean, oldEvents As Boolean, oldAlerts As Boolean
    Dim settingsSaved As Boolean, settingsChanged As Boolean, restoreErrors As Long
    Dim totalRows As Long, totalAmount As Double, expectedAmount As Double

    Set created = New Collection
    On Error GoTo Failed
    Set book = ThisWorkbook
    If book.ReadOnly Then Err.Raise vbObjectError + 110, , "Workbook is read-only"
    If book.ProtectStructure Then Err.Raise vbObjectError + 111, , "Workbook structure is protected"
    Set source = book.Worksheets(SourceName())
    Set lastCell = source.Range("A:C").Find(What:="*", After:=source.Range("A1"), _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
    If lastCell Is Nothing Then Err.Raise vbObjectError + 112, , "Source is empty"
    lastRow = lastCell.Row
    If lastRow < 2 Then Err.Raise vbObjectError + 113, , "No source data rows"
    If lastRow - 1 > MAX_DATA_ROWS Then
        Err.Raise vbObjectError + 114, , "Practice row limit exceeded"
    End If

    For Each cell In source.Range("A1:C" & lastRow).Cells
        If cell.MergeCells Then Err.Raise vbObjectError + 115, , "Merged source cell: " & cell.Address
        If IsError(cell.Value2) Then Err.Raise vbObjectError + 116, , "Error source cell: " & cell.Address
    Next cell
    data = source.Range("A1:C" & lastRow).Value2
    If CStr(data(1, 1)) <> CustomerHeader() Or CStr(data(1, 2)) <> ProductHeader() Or CStr(data(1, 3)) <> AmountHeader() Then
        Err.Raise vbObjectError + 117, , "Expected three source headers in A1:C1"
    End If

    Set groups = New Collection
    ReDim names(1 To lastRow - 1)
    ReDim sheetNames(1 To lastRow - 1)
    ReDim seen(1 To lastRow)
    For r = 2 To lastRow
        CheckText data(r, 1), "A" & r
        CheckText data(r, 2), "B" & r
        If Not NumericCell(data(r, 3)) Then
            Err.Raise vbObjectError + 118, , "Expected a numeric amount at C" & r
        End If
        expectedAmount = expectedAmount + CDbl(data(r, 3))
        customer = Trim$(CStr(data(r, 1)))
        groupIndex = 0
        For g = 1 To groupCount
            If StrComp(names(g), customer, vbTextCompare) = 0 Then
                groupIndex = g
                Exit For
            End If
        Next g
        If groupIndex = 0 Then
            groupCount = groupCount + 1
            groupIndex = groupCount
            names(groupIndex) = customer
            Set rows = New Collection
            groups.Add rows
        Else
            Set rows = groups(groupIndex)
        End If
        rows.Add r
    Next r

    For g = 1 To groupCount
        sheetNames(g) = SafeName(names(g))
        For j = 1 To g - 1
            If StrComp(sheetNames(j), sheetNames(g), vbTextCompare) = 0 Then
                Err.Raise vbObjectError + 119, , "Two groups map to the same sheet name: " & sheetNames(g)
            End If
        Next j
        If ExistingSheet(book, sheetNames(g)) Then
            Err.Raise vbObjectError + 120, , "Result sheet already exists: " & sheetNames(g)
        End If
    Next g

    Set originalActive = ActiveSheet
    oldScreen = Application.ScreenUpdating
    oldEvents = Application.EnableEvents
    oldAlerts = Application.DisplayAlerts
    settingsSaved = True
    settingsChanged = True
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    For g = 1 To groupCount
        Set rows = groups(g)
        Set output = book.Worksheets.Add(After:=book.Sheets(book.Sheets.Count))
        created.Add output
        output.Name = sheetNames(g)
        If failAfterCreate > 0 And created.Count = failAfterCreate Then
            Err.Raise vbObjectError + 121, , "Intentional practice error after sheet creation"
        End If
        ReDim outputData(1 To rows.Count + 1, 1 To 4)
        For c = 1 To 3
            outputData(1, c) = data(1, c)
        Next c
        outputData(1, 4) = RowHeader()
        For i = 1 To rows.Count
            sourceRow = CLng(rows(i))
            For c = 1 To 3
                outputData(i + 1, c) = data(sourceRow, c)
            Next c
            outputData(i + 1, 4) = sourceRow
        Next i
        output.Range("A1:B" & rows.Count + 1).NumberFormat = "@"
        output.Range("A1").Resize(rows.Count + 1, 4).Value2 = outputData
        output.Range("C2:C" & rows.Count + 1).NumberFormat = "#,##0.00"
        output.Range("D2:D" & rows.Count + 1).NumberFormat = "0"
        output.Range("A1:D1").Font.Bold = True
        output.Range("A:D").Columns.AutoFit
    Next g

    For g = 1 To groupCount
        Set output = created(g)
        Set rows = groups(g)
        actual = output.Range("A2").Resize(rows.Count, 4).Value2
        For Each cell In output.Range("A1").Resize(rows.Count + 1, 4).Cells
            If cell.HasFormula Then Err.Raise vbObjectError + 122, , "Unexpected output formula"
        Next cell
        For i = 1 To rows.Count
            If Not NumericCell(actual(i, 4)) Then Err.Raise vbObjectError + 123, , "Invalid source row reference"
            rawIndex = CDbl(actual(i, 4))
            If rawIndex < 2 Or rawIndex > lastRow Or rawIndex <> Fix(rawIndex) Then
                Err.Raise vbObjectError + 124, , "Source row reference is out of range"
            End If
            sourceRow = CLng(rawIndex)
            If seen(sourceRow) <> 0 Then Err.Raise vbObjectError + 125, , "Duplicated source row"
            If sourceRow <> CLng(rows(i)) Then Err.Raise vbObjectError + 126, , "Unexpected source row order"
            If StrComp(Trim$(CStr(data(sourceRow, 1))), names(g), vbTextCompare) <> 0 Then
                Err.Raise vbObjectError + 127, , "Source row is in the wrong group"
            End If
            For c = 1 To 2
                If VarType(actual(i, c)) <> vbString Then Err.Raise vbObjectError + 128, , "Output text type changed"
                If StrComp(CStr(actual(i, c)), CStr(data(sourceRow, c)), vbBinaryCompare) <> 0 Then
                    Err.Raise vbObjectError + 129, , "Output text differs from source"
                End If
            Next c
            If Not NumericCell(actual(i, 3)) Then Err.Raise vbObjectError + 130, , "Output amount type changed"
            If CDbl(actual(i, 3)) <> CDbl(data(sourceRow, 3)) Then
                Err.Raise vbObjectError + 131, , "Output amount differs from source"
            End If
            seen(sourceRow) = 1
            totalRows = totalRows + 1
            totalAmount = totalAmount + CDbl(actual(i, 3))
        Next i
    Next g
    For r = 2 To lastRow
        If seen(r) <> 1 Then Err.Raise vbObjectError + 132, , "Missing source row"
    Next r
    GoTo Cleanup

Failed:
    failure = Err.Description
    On Error Resume Next
    If created.Count > 0 Then
        Application.DisplayAlerts = False
        For i = created.Count To 1 Step -1
            Set undoSheet = created(i)
            Err.Clear
            undoSheet.Delete
            If Err.Number <> 0 Then
                rollbackFailures = rollbackFailures & undoSheet.Name & "; "
            End If
        Next i
    End If
    On Error GoTo 0

Cleanup:
    On Error Resume Next
    If settingsSaved And settingsChanged Then
        Err.Clear
        Application.DisplayAlerts = oldAlerts
        If Err.Number <> 0 Then restoreErrors = restoreErrors + 1
        Err.Clear
        Application.EnableEvents = oldEvents
        If Err.Number <> 0 Then restoreErrors = restoreErrors + 1
        Err.Clear
        Application.ScreenUpdating = oldScreen
        If Err.Number <> 0 Then restoreErrors = restoreErrors + 1
    End If
    If Not originalActive Is Nothing Then originalActive.Activate
    On Error GoTo 0
    If Len(failure) > 0 Then
        MsgBox "Stopped: " & failure & vbCrLf & _
            "Rollback failures (if any): " & rollbackFailures & vbCrLf & _
            "Application-setting restore errors: " & restoreErrors, vbExclamation
    Else
        MsgBox "Created sheets: " & groupCount & vbCrLf & _
            "Rows checked individually: " & totalRows & vbCrLf & _
            "Amount total (display only): " & Format$(totalAmount, "#,##0.00") & vbCrLf & _
            "Application-setting restore errors: " & restoreErrors, vbInformation
    End If
End Sub

시트 분리 사본에서 실행할 순서

practice-63.xlsx를 별도 customer-split-demo.xlsm 사본으로 저장합니다.
그 파일에 split_by_customer.bas를 표준 모듈로 가져오고 컴파일을 확인합니다.
SplitByCustomer를 실행합니다. 원본 행 번호·내용·그룹별 4/405000, 3/272000, 2/217000을 확인합니다.
검산!D6:E8에 실제값을 입력하되 원본 번호 대조를 생략하지 않습니다.
새 결과가 없는 다른 사본에서 CheckRollbackOnCopy의 고의 실패와 생성한 시트만 삭제되는지 확인합니다.
코드·통합 문서는 자동 저장되지 않습니다. Excel 실행은 아직 검증하지 않았습니다.

검증 결과와 Excel에서 남길 기록

독립 Python 검사는 XLSX의 실제 9행을 읽어 그룹별 원본 번호·합계를 구하고, 중복 행 반례와 이름 충돌을 거부하는 참조 검사를 실행합니다. 워크북의 검산!B6:C9도 4/405000, 3/272000, 2/217000, 9/894000입니다. 이것은 VBA 실행 기록이 아닙니다.

Excel 사본에서는 SplitByCustomer를 실행한 후 먼저 결과 D열의 원본 번호와 A:C를 비교합니다. 다음으로 데이터 행 수와 C열 합계를 검산!D6:E8에 입력합니다. 그 칸은 지금 비어 있습니다. 비교 수식의 “합계 일치”만으로 행별 검증을 대신하지 마세요. 원본을 늘리면 고정된 검산 수식 범위 A2:C10도 함께 확장해야 합니다. 파일은 자동 저장되지 않습니다.

품목A·품목B를 품목A 두 번으로 잘못 복사해도 2행100000원이 되는 반례와 행번호 대조·부분 실패 복구를 설명한 도해. 실제 앱 실행 화면이 아닌 설명 도해이며 Microsoft Excel·VBA·Outlook의 실행이나 성공을 확인한 증거가 아닙니다.

실패한 실행을 다시 누르기 전에

기존 결과가 있으면 덮어쓰지 않고 멈춥니다. 새 결과가 없는 별도 사본에서 CheckRollbackOnCopy를 실행하면 두 번째 시트를 만든 직후 오류가 나도록 설계되어 있습니다. 이때 이번 실행에서 만든 시트만 역순 삭제를 시도하고 원본·검산·기존 시트는 보존해야 합니다. 기존 결과 때문에 사전 검사에서 막혔다면 복구 코드가 시험된 것이 아닙니다.

삭제 또는 이벤트·화면 갱신·경고 표시 복원에 실패했다는 메시지는 남은 상태를 확인하라는 뜻입니다. 결과를 확정하거나 다시 실행하지 말고 남은 시트와 설정을 확인하거나 연습 사본을 폐기합니다. 참조 검사는 복구 목록의 소유권 규칙을 검산했지만 실제 Excel 오류·강제 종료까지 원자적으로 되돌린다는 보장은 없습니다.

적용 범위와 남은 확인

이 예제는 한 통합 문서의 값을 그룹별 새 시트로 분리합니다. 별도 파일 저장, 모든 언어의 상호 정규화, 대량 처리 성능은 검증 범위 밖입니다. 그룹 비교는 Trim과 실행 환경의 vbTextCompare를 사용합니다. Excel 컴파일, 실제 시트 생성·삭제, 설정 복원은 사용할 데스크톱 Excel에서 위 사본 절차로 확인해야 합니다.

공식 자료

Rename a worksheet | Microsoft Support

Worksheets.Add method (Excel)

Range.Value2 property (Excel)

출처 원문 수집일: S1 2026-09-24; S2 2026-09-24; S3 2026-09-24.

이어서 연습할 글

거래처별로 나누기 전에 여러 입력 파일을 합치는 작업은 VBA 폴더 XLSX 통합 예제에서 헤더와 실패 처리까지 따로 확인할 수 있습니다.

작성자 소개

최지원

엑셀·VBA·파이썬·AI 업무 자동화의 사용 절차와 실습 자료를 정리합니다.

WP 게시물 작성자의 글 보기