엑셀 수수료 정산액이 안 맞을 때: 주문번호 채널코드·상품분류·월별 요율 SUMIFS 검산법

엑셀 수수료 정산액이 쇼핑몰 관리자 화면과 안 맞을 때, 생각보다 원인은 복잡한 함수가 아니라 주문번호에서 채널코드를 잘못 뽑았거나, 상품코드 분류가 틀렸거나, 날짜가 텍스트라서 SUMIFS 조건에 걸리지 않는 경우가 많습니다. 특히 월별 요율표를 따로 관리하는 회사라면 VLOOKUP이 #N/A를 내거나, SUMIFS가 0으로 나와서 정산 마감 직전에 꽤 고생하게 됩니다.

이번 예시는 온라인몰 판매내역에서 주문번호 앞 2자리로 채널을 구분하고, 상품코드 중간 2자리로 카테고리를 뽑은 뒤, 기준월별 수수료율을 찾아 정산 수수료를 계산하는 상황입니다. 실무에서 자주 쓰는 SUMIFS, VLOOKUP, INDEX, MATCH, IFERROR, LEFT, MID, RIGHT, TEXT, DATE, EOMONTH, ROUND 조합으로 차근차근 잡아보겠습니다.

문제 상황: 판매금액은 맞는데 수수료 합계만 1,200원씩 차이 나는 파일

예를 들어 판매내역 시트에는 A열부터 F열까지 원본 데이터가 들어온다고 가정하겠습니다. 원본은 쇼핑몰에서 내려받은 자료라서 주문일이 날짜가 아니라 20260703 같은 8자리 값으로 들어와 있습니다.

실제 데이터 범위는 판매내역!A2:F1000까지 있고, G열부터 N열까지는 계산용 보조열로 사용할 예정입니다. 요약 결과는 정산요약 시트에서 B2에 기준월, B3에 채널코드, B4에 카테고리코드를 입력하고 B6부터 결과를 표시하는 구조로 만들겠습니다.

원인부터 잡아야 합니다: SUMIFS 0 나옴, VLOOKUP #N/A, ROUND 차이가 한꺼번에 생기는 이유

이 파일에서 수수료 정산액이 틀어지는 원인은 보통 세 가지입니다. 첫째, A열 주문일이 진짜 날짜가 아니라 텍스트라서 월 조건을 걸 때 빠집니다. 눈으로 보면 2026년 7월 주문처럼 보이지만, 엑셀 입장에서는 날짜가 아니라 문자일 수 있습니다.

둘째, 주문번호와 상품코드에서 조건값을 뽑는 위치가 흔들립니다. 예를 들어 주문번호 앞 2자리 NV를 채널코드로 써야 하는데 LEFT 범위를 3자리로 잡으면 NV-가 되어 수수료율표의 NV와 일치하지 않습니다.

셋째, 수수료 계산에서 반올림 위치가 다릅니다. 어떤 쇼핑몰은 주문 건별로 수수료를 ROUND 처리한 뒤 합산하고, 어떤 내부 보고서는 총 판매금액에 요율을 곱한 뒤 마지막에 한 번만 반올림합니다. 이 차이만으로도 월말에 몇 백 원에서 몇 천 원까지 차이가 납니다.

판매내역 보조열 구성: 날짜, 월, 채널, 카테고리, 요율키를 분리합니다

먼저 판매내역 시트에서 G열부터 보조열을 만듭니다. 보조열을 만드는 이유는 수식이 조금 길어지더라도 나중에 오류 위치를 바로 찾기 위해서입니다. 한 셀에 모든 계산을 몰아넣으면 결과는 나오지만, 어디서 틀렸는지 확인하기가 어렵습니다.

G2에는 주문일을 날짜로 바꾸는 수식을 넣습니다. A열 값이 20260703처럼 들어왔다면 LEFT, MID, RIGHT로 연도·월·일을 분리한 뒤 DATE로 다시 조립합니다.

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

H2에는 정산월을 만듭니다. 월별 SUMIFS를 안정적으로 하려면 2026-07 같은 텍스트보다, 해당 월의 첫날 날짜를 넣어두는 편이 좋습니다.

=IFERROR(DATE(LEFT(A2,4),MID(A2,5,2),1),"")

I2에는 주문번호의 앞 2자리 채널코드를 뽑습니다. 주문번호가 NV-20260703-001이면 결과는 NV가 됩니다.

=LEFT(B2,2)

J2에는 상품코드에서 카테고리코드를 뽑습니다. 예시 상품코드 PR-BT-1001에서 BT는 4번째부터 2글자이므로 MID를 사용합니다.

=MID(C2,4,2)

M2에는 수수료율표를 찾기 위한 요율키를 만듭니다. 월은 TEXT로 yyyymm 형태로 맞추고, 채널과 카테고리를 붙입니다. 구분자 |를 넣는 이유는 202607NVBT처럼 붙였을 때 코드 경계가 애매해지는 일을 막기 위해서입니다.

=TEXT(H2,"yyyymm")&"|"&I2&"|"&J2

