VBA 피벗 자동 생성: 합계 600과 비어 있는 교차 셀을 따로 검산하기

Posted on 5월 24, 202610월 6, 2026 by 최지원

총합 600이 맞아도 지점과 월을 바꿔 넣은 피벗은 틀릴 수 있습니다. 이번 실습은 세 행을 새 통합 문서에 복사하고 총합·지점별·월별·입력이 없는 교차 셀을 각각 확인합니다. 기존 보고서부터 지우거나 같은 시트 위에 다시 만드는 방식은 사용하지 않습니다.

가상 입력과 올바른 기존 모듈을 보존했습니다. Python으로 집계를 독립 검산하고 첨부 XLSX 수식 값을 확인했습니다. Excel PivotCache 생성·VBA 컴파일·실행은 하지 않았으며 첨부 파일 자체에 피벗 개체도 없습니다.

가상 세 행의 총합 600과 지점·월별 교차표에서 입력 없는 B의 2월을 구분한 설명 도해. 가상 입력과 기대값을 설명한 그림이며 실제 Excel 실행 화면이나 실행 성공 증거가 아닙니다.

세 행을 원본으로 고정합니다

Data 행	Branch	Month	Sales
2	A	2026-01	100
3	A	2026-02	200
4	B	2026-01	300

A1:C1은 Branch, Month, Sales입니다. Month는 날짜 일련번호가 아닌 텍스트이고 Sales는 숫자입니다. 화면에 300으로 보여도 문자열이면 거부합니다. Branch·Month의 앞뒤 일반 공백은 잘라 쓰며 빈 값은 중단합니다. Month가 유효한 연월인지까지 검사하지는 않으므로 이 실습은 표의 두 문자열로 제한합니다.

코드는 A:C 중 가장 아래 값을 찾아 범위를 정합니다. A5가 비어 있고 B5:C5만 남아 있어도 마지막 행 검사에 포함돼 오류가 드러납니다. 중간 빈 행·합계 행·설명은 입력 표 안에 넣지 않습니다. 수식 셀을 넣으면 계산된 Value2를 읽으므로 계산이 끝나지 않은 원본을 자동으로 고쳐 주지는 않습니다.

새 보고서에 입력 사본을 먼저 남깁니다

PivotCaches.Create는 새 PivotCache를 만듭니다. 코드에서는 새 통합 문서의 Snapshot에 입력값을 복사하고, 그 범위를 통합 문서·시트·R1C1 주소가 포함된 문자열로 전달합니다. CreatePivotTable의 출력 범위는 해당 PivotCache를 가진 통합 문서 안에 있어야 합니다. 따라서 Pivot 시트도 같은 새 통합 문서에 둡니다.

AddDataField의 Function 인수는 추가한 값 필드의 집계 함수를 지정합니다. Sales를 TotalSales라는 캡션과 xlSum으로 등록합니다. 원본의 Sales가 텍스트여서 합계 대신 개수가 생기는 상황을 입력 검사에서 먼저 막는 구조입니다.

입력 검사 후 원본을 보존하고 새 Snapshot과 Pivot을 만드는 설명 도해. 가상 입력과 기대값을 설명한 그림이며 실제 Excel 실행 화면이나 실행 성공 증거가 아닙니다.

전체 생성 코드

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

Attribute VB_Name = "PivotPractice"
Option Explicit

Public Sub BuildSalesPivot()
    Dim src As Worksheet, output As Workbook
    Dim snap As Worksheet, dst As Worksheet
    Dim cache As PivotCache, pivot As PivotTable, totalField As PivotField
    Dim data As Variant, lastRow As Long, col As Long, candidate As Long
    Dim r As Long, expected As Double, actual As Double
    Dim oldScreen As Boolean, oldEvents As Boolean, errorText As String
    oldScreen = Application.ScreenUpdating
    oldEvents = Application.EnableEvents
    On Error GoTo Failed
    Set src = ThisWorkbook.Worksheets("Data")
    For col = 1 To 3
        candidate = src.Cells(src.Rows.Count, col).End(xlUp).Row
        If candidate > lastRow Then lastRow = candidate
    Next col
    If lastRow < 2 Or lastRow > 10001 Then
        Err.Raise vbObjectError + 801, , "Use 1 to 10000 input rows for this practice."
    End If
    data = src.Range("A1:C" & lastRow).Value2
    If CStr(data(1, 1)) <> "Branch" Or CStr(data(1, 2)) <> "Month" _
       Or CStr(data(1, 3)) <> "Sales" Then
        Err.Raise vbObjectError + 802, , "Headers must be Branch, Month, Sales."
    End If
    For r = 2 To UBound(data, 1)
        For col = 1 To 2
            If VarType(data(r, col)) <> vbString Then
                Err.Raise vbObjectError + 803, , "Branch and Month must be text. Row " & r
            End If
            data(r, col) = Trim$(data(r, col))
            If Len(data(r, col)) = 0 Then
                Err.Raise vbObjectError + 804, , "Blank Branch or Month. Row " & r
            End If
        Next col
        Select Case VarType(data(r, 3))
            Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal
                expected = expected + CDbl(data(r, 3))
            Case Else
                Err.Raise vbObjectError + 805, , "Sales must be a number, not text. Row " & r
        End Select
    Next r

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set output = Workbooks.Add(xlWBATWorksheet)
    Set snap = output.Worksheets(1)
    snap.Name = "Snapshot"
    snap.Range("A1:B" & lastRow).NumberFormat = "@"
    snap.Range("A1:C" & lastRow).Value2 = data
    snap.Range("C2:C" & lastRow).NumberFormat = "#,##0.00"
    Set dst = output.Worksheets.Add(After:=snap)
    dst.Name = "Pivot"
    Set cache = output.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=snap.Range("A1:C" & lastRow).Address( _
            RowAbsolute:=True, ColumnAbsolute:=True, _
            ReferenceStyle:=xlR1C1, External:=True))
    Set pivot = cache.CreatePivotTable( _
        TableDestination:=dst.Range("A3"), TableName:="SalesPivot")
    With pivot
        .PivotFields("Branch").Orientation = xlRowField
        .PivotFields("Branch").Position = 1
        .PivotFields("Month").Orientation = xlColumnField
        .PivotFields("Month").Position = 1
        .RowGrand = True
        .ColumnGrand = True
        Set totalField = .AddDataField(.PivotFields("Sales"), "TotalSales", xlSum)
        totalField.NumberFormat = "#,##0.00"
        .RefreshTable
    End With
    actual = CDbl(pivot.GetPivotData("TotalSales").Value2)
    If Abs(actual - expected) > 0.000001 * (1 + Abs(expected)) Then
        Err.Raise vbObjectError + 806, , "Grand total differs from captured input."
    End If
    snap.Columns("A:C").AutoFit
    dst.UsedRange.Columns.AutoFit
    dst.Activate
    Application.EnableEvents = oldEvents
    Application.ScreenUpdating = oldScreen
    MsgBox "New report is open. Grand total: " & CStr(actual) & _
           ". Check the branch totals and save it yourself.", vbInformation
    Exit Sub

