엑셀 재구매 고객 수가 안 맞을 때: 고객번호 앞자리 0·주문일 텍스트·중복 주문까지 검산하기

월별 매출 보고서에서 순매출은 맞는 것 같은데 신규 고객 수와 재구매 고객 수가 계속 안 맞는 경우가 있습니다. 특히 쇼핑몰, 자사몰, 오픈마켓 주문 데이터를 한 파일로 합칠 때 고객번호 앞자리 0이 사라짐, 주문일이 20260703 같은 숫자 텍스트로 들어옴, 한 고객이 같은 달에 여러 번 주문함 때문에 COUNTIFS 결과가 엉키는 일이 많습니다.

이번 예시는 주문 원장에서 월별·채널별 순매출, 결제건수, 신규 고객 수, 재구매 고객 수를 검산하는 상황입니다. 낯선 기능으로 한 번에 끝내기보다, 실무에서 많이 쓰는 LEFT, TEXT, DATE, ROUND, IF, IFERROR, VLOOKUP, SUMIFS, COUNTIFS 조합으로 차근차근 잡아보겠습니다.

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

아래처럼 A1:G5000 범위에 주문 원장이 있다고 가정하겠습니다. 1행은 제목이고, 실제 데이터는 2행부터 시작합니다.

예시 데이터는 이런 모습입니다.

먼저 채널코드와 고객번호부터 표준화했는가?

집계 수식이 틀리는 가장 흔한 원인은 SUMIFS나 COUNTIFS가 아니라, 조건값이 서로 다른 모양으로 들어가 있는 것입니다. 예를 들어 고객번호 001245와 1245는 사람 눈에는 같은 고객처럼 보여도 엑셀에서는 다른 값으로 볼 수 있습니다.

먼저 X1:Y20에 채널 매핑표를 만들어 둡니다. X열에는 원본채널 앞 2자리, Y열에는 보고서에 쓸 표준채널명을 넣습니다.

H열에는 표준채널을 만들겠습니다. H1에는 표준채널이라고 입력하고, H2에 아래 수식을 넣은 뒤 H5000까지 복사합니다.

=IFERROR(VLOOKUP(LEFT(B2,2),$X$2:$Y$20,2,FALSE),"채널확인")

원본채널 B2가 NV-STORE라면 LEFT(B2,2)가 NV를 뽑고, X:Y 매핑표에서 네이버를 찾아옵니다. 만약 새 채널이 들어왔는데 매핑표에 없으면 H열에 채널확인이라고 표시되므로 보고서 집계 전에 누락을 잡을 수 있습니다.

XLOOKUP을 쓰는 파일이라면 아래처럼 바꿔도 됩니다. 다만 여러 사람이 함께 쓰는 파일이라면 VLOOKUP이 더 익숙한 경우도 많으니, 팀 환경에 맞춰 선택하면 됩니다.

=IFERROR(XLOOKUP(LEFT(B2,2),$X$2:$X$20,$Y$2:$Y$20),"채널확인")

이제 I열에는 정리된 고객번호를 만듭니다. I1에는 정리고객번호라고 입력하고, I2에 아래 수식을 넣습니다.

=RIGHT("000000"&RIGHT(C2,6),6)

이 수식은 C2가 1245여도 001245로 맞춰주고, 이미 001245여도 그대로 유지합니다. 고객번호 자릿수가 회사마다 다르다면 "000000"과 마지막 숫자 6을 실제 고객번호 자리수에 맞게 바꾸면 됩니다.

주문일자가 날짜가 아니라서 월 집계가 0으로 나오지는 않는가?

D열 주문일자가 20260703처럼 들어오면 겉보기에는 날짜 같지만, 실제로는 숫자 또는 텍스트일 수 있습니다. 이 상태에서 월별 SUMIFS를 걸면 기준월과 맞지 않아 결과가 0으로 나오는 일이 생깁니다.

J열에는 엑셀이 인식하는 날짜를 만들겠습니다. J1에는 정리주문일이라고 입력하고, J2에 아래 수식을 넣습니다.

=DATE(LEFT(TEXT(D2,"00000000"),4),MID(TEXT(D2,"00000000"),5,2),RIGHT(TEXT(D2,"00000000"),2))

TEXT(D2,"00000000")는 주문일자를 8자리로 맞춘 뒤, LEFT로 연도 4자리, MID로 월 2자리, RIGHT로 일 2자리를 분리합니다. 그리고 DATE 함수로 진짜 날짜를 만들어 줍니다.

K열에는 집계용 기준월을 만듭니다. K1에는 기준월이라고 입력하고, K2에 아래 수식을 넣습니다.

=TEXT(J2,"yyyymm")

