정산표 금액이 자꾸 틀릴 때: 매출·취소·반품을 ‘부호표’로 잡는 실무 계산법

월말 정산을 하다 보면 가장 찝찝한 순간이 있습니다. 쇼핑몰 관리자 화면의 매출 합계와 엑셀 보고서의 순매출이 몇 만 원, 많게는 몇 백만 원씩 어긋나는 경우입니다. 원인을 따라가 보면 대개 함수가 어려워서가 아니라 매출, 취소, 반품, 부분취소를 같은 방식으로 더해버린 것이 문제일 때가 많습니다.

특히 정산 원장을 여러 채널에서 내려받는 업무라면 더 헷갈립니다. 어떤 파일은 취소 금액이 음수로 들어오고, 어떤 파일은 취소도 양수로 들어옵니다. 반품 배송비는 별도 열에 있고, 쿠폰 할인은 매출에서 이미 빠져 있기도 합니다. 이 상태에서 바로 SUMIFS를 걸면 숫자는 그럴듯하게 나오지만, 보고서 검증 단계에서 꼭 한 번씩 발목을 잡습니다.

이번 글에서는 매출 정산표를 기준으로 거래유형별 부호표를 만들어 순매출을 안정적으로 계산하는 방식을 정리해 보겠습니다. 단순히 수식을 하나 외우는 방식이 아니라, 다음 달 원장이 바뀌어도 확인하기 쉬운 구조로 만드는 쪽에 초점을 두겠습니다.

정산이 틀어지는 지점은 금액보다 ‘거래유형’에 숨어 있습니다

아래처럼 매출 원장이 있다고 가정해 보겠습니다. 실무에서는 열이 훨씬 많겠지만, 정산에 꼭 필요한 열만 추리면 대략 이런 형태입니다.

여기서 흔한 실수는 결제금액 - 수수료 - 배송비처럼 바로 순정산 금액을 계산하는 것입니다. 매출 행에서는 맞아 보이지만, 취소와 반품 행에서는 부호가 섞이면서 결과가 달라집니다. 특히 반품 금액이 이미 음수로 들어온 원장과 양수로 들어온 원장을 섞으면 같은 거래유형인데도 방향이 달라집니다.

그래서 저는 정산표를 만들 때 원장 금액의 부호를 그대로 믿기보다, 거래유형을 기준으로 방향을 정하는 보조표를 먼저 둡니다.

거래유형 부호표를 따로 두면 다음 달 검증이 쉬워집니다

원장 옆 어딘가에 직접 IF문을 길게 쓰는 방식도 가능하지만, 거래유형이 늘어나는 업무에서는 관리가 불편합니다. 예를 들어 ‘교환출고’, ‘부분취소’, ‘재결제’, ‘배송비환불’ 같은 유형이 추가될 때마다 수식을 열어 수정해야 합니다.

대신 별도의 표를 하나 만들고 이름을 tbl부호로 지정해 두면 훨씬 안정적입니다.

이 표의 장점은 단순합니다. 계산 로직을 수식 안에 숨기지 않고, 눈에 보이는 표로 꺼내는 것입니다. 나중에 팀원이 파일을 열어도 “왜 반품이 빠졌지?”를 수식에서 찾는 대신 부호표에서 확인할 수 있습니다.

원장은 표로 바꾸고, 계산 열은 최대한 짧게 둡니다

원장 범위를 선택한 뒤 Ctrl + T로 엑셀 표를 만들어 두세요. 표 이름은 예시로 tbl원장이라고 하겠습니다. 이렇게 해두면 다음 달 행이 늘어나도 수식 범위를 다시 잡을 필요가 줄어듭니다.

먼저 거래일에서 월 기준을 뽑는 열을 추가합니다. 월별 보고서에서는 날짜를 텍스트로 자르는 것보다 실제 날짜값으로 월초일을 만드는 편이 피벗이나 정렬에서 안전합니다.

=DATE(YEAR([@거래일]),MONTH([@거래일]),1)

그다음 원장에 부호 열을 추가합니다. 최신 함수만 고집할 필요는 없습니다. 여러 버전에서 무난하게 쓰려면 INDEX/MATCH 조합이 아직도 실무에서 꽤 튼튼합니다.

