엑셀 카드매출 정산액이 안 맞을 때: 승인취소·앞자리 0 매장코드·수수료 ROUND 검산법

엑셀 카드매출 정산을 맞추다 보면 SUMIFS 합계가 0으로 나오거나, 카드사 입금예상액이 ERP와 몇 백 원씩 안 맞는 경우가 꽤 자주 있습니다. 특히 매장코드 앞자리 0이 사라지고, 승인취소가 양수로 들어오고, 승인일이 20260703 같은 숫자처럼 들어오면 단순 합계로는 거의 맞지 않습니다.

이번 예제는 카드사 원장을 받아 월별·매장별·카드사별로 승인금액, 수수료, 입금예상액을 검산하는 상황입니다. 낯선 기능으로 한 번에 끝내기보다, 실무에서 가장 많이 쓰는 IF, TEXT, DATE, SUMIFS, COUNTIFS, VLOOKUP, XLOOKUP, INDEX MATCH, ROUND 조합으로 차근차근 잡아보겠습니다.

Q. 카드사 원장 표는 어떻게 잡아야 SUMIFS가 정확히 먹나요?

먼저 원본 시트 이름을 카드원장이라고 하겠습니다. 데이터는 2행부터 5000행까지 들어온다고 가정합니다. A열부터 G열까지는 카드사에서 받은 원본이고, H열부터 L열까지는 계산용 보조열입니다.

예시 데이터는 아래처럼 들어왔다고 보겠습니다. 같은 매장인데 어떤 행은 0012, 어떤 행은 12로 들어오는 것이 핵심 문제입니다.

Q. SUMIFS가 0으로 나오는 첫 번째 원인은 뭔가요?

가장 흔한 원인은 매장코드가 서로 다른 값으로 인식되는 것입니다. 사람 눈에는 12와 0012가 같은 매장처럼 보여도, 엑셀은 숫자 12와 텍스트 0012를 다르게 봅니다. 그래서 H열에 집계용 매장코드를 따로 만들어야 합니다.

H2 셀에 아래 수식을 넣고 H5000까지 복사합니다. B열에 12처럼 숫자로 들어와도 0012로 바꾸고, 0012-01처럼 지점 코드가 붙어도 앞 4자리만 사용합니다.

=IFERROR(TEXT(B2,"0000"),TEXT(VALUE(LEFT(B2,4)),"0000"))

여기서 자기 파일의 매장코드가 5자리라면 "0000"을 "00000"으로 바꾸고, LEFT(B2,4)도 LEFT(B2,5)로 바꾸면 됩니다.

Q. 승인일이 20260703이면 날짜 조건 집계가 왜 틀리나요?

SUMIFS에서 월 조건을 걸려면 승인일이 진짜 날짜값이어야 합니다. 그런데 카드사 파일은 승인일이 20260703처럼 들어오거나, 2026.07.03처럼 텍스트로 들어오는 경우가 많습니다. 이 상태에서 >=2026-07-01 같은 조건을 걸면 월별 합계가 빠질 수 있습니다.

I2 셀에는 정리승인일을 만듭니다. 아래 수식은 A열이 엑셀 날짜 일련번호이면 그대로 쓰고, 20260703 같은 8자리 숫자나 텍스트이면 DATE 함수로 다시 날짜를 만듭니다.

=IF(ISNUMBER(A2),IF(A2>60000,DATE(LEFT(TEXT(A2,"0"),4),MID(TEXT(A2,"0"),5,2),RIGHT(TEXT(A2,"0"),2)),A2),IF(LEN(A2)=8,DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))))

수식을 넣은 뒤 I열 표시 형식은 yyyy-mm-dd로 바꿔 주세요. 보이는 모양만 바뀌는 것이 아니라, SUMIFS가 월초부터 월말까지 비교할 수 있는 상태인지 확인하는 과정입니다.

Q. 승인취소가 있으면 금액은 어떻게 처리해야 하나요?

카드사 원장에서는 취소 건도 승인금액처럼 양수로 들어오는 경우가 많습니다. 이 상태로 합계를 내면 실제 매출보다 크게 나옵니다. J열에 부호금액을 만들어 승인은 양수, 취소는 음수로 바꿉니다.

=IF(E2="취소",-F2,F2)

이제 수수료도 부호금액 기준으로 계산해야 합니다. K2에는 수수료를 계산합니다. 카드사 정산은 원 단위 반올림 때문에 1원 차이가 생기기 쉬우므로 ROUND를 같이 씁니다.

