두 파일 대사, 눈으로 비교하지 말고 COUNTIF로 끝내기

재고 실사표와 전산 재고, 은행 입금내역과 매출 장부, 지난달 명단과 이번 달 명단처럼 두 목록을 서로 맞춰봐야 하는 일은 실무에서 꾸준히 생깁니다.

데이터가 몇십 건이라면 두 파일을 나란히 띄워놓고 확인할 수도 있습니다. 하지만 100건, 1,000건으로 늘어나면 눈으로 비교하는 방식은 금방 한계가 생깁니다. 빠진 항목을 찾으려다가 오히려 다른 행을 놓치기 쉽습니다.

두 파일 대사는 눈으로 비교하기보다 기준값을 정하고 함수로 판정하는 편이 안전합니다. COUNTIF를 사용하면 상대 목록에 같은 항목이 몇 개 있는지 숫자로 확인할 수 있고, 필터를 적용하면 확인이 필요한 행만 따로 걸러낼 수 있습니다.

1. 먼저 두 파일에서 비교할 기준을 정하기

대사를 시작하기 전에 가장 먼저 해야 할 일은 두 파일에서 같은 대상을 식별할 수 있는 기준값을 정하는 것입니다.

예를 들어 거래처를 비교한다면 거래처명보다 사업자번호나 거래처코드가 좋고, 재고를 비교한다면 품명보다 품목코드가 좋습니다. 이름은 띄어쓰기나 표기가 달라질 수 있지만 고유 코드는 상대적으로 흔들림이 적기 때문입니다.

이 글에서는 두 시트를 다음처럼 사용하겠습니다.

• 장부 - 기준 데이터
• 전산 - 비교할 데이터

예를 들어 장부 시트에 다음 데이터가 있다고 가정해보겠습니다.

거래번호	거래처	정제키	금액
A001	대성물산	A001	120,000
A002	한빛상사	A002	85,000
A003	제일유통	A003	210,000
A004	미래산업	A004	150,000

전산 시트에는 A001, A002, A004는 있지만 A003이 없다고 가정해보겠습니다. 이 상태에서 COUNTIF를 사용하면 장부에만 존재하는 A003을 바로 찾을 수 있습니다.

2. 두 파일을 한 통합문서에 모으면 관리가 편합니다

서로 다른 Excel 파일을 직접 참조하는 수식도 사용할 수 있습니다. 다만 외부 파일을 참조하면 수식에 파일명과 경로가 포함되어 길어지고, 파일 위치가 변경됐을 때 연결 관리가 번거로워질 수 있습니다.

일회성 비교라면 그대로 진행해도 되지만, 수식을 만들고 검토하기 편하게 하려면 비교할 시트를 하나의 통합문서로 복사해두는 방법도 좋습니다.

시트 탭에서 우클릭 → 이동/복사 → 대상 통합문서 선택 → 복사본 만들기를 체크하면 됩니다.

이후 장부와 전산이라는 두 시트가 한 파일 안에 있다고 가정하고 진행하겠습니다.

3. 비교하기 전에 키 값을 먼저 정리하기

COUNTIF 수식이 정확해도 원본 데이터에 불필요한 공백이나 보이지 않는 문자가 섞여 있으면 원하는 결과가 나오지 않을 수 있습니다.

문자 형태의 키라면 보조 열을 만들고 다음처럼 정리할 수 있습니다.

=TRIM(CLEAN(B2))

TRIM은 일반적인 불필요한 공백을 정리하고, CLEAN은 일부 인쇄되지 않는 문자를 제거합니다.

다만 웹이나 다른 시스템에서 복사한 데이터에는 일반 공백이 아닌 특수 공백이 포함되는 경우도 있으므로, 값이 계속 맞지 않는다면 원본 데이터 자체를 추가로 확인해야 합니다.

중요한 점은 양쪽 시트를 같은 방식으로 정리하는 것입니다. 장부만 정제하고 전산은 그대로 두면 비교 기준 자체가 달라질 수 있습니다.

4. COUNTIF로 상대 시트에 존재하는지 확인하기

장부의 정제키가 C열이고 전산의 정제키 역시 C열이라고 가정하겠습니다.

장부 시트의 빈 열에 다음 수식을 입력합니다.

=COUNTIF(전산!$C$2:$C$5000,$C2)

COUNTIF는 전산 시트의 C2:C5000에서 현재 행의 C열 값과 같은 항목이 몇 개 있는지를 셉니다.

