엑셀 세금계산서 대사 금액이 안 맞을 때: 사업자번호 하이픈·승인번호·부가세 ROUND로 원인 잡기

월말에 매입 세금계산서 대사를 하다 보면 ERP 합계와 홈택스 내려받기 합계가 딱 맞아야 하는데, 이상하게 11원, 110원, 23,100원처럼 애매한 차이가 나는 경우가 있습니다. 더 답답한 건 VLOOKUP으로 승인번호를 찾으면 일부는 #N/A가 뜨고, SUMIFS로 월별 합계를 잡으면 거래처별 금액이 0으로 나오는 증상입니다.

이번 글은 실제로 많이 보는 엑셀 세금계산서 대사 오류를 복구하는 흐름으로 정리했습니다. 핵심은 어려운 기능을 쓰는 것이 아니라, 날짜·사업자번호·승인번호·부가세 반올림 기준을 하나씩 같은 형식으로 맞춘 뒤 SUMIFS, COUNTIFS, VLOOKUP, XLOOKUP, INDEX MATCH로 검증하는 것입니다.

ERP 금액은 있는데 홈택스 금액이 0으로 잡히는 상황

예시 파일은 시트가 3개 있다고 가정하겠습니다. ERP 시트에는 내부 회계시스템에서 내려받은 매입 전표가 있고, 홈택스 시트에는 세금계산서 목록을 내려받은 자료가 있습니다. 마지막으로 대사결과 시트에서 기준월과 사업자번호를 넣고 합계를 확인합니다.

예를 들어 대사결과!B2에는 2026-07-01, 대사결과!B3에는 1234567890을 입력합니다. 그런데 ERP 합계는 12,331,000원인데 홈택스 합계는 0원으로 나오면, 대부분은 금액 문제가 아니라 조건값의 모양이 서로 다른 문제입니다.

처음 의심한 건 금액이 아니라 날짜 형식이었다

SUMIFS에서 날짜 조건을 걸 때 가장 흔한 실수는 날짜처럼 보이지만 실제로는 숫자나 텍스트인 값을 그대로 조건 범위로 쓰는 것입니다. ERP 시트 A열의 작성일이 20260705로 들어와 있다면, 엑셀은 이것을 날짜가 아니라 그냥 숫자로 볼 수 있습니다.

ERP 시트 H열을 정리작성일로 만들고, H2에 아래 수식을 넣은 뒤 H5000까지 복사합니다. A열의 8자리 값을 실제 날짜로 바꾸기 위한 보조열입니다.

=IFERROR(DATE(LEFT(TEXT(A2,"00000000"),4),MID(TEXT(A2,"00000000"),5,2),RIGHT(TEXT(A2,"00000000"),2)),A2)

홈택스 시트도 같은 방식으로 G열을 정리발행일로 만들고 G2에 수식을 넣습니다. 홈택스 A열도 20260705 형태라면 같은 수식을 그대로 쓰면 됩니다.

=IFERROR(DATE(LEFT(TEXT(A2,"00000000"),4),MID(TEXT(A2,"00000000"),5,2),RIGHT(TEXT(A2,"00000000"),2)),A2)

여기까지 한 뒤에는 셀 서식을 날짜로 바꿔서 2026-07-05처럼 보이는지 확인합니다. 수식 결과가 이상하게 1900년대나 0004년대로 보이면 원본 A열에 이미 다른 형식의 날짜가 섞여 있는 것이므로, 문제 행만 따로 필터링해서 확인하는 것이 좋습니다.

사업자번호 하이픈 하나 때문에 SUMIFS가 0을 반환했다

ERP 시트 C열에는 사업자번호가 1234567890으로 있고, 홈택스 시트 C열에는 123-45-67890으로 있는 경우가 많습니다. 눈으로 보면 같은 거래처지만, 엑셀 조건식에서는 완전히 다른 값입니다.

ERP 시트 J열을 정리사업자번호로 만들고 J2에 아래 수식을 입력합니다. 하이픈이 있는 경우와 없는 경우를 나눠 10자리 숫자 모양의 텍스트로 통일합니다.

=IF(MID(C2,4,1)="-",LEFT(C2,3)&MID(C2,5,2)&RIGHT(C2,5),TEXT(C2,"0000000000"))

홈택스 시트도 I열을 정리등록번호로 만들고 I2에 같은 수식을 넣습니다. 이 보조열끼리 비교해야 거래처별 합계가 안정적으로 맞습니다.

=IF(MID(C2,4,1)="-",LEFT(C2,3)&MID(C2,5,2)&RIGHT(C2,5),TEXT(C2,"0000000000"))

이때 사업자번호가 숫자로 저장되어 앞자리 0이 사라지는 업종 코드 자료와 섞여 있다면 TEXT 함수가 특히 중요합니다. 대사 기준으로 쓸 코드는 숫자가 아니라 자리수가 고정된 텍스트라고 생각하면 실수가 줄어듭니다.

