엑셀 카드매출 입금액이 안 맞을 때: 승인번호 앞자리 0·취소전표·수수료 ROUND 검산법

카드사 입금 정산을 맞추다 보면 매장 POS 매출은 12,438,000원인데 카드사 입금 예정표는 12,105,720원처럼 애매하게 차이 나는 경우가 있습니다. 처음에는 수수료 때문이라고 생각하지만, 실제로 뜯어보면 승인번호 앞자리 0이 사라졌거나, 취소전표가 매출로 남아 있거나, 승인일이 텍스트 날짜라서 SUMIFS 조건에서 빠진 경우가 꽤 많습니다.

이번 글은 카드매출 마감 파일을 복구했던 흐름 그대로 정리했습니다. 낯선 기능으로 한 번에 끝내기보다, 실무에서 많이 쓰는 TEXT, DATE, LEFT, RIGHT, XLOOKUP, INDEX MATCH, SUMIFS, COUNTIFS, IF, IFERROR, ROUND 조합으로 원인을 좁혀가겠습니다.

증상은 입금액 차이인데, 실제 원인은 승인번호와 날짜가 섞여 있었다

예시 파일은 카드매출 시트에 원천 데이터가 있고, 수수료율표는 같은 시트 오른쪽에 있다고 가정하겠습니다. 원천 데이터는 A4:H13 범위에 들어 있습니다.

그리고 K4:M8에는 카드사별 수수료율표를 둡니다. K열은 카드사, L열은 수수료율, M열은 비고입니다. 예를 들어 K5는 국민, L5는 2.15%입니다.

결과를 확인할 조건 셀은 O2:Q3에 두겠습니다. O2에는 기준월, P2에는 매장코드, Q2에는 카드사를 입력합니다. 예를 들어 O2는 2026-07-01, P2는 S001, Q2는 국민입니다. 결과 셀은 O5:O10에 순서대로 표시하겠습니다.

승인번호 앞자리 0이 사라지면 중복 확인도, 취소 매칭도 틀어진다

카드 승인번호는 숫자처럼 보여도 업무상으로는 코드입니다. D열 승인번호가 124567처럼 들어왔는데 카드사 자료에는 00124567로 있으면, 같은 승인 건인데도 서로 다른 값으로 인식됩니다.

G5 셀에 아래 수식을 넣고 G13까지 내려 복사합니다. 승인번호를 무조건 8자리 텍스트로 맞추는 방식입니다.

=TEXT(D5,"00000000")

만약 승인번호가 이미 텍스트로 들어와 있고 중간에 공백이 섞인 파일이라면 아래처럼 IFERROR를 한 번 감싸는 편이 안전합니다. 숫자로 바꿀 수 있으면 8자리로 맞추고, 아니면 오른쪽 8자리만 가져옵니다.

=IFERROR(TEXT(D5,"00000000"),RIGHT(D5,8))

여기서 흔한 실수는 D열 전체를 숫자 서식으로 바꾼 뒤 저장하는 것입니다. 이미 사라진 앞자리 0은 서식만 바꾼다고 복구되지 않습니다. 원본 승인번호가 카드사 파일에 남아 있다면 원본을 다시 가져오고, 내부 파일만 있다면 TEXT로 자리수를 통일한 뒤 비교해야 합니다.

승인일이 텍스트라서 SUMIFS가 0으로 나오는지 먼저 잡았다

A열 승인일이 2026.07.03처럼 점으로 들어온 경우, 눈으로는 날짜처럼 보여도 실제로는 텍스트일 수 있습니다. SUMIFS에서 O2 이상, EOMONTH(O2,0) 이하 조건을 걸면 이런 행은 집계에서 빠집니다.

H5 셀에는 승인일을 실제 날짜로 바꾸는 수식을 넣습니다. A열 값이 2026.07.03 형식이라고 가정했습니다.

=IFERROR(DATE(LEFT(A5,4),MID(A5,6,2),RIGHT(A5,2)),A5)

이 수식은 LEFT로 연도 4자리, MID로 월 2자리, RIGHT로 일 2자리를 잘라 DATE로 조립합니다. 이미 날짜로 들어온 셀이라면 IFERROR 뒤의 A5가 그대로 반환됩니다. 단, A열에 20260703처럼 점이 없는 형식이 섞여 있다면 별도 보정이 필요합니다.

텍스트 날짜가 제대로 바뀌었는지 확인하려면 H열 표시 형식을 일반으로 바꿔 보세요. 2026-07-03이 46206 같은 숫자로 보이면 실제 날짜값입니다. 반대로 여전히 2026.07.03처럼 보인다면 텍스트가 남아 있을 가능성이 큽니다.

취소전표는 빼야 하는데, 승인금액을 그대로 더해서 차이가 났다

