쇼핑몰 정산 엑셀 검증: 주문·환불·수수료를 맞추는 예제

AI Editter

쇼핑몰 주문 합계와 통장 입금액을 바로 빼면 차이가 나는 것이 자연스럽습니다. 결제일과 정산일이 다르고, 부분환불·수수료·보류금·이월금이 서로 다른 시점에 반영될 수 있기 때문입니다. 문제는 차이가 있다는 사실보다 어떤 거래가 왜 차이를 만들었는지 설명할 수 없는 상태입니다.

여기서는 특정 플랫폼의 수수료율을 적용하지 않습니다. 각 판매처에서 내려받은 실제 정산 내역을 기준으로, 주문·환불·공제·입금 자료를 연결하는 엑셀 구조를 만듭니다. 아래 금액과 거래번호는 계산을 보여 주기 위한 가상 예제입니다.

주문번호 하나로 모든 자료를 합치면 안 되는 경우

한 주문에 결제가 두 번 시도되거나, 한 결제에서 부분환불이 여러 번 발생할 수 있습니다. 따라서 주문표는 주문번호, 결제표는 결제번호, 환불표는 환불번호를 각각 고유키로 보관합니다. 정산표에 결제번호가 제공된다면 이를 연결키로 사용합니다.

주문번호와 상품번호를 묶어야 하는 플랫폼도 있습니다. 자료마다 한 행이 주문 전체인지, 상품 한 줄인지, 정산 이벤트 한 건인지 확인합니다. 결제 금액을 상품 행마다 반복한 자료를 그대로 더하면 상품 수만큼 매출이 커집니다.

88,200원이 들어왔을 때 검산하는 순서

가상 결제 세 건의 승인액은 합계 150,000원입니다. 환불액은 승인액과 별도 표에서 양수로 기록합니다.

이 예제의 공제액은 가상 정산 명세에 적힌 값을 그대로 사용했습니다. 모든 플랫폼이 환불 후 잔액에 같은 비율로 수수료를 계산한다는 뜻은 아닙니다. 수수료 반환 여부, 부가 항목, 반올림 단위는 계약과 정산 명세를 확인해야 합니다.

예상 입금액을 88,200원으로 계산했더라도 실제 입금과 연결되는 정산 묶음이 같은지 확인합니다. 오늘 입금에 어제 확정된 여러 주문이 섞여 있다면 주문일로 걸러 낸 150,000원과는 비교 범위가 다릅니다.

환불은 금액보다 상태와 이벤트 번호를 먼저 검사

환불표 이름을 환불로 만들고 환불번호, 결제번호, 환불액, 상태를 둡니다. 환불번호 중복은 다음처럼 확인합니다.

=COUNTIF(환불[환불번호],[@환불번호])

같은 환불 이벤트가 두 번 내려받아졌다면 중복 반영을 막아야 합니다. 그러나 환불번호가 다르면 금액이 같아도 별개의 부분환불일 수 있습니다. 결제번호와 금액만 같다는 이유로 삭제하면 안 됩니다.

결제표에서 확정된 환불만 합산하는 예시는 다음과 같습니다. 실제 파일의 상태 명칭이 환불완료라면 조건도 그 값으로 바꿉니다.

=SUMIFS(환불[환불액],환불[결제번호],[@결제번호],환불[상태],"완료")

환불 요청·실패·완료를 섞어 더하지 않습니다. 환불표에서 결제번호가 원장에 없는 행도 별도로 추출해야 합니다. 조건별 합산 방식은 Microsoft SUMIFS 문서를 참고할 수 있습니다.

공제와 보류금을 한 열에 뭉치지 않기

정산 명세의 수수료, 배송 관련 조정, 광고비 공제, 보류금, 보류 해제액을 가능한 한 분리합니다. 예를 들어 보류금은 이번 입금을 줄이지만 나중에 다시 지급될 수 있습니다. 비용으로 확정된 공제와 같은 열에 넣으면 다음 달 지급을 중복 매출처럼 처리하기 쉽습니다.

월간 대사표에는 다음 연결식을 둡니다. 각 항목의 부호와 포함 범위는 해당 플랫폼 명세의 정의와 맞춰야 합니다.

기초 미정산 잔액
+ 이번 기간에 정산 대상으로 확정된 금액
- 이번 기간 공제·조정
- 이번 기간 실제 입금
= 기말 미정산 잔액

기말 잔액이 0이 아니라고 무조건 오류는 아닙니다. 지급 예정일이 남아 있는 금액과 지급 기한이 지났는데 입금되지 않은 금액을 나눕니다. 지급 예정일을 기준으로 경과일을 붙이면 확인 우선순위를 정하기 쉽습니다.

Power Query 병합 전후에 반드시 볼 숫자

결제별 환불을 먼저 집계한 뒤 결제표와 병합합니다. 환불 원본과 상품별 주문 원본을 바로 병합하면 한 결제의 여러 상품과 여러 환불이 서로 조합되어 금액이 부풀 수 있습니다.

정산표와 결제표를 대조할 때는 전체 외부 조인으로 양쪽에만 있는 거래를 남깁니다. Microsoft의 병합 종류 안내에서 각 조인이 남기는 행을 확인할 수 있습니다.

병합 전 결제 건수, 승인액 합계, 환불 합계를 기록하고 병합 후 값과 비교합니다. 결제 단위로 한 행이어야 하는 결과의 행 수가 늘었다면 중복키부터 확인합니다. 차액 열을 만들기 전에 병합 자체가 맞았는지 검증해야 합니다.

차액을 확인 요청으로 바꾸는 대사표

원본은 내려받은 상태로 보관하고 수정은 비교용 사본에서 합니다. 대사표의 차액을 손으로 0으로 만드는 대신, 근거가 확인된 조정 내역을 별도 행으로 남깁니다. 다음 달 같은 거래가 다시 나타났을 때 어떤 판단을 했는지 확인할 수 있어야 합니다.

월마감 때 확인할 것: 결제와 환불의 중복 이벤트가 없는지, 모든 입금이 정산 묶음에 연결됐는지, 미정산 잔액에 사유와 예정일이 있는지 점검합니다. 이 세 가지가 맞으면 총액 하나가 맞는 것보다 훨씬 설명 가능한 정산 자료가 됩니다.

관련 절차: SQL JOIN 중복으로 SUM 합계가 늘어날 때: 재현과 해결