승인번호 #N/A는 VLOOKUP 문제가 아니라 원본 보존 문제였다

세금계산서 승인번호는 길이가 긴 값이라 엑셀에서 숫자로 열면 뒤쪽 자리가 0000처럼 깨지는 경우가 있습니다. 이미 숫자로 열려서 손상된 승인번호는 수식으로 원래 번호를 복구할 수 없습니다. 그래서 원본을 다시 받을 때 승인번호 열은 텍스트로 유지하는 것이 안전합니다.

그래도 현재 파일에서 비교 가능한 상태라면 ERP 시트 K열을 정리승인번호로 만들고 K2에 아래 수식을 넣습니다. 홈택스 시트도 H열을 정리승인번호로 만들고 같은 수식을 사용합니다.

=TEXT(B2,"0")

그다음 ERP 시트 L열에는 홈택스 합계금액을 가져옵니다. Microsoft 365나 Excel 2021 이상이면 XLOOKUP을 쓰는 것이 보기 편합니다.

=IFERROR(XLOOKUP(K2,홈택스!$H$2:$H$8000,홈택스!$F$2:$F$8000),0)

XLOOKUP이 없는 환경이라면 INDEX와 MATCH 조합으로 같은 결과를 만들 수 있습니다. 아래 수식은 ERP!K2의 승인번호를 홈택스!H열에서 찾고, 해당 행의 홈택스 합계금액 F열을 가져옵니다.

=IFERROR(INDEX(홈택스!$F$2:$F$8000,MATCH(K2,홈택스!$H$2:$H$8000,0)),0)

L열 결과가 0이면 실제로 홈택스에 없을 수도 있지만, 승인번호 형식이 깨졌거나 공백이 섞였을 가능성도 큽니다. 이때는 승인번호 앞 5자리, 뒤 5자리를 LEFT, RIGHT로 잘라 원본끼리 비교해 보면 손상 여부를 빨리 확인할 수 있습니다.

부가세 1원 차이는 ROUND 기준부터 맞춰야 했다

ERP와 홈택스 대사에서 가장 찜찜한 차이가 바로 세액 1원 차이입니다. 공급가액에 10%를 곱한 뒤 반올림하는지, 절사하는지, 품목별로 계산한 뒤 합산하는지에 따라 결과가 달라집니다.

ERP 시트 M열을 세액검산으로 만들고 M2에 아래 수식을 입력합니다. 여기서는 공급가액 E열에 10%를 곱하고 ROUND로 원 단위 반올림한 결과가 ERP 세액 F열과 같은지 확인합니다.

=IF(ROUND(E2*10%,0)=F2,"세액OK","세액확인")

N열에는 ERP 합계금액과 홈택스 합계금액의 차이를 표시합니다. L열이 홈택스 합계금액이라고 했으니, N2에는 아래처럼 입력합니다.

=ROUND(G2-L2,0)

O열에는 상태값을 붙여두면 필터링이 쉬워집니다. 홈택스에 없어서 0인지, 금액이 다른지, 정상 일치인지 한눈에 볼 수 있습니다.

=IF(L2=0,"홈택스 없음",IF(N2=0,"일치","금액차이"))

월별 거래처 합계는 보조열 기준으로 다시 잡았다

이제 대사결과 시트에서 거래처별 월 합계를 확인해 보겠습니다. 대사결과!B2에는 기준월 첫날인 2026-07-01, B3에는 하이픈 없는 사업자번호 1234567890을 넣습니다.

대사결과!B5에는 ERP 합계금액을 계산합니다. 날짜는 ERP!H열의 정리작성일, 사업자번호는 ERP!J열의 정리사업자번호를 기준으로 합니다.

=SUMIFS(ERP!$G$2:$G$5000,ERP!$H$2:$H$5000,">="&$B$2,ERP!$H$2:$H$5000,"<="&EOMONTH($B$2,0),ERP!$J$2:$J$5000,$B$3)

대사결과!B6에는 홈택스 합계금액을 계산합니다. 홈택스 시트에서는 정리발행일 G열, 정리등록번호 I열, 합계금액 F열을 사용합니다.

=SUMIFS(홈택스!$F$2:$F$8000,홈택스!$G$2:$G$8000,">="&$B$2,홈택스!$G$2:$G$8000,"<="&EOMONTH($B$2,0),홈택스!$I$2:$I$8000,$B$3)

대사결과!B7에는 차이를 표시합니다.

=ROUND(B5-B6,0)

여기서 B7이 0이면 거래처 월 합계는 일치합니다. 0이 아니라면 ERP 시트 O열에서 금액차이, 홈택스 없음 상태를 필터링하고, 같은 승인번호가 중복으로 들어간 행이 있는지도 확인해야 합니다.

중복 승인번호가 숨어 있으면 합계가 두 번 더해진다