=IFERROR(INDEX(tbl부호[부호],MATCH([@거래유형],tbl부호[거래유형],0)),0)

여기서 찾지 못한 거래유형은 0으로 처리했습니다. 일부러 이렇게 둔 이유가 있습니다. 새 거래유형이 들어왔을 때 잘못 더해지는 것보다, 일단 보고서에서 빠지게 만든 뒤 검토하는 편이 안전하기 때문입니다. 다만 실제 제출 전에는 반드시 누락 유형을 확인해야 합니다.

이제 조정매출 열을 만듭니다. 채널별 원장에서 취소 금액이 양수와 음수로 섞이는 경우가 많다면 ABS로 금액의 절댓값을 만든 뒤, 우리가 정한 부호를 곱하는 편이 깔끔합니다.

=ABS([@결제금액])*[@부호]

순정산 금액은 회사 정책에 맞게 조정해야 합니다. 예를 들어 수수료와 배송비부담을 차감하는 구조라면 다음처럼 둘 수 있습니다.

=[@조정매출]-[@수수료]-[@배송비부담]

단, 반품 시 배송비를 고객에게 청구하는지, 회사가 부담하는지에 따라 이 식은 달라질 수 있습니다. 정산표에서 가장 위험한 부분이 바로 이런 정책성 금액입니다. 수식보다 먼저 정산 기준을 확인해야 합니다.

보고서 숫자는 SUMIFS로 만들되, 검증 줄을 꼭 남깁니다

월별·매장별 순정산 보고서를 만든다면 행에는 월, 열에는 매장을 놓고 SUMIFS를 걸 수 있습니다. 예를 들어 A열에 월, 4행에 매장명이 있는 보고서라면 다음처럼 작성합니다.

=SUMIFS(tbl원장[순정산],tbl원장[월],$A5,tbl원장[매장],B$4)

이때 보고서 아래쪽에 검증용 합계를 따로 두는 습관이 중요합니다. 예를 들어 원장의 전체 순정산 합계와 보고서 영역의 총합이 같은지 확인합니다.

보고서가 틀렸을 때는 수식을 바로 고치기보다 차이가 나는 원인을 좁히는 순서가 좋습니다. 월 기준이 날짜값인지, 매장명이 원장과 보고서 머리글에서 정확히 같은지, 필터로 일부 행이 빠진 것은 아닌지부터 확인해 보세요. 의외로 ‘강남점’ 뒤에 공백이 붙어 있거나, 거래일이 텍스트라 월 계산이 실패한 경우가 많습니다.

찾지 못한 거래유형을 먼저 잡아야 정산 사고를 줄일 수 있습니다

부호표를 쓰는 방식의 핵심은 누락된 거래유형을 조기에 발견하는 것입니다. 원장에 검토필요 열을 하나 만들어 아래처럼 표시해 두면 좋습니다.

=IF([@부호]=0,"거래유형 확인","")

보고서 제출 전에 이 열을 필터링해서 빈칸이 아닌 행이 있는지 확인합니다. 거래유형이 새로 생겼다면 부호표에 추가하고, 정산 기준을 확인한 뒤 다시 계산하면 됩니다.

또 하나 자주 놓치는 부분은 부분취소입니다. 주문번호 하나에 매출과 부분취소가 같이 존재하면 주문번호 기준으로 중복처럼 보일 수 있습니다. 이때 중복 제거를 해버리면 정산 금액이 틀어집니다. 정산 원장에서는 중복 제거보다 주문번호 + 거래유형 + 거래일 + 금액 조합으로 흐름을 보는 것이 안전합니다.

원장이 여러 파일로 들어오면 Power Query에서 부호표를 합치는 편이 편합니다

매달 쇼핑몰별 원장을 각각 내려받는 업무라면 수식만으로도 가능하지만, 파일 수가 늘어날수록 Power Query가 편합니다. 핵심 흐름은 원장을 불러온 뒤, 부호표와 병합해서 부호 열을 붙이고, 사용자 지정 열로 조정매출을 만드는 것입니다.

Power Query에서는 원장의 거래유형 열과 부호표의 거래유형 열을 기준으로 병합합니다. 병합 후 부호 열을 확장하고, 사용자 지정 열에 다음과 같은 계산을 넣을 수 있습니다.

