엑셀 월별 순매출이 ERP와 안 맞을 때: 반품을 원주문월 기준으로 SUMIFS 대사하는 법

월별 순매출 보고서를 만들 때 은근히 자주 터지는 엑셀 오류가 있습니다. 판매는 6월에 잡혔는데 반품은 7월에 접수되어, 6월 순매출을 뽑으면 ERP와 금액이 안 맞는 상황입니다. 겉으로 보면 SUMIFS 합계가 틀린 것처럼 보이지만, 실제 원인은 반품을 ‘반품일 기준’으로 차감했는지, ‘원주문월 기준’으로 차감했는지 기준이 섞인 경우가 많습니다.

이번 예제는 쇼핑몰, 도매, 물류 정산에서 바로 만나는 구조로 잡아보겠습니다. 한 시트 안에 매출원장, 반품원장, 상품마스터, 월별 정산표가 함께 있고, 보고서 기준월은 AJ2, 거래처 조건은 AJ3에 입력하는 방식입니다. 함수는 SUMIFS, XLOOKUP, INDEX, MATCH, VLOOKUP, IF, IFERROR, DATE, TEXT, EOMONTH, ROUND 위주로 엮어서 해결하겠습니다.

월별 순매출이 안 맞는 실제 표 구조부터 확인하기

시트 이름은 정산작업이라고 가정하겠습니다. 매출원장은 A열부터 I열까지, 반품원장은 K열부터 V열까지 사용합니다. 데이터는 5행부터 시작하고, 4행은 제목 행입니다.

반품원장은 원주문번호를 가지고 들어온다고 보겠습니다. 여기서 핵심은 반품일 K열이 아니라, 원주문번호를 매출원장에서 찾아온 원주문일 S열과 원주문월 T열입니다.

예를 들어 6월 주문이 7월 3일에 반품되었다면, 7월 보고서에서 차감할지 6월 보고서에서 차감할지 회사 기준이 필요합니다. 이번 글에서는 실무 정산에서 자주 쓰는 방식인 원주문월 기준 차감으로 설명하겠습니다.

반품월 기준으로 SUMIFS를 걸면 왜 금액이 틀어질까

많이 하는 실수는 반품액을 구할 때 K열 반품일로 기준월을 거는 것입니다. 아래처럼 쓰면 2026년 6월에 판매된 상품이라도 반품 접수가 7월이면 6월 순매출에서 빠지지 않습니다.

=SUMIFS($Q$5:$Q$5000,$N$5:$N$5000,$AA7,$K$5:$K$5000,">="&$AJ$2,$K$5:$K$5000,"<="&EOMONTH($AJ$2,0))

이 수식 자체가 틀린 것은 아닙니다. ‘반품 접수월 기준 반품액’을 구할 때는 맞는 수식입니다. 하지만 월별 순매출 보고서에서 “6월 판매분 중 나중에 반품된 금액도 6월에서 차감”해야 한다면, 기준 열을 K열이 아니라 원주문월을 계산한 T열로 바꿔야 합니다.

따라서 먼저 반품원장에 원주문일과 원주문월을 붙여야 합니다. 이 작업을 하지 않고 SUMIFS만 계속 고치면, 조건은 맞는 것 같은데 결과는 계속 ERP와 다르게 나옵니다.

매출원장에 대사키와 주문월을 만들어 반품을 붙일 준비하기

주문번호만으로도 찾을 수 있으면 좋지만, 실무 파일에서는 한 주문번호 안에 여러 상품이 들어가는 경우가 많습니다. 그래서 주문번호 + 상품코드를 묶은 대사키를 만들겠습니다. 매출원장 H열 제목은 대사키, I열 제목은 주문월로 둡니다.

H5에는 아래 수식을 입력하고 H5000까지 복사합니다. 자기 파일에서 주문번호 열과 상품코드 열이 다르면 B5, D5 부분만 바꾸면 됩니다.

=B5&"|"&D5

I5에는 주문일을 월 첫날 날짜로 바꾸는 수식을 넣습니다. AJ2에 2026-06-01처럼 기준월 첫날을 넣을 예정이므로, 주문월도 같은 형식으로 맞춰두면 SUMIFS가 훨씬 안정적입니다.

=DATE(LEFT(TEXT(A5,"yyyymmdd"),4),MID(TEXT(A5,"yyyymmdd"),5,2),1)

