세금계산서 대사표 1원 차이·누락 거래 잡기: SUMIFS와 ROUND 검산 체크리스트

월마감 때 세금계산서 대사표 금액 1원 차이, SUMIFS 합계 안 맞음, VLOOKUP 계산서없음 같은 증상이 나오면 대부분 수식 하나가 틀렸다기보다 날짜, 사업자번호, 반올림 기준이 섞여 있는 경우가 많습니다. 특히 매출원장은 주문일 기준이고 전자세금계산서 파일은 작성일 기준이라서, 같은 5월 자료를 본다고 해도 조건 범위가 살짝 달라집니다.

이번 예시는 실무에서 자주 보는 구조로 잡아보겠습니다. 매출원장 시트에는 A열부터 J열까지 주문 내역이 있고, 전자세금계산서 시트에는 A열부터 G열까지 국세청 또는 회계 프로그램에서 내려받은 계산서 목록이 있습니다. 목표는 기준월과 사업자번호를 넣으면 공급가액, 부가세, 합계가 맞는지 확인하고, 승인번호 기준으로 누락 거래까지 찾는 것입니다.

어떤 표 구조에서 문제가 자주 생길까?

먼저 예시 파일의 열 구조를 정확히 정리하겠습니다. 실제 파일에 적용할 때는 열 위치만 본인 파일에 맞게 바꾸면 됩니다.

검산표에는 다음처럼 조건 셀을 둡니다. B2에는 기준월을 텍스트로 2026-05라고 입력하고, B3에는 사업자번호 10자리를 하이픈 없이 1234567890처럼 입력합니다. 결과는 C열에 표시하겠습니다.

SUMIFS가 0으로 나오면 날짜부터 확인해야 할까?

기준월을 2026-05처럼 입력해 두고 바로 SUMIFS 조건에 넣으면 날짜 조건이 어긋날 수 있습니다. 그래서 검산표 C2와 C3에 시작일과 종료일을 먼저 만들어 두는 방식이 안전합니다.

검산표 C2에는 아래 수식을 넣습니다. B2의 왼쪽 4자리는 연도, 오른쪽 2자리는 월로 보고 DATE 함수로 실제 날짜를 만듭니다.

=DATE(LEFT($B$2,4),RIGHT($B$2,2),1)

검산표 C3에는 해당 월의 말일을 구합니다. 2월, 4월, 6월처럼 말일이 다른 달도 EOMONTH로 처리하면 실수가 줄어듭니다.

=EOMONTH($C$2,0)

여기서 C2, C3이 왼쪽 정렬된 문자처럼 보이면 문제가 있습니다. 날짜 형식으로 보이더라도 실제 값이 문자일 수 있으니 셀 서식을 날짜로 바꿔보고, 표시가 자연스럽게 바뀌는지 확인하세요.

사업자번호 하이픈 때문에 COUNTIFS와 SUMIFS가 빗나가지는 않을까?

매출원장 C열에는 123-45-67890처럼 하이픈이 들어 있고, 전자세금계산서 C열에는 1234567890처럼 붙어 있는 경우가 흔합니다. 이 상태에서 조건 셀 B3에 1234567890을 넣고 SUMIFS를 돌리면 원장 쪽 합계가 0으로 나올 수 있습니다.

매출원장 K열을 보조열로 사용해 사업자번호10자리를 만들겠습니다. K1에는 사업자번호10자리라고 적고, K2에 아래 수식을 입력한 뒤 K5000까지 복사합니다.

=IFERROR(LEFT(C2,3)&MID(C2,5,2)&RIGHT(C2,5),C2)

이 수식은 C2가 123-45-67890 형식일 때 앞 3자리, 가운데 2자리, 뒤 5자리를 붙입니다. 이미 하이픈이 없는 값까지 섞여 있다면 일부 행은 별도 확인이 필요합니다. 사업자번호가 10자리인지 검사용으로 L열에 아래처럼 표시해 두면 좋습니다.

=IF(K2=$K2,"확인","확인")

위 수식은 단순 표시용으로는 큰 의미가 없으니, 실제 검증은 필터로 K열의 길이가 이상한 값을 따로 보는 것이 좋습니다. 함수만으로 끝내려면 사업자번호가 하이픈 포함 12자리 형식인지, 하이픈 없는 10자리 형식인지 원본 규칙을 먼저 맞춰두는 것이 핵심입니다.

기준월·사업자번호별 공급가액을 어떻게 맞춰볼까?

이제 검산표에서 매출원장 합계와 전자세금계산서 합계를 각각 구합니다. 검산표 B5에는 매출원장 공급가액, B6에는 계산서 공급가액, B7에는 차이라고 적어두겠습니다.