이제 카드매출의 핵심인 부호 처리를 합니다. I열을 새로 만들고 열 이름을 정산대상금액으로 입력합니다. I5 셀에는 구분이 취소면 음수, 승인이면 양수로 바꾸는 수식을 넣습니다.

=IF(E5="취소",-F5,F5)

취소라는 글자 앞뒤에 공백이 들어가는 파일도 있습니다. 그런 경우에는 조건이 맞지 않아 취소가 양수로 남습니다. 데이터가 지저분하다면 먼저 E열을 확인하고, 값이 '취소 '처럼 보이는 행이 있는지 필터로 찾아보는 것이 좋습니다.

이제 O5 셀에는 기준월, 매장코드, 카드사 조건으로 승인금액을 집계합니다.

=SUMIFS($I$5:$I$13,$H$5:$H$13,">="&$O$2,$H$5:$H$13,"<="&EOMONTH($O$2,0),$B$5:$B$13,$P$2,$C$5:$C$13,$Q$2)

이 수식에서 자기 파일에 맞게 바꿔야 할 부분은 범위입니다. 원천 데이터가 5행부터 3000행까지 있다면 $I$5:$I$3000, $H$5:$H$3000처럼 끝 행을 맞춰야 합니다. 조건 셀 O2, P2, Q2는 그대로 쓰거나 본인 집계표 위치에 맞게 바꾸면 됩니다.

수수료율 조회는 XLOOKUP, 안 되면 INDEX MATCH로 같은 결과를 만든다

카드사별 수수료율은 K5:L8 범위에서 가져오겠습니다. O6 셀에는 선택한 카드사 Q2의 수수료율을 표시합니다.

=IFERROR(XLOOKUP($Q$2,$K$5:$K$8,$L$5:$L$8),0)

XLOOKUP을 사용할 수 없는 환경이라면 INDEX와 MATCH 조합으로 바꿔도 됩니다. 실무 파일을 여러 사람이 열어야 할 때는 이 방식이 더 무난할 때도 있습니다.

=IFERROR(INDEX($L$5:$L$8,MATCH($Q$2,$K$5:$K$8,0)),0)

수수료율이 0으로 나온다면 카드사명이 다를 가능성이 큽니다. 예를 들어 원천 데이터 C열에는 'KB국민'이라고 되어 있는데 수수료율표 K열에는 '국민'이라고 되어 있으면 조회가 실패합니다. 이때는 원천 데이터의 카드사명을 표준명으로 바꾸거나, 별도 매핑표를 만들어 통일해야 합니다.

입금예정액은 ROUND 위치가 중요하다

카드사 정산은 1원 차이가 자주 납니다. 특히 수수료를 전체 합계에 한 번 곱할지, 건별로 계산 후 합산할지에 따라 결과가 달라질 수 있습니다. 카드사 입금표가 건별 수수료를 반올림한 뒤 합산하는 방식이라면, 우리 계산도 건별 ROUND를 맞춰야 합니다.

J열을 새로 만들고 열 이름을 예상입금액으로 입력합니다. J5 셀에는 카드사별 수수료율을 찾아 건별 입금액을 계산하는 수식을 넣고 J13까지 복사합니다.

=ROUND(I5*(1-IFERROR(INDEX($L$5:$L$8,MATCH(C5,$K$5:$K$8,0)),0)),0)

이 수식은 I열 정산대상금액에 카드사 수수료율을 차감해 입금예정액을 계산합니다. 취소전표는 I열에서 이미 음수로 바뀌었기 때문에 J열도 자동으로 음수 입금 조정액이 됩니다.

O7 셀에는 조건별 예상입금액을 합산합니다.

=SUMIFS($J$5:$J$13,$H$5:$H$13,">="&$O$2,$H$5:$H$13,"<="&EOMONTH($O$2,0),$B$5:$B$13,$P$2,$C$5:$C$13,$Q$2)

만약 O5의 순매출에 수수료율을 곱해 한 번만 ROUND하면 카드사 입금표와 1원, 2원씩 차이 날 수 있습니다. 정산 자료가 건별 계산인지 합계 계산인지 먼저 확인하고 ROUND 위치를 맞추는 것이 포인트입니다.

COUNTIFS로 빠진 행과 중복 승인번호를 확인했다

합계가 맞지 않을 때는 바로 수식을 고치기보다, 몇 건이 조건에 들어오는지부터 확인하면 빠릅니다. O8 셀에는 해당 월, 매장, 카드사 조건에 들어오는 건수를 구합니다.

=COUNTIFS($H$5:$H$13,">="&$O$2,$H$5:$H$13,"<="&EOMONTH($O$2,0),$B$5:$B$13,$P$2,$C$5:$C$13,$Q$2)

