글

결산검증 - 전월 값으로 굳힌 숫자가 두 달 전 것일 때

월 전환을 할 때 당월 열을 전월 열로 옮긴다. 이때 수식을 그대로 두면 다음 달 값으로 바뀌어버리니, 값으로 붙여넣어 고정한다. 확정된 숫자를 굳히는 것이니 맞는 방식이다. 문제는 그 굳히기를 한 번 건너뛰거나 두 번 하는 달이다. 결론 값으로 굳힌 숫자는 자기가 몇 월 것인지 말하지 않는다. 수식이면 추적이라도 되는데, 값은 그냥 숫자다. 어긋나도 흔적이 없다. 어긋남이 생기는 순간 월 전환 작업은 보통 이렇게 돌아간다. 정상 전환 전전월 8월 전월 9월 당월 10월 한 칸씩 밀린다 밀기를 건너뛴 달 전전월 8월 전월 자리에 8월 값 당월 10월 9월이 통째로 사라졌다 전월 열에 두 달 전 숫자가 앉아 있어도 화면은 똑같이 생겼다. 이런 일은 다음 상황에서 생긴다. 마감이 길어져 월 전환을 미뤘다가, 다음 달에 한 번만 밀었다 중간에 담당이 바뀌면서 전환이 이미 된 줄 알고 넘어갔다 전환은 했는데 값 붙여넣기를 안 해서, 수식이 당월 값을 따라가버렸다 한 번 더 밀어서 전월 자리에 당월 값이 들어갔다 세 번째가 특히 고약하다. 값으로 굳히지 않으면 전월 열 수식이 당월 데이터를 따라 움직여서, 전월과 당월이 같은 숫자가 된다. 왜 발견이 늦나 전월 열은 아무도 안 본다. 결산할 때 보는 건 당월 숫자고, 전월은 비교용 배경일 뿐이다. 그래서 전월 열이 틀려도 당월 숫자는 멀쩡하다. 합계도 맞고 검증도 통과한다. 틀린 건 증감 분석뿐인데, 증감은 틀려도 숫자가 아니라 해석의 영역이라 바로 안 걸린다. 전월 대비 증감이 이상해 보여도 사람은 먼저 사업을 의심한다. 왜 이번 달에 이게 늘었지, 하고 원인을 찾아 나선다. 설명을 만들어내려 하지, 전월 열 자체를 의심하지는 않는다. 어떻게 잡아내나 값으로 굳힌 숫자는 추적이 안 되니, 바깥에서 대조하는 수밖에 없다. 방법 보는 법 전월 파일과 대조 전월 파일의 당월 열과 이번 파일의 전월 열이 같은지 전월 열이 수식인지 ...
Read more »

인수인계 - 당월 파일이 전월 파일을 참조하고 있었다

매달 전월 파일을 복사해서 당월 파일을 만든다. 파일명을 바꾸고, 날짜를 바꾸고, 숫자를 갱신한다. 익숙한 작업이다. 그런데 수식 안의 참조 파일명은 안 바뀐다. 결론 파일을 복사하면 내용은 따라오지만 참조 대상은 따라오지 않는다. 10월 파일이 9월 원가 파일을 보고 있어도 엑셀은 아무 말도 하지 않는다. 복사로 만든 파일의 구조 9월 결산 파일에 이런 수식이 있다고 하자. ='[원가집계_2609.xlsx]배부'!$F$24 이 파일을 복사해 10월 파일을 만들면, 수식은 글자 하나 안 바뀐 채 따라온다. 파일 이름만 10월로 바꾸고 시작하면 겉은 10월 파일인데 속은 9월 데이터를 보고 있다. 9월 결산 원가_2609 참조 복사 10월 결산 원가_2609 참조 원가집계_2609 원가집계_2610 아무도 안 보는 10월 원가 새 파일이 만들어졌을 뿐, 참조선은 옛 자리에 그대로 묶여 있다. 여기서 앞 글들과 결정적으로 다른 점이 있다. 연결 상태가 정상이다. 9월 원가 파일은 실제로 존재한다. 경로도 맞고, 값도 읽어온다. 연결 편집을 열어도 확인 상태로 뜬다. 끊긴 게 아니니 아무 경고도 안 나온다. 왜 바로 안 걸리나 숫자가 아주 틀린 게 아니기 때문이다. 전월 원가와 당월 원가는 대개 비슷하다. 10%쯤 차이가 나도 사업상 있을 법한 변동으로 보인다. 그리고 이 결함은 월이 바뀔 때마다 한 달씩 밀린다. 11월 파일은 10월 파일을 복사해 만드니 여전히 9월을 보고 있다. 아무도 안 고치면 참조 대상은 영원히 9월에 머문다. 시간이 지날수록 차이가 벌어지는데, 그때쯤이면 원인을 찾기가 훨씬 어려워져 있다. 어떻게 잡아내나 방법 보는 법 연결 목록에서 파일명 확인 연결 편집을 열어 파일명의 연월이 당월인지 본다 수식 표시 Ctrl + 백틱으로 수식 안 파일명을 가로로 훑는다 기준일 참조 원본의 기준연월을 같이 끌어와 상단에 띄운다 전월 대비 비교 참조...
Read more »