=ROUND(J2*G2,0)

L2에는 입금예상액을 계산합니다. 승인금액에서 수수료를 뺀 값이고, 취소 건은 자연스럽게 음수 입금예상액으로 계산됩니다.

=J2-K2

Q. 같은 승인번호가 두 번 들어온 건 어떻게 찾나요?

정산액이 갑자기 커졌다면 중복 행을 먼저 봐야 합니다. 특히 같은 승인번호, 같은 매장, 같은 카드사, 같은 구분, 같은 금액이 반복되면 원본 다운로드가 중복되었거나 붙여넣기가 두 번 된 경우가 많습니다.

M2 셀에 중복체크 열을 만들고 아래 수식을 넣습니다.

=IF(COUNTIFS($H$2:$H$5000,H2,$C$2:$C$5000,C2,$D$2:$D$5000,D2,$E$2:$E$5000,E2,$F$2:$F$5000,F2)>1,"중복확인","")

여기서 주의할 점은 승인과 취소가 같은 승인번호로 같이 있는 것은 정상일 수 있다는 점입니다. 그래서 COUNTIFS 조건에 E열 구분까지 넣었습니다. 승인끼리 중복인지, 취소끼리 중복인지 분리해서 보는 방식입니다.

Q. 월별·매장별·카드사별 정산표는 어떤 수식으로 만들면 되나요?

이제 집계 시트를 만들어 보겠습니다. 시트 이름은 월정산이라고 하고, N2에는 기준월을 입력합니다. 예를 들어 N2에 2026-07-01을 날짜로 입력합니다.

R2에는 순승인금액을 구합니다. 월초는 N2, 월말은 EOMONTH로 잡습니다.

=SUMIFS(카드원장!$J$2:$J$5000,카드원장!$H$2:$H$5000,$P2,카드원장!$C$2:$C$5000,$Q2,카드원장!$I$2:$I$5000,">="&$N$2,카드원장!$I$2:$I$5000,"<="&EOMONTH($N$2,0))

S2에는 수수료 합계를 구합니다.

=SUMIFS(카드원장!$K$2:$K$5000,카드원장!$H$2:$H$5000,$P2,카드원장!$C$2:$C$5000,$Q2,카드원장!$I$2:$I$5000,">="&$N$2,카드원장!$I$2:$I$5000,"<="&EOMONTH($N$2,0))

T2에는 입금예상액을 구합니다.

=SUMIFS(카드원장!$L$2:$L$5000,카드원장!$H$2:$H$5000,$P2,카드원장!$C$2:$C$5000,$Q2,카드원장!$I$2:$I$5000,">="&$N$2,카드원장!$I$2:$I$5000,"<="&EOMONTH($N$2,0))

수식에서 바꿔야 할 곳은 크게 세 군데입니다. 원본 범위가 5000행보다 많다면 $5000을 실제 마지막 행보다 넉넉하게 늘리고, 매장코드 조건 셀이 P열이 아니라면 $P2를 바꾸면 됩니다. 기준월 위치가 N2가 아니라면 $N$2도 같이 바꿔야 합니다.

Q. 매장명을 자동으로 붙이고 싶으면 VLOOKUP을 쓰면 되나요?

네. 월정산 시트의 W열과 X열에 매장 마스터를 만들어 두면 됩니다. W2:W20에는 매장코드, X2:X20에는 매장명을 둔다고 가정합니다.

U2에 매장명을 표시하려면 VLOOKUP으로 이렇게 씁니다.

=IFERROR(VLOOKUP(P2,$W$2:$X$20,2,FALSE),"매장코드 확인")

XLOOKUP을 쓸 수 있는 버전이라면 아래처럼 써도 됩니다.

=IFERROR(XLOOKUP(P2,$W$2:$W$20,$X$2:$X$20),"매장코드 확인")

XLOOKUP을 쓰기 어려운 파일이라면 INDEX와 MATCH 조합도 안정적입니다.

=IFERROR(INDEX($X$2:$X$20,MATCH(P2,$W$2:$W$20,0)),"매장코드 확인")

여기서도 핵심은 매장 마스터의 W열 코드가 반드시 0012처럼 같은 자리수의 텍스트 코드여야 한다는 점입니다. 원장에서는 H열을 정리했는데, 마스터는 숫자 12로 남아 있으면 다시 #N/A가 나올 수 있습니다.

