글

엑셀함정 - 항목 순서가 뒤바뀌어도 합계는 맞는다

매달 원천 파일을 받아서 보고용 파일에 옮긴다. 원천의 C열을 보고용 D열에 그대로 붙여넣거나, 같은 행 번호로 참조를 건다. 순서가 늘 같았으니까. 그러다 원천 쪽에서 항목 순서가 한 번 바뀐다. 보고용 파일은 그 사실을 모른다. 결론 값은 위치가 아니라 이름으로 가져온다. 행 번호로 가져오는 순간, 원천의 순서가 곧 내 파일의 정답이 된다. 자리가 바뀌면 무슨 일이 생기나 원천 파일에서 두 항목의 순서가 바뀌었다고 하자. 행 번호로 참조하는 보고용 파일은 여전히 3행을 A라고, 4행을 B라고 부른다. 하지만 이제 3행에는 B의 값이, 4행에는 A의 값이 들어 있다. 원천 파일 (순서가 바뀜) 3행 B사업부 80 4행 A사업부 20 5행 C사업부 50 합계 150 행 번호로 참조 보고용 파일 A사업부 80 B사업부 20 C사업부 50 합계 150 이름표와 값이 엇갈렸다 합계는 같다 값은 하나도 틀리지 않았다. 틀린 건 각 값이 앉은 자리다. 숫자 하나하나는 전부 원천과 일치한다. 합계도 맞는다. 대사를 해도 통과한다. 틀린 건 어떤 값에 어떤 이름표가 붙었는가 뿐이다. 그리고 이게 가장 비싼 종류의 오류다. A사업부가 잘한 달에 B사업부가 칭찬받고, B사업부가 부진한 달에 A사업부가 불려간다. 왜 순서가 바뀌나 원천 파일을 만드는 쪽에서는 순서를 바꾸는 게 아무 일도 아니다. 정렬 기준을 이름순에서 금액순으로 바꿨다 조직이 바뀌어 항목이 하나 추가되거나 합쳐졌다 시스템에서 내려받는 조회 조건이 달라졌다 담당자가 보기 편하게 행을 옮겼다 모두 원천 쪽 입장에서는 정당하다. 문제는 받는 쪽 파일이 순서가 영원히 같을 거라는 가정 위에 서 있다는 것이다. 그 가정은 어디에도 적혀 있지 않다. 어떻게 잡아내나 합계는 소용없다. 대신 각 항목을 자기 자신의 과거와 비교한다. 신호 의미 한 항목이 급증했다 실제 변동일 수 있다 다른 항목이 급...
Read more »

결산검증 - 오류 셀 0개가 안전을 뜻하지 않는 이유

결산 파일을 넘기기 전에 오류 검사를 돌린다. 초록색 삼각형도 없고 #REF! 도 없다. 통과다. 그런데 지금까지 본 함정 여섯 개 중에 오류 표시가 뜬 것은 하나도 없었다. 결론 엑셀의 오류 검사는 수식이 돌아가는지만 본다. 수식이 맞는지는 보지 않는다. 그건 사람이 설계해야 할 영역이다. 엑셀이 잡아주는 것 엑셀이 경고를 띄우는 건 계산 자체가 불가능한 상황 이다. 참조하던 셀이 삭제됐다 ( #REF! ) 숫자여야 할 자리에 글자가 있다 ( #VALUE! ) 0으로 나눴다 ( #DIV/0! ) 찾는 값이 없다 ( #N/A ) 인접한 셀이 범위에서 빠진 것 같다 (초록 삼각형) 공통점이 있다. 전부 엑셀이 답을 못 내는 경우 다. 답을 낼 수 있으면 그 답이 맞는지는 묻지 않는다. 그래서 이런 것들은 전부 통과한다 무슨 일이 일어났나 엑셀이 보는 것 열 전체 참조로 같은 계정을 두 번 셌다 정상적인 덧셈 검증줄에 수식 대신 값이 들어 있다 정상적인 숫자 범위가 옆 블록을 한 칸 먹었다 정상적인 범위 새 항목이 소계에서 빠졌다 정상적인 범위 이름표와 값이 엇갈렸다 정상적인 참조 배부 기준을 잘못 걸었다 정상적인 곱셈 엑셀 입장에서는 아무 문제가 없다. 시킨 대로 계산했을 뿐이다. 틀린 건 시킨 내용이다. 엑셀이 보는 영역 계산이 가능한가 참조가 살아 있는가 사람이 봐야 할 영역 범위가 맞는가 무엇을 세고 있는가 구조가 봐야 할 영역 사람이 놓쳐도 걸리게 되어 있는가 오류 셀 0개는 왼쪽 칸만 통과했다는 뜻이다. 결산 사고는 거의 전부 가운데와 오른쪽 칸에서 난다. 그럼 무엇으로 대신하나 오류 검사가 못 보는 자리를 메우는 건 결국 다른 경로로 한 번 더 계산해보는 것 이다. 같은 수식을 다시 보는 건 도움이 안 된다. 틀린 수식은 몇 번을 봐도 같은 답을 낸다. 오류 검사를 대신하는 다섯 가지 다른 파일과 대사한다 — 내 파일 안에서만 맞춰보면 같이 틀린 걸 못 ...
Read more »