COUNTIF 결과	의미
0	상대 시트에 같은 키가 없음
1	상대 시트에서 한 건 발견
2 이상	같은 키가 여러 건 존재

다만 1=정상, 2 이상=오류라고 무조건 판단하면 안 됩니다. 해당 키가 원래 한 번만 존재해야 하는 데이터인지를 먼저 확인해야 합니다.

예를 들어 사번이나 고유 품목코드라면 2건 이상이 중복 문제일 수 있지만, 거래처별 거래내역처럼 같은 거래처가 여러 번 나타나는 데이터라면 2 이상도 정상일 수 있습니다.

5. 한쪽만 확인하면 절반만 비교한 것입니다

장부에서 전산을 조회했을 때 0인 항목을 찾으면 장부에는 있지만 전산에는 없는 데이터를 확인할 수 있습니다.

하지만 이것만으로 대사가 끝나는 것은 아닙니다.

전산에는 존재하지만 장부에는 없는 데이터도 따로 있을 수 있기 때문입니다.

전산 시트에도 반대 방향으로 다음 수식을 입력합니다.

=COUNTIF(장부!$C$2:$C$5000,$C2)

이렇게 하면 두 가지를 따로 확인할 수 있습니다.

• 장부 → 전산 : 장부에는 있지만 전산에는 없는 항목
• 전산 → 장부 : 전산에는 있지만 장부에는 없는 항목

두 파일 대사에서는 양방향 확인이 핵심입니다.

6. 일치·불일치라는 문구로 표시하고 싶다면

0과 1을 그대로 사용해도 되지만 다른 사람이 파일을 확인해야 한다면 문구로 표시하는 편이 이해하기 쉽습니다.

=IF(COUNTIF(전산!$C$2:$C$5000,$C2)=0,"불일치","일치")

그러면 상대 시트에 키가 없으면 불일치, 하나 이상 존재하면 일치로 표시됩니다.

중복 여부까지 함께 보고 싶다면 숫자를 그대로 유지하는 편이 좋습니다. COUNTIF 결과가 2, 3처럼 표시되면 같은 키가 여러 건 있다는 사실까지 바로 확인할 수 있기 때문입니다.

7. 항목은 있는데 금액이 다른 경우 확인하기

실무에서는 두 파일에 항목 자체는 모두 존재하지만 금액이나 수량이 서로 다른 경우도 많습니다.

장부 금액이 E열이고 전산 금액도 E열이라고 가정하면 다음처럼 비교할 수 있습니다.

=SUMIF(전산!$C$2:$C$5000,$C2,전산!$E$2:$E$5000)-$E2

결과가 0이면 금액이 같고, 0이 아니라면 그 숫자만큼 차이가 있다는 뜻입니다.

예를 들어 결과가 5000이라면 전산의 합계가 장부보다 5,000 크고, -5000이라면 전산의 합계가 장부보다 5,000 작다는 의미입니다.

8. 중복 키가 있다면 VLOOKUP보다 SUMIF가 맞는 경우가 있습니다

대사 기준이 되는 코드가 양쪽에서 반드시 한 번씩만 나타난다면 조회 함수로 금액을 가져와 비교할 수도 있습니다.

하지만 한 거래처나 한 품목이 여러 행에 나뉘어 있는 자료라면 이야기가 달라집니다.

VLOOKUP은 같은 키가 여러 건 있어도 처음 찾은 값을 반환하기 때문에 여러 행의 금액을 자동으로 합산하지 않습니다.

반면 SUMIF는 같은 조건을 만족하는 여러 행의 값을 합산할 수 있습니다.

따라서 같은 코드의 여러 거래를 합쳐서 장부와 전산의 총액을 맞추는 작업이라면 SUMIF나 SUMIFS를 사용하는 편이 목적에 맞습니다.

거래처별 집계를 더 체계적으로 보고 싶다면 거래처별 매출 집계, SUMIF 말고 피벗테이블로 하는 법도 함께 참고할 수 있습니다.

9. 차이가 나는 행만 필터로 뽑기

COUNTIF를 적용했다면 이제 전체 데이터를 하나씩 볼 필요가 없습니다.

데이터 영역의 아무 셀이나 클릭한 뒤 Ctrl + Shift + L을 눌러 필터를 켭니다.

• 존재 여부 열에서 0만 선택 → 상대 시트에 없는 항목
• 금액 차이 열에서 0을 제외 → 금액이 다른 항목
• COUNTIF 결과에서 2 이상 → 중복 여부를 확인해야 하는 항목