Q. 계산 결과가 ERP와 안 맞을 때는 어디부터 확인하나요?

먼저 월정산의 R2 순승인금액과 카드원장 J열 필터 합계를 비교합니다. H열 매장코드가 0012이고, C열 카드사가 국민카드이며, I열 날짜가 2026-07-01부터 2026-07-31인 행만 필터링했을 때 합계가 R2와 같아야 합니다.

다음으로 M열에 중복확인이 있는지 봅니다. 중복 행이 있다면 해당 행을 삭제하기 전에 원본 파일과 비교해야 합니다. 승인번호가 같아도 승인과 취소가 한 쌍으로 있는 건 정상일 수 있으니, 구분과 금액까지 같이 확인하는 습관이 좋습니다.

마지막으로 수수료율 G열을 확인합니다. 카드사별, 매장별로 수수료율이 다른데 한 가지 요율을 전체에 붙이면 ROUND를 써도 큰 차이가 납니다. 1원 차이는 반올림 기준 문제일 수 있지만, 몇 천 원 이상 차이가 나면 요율 또는 취소 반영 여부를 먼저 의심해야 합니다.

Q. 매월 같은 원장을 받으면 수식 복사가 너무 번거로운데요?

반복 작업이라면 보조열 수식을 한 번에 채우는 간단한 VBA를 써도 좋습니다. 아래 코드는 현재 활성화된 시트에서 A열 마지막 행을 기준으로 H:L열 제목과 수식을 채웁니다. 실행 전에는 반드시 파일을 복사해 두세요. 매크로 실행 후 변경 내용은 일반적인 Ctrl+Z로 되돌리기 어렵기 때문에, 잘못 실행했다면 저장하지 말고 닫거나 백업 파일로 돌아가는 방식이 안전합니다.

Sub 카드정산_보조열채우기()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "2행부터 데이터가 있는지 확인하세요."
        Exit Sub
    End If

    ws.Range("H1:L1").Value = Array("정리매장코드", "정리승인일", "부호금액", "수수료", "입금예상")

    ws.Range("H2:H" & lastRow).Formula = "=IFERROR(TEXT(B2,""0000""),TEXT(VALUE(LEFT(B2,4)),""0000""))"
    ws.Range("I2:I" & lastRow).Formula = "=IF(ISNUMBER(A2),IF(A2>60000,DATE(LEFT(TEXT(A2,""0""),4),MID(TEXT(A2,""0""),5,2),RIGHT(TEXT(A2,""0""),2)),A2),IF(LEN(A2)=8,DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))))"
    ws.Range("J2:J" & lastRow).Formula = "=IF(E2=""취소"",-F2,F2)"
    ws.Range("K2:K" & lastRow).Formula = "=ROUND(J2*G2,0)"
    ws.Range("L2:L" & lastRow).Formula = "=J2-K2"

    ws.Range("I2:I" & lastRow).NumberFormat = "yyyy-mm-dd"

    MsgBox "카드정산 보조열 수식 입력 완료: 2행부터 " & lastRow & "행까지"
End Sub

이 코드는 카드원장처럼 A열에 승인일이 있고, B열부터 G열까지 원본 구조가 동일할 때만 그대로 쓰면 됩니다. 열 위치가 다르면 코드 안의 A, B, E, F, G 참조를 자기 파일에 맞게 바꿔야 합니다.

이럴 때 이렇게 쓰면 된다

카드매출 정산표가 안 맞을 때는 바로 SUMIFS 수식을 고치기보다, 먼저 원본을 집계 가능한 상태로 정리하는 것이 빠릅니다. 매장코드는 TEXT와 LEFT로 자리수를 맞추고, 승인일은 DATE로 진짜 날짜로 바꾸고, 취소는 IF로 음수 처리한 뒤, 수수료는 ROUND로 원 단위 기준을 맞추면 됩니다.

그 다음 SUMIFS로 월별·매장별·카드사별 합계를 만들고, COUNTIFS로 중복 승인 건을 확인하세요. 매장명이나 담당 구역을 붙여야 한다면 VLOOKUP, XLOOKUP, INDEX MATCH 중 파일 버전에 맞는 방식을 쓰면 됩니다. 정산 오류는 대개 수식 하나의 문제가 아니라 코드, 날짜, 취소, 중복, 반올림이 같이 엉킨 문제라서 이 순서대로 보면 훨씬 빨리 잡힙니다.