엑셀 매출 관리: 채널에서 받은 파일을 보고서까지 일곱 단계로 옮깁니다

매달 같은 표를 다시 만듭니다.

채널에서 주문 파일을 내려받고, 거래처 이름을 손으로 맞추고, 피벗을 다시 돌리고, 수수료를 빼고, 작년 숫자를 옆에 붙입니다. 파일이 셋이면 반나절, 다섯이면 하루가 갑니다.

그런데 매달 하는 일이 매달 새로운 일은 아닙니다. 순서가 같습니다. 그 순서를 한 번 만들어 두면 다음 달에는 원본만 갈아 끼웁니다.

엑셀 매출 관리는 일곱 단계로 나뉩니다

① 받은 파일을 한 폴더에 모읍니다

② 채널마다 다른 열 이름을 표준 열로 맞춥니다

③ 거래처와 상품 표기를 코드로 통일합니다

④ 취소·반품 줄을 정리합니다

⑤ 거래처별·월별로 집계합니다

⑥ 수수료와 공제를 빼서 입금액을 냅니다

⑦ 작년과 목표에 견줍니다

일이 막히는 곳은 ⑤가 아닙니다. 대개 ②와 ③입니다. 집계 수식은 십 분이면 쓰고, 이름이 안 맞아 합계가 쪼개지는 것을 찾는 데 두 시간이 갑니다.

열 이름부터 맞추면 뒤가 편해집니다

스마트스토어는 상품주문번호, 다른 채널은 주문번호, 세 번째는 Order No로 옵니다. 사람 눈에는 같은 것이고 수식에는 다른 것입니다.

표준 열을 먼저 정합니다. 주문일 · 채널 · 거래처 · 상품코드 · 수량 · 단가 · 금액 여섯 일곱 개면 대부분 덮습니다. 그다음 채널별로 원본 열 이름과 표준 열 이름을 짝지은 매핑 시트를 하나 둡니다.

이렇게 두면 채널이 열 이름을 바꿔도 고칠 곳이 매핑 시트 한 줄입니다. 수식을 열어 고치는 방식은 채널이 늘 때마다 손이 갑니다. 자세한 순서는 주문서 취합에 적었습니다.

거래처 이름은 코드로 바꿔 둡니다

(주)한국상사 · 주식회사 한국상사 · 한국상사가 한 파일에 같이 있으면 피벗이 세 줄로 만듭니다. 합계는 맞는데 거래처별 숫자가 전부 틀립니다.

거래처 마스터를 한 장 만들고 코드를 붙입니다. 그리고 원본에 나오는 표기를 전부 그 코드에 연결한 별칭표를 둡니다.

=IFERROR(VLOOKUP(C2,별칭표!A:B,2,FALSE),"미매칭")

미매칭으로 남는 줄이 이번 달에 새로 나타난 표기입니다. 그 줄만 보고 별칭표에 추가하면 다음 달부터 자동으로 붙습니다. 거래처 관리에서 이 표를 어떻게 만드는지 다뤘습니다.

집계는 SUMIFS 하나로 끝납니다

여기까지 오면 계산은 짧습니다.

=SUMIFS(금액범위,거래처범위,$A2,월범위,B$1)

거래처를 행에, 월을 열에 두면 표가 한 번에 채워집니다. 피벗으로 해도 되고, 매달 같은 모양이 필요하면 수식이 낫습니다.

집계를 마치면 검산을 합니다. 원본 금액 열의 총합과 집계표 총합이 같아야 합니다. 다르면 ③에서 놓친 줄이 있습니다. 엑셀 거래처별 매출 합계에서 사람들이 가장 많이 놓치는 것이 이 검산입니다. 자세한 수식과 안 맞는 세 경우는 거래처별 매출 합계에 있습니다.

매출과 입금액은 다른 숫자입니다