A열 주문일이 진짜 날짜라면 위 수식이 잘 작동합니다. 만약 주문일이 20260612처럼 숫자 8자리로 들어온 파일이라도 TEXT와 LEFT, MID 조합으로 월을 잡을 수 있습니다. 날짜가 텍스트인지 날짜값인지 애매할 때는 셀 표시 형식만 믿지 말고, 월별 SUMIFS 결과가 0으로 나오는지 함께 확인하는 것이 좋습니다.

매출액 G5도 수식으로 관리하는 편이 안전합니다. 단가에 소수점이 있거나 공급가 계산 과정에서 1원 차이가 생기는 파일이라면 ROUND를 같이 써주세요.

=ROUND(E5*F5,0)

반품원장에는 원주문일, 원거래처, 원주문월을 붙여야 한다

반품원장 R열에는 매출원장과 같은 방식으로 대사키를 만듭니다. R5에 아래 수식을 입력하고 아래로 복사합니다.

=M5&"|"&N5

S열에는 원주문일을 가져옵니다. Microsoft 365 또는 Excel 2021 이상에서 XLOOKUP을 사용할 수 있다면 아래 수식이 가장 읽기 쉽습니다. 아래 수식은 Microsoft 365 최신 버전을 기준으로 설명합니다. Excel 2021/2024 등 영구 버전에서는 사용 중인 버전에서 함수 지원 여부를 먼저 확인해 주세요.

=IFERROR(XLOOKUP(R5,$H$5:$H$5000,$A$5:$A$5000,"원주문없음"),"원주문없음")

XLOOKUP이 없는 버전이라면 INDEX와 MATCH 조합으로 같은 결과를 만들 수 있습니다. 실무에서는 이 방식도 여전히 많이 씁니다.

=IFERROR(INDEX($A$5:$A$5000,MATCH(R5,$H$5:$H$5000,0)),"원주문없음")

V열에는 원거래처를 가져오겠습니다. 반품원장에 거래처가 따로 있더라도, 정산 기준은 매출원장의 거래처와 맞추는 편이 안전합니다. 거래처명이 중간에 바뀌었거나 반품 파일의 거래처명이 축약되어 있으면 SUMIFS에서 누락될 수 있기 때문입니다.

=IFERROR(XLOOKUP(R5,$H$5:$H$5000,$C$5:$C$5000,"거래처확인"),"거래처확인")

T열에는 원주문월을 계산합니다. S열이 원주문없음이면 빈칸으로 두고, 정상 날짜일 때만 월 첫날로 바꿉니다.

=IF(S5="원주문없음","",DATE(LEFT(TEXT(S5,"yyyymmdd"),4),MID(TEXT(S5,"yyyymmdd"),5,2),1))

Q열 반품액도 ROUND로 한 번 맞춰주세요. 반품액을 양수로 관리하고, 보고서에서 차감하는 방식으로 설명하겠습니다.

=ROUND(O5*P5,0)

U열에는 검증 메시지를 넣어두면 나중에 보고서가 틀어졌을 때 원인을 빨리 찾을 수 있습니다. U5에 아래 수식을 넣습니다.

=IF(S5="원주문없음","원주문 확인",IF(Q5<>ROUND(O5*P5,0),"반품액 확인","정상"))

정산표에서 판매액, 원주문월 기준 반품액, 순매출을 나눠 계산하기

이제 오른쪽에 월별 정산표를 만듭니다. AJ2에는 기준월을 날짜로 입력합니다. 예를 들어 2026년 6월 보고서라면 2026-06-01을 입력합니다. AJ3에는 거래처명, 예를 들어 한빛유통을 입력합니다.

상품마스터는 X열과 Y열에 둔다고 가정하겠습니다. X열은 상품코드, Y열은 상품명이며 데이터는 X5:Y200 범위입니다. AB7에는 VLOOKUP으로 상품명을 가져옵니다.

=IFERROR(VLOOKUP(AA7,$X$5:$Y$200,2,0),"상품명 확인")

XLOOKUP을 쓰는 파일이라면 아래처럼 작성해도 됩니다.

=IFERROR(XLOOKUP(AA7,$X$5:$X$200,$Y$5:$Y$200),"상품명 확인")

