엑셀 피벗테이블 합계 - 평균 일괄 변경, 원클릭 해결방법

엑셀 VBA 매크로를 사용해서 피벗테이블의 합계를 평균으로 한 번에 변환하는 방법을 알아봅니다

홈페이지 » 엑셀 피벗테이블 합계 평균 일괄 변경, 원클릭 해결방법!

엑셀 피벗테이블 합계 평균 일괄 변경, 원클릭 해결방법

피벗테이블 합계 평균 변경 목차 바로가기
영상 강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀프레소] 피벗테이블 요약 일괄 변경 매크로
    예제파일
  • [엑셀프레소] 피벗테이블 요약 일괄 변경 매크로
    완성파일

피벗테이블 합계 ▶ 평균 일괄 변환 매크로

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

피벗테이블 평균 일괄변경 매크로 사용법

엑셀 데이터분석 업무를 위한 실무자 필수 기능! 바로 '피벗테이블' 입니다. 이 피벗테이블 기능은 잘 다듬어진 데이터로 사용하면 큰 문제가 없지만, 만약 가로방향으로 나열된 데이터를 피벗테이블로 사용하면 여러 필드를 매번 추가하거나 계산방식을 변경할 때마다 상당히 많은 양의 반복업무가 필요합니다.

가장 자주 발생하는 상황은 '합계'로 요약된 필드를 모두 '평균'으로 변경해야 할 경우가 되겠는데요. 이번 강의에서는 피벗테이블의 '합계'로 요약된 필드를 '평균'으로 한 번에 일괄 변경하는 방법을 알아보겠습니다.

  1. 매크로 추가하기 : 예제파일을 실행한 뒤, [개발도구] 탭 - [Visual Basic] 버튼을 클릭하거나 단축키 ALT + F11 을 눌러 매크로 편집기를 실행합니다. 만약 화면 상단에 [개발도구]가 안 보이실 경우, 아래 개발도구 추가하기 관련 게시글을 참고하세요.

    엑셀 매크로 편집기 실행
    엑셀 매크로 편집기를 실행합니다.

  2. 매크로 편집기에서 [삽입] - [모듈]을 클릭해 새로운 모듈을 추가합니다. 추가된 모듈로 이동한 뒤, 홈페이지에 올려드린 명령문을 복사/붙여넣기 합니다.

    엑셀 피벗테이블 평균 일괄 변경 매크로
    새로운 모듈을 추가한 뒤, 복사한 명령문을 붙여넣기합니다.

  3. 매크로를 단축키로 등록하기 : 다시 엑셀로 이동한 뒤, [개발도구] 탭 - [매크로] 버튼을 클릭하거나, 단축키 ALT + F8 을 눌러 매크로 목록창을 실행합니다.

    엑셀 피벗테이블 매크로 옵션
    매크로 옵션에서 방금 추가한 명령문의 옵션으로 이동합니다.

  4. 매크로 목록에서 "ChangePV_SumtoAvg"을 선택한 뒤, [옵션] 버튼을 클릭합니다. 매크로 옵션창이 나오면 바로 가기 키를 Ctrl + Shift + P 로 등록합니다. 또는 다른 편한 바로가기 키를 등록해도 괜찮습니다.
    엑셀 매크로 단축키 설정
    매크로 단축키를 설정합니다.
    오빠두Tip : 바로가기 키를 등록할 때 Ctrl 키는 기본으로 설정됩니다. 따라서 Shift + P 만 눌러서 바로가기 키를 등록합니다.
  5. 피벗테이블 평균 일괄변경 매크로 : 확인을 눌러 마무리하면 매크로 등록이 끝났습니다. 이제 바로가기 키를 누르면 필드명을 입력하는 대화상자가 나옵니다. 변경할 필드명으로 "수량, 단가"를 입력 후 [확인] 버튼을 누르면 필드명에서 '수량'과 '단가'가 포함된 필드의 요약방식이 모두 평균으로 일괄 변경됩니다

    엑셀 피벗테이블 일괄 변경
    요약방식을 평균으로 변경할 필드명을 입력합니다.

  6. 또는 필드명으로 "*" 를 입력 후 [확인] 버튼을 누르면 피벗테이블의 모든 필드의 요약방식을 '평균'으로 일괄 변경할 수 있습니다.
    피벗테이블 합계 평균 바꾸기
    필드명으로 * 을 입력하면 모든 필드의 계산이 합계에서 평균으로 일괄 변경됩니다.
    오빠두Tip : 단, 원본데이터의 문자가 포함되어 '개수'로 요약된 필드는 '평균'으로 요약할 수 없으므로 평균으로 변경 시 "#DIV/0" 오류가 반환됩니다.
5 1 투표
게시글평점
guest
1 댓글
Inline Feedbacks
모든 댓글 보기
만수무강짱
만수무강짱
2021년 11월 22일 11:16 오전
게시글평점 :
     

감사합니다

1
0
여러분의 생각을 댓글로 남겨주세요.x