주문 금액을 다 더해도 통장에 그 돈이 안 들어옵니다. 판매수수료, 결제수수료, 반품, 쿠폰 부담분, 광고비 상계가 차례로 빠집니다.

채널별 수수료율표를 만들어 두고 공제 항목을 열로 세워 둡니다. 그러면 예상 입금액이 나오고, 실제 입금액과 비교할 수 있습니다. 이 두 숫자의 차이가 매달 얼마인지 아는 곳이 생각보다 적습니다. 정산서에서 이 부분만 따로 봤습니다.

늘었다 줄었다 다음에 할 일이 있습니다

작년 대비 12% 늘었다는 문장은 보고서에 쓸 수 있지만 다음 행동을 못 정합니다.

증감액을 두 숫자로 나눕니다. 수량이 늘어서 생긴 부분과 단가가 올라서 생긴 부분입니다.

수량 차이 = (올해 수량 - 작년 수량) × 작년 단가
단가 차이 = (올해 단가 - 작년 단가) × 올해 수량

두 숫자를 합치면 증감액이 됩니다. 수량이 늘어난 것과 단가가 오른 것은 다음 달에 할 일이 다릅니다. 전년대비 계산에 분모가 0이거나 음수일 때 처리까지 적었습니다.

엑셀 자동화는 수식보다 앞 단계에 붙습니다

일곱 단계를 다 만들어도 다음 달에 ①과 ②를 손으로 반복하게 됩니다.

파워 쿼리에서 폴더를 원본으로 잡고 열 이름 바꾸기·형식 변환을 단계로 저장해 두면, 새 파일을 폴더에 넣고 새로 고침만 누르면 ④까지 채워진 표가 나옵니다. 엑셀 매출 관리에서 시간을 가장 많이 줄이는 곳이 여기입니다.

엑셀 매출 정리하는 법을 검색하면 대개 ⑤부터 나옵니다. 수식은 이미 잘 정리돼 있습니다. 매달 반복되는 손은 그 앞에 있습니다.

이 순서는 재무 쪽에서 매출 원장을 다루는 방식과 같습니다. 데이터를 정제하는 앞 단계는 엑셀 데이터 정리 여덟 단계에 더 자세히 적어 두었습니다.

자주 받는 질문

Q. 파일이 매달 두 개뿐인데도 이 순서가 필요한가요

A. 두 개면 손으로 해도 됩니다. 다만 거래처 코드와 별칭표는 파일 개수와 상관없이 만들어 두는 편이 낫습니다. 표기 흔들림은 파일이 하나여도 생기고, 나중에 몇 달치를 붙일 때 그때 만들면 과거분을 다시 손봐야 합니다.

Q. 피벗과 SUMIFS 중 무엇을 쓰나요

A. 매달 모양이 같은 보고서면 SUMIFS로 틀을 고정합니다. 이번 달만 이리저리 잘라 봐야 하면 피벗이 빠릅니다. 둘을 같이 두고 피벗으로 확인한 숫자를 SUMIFS 표와 대조하는 방식도 씁니다.

Q. 엑셀 매출 관리를 어디까지 하고 시스템으로 넘어가야 하나요

A. 판단 기준은 파일 개수가 아니라 손이 가는 횟수입니다. 매달 같은 손질을 세 번 넘게 반복하고 있으면 그 손질을 앞 단계로 옮길 시점입니다. 그때도 엑셀을 버리는 것이 아니고 앞 단계만 옮깁니다.

Q. 검산이 안 맞으면 어디부터 보나요

A. 미매칭 줄 개수를 먼저 봅니다. 대개 여기서 끝납니다. 그다음이 취소·반품 줄의 부호이고, 마지막이 숫자로 보이는 텍스트입니다. 세 번째는 공백과 형식 정제에 진단 수식을 적어 뒀습니다.

원장 총합과 보고서 총합이 같은 달만 보고서를 냅니다. 그 한 줄부터 만들어 두려 합니다.