AC7에는 기준월 판매액을 구합니다. 주문일 A열이 AJ2 이상이고, 기준월 말일 이하인 데이터만 합산합니다.

=SUMIFS($G$5:$G$5000,$D$5:$D$5000,$AA7,$C$5:$C$5000,$AJ$3,$A$5:$A$5000,">="&$AJ$2,$A$5:$A$5000,"<="&EOMONTH($AJ$2,0))

AD7에는 원주문월 기준 반품액을 구합니다. 여기서 가장 중요한 조건은 T열입니다. 반품일 K열이 아니라, 원주문일을 기준으로 만든 원주문월 T열을 사용합니다.

=SUMIFS($Q$5:$Q$5000,$N$5:$N$5000,$AA7,$V$5:$V$5000,$AJ$3,$T$5:$T$5000,$AJ$2)

AE7에는 순매출을 계산합니다. 반품액 Q열을 양수로 관리했으므로 판매액에서 빼면 됩니다.

=AC7-AD7

AF7에는 참고용으로 반품일 기준 반품액을 따로 구해두면 좋습니다. 이 열은 보고서 제출용이라기보다, 왜 월별 순매출이 ERP와 달라 보였는지 설명할 때 유용합니다.

=SUMIFS($Q$5:$Q$5000,$N$5:$N$5000,$AA7,$V$5:$V$5000,$AJ$3,$K$5:$K$5000,">="&$AJ$2,$K$5:$K$5000,"<="&EOMONTH($AJ$2,0))

AG7에는 차이 확인 문구를 넣습니다. 원주문월 기준 반품액과 반품일 기준 반품액이 다르면, 대부분 월 넘김 반품이 있다는 뜻입니다.

=IF(AD7=AF7,"같음","반품월 차이 확인")

SUMIFS가 0으로 나오거나 반품이 누락될 때 보는 검증 순서

첫 번째로 볼 것은 원주문을 못 찾은 반품 건수입니다. AJ5 같은 빈 셀에 아래 수식을 넣어보세요. 결과가 0보다 크면 반품원장 M열 원주문번호 또는 N열 상품코드가 매출원장과 맞지 않는 것입니다.

=COUNTIFS($S$5:$S$5000,"원주문없음")

두 번째는 거래처 조건입니다. 반품원장의 V열 원거래처가 AJ3과 정확히 같은지 확인합니다. ‘한빛유통’과 ‘한빛 유통’처럼 중간 공백이 다르면 SUMIFS는 다른 값으로 봅니다. 이럴 때는 반품원장의 거래처명을 믿기보다 매출원장에서 XLOOKUP으로 가져온 V열을 조건으로 쓰는 편이 안전합니다.

세 번째는 날짜 조건입니다. AJ2가 2026-06처럼 보이지만 실제로는 텍스트일 수 있습니다. 기준월 셀 AJ2는 반드시 날짜값으로 입력하고, 표시 형식만 yyyy-mm으로 바꾸는 것을 추천합니다. 수식 입력줄에서 2026-06-01처럼 보이면 가장 안정적입니다.

네 번째는 금액 반올림입니다. 단가에 소수점이 있거나 공급가를 역산한 파일에서는 행별 ROUND를 하지 않으면 합계에서 1원, 2원 차이가 생깁니다. G열 매출액과 Q열 반품액을 모두 ROUND로 계산한 뒤 SUMIFS를 걸면 대사표가 훨씬 깔끔해집니다.

복사해서 쓰기 좋은 전체 결과 열 배치

아래처럼 열을 고정해두면 다음 달에도 조건 셀 AJ2만 바꿔서 바로 재계산할 수 있습니다. 월마감 때마다 수식을 새로 만들지 않아도 되고, 반품월 기준 금액과 원주문월 기준 금액을 함께 보여줄 수 있어 설명하기도 쉽습니다.

보고서 제출 전에는 AC열 합계, AD열 합계, AE열 합계를 따로 확인하세요. 전체 순매출은 보통 아래처럼 계산합니다.

=SUM(AC7:AC50)-SUM(AD7:AD50)

이 값이 ERP의 원주문월 기준 순매출과 맞아야 합니다. 만약 ERP가 반품접수월 기준으로 보는 화면이라면 AD열이 아니라 AF열을 차감해야 합니다. 같은 반품 데이터라도 기준월 정의가 다르면 보고서 숫자가 달라지는 것이 정상입니다.

