피벗테이블 합계 ▶ 평균 일괄 변환 매크로
Sub ChangePV_SumtoAvg()
Dim WS As Worksheet
Dim pvTbl As PivotTable: Dim pvFld As PivotField
Dim sFld As String: Dim vArr As Variant: Dim v As Variant
Dim blnAll As Boolean: blnAll = False
sFld = InputBox("평균으로 변경할 필드를 입력하세요." & vbNewLine & "(모두 변경하려면 * 입력)", "필드명 입력")
If sFld = "" Then Exit Sub
If sFld = "*" Then blnAll = True
vArr = Split(sFld, ",")
On Error GoTo EH:
Set WS = ActiveSheet
Set pvTbl = ActiveCell.PivotTable
For Each pvFld In pvTbl.DataFields
For Each v In vArr
If blnAll = True Or InStr(1, pvFld.Caption, trim(v)) > 0 Then
pvFld.Function = xlAverage
pvFld.Caption = "평균 " & Mid(pvFld.Caption, InStr(1, pvFld.Caption, ":"))
End If
Next
Next
MsgBox "선택하신 필드의 계산이 평균으로 변경되었습니다."
Exit Sub
EH:
MsgBox "피벗테이블이 제대로 선택되지 않았거나 필드명이 올바르지 않습니다. 다시 확인해주세요"
End Sub
엑셀 데이터분석 업무를 위한 실무자 필수 기능! 바로 '피벗테이블' 입니다. 피벗테이블은 잘 정리된 데이터에서는 문제가 없습니다. 하지만 가로 방향으로 나열된 데이터를 사용하면, 여러 필드를 추가하거나 계산방식을 변경할 때마다 상당히 많은 반복 작업이 필요합니다.
대표적인 상황이 '합계'로 요약된 필드를 모두 '평균'으로 바꿔야 하는 경우입니다. 이번 강의에서는 피벗테이블의 '합계'로 요약된 필드를 '평균'으로 한 번에 일괄 변경하는 방법을 살펴보겠습니다.
- 매크로 추가하기 : 예제파일을 실행한 뒤, [개발도구] 탭 - [Visual Basic] 버튼을 클릭하거나 단축키 ALT + F11 을 눌러 매크로 편집기를 실행합니다. 만약 화면 상단에 [개발도구]가 안 보이실 경우, 아래 개발도구 추가하기 관련 게시글을 참고하세요.
엑셀 매크로 편집기를 실행합니다.
- 매크로 편집기에서 [삽입] - [모듈]을 클릭해 새로운 모듈을 추가합니다. 추가된 모듈로 이동한 뒤, 홈페이지에 올려드린 명령문을 복사/붙여넣기 합니다.
새로운 모듈을 추가한 뒤, 복사한 명령문을 붙여넣기합니다.
- 매크로를 단축키로 등록하기 : 다시 엑셀로 돌아온 뒤, [개발도구] 탭 - [매크로] 버튼을 클릭하거나 단축키 ALT + F8 을 눌러 매크로 목록창을 실행합니다.
매크로 옵션에서 방금 추가한 명령문의 옵션으로 이동합니다.
- 매크로 목록에서 "ChangePV_SumtoAvg"을 선택한 뒤, [옵션] 버튼을 클릭합니다. 매크로 옵션창이 나오면 바로 가기 키를 Ctrl + Shift + P 로 등록합니다. 또는 다른 편한 바로가기 키를 등록해도 괜찮습니다.
매크로 단축키를 설정합니다.
오빠두Tip : 바로가기 키를 등록할 때 Ctrl 키는 기본으로 설정됩니다. 따라서 Shift + P 만 눌러서 바로가기 키를 등록합니다.
- 피벗테이블 평균 일괄변경 매크로 : 확인을 눌러 마무리하면 매크로 등록이 끝났습니다. 이제 바로가기 키를 누르면 필드명을 입력하는 대화상자가 나옵니다. 변경할 필드명으로 "수량, 단가"를 입력하고 [확인] 버튼을 누르면, 필드명에 '수량'과 '단가'가 포함된 모든 필드의 요약방식이 평균으로 일괄 변경됩니다.
요약방식을 평균으로 변경할 필드명을 입력합니다.
- 또는 필드명으로 "*" 를 입력 후 [확인] 버튼을 누르면 피벗테이블의 모든 필드의 요약방식을 '평균'으로 일괄 변경할 수 있습니다.
필드명으로 * 을 입력하면 모든 필드의 계산이 합계에서 평균으로 일괄 변경됩니다.
오빠두Tip : 단, 원본데이터의 문자가 포함되어 '개수'로 요약된 필드는 '평균'으로 요약할 수 없으므로 평균으로 변경 시 "#DIV/0" 오류가 반환됩니다.
