지난달 파일과 이번 달 파일을 나란히 띄워 놓고 눈으로 훑어 본 적이 있다면, 서른 줄쯤에서 집중력이 떨어지는 경험도 같이 했을 겁니다. 두 시트가 어디서 다른지는 수식 세 개면 끝납니다.
먼저 정리할 것이 하나 있습니다. 「다르다」는 말이 실제로는 세 가지 서로 다른 일을 가리킨다는 점입니다. 한쪽에만 있는 줄, 다른 쪽에만 있는 줄, 양쪽에 다 있는데 값이 어긋난 줄. 셋은 찾는 방법이 다릅니다.
이 글은 그 셋을 각각 잡아내는 수식과, 실무 데이터에서 그 수식이 헛도는 자리를 다룹니다. 예제 파일을 같이 두었으니 값을 맞춰 보면서 따라가면 됩니다.
「다르다」가 가리키는 세 가지
찾는 것이 다르면 수식도 다르다
비교를 시작하기 전에 무엇을 찾는지부터 정해야 합니다. 이 단계를 건너뛰면 수식을 하나 만들어 놓고 왜 원하는 게 안 나오는지 붙들고 있게 됩니다.
| 찾는 것 | 쓰는 함수 | 어디에 넣나 |
|---|---|---|
| 이번 달에만 있는 줄 | COUNTIF | 이번 달 시트의 빈 열 |
| 지난달에만 있는 줄 | COUNTIF | 지난달 시트의 빈 열 |
| 양쪽에 있는데 값이 다른 줄 | VLOOKUP과 빼기 | 이번 달 시트의 빈 열 두 개 |
세 가지를 한 수식으로 묶으려는 시도가 처음에는 자연스러워 보이는데, 결과가 나와도 어느 줄이 왜 걸린 것인지 다시 뜯어봐야 해서 시간이 더 듭니다. 열을 셋으로 나눠 두면 필터 한 번으로 원하는 줄만 남길 수 있습니다.
한쪽에서만 보면 절반을 놓친다
이번 달 시트에만 수식을 넣으면 새로 생긴 줄은 잡히는데 사라진 줄은 안 잡힙니다. 사라진 줄은 이번 달 시트에 아예 없으니 셀 자리도 없습니다. 첫 두 줄이 서로 다른 시트를 가리키는 이유가 여기 있습니다.
셋 중에 무엇이 급한지는 일에 따라 다릅니다. 정산이라면 값이 어긋난 줄이 먼저고, 명단 관리라면 빠진 줄이 먼저입니다. 급한 것부터 만들어 두고 나머지는 나중에 붙여도 됩니다.
세 번째는 방향이 하나여도 됩니다. 양쪽에 다 있는 줄이라 어느 쪽에서 봐도 같은 줄이 잡히기 때문입니다. 이번 달 시트에서 한 번만 돌리면 끝납니다. 대신 양쪽에 다 있는 줄만 대상이라 한쪽에만 있는 줄은 이 열에서 비워 둡니다.
기준이 될 열을 먼저 정한다
키가 없으면 비교가 성립하지 않는다
두 시트를 견주려면 어느 줄과 어느 줄이 같은 줄인지 판정할 기준이 있어야 합니다. 거래처 코드, 사번, 주문 번호처럼 줄마다 하나씩 붙어 있고 겹치지 않는 열입니다.
예제 파일에서는 코드 열이 그 역할을 합니다. 이름이 아니라 코드를 쓰는 이유는 이름이 바뀌기 때문입니다. 상호가 바뀐 거래처는 이름으로 견주면 사라진 줄 하나와 새로 생긴 줄 하나로 잡혀 버립니다.
1키가 겹치는지 먼저 센다
한 시트 안에서 같은 코드가 두 번 나오면 그 열은 키가 아닙니다. 코드 열을 선택하고 고유 개수와 전체 줄 수가 같은지 보면 바로 판가름이 납니다.
2키가 없으면 만들어 쓴다
줄을 하나로 지목할 열이 없으면 두세 열을 이어 붙여 만듭니다. 날짜와 거래처와 품목을 이어 붙인 값이 줄마다 유일하면 그게 키입니다. 양쪽 시트에 똑같은 방식으로 만들어야 합니다.
3범위를 고정해 둔다
아래로 끌어 채울 수식이라 비교 대상 범위에는 달러 기호를 붙여 잠급니다. 이걸 빼먹으면 아래로 갈수록 범위가 밀려 뒤쪽 줄이 엉뚱하게 잡힙니다.
키 열이 정해지면 나머지는 기계적으로 흘러갑니다. 반대로 키가 흔들리는 상태로 수식을 먼저 쓰면 나온 결과를 믿을 수가 없습니다.
시트 이름에 공백이나 기호가 들어 있으면 수식에서 작은따옴표로 감싸야 합니다. 이름을 직접 치지 말고 마우스로 그 시트의 범위를 끌어 잡으면 엑셀이 알아서 붙여 줍니다.
순서대로 해 보기
열 세 개를 붙이면 끝난다
예제 파일의 이번 달 시트에는 다섯 줄, 지난달 시트에도 다섯 줄이 들어 있습니다. 이번 달에만 있는 코드가 하나, 지난달에만 있는 코드가 하나, 금액이 어긋난 줄이 하나입니다. 수식을 넣으면 그 셋이 그대로 드러납니다.
1새로 생긴 줄 표시하기
이번 달 시트의 빈 열에 COUNTIF로 지난달 코드 범위를 세어 보고, 0이면 신규라고 적게 합니다. 예제에서는 마지막 줄 하나만 신규로 뜹니다.
2지난달 값을 끌어오기
그 옆 열에 VLOOKUP으로 지난달 금액을 가져옵니다. 없는 코드에서 오류가 뜨니 IFERROR로 감싸 없음이라고 적게 해 두면 화면이 깔끔합니다.
3차이를 빼서 보기
세 번째 열에서 이번 달 금액에서 방금 가져온 값을 뺍니다. 0이 아닌 줄이 값이 어긋난 줄입니다. 예제에서는 한 줄이 마이너스 50으로 나옵니다.
여기까지가 이번 달 시트에서 하는 일입니다. 사라진 줄은 아직 안 잡혔으니, 지난달 시트로 건너가 같은 방식으로 COUNTIF를 한 번 더 돌립니다. 이번에는 이번 달 코드 범위를 세는 방향입니다.
세 열의 순서는 바꿔도 되지만 붙어 있는 편이 낫습니다. 나중에 필터를 걸 때 한 덩어리로 잡힙니다.
양쪽이 끝나면 필터를 걸어 신규와 사라짐, 그리고 차이가 0이 아닌 줄만 남겨 놓습니다. 다섯 줄짜리 예제에서는 필터가 굳이 필요 없지만 수백 줄이면 이 단계가 전부입니다.
이런 데이터라면 헛돈다
수식은 맞는데 결과가 이상할 때
교과서 예제는 잘 되는데 회사 파일에서 안 되는 이유는 대개 데이터 모양에 있습니다. 아래 다섯 가지가 실제로 자주 걸리는 자리입니다.
| 데이터가 이러면 | 이렇게 어긋난다 | 먼저 할 일 |
|---|---|---|
| 코드 뒤에 공백이 붙었다 | 멀쩡한 줄이 신규로 잡힌다 | 양쪽 코드 열의 앞뒤 공백을 떼어 낸다 |
| 한쪽은 숫자, 한쪽은 글자 | 같은 코드인데 못 찾는다 | 양쪽을 한 종류로 맞춘다 |
| 같은 코드가 두 번 나온다 | 첫 줄만 잡히고 나머지는 무시된다 | 중복을 합치거나 키를 다시 만든다 |
| 금액에 소수점이 숨어 있다 | 차이가 0으로 안 떨어진다 | 빼기 결과를 ROUND로 감싼다 |
| 병합된 셀이 섞여 있다 | 빈칸으로 읽혀 줄이 밀린다 | 병합을 풀고 값을 채워 넣는다 |
공통점은 하나입니다. 다섯 가지 모두 수식이 아니라 데이터 쪽 문제라, 수식을 고쳐서는 해결되지 않습니다. 결과가 이상하면 수식부터 뜯지 말고 키 열을 먼저 봅니다.
키 열만 따로 만들어 두면 편하다
원본 코드 열을 건드리지 않고, 공백을 떼고 종류를 맞춘 값을 빈 열에 하나 더 만들어 그 열로 비교하는 방식입니다. 양쪽 시트에 같은 방식으로 한 열씩 붙여 두면 원본은 그대로 남고 비교만 깨끗해집니다.
첫 줄이 가장 흔합니다. 시스템에서 내려받은 파일은 코드 뒤에 눈에 안 보이는 공백이 붙어 나오는 일이 잦고, 화면에서는 두 값이 똑같아 보이니 원인을 찾는 데 시간이 걸립니다.
세 번째 줄은 결과가 조용히 틀리는 쪽이라 더 위험합니다. 오류도 안 나고 숫자도 그럴듯하게 나오는데, 같은 코드가 두 번 있는 줄에서 뒤쪽 값이 통째로 무시된 채 넘어갑니다.
네 번째 줄도 의외로 많습니다. 화면에는 정수로 보이는데 속에 소수점 아래가 남아 있으면 빼기 결과가 아주 작은 수로 떨어집니다. 0이 아니니 다른 줄로 잡히고, 눈으로 보면 같은 값이라 한참 헤매게 됩니다.
세 줄짜리 요약 숫자
보고하기 전에 먼저 보는 숫자
줄마다 표시를 해 두면 눈으로 세게 됩니다. 몇 건인지는 수식이 세는 편이 빠르고, 이 숫자 셋이 있으면 비교가 제대로 돌았는지도 같이 확인됩니다.
예제 파일에서 나오는 값
새로 생긴 줄 1건 · 사라진 줄 1건 · 금액이 어긋난 줄 1건.
다섯 줄짜리 예제라 손으로 세어도 같은 값이 나옵니다. 수식이 이 셋과 다른 숫자를 내면 수식이 아니라 데이터를 봐야 한다는 신호입니다.
숫자 셋을 시트 맨 위에 고정해 두면 파일을 다시 열었을 때 상태가 한눈에 들어옵니다. 자료가 바뀌어도 그 자리에서 다시 계산되니 비교를 처음부터 하지 않아도 됩니다.
건수를 세는 일 자체는 보조 열이 있으면 COUNTIF 한 줄로도 됩니다. 신규라고 적힌 칸을 세고, 차이 열에서 0이 아닌 칸을 세면 끝입니다.
세 숫자를 모두 SUMPRODUCT로 만들 수 있습니다. COUNTIF의 결과 묶음을 그대로 세는 방식이라 보조 열 없이도 돌아가고, 두 시트 어느 쪽에서 돌리든 같은 자리에 값이 남습니다.
새로 생긴 줄과 사라진 줄의 건수가 똑같이 나오면 한 번 더 봐야 합니다. 정말로 하나 빠지고 하나 생긴 경우도 있지만, 코드 표기가 바뀐 같은 거래처가 둘로 갈린 경우도 같은 모양으로 나옵니다.
숫자가 예상과 다르면 순서가 정해져 있습니다. 키 열에 공백이 붙었는지, 한쪽이 글자인지, 중복이 있는지를 차례로 봅니다. 앞 절의 표가 그 순서 그대로입니다.
색으로 바로 보이게
수식 대신 서식으로 표시하는 방법
보조 열을 늘리기 싫으면 조건부 서식으로 같은 일을 할 수 있습니다. 규칙에 수식을 넣고, 그 수식이 참이 되는 줄에 색을 칠하게 하는 방식입니다.
보조 열 방식과 견주면 장단이 뚜렷합니다. 색은 표를 그대로 두면서 눈에 띄게 해 주지만, 몇 건인지 세거나 걸린 줄만 골라내는 일은 못 합니다. 두 방식을 같이 쓰는 경우가 실제로는 가장 많습니다.
주의 — 규칙 안의 참조는 고정 방식이 반대로 걸리기 쉽습니다
적용 범위의 첫 셀 기준으로 수식을 쓰고, 열 전체를 칠하려면 열 문자에만 달러 기호를 붙입니다. 이걸 반대로 걸면 첫 줄만 맞고 아래로 갈수록 엉뚱한 줄에 색이 들어갑니다.
색은 두 가지면 충분합니다. 한쪽에만 있는 줄과 값이 어긋난 줄을 다른 색으로 두고, 나머지는 그냥 둡니다. 세 가지가 넘어가면 색을 다시 확인하느라 눈이 더 느려집니다.
규칙을 넣을 때는 적용 범위를 먼저 잡아 두는 편이 편합니다. 범위를 선택한 상태에서 규칙을 만들면 그 범위가 그대로 들어가, 나중에 적용 범위를 다시 고칠 일이 줄어듭니다.
인쇄해서 돌릴 문서라면 색만으로는 부족합니다. 흑백으로 뽑으면 두 색이 같은 회색으로 나오니, 보조 열의 글자 표시를 같이 남겨 두는 쪽이 안전합니다. 색과 글자를 같이 두면 화면으로 보는 사람과 종이로 보는 사람이 같은 것을 봅니다. 파일을 그대로 넘길 때도 받는 쪽이 규칙을 지우지 않는 한 색은 따라갑니다.
훑는 순서
위에서부터 하면 되돌아갈 일이 없다
아래 그림은 비교가 어디서 걸리는지를 한 장에 모은 것입니다. 키 열, 잠가 둔 범위, 그리고 차이 열입니다.
위에서부터 훑는다
□ 무엇을 찾는지 셋 중에서 정했나
□ 키로 쓸 열이 양쪽에 다 있고 중복이 없나
□ 양쪽 키 열의 앞뒤 공백을 떼어 냈나
□ 양쪽 키가 같은 종류인가
□ 비교 범위에 달러 기호를 붙여 잠갔나
□ 사라진 줄을 잡으려고 반대 방향도 돌렸나
□ 요약 숫자 셋이 손으로 센 값과 맞나
앞의 네 줄은 데이터를 손보는 일이고 뒤의 세 줄은 수식을 확인하는 일입니다. 순서가 뒤집히면 고친 데이터로 수식을 다시 돌리게 되니, 위에서부터 차례로 내려가는 편이 결국 빠릅니다.
여섯 번째 줄을 빠뜨리는 일이 제일 많습니다. 한 방향만 돌리고 다 했다고 넘어가면 사라진 줄이 통째로 보고에서 빠지는데, 빠진 것은 원래 눈에 안 띄니 나중에도 발견되지 않습니다.
마지막 줄은 매번 하기 번거로워 보이지만 다섯 줄짜리 표로 한 번만 해 두면 됩니다. 수식이 맞게 짜였는지 확인하는 용도라, 그 뒤로는 줄이 몇백이 되어도 같은 수식을 그대로 씁니다.
키 열에 공백이 붙는 문제가 반복되면 그건 비교의 문제가 아니라 데이터를 받는 단계의 문제입니다. 공백을 지웠는데 여전히 남아 있을 때에서 눈에 안 보이는 문자가 어디서 들어오는지 다뤘습니다.
#엑셀 #시트비교 #VLOOKUP #COUNTIF #데이터정리
