택배비 정산서가 맞는지, 무게 구간 요율표로 한 줄씩 검산하는 법

월말 택배비 정산서를 받으면 총액만 보고 맞다, 틀리다 판단하기가 참 애매합니다. 특히 건수가 몇천 건으로 늘어나면 한두 건 차이는 눈에 잘 안 들어오고, 제주·도서산간·반품 수거비처럼 조건이 섞이면 단순 합계로는 검산이 어렵습니다. 이번 글에서는 작은 택배비 정산 사례 하나를 끝까지 다듬어 보겠습니다. 핵심은 운송장별 청구금액 옆에 ‘우리 기준 예상 택배비’를 붙이고, 차이가 나는 건만 바로 걸러내는 구조를 만드는 것입니다.

예제는 아래처럼 시작합니다. 택배사에서 받은 원본에는 운송장번호, 출고일, 권역, 실중량, 청구택배비, 수거구분이 들어 있고, 별도 시트에는 무게 구간별 요율표가 있습니다. 여기서 실무적으로 가장 중요한 판단은 ‘무게를 어느 구간에 넣을 것인가’입니다. 2.8kg은 0kg 이상 3kg 미만 구간, 3.0kg은 3kg 이상 구간으로 계산해야 하므로, 정확히 같은 값을 찾는 방식이 아니라 ‘직전 구간’을 찾는 방식이 필요합니다.

요율표는 다음처럼 ‘시작kg’을 기준으로 정리해 둡니다. 여기서 시작kg 열은 반드시 오름차순이어야 합니다. 이 부분이 흐트러지면 수식은 오류를 내지 않고도 엉뚱한 구간을 가져올 수 있어서 더 위험합니다.

먼저 두 범위를 각각 표로 바꿉니다. 운송장 원본은 tbl운송장, 요율표는 tbl요율처럼 이름을 붙여두면 나중에 행이 추가되어도 수식 범위를 다시 고칠 일이 줄어듭니다. 표 이름은 아무렇게나 지어도 되지만, 한글 이름을 쓰더라도 중간에 공백을 넣지 않는 편이 관리하기 좋습니다.

원본의 실중량이 처음부터 숫자라면 바로 계산해도 됩니다. 그런데 실제 정산 파일은 2.8kg, 3 kg, 4.2KG처럼 단위와 공백이 섞여 들어오는 경우가 많습니다. 이 상태에서 수식을 걸면 겉으로는 숫자처럼 보여도 비교 계산이 되지 않습니다. 건수가 적으면 찾기/바꾸기로 처리해도 되지만, 매달 반복된다면 Power Query에서 한 번 정리해 두는 편이 안정적입니다.

데이터 탭에서 표/범위에서 가져오기로 쿼리를 열고, 실중량 열에 들어 있는 kg 문자를 제거한 뒤 숫자 형식으로 바꿉니다. 아래는 같은 작업을 표현한 예시입니다. 꼭 코드를 직접 입력할 필요는 없지만, 어떤 처리가 들어갔는지 이해해 두면 나중에 문제를 찾기 쉽습니다.

= Table.TransformColumns(이전단계, {{"실중량", each Number.FromText(Text.Replace(Text.Lower(Text.Trim(_)), "kg", "")), type number}})

이제 정리된 열 이름을 실중량kg로 바꿔 둡니다. 이름만 봐도 숫자인지, 단위가 제거된 값인지 알 수 있어야 합니다. 엑셀 파일을 여럿이 같이 쓸 때는 이런 열 이름 하나가 사고를 줄여줍니다.

예상 택배비 열을 추가하고 수식을 넣어 보겠습니다. 아래 수식은 Microsoft 365 최신 버전을 기준으로 설명합니다. Excel 2021/2024 등 영구 버전에서는 일부 동적 배열 함수 지원 여부가 다를 수 있으니, 사용 중인 버전에서 함수 지원 여부를 먼저 확인해 주세요.

