엑셀 피벗테이블 자동 업데이트, 해결방법 총정리

엑셀 피벗테이블 자동 업데이트, 기초부터 응용까지 총정리

피벗테이블 자동 업데이트 목차 바로가기

영상강의

예제파일 다운로드

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

패치노트

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

강의 설명

실무자가 엑셀에서 가장 많이 사용하는 기능 중 하나, 바로 '피벗테이블' 입니다. 피벗테이블은 엑셀에서 아주 훌륭한 도구이지만, 원본데이터 업데이트가 실시간으로 안 되는 문제가 있는데요. 그러다보니 주기적으로 데이터가 추가되는 자료에서, 매번 '새로고침' 작업을 해줘야 하는 번거로움이 있었습니다.

피벗테이블 자동 업데이트를 쉽게 구현할 수 있는 방법, 어디 없을까요?

이번 강의에서는 명령문 복사/붙여넣기만으로 할 수 있는 각 상황별 피벗테이블 자동 업데이트 방법을 3단계로 나눠서 자세히 알아봅니다.

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

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

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

만약 원본데이터와 피벗테이블이 다른 시트에 있을 경우는 어떻게 해야 할까요? 이전과 동일하게 피벗테이블을 실시간으로 업데이트 해줘야 할까요?

아닙니다!

피벗테이블이 원본데이터와 다른 시트에 있다면, 원본데이터에 새로운 데이터 추가 후 '피벗테이블로 시트로 이동하는 순간' 한번만 업데이트 해주면 실시간으로 업데이트 되는 것과 동일한 효과를 볼 수 있습니다.

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

매크로를 사용하여 피벗테이블 자동 업데이트를 구현할 경우 한가지 문제점이 있습니다. 바로 매크로가 동작된 시점부터 '이전 단계로 돌아가기' 작업이 모두 초기화 된다는 점인데요.

이는 새로운 자료를 추가할 때는 별 문제되지 않지만, 자료를 잘못 삭제하여 이전단계로 돌아가야 할 경우에는 큰 문제가 될 수 있습니다.

이 문제는 명령문을 약간만 변형하면 쉽게 해결할 수 있습니다. 아래 코드를 복사하여 각 명령문에 붙여넣기하면, 데이터가 삭제되어도 이전 단계로 돌아갈 수가 있게 됩니다. (아래 명령문은 통채로 복사해서 시트모듈에 그대로 붙여넣기 합니다.)

단, 이전단계로 돌아가기 시 돌아가는 순서는 삭제된 순서와 동일해야 합니다. 즉, 이전단계로 돌아가는 도중 키보드나 마우스로 다른 셀을 클릭하거나 이동하게 되면 이전단계로 돌아가기 기록이 초기화되는 것에 주의합니다.

' 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 키)은 한번만 이전단계로 돌아가기가 가능하다는 점을 주의해야 합니다. 그 이유는, 셀의 내용만 지운 작업을 이전단계로 돌아가기 할 경우, 셀의 내용이 '입력' 되면서 매크로가 실행됩니다. 따라서 피벗테이블 자동 업데이트 매크로가 돌게되고, 이전의 모든 작업이 초기화됩니다.