인수인계 - 외부링크 캐시는 옛날 값을 들고 있다

연결 편집을 열어봤다. 모든 연결이 확인 상태다. 경로도 공용 폴더를 제대로 가리킨다. 그런데 참조해온 숫자가 원본과 다르다. 결론 연결이 살아 있다는 것과 값이 최신이라는 것은 다른 이야기다. 엑셀은 참조값을 파일 안에 저장해두고, 갱신은 특정 조건에서만 한다. 엑셀이 외부값을 다루는 방식 외부 파일을 참조하면 엑셀은 그 값을 내 파일 안에 복사해 보관한다. 원본 파일이 닫혀 있어도 숫자가 보이는 이유다. 그 보관된 값은 다음 경우에만 바뀐다. 파일을 열 때 업데이트를 누른 경우 연결 편집에서 값 업데이트를 실행한 경우 원본 파일을 같이 열어둔 상태인 경우 반대로 말하면, 이 중 아무것도 안 하면 보관된 값이 그대로 남는다. 원본이 열 번 바뀌어도 내 파일은 모른다. 원본 파일 8월 100 9월 120 10월 140 내 파일이 들고 있는 값 100 8월에 마지막으로 업데이트한 뒤 멈춰 있다 갱신되지 않음 연결 상태는 정상. 경로도 정상. 값만 과거에 머물러 있다. 왜 업데이트를 안 누르게 되나 엑셀은 외부 참조가 있는 파일을 열 때 업데이트 여부를 묻는다. 그런데 그 창을 안 누르게 되는 상황이 여럿 있다. 상황 결과 급해서 사용 안 함을 눌렀다 그 세션 동안 옛날 값 보안 설정으로 창이 아예 안 뜬다 매번 옛날 값 원본이 다른 사람에게 열려 있다 갱신 실패, 조용히 넘어감 파일을 메일로 받아 열었다 보호된 보기라 갱신 안 됨 업데이트 안 함으로 설정이 저장돼 있다 누구 PC에서 열어도 옛날 값 마지막 경우가 특히 성가시다. 파일 자체에 묻지 않고 업데이트하지 않음 설정이 저장될 수 있는데, 그러면 창이 안 뜨고 아무도 이상하다고 느끼지 않는다. 세 번째도 자주 생긴다. 결산 기간에는 원본 파일을 여러 사람이 동시에 열어두기 때문에, 갱신이 조용히 실패하는 경우가 많다. 어떻게 확인하나 가장 확실한 방법은 원본과 직접 대조하는 것 이다. 그런데 매달 전부 대...
Read more »

인수인계 - 외부참조가 남의 PC를 보고 있을 때

