경제 정보성 블로그

영업1팀의 실적을 집계하는데 숫자가 안 맞는다. 원천을 뒤져보니 같은 팀이 이렇게 적혀 있다. 영업1팀 영업 1팀 영업1 팀 영업1팀 (주)○○ 영업1팀 사람 눈에는 다 같은 팀이다. 엑셀에게는 전부 다른 글자다. 결론 표기가 하나라도 다르면 그 금액은 조용히 사라진다. 해결은 표기를 통일하는 게 아니라, 표기를 잇는 대응표를 만드는 것이다. 왜 표기가 갈라지나 데이터가 여러 경로로 들어오기 때문이다. 출처 표기 특징 회계 시스템 코드 체계에 맞춘 공식 명칭 영업 쪽 보고 파일 현장에서 부르는 약칭 수기 입력 사람마다 띄어쓰기가 다름 외부에서 받은 자료 법인명이 앞에 붙음 과거 파일 개편 전 옛 이름이 남아 있음 각 경로에서는 그 표기가 맞다. 영업팀이 자기들끼리 약칭을 쓰는 건 자연스럽고, 수기 입력에서 띄어쓰기가 흔들리는 것도 당연하다. 문제는 이 데이터들이 한 파일에서 만날 때 생긴다. 어떻게 사라지나 SUMIF는 조건과 정확히 일치하는 것만 더한다. 조건을 "영업1팀" 으로 걸면 "영업 1팀" 은 안 잡힌다. 그런데 오류가 나지 않는다. 일치하는 게 없으면 그냥 안 더할 뿐이다. 결과는 0도 아니고 에러도 아니고, 그저 조금 작은 숫자다. 원천 데이터 영업1팀 40 영업 1팀 30 영업1팀 20 집계됨 누락 집계됨 결과 60 실제는 90 30이 어디에도 안 뜬다 띄어쓰기 하나 때문에 빠진 금액은 오류 목록에도 안 올라온다. 먼저 전체 합계부터 본다 개별 항목을 들여다보기 전에 할 일이 있다. 부서별 집계의 총합과 원천 전체 합계를 비교하는 것 이다. 부서별로 다 더했는데 원천 총합보다 작다면, 그 차액이 어느 부서에도 안 잡힌 금액이다. 이 한 줄이 누락 전체를 한 번에 드러낸다. 그다음 원천에서 부서명 열만 뽑아 중복을 제거해본다. 비슷한데 미묘하게 다른 이름들이 나란히 보인다. 정렬하면 더 잘 보인다. 대응표로...

앞 글에서 부서명을 수식에 박지 말라고 했다. 그럼 어디에 둬야 하나. 답은 간단하다. 헤더 한 줄 이다. 결론 부서명은 표 맨 윗줄에 한 번만 적고, 아래 모든 수식이 그 줄을 올려다보게 한다. 그러면 개편이 와도 고칠 곳은 그 한 줄뿐이다. 어떻게 생긴 구조인가 흔히 이렇게 짠다. 각 열의 수식에 부서명이 직접 들어가 있는 형태다. 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 하나를 만들어 오른쪽과 아래로 끌면 전체가 완성된다. 모든 셀의 수식이 똑같아진다. 영업1팀 영업2팀 물류팀 헤더 한 줄 여기만 고치면 된다 = … D$4 … = … E$4 … = … F$4 … 아래로 수백 행, 전부 같은 수식 수식에는 부서명이 하나도 없다 글자를 위로 몰아두면, 아래는 전부 같은 수식이 된다. 달러 기호의 위치가 핵심이다 D$4 에서 행 앞에만 달러를 붙였다. 이게 전부를 결정한다. 표기 끌었을 때 D$4 오른쪽으로 가면 E4, F4로 따라감. 아래로는 계속 4행 $D$4 끌어도 D4 고정. 모든 열이 영업1팀만 집계 D4 아래로 가면 5행, 6행으로 밀림. 엉뚱한 셀을 봄 두 번째 실수가 특히 위험하다. 전부 고정하면 모든 부서...

사업본부 하나가 둘로 갈라진다는 공지가 내려온다. 인사와 조직도가 바뀌고, 다음 달부터 새 조직으로 보고하라는 말이 붙는다. 결산 담당 입장에서 이건 조직 문제가 아니라 파일 문제 다. 그리고 생각보다 훨씬 큰 문제다. 결론 부서명이 수식 안에 글자로 박혀 있으면, 조직이 바뀔 때마다 파일을 다시 짜야 한다. 부서는 데이터여야지 수식의 일부여서는 안 된다. 무엇이 깨지나 조직이 바뀌면 보통 이런 것들이 동시에 움직인다. 변화 파일에서 깨지는 것 부서가 둘로 갈라짐 열이 하나 늘면서 이후 열이 전부 밀림 부서가 합쳐짐 사라진 부서를 찾는 수식이 0을 반환 부서명만 변경 조건으로 쓴 글자가 안 맞아 집계 누락 소속이 이동 배부 기준 인원·면적이 바뀌는데 반영 안 됨 첫 번째가 가장 파괴적이다. 부서 하나가 둘이 되면 원천 데이터에 열이 하나 추가되는데, 그 뒤의 모든 열이 한 칸씩 오른쪽으로 밀린다. 내 파일이 열 위치로 값을 가져오고 있었다면, 그날부터 모든 부서가 옆 부서 숫자를 가져온다. 개편 전 원천 영업1팀 영업2팀 물류팀 개편 후 원천 영업1팀 영업3팀 신설 영업2팀 물류팀 열 위치로 참조 두 번째 열을 읽으면 영업3팀이 온다 열이 하나 끼어들면 그 뒤가 전부 한 칸씩 어긋난다. 개편은 월 중간에 온다 이 작업이 어려운 진짜 이유가 있다. 조직은 결산 일정에 맞춰 바뀌지 않는다. 월 중간에 바뀌면 그달은 옛 조직과 새 조직이 섞인다. 15일까지는 한 부서였다가 16일부터 둘인 데이터를 어떻게 보고할 것인가. 이건 수식의 문제가 아니라 판단의 문제다. 선택지는 보통 셋이다. 당월은 옛 조직 기준으로 마감하고 다음 달부터 새 조직 적용 당월부터 새 조직으로 하되, 개편 전 실적을 기준에 따라 나눠서 배분 당월만 양쪽을 다 만들어 비교 가능하게 둠 어느 쪽이든 먼저 정하고 기록해야 한다. 정하지 않고 시작하면 사람마다 다르게 처리하고, 다음 달에 전월 비교가 안...

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

매달 전월 파일을 복사해서 당월 파일을 만든다. 파일명을 바꾸고, 날짜를 바꾸고, 숫자를 갱신한다. 익숙한 작업이다. 그런데 수식 안의 참조 파일명은 안 바뀐다. 결론 파일을 복사하면 내용은 따라오지만 참조 대상은 따라오지 않는다. 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 + 백틱으로 수식 안 파일명을 가로로 훑는다 기준일 참조 원본의 기준연월을 같이 끌어와 상단에 띄운다 전월 대비 비교 참조...

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

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