Number.Abs([결제금액]) * [부호]

순정산 열은 다음처럼 만들 수 있습니다.

[조정매출] - [수수료] - [배송비부담]

이 방식의 장점은 원장 파일이 바뀌어도 새로고침만으로 같은 정리 과정을 반복할 수 있다는 점입니다. 특히 매출, 취소, 반품 시트가 따로 들어오는 회사라면 Power Query에서 시트를 합친 뒤 부호표를 붙이는 구조가 훨씬 깔끔합니다.

실무에서 자주 터지는 실수는 계산식보다 기준표 관리에서 나옵니다

첫 번째로 조심할 부분은 ABS 사용입니다. 모든 거래유형의 방향을 부호표로 통제하겠다는 전제가 있을 때는 유용하지만, 원장에 이미 할인이나 차감 항목이 음수로 정확히 들어와 있고 그 의미를 유지해야 한다면 무조건 ABS를 씌우면 안 됩니다. 금액 열이 무엇을 뜻하는지 먼저 확인해야 합니다.

두 번째는 수수료 부호입니다. 어떤 정산 파일은 수수료를 양수로 제공하고, 어떤 파일은 이미 음수로 제공합니다. 위 예시처럼 순정산 = 조정매출 - 수수료로 계산하려면 수수료 열은 양수 차감값이라는 기준이 필요합니다. 만약 수수료가 음수로 들어온다면 별도 보정 열을 두는 편이 안전합니다.

=ABS([@수수료])

세 번째는 날짜입니다. 거래일이 2026.06.01처럼 보이지만 실제로는 텍스트인 경우, 월 계산이 엉뚱하게 실패할 수 있습니다. 날짜 열을 선택했을 때 표시 형식만 날짜인지, 실제 값도 날짜인지 확인하세요. 날짜가 왼쪽 정렬되어 있거나 YEAR 함수가 오류를 내면 텍스트일 가능성이 큽니다.

빠른 점검이 필요할 때는 오류 행만 색칠해도 효과가 큽니다

파일을 다른 사람에게 넘기기 전, 거래유형 누락 행을 눈에 띄게 표시하고 싶다면 조건부 서식으로도 충분합니다. 그래도 반복 검토가 잦은 파일이라면 간단한 매크로로 부호가 0인 행을 색칠해 둘 수 있습니다. 아래 예시는 tbl원장 표에서 부호 열이 0인 행을 연한 빨간색으로 표시하는 방식입니다.

Sub HighlightNeedCheckRows()
    Dim lo As ListObject
    Dim r As ListRow
    Dim signCol As Long

    Set lo = ActiveSheet.ListObjects("tbl원장")
    signCol = lo.ListColumns("부호").Index

    lo.DataBodyRange.Interior.ColorIndex = xlNone

    For Each r In lo.ListRows
        If r.Range.Cells(1, signCol).Value = 0 Then
            r.Range.Interior.Color = RGB(255, 230, 230)
        End If
    Next r
End Sub

매크로를 쓰지 않는 환경이라면 조건부 서식에서 수식 규칙을 사용해도 됩니다. 표의 부호 열이 0인 행만 색칠하도록 규칙을 걸어두면, 새 거래유형이 들어왔을 때 바로 눈에 들어옵니다.

응용하면 재고·정산·매출 보고서의 구조가 비슷해집니다

거래유형별 부호표 방식은 매출 정산에만 쓰는 방법이 아닙니다. 재고 관리에서는 입고를 +1, 출고를 -1, 조정을 ±로 두고 현재고를 계산할 수 있습니다. 비용 정산에서는 청구를 +1, 환불을 -1로 두면 월별 순비용을 만들 수 있습니다. 핵심은 원장 데이터의 숫자를 무조건 믿는 것이 아니라, 업무 의미를 기준표로 분리해 관리하는 것입니다.

정산 보고서에서 숫자가 맞지 않을 때는 SUMIFS 수식부터 의심하기 쉽지만, 실제 원인은 거래유형, 날짜, 부호, 수수료 기준에 있는 경우가 많습니다. 원장에 계산 열을 덕지덕지 붙이기 전에 부호표를 하나 만들어 보세요. 다음 달에 같은 문제를 다시 찾는 시간이 꽤 줄어들 것입니다.