예를 들어 전체 5,000건 중 COUNTIF가 0인 행이 12건이라면 실제로 확인해야 할 대상은 5,000건이 아니라 12건으로 줄어듭니다.

필터링된 결과를 별도 시트에 복사하면 그대로 대사 확인 목록으로 활용할 수도 있습니다.

10. 조건부 서식으로 불일치 행 표시하기

다른 사람에게 파일을 전달해야 한다면 불일치 행을 색으로 표시하는 것도 편리합니다.

존재 여부 결과가 F열에 있다고 가정하겠습니다.

데이터 범위를 선택한 다음 홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정으로 이동합니다.

다음 수식을 입력합니다.

=$F2=0

$F처럼 열만 고정하면 각 행의 F열 결과를 기준으로 해당 행 전체에 서식을 적용할 수 있습니다.

11. 수천 건과 수만 건에서는 범위를 다르게 잡기

COUNTIF는 수천 건 정도의 일반적인 업무 데이터에서는 간단하고 사용하기 편합니다.

다만 데이터가 커질수록 불필요하게 넓은 범위를 참조하지 않는 것이 좋습니다.

예를 들어 실제 데이터가 5,000행이라면 다음처럼 필요한 영역을 지정합니다.

=COUNTIF(전산!$C$2:$C$5000,$C2)

편하다는 이유로 C:C처럼 전체 열을 여러 수식에서 반복 참조하면 큰 파일에서는 계산 부담이 커질 수 있습니다.

또 원본 데이터를 Ctrl+T로 표(Table)로 만들어두면 데이터가 추가될 때 범위를 관리하기 편해집니다.

수만 행 이상의 데이터를 매달 반복해서 비교하거나 여러 열을 함께 병합해야 한다면 함수 수식을 수만 개 만드는 것보다 Power Query를 사용하는 편이 관리하기 좋은 경우가 많습니다.

12. 매달 반복하는 대사라면 Power Query도 고려하기

매달 같은 형식의 장부와 전산 파일을 비교한다면 매번 COUNTIF 수식을 새로 만들 필요는 없습니다.

Power Query에서는 두 데이터의 키 열을 기준으로 병합할 수 있습니다.

데이터 → 데이터 가져오기에서 두 데이터를 불러온 뒤 쿼리 병합을 사용합니다.

양쪽 데이터를 모두 남겨 차이를 확인하려면 완전 외부(Full Outer) 조인을 사용할 수 있습니다.

처음 구조를 만들어두면 이후에는 새 원본으로 교체하고 새로 고침하여 같은 비교 과정을 반복할 수 있다는 장점이 있습니다.

자주 막히는 지점

1) 분명히 같은 문자열인데 결과가 0으로 나옵니다

먼저 앞뒤 공백이나 보이지 않는 문자가 포함되어 있는지 확인합니다.

두 셀의 글자 수를 비교하려면 다음처럼 확인할 수 있습니다.

=LEN(B2)

화면에는 같은 문자열로 보이는데 LEN 결과가 다르다면 공백이나 추가 문자가 들어 있을 가능성이 있습니다.

이럴 때는 양쪽 파일 모두 같은 방식으로 정제한 보조 열을 만든 뒤 그 열을 기준으로 비교하는 것이 좋습니다.

2) 한쪽은 숫자이고 다른 쪽은 텍스트입니다

시스템에서 내려받은 파일에서는 숫자로 보여도 실제로는 텍스트 형식으로 저장되어 있는 경우가 있습니다.

두 값이 어떤 형식인지 확인하려면 ISNUMBER 등을 이용하거나 셀의 경고 표시와 실제 값을 함께 확인합니다.

=ISNUMBER(A2)

TRUE라면 숫자이고, FALSE라면 숫자가 아닌 데이터일 가능성이 있습니다.

텍스트로 저장된 숫자를 숫자로 바꿔야 한다면 상황에 따라 VALUE 함수나 곱하기 1 방식 등을 사용할 수 있습니다.

중요한 것은 두 파일의 키 열 형식을 먼저 통일한 뒤 대사를 진행하는 것입니다. 숫자가 텍스트로 저장된 데이터를 구분하고 변환하는 방법은 엑셀 숫자가 텍스트로 저장됐을 때, 원인과 해결법 4가지에서 자세히 확인할 수 있습니다.

3) 같은 날짜인데 일치하지 않습니다

화면에는 날짜만 표시되어 있어도 실제 셀 값에는 시각이 포함되어 있을 수 있습니다.

예를 들어 한쪽은 2026-08-31이고 다른 쪽은 실제 값이 2026-08-31 09:31이라면 완전히 같은 숫자값은 아닙니다.

