엑셀 VBA로 피벗테이블 값필드 요약기준을 합계로 변경하기

엑셀 반복 작업을 자동화하자!

엑셀에서 피벗 테이블을 사용할 때 매출, 영업이익 등의 합계를 보고자 하는 필드는 '값' 부분에 추가하게 됩니다. 이때 숫자만 있다면 값 필드 요약 기준이 자동으로 '합계'가 되지만 숫자 이외의 공백이나 문자가 있을 경우 '개수'로 집계됩니다. 이 경우 수작업을 통해 '합계'로 변경해줘야 원하는 대로 숫자 집계가 가능합니다.

하지만 손익계산서 등의 복잡한 레이아웃을 구성할 경우 필드가 몇 십개 되는 경우도 있습니다. 이 경우 수작업으로 값 필드 요약 기준을 변경하려면 의미 없는 수작업을 반복해야 하는데요. 엑셀 매크로와 VBA 기능을 이용하면 손쉽게 '합계'로 변경할 수 있습니다.

엑셀 매크로란?

엑셀 매크로는 엑셀에서 반복되는 작업을 자동화하기 위해서 키보드와 마우스 입력을 프로그래밍처럼 기록해서 반복 사용하게 해주는 기능입니다. 당연히 기록된 내용을 수정할 수도 있고 단순 키보드/마우스 입력으로 불가능한 부분은 Visual Basic 함수를 이용해서 프로그래밍 할 수 있습니다. 다만 포스트 하나로 Visual Basic을 설명할 수는 없기 때문에 이미 작성된 매크로 내용을 공유드리고 이를 적용하는 방법을 설명드리겠습니다.

액셀 매크로 생성하기

엑셀 '보기' 메뉴 밑에 있는 매크로 부분을 찾아서 '매크로 기록'을 클릭합니다.

그러면 매크로 이름와 바로 가기 키 등을 설정하는 창이 뜨는데, 지금 수정할 필요는 없기 때문에 '확인'을 클릭해서 다음으로 넘어갑니다.

그러면 바로 키보드와 마우스 입력이 기록될 수 있는 상태가 되는데, 나중에 프로그래밍 구분을 입력할 것이기 때문에 바로 '기록 중지'를 크릭합니다. 해당 메뉴는 '보기' 메뉴 밑에 매크로 부분에 있습니다.

그러면 아래와 같이 매크로가 저장된 상태가 보이는데 여기에서 '편집'을 클릭해서 Visual Basic 프로그래밍 구분을 입력할 수 있는 화면으로 이동합니다.

VBA 프로그래밍

VBA(Visual Basic for Applications) 프로그래밍 구분을 입력할 수 있는 화면이 뜨는데, 프로그래밍 경험이 있으신 분들은 아시겠지만 Visual Studio와 비슷합니다. 여기에서 아래의 구분을 입력합니다. 구분은 포스트 하단에서 텍스트 복사하시면 되겠습니다.

VBA 내용을 다 입력하셨다면 창을 닫고 마우스 커서를 피벗 테이블 내부의 아무 곳이나 위치하게 합니다. 그리고 '매크로 보기' 창을 띄운 후 '실행'을 클릭하면 모든 값 필드가 '합계'로 변경됩니다.

VBA 내용은 아래를 참고하시길 바랍니다.

Sub dhMain()

'피벗 테이블 필드(데이터 필드만)를 요약하는 데 사용되는 함수를 설정하거나 반환한다.
'읽기/쓰기가 가능한 XlConsolidationFunction 형식
'XlConsolidationFunction은 다음 XlConsolidationFunction 상수 중 하나를 사용할 수 있다

'xlAverage
'xlCountNums
'xlMin
'xlStDev
'xlSum
'xlVar
'xlCount
'xlMax
'xlProduct
'xlStDevP
'xlUnknown
'xlVarP

    dhALLPivotTableDataFunctionChange xlSum '합계로 변경

    'dhALLPivotTableDataFunctionChange xlCount '개수로 변경
End Sub

Sub dhALLPivotTableDataFunctionChange(lngF As Long)
'*********************************************************************
' 활성 모든 피벗테이블의 모든 데이터 필드의 설정을 특정 함수로 변경하기
' 만든이 황기성
' 2006. 8. 3
' 엑셀의 모든 것 - MagicSheet & 엑사모
' http://excellove.com
'**********************************************************************
Dim p As PivotTable
Dim d As Object
Dim strF As String
Dim lngFunction(1 To 12) As Long
Dim strFunction(1 To 12) As String
Dim i As Integer
Const Es As String = "MagicSheet & 엑사모"

'On Error GoTo e1
    If ActiveSheet.PivotTables.Count = 0 Then
        MsgBox "현재 시트에는 피벗테이블이 없습니다!", vbInformation, Es
        Exit Sub
    Else
    End If

    For i = 1 To 12
        lngFunction(i) = Choose(i, xlSum, xlCount, xlAverage, xlMax, xlMin, xlProduct, xlCountNums, xlStDev, xlStDevP, xlVar, xlVarP, xlUnknown)
        strFunction(i) = Choose(i, "합계", "개수", "평균", "최대값", "최소값", "곱", "숫자개수", "표본표준편차", "표준편차", "표본분산", "분산", "알려지지 않음")
    Next i

    strF = strFunction(Application.WorksheetFunction.Match(lngF, lngFunction, 0))

    If MsgBox("현재 워크시트의 모든 피벗테이블의 필드의 함수를" & _
               vbCr & strF & "(으)로 변경하시겠습니까?", vbYesNo + vbQuestion, Es) = vbYes Then

        For Each p In ActiveSheet.PivotTables
            For Each d In p.DataFields
                d.Function = lngF
            Next d
        Next p
        MsgBox "일괄 변경을 마쳤습니다!", vbInformation, Es
    Else
    End If
    Exit Sub
e1:
    MsgBox Err.Number & vbCr & Err.Description, vbExclamation, Es
End Sub