여기서 중요한 점은 K열의 기준월이 텍스트 형태의 202607이라는 것입니다. 나중에 보고서 조건 셀도 같은 형태로 맞춰야 COUNTIFS와 SUMIFS가 안정적으로 계산됩니다.

반품과 취소는 매출에서 어떻게 처리할까?

순매출이 맞지 않는 파일을 보면 결제금액 G열을 그대로 SUMIFS로 더하는 경우가 많습니다. 하지만 E열 처리구분이 반품인 행은 매출에서 빼야 하고, 취소는 회사 기준에 따라 0으로 처리하는 경우가 많습니다.

L열에는 정산금액을 만듭니다. L1에는 정산금액이라고 입력하고, L2에 아래 수식을 넣습니다.

=ROUND(IF(E2="결제",G2,IF(E2="반품",-G2,0)),0)

결제는 양수, 반품은 음수, 그 외 취소는 0으로 잡았습니다. PG 수수료나 포인트 차감 때문에 소수점이 섞이는 파일이라면 ROUND로 원 단위 반올림을 걸어두면 1원 차이 검산에도 도움이 됩니다.

같은 고객이 같은 달에 2번 주문하면 신규 고객을 2명으로 세고 있지 않은가?

신규 고객 수와 재구매 고객 수가 틀어지는 핵심은 여기입니다. 주문건수는 한 고객이 여러 번 주문하면 여러 건으로 세는 것이 맞지만, 고객 수는 같은 달 안에서 고객당 1명만 세야 합니다.

M열에는 월 안에서 해당 고객을 한 번만 세기 위한 표시를 만들겠습니다. M1에는 월고객1건이라고 입력하고, M2에 아래 수식을 넣습니다.

=IF(E2<>"결제",0,IF(COUNTIFS($I$2:I2,I2,$K$2:K2,K2,$E$2:E2,"결제")=1,1,0))

이 수식은 현재 행까지 내려오면서 같은 정리고객번호, 같은 기준월, 처리구분 결제인 행이 처음 나오면 1을 표시합니다. 같은 고객이 같은 달에 두 번째 결제를 하면 0이 됩니다.

단, 이 방식은 고객 수를 세기 위한 실무용 표시입니다. 반드시 주문 원장이 위에서 아래로 누적되는 형태인지 확인하세요. 월별 파일을 합쳤다면 주문일 기준으로 정렬한 뒤 적용하는 것이 안전합니다.

신규와 재구매는 어느 기준일로 나눌까?

재구매 고객은 보통 해당 월 이전에 결제 이력이 있는 고객입니다. 예를 들어 2026년 7월 보고서라면, 2026년 7월 1일보다 이전에 결제한 적이 있는 고객을 재구매로 볼 수 있습니다.

N열에는 이전 구매 여부를 표시합니다. N1에는 이전구매라고 입력하고, N2에 아래 수식을 넣습니다.

=IF(M2=0,0,IF(COUNTIFS($I$2:$I$5000,I2,$J$2:$J$5000,"<"&DATE(LEFT(K2,4),RIGHT(K2,2),1),$E$2:$E$5000,"결제")>0,1,0))

여기서 DATE(LEFT(K2,4),RIGHT(K2,2),1)는 기준월의 1일을 만듭니다. K2가 202607이면 2026년 7월 1일이 되고, 그 날짜보다 이전 결제 이력이 있는지 COUNTIFS로 확인합니다.

O열에는 최종 고객구분을 만듭니다. O1에는 고객구분이라고 입력하고, O2에 아래 수식을 넣습니다.

=IF(M2=0,"",IF(N2=1,"재구매","신규"))

M열이 1인 행만 고객 수 집계 대상입니다. 그중 N열이 1이면 재구매, 아니면 신규로 구분합니다. 같은 달 두 번째 주문은 O열이 빈칸이므로 고객 수에 중복 반영되지 않습니다.

월별·채널별 보고서 수식은 어디에 넣을까?

이제 오른쪽에 보고서 영역을 만들겠습니다. Q1:W1에 아래 제목을 입력합니다.

Q2에는 202607, R2에는 네이버를 입력해 보겠습니다. 이때 Q2는 K열과 같은 텍스트 기준월이어야 합니다. 셀 서식을 텍스트로 바꾸고 입력하거나, 아래처럼 수식으로 만들어도 됩니다.

=TEXT(DATE(2026,7,1),"yyyymm")

S2에는 순매출을 계산합니다.

=SUMIFS($L$2:$L$5000,$K$2:$K$5000,$Q2,$H$2:$H$5000,$R2)

T2에는 결제건수를 계산합니다. 반품과 취소는 제외하고 결제 행만 셉니다.

=COUNTIFS($K$2:$K$5000,$Q2,$H$2:$H$5000,$R2,$E$2:$E$5000,"결제")

