인수인계 - 외부링크 캐시는 옛날 값을 들고 있다
연결 편집을 열어봤다. 모든 연결이 확인 상태다. 경로도 공용 폴더를 제대로 가리킨다.
그런데 참조해온 숫자가 원본과 다르다.
결론
엑셀은 참조값을 파일 안에 저장해두고, 갱신은 특정 조건에서만 한다.
엑셀이 외부값을 다루는 방식
외부 파일을 참조하면 엑셀은 그 값을 내 파일 안에 복사해 보관한다. 원본 파일이 닫혀 있어도 숫자가 보이는 이유다.
그 보관된 값은 다음 경우에만 바뀐다.
- 파일을 열 때 업데이트를 누른 경우
- 연결 편집에서 값 업데이트를 실행한 경우
- 원본 파일을 같이 열어둔 상태인 경우
반대로 말하면, 이 중 아무것도 안 하면 보관된 값이 그대로 남는다. 원본이 열 번 바뀌어도 내 파일은 모른다.
왜 업데이트를 안 누르게 되나
엑셀은 외부 참조가 있는 파일을 열 때 업데이트 여부를 묻는다. 그런데 그 창을 안 누르게 되는 상황이 여럿 있다.
| 상황 | 결과 |
|---|---|
| 급해서 사용 안 함을 눌렀다 | 그 세션 동안 옛날 값 |
| 보안 설정으로 창이 아예 안 뜬다 | 매번 옛날 값 |
| 원본이 다른 사람에게 열려 있다 | 갱신 실패, 조용히 넘어감 |
| 파일을 메일로 받아 열었다 | 보호된 보기라 갱신 안 됨 |
| 업데이트 안 함으로 설정이 저장돼 있다 | 누구 PC에서 열어도 옛날 값 |
마지막 경우가 특히 성가시다. 파일 자체에 묻지 않고 업데이트하지 않음 설정이 저장될 수 있는데, 그러면 창이 안 뜨고 아무도 이상하다고 느끼지 않는다.
세 번째도 자주 생긴다. 결산 기간에는 원본 파일을 여러 사람이 동시에 열어두기 때문에, 갱신이 조용히 실패하는 경우가 많다.
어떻게 확인하나
가장 확실한 방법은 원본과 직접 대조하는 것이다. 그런데 매달 전부 대조할 수는 없으니, 자동으로 드러나게 만들어두는 편이 낫다.
- 원본 파일에 갱신 기준일 셀을 하나 만들고, 그 값을 같이 참조해 온다
- 내 파일 상단에 그 기준일을 띄워둔다. 당월이 아니면 바로 보인다
- 결산 시작 전에 연결 편집에서 값 업데이트를 수동으로 한 번 실행한다
- 원본을 열고 있는 사람이 없는 시간에 갱신한다
- 메일로 받은 파일은 편집 사용을 먼저 누르고 작업한다
첫 번째와 두 번째가 핵심이다. 숫자는 틀려도 티가 안 나지만 날짜는 다르면 바로 눈에 띈다. 원본에 기준일을 하나 박아두고 그걸 같이 끌어오면, 갱신이 안 된 달에는 상단 날짜가 지난달로 떠 있다.
검증이라기보다 신호등을 다는 일에 가깝다. 비용은 셀 하나인데 효과는 크다.
구조를 바꾸는 쪽
근본적으로는 외부 참조를 줄이는 게 맞다. 다른 파일의 값을 수식으로 끌어오는 대신, 필요한 데이터를 복사해 와서 내 파일 안의 시트로 두는 방식이다.
손이 더 가는 것 같지만 실제로는 그렇지 않다. 붙여넣는 순간 언제 가져온 데이터인지가 명확해지고, 갱신 여부를 고민할 일이 없어진다. 데이터를 가져온 시트에 날짜만 적어두면 된다.
외부 참조는 원본이 자주 바뀌고 그 변화를 즉시 반영해야 할 때만 쓴다. 월 결산처럼 한 달에 한 번 확정치를 받는 구조에서는 복사해 오는 편이 안전하다.
남는 이야기
앞서 본 함정들과 이것의 차이가 하나 있다. 다른 함정들은 수식이 틀렸다. 이건 수식이 맞는데 값이 낡았다.
그래서 수식을 아무리 들여다봐도 안 보인다. 범위도 맞고, 조건도 맞고, 참조도 살아 있다. 틀린 건 그 수식이 가져온 값이 언제 것이냐뿐이다.
결산 파일을 점검할 때 수식의 정확성만 보는 경우가 많다. 그런데 데이터가 언제 것인가도 똑같이 중요하다. 맞는 계산식에 낡은 숫자를 넣으면 결과는 정확하게 틀린다.