여기까지 만든 수식은 G2, H2, I2, J2, M2에 각각 입력한 뒤 1000행까지 복사합니다. 실제 파일이 3만 행이면 범위를 넉넉하게 늘리면 됩니다.

수수료율표 만들기: VLOOKUP이 실패하지 않게 키를 왼쪽에 둡니다

수수료율표 시트는 A열부터 F열까지 아래처럼 만듭니다. 중요한 점은 VLOOKUP으로 조회할 키가 조회범위의 가장 왼쪽에 있어야 한다는 것입니다. 그래서 E열에 요율키를 만들고, F열에 수수료율을 한 번 더 참조해 두면 실무에서 관리하기 편합니다.

수수료율표의 E2에는 아래 수식을 넣고 아래로 복사합니다. 기준월이 실제 날짜라면 TEXT로 월 형식을 통일해 줍니다.

=TEXT(A2,"yyyymm")&"|"&B2&"|"&C2

F2는 단순히 D열 요율을 참조합니다.

=D2

이제 판매내역 시트로 돌아와 K2에 수수료율을 가져옵니다. M열의 요율키가 수수료율표 E열에서 발견되면 F열 요율을 가져오고, 없으면 0으로 표시합니다.

=IFERROR(VLOOKUP(M2,수수료율표!$E$2:$F$50,2,FALSE),0)

VLOOKUP 대신 INDEX와 MATCH를 선호한다면 아래처럼 써도 됩니다. 두 수식의 목적은 같습니다. 다만 팀원이 VLOOKUP에 더 익숙하다면, 월말 정산 파일에서는 VLOOKUP 방식이 설명하기 편할 때가 많습니다.

=IFERROR(INDEX(수수료율표!$F$2:$F$50,MATCH(M2,수수료율표!$E$2:$E$50,0)),0)

건별 수수료 계산: 취소건 제외와 ROUND 위치를 명확히 합니다

L2에는 건별 수수료를 계산합니다. 상태가 취소면 0, 정상 거래면 판매금액에 수수료율을 곱하고 ROUND로 원 단위 반올림합니다.

=IF(F2="취소",0,ROUND(E2*K2,0))

여기서 중요한 것은 정산 기준이 건별 반올림인지, 월 합계 반올림인지입니다. 쇼핑몰 정산서가 주문 건별로 수수료를 반올림해서 보여준다면 L열처럼 건별로 ROUND를 넣는 방식이 맞습니다. 반대로 내부 손익 보고서에서 총액 기준으로 계산한다면 요약표에서 마지막에 ROUND를 한 번만 해야 합니다.

그리고 K열 수수료율이 0으로 나온 행은 반드시 확인해야 합니다. 진짜 0% 이벤트가 아니라면 대부분 요율키가 안 맞은 것입니다. 채널코드에 하이픈이 붙었는지, 카테고리 MID 위치가 틀렸는지, 수수료율표 기준월이 날짜가 아니라 텍스트인지부터 보면 됩니다.

정산요약에서 SUMIFS로 월별·채널별·카테고리별 합계 검산하기

이제 정산요약 시트에 조건 셀을 만듭니다. B2에는 기준월 2026-07-01, B3에는 채널코드 NV, B4에는 카테고리코드 BT를 입력합니다.

B6에는 정상 판매금액 합계를 구합니다. H열 정산월, I열 채널코드, J열 카테고리코드, F열 상태를 조건으로 겁니다.

=SUMIFS(판매내역!$E$2:$E$1000,판매내역!$H$2:$H$1000,$B$2,판매내역!$I$2:$I$1000,$B$3,판매내역!$J$2:$J$1000,$B$4,판매내역!$F$2:$F$1000,"정상")

B7에는 건별로 계산된 수수료 L열을 합산합니다. 쇼핑몰 정산서의 수수료 합계와 비교할 때는 보통 이 값이 더 잘 맞습니다.

=SUMIFS(판매내역!$L$2:$L$1000,판매내역!$H$2:$H$1000,$B$2,판매내역!$I$2:$I$1000,$B$3,판매내역!$J$2:$J$1000,$B$4,판매내역!$F$2:$F$1000,"정상")

B8에는 정상 주문건수를 세어 검산합니다. 금액만 맞춰보면 중복 주문이나 취소 누락을 놓치기 쉬우므로 COUNTIFS로 건수도 같이 봅니다.

=COUNTIFS(판매내역!$H$2:$H$1000,$B$2,판매내역!$I$2:$I$1000,$B$3,판매내역!$J$2:$J$1000,$B$4,판매내역!$F$2:$F$1000,"정상")

만약 정산서가 월 전체 기간을 날짜 범위로 잡아야 하는 구조라면 H열 정산월 대신 G열 주문일자를 기준으로 아래처럼 작성해도 됩니다. 기준월 B2 이상, EOMONTH(B2,0) 이하 조건을 쓰는 방식입니다.