날짜만 비교하려는 목적이라면 보조 열에서 다음처럼 정리할 수 있습니다.

=INT(A2)

4) COUNTIF 결과가 2 이상인데 무조건 중복 오류인가요?

아닙니다. 먼저 그 키가 원래 고유해야 하는 값인지 확인해야 합니다.

품목코드 마스터나 사번 목록처럼 한 번만 존재해야 하는 데이터라면 2 이상이 중복일 가능성이 높습니다.

반면 거래내역처럼 같은 거래처 코드가 여러 행에 나타나는 자료라면 여러 건이 정상일 수 있습니다.

따라서 COUNTIF 결과는 단순히 일치·불일치뿐 아니라 원본 데이터의 구조를 확인하는 지표로 보는 것이 좋습니다.

중복값을 바로 삭제하기 전에 확인해야 할 내용은 엑셀 중복 데이터, 지우기 전에 먼저 확인하는 법에서 정리해두었습니다.

자주 묻는 질문

엑셀에 파일 비교 기능이 따로 있지 않나요?

Excel 환경에 따라 별도의 비교 기능을 사용할 수 있는 경우도 있습니다. 하지만 서로 다른 두 목록에서 특정 키가 존재하는지 확인하거나 행 순서가 다른 데이터를 비교하는 작업에서는 COUNTIF 방식이 간단하고 직접적입니다.

키가 될 만한 고유 열이 없으면 어떻게 하나요?

두 개 이상의 열을 합쳐 임시 키를 만들 수 있습니다.

=C2&"|"&D2

중간에 |처럼 구분자를 넣는 것이 좋습니다. 단순히 값만 이어붙이면 서로 다른 조합이 우연히 같은 문자열이 되는 경우가 생길 수 있기 때문입니다.

COUNTIF만으로 금액까지 완전히 대사할 수 있나요?

COUNTIF는 특정 키가 상대 데이터에 몇 개 존재하는지 확인하는 데 적합합니다.

같은 키의 금액 합계까지 비교해야 한다면 SUMIF 또는 SUMIFS를 함께 사용하는 편이 좋습니다.

몇 건까지 COUNTIF로 비교해도 되나요?

단순히 행 개수만으로 한계를 정하기는 어렵습니다. 수식 개수, 참조 범위, 다른 계산식, PC 성능에 따라 차이가 생기기 때문입니다.

수천 행 정도의 일반적인 비교에서는 사용하기 편하지만, 데이터가 수만 행으로 커지고 반복 작업까지 많아진다면 불필요한 전체 열 참조를 줄이고 Power Query 같은 방식도 검토하는 것이 좋습니다.

Google 스프레드시트에서도 사용할 수 있나요?

COUNTIF, SUMIF, TRIM 등의 기본 함수는 Google 스프레드시트에서도 사용할 수 있습니다.

다만 서로 다른 Google 스프레드시트 파일을 직접 연결해야 한다면 Excel과 참조 방식이 다르므로 실제 사용 환경에 맞춰 범위를 연결해야 합니다.

정리

• 두 파일을 비교하기 전에 고유한 키 열을 먼저 정합니다.
• 문자 데이터는 공백이나 불필요한 문자를 정리한 뒤 비교합니다.
• 존재 여부는 COUNTIF로 확인합니다.
• 장부 → 전산뿐 아니라 전산 → 장부도 확인해야 합니다.
• COUNTIF 결과가 2 이상이라고 무조건 오류는 아니며 원본 데이터 구조를 확인해야 합니다.
• 같은 키의 금액 합계 비교에는 SUMIF 또는 SUMIFS가 적합합니다.
• 필터를 이용하면 전체 데이터에서 불일치 행만 빠르게 추릴 수 있습니다.
• 수만 행을 반복적으로 비교한다면 Power Query 병합도 고려할 수 있습니다.

두 파일 대사의 핵심은 복잡한 함수가 아닙니다. 비교할 기준을 정확히 정하고, 양쪽 방향에서 빠진 항목을 확인하는 것입니다.

COUNTIF를 사용하면 수천 행의 목록도 눈으로 하나씩 비교할 필요 없이 확인이 필요한 항목부터 좁혀갈 수 있습니다.

관련 글

• 거래처별 매출 집계, SUMIF 말고 피벗테이블로 하는 법
• 엑셀 중복 데이터, 지우기 전에 먼저 확인하는 법
• 엑셀 숫자가 텍스트로 저장됐을 때, 원인과 해결법 4가지