U2에는 신규 고객 수를 계산합니다.

=COUNTIFS($K$2:$K$5000,$Q2,$H$2:$H$5000,$R2,$O$2:$O$5000,"신규")

V2에는 재구매 고객 수를 계산합니다.

=COUNTIFS($K$2:$K$5000,$Q2,$H$2:$H$5000,$R2,$O$2:$O$5000,"재구매")

W2에는 반품액을 양수로 표시해 보겠습니다. L열에서는 반품을 음수로 만들었기 때문에 앞에 마이너스를 붙였습니다.

=-SUMIFS($L$2:$L$5000,$K$2:$K$5000,$Q2,$H$2:$H$5000,$R2,$E$2:$E$5000,"반품")

SUMIFS 0 나옴, COUNTIFS 고객 수 이상함: 확인표로 빠르게 점검하기

수식은 맞는데 결과가 이상할 때는 아래 순서대로 보면 대부분 원인이 나옵니다. 특히 월마감 파일은 여러 사람이 붙여넣은 데이터가 섞여 있어 값의 모양부터 확인해야 합니다.

행이 많을 때는 보조열 수식을 VBA로 한 번에 채우기

매일 주문 원장을 내려받아 H:O 보조열을 반복해서 채운다면 VBA로 자동화해도 좋습니다. 아래 코드는 현재 활성 시트에서 A열 주문번호의 마지막 행을 기준으로 H:O열 수식을 한 번에 입력합니다.

실행 전에는 파일을 복사해 테스트하세요. 매크로 실행 작업은 Ctrl+Z로 되돌리기 어렵습니다. 되돌리고 싶다면 저장하지 않고 닫거나, H:O열 보조열을 삭제한 뒤 백업 파일에서 다시 시작하는 방식이 안전합니다.

Sub FillCustomerReportHelpers()
    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:O1").Value = Array("표준채널", "정리고객번호", "정리주문일", "기준월", "정산금액", "월고객1건", "이전구매", "고객구분")

    ws.Range("H2:H" & lastRow).Formula = "=IFERROR(VLOOKUP(LEFT(B2,2),$X$2:$Y$20,2,FALSE),""채널확인"")"
    ws.Range("I2:I" & lastRow).Formula = "=RIGHT(""000000""&RIGHT(C2,6),6)"
    ws.Range("J2:J" & lastRow).Formula = "=DATE(LEFT(TEXT(D2,""00000000""),4),MID(TEXT(D2,""00000000""),5,2),RIGHT(TEXT(D2,""00000000""),2))"
    ws.Range("K2:K" & lastRow).Formula = "=TEXT(J2,""yyyymm"")"
    ws.Range("L2:L" & lastRow).Formula = "=ROUND(IF(E2=""결제"",G2,IF(E2=""반품"",-G2,0)),0)"
    ws.Range("M2:M" & lastRow).Formula = "=IF(E2<>""결제"",0,IF(COUNTIFS($I$2:I2,I2,$K$2:K2,K2,$E$2:E2,""결제"")=1,1,0))"
    ws.Range("N2:N" & lastRow).Formula = "=IF(M2=0,0,IF(COUNTIFS($I$2:$I$" & lastRow & ",I2,$J$2:$J$" & lastRow & ",""<""&DATE(LEFT(K2,4),RIGHT(K2,2),1),$E$2:$E$" & lastRow & ",""결제"")>0,1,0))"
    ws.Range("O2:O" & lastRow).Formula = "=IF(M2=0,"""",IF(N2=1,""재구매"",""신규""))"

    MsgBox "H:O 보조열 수식 입력 완료: 2행부터 " & lastRow & "행까지"
End Sub

이 코드는 원본 데이터 A:G열은 건드리지 않습니다. 다만 H:O열에 이미 다른 내용이 있다면 덮어쓰므로, 해당 열을 비워두고 실행해야 합니다. 채널 매핑표 X:Y도 먼저 만들어 두어야 H열의 VLOOKUP 결과가 제대로 나옵니다.

적용 전에 확인할 것

이 방식의 핵심은 원장을 바로 집계하지 않고, 집계 전에 표준채널, 정리고객번호, 정리주문일, 기준월, 정산금액, 고객구분을 보조열로 분리해 검산하는 것입니다. 보조열이 있으면 어느 단계에서 값이 틀어졌는지 바로 보이기 때문에 월마감 때 훨씬 덜 불안합니다.

마지막으로 보고서 값이 이상하면 S:W 결과 셀만 보지 말고 H:O 보조열을 필터로 확인해 보세요. H열에 채널확인이 있는지, J열 날짜가 정상인지, O열 신규·재구매가 예상대로 찍히는지만 확인해도 대부분의 재구매 고객 수 오류는 빠르게 잡을 수 있습니다.