=SUMIFS(판매내역!$E$2:$E$1000,판매내역!$G$2:$G$1000,">="&$B$2,판매내역!$G$2:$G$1000,"<="&EOMONTH($B$2,0),판매내역!$I$2:$I$1000,$B$3,판매내역!$J$2:$J$1000,$B$4,판매내역!$F$2:$F$1000,"정상")

검증 방법: 차이가 날 때는 금액보다 키와 건수부터 봅니다

수수료 합계가 계속 안 맞을 때는 결과 셀만 들여다보지 말고, 아래 순서로 확인하는 것이 빠릅니다. 먼저 판매내역 K열에서 수수료율이 0인 정상 주문이 있는지 확인합니다. 정산요약!B10에 아래 수식을 넣으면 기준월 기준으로 요율 누락 건수를 볼 수 있습니다.

=COUNTIFS(판매내역!$H$2:$H$1000,$B$2,판매내역!$K$2:$K$1000,0,판매내역!$F$2:$F$1000,"정상")

다음으로 주문번호 중복을 확인합니다. 판매내역 N2에 아래 수식을 넣고 아래로 복사하면 같은 주문번호가 두 번 들어온 행을 바로 표시할 수 있습니다.

=IF(COUNTIFS($B$2:$B$1000,B2)>1,"중복확인","")

마지막으로 반올림 차이를 따로 계산해 봅니다. 정산요약 B12에 총액 기준 수수료를 계산해 두고, B7의 건별 수수료 합계와 비교하면 ROUND 위치 차이인지 판단할 수 있습니다.

=ROUND(B6*VLOOKUP(TEXT($B$2,"yyyymm")&"|"&$B$3&"|"&$B$4,수수료율표!$E$2:$F$50,2,FALSE),0)

B13에는 차이를 표시합니다.

=B7-B12

B13이 0이 아니더라도 무조건 오류는 아닙니다. 정산서 기준이 건별 반올림이면 B7이 맞고, 총액 기준 반올림이면 B12가 맞습니다. 중요한 것은 어느 방식으로 보고할지 회사 기준을 정해 두는 것입니다.

월마다 같은 작업을 한다면 보조열 채우기는 VBA로 줄일 수 있습니다

매월 판매내역을 새로 붙여넣고 G열부터 N열까지 같은 수식을 반복해서 복사한다면 간단한 VBA로 시간을 줄일 수 있습니다. 아래 코드는 판매내역 시트의 A열 마지막 행을 기준으로 G, H, I, J, M, K, L, N열 수식을 자동 입력합니다.

실행 전에는 파일을 먼저 저장하거나 복사본에서 테스트하세요. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않을 수 있으므로, 결과가 마음에 들지 않으면 저장하지 않고 닫거나 백업 파일로 돌아가는 방식이 안전합니다.

Sub FillCommissionHelper()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("판매내역")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "입력된 판매내역이 없습니다."
        Exit Sub
    End If

    With ws
        .Range("G2:G" & lastRow).Formula = "=IFERROR(DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),"""")"
        .Range("H2:H" & lastRow).Formula = "=IFERROR(DATE(LEFT(A2,4),MID(A2,5,2),1),"""")"
        .Range("I2:I" & lastRow).Formula = "=LEFT(B2,2)"
        .Range("J2:J" & lastRow).Formula = "=MID(C2,4,2)"
        .Range("M2:M" & lastRow).Formula = "=TEXT(H2,""yyyymm"")&""|""&I2&""|""&J2"
        .Range("K2:K" & lastRow).Formula = "=IFERROR(VLOOKUP(M2,수수료율표!$E$2:$F$50,2,FALSE),0)"
        .Range("L2:L" & lastRow).Formula = "=IF(F2=""취소"",0,ROUND(E2*K2,0))"
        .Range("N2:N" & lastRow).Formula = "=IF(COUNTIFS($B$2:$B$1000,B2)>1,""중복확인"","""")"
    End With

    MsgBox "수수료 정산 보조열 입력이 완료되었습니다."
End Sub

다만 이 코드는 판매내역이 1000행 이하라는 전제의 중복 확인 수식을 포함하고 있습니다. 데이터가 5만 행까지 늘어난다면 N열 수식의 $B$1000 범위를 실제 행 수에 맞게 바꾸거나, VBA 코드에서 lastRow를 넣도록 수정하는 편이 좋습니다.

실무에서 적용할 때 볼 것

수수료 정산 파일은 단순히 판매금액에 요율을 곱하는 문제가 아닙니다. 주문일이 날짜로 변환됐는지, 주문번호에서 채널코드를 정확히 뽑았는지, 상품코드 분류 위치가 맞는지, 월별 요율표의 키가 같은 형식인지가 먼저입니다.

정리하면 G열 날짜 변환, H열 정산월, I열 채널코드, J열 카테고리코드, M열 요율키, K열 요율, L열 건별 수수료 순서로 분리해 두면 오류를 훨씬 빨리 찾을 수 있습니다. 결과는 SUMIFS로 합산하되, COUNTIFS로 건수와 누락 요율을 같이 확인하면 월말 정산 대사 시간이 확 줄어듭니다.