피벗테이블 자동 업데이트 완벽정리

영상강의

패치노트

• 2020.04.23 :
  셀의 내용을 삭제한 경우에도 횟수에 상관없이 이전 단계로 되돌릴 수 있도록 전체 명령문을 업데이트하였습니다. 자세한 내용은 세 번째 항목을 참고해 주세요.
• 2020.04.23 추가 :
  댓글로 문의주신 분이 많아, 완성파일 시트를 두 개로 분리하였습니다. 완성파일을 다운로드하면 두 개의 시트가 포함되어 있습니다.
  [완성예제#1] 시트에는 데이터 추가/삭제 시 모두 실시간으로 업데이트되는 명령문이 입력되어 있습니다. 다만 데이터 삭제 후에는 이전 단계로 되돌릴 수 없다는 단점이 있습니다.
  [완성예제#2] 시트는 데이터 추가 시에만 실시간으로 업데이트됩니다. 데이터 삭제 후에도 횟수에 상관없이 이전 단계로 되돌릴 수 있습니다.

강의 설명

실무자가 엑셀에서 가장 많이 사용하는 기능 중 하나가 바로 피벗테이블입니다. 피벗테이블은 데이터를 요약하는 강력한 도구이지만, 원본 데이터가 변경되어도 실시간으로 갱신되지 않는다는 한계가 있습니다. 그러다 보니 주기적으로 자료가 추가되는 데이터에서는 매번 직접 새로고침을 해야 하는 번거로움이 따랐습니다.

그렇다면 피벗테이블 자동 업데이트를 손쉽게 구현할 방법은 없을까요?

이번 강의에서는 명령문 복사·붙여넣기만으로 적용할 수 있는 상황별 피벗테이블 자동 업데이트 방법을 3단계로 나누어 자세히 살펴봅니다.

피벗테이블 자동 새로고침 단계별 알아보기

원본 데이터가 피벗테이블과 같은 시트에 있는 경우, 원본 데이터에 새로운 자료가 추가될 때마다 피벗테이블도 함께 업데이트되어야 합니다. 그래야 사용자에게 실시간으로 반영되는 모습을 보여줄 수 있습니다.

따라서 워크시트의 'Change' 이벤트를 사용하여 명령문을 작성합니다.

1. 원본 데이터와 피벗테이블이 위치한 시트명을 확인합니다.
   예제파일에서는 '예제1' 시트에 피벗테이블을 작성해 두었습니다.
2. 엑셀 화면 상단의 '개발도구' 탭에서 'Visual Basic'을 클릭하거나, 키보드 Alt + F11 단축키를 눌러 매크로 편집기로 이동합니다.
   화면 상단에 개발도구 탭이 보이지 않는 경우 아래 개발도구 활성화 방법 포스트를 참고하세요.
   
3. 매크로 편집기 좌측 프로젝트 창의 'Microsoft Excel 개체' 목록에는 현재 통합문서에 포함된 시트가 나열됩니다. 이 중 자동 업데이트를 적용할 시트를 더블클릭하여 시트 명령문 창으로 이동합니다.

   

4. 우측 편집기 상단에서 '(일반)' 항목을 'Worksheet'로 변경합니다.

   

5. 이어서 (선언) 항목에서 'Change' 이벤트를 선택합니다.

   

6. 그러면 Worksheet_Change 라는 이벤트 명령문이 자동으로 생성됩니다.

   

7. 아래 코드를 복사하여 Worksheet_Change 명령문 안에 붙여넣기합니다.

   Dim pvTbl As PivotTable
    
   Application.EnableEvents = False
   For Each pvTbl In Me.PivotTables
       pvTbl.RefreshTable
   Next
   Application.EnableEvents = True

8. 이로써 피벗테이블 자동 업데이트 설정이 완료되었습니다. 해당 시트에서 원본 데이터를 변경하면 피벗테이블이 실시간으로 자동 갱신됩니다.

   

9. 완성된 파일은 매크로가 포함된 파일이므로 'Excel 매크로 사용 통합문서(*.xlsm)' 형식으로 저장합니다.

   

그렇다면 원본 데이터와 피벗테이블이 서로 다른 시트에 있는 경우는 어떻게 처리해야 할까요? 앞서와 동일하게 피벗테이블을 실시간으로 업데이트해야 할까요?

그렇지 않습니다.

피벗테이블이 원본 데이터와 다른 시트에 있다면, 새로운 데이터를 추가한 뒤 피벗테이블 시트로 이동하는 시점에 한 번만 업데이트해 주어도 실시간 갱신과 동일한 효과를 얻을 수 있습니다.

따라서 원본 데이터와 피벗테이블이 다른 시트에 있는 경우에는 워크시트의 'Activate(활성화)' 이벤트를 사용하여 자동 업데이트를 구현합니다.

1. 매크로 편집기에서 피벗테이블이 위치한 시트를 더블클릭하여 시트 모듈로 이동합니다.

   

2. '(일반)' 항목에서 Worksheet, '(선언)' 항목에서 Activate를 선택합니다.

   

3. 아래 코드를 복사하여 'Worksheet_Activate' 명령문 안에 붙여넣기합니다.

   Dim pvTbl As PivotTable
    
   Application.EnableEvents = False
   For Each pvTbl In Me.PivotTables
       pvTbl.RefreshTable
   Next
   Application.EnableEvents = True

4. 이로써 원본 데이터와 피벗테이블이 다른 시트에 있을 때의 자동 업데이트 설정이 완료되었습니다. 원본 데이터에 자료를 추가한 뒤 피벗테이블 시트로 이동하면 피벗테이블이 자동으로 갱신됩니다.

   

5. 완성된 파일은 매크로가 포함된 파일이므로 'Excel 매크로 사용 통합문서(*.xlsm)' 형식으로 저장합니다.

   

매크로로 피벗테이블 자동 업데이트를 구현하면 한 가지 주의할 점이 있습니다. 바로 매크로가 동작한 시점부터 '실행 취소(이전 단계로 돌아가기)'가 모두 초기화된다는 점입니다.

새로운 자료를 추가하는 상황에서는 큰 문제가 없지만, 자료를 잘못 삭제하여 이전 단계로 되돌려야 하는 상황에서는 작업 손실로 이어질 수 있습니다.

이 문제는 명령문을 약간만 보완하면 어렵지 않게 해결할 수 있습니다. 아래 코드를 복사하여 시트 모듈에 붙여넣으면 데이터가 삭제된 경우에도 이전 단계로 되돌릴 수 있도록 동작합니다. (아래 명령문은 전체를 그대로 복사하여 시트 모듈에 붙여넣어야 합니다.)

단, 실행 취소를 진행할 때는 되돌리는 순서가 삭제된 순서와 동일해야 합니다. 즉, 이전 단계로 되돌리는 도중 키보드나 마우스로 다른 셀을 선택하거나 이동하면 되돌리기 기록이 초기화되므로 주의해야 합니다.

' nVal          : Change Event로 받아온 셀에 입력된 최종 값
' oVal          : Selection Change Event로 받아온 최종 수정 전 입력된 값
' tVal          : 삭제된 값이 원복된 것인지 확인하기 위한 임시 값
' DeleteFlag    : 셀이 삭제 될 경우 발동되는 Trigger
Dim nVal As Variant: Dim oVal As Variant
Dim DeleteFlag As Boolean: Dim tVal As Variant
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
oVal = Target.Value
 
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim pvTbl As PivotTable
 
' 오류가 발생할 경우 EH로 넘어가면서 명령문이 바로 종료됩니다.
On Error GoTo EH:
 
nVal = Target.Value
 
'  Target이 비어있을 경우 오류가 발생하면서 명령문 종료 (셀 완전 삭제시)
If nVal <> "" Then
    ' DeleteFlag (셀 삭제여부 Trigger) 가 False 이거나 빈칸일 경우 피벗테이블 업데이트를 진행합니다.
    If DeleteFlag = False Or IsEmpty(DeleteFlag) Then
        If oVal <> nVal Then
            Application.EnableEvents = False
            For Each pvTbl In Me.PivotTables
                pvTbl.RefreshTable
            Next
            Application.EnableEvents = True
        End If
    Else
        Select Case True
            ' DeleteFlag가 True이고 새로입력된값 = 임시값 이면 Pass
            Case nVal = tVal
            ' DeleteFlag가 True이고 임시값 = 이전값일 경우 삭제된 값이 복원된 상태이므로
            ' 임시값을 새로운 값으로 치환하고 Pass
            Case oVal = tVal
                tVal = nVal
            ' 위 두가지 상황이 아닐 경우 새로 입력된경우이므로 피벗테이블 업데이트 후 DeleteFlag 를 False로 리턴
            Case Else
            Application.EnableEvents = False
            For Each pvTbl In Me.PivotTables
                pvTbl.RefreshTable
            Next
            Application.EnableEvents = True
            DeleteFlag = False
        End Select
    End If
Else
    If DeleteFlag = False Or IsEmpty(DeleteFlag) Then
        DeleteFlag = True
        tVal = oVal
    Else
        tVal = oVal
    End If
End If
 
Exit Sub
EH:
 
End Sub

위 명령문을 사용할 때 한 가지 주의 사항이 있습니다.

'셀을 완전히 삭제하는 작업'(우클릭 후 삭제 또는 단축키 Ctrl + - 키)은 횟수에 상관없이 이전 단계로 되돌릴 수 있습니다.

다만 '셀 안의 내용만 지우는 작업'(← 또는 Delete 키)은 한 번만 되돌리기가 가능하다는 점에 유의해야 합니다. 셀 내용만 지운 작업을 되돌리는 과정에서 셀에 값이 다시 입력되면서 매크로가 실행되기 때문입니다. 이로 인해 피벗테이블 자동 업데이트 매크로가 동작하면서 이전 작업 기록이 초기화됩니다.