엑셀 피벗 캐시 손상 복구 방법: 오류 원인 진단부터 안전한 복구 절차까지

이 글의 목적은 엑셀 피벗 캐시 손상으로 인한 다양한 오류를 체계적으로 진단하고, 데이터 유실 없이 안전하게 복구하는 실무 절차와 예방 팁을 제공하는 것이다.

피벗 캐시의 개념과 손상 증상

피벗 캐시는 피벗테이블이 참조하는 데이터의 스냅샷을 통합·압축하여 저장하는 내부 개체이다. 하나의 통합문서에서 여러 피벗테이블이 동일한 캐시를 공유할 수 있으며, 캐시가 손상되면 연동된 피벗테이블 모두에서 오류가 발생한다.

대표 증상	오류 메시지·행동	가능 원인
새로고침 실패	피벗테이블 새로 고침 중 중단 또는 응답 없음	캐시 레코드 불일치, 데이터 원본 이름 변경, 손상된 연결
필드 목록 이상	필드 목록이 비거나 필드명이 유효하지 않다는 경고	원본 범위 손실, 정의된 이름 깨짐, 열 머리글 문제
파일 열기 경고	일부 콘텐츠에 문제가 있어 복구했다는 메시지	xl/pivotCacheDefinition*.xml, pivotCacheRecords*.xml 불일치
보고서 레이아웃 붕괴	그룹 해제, 항목 소실, 빈 피벗	캐시-필드 메타데이터 손상, 오래된 항목 유지 설정
외부연결 오류	데이터 원본을 찾을 수 없음, 자격 증명 실패	연결 문자열 변경, ODC/쿼리 손상, 권한 만료

주의 : 캐시 손상 추정 시 즉시 원본 파일을 복사하여 별도 복구용 사본에서 작업해야 한다.

복구 전략 개요

실무에서는 위험도가 낮은 조치부터 높은 조치 순으로 진행하는 것이 안전하다. 아래 순서를 권장한다.

1. 기본 점검: 안전 모드·추가 기능 비활성화·빠른 검사 수행
2. 데이터 원본 정합성 확보: 테이블화, 머리글 검증, 정의된 이름 수정
3. 캐시 정리 및 재생성: 오래된 항목 제거, 피벗테이블 재지정
4. 피벗 캐시 대체: 새 캐시를 만든 뒤 모든 피벗을 일괄 리맵
5. 연결·쿼리 복구: Power Query·외부연결 재설정
6. 고급 복구: Open and Repair, 시트/개체 이관, XML 레벨 점검

1단계. 기본 점검

1) 안전 모드로 실행

환경 문제와 충돌을 배제하기 위해 안전 모드에서 파일을 열어 증상을 재현한다.

  
Win + R excel /safe 
  

안전 모드에서 오류가 사라지면 COM 추가 기능·XLL·VBA 애드인의 간섭 가능성이 높다. 일반 모드로 재기동 후 [파일 > 옵션 > 추가 기능]에서 문제 애드인을 비활성화한다.

2) 빠른 무결성 점검

• [파일 > 정보]에서 통합문서 보호 상태와 외부 링크 경고를 확인한다.
• [데이터 > 연결]에서 불필요한 연결을 제거한다.
• [수식 > 이름 관리자]에서 #REF!가 포함된 정의된 이름을 수정한다.

2단계. 데이터 원본 정합성 확보

피벗 캐시는 원본 구조에 강하게 의존한다. 다음 항목을 먼저 바로잡아야 한다.

• 원본 범위를 표(테이블)로 변환한다. 삽입 > 표 또는 Ctrl + T 를 사용한다.
• 모든 열 머리글은 비어 있지 않고 중복되지 않아야 한다. 병합 셀을 해제한다.
• 숫자 열에 문자열이 섞이면 같은 항목이 다른 필드로 분기될 수 있다. 데이터 형식을 정규화한다.
• 이름 관리자에서 피벗 원본용 이름이 올바른 참조(예: =Table1 )를 가리키는지 확인한다.

주의 : “피벗테이블 필드 이름이 유효하지 않음”은 대개 빈 머리글이나 병합 셀로 인해 발생한다.

3단계. 캐시 정리 및 재생성 시도

1) 오래된 항목 제거

피벗테이블 옵션에서 오래된 항목을 보존하지 않도록 설정한다.

1. 피벗을 선택하고 마우스 우클릭 > 피벗테이블 옵션
2. [데이터] 탭에서 [필드당 보존할 항목 수]를 “없음”으로 설정
3. [파일 > 저장] 후 피벗 새로 고침