행이 많을 때 대사키와 검증 수식을 한 번에 채우는 VBA

매월 원장이 1만 행 이상이면 H열, I열, R열, S열 같은 보조열 수식을 손으로 내리는 것도 꽤 번거롭습니다. 아래 코드는 정산작업 시트에서 매출원장과 반품원장의 마지막 행을 찾아 필요한 수식을 자동으로 채워줍니다.

실행 전에는 반드시 파일 복사본에서 먼저 테스트하세요. 매크로로 입력된 수식은 일반적인 Ctrl+Z로 되돌리기 어려울 수 있으니, 원본 파일을 저장해둔 뒤 실행하는 것이 안전합니다. 되돌리고 싶을 때는 저장하지 않고 닫거나, 실행 전 복사해둔 파일로 돌아가면 됩니다.

Sub FillSalesReturnCheckFormulas()
    Dim ws As Worksheet
    Dim lastSales As Long
    Dim lastReturn As Long

    Set ws = Worksheets("정산작업")

    lastSales = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    lastReturn = ws.Cells(ws.Rows.Count, "M").End(xlUp).Row

    If lastSales < 5 And lastReturn < 5 Then
        MsgBox "5행 이후 데이터가 없습니다."
        Exit Sub
    End If

    Application.ScreenUpdating = False

    If lastSales >= 5 Then
        ws.Range("G5:G" & lastSales).Formula = "=ROUND(E5*F5,0)"
        ws.Range("H5:H" & lastSales).Formula = "=B5&""|""&D5"
        ws.Range("I5:I" & lastSales).Formula = "=DATE(LEFT(TEXT(A5,""yyyymmdd""),4),MID(TEXT(A5,""yyyymmdd""),5,2),1)"
    End If

    If lastReturn >= 5 Then
        ws.Range("Q5:Q" & lastReturn).Formula = "=ROUND(O5*P5,0)"
        ws.Range("R5:R" & lastReturn).Formula = "=M5&""|""&N5"
        ws.Range("S5:S" & lastReturn).Formula = "=IFERROR(XLOOKUP(R5,$H$5:$H$5000,$A$5:$A$5000,""원주문없음""),""원주문없음"")"
        ws.Range("T5:T" & lastReturn).Formula = "=IF(S5=""원주문없음"","""",DATE(LEFT(TEXT(S5,""yyyymmdd""),4),MID(TEXT(S5,""yyyymmdd""),5,2),1))"
        ws.Range("U5:U" & lastReturn).Formula = "=IF(S5=""원주문없음"",""원주문 확인"",IF(Q5<>ROUND(O5*P5,0),""반품액 확인"",""정상""))"
        ws.Range("V5:V" & lastReturn).Formula = "=IFERROR(XLOOKUP(R5,$H$5:$H$5000,$C$5:$C$5000,""거래처확인""),""거래처확인"")"
    End If

    Application.ScreenUpdating = True
    MsgBox "대사키, 원주문월, 검증 수식 입력이 완료되었습니다."
End Sub

적용 범위는 정산작업 시트의 매출원장 B열 마지막 행, 반품원장 M열 마지막 행 기준입니다. 만약 데이터가 5000행을 넘는다면 XLOOKUP 범위의 $H$5:$H$5000, $A$5:$A$5000, $C$5:$C$5000 부분을 실제 최대 행에 맞게 늘려주세요.

월마감 보고서에서 숫자를 설명할 수 있어야 진짜 정산표다

순매출 보고서가 틀리는 이유는 함수 하나가 부족해서라기보다, 판매월과 반품월의 기준을 섞어서 보는 경우가 많습니다. 그래서 판매액, 원주문월 기준 반품액, 반품일 기준 반품액을 따로 계산해두면 숫자가 왜 달라졌는지 바로 설명할 수 있습니다.

다음 달 파일에 적용할 때는 AJ2 기준월, AJ3 거래처명, AA열 상품코드 목록만 바꾸고 AC:AG 수식을 아래로 복사하면 됩니다. 반품이 다음 달에 들어와도 원주문번호와 상품코드만 정확하면 원래 판매월로 따라붙기 때문에, 월별 순매출 대사 시간이 훨씬 줄어듭니다.