엑셀함정 - SUM 범위가 옆 블록을 한 칸 먹을 때

합계 수식은 대개 드래그로 만든다. 시작 셀을 잡고 끝까지 끌어내린 다음 엔터. 그때 한 칸을 더 잡거나 덜 잡는 일이 생긴다. 덜 잡으면 언젠가 티가 난다. 더 잡으면 오래 안 걸린다. 결론 SUM 범위와 블록 경계 사이에는 빈 행을 최소 한 줄 둔다. 경계가 붙어 있으면 한 칸의 실수가 다른 블록의 값을 끌어온다. 왜 한 칸이 위험한가 관리회계 시트는 블록이 세로로 이어진다. 매출 블록 아래 원가 블록, 그 아래 판관비 블록. 각 블록 끝에는 소계가 있다. 이때 블록과 블록이 딱 붙어 있으면, 소계 범위가 한 칸 아래로 밀리는 순간 다음 블록의 첫 줄 을 끌어온다. 블록이 붙어 있을 때 매출 항목 1 매출 항목 2 원가 항목 1 매출 소계 범위가 한 칸 아래로 밀리면 원가가 매출에 섞인다 빈 행을 뒀을 때 매출 항목 1 매출 항목 2 빈 행 매출 소계 한 칸 밀려도 빈 칸을 더할 뿐이라 값이 안 변한다 빈 행 한 줄이 실수를 흡수한다. 완충 장치다. 결과는 숫자가 조금 커지는 것뿐이다. 오류 표시는 없다. 그리고 소계가 커졌으니 총계도 같이 커지는데, 총계 역시 소계를 더한 값이라 내부적으로는 앞뒤가 맞는다. 더 잡는 실수가 덜 잡는 실수보다 나쁜 이유 범위를 덜 잡으면 항목 하나가 빠진다. 그 항목의 금액이 크면 합계가 눈에 띄게 줄어서 대사에서 걸린다. 범위를 더 잡으면 다르다. 옆 블록의 첫 줄 하나가 딸려올 뿐이라 증가폭이 작다. 대사를 다른 파일과 하는 게 아니라 이 파일 안에서만 한다면, 소계도 총계도 같이 늘어서 아무 데서도 안 걸린다. 그리고 이 실수는 수식을 복사할 때 옆으로 번진다. 한 열에서 범위를 잘못 잡고 오른쪽으로 채우면 모든 월, 모든 사업부에 같은 오류가 퍼진다. 이때는 열끼리 비교해도 안 걸린다. 전부 똑같이 틀려 있으니까. 어떻게 찾아내나 엑셀에 내장된 기능으로 대부분 잡힌다. 방법 쓰는 법 참조되는 셀 추적 수식 탭에서 소계 셀을 ...
Read more »

엑셀함정 - 합계식에서 항목 하나가 빠져도 숫자는 나온다