검산표 C5에는 매출원장의 공급가액 합계를 구합니다. 매출원장 A열 주문일이 C2 이상, C3 이하이고, K열 사업자번호10자리가 B3과 같은 건만 더합니다.

=SUMIFS(매출원장!$G$2:$G$5000,매출원장!$A$2:$A$5000,">="&$C$2,매출원장!$A$2:$A$5000,"<="&$C$3,매출원장!$K$2:$K$5000,$B$3)

검산표 C6에는 전자세금계산서 공급가액 합계를 구합니다. 계산서 쪽은 작성일이 A열, 사업자번호가 C열, 공급가액이 E열입니다.

=SUMIFS(전자세금계산서!$E$2:$E$2000,전자세금계산서!$A$2:$A$2000,">="&$C$2,전자세금계산서!$A$2:$A$2000,"<="&$C$3,전자세금계산서!$C$2:$C$2000,$B$3)

검산표 C7에는 차이를 계산합니다.

=C5-C6

C7이 0이면 공급가액 기준으로는 일치합니다. 다만 공급가액은 맞는데 부가세나 합계에서 1원 차이가 날 수 있으므로 여기서 멈추면 안 됩니다.

부가세 1원 차이는 ROUND 기준이 다른 건 아닐까?

세금계산서 대사에서 가장 자주 보이는 것이 공급가액은 같은데 부가세가 1원 차이 나는 경우입니다. 원인은 대부분 행별 반올림과 월합계 반올림의 기준 차이입니다. 원장에서는 주문 1건마다 부가세를 ROUND 처리하고, 계산서에서는 월 공급가액 합계에 10%를 곱해 반올림하는 방식이면 1원 이상 차이가 날 수 있습니다.

매출원장 L열에 부가세검산이라는 보조열을 만들고 L2에 아래 수식을 넣습니다. G열 공급가액의 10%를 반올림해 H열 부가세와 비교하기 위한 값입니다.

=ROUND(G2*0.1,0)

매출원장 M열에는 부가세차이를 만들고 M2에 아래 수식을 넣습니다.

=H2-L2

M열에 0이 아닌 값이 필터로 잡히면, 해당 행은 원장 자체의 부가세 계산 기준이 다르거나 수기로 수정된 행일 가능성이 있습니다. 이때 바로 값을 고치기보다 거래처, 품목, 승인번호를 같이 확인해야 합니다.

검산표에서도 부가세 합계를 비교해 보겠습니다. 검산표 B8에는 매출원장 부가세, B9에는 계산서 세액, B10에는 부가세 차이라고 적습니다.

=SUMIFS(매출원장!$H$2:$H$5000,매출원장!$A$2:$A$5000,">="&$C$2,매출원장!$A$2:$A$5000,"<="&$C$3,매출원장!$K$2:$K$5000,$B$3)

=SUMIFS(전자세금계산서!$F$2:$F$2000,전자세금계산서!$A$2:$A$2000,">="&$C$2,전자세금계산서!$A$2:$A$2000,"<="&$C$3,전자세금계산서!$C$2:$C$2000,$B$3)

=C8-C9

부가세 차이가 1원, 2원 정도라면 먼저 반올림 기준을 확인합니다. 하지만 차이가 수천 원, 수만 원이면 누락 발행, 취소 계산서, 작성일 월 차이 가능성이 더 큽니다.

승인번호 기준으로 누락 거래를 바로 찾으려면?

월별 합계 차이만 보면 어느 행이 문제인지 찾는 데 시간이 오래 걸립니다. 이때는 승인번호를 기준으로 원장에 있는 계산서가 실제 계산서 목록에도 있는지 확인하는 열을 추가합니다.

매출원장 N열에 계산서대사를 만들고 N2에 아래 수식을 넣습니다. Microsoft 365 또는 Excel 2021 이상에서 XLOOKUP을 사용할 수 있다면 이 방식이 읽기 쉽습니다.

=IF(J2="","승인번호없음",IFERROR(IF(XLOOKUP(J2,전자세금계산서!$B$2:$B$2000,전자세금계산서!$G$2:$G$2000)=I2,"일치","합계차이"),"계산서없음"))

위 수식은 매출원장 J2의 승인번호를 전자세금계산서 B열에서 찾고, 계산서 G열의 합계와 매출원장 I열 합계를 비교합니다. 결과가 계산서없음이면 승인번호가 계산서 목록에 없다는 뜻이고, 합계차이이면 승인번호는 있지만 금액이 다르다는 뜻입니다.

