엑셀 쿠폰 정산액이 ERP와 안 맞을 때: 쿠폰코드 행사월·요율표 XLOOKUP·SUMIFS 0 나옴 잡는 법
엑셀 쿠폰 정산액이 ERP와 안 맞을 때는 대부분 금액 공식보다 쿠폰코드에서 뽑은 행사월, 채널코드, 상품군이 틀어진 경우가 많습니다. 특히 요약표에서 SUMIFS 결과가 0으로 나오거나, VLOOKUP 또는 XLOOKUP으로 요율을 가져왔는데 일부 행만 비어 있다면 원본 코드 구조부터 확인해야 합니다.
이번 예제는 온라인몰, 오프라인 매장, 제휴 쿠폰 정산에서 자주 만나는 형태로 잡아보겠습니다. 쿠폰코드 안에 채널과 행사월이 들어 있고, 상품코드는 별도 마스터에서 상품군을 찾아온 뒤, 요율표와 연결해 정산액을 계산하는 흐름입니다.
문제 상황: 쿠폰 정산표는 있는데 월별 합계가 ERP보다 작게 나오는 경우
시트 이름은 매출내역이라고 하겠습니다. A열부터 H열까지는 ERP에서 내려받은 원본이고, I열부터 Q열까지는 우리가 계산용으로 추가할 열입니다.
| 열 | 열 이름 | 예시 값 | 설명 |
|---|---|---|---|
| A | 주문일 | 2024-07-03 | 실제 결제일 |
| B | 매장코드 | S001 | 매장 또는 판매처 코드 |
| C | 영수증번호 | R240703-001 | 중복 확인 기준 |
| D | 쿠폰코드 | CPON2407A100 | 채널, 행사월, 행사품번 포함 |
| E | 상품코드 | P-1001 | 상품마스터와 연결 |
| F | 판매금액 | 58000 | 정산율을 곱할 금액 |
| G | 수량 | 1 | 판매 수량 |
| H | 승인상태 | 승인 | 승인, 취소 구분 |
쿠폰코드는 CPON2407A100처럼 12자리라고 가정합니다. 앞의 CP는 고정값, 3~4번째 글자인 ON은 채널코드, 5~8번째 글자인 2407은 행사월, 마지막 4자리 A100은 행사품번입니다.
요약표는 같은 시트 오른쪽에 둔다고 가정하겠습니다. S2에는 기준월, T2에는 매장코드, U2에는 채널코드를 입력하고, V2에는 최종 정산액 합계를 표시합니다.
| 셀 | 입력값 | 의미 |
|---|---|---|
| S2 | 2024-07-01 | 조회할 기준월 |
| T2 | S001 | 조회할 매장코드 |
| U2 | ON | 조회할 채널코드 |
| V2 | 결과 수식 | 정산액 합계 |
원인: SUMIFS 문제가 아니라 기준값이 서로 다른 모양인 경우가 많습니다
이 상황에서 바로 SUMIFS만 고치려고 하면 오래 헤맬 수 있습니다. 조건별 합계가 0으로 나오는 이유는 합계 범위가 잘못된 것이 아니라, 조건 범위의 값이 우리가 생각한 값과 다를 때가 많습니다.
예를 들어 주문일은 2024년 7월인데 쿠폰코드 안의 행사월은 2406일 수 있습니다. 정산 기준이 주문월인지 행사월인지 정하지 않으면 ERP와 엑셀 집계가 계속 어긋납니다.
또 하나는 요율표 연결 문제입니다. 상품코드 P-1001을 그대로 요율표에서 찾는 줄 알았는데, 실제 요율표는 상품코드가 아니라 상품군 기준으로 되어 있는 경우가 많습니다. 이럴 때 VLOOKUP 안됨, XLOOKUP 빈칸, #N/A 오류가 한꺼번에 생깁니다.
쿠폰코드에서 행사월, 채널코드, 행사품번을 먼저 분리합니다
먼저 I열부터 K열까지 쿠폰코드 분석용 열을 만듭니다. 매출내역의 데이터는 2행부터 5000행까지 있다고 가정하겠습니다.
I2에는 쿠폰코드의 5~8번째 글자 2407을 날짜로 바꿉니다. 월별 SUMIFS를 안정적으로 하려면 텍스트 2407보다 실제 날짜인 2024-07-01 형태가 좋습니다.
=IFERROR(DATE(2000+LEFT(MID(D2,5,4),2),RIGHT(MID(D2,5,4),2),1),"")이 수식에서 D2는 쿠폰코드 셀입니다. 본인 파일에서 쿠폰코드가 D열이 아니라면 D2만 해당 위치로 바꾸면 됩니다.
J2에는 채널코드를 뽑습니다. 쿠폰코드 CPON2407A100에서 ON만 가져오는 수식입니다.
=IFERROR(MID(D2,3,2),"")K2에는 행사품번 마지막 4자리를 가져옵니다.
=IFERROR(RIGHT(D2,4),"")여기서 흔한 실수가 하나 있습니다. ERP에서 내려받은 쿠폰코드가 CP-ON-2407-A100처럼 하이픈이 들어간 형태라면 위 위치 기준이 전부 밀립니다. 이 경우에는 원본을 바로 손대기보다 D열을 복사해 별도 열에 붙여 넣고, Ctrl+H로 하이픈을 빈칸으로 바꾼 뒤 그 열을 기준으로 수식을 걸어보는 편이 안전합니다.
상품마스터에서 상품군을 가져와 요율표와 연결합니다
이번에는 별도 시트 상품마스터를 사용합니다. 상품마스터 시트의 A열은 상품코드, B열은 상품명, C열은 상품군입니다. 범위는 A2:C200이라고 하겠습니다.
| A 상품코드 | B 상품명 | C 상품군 |
|---|---|---|
| P-1001 | 기본 티셔츠 | 의류 |
| P-1002 | 데님 팬츠 | 의류 |
| P-2001 | 텀블러 | 생활 |
| P-3001 | 스킨케어 세트 | 뷰티 |
L2에는 상품코드를 기준으로 상품군을 가져옵니다. Microsoft 365 또는 Excel 2021 이상에서 XLOOKUP을 쓸 수 있다면 아래 수식이 보기 쉽습니다.
=IFERROR(XLOOKUP(E2,상품마스터!$A$2:$A$200,상품마스터!$C$2:$C$200),"상품확인")XLOOKUP을 사용할 수 없는 파일이라면 VLOOKUP으로도 충분히 해결됩니다.
=IFERROR(VLOOKUP(E2,상품마스터!$A$2:$C$200,3,FALSE),"상품확인")VLOOKUP에서 FALSE를 빼면 비슷한 값을 찾아오는 문제가 생길 수 있습니다. 정산 파일에서는 상품코드 하나만 틀려도 금액이 바로 달라지므로 마지막 인수는 꼭 FALSE로 둡니다.
요율표는 복합키를 만들어 INDEX MATCH 또는 XLOOKUP으로 찾습니다
요율표 시트는 A열 채널코드, B열 상품군, C열 기준월, D열 정산율, E열 정산키로 구성하겠습니다. 데이터 범위는 A2:E100입니다.
| A 채널코드 | B 상품군 | C 기준월 | D 정산율 | E 정산키 |
|---|---|---|---|---|
| ON | 의류 | 2024-07-01 | 0.08 | ON의류202407 |
| ON | 생활 | 2024-07-01 | 0.06 | ON생활202407 |
| OF | 의류 | 2024-07-01 | 0.05 | OF의류202407 |
| ON | 뷰티 | 2024-08-01 | 0.09 | ON뷰티202408 |
요율표 E2에는 채널, 상품군, 기준월을 붙인 정산키를 만듭니다. 아래 수식을 E2에 넣고 E100까지 복사합니다.
=A2&B2&TEXT(C2,"yyyymm")이제 매출내역 M2에도 같은 방식으로 정산키를 만듭니다.
=J2&L2&TEXT(I2,"yyyymm")N2에는 정산율을 가져옵니다. XLOOKUP을 쓰는 경우입니다.
=IFERROR(XLOOKUP(M2,요율표!$E$2:$E$100,요율표!$D$2:$D$100),"요율확인")INDEX와 MATCH 조합이 더 익숙하다면 아래 수식을 사용해도 됩니다. 실무에서는 이 방식도 여전히 많이 씁니다.
=IFERROR(INDEX(요율표!$D$2:$D$100,MATCH(M2,요율표!$E$2:$E$100,0)),"요율확인")이 구조의 장점은 조건이 세 개여도 복잡한 배열 수식처럼 보이지 않는다는 점입니다. 채널코드, 상품군, 기준월을 하나의 정산키로 맞춰놓으면 어느 조건에서 틀어졌는지 눈으로 추적하기 쉽습니다.
정산액은 취소건을 제외하고 ROUND로 원 단위 차이를 줄입니다
O2에는 최종 정산액을 계산합니다. 승인상태가 취소이면 0으로 처리하고, 승인 건만 판매금액에 정산율을 곱한 뒤 ROUND로 반올림합니다.
=IF(H2="취소",0,IFERROR(ROUND(F2*N2,0),0))정산율이 없는 행을 무조건 0으로 처리하면 합계는 맞는 것처럼 보이지만 실제 누락을 놓칠 수 있습니다. 그래서 P열에는 점검메모를 따로 만드는 것이 좋습니다.
=IF(L2="상품확인","상품마스터 누락",IF(N2="요율확인","요율표 누락",""))Q2에는 영수증번호와 쿠폰코드가 중복된 행을 표시합니다. 같은 쿠폰이 같은 영수증에 두 번 들어간 경우 정산액이 두 배로 잡힐 수 있습니다.
=IF(COUNTIFS($C$2:$C$5000,C2,$D$2:$D$5000,D2)>1,"중복확인","")요약표 SUMIFS는 주문일이 아니라 쿠폰월 기준으로 잡습니다
이제 S2 기준월, T2 매장코드, U2 채널코드를 조건으로 V2에 정산액 합계를 구합니다. 이때 핵심은 I열 쿠폰월을 기준으로 월초부터 월말까지 조건을 거는 것입니다.
=SUMIFS($O$2:$O$5000,$B$2:$B$5000,$T$2,$I$2:$I$5000,">="&$S$2,$I$2:$I$5000,"<="&EOMONTH($S$2,0),$J$2:$J$5000,$U$2,$H$2:$H$5000,"승인")W2에는 같은 조건으로 승인건수를 세어봅니다. 금액 검산 전에 건수부터 맞춰보면 원인 찾는 시간이 훨씬 줄어듭니다.
=COUNTIFS($B$2:$B$5000,$T$2,$I$2:$I$5000,">="&$S$2,$I$2:$I$5000,"<="&EOMONTH($S$2,0),$J$2:$J$5000,$U$2,$H$2:$H$5000,"승인")X2에는 점검이 필요한 행 수를 세어 둡니다. 이 값이 0이 아닌데 정산액을 확정하면 거의 반드시 차이가 납니다.
=COUNTIFS($P$2:$P$5000,"<>")검증할 때는 합계보다 건수, 빈칸, 중복을 먼저 봅니다
정산액이 ERP와 다르면 바로 요율을 의심하기 쉽지만, 실제로는 기준 행 수가 다른 경우가 더 많습니다. 필터를 켜고 P열 점검메모에서 상품마스터 누락, 요율표 누락이 있는지 먼저 확인합니다.
그다음 Q열 중복확인을 봅니다. 같은 영수증번호와 쿠폰코드가 두 번 이상 있으면 승인취소가 같이 들어왔는지, 원본에서 중복으로 내려온 것인지 확인해야 합니다.
마지막으로 I열 쿠폰월과 A열 주문일이 다른 행을 따로 봅니다. 프로모션 정산은 주문월 기준이 아니라 쿠폰 행사월 기준으로 하는 회사가 많기 때문에, 여기서 ERP와의 차이가 크게 벌어질 수 있습니다.
실무 응용: 매장별, 채널별 정산표로 확장하기
매장별 정산표를 만들고 싶다면 T열 아래로 매장코드를 S001, S002, S003처럼 나열하고, U열에는 ON 또는 OF를 넣은 뒤 V2 수식을 아래로 복사하면 됩니다. 기준월 S2만 고정하고 싶다면 수식에서 $S$2처럼 절대참조를 유지하세요.
채널별로 열을 나누고 싶다면 U2에는 ON, U3에는 OF처럼 세로로 두거나, 표 상단에 채널코드를 가로로 배치해도 됩니다. 중요한 것은 SUMIFS 조건 범위와 조건 셀의 위치를 헷갈리지 않는 것입니다.
정산 파일은 한 번 맞춰두면 다음 달에도 거의 같은 구조로 반복됩니다. 원본 붙여넣기 후 I열부터 Q열 수식을 아래로 복사하고, 요율표 기준월만 추가한 뒤, 요약표의 S2 기준월을 바꿔 검산하는 흐름으로 쓰면 안정적입니다.
실무에서 적용할 때 볼 것
쿠폰 정산액이 맞지 않을 때는 SUMIFS 수식 하나만 보는 것보다 쿠폰코드 분해, 상품군 연결, 요율표 키, 승인상태, 중복 여부를 순서대로 확인하는 편이 빠릅니다. 특히 날짜는 텍스트 2407이 아니라 DATE와 EOMONTH로 월 범위를 잡아야 조건별 합계가 흔들리지 않습니다.
이번 구조는 복잡해 보이지만 사용하는 함수는 SUMIFS, COUNTIFS, XLOOKUP, VLOOKUP, INDEX, MATCH, IF, IFERROR, MID, LEFT, RIGHT, TEXT, DATE, EOMONTH, ROUND처럼 실무에서 자주 쓰는 함수들입니다. 낯선 기능으로 한 번에 끝내기보다, 중간 열을 만들어 눈으로 검증할 수 있게 두는 것이 정산 업무에서는 훨씬 안전합니다.