세금계산서 대사에서 생각보다 자주 나오는 원인이 중복 승인번호입니다. 같은 승인번호가 ERP에 두 번 들어가 있으면 월 합계는 맞을 수가 없습니다.

ERP 시트 P열을 중복확인으로 만들고 P2에 아래 수식을 넣습니다.

=IF(COUNTIFS($K$2:$K$5000,K2)>1,"중복","")

홈택스 시트도 J열에 같은 방식으로 중복 여부를 확인합니다.

=IF(COUNTIFS($H$2:$H$8000,H2)>1,"중복","")

중복이 표시되면 바로 삭제하기보다, 전표가 취소분인지 재발행분인지 먼저 확인해야 합니다. 특히 마이너스 세금계산서가 별도 행으로 들어오는 회사는 거래구분 열을 추가해서 정상, 취소, 수정발행을 나눠 보는 편이 안전합니다.

거래처명이 바뀐 경우에는 사업자번호 기준 VLOOKUP으로 정리했다

같은 사업자번호인데 ERP 거래처명은 주식회사 한빛물류, 홈택스 거래처명은 한빛물류(주)처럼 다르게 들어오는 경우도 많습니다. 거래처명으로 찾으면 오류가 나지만, 사업자번호 기준으로 찾으면 안정적입니다.

관리표 시트 A열에는 정리사업자번호, B열에는 표준거래처명을 입력해 둡니다. ERP 시트 Q열에 표준거래처명을 붙이고 싶다면 Q2에 아래 수식을 넣습니다.

=IFERROR(VLOOKUP(J2,관리표!$A$2:$B$300,2,0),"거래처확인")

이 수식은 거래처명 대조가 아니라 사업자번호 대조입니다. 실무에서는 이름보다 코드가 우선입니다. 거래처명이 바뀌어도 사업자번호가 같으면 같은 거래처로 묶어야 월별 합계가 흔들리지 않습니다.

반복 정리가 많다면 선택 범위만 VBA로 정리한다

사업자번호와 승인번호 열을 매번 수식으로 정리해도 되지만, 여러 파일을 반복해서 대사한다면 선택 범위의 하이픈과 공백을 제거하는 간단한 VBA를 써도 좋습니다. 다만 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않을 수 있으니, 실행 전 반드시 파일을 복사해 두세요.

아래 코드는 선택한 범위에만 적용됩니다. 예를 들어 홈택스 시트 C2:C8000 사업자번호 범위를 선택한 뒤 실행하면 하이픈과 공백을 제거하고 텍스트 형식으로 저장합니다.

Sub NormalizeSelectedTextKeys()
    Dim c As Range
    Dim v As String

    If TypeName(Selection) <> "Range" Then Exit Sub

    For Each c In Selection.Cells
        If Len(c.Value) > 0 Then
            v = c.Text
            v = Replace(v, "-", "")
            v = Replace(v, " ", "")
            v = Replace(v, "'", "")
            c.NumberFormat = "@"
            c.Value = v
        End If
    Next c
End Sub

이 코드는 이미 숫자로 열리면서 손상된 긴 승인번호를 복구하지는 못합니다. 승인번호 뒤 자리가 0000으로 바뀐 상태라면 원본 파일을 다시 열 때 해당 열을 텍스트로 유지해야 합니다. 되돌리는 방법은 매크로 실행 전 복사해 둔 백업 시트로 교체하거나, 저장하지 않고 닫은 뒤 다시 여는 방식이 가장 안전합니다.

다음 달에도 같은 오류가 나지 않게 보는 체크포인트

세금계산서 대사는 금액부터 보면 오래 걸립니다. 날짜, 사업자번호, 승인번호처럼 조건에 쓰는 값이 같은 모양인지 먼저 맞추고, 그다음 금액 차이를 봐야 원인을 빨리 좁힐 수 있습니다.

• 작성일과 발행일은 실제 날짜인지 확인하고, 월 조건은 EOMONTH로 마지막 날까지 잡습니다.
• 사업자번호는 하이픈을 제거한 10자리 텍스트로 맞춥니다.
• 승인번호는 숫자로 열리지 않게 주의하고, 손상 여부를 먼저 확인합니다.
• SUMIFS가 0이면 합계 범위보다 조건 범위의 형식을 먼저 봅니다.
• 부가세 차이는 ROUND 기준과 품목별 계산 여부를 확인합니다.
• COUNTIFS로 승인번호 중복을 확인한 뒤 취소·수정 전표를 분리합니다.

이 순서대로 점검하면 ERP와 홈택스 합계가 안 맞을 때도 막연히 행을 뒤지는 시간을 크게 줄일 수 있습니다. 특히 보조열을 남겨두면 다음 달 파일에도 같은 수식을 복사해서 바로 검증할 수 있어 대사 작업이 훨씬 안정적입니다.