XLOOKUP이 없는 환경이라면 VLOOKUP으로도 처리할 수 있습니다. 전자세금계산서 시트에서 승인번호가 B열이고 합계가 G열이므로 B:G 범위를 잡고 6번째 열을 가져오면 됩니다.

=IF(J2="","승인번호없음",IFERROR(IF(VLOOKUP(J2,전자세금계산서!$B$2:$G$2000,6,FALSE)=I2,"일치","합계차이"),"계산서없음"))

승인번호가 왼쪽에 있지 않아 VLOOKUP 범위를 잡기 애매한 파일이라면 INDEX와 MATCH 조합도 좋습니다. 아래 수식은 승인번호 위치와 가져올 열 위치가 떨어져 있어도 사용할 수 있습니다.

=IF(J2="","승인번호없음",IFERROR(IF(INDEX(전자세금계산서!$G$2:$G$2000,MATCH(J2,전자세금계산서!$B$2:$B$2000,0))=I2,"일치","합계차이"),"계산서없음"))

실무 검산 체크리스트: 어디부터 봐야 빨리 잡힐까?

대사표가 틀렸을 때는 무작정 필터를 열어보는 것보다 아래 순서대로 확인하면 시간이 훨씬 줄어듭니다.

건수 비교도 같이 해두면 좋습니다. 검산표 C12에는 매출원장 건수, C13에는 계산서 건수를 구해 보겠습니다.

=COUNTIFS(매출원장!$A$2:$A$5000,">="&$C$2,매출원장!$A$2:$A$5000,"<="&$C$3,매출원장!$K$2:$K$5000,$B$3)

=COUNTIFS(전자세금계산서!$A$2:$A$2000,">="&$C$2,전자세금계산서!$A$2:$A$2000,"<="&$C$3,전자세금계산서!$C$2:$C$2000,$B$3)

금액 차이는 없는데 건수 차이가 있다면 합산 계산서와 개별 주문 원장이 섞인 상황일 수 있습니다. 반대로 건수는 같은데 금액 차이가 있으면 단가, 공급가액, 부가세 중 어느 열에서 차이가 나는지 행 단위로 내려가야 합니다.

사업자번호 정리가 너무 많을 때는 선택 범위만 일괄 정리

사업자번호 하이픈 제거를 매번 수식으로 처리해도 되지만, 외부에서 받은 계산서 파일을 별도 보관용으로 정리해야 한다면 선택 범위만 일괄 정리하는 방법도 쓸 수 있습니다. 아래 코드는 선택한 셀 안의 하이픈과 공백만 제거합니다.

실행 전에는 반드시 파일을 복사해 두세요. 매크로로 바꾼 값은 일반적인 실행 취소가 되지 않는 경우가 많습니다. 되돌리려면 저장하지 않고 닫거나, 미리 복사해 둔 원본 시트에서 다시 가져오는 방식이 안전합니다.

Sub 선택범위_사업자번호_하이픈제거()
    Dim c As Range
    If TypeName(Selection) <> "Range" Then Exit Sub
    For Each c In Selection
        If c.Value <> "" Then
            c.Value = Replace(Replace(CStr(c.Value), "-", ""), " ", "")
        End If
    Next c
End Sub

적용 범위는 사용자가 마우스로 선택한 셀입니다. 예를 들어 전자세금계산서 시트 C2:C2000을 선택한 뒤 실행하면 해당 범위의 사업자번호에서 하이픈과 공백이 제거됩니다. 다만 원본 증빙 파일 자체를 수정하면 나중에 비교가 어려울 수 있으니, 실무에서는 복사본 시트에서만 사용하는 편이 좋습니다.

적용 전에 확인할 것

이 방식의 핵심은 어려운 기능을 쓰는 것이 아니라, 날짜 범위, 사업자번호 형식, 반올림 기준, 승인번호 존재 여부를 분리해서 보는 것입니다. 한 셀에 모든 판단을 몰아넣으면 수식은 길어지고, 어디서 틀렸는지 찾기 어려워집니다.

실제 파일에 적용할 때는 먼저 검산표 B2의 기준월이 텍스트인지 날짜인지 정하고, C2와 C3이 실제 날짜로 계산되는지 확인하세요. 그다음 매출원장 K열처럼 사업자번호 보조열을 만든 뒤 SUMIFS를 걸어야 0 나오는 문제를 줄일 수 있습니다.

마지막으로 C7, C10의 차이 금액만 보지 말고 N열의 계산서대사 결과를 필터로 확인해 보세요. 계산서없음, 합계차이, 승인번호없음만 따로 모으면 월마감 대사에서 실제로 손봐야 할 행이 빠르게 좁혀집니다.