2) 파일 저장 시 원본 데이터 포함

[피벗테이블 옵션 > 데이터]에서 “원본 데이터를 파일과 함께 저장”을 체크하고 저장한다. 이후 새로 고침으로 캐시를 재구성한다.

3) 모든 피벗 새로 고침

  
데이터 탭 > 모두 새로 고침 
  

여전히 실패하면 다음 단계로 진행한다.

4단계. 새 피벗 캐시로 일괄 리맵

손상된 캐시를 버리고 동일 원본으로 새 캐시를 만들어 피벗테이블들을 일괄 연결한다. 아래 VBA는 표 이름( Table1 )을 원본으로 새 캐시를 만든 뒤 모든 피벗을 교체하고, 누락 항목 보존을 제거한다.

  
Option Explicit
Sub RebuildPivotCachesToTable1()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet, pt As PivotTable
Dim newPC As PivotCache
Dim src As Variant

src = wb.Worksheets(1).Range("Table1").Address(ReferenceStyle:=xlR1C1, External:=True)
Set newPC = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=src)

For Each ws In wb.Worksheets
    For Each pt In ws.PivotTables
        ' 캐시 교체
        pt.ChangePivotCache newPC
        ' 오래된 항목 보존 안 함
        On Error Resume Next
        pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
        On Error GoTo 0
        pt.RefreshTable
    Next pt
Next ws
End Sub

  

주의 : 위 매크로 실행 전 반드시 통합문서를 백업해야 한다. 표 이름이 다르면 Table1 을 실제 표 이름으로 바꿔야 한다.

5단계. 연결·쿼리 기반 피벗 복구

Power Query(쿼리 및 연결) 사용 시

1. [데이터 > 쿼리 및 연결]에서 해당 쿼리를 선택한다.
2. 오른쪽 클릭 > 속성 > “데이터 새로 고침 시 이 통합 문서의 데이터 모델 새로 고침”을 활성화한다.
3. 쿼리를 편집하여 원본 경로·형식을 재검증한다. 경로가 네트워크 드라이브이면 UNC 경로로 통일한다.
4. 닫기 및 로드 > “연결만 만들기” 후 테이블로 다시 로드해 피벗 원본을 갱신한다.

외부 연결(ODC, OLE DB, ODBC) 사용 시

• [데이터 > 연결]에서 문제 연결을 선택해 [속성]에서 연결 문자열과 명령 텍스트를 점검한다.
• 자격 증명 캐시를 재설정하고 테스트 연결을 수행한다.
• OLAP 큐브·Power Pivot 데이터 모델을 사용하는 경우 [데이터 모델 관리]에서 테이블 관계를 재평가한다.

6단계. 고급 복구 시나리오

1) Open and Repair

1. [파일 > 열기]에서 문제 파일을 선택한다.
2. [열기] 옆 화살표 > [열기 및 복구] > [복구] → 실패 시 [데이터 추출]을 시도한다.

2) 시트·개체 이관

손상이 특정 피벗 캐시에 국한된 경우, 데이터 시트와 서식 시트만 새 통합문서로 복사하고 피벗은 새로 만든다. 서식·계산 필드 정의는 별도 기록 후 재구성한다.

3) XML 레벨 점검(전문가용)

파일 사본의 확장자를 .zip으로 변경해 압축을 해제한다. xl/pivotCacheDefinition*.xml · xl/pivotCacheRecords*.xml 에서 0바이트 파일이나 명백한 단절을 점검한다. 구조 이상이 확인되면 문제 캐시가 참조된 피벗을 삭제하고 통합문서를 재저장한 뒤, 새 피벗을 만들고 4단계의 일괄 리맵 절차를 적용한다. 수동 XML 편집은 고위험이므로 권장하지 않는다.

주의 : XML을 직접 수정하면 서명 무효화·파일 자체 손상으로 이어질 수 있다. 가능한 한 UI·VBA 기반 방식으로 복구해야 한다.

진단 체크리스트

점검 항목	합격 기준
데이터 머리글 무결성	빈 셀·병합 없음, 고유 머리글
원본 참조 방식	표 이름 또는 정의된 이름 사용, 절대참조 불필요
정의된 이름 상태	#REF! 없음, 올바른 범위
연결·쿼리 상태	테스트 연결 통과, 자격 증명 유효
피벗 캐시 설정	보존 항목: 없음, 저장 시 원본 포함: 활성
애드인 간섭	안전 모드에서 재현 시 애드인 의심 해소