수식을 처음 짤 때는 대개 정확하다. 항목이 여덟 개면 여덟 개를 다 넣는다. 문제는 그다음 달이다. 항목이 하나 늘어난다. 결론 합계 수식은 만든 시점이 아니라 항목이 늘어난 시점에 틀린다. 그래서 수식을 검증하는 게 아니라, 항목 수와 수식을 함께 검증해야 한다. 행을 끼워 넣으면 어떻게 되나 엑셀은 똑똑해서 대부분 알아서 따라온다. 하지만 어디에 끼워 넣느냐 에 따라 결과가 갈린다. 범위 안쪽에 삽입 항목 1 새 항목 항목 2 항목 3 소계 범위가 자동으로 넓어진다 범위 끝에 추가 항목 1 항목 2 항목 3 새 항목 소계 범위 밖이라 빠진다 같은 삽입인데 위치 하나로 결과가 갈린다. 사람은 이 차이를 화면에서 구분하지 못한다. 범위 중간에 행을 끼우면 엑셀이 범위를 자동으로 늘려준다. 그런데 마지막 항목과 소계 사이에 행을 추가하면, 엑셀은 그 행을 범위 밖으로 본다. 새로 만든 줄이 소계에 안 들어간다. 화면에서는 둘이 똑같이 생겼다. 새 줄이 목록 안에 얌전히 들어가 있고, 금액도 제대로 적혀 있고, 소계 줄도 바로 아래에 있다. 다른 건 수식 안의 숫자뿐이다. 덧셈으로 짠 수식은 더 위험하다 =SUM(D5:D12) 대신 =D5+D6+D7+D8 처럼 항목을 하나씩 더한 수식이 있다. 항목 성격이 달라 몇 개만 골라 더해야 할 때 이렇게 짠다. 이 수식은 행이 추가돼도 절대 자동으로 늘어나지 않는다. 어디에 끼워 넣든 마찬가지다. 그리고 수식이 길어서 어떤 셀이 빠졌는지 눈으로 세기 어렵다. 실무에서 이런 수식은 대개 예외 처리 때문에 생긴다. 특정 항목만 빼야 해서, 또는 일부만 다른 계산을 해야 해서. 그런데 그 예외를 만든 사람은 나중에 기억하지 못하고, 물려받은 사람은 그게 예외인지 실수인지 모른다. 어떻게 잡아내나 이건 수식을 열어보는 것보다 세는 게 빠르다. 방법 보는 법 건수 카운트 소계 옆에 COUNT를 같은 범위로 하나 더 건다. 항목 수와 ...
Read more »

결산검증 - 합계는 맞는데 틀린 엑셀

결산 파일을 검증할 때 가장 먼저 보는 건 합계다. 소계를 다 더한 값과 총계가 일치하면 일단 안심한다. 대사도 맞고, 오류 셀도 없다. 그런데 합계가 맞는다는 사실은 생각보다 적은 것을 보장한다. 결론 합계 일치는 통과 조건이 아니라 최소 조건이다. 합계는 맞는데 안이 틀린 경우가, 합계가 안 맞는 경우보다 훨씬 위험하다. 함께 읽기 — SUMIF에 $C:$C 를 쓰면 안 되는 이유 · 검증줄이 자기 자신을 보고 있었다 합계가 맞아도 틀릴 수 있는 세 가지 합계는 개별 값들의 합이다. 그래서 개별 값이 잘못되어도 서로 상쇄되면 합계는 그대로다. 원래 값 A 30 B 50 C 20 합계 100 틀린 값 A 35 B 45 C 20 합계 100 A가 5 늘고 B가 5 줄었다 합계는 그대로 사업부별 손익은 뒤집혔는데 전사 합계는 한 자리도 움직이지 않는다. 이런 상쇄가 실제로 어떻게 생기는지 세 가지만 보자. 하나. 분류가 잘못된 경우 어떤 거래가 A사업부가 아니라 B사업부로 잡혔다. 전사 합계는 한 원도 안 바뀐다. 그런데 사업부별 손익은 달라졌고, 그 숫자로 경영진이 판단을 한다. 합계 대사만 하는 검증은 이걸 절대 못 잡는다. 구조상 잡을 수가 없다. 둘. 배부 결과만 맞춘 경우 간접비를 배부했는데 배부 후 총액이 배부 전 총액과 같으면 통과로 본다. 배부는 나누는 작업이니 총액이 같은 건 당연하다. 총액이 같다는 건 배부가 실행됐다는 뜻이지, 제대로 나뉘었다는 뜻이 아니다. 기준을 잘못 걸어도, 특정 부서가 통째로 빠져도, 총액은 늘 맞는다. 셋. 오타가 두 번 난 경우 한 곳에서 잘못 더하고 다른 곳에서 잘못 빼면 합계는 돌아온다. 드물 것 같지만, 같은 사람이 같은 날 같은 파일을 고칠 때 의외로 자주 생긴다. 한 곳을 고치다 균형이 깨져서 다른 곳을 억지로 맞추는 경우다. 그래서 무엇을 봐야 하나 합계 대신 보는 게 아니라, 합계를 본 다음에 봐야 하...
Read more »

결산검증 - 검증줄이 자기 자신을 보고 있었다