=LET(w,[@실중량kg],zone,[@권역],fee,INDEX(tbl요율[[일반]:[도서]],MATCH(w,tbl요율[시작kg],1),MATCH(zone,tbl요율[[#Headers],[일반]:[도서]],0)),fee+IF([@수거구분]="반품",500,0))

이 수식은 길어 보이지만 구조는 단순합니다. MATCH(w,tbl요율[시작kg],1) 부분이 실중량에 맞는 무게 구간 행을 찾습니다. 여기서 마지막 인수 1은 ‘작거나 같은 값 중 가장 가까운 값’을 찾겠다는 뜻입니다. 그래서 4.2kg은 시작kg 3행에 들어가고, 5.0kg은 시작kg 5행에 들어갑니다. 실무에서 무게 구간 요금, 누진 단가, 등급별 수수료를 계산할 때 자주 쓰는 방식입니다.

그다음 MATCH(zone,tbl요율[[#Headers],[일반]:[도서]],0)는 권역에 해당하는 열을 찾습니다. 권역이 일반이면 일반 열, 제주면 제주 열, 도서면 도서 열을 가져옵니다. 이렇게 행과 열을 각각 찾은 뒤 INDEX로 교차 지점의 금액을 반환합니다. 마지막에는 반품이면 수거비 500원을 더했습니다. 회사마다 반품비를 별도 요율로 계산하는 곳도 있으니, 이 부분은 내부 기준에 맞게 바꾸면 됩니다.

여기서 흔히 생기는 실수는 권역 값이 요율표 머리글과 미세하게 다른 경우입니다. 예를 들어 원본에는 제주도, 요율표에는 제주라고 되어 있으면 수식은 해당 열을 찾지 못합니다. 도서산간도 도서, 도서산간, 산간처럼 표현이 제각각일 때가 많습니다. 이때는 원본을 억지로 수식에서 처리하기보다, 기준값을 먼저 통일하는 보조표를 하나 두는 편이 좋습니다.

예상 택배비가 붙었다면 이제 차이를 계산합니다. 새 열 이름을 차이로 만들고 아래처럼 넣습니다.

=[@청구택배비]-[@예상택배비]

차이가 0이면 택배사 청구금액과 우리 기준 계산금액이 일치한다는 뜻입니다. 양수이면 택배사가 더 많이 청구한 것이고, 음수이면 우리 기준보다 적게 청구된 것입니다. 여기서 바로 합계만 보지 말고, 판정 열을 하나 더 두면 검토 속도가 빨라집니다.

=IF([@차이]=0,"일치",IF(ABS([@차이])<=100,"소액차이","확인필요"))

소액차이를 따로 둔 이유는 실제 업무에서 부가세 반올림, 택배사 시스템의 최소 과금 단위, 계약 변경일 같은 이유로 10원 또는 100원 단위 차이가 생기기 때문입니다. 모든 차이를 같은 무게로 보면 정작 중요한 건을 놓칩니다. 기준은 회사마다 다르지만, 처음에는 0원 일치와 0원이 아닌 차이를 나누고, 몇 달치 패턴을 본 뒤 허용 범위를 정하는 것을 추천합니다.

확인 순서는 이렇게 잡으면 좋습니다. 먼저 요율표의 시작kg이 오름차순인지 봅니다. 다음으로 실중량kg 열이 숫자인지 확인합니다. 셀 왼쪽 위 초록 삼각형이 있거나, 합계가 이상하게 나오면 텍스트일 가능성이 큽니다. 그다음 권역 값이 요율표 머리글과 정확히 맞는지 봅니다. 마지막으로 반품 수거비처럼 별도 가산 조건이 실제 계약서와 같은지 확인합니다. 수식 자체보다 기준표와 원본 정리가 먼저입니다.

검토용 화면은 조건부 서식까지 걸어두면 훨씬 보기 편합니다. 판정 열이 확인필요인 행만 연한 빨강으로 표시하고, 차이 금액이 양수인 경우만 굵게 표시해 보세요. 조건부 서식의 수식 규칙을 쓴다면 예를 들어 판정 열이 J열일 때 다음처럼 지정할 수 있습니다.

=$J2="확인필요"

여기서 행 번호는 데이터의 첫 행에 맞추고, 열 문자만 고정하는 것이 포인트입니다. $J$2처럼 행까지 고정하면 첫 행만 기준으로 전체 색이 칠해지는 실수가 생깁니다. 조건부 서식이 이상하게 보일 때는 적용 대상 범위와 달러 기호 위치를 같이 확인해야 합니다.

조금 더 실무적으로 쓰려면 피벗 테이블을 하나 붙여서 권역별 차이 합계, 판정별 건수, 택배사 청구금액 합계를 같이 봅니다. 한 줄 검산은 운송장 단위 오류를 찾는 용도이고, 피벗은 월말 보고용 요약을 만드는 용도입니다. 예를 들어 판정이 확인필요인 건수는 12건인데 차이 합계가 8,000원이라면 단순 확인으로 끝날 수 있지만, 3건에 70,000원이 몰려 있다면 계약 요율 누락이나 추가 운임 청구를 의심해야 합니다.

이 구조의 장점은 다음 달에도 그대로 쓸 수 있다는 점입니다. 택배사 파일만 새로 붙여 넣거나 Power Query 새로 고침을 하면, 실중량 정리부터 예상 택배비 계산, 차이 판정까지 이어집니다. 요율이 바뀌면 수식을 고치지 말고 tbl요율의 금액만 바꾸면 됩니다. 만약 계약 변경일이 중간에 끼어 있다면 요율표에 적용시작일을 추가하고, 출고일 기준으로 요율을 고르는 방식으로 확장할 수 있습니다.

이번 예제는 택배비였지만 같은 방식은 포장비, 대행 수수료, 광고 수수료, 누진 구간별 인센티브 계산에도 그대로 응용됩니다. 핵심은 원본을 숫자로 정리하고, 기준표를 오름차순 구간표로 만든 뒤, 차이만 남겨 검토하는 흐름입니다. 다음에는 여기에 계약 변경일 조건까지 넣어 기간별 요율이 자동으로 바뀌는 형태로 넓혀볼 수 있습니다.