예방 가이드

• 피벗 원본은 반드시 표로 유지한다. 열 추가·삭제가 잦다면 Power Query로 스키마를 고정한다.
• 여러 피벗이 동일 원본을 사용한다면 같은 캐시를 공유하게 하여 일관성을 유지한다.
• 대용량 파일은 “오래된 항목 보존 없음”을 기본 정책으로 한다.
• 외부연결 자격 증명은 조직 정책에 맞춰 주기적으로 갱신한다.
• 버전 관리: 월 1회 백업 스냅샷을 남기고, 주요 변경 후에는 즉시 보관본을 만든다.

자주 묻는 질문(FAQ)

피벗테이블 필드 목록이 비어 있는데 원본 데이터는 정상이다. 무엇을 확인해야 하나?

원본이 표 형태인지, 머리글이 비어 있지 않은지, 병합 셀이 없는지 확인해야 한다. 이름 관리자에서 피벗 원본 이름이 올바른 범위를 가리키는지도 점검해야 한다. 이후 피벗테이블 옵션에서 원본 데이터를 파일과 함께 저장을 활성화하고 새로 고침을 수행해야 한다.

여러 피벗이 서로 다른 캐시를 쓰고 있다. 하나로 통합하려면 어떻게 하나?

VBA로 새 캐시를 생성한 뒤 ChangePivotCache 를 통해 모든 피벗을 동일 캐시에 리맵해야 한다. 본문 4단계 매크로를 활용하면 된다.

“일부 콘텐츠에 문제가 있어 복구함” 메시지 이후 피벗이 사라졌다. 복원 가능한가?

대개 손상된 캐시가 제거되며 피벗 정의가 초기화된다. 시트 이관 후 새 캐시로 피벗을 재구성하고, 기존 필드·계산 항목 정의를 기록해 동일하게 재설정해야 한다.

데이터 모델(파워피벗)을 쓰는 경우의 추가 점검 포인트는 무엇인가?

관계 무결성, 열 데이터 형식, 계산 열·측정값의 DAX 오류를 점검해야 한다. 모델 새로 고침 정책을 “파일 열 때 데이터 새로 고침”으로 설정하고, 필요 시 연결을 삭제 후 다시 추가해야 한다.

복구 후 값은 맞지만 항목 목록이 지나치게 길다. 어떻게 줄이나?

피벗 캐시의 누락 항목 보존을 “없음”으로 설정하고 전체 새로 고침을 수행해야 한다. VBA의 MissingItemsLimit = xlMissingItemsNone 로 일괄 적용해도 된다.

현장용 복구 절차 요약(체크리스트)

1. 파일 사본 생성
2. 안전 모드·애드인 배제
3. 원본 표화·머리글 정비·정의된 이름 수정
4. 보존 항목 없음, 원본 저장 옵션 활성화
5. 모두 새로 고침
6. VBA로 새 캐시 생성 후 모든 피벗 리맵
7. 연결·쿼리 재설정 또는 데이터 모델 점검
8. 불가 시 Open and Repair 또는 시트 이관

주의 : 통합문서에 보호나 디지털 서명이 걸려 있으면 복구 중 정책 위반이 발생할 수 있다. 조직의 정보보안 정책에 따라 사전 승인 절차를 준수해야 한다.

부록: 추가 VBA 스니펫

1) 모든 캐시 강제 새로 고침

  
Sub RefreshAllPivotCaches() Dim pc As PivotCache For Each pc In ThisWorkbook.PivotCaches On Error Resume Next pc.MissingItemsLimit = xlMissingItemsNone On Error GoTo 0 pc.Refresh Next pc End Sub 
  

2) 원본이 범위(Range)인 피벗을 표(Table)로 전환

  
Sub ConvertSourceToTableAndRebind() Dim ws As Worksheet, rng As Range, lo As ListObject Set ws = Worksheets("Data") Set rng = ws.Range("A1").CurrentRegion Set lo = ws.ListObjects.Add(xlSrcRange, rng, , xlYes) lo.Name = "Table1" Call RebuildPivotCachesToTable1 End Sub 
  

3) 정의된 이름에 #REF!가 있는지 탐지

  
Sub FindBrokenNames() Dim nm As Name For Each nm In ThisWorkbook.Names If InStr(1, nm.RefersTo, "#REF!", vbTextCompare) > 0 Then Debug.Print "Broken name: " & nm.Name & " | " & nm.RefersTo End If Next nm End Sub