두 파일 대사, 눈으로 비교하지 말고 COUNTIF로 끝내기
재고 실사표와 전산 재고, 은행 입금내역과 매출 장부, 지난달 명단과 이번달 명단. 두 목록을 맞춰봐야 하는 일은 실무에서 꾸준히 생깁니다.
이때 두 파일을 나란히 띄워놓고 눈으로 훑는 방식은 100건만 넘어가도 무너집니다. 놓친 항목을 찾자고 시작한 작업인데, 정작 놓치는 게 눈이거든요.
대사는 눈이 아니라 함수로 합니다. COUNTIF 하나면 "상대 파일에 이 항목이 있는지"가 0과 1로 나오고, 필터 한 번이면 차이 나는 행만 남습니다.
준비. 두 파일을 한 통합문서로 모으기
파일이 둘로 나뉘어 있으면 수식에 파일 경로가 통째로 들어가서 지저분해지고, 한쪽 파일을 닫으면 값이 깨집니다.
비교할 파일의 시트 탭에서 우클릭 → 이동/복사 → 대상 통합 문서를 기준 파일로 선택 → 복사본 만들기 체크.
이 글에서는 시트 이름을 장부(기준)와 전산(비교 대상)으로 두고 설명합니다.
A단계. 키 열을 정하고 정제하기
대사의 성패는 90% 여기서 갈립니다. 두 시트에서 같은 대상을 가리키는 열을 하나 정하세요. 거래처명보다는 사업자번호, 품명보다는 품목코드처럼 중복과 표기 흔들림이 적은 열이 좋습니다.
정한 열 옆에 빈 열을 하나 만들고 이렇게 넣습니다.
=TRIM(CLEAN(B2))
TRIM은 앞뒤 공백과 중복 공백을 없애고, CLEAN은 눈에 안 보이는 줄바꿈 문자를 제거합니다. 다른 시스템에서 내려받은 파일에는 이런 게 거의 항상 붙어 있습니다.
양쪽 시트 모두 이 정제 열을 만들어야 합니다. 한쪽만 하면 의미가 없습니다.
B단계. 상대 파일에 있는지 판정하기
기준 시트(장부)의 빈 열에 넣습니다. 정제 열이 C열, 전산 시트의 정제 열도 C열이라고 가정합니다.
=COUNTIF(전산!$C$2:$C$5000, $C2)
결과가 0이면 전산에 없는 항목, 1이면 정상, 2 이상이면 전산 쪽에 중복이 있다는 뜻입니다. 이 세 가지가 한 번에 나오는 게 COUNTIF의 장점입니다.
반대 방향도 확인해야 합니다. 전산 시트에도 같은 방식으로 넣으세요.
=COUNTIF(장부!$C$2:$C$5000, $C2)
한쪽만 보면 절반을 놓칩니다. 장부에만 있는 건과 전산에만 있는 건은 완전히 다른 문제라서 둘 다 뽑아야 합니다.
C단계. 금액 차이 계산하기
항목은 양쪽에 다 있는데 금액이 다른 경우가 실무에서는 더 흔합니다. 기준 시트에 열을 하나 더 만듭니다. 장부 금액이 E열, 전산 금액이 E열이라고 하면,
=SUMIF(전산!$C$2:$C$5000, $C2, 전산!$E$2:$E$5000) - $E2
결과가 0이면 일치, 0이 아니면 그 금액만큼 차이가 난다는 뜻입니다.
여기서 VLOOKUP을 쓰지 않는 이유가 있습니다. VLOOKUP은 중복 키가 있으면 첫 번째 값만 가져오고, 없으면 #N/A를 뱉어서 뺄셈 자체가 오류가 됩니다. SUMIF는 중복을 다 더해주고, 없으면 0을 반환해서 계산이 그대로 이어집니다. 대사 작업에서는 SUMIF가 맞습니다.
D단계. 차이 나는 행만 뽑아내기
데이터 아무 셀 클릭 후 Ctrl + Shift + L로 필터를 켭니다.
- 존재 여부 열 → 0만 체크 = 상대에 없는 항목
- 금액 차이 열 → 0 체크 해제 = 금액이 어긋난 항목
이 두 가지를 각각 걸러서 별도 시트에 붙여넣으면 대사 보고서가 됩니다. 전체 5,000건 중에 실제로 볼 건 보통 열 몇 건입니다.
E단계. 눈에 띄게 표시하기 (선택)
보고용으로 넘길 때는 색으로 표시하는 편이 낫습니다.
데이터 범위 전체 선택 → 홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정. 존재 여부 열이 F열이라면,
=$F2=0
열 문자 앞에만 $를 붙이는 게 핵심입니다. 이렇게 해야 행 전체에 색이 칠해집니다. 양쪽 다 $를 붙이면 첫 행에만 적용되고, 둘 다 빼면 엉뚱한 셀이 칠해집니다.
F단계. 매달 반복한다면 파워쿼리
같은 대사를 매달 한다면 위 과정을 매번 다시 만들 필요가 없습니다.
데이터 → 데이터 가져오기 → 쿼리 결합 → 병합에서 두 시트를 키 열로 연결하고, 조인 종류를 완전 외부(Full Outer)로 선택하세요. 양쪽 어디에도 짝이 없는 행까지 전부 남습니다.
한 번 만들어두면 다음 달에는 원본 데이터만 갈아끼우고 모두 새로 고침만 누르면 됩니다.
자주 막히는 지점 세 가지
① 분명히 같은 값인데 COUNTIF가 0으로 나옴
눈에 안 보이는 공백이나 줄바꿈이 붙어 있는 경우입니다. A단계의 TRIM(CLEAN())을 건너뛰었다면 여기서 반드시 걸립니다.
확인하려면 빈 셀에 =LEN(B2)를 넣어 글자 수를 세어보세요. 양쪽 숫자가 다르면 공백 문제입니다.
② 숫자 코드가 안 맞음
한쪽은 숫자 1001, 다른 쪽은 텍스트 "1001"로 저장된 경우입니다. 화면에는 똑같이 보이지만 COUNTIF는 다른 값으로 봅니다. 값이 셀 왼쪽에 붙어 있으면 텍스트입니다.
빈 셀에 1을 입력하고 복사 → 해당 열 전체 선택 → 선택하여 붙여넣기 → 곱하기. 한 번에 숫자로 바뀝니다.
③ 날짜가 안 맞음
전산에서 내려받은 날짜에는 2026-01-04 09:31처럼 시각이 숨어 있는 경우가 많습니다. 표시 형식만 날짜로 되어 있어서 화면에는 안 보입니다.
=INT(A2)로 시각을 잘라내고 날짜만 남긴 열을 만들어서 비교하세요.
자주 묻는 질문
Q. 엑셀에 파일 비교 기능이 따로 있지 않나요?
Spreadsheet Compare라는 도구가 있지만 Office Professional Plus 등 일부 버전에만 들어 있습니다. 또 셀 단위로 다른 곳을 전부 표시해주는 방식이라, 행 순서가 다른 두 목록을 맞추는 용도로는 오히려 불편합니다. 순서와 무관하게 항목을 대조하려면 이 글의 방식이 맞습니다.
Q. 키가 될 만한 고유 열이 없으면요?
두 열 이상을 이어붙여 임시 키를 만듭니다. =C2&"|"&D2 처럼 구분자를 넣어서 연결하세요. 구분자를 빼면 "12"+"34"와 "123"+"4"가 같은 값이 되어버립니다.
Q. 몇 건까지 감당되나요?
수천 건은 문제없습니다. 다만 COUNTIF와 SUMIF는 행마다 전체 범위를 훑기 때문에 수만 건이 넘어가면 눈에 띄게 느려집니다. 그 규모부터는 파워쿼리 병합이 훨씬 빠릅니다.
Q. 구글 시트에서도 되나요?
COUNTIF, SUMIF, TRIM, INT 모두 동일하게 작동합니다. 다른 파일을 참조할 때만 IMPORTRANGE를 써야 하고, 처음 한 번 접근 권한을 승인해야 합니다.
정리
- 키 열은
TRIM(CLEAN())으로 먼저 정제 - 존재 여부는
COUNTIF, 양쪽 방향 모두 - 금액 대사는 VLOOKUP이 아니라
SUMIF - 매달 반복한다면 파워쿼리 병합으로 한 번만 세팅
관련 글
- 거래처별 매출 집계, SUMIF 말고 피벗테이블로 하는 법
- 숫자가 텍스트로 인식될 때 해결법
- 실무에서 진짜 쓰는 엑셀 함수 12개
댓글
댓글 쓰기