엑셀함정 - 부서명을 헤더 한 줄로 모으면 개편 때 한 줄만 고친다
앞 글에서 부서명을 수식에 박지 말라고 했다. 그럼 어디에 둬야 하나.
답은 간단하다. 헤더 한 줄이다.
결론
그러면 개편이 와도 고칠 곳은 그 한 줄뿐이다.
어떻게 생긴 구조인가
흔히 이렇게 짠다. 각 열의 수식에 부서명이 직접 들어가 있는 형태다.
D열 =SUMIF(원천!$B$5:$B$300, "영업1팀", 원천!$F$5:$F$300)
E열 =SUMIF(원천!$B$5:$B$300, "영업2팀", 원천!$F$5:$F$300)
F열 =SUMIF(원천!$B$5:$B$300, "물류팀", 원천!$F$5:$F$300)열마다 다른 글자가 박혀 있다. 부서가 열 개면 열 가지 수식이 있는 셈이고, 행이 백 줄이면 수정할 곳이 천 군데다.
이걸 이렇게 바꾼다.
4행 D4: 영업1팀 E4: 영업2팀 F4: 물류팀 ← 헤더
D5 =SUMIF(원천!$B$5:$B$300, D$4, 원천!$F$5:$F$300)수식에서 글자가 사라지고 D$4가 들어갔다. 이제 D5 하나를 만들어 오른쪽과 아래로 끌면 전체가 완성된다. 모든 셀의 수식이 똑같아진다.
달러 기호의 위치가 핵심이다
D$4에서 행 앞에만 달러를 붙였다. 이게 전부를 결정한다.
| 표기 | 끌었을 때 |
|---|---|
D$4 | 오른쪽으로 가면 E4, F4로 따라감. 아래로는 계속 4행 |
$D$4 | 끌어도 D4 고정. 모든 열이 영업1팀만 집계 |
D4 | 아래로 가면 5행, 6행으로 밀림. 엉뚱한 셀을 봄 |
두 번째 실수가 특히 위험하다. 전부 고정하면 모든 부서 열에 같은 부서 숫자가 들어간다. 그런데 합계를 보면 어느 한 부서 값의 배수라 크기가 그럴듯해서, 바로 안 걸린다.
원천 범위는 반대로 $B$5:$B$300처럼 완전히 고정한다. 그건 어느 열에서 보든 같은 자리여야 하니까.
개편이 왔을 때
이 구조에서는 작업이 이렇게 끝난다.
- 부서명 변경 — 헤더 글자만 고친다. 아래 수식은 손댈 것 없음
- 부서 추가 — 열 하나를 삽입하고 헤더에 이름을 적은 뒤, 옆 열 수식을 복사
- 부서 통합 — 두 열을 하나로 줄이고 헤더 수정
전부 몇 분이다. 중요한 건 수식을 읽을 필요가 없다는 것이다. 수식이 어떻게 생겼는지 몰라도 헤더만 바꾸면 되기 때문에, 파일을 처음 보는 사람도 할 수 있다.
인수인계 관점에서 이게 크다. 앞 글들에서 물려받은 파일의 문제를 계속 다뤘는데, 이런 구조로 짜두면 애초에 물려줄 게 적어진다.
한 걸음 더
- 부서명은 헤더 행에만 적고, 모든 수식은 그 행을 D$4 형태로 참조한다
- 헤더 행 자체를 부서 목록 시트에서 끌어오면, 고칠 곳이 시트 하나로 더 줄어든다
- 헤더 셀에 색을 지정해 여기만 손으로 고치는 자리라는 걸 표시한다
- 수식을 만들 때 첫 셀 하나만 제대로 만들고 나머지는 끌어서 채운다
- 다 채운 뒤 맨 오른쪽 열 하나를 열어 참조가 제대로 따라갔는지 확인한다
두 번째가 다음 단계다. 부서 목록을 별도 시트에 두고 각 집계표의 헤더가 그 목록을 참조하면, 파일 전체에서 부서명이 적힌 곳이 딱 한 군데가 된다. 집계표가 다섯 개든 열 개든 상관없다.
다섯 번째는 간단하지만 자주 빠진다. 끌어서 채우면 다 된 것 같지만, 중간에 한 열이 어긋나 있는 경우가 있다. 양 끝만 확인하면 2초다.
남는 이야기
이 방식의 본질은 변하는 것과 변하지 않는 것을 분리하는 일이다.
계산 방법은 거의 안 바뀐다. 부서별로 금액을 합친다는 논리는 조직이 어떻게 바뀌어도 그대로다. 반면 부서 이름은 계속 바뀐다.
그런데 이 둘을 같은 셀에 섞어두면, 자주 바뀌는 쪽 때문에 안 바뀌는 쪽까지 매번 건드리게 된다. 그러다 멀쩡하던 계산 논리가 깨진다.
그래서 좋은 파일 구조는 바뀔 것을 한곳에 모아둔다. 부서명, 기준 연월, 배부율 같은 것들이다. 이것들만 따로 모아두면 유지보수는 그 영역만 보면 되고, 나머지는 건드릴 일이 없어진다.