연결 편집을 열어보니 참조 경로가 이렇게 생겼다. 'C:\Users\전임자이름\Desktop\결산\[원가집계.xlsx]Sheet1'!$D$12 내 PC에 없는 경로다. 그런데 화면에는 숫자가 멀쩡히 떠 있다. 결론 참조 경로는 사람이 아니라 위치를 가리켜야 한다. 개인 PC 경로는 그 사람이 떠나는 순간 끊어진다. 그리고 끊어진 티가 안 난다. 왜 개인 경로가 들어가나 의도한 게 아니다. 엑셀이 자동으로 넣는다. 두 파일을 동시에 열어놓고 한쪽에서 다른 쪽 셀을 클릭해 수식을 만들면, 엑셀은 그 순간 그 파일이 있던 자리를 그대로 적어넣는다. 바탕화면에서 작업했으면 바탕화면 경로가 박힌다. 그 상태로 파일을 공용 폴더에 올려도 수식 안의 경로는 안 바뀐다. 만든 사람 PC에서는 계속 잘 돌아가니 아무도 모른다. 끊어져도 숫자가 보이는 이유 엑셀은 외부 파일에서 읽어온 값을 파일 안에 저장해둔다. 연결이 끊겨도 마지막으로 읽었던 숫자를 그대로 보여준다. 전임자 PC에서 연결 살아 있음 매달 갱신됨 정상 내 PC에서 연결 끊김 캐시값 표시 숫자는 보인다 몇 달 전 값으로 끊어진 연결은 오류가 아니라 정지로 나타난다. 틀렸다는 신호가 없다. 그래서 이 결함은 조용히 멈춘다. 매달 숫자가 바뀌는 항목이면 몇 달 지나 이상하다고 느끼지만, 변동이 작은 항목은 몇 년을 갈 수도 있다. 파일을 열 때 뜨는 업데이트 여부를 묻는 창이 유일한 신호인데, 그것도 끊긴 경로에서는 조용히 실패하거나 사용 안 함을 한 번 누르면 다음부터 안 뜬다. 어떻게 고치나 연결 편집 창에서 원본 변경으로 새 경로를 지정하면 된다. 다만 참조가 여러 개면 하나씩 바꾸는 게 번거롭다. 이럴 때는 찾기 및 바꾸기가 빠르다. 단계 방법 현재 경로 확인 연결 편집에서 원본 경로를 복사 일괄 변경 Ctrl+H, 찾을 내용에 옛 경로, 바꿀 내용에 새 경로 범위 지정 옵션에서 범위를 통합 문서, 찾는 위치...
Read more »

인수인계 - 인수인계 받은 엑셀에서 가장 먼저 확인할 세 가지

전임자가 쓰던 결산 파일을 물려받는다. 양식은 완성돼 있고 수식도 다 걸려 있다. 열어서 이번 달 숫자만 바꾸면 되는 것처럼 보인다. 그래서 대부분 그렇게 한다. 그리고 몇 달 뒤에 숫자가 틀어진 걸 발견한다. 결론 물려받은 파일은 쓰기 전에 한 번 뜯어봐야 한다. 확인할 것은 세 가지다. 어디를 보고 있는가, 무엇이 값인가, 무엇이 검증인가. 하나. 이 파일은 어디를 보고 있나 가장 먼저 외부 참조를 확인한다. 수식 탭의 연결 편집 기능으로 이 파일이 참조하는 다른 파일 목록이 한 번에 나온다. 여기서 자주 나오는 것들이 있다. 전임자 PC의 바탕화면 경로를 그대로 가리키고 있다 이제 쓰지 않는 옛날 폴더를 보고 있다 당월 파일인데 전월 파일을 참조하고 있다 참조하는 파일 자체가 사라졌는데 마지막 값만 남아 있다 마지막 경우가 까다롭다. 엑셀은 외부 파일이 없어도 마지막으로 읽었던 값을 캐시로 들고 있다. 그래서 화면에는 숫자가 멀쩡히 보인다. 몇 달 전 값이라는 것만 빼고. 물려받은 파일 화면에 숫자가 보인다 외부 참조 전임자 PC 경로 닿지 않는다 엑셀이 들고 있는 캐시값 마지막으로 연결됐을 때의 숫자가 그대로 표시된다 오류 표시 없음 경고 없음 갱신만 안 될 뿐 끊어진 연결은 빈칸이 아니라 옛날 숫자로 나타난다. 연결 편집 창에서 상태를 확인하면 어느 연결이 살아 있고 어느 것이 끊겼는지 나온다. 끊긴 게 있으면 그 수식이 참조하는 셀의 값은 전부 의심해야 한다. 둘. 무엇이 수식이고 무엇이 값인가 수식이어야 할 자리에 값이 박혀 있는 경우가 많다. 급한 달에 누군가 숫자를 직접 때려 넣고, 그게 다음 달 파일로 복사되면서 굳어진 것이다. 찾는 방법은 간단하다. Ctrl + ` (백틱)을 누르면 시트 전체가 값 대신 수식으로 바뀐다. 수식이 있어야 할 줄에 숫자만 덩그러니 있으면 바로 보인다. 또는 이동 옵션에서 상수만 선택하면 값이 직접 입력된 셀이 전부 선택된다. 데이터 입...
Read more »

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

매달 원천 파일을 받아서 보고용 파일에 옮긴다. 원천의 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 »