엑셀 주문서 취합 방법: 채널마다 열 이름이 다르면 표준 열을 먼저 만듭니다

파일 다섯 개를 하나로 붙이는 데 오전이 갑니다.

붙이는 것 자체는 복사하고 붙여넣으면 끝납니다. 시간이 가는 곳은 그다음입니다. 어떤 파일은 상품주문번호이고 어떤 파일은 주문번호이고, 수량 열이 세 번째에 있다가 일곱 번째로 가 있습니다. 열 위치를 믿고 붙이면 금액 열에 수량이 들어갑니다. 표는 만들어지고 숫자만 틀립니다.

엑셀 주문서 취합 방법은 표준 열을 정하는 것부터입니다

붙이기 전에 내가 쓸 열을 정합니다. 주문일 · 채널 · 주문번호 · 거래처 · 상품코드 · 수량 · 단가 · 금액 여덟 개면 대부분 덮습니다.

엑셀 주문서 취합 방법에서 실수가 나오는 곳이 여기입니다. 원본에 있는 열을 다 가져오려 하면 파일마다 열 개수가 달라져서 표가 안 붙습니다. 필요한 여덟 개만 뽑고 나머지는 두고 옵니다. 나중에 필요해지면 그때 한 열을 추가합니다.

표준 열은 한 번 정하면 잘 안 바뀝니다. 바뀌는 쪽은 늘 원본입니다.

매핑 시트에 원본 열 이름을 적어 둡니다

열매핑 시트를 하나 만들고 세 열을 둡니다. 채널 · 원본 열 이름 · 표준 열 이름입니다.

스마트스토어  상품주문번호   주문번호
스마트스토어  수량           수량
쿠팡          주문번호       주문번호
쿠팡          구매수(수량)   수량
자사몰        Order No       주문번호

이렇게 두면 채널이 열 이름을 바꿔도 고칠 곳이 이 시트 한 줄로 끝납니다. 수식이나 매크로 안에 열 이름을 박아 두면 채널이 하나 늘 때마다 그 안을 열어 고치게 됩니다.

스마트스토어 엑셀은 다운로드 양식이 개편될 때 열 이름과 순서가 같이 바뀝니다. 매핑 시트를 두는 이유가 대부분 여기서 나옵니다.

엑셀 시트 합치기는 파워 쿼리로 폴더째 읽습니다

파일을 하나씩 열어 복사하는 대신 폴더를 원본으로 잡습니다.

데이터 탭에서 데이터 가져오기 · 파일에서 · 폴더에서를 고르고 주문 파일을 모아 둔 폴더를 지정합니다. 결합을 누르면 폴더 안의 모든 파일이 한 표로 쌓입니다. 파일을 하나도 열지 않습니다.

여기서 두 가지를 꼭 합니다.

① Source.Name 열을 남깁니다. 어느 파일에서 온 줄인지 나중에 추적할 때 씁니다

② 열 이름 바꾸기와 형식 변환을 쿼리 단계로 저장합니다. 주문일은 날짜로, 수량과 금액은 숫자로 바꿔 둡니다

다음 달에는 파일을 그 폴더에 넣고 새로 고침만 누르면 됩니다. 엑셀 주문서 취합 방법에서 시간이 실제로 줄어드는 곳이 이 단계입니다. 엑셀 자동화를 붙인다면 수식보다 여기가 먼저입니다. 엑셀 자동화로 줄어드는 시간의 대부분이 이 단계에 있습니다.

열 이름이 바뀌면 조용히 틀어집니다

파워 쿼리는 없는 열을 만나면 오류를 내기도 하고, 빈 열로 통과시키기도 합니다. 뒤쪽이 위험합니다. 오류가 안 나니 아무도 안 봅니다. 표는 만들어지는데 그 채널 매출만 0으로 들어갑니다.

그래서 취합이 끝나면 세 줄을 확인합니다.

=COUNTA(표준열범위)          표준 열마다 값이 몇 개 들어왔는지
=SUMIF(채널범위,"쿠팡",금액)  채널별 합계가 0이 아닌지
=COUNTIF(주문번호범위,"")     주문번호가 빈 줄이 몇 개인지

채널별 합계가 지난달과 비슷한지만 봐도 대부분 걸립니다. 이 확인을 안 하면 보고서를 내고 나서 한 채널이 통째로 빠진 것을 알게 됩니다.

붙인 다음에는 표기를 통일합니다

합쳐진 표에는 같은 거래처가 여러 이름으로 들어 있습니다. 채널마다 입력 규칙이 다르기 때문입니다.

취합과 표기 통일은 다른 작업입니다. 취합은 열을 맞추고, 표기 통일은 값을 맞춥니다. 순서를 섞으면 두 번 손댑니다. 거래처 관리에서 별칭표로 이 값을 코드로 바꾸는 방법을 다뤘습니다.

시트 구조가 처음부터 같은 파일들을 붙이는 경우라면 파워 쿼리까지 안 가도 됩니다. 엑셀 시트 합치기에 더 간단한 순서를 적어 뒀습니다.

표준 열에 안 붙은 열을 세어 둡니다

매달 기록할 숫자 하나를 정하면 이것입니다. 원본에는 있는데 매핑 시트에 없어서 버려진 열이 몇 개인지 셉니다.

이 숫자가 갑자기 늘면 채널이 양식을 바꾼 달입니다. 그때 매핑 시트를 손봅니다. 다른 달에는 손댈 일이 없습니다. 세어 두지 않으면 몇 달 뒤 숫자가 안 맞을 때 원인을 못 찾습니다.

전체 순서는 엑셀 매출 관리 일곱 단계에 정리해 두었습니다. 이 글은 그중 두 번째 단계를 펼친 것입니다.

자주 받는 질문

Q. 파워 쿼리가 없는 버전을 씁니다

A. 2016 이상이면 데이터 탭에 들어 있습니다. 그보다 낮으면 파일마다 표준 열 순서로 시트를 하나 만들어 두고 그 시트만 복사해 쌓는 방식이 현실적입니다. 원본을 직접 붙이지 않고 중간 시트를 두고 그 시트만 붙입니다.

Q. 채널이 파일을 CSV로 주는데 한글이 깨집니다

A. 파일을 더블클릭해서 열지 않고 데이터 가져오기로 불러오면서 인코딩을 UTF-8로 지정합니다. 더블클릭으로 열면 엑셀이 시스템 기본 인코딩으로 읽어서 깨집니다. 이미 깨진 파일은 다시 받는 편이 빠릅니다.

Q. 취소된 주문은 취합 단계에서 빼나요

A. 빼지 않고 그대로 가져옵니다. 상태 열을 표준 열에 하나 더 두고, 집계할 때 조건으로 거릅니다. 취합 단계에서 지우면 나중에 취소율을 못 셉니다. 원본은 받은 그대로 쌓아 둡니다.

Q. 스마트스토어 엑셀 양식이 바뀌면 쿼리를 다시 만들어야 하나요

A. 열 이름 바꾸기를 쿼리 단계가 아니라 매핑 시트로 처리했다면 다시 만들 필요가 없습니다. 쿼리는 폴더를 읽어 쌓기만 하고, 이름 맞추기는 시트에서 하는 구성이 개편에 잘 버팁니다.

표준 열에 안 붙은 열이 0인 달만 그대로 넘어갑니다. 그 한 숫자부터 세어 두려 합니다.