결산 파일에는 대개 맨 아랫줄에 검증줄이 있다. 다른 파일에서 뽑은 숫자와 이 파일의 집계값을 나란히 놓고, 차이를 계산하는 줄이다. 차이가 0이면 통과다. 그래서 우리는 그 줄만 본다. 0이면 넘어간다. 결론 검증줄은 반드시 다른 파일을 참조해야 검증이다. 자기 값을 자기가 검증하면 그건 검증이 아니라 복사다. 차이가 0인데 검증이 안 된 상태 인수인계 받은 파일을 열었는데 검증줄이 전부 0이었다. 몇 달째 0이었다. 그런데 셀을 클릭해 보니 수식이 아니라 값 이었다. 누군가 대사값 자리에 바로 위 집계값을 그대로 복사해 넣어둔 것이다. 같은 숫자를 두 번 쓰고 빼면 당연히 0이 나온다. 제대로 된 검증 내 파일 집계값 다른 파일 대사값 차이 0 = 두 파일이 일치 가짜 검증 내 파일 집계값 같은 값 복사 수식 아님 차이 0 = 아무것도 검증 안 됨 두 화면은 똑같이 0을 보여준다. 다른 것은 그 0이 어디서 왔는가뿐이다. 여기가 이 함정의 핵심이다. 화면에 보이는 결과가 정상과 완전히 똑같다. 오류도 없고, 경고도 없고, 색깔도 같다. 0이라는 숫자 하나만 놓고는 진짜 검증인지 복사인지 구분할 방법이 없다. 왜 이런 일이 생기나 악의로 하는 사람은 없다. 대개 이렇게 생긴다. 마감이 급한 날, 검증줄에 차이가 난다. 원인을 찾을 시간이 없다. 일단 숫자는 다른 데서 이미 맞춰봤으니 넘어가자 하고, 차이를 0으로 만들어놓는다. 다음 달에 제대로 보려고 한다. 그런데 다음 달은 그 파일을 복사해서 쓴다. 검증줄도 같이 복사된다. 그렇게 몇 달이 지나면 아무도 그 줄이 가짜라는 걸 모른다. 어떻게 찾아내나 검증줄을 한 줄씩 클릭해서 수식 입력줄을 보면 된다. 다만 파일이 스무 개면 그렇게 못 한다. 순서를 정해두는 편이 빠르다. 확인 정상이라면 검증 셀이 수식인가 값인가 수식이어야 한다 그 수식이 무엇을 참조하는가 다른 파일 또는 다른 시트 차이가 항상 정확히 0인가 가끔은...
Read more »

엑셀함정 - SUMIF에 $C:$C 를 쓰면 안 되는 이유

관리회계 파일은 매달 같은 양식으로 돌아간다. 수식은 한 번 잘 짜두면 몇 년을 쓴다. 그래서 조건 범위를 열 전체로 잡는 습관이 생긴다. $C:$C 로 걸어두면 원천 데이터가 몇 행이 되든 알아서 잡히니까 편하다. 그러다 어느 달, 합계가 조금 커진다. 오류 표시는 없다. 결론 조건 범위와 합계 범위는 반드시 행을 한정한다. $C:$C 가 아니라 $C$6:$C$182 로 쓴다. 왜 오류 표시가 나지 않는가 SUMIF가 열 전체를 훑는다는 건, 그 열에 있는 모든 것 을 본다는 뜻이다. 문제는 관리회계 원천 시트가 대개 한 덩어리가 아니라는 데 있다. 위쪽에는 계정별 실적이 있고, 아래쪽에는 배부 후 재집계 블록이 따로 붙어 있다. 검증용 대사 블록이 더 아래에 있기도 하다. 같은 시트 안에 성격이 다른 표가 두세 개 들어앉아 있는 구조다. 이때 위쪽 실적 블록에 판관비라는 계정이 있고, 아래쪽 재집계 블록에도 판관비라는 줄이 있으면, 열 전체를 조건으로 잡은 수식은 둘 다 더한다. 원천 시트 · C열 실적 블록 판관비 그 밖의 계정들 재집계 블록 판관비 배부 후 다시 집계한 값 $C$6:$C$182 실적 블록만 센다 $C:$C 두 블록을 다 센다 같은 계정명이 두 번 등장하는 순간, 열 전체 참조는 조용히 두 배를 센다 =SUMIF($C:$C, "판관비", $F:$F) → 위아래 두 블록을 다 센다 =SUMIF($C$6:$C$182, "판관비", $F$6:$F$182) → 실적 블록만 센다 여기서 무서운 건 결과가 #N/A 도 #VALUE! 도 아니라는 점이다. 숫자가 그냥 커진다. 엑셀의 오류 검사는 아무것도 잡아내지 못한다. 오류 셀 0개인데 답이 틀린 상태 가 된다. 그리고 이 함정은 처음부터 터지지 않는다. 원천 시트에 재집계 블록이 나중에 추가되는 날, 몇 달 잘 돌던 수식이 조용히 두 배를 세기 시작한다. 어떻게...
Read more »