Failed:
    errorText = Err.Description
    On Error Resume Next
    If Not output Is Nothing Then output.Close SaveChanges:=False
    Application.EnableEvents = oldEvents
    Application.ScreenUpdating = oldScreen
    On Error GoTo 0
    MsgBox "No existing report was replaced. " & errorText, vbExclamation
End Sub

새 피벗을 확인하는 순서

practice-80.xlsx를 PivotPractice.xlsm 사본으로 저장하고 Data A1:C4의 자료형을 확인합니다.
PivotPractice.bas를 해당 파일에 가져와 컴파일을 확인합니다.
BuildSalesPivot을 실행하고 새 통합 문서의 Snapshot 세 행을 원본과 대조합니다.
Pivot의 A/B 각 300, 1월 400, 2월 200, 전체 600과 B 2월의 빈 교차 셀을 확인합니다.
실제 관찰값을 검산 E열에 입력하고 필요한 결과만 직접 저장합니다.
원본 입력을 바꾼 경우 예전 Snapshot이 아닌 새 실행의 출력인지 확인합니다.

검증 결과: 자동 총합 검사 뒤 수동 교차 검사

구분	2026-01	2026-02	합계
A	100	200	300
B	300	입력 없음	300
전체	400	200	600

B의 2월 거래는 아예 없습니다. 검산 워크북의 산술 표는 합계를 더하기 위해 0을 넣었지만, 새 피벗의 기본 빈 교차 셀 표시를 숫자 0과 동일한 입력으로 설명하지 않습니다. 값 필드·레이아웃을 확인하면서 빈 칸을 관찰하세요.

코드 안의 GetPivotData는 전체 TotalSales를 기대 총합과 오차 한도 안에서 비교합니다. 지점·월별 네 교차 항목을 모두 자동 대조하는 코드는 아닙니다. 예를 들어 B의 300을 A로 잘못 이동시켜도 전체 600은 유지되므로 위 표와 Snapshot 행을 별도로 확인해야 합니다.

Python 검사는 실제 XLSX 입력과 총합 600, 지점 300·300, 월 400·200을 대조합니다. 금액을 바꿔 같은 총합을 만드는 반례도 별도로 검산했습니다. 이는 피벗 엔진 실행 결과가 아니며 Excel에서 실제 확인한 값은 검산 E열에 기록합니다.

B의 300을 A로 옮겨 총합만 같아지는 반례와 수동 교차 검사를 보여 주는 설명 도해. 가상 입력과 기대값을 설명한 그림이며 실제 Excel 실행 화면이나 실행 성공 증거가 아닙니다.

중단됐을 때 남겨야 할 원본

숫자 문자열·불리언·오류·빈 지점이 있으면 입력을 확인하고 사본에서 다시 실행합니다. 숫자 문자열을 허용하려고 IsNumeric만으로 바꾸면 계약이 달라집니다. 음수·소수는 실제 숫자로 받는 코드이므로 반품 금액 등을 배제하는 회계 규칙은 별도로 정해야 합니다.

피벗 생성 중 실패하면 이 실행에서 만든 새 output만 닫기를 시도하고 화면 갱신·이벤트를 시작 값으로 복원합니다. 원본과 기존 보고서는 삭제하지 않습니다. 닫기·설정 복원이 실패하거나 앱을 강제 종료하면 자동 복구를 보장하지 않으므로 남은 창과 상태를 확인합니다.

입력을 수정한 뒤 예전 출력의 새로고침만 누르면 예전 Snapshot을 다시 읽습니다. 새 입력을 반영하려면 원본에서 BuildSalesPivot을 다시 실행해 새 보고서를 만들고 이전 결과와 구분하세요. 결과 자동 저장, 월 형식의 의미 검사, 피벗 호환성 조사·대량 처리 실측은 이 예제에 포함하지 않았습니다.