카드사 입금표에는 12건인데 엑셀 집계는 10건이면 날짜 조건이나 카드사명이 틀린 것입니다. 반대로 건수는 같은데 금액만 다르면 취소 부호, 수수료율, ROUND 위치를 확인해야 합니다.

중복 승인번호도 자주 나옵니다. K열 옆이 아니라 원천 데이터 쪽에 L열을 추가하기 어렵다면, 임시로 N열에 중복확인을 만들고 N5에 아래 수식을 넣습니다.

=IF(COUNTIFS($G$5:$G$13,G5,$B$5:$B$13,B5,$C$5:$C$13,C5)>1,"중복확인","")

승인과 취소가 같은 승인번호로 한 쌍 존재하는 것은 정상일 수 있습니다. 하지만 같은 승인번호, 같은 매장, 같은 카드사, 같은 구분의 행이 두 번 들어 있다면 중복 적재 가능성이 높습니다. 그때는 원본에서 한 번 내려받았는지, 카드사별 파일을 합치면서 같은 시트를 두 번 붙였는지 확인해야 합니다.

실제 검증은 원천합계, 수수료합계, 입금예정액을 따로 놓고 봐야 빨랐다

O9 셀에는 예상 수수료를 계산합니다. 건별 반올림 기준을 확인하려면 정산대상금액과 예상입금액의 차이를 합산하는 방식이 좋습니다.

=O5-O7

O10 셀에는 카드사에서 받은 입금 예정액을 직접 입력하고, O11 셀에는 차이를 계산합니다. 예를 들어 O10에 카드사 입금표 금액을 넣었다면 O11은 아래처럼 둡니다.

=O7-O10

O11이 0이면 이번 기준월, 매장, 카드사 조건에서는 계산 로직이 맞는 것입니다. 차이가 난다면 금액만 보지 말고 O8의 건수부터 확인하세요. 건수 차이는 조건 문제, 건수 일치 후 금액 차이는 부호나 수수료 계산 문제일 확률이 높습니다.

반복 검산이 많다면 선택 범위의 앞자리 0을 한 번에 맞추는 VBA도 유용하다

승인번호 열을 매번 TEXT 수식으로 만들기 어렵거나, 카드사별 파일을 붙여넣은 뒤 선택 범위만 8자리 승인번호로 정리해야 한다면 작은 매크로를 써도 됩니다. 아래 코드는 선택한 셀 범위의 값을 8자리 텍스트로 바꿉니다.

실행 전에는 원본 파일을 다른 이름으로 저장해 두세요. 매크로는 선택 범위 값을 직접 바꾸므로, 되돌리기는 Ctrl+Z가 안 되는 경우가 있습니다. 잘못 실행했을 때는 저장하지 말고 닫은 뒤 백업 파일을 다시 여는 방식이 안전합니다.

Sub 승인번호_8자리_정리()
    Dim rng As Range
    Dim c As Range
    
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set rng = Selection
    
    For Each c In rng.Cells
        If Len(c.Value) > 0 Then
            c.NumberFormat = "@"
            If IsNumeric(c.Value) Then
                c.Value = Format(c.Value, "00000000")
            Else
                c.Value = Right(CStr(c.Value), 8)
            End If
        End If
    Next c
End Sub

적용 범위는 승인번호가 들어 있는 셀만 선택하는 것이 좋습니다. 예를 들어 D5:D3000만 선택하고 실행해야 하며, 금액 열이나 날짜 열까지 같이 선택하면 값이 의도와 다르게 바뀔 수 있습니다. 처음에는 복사본 시트에서 D5:D20 정도만 테스트해 보고 결과를 확인한 뒤 전체 범위에 적용하세요.

다음 달에도 같은 문제가 안 생기게 보는 체크포인트

카드매출 입금액 차이는 대부분 큰 수식 하나보다 작은 정리 기준 몇 개에서 갈립니다. 승인번호는 TEXT로 자리수를 통일하고, 승인일은 DATE로 실제 날짜값을 만들고, 취소전표는 IF로 음수 처리한 뒤 SUMIFS에 넣는 순서가 안정적입니다.

마지막으로 파일을 닫기 전에 이 네 가지만 확인해 보세요. 첫째, H열 정리승인일이 실제 날짜값인지 확인합니다. 둘째, G열 승인번호가 모두 8자리인지 봅니다. 셋째, E열 취소 건이 I열에서 음수로 바뀌었는지 필터로 확인합니다. 넷째, 카드사별 수수료율표의 명칭이 원천 데이터와 정확히 같은지 점검합니다.

정산 업무는 1원 차이도 다시 열어보게 됩니다. 그래서 수식 자체보다 검증 셀을 같이 만들어 두는 습관이 중요합니다. O5 순매출, O7 예상입금액, O8 건수, O11 차이까지 한 화면에 놓아두면 다음 달 같은 파일이 와도 원인 찾는 시간이 훨씬 줄어듭니다.