엑셀함정 - 항목 순서가 뒤바뀌어도 합계는 맞는다
매달 원천 파일을 받아서 보고용 파일에 옮긴다. 원천의 C열을 보고용 D열에 그대로 붙여넣거나, 같은 행 번호로 참조를 건다. 순서가 늘 같았으니까.
그러다 원천 쪽에서 항목 순서가 한 번 바뀐다. 보고용 파일은 그 사실을 모른다.
결론
행 번호로 가져오는 순간, 원천의 순서가 곧 내 파일의 정답이 된다.
자리가 바뀌면 무슨 일이 생기나
원천 파일에서 두 항목의 순서가 바뀌었다고 하자. 행 번호로 참조하는 보고용 파일은 여전히 3행을 A라고, 4행을 B라고 부른다. 하지만 이제 3행에는 B의 값이, 4행에는 A의 값이 들어 있다.
숫자 하나하나는 전부 원천과 일치한다. 합계도 맞는다. 대사를 해도 통과한다. 틀린 건 어떤 값에 어떤 이름표가 붙었는가뿐이다.
그리고 이게 가장 비싼 종류의 오류다. A사업부가 잘한 달에 B사업부가 칭찬받고, B사업부가 부진한 달에 A사업부가 불려간다.
왜 순서가 바뀌나
원천 파일을 만드는 쪽에서는 순서를 바꾸는 게 아무 일도 아니다.
- 정렬 기준을 이름순에서 금액순으로 바꿨다
- 조직이 바뀌어 항목이 하나 추가되거나 합쳐졌다
- 시스템에서 내려받는 조회 조건이 달라졌다
- 담당자가 보기 편하게 행을 옮겼다
모두 원천 쪽 입장에서는 정당하다. 문제는 받는 쪽 파일이 순서가 영원히 같을 거라는 가정 위에 서 있다는 것이다. 그 가정은 어디에도 적혀 있지 않다.
어떻게 잡아내나
합계는 소용없다. 대신 각 항목을 자기 자신의 과거와 비교한다.
| 신호 | 의미 |
|---|---|
| 한 항목이 급증했다 | 실제 변동일 수 있다 |
| 다른 항목이 급감했다 | 실제 변동일 수 있다 |
| 둘이 동시에, 서로의 전월값 근처로 움직였다 | 자리가 바뀌었다 |
세 번째가 결정적이다. A가 B의 평소 금액이 되고, B가 A의 평소 금액이 됐다면 사업이 그렇게 움직였을 가능성보다 행이 바뀌었을 가능성이 훨씬 크다. 금액의 크기가 아니라 짝을 이뤄 뒤바뀐 모양을 보는 것이다.
그래서 항목별 전월 대비 증감을 뽑아둘 때, 큰 순서로만 정렬하지 말고 증가 상위와 감소 상위를 나란히 놓고 보면 좋다. 짝이 보인다.
구조로 막는 법
- 원천에서 값을 가져올 때 행 번호가 아니라 항목명으로 찾는다 (SUMIF, XLOOKUP)
- 원천 파일을 통째로 붙여넣는 방식이라면, 붙여넣은 뒤 이름표 열부터 대조한다
- 보고용 파일 옆에 원천의 이름표를 한 열 같이 끌어와서, 내 이름표와 일치하는지 자동으로 표시한다
- 원천 쪽에 순서를 바꿀 때 알려달라고 요청하되, 알려줄 거라고 믿지는 않는다
- 항목별 전월 대비 증감에서 서로 반대 방향으로 비슷한 크기로 움직인 짝을 찾는다
세 번째가 가장 싼 방어다. 보고용 파일의 A사업부 줄 옆에, 원천에서 같은 위치의 이름을 끌어와 띄워두기만 하면 된다. 두 이름이 다르면 빨간색이 뜨게 한다. 순서가 바뀐 달에 바로 보인다.
남는 이야기
이 함정의 뿌리는 엑셀이 아니라 보이지 않는 약속이다.
원천 파일과 보고용 파일 사이에는 순서가 같다는 약속이 있었다. 하지만 누구도 그 약속을 한 적이 없다. 한쪽이 만든 습관을 다른 쪽이 계약으로 믿었을 뿐이다.
파일과 파일이 연결되는 곳마다 이런 약속이 숨어 있다. 열 순서, 시트 이름, 계정 표기, 단위. 좋은 결산 구조는 이 약속을 없애는 쪽으로 간다. 순서가 아니라 이름으로 찾고, 가정하는 대신 확인하는 표시를 남긴다. 상대가 바꿔도 내가 깨지지 않는 연결을 만드는 것이다.