데이터 병합 체크리스트, 주문번호와 고객 정보를 틀리지 않게 합치는 법

스마트스토어 주문 내역과 택배 송장 파일을 합치는데 주문번호가 하나라도 어긋나면 고객에게 다른 송장번호가 안내될 수 있습니다. 데이터 병합은 표 두 개를 붙이는 작업처럼 보이지만, 실제로는 같은 사람과 같은 주문을 정확히 찾는 작업입니다.

데이터 병합 체크리스트는 엑셀 수식이나 기능을 쓰기 전에 확인할 기준입니다. 주문번호, 회원번호, 휴대폰 번호, 이메일, 상품코드처럼 두 파일을 이어주는 값을 먼저 정하고, 그 값이 깨끗한지 확인해야 합니다.

병합할 목적을 한 문장으로 적습니다

파일을 합치기 전에 왜 합치는지부터 정해야 합니다. 목적이 다르면 필요한 열도 달라집니다. 주문 내역에 송장번호를 붙이는 것과 고객 문의에 주문 상태를 붙이는 것은 같은 병합이 아닙니다.

• 주문 내역 + 송장 파일: 주문번호로 배송 정보 붙이기
• 고객 문의 + 주문 내역: 휴대폰 또는 주문번호로 구매 정보 확인하기
• 광고 리포트 + 매출 파일: 상품코드나 캠페인명으로 성과 묶기
• 교육 신청자 + 출석부: 연락처나 신청번호로 참석 여부 붙이기

목적이 정해지면 유지할 열과 버릴 열이 보입니다. 모든 열을 다 붙이면 파일만 무거워지고 오류를 찾기 어려워집니다.

병합 키는 하나로 부족할 수 있습니다

병합 키는 두 파일을 연결하는 값입니다. 가장 좋은 키는 주문번호, 신청번호, 회원번호처럼 고유한 값입니다. 이름이나 휴대폰 뒷자리처럼 중복될 수 있는 값은 위험합니다.

고유한 키가 없으면 두 개 이상을 같이 써야 합니다. 예를 들어 이름만으로는 동명이인을 구분할 수 없습니다. 이름과 휴대폰 번호, 주문일과 상품코드처럼 조합해서 확인해야 합니다.

앞자리 0이 사라지는지 확인합니다

엑셀에서 전화번호, 우편번호, 상품코드를 숫자로 열면 앞자리 0이 사라질 수 있습니다. “01012345678”은 괜찮아 보여도 “010” 앞자리가 다른 형식으로 들어온 파일과 맞지 않을 수 있습니다.

병합 키로 쓸 열은 숫자가 아니라 텍스트로 관리하는 편이 안전합니다. 특히 주문번호, 송장번호, 회원번호는 계산할 숫자가 아닙니다. 텍스트로 두고 공백과 특수문자를 정리해야 합니다.

공백과 하이픈을 먼저 맞춥니다

같은 전화번호라도 한 파일에는 010-1234-5678, 다른 파일에는 01012345678로 들어올 수 있습니다. 이름 앞뒤 공백, 이메일 대소문자, 상품코드의 하이픈도 병합 실패 원인이 됩니다.

병합 전에 정리용 열을 하나 만들어 표기를 맞춥니다. 전화번호는 하이픈을 제거하고, 이름은 앞뒤 공백을 지우고, 이메일은 소문자로 맞추는 식입니다. 원본 열을 바로 고치기보다 정리용 열을 따로 두면 되돌리기 쉽습니다.

중복 키를 먼저 세어봅니다

병합 키가 중복되면 결과가 엉킬 수 있습니다. 주문번호는 보통 한 번만 나와야 하지만, 주문번호 하나에 여러 상품 행이 있는 파일도 있습니다. 이 경우는 주문 단위인지 상품 행 단위인지 먼저 정해야 합니다.

고객 문의 파일도 한 고객이 여러 번 문의하면 휴대폰 번호가 여러 번 나옵니다. 이런 데이터를 주문 내역과 합칠 때는 최신 문의만 볼지, 모든 문의를 행으로 유지할지 정해야 합니다.

병합 전후 행 수를 기록합니다

데이터를 합친 뒤 행 수가 갑자기 늘거나 줄면 확인해야 합니다. 주문 1,000건에 송장 1,000건을 붙였는데 결과가 1,120행이면 중복 키가 있는 것입니다. 반대로 930행만 남으면 일부 주문이 누락됐을 수 있습니다.

못 찾은 값은 따로 모읍니다

XLOOKUP이나 VLOOKUP으로 병합하면 못 찾은 값이 생깁니다. 이 값을 그냥 빈칸으로 두면 나중에 놓칩니다. “미매칭” 같은 표시를 넣고 따로 필터링해야 합니다.

Google Sheets의 XLOOKUP 함수는 찾는 값, 찾을 범위, 반환할 범위를 지정해 값을 가져올 수 있습니다. 값이 없을 때 표시할 문구도 지정할 수 있으므로, 병합 후 누락값을 확인하기 좋습니다.

엑셀 파일이 크면 Power Query를 씁니다

행이 많고 매주 반복되는 병합이라면 수식만으로 처리하기보다 Power Query를 쓰는 편이 낫습니다. Microsoft 지원 문서에서는 Power Query에서 데이터를 가져오고 변환한 뒤 쿼리를 로드할 수 있다고 안내합니다. 쿼리 관리 문서에는 쿼리의 열을 다른 쿼리의 일치하는 열과 병합하는 기능도 설명되어 있습니다.

Power Query의 장점은 같은 작업을 다음 주에도 반복할 수 있다는 점입니다. 주문 파일과 송장 파일 형식이 같다면 새 파일만 바꿔 넣고 새로 고침하면 됩니다. 매번 수식을 복사하면서 범위를 잘못 잡는 실수를 줄일 수 있습니다.

샘플 10건은 눈으로 대조합니다

기능이 맞아 보여도 샘플 확인은 필요합니다. 병합 결과에서 앞쪽 5건, 중간 3건, 끝쪽 2건 정도를 골라 원본 파일과 직접 대조합니다. 특히 금액, 송장번호, 고객명, 전화번호는 눈으로 확인하는 것이 좋습니다.

데이터가 많은 경우에는 금액이 큰 주문, 취소 주문, 교환 주문, 같은 이름 고객처럼 위험한 행을 따로 뽑아 봅니다. 평범한 행 10건만 맞는다고 전체가 맞는 것은 아닙니다.

작업 파일과 제출 파일을 나눕니다

병합 작업을 한 파일 그대로 제출하면 불필요한 원본 열이나 내부 메모가 같이 나갈 수 있습니다. 작업 파일에는 원본, 정리용 열, 확인용 열을 남기고, 제출 파일에는 필요한 열만 복사합니다.

고객에게 전달되는 파일이라면 개인정보 열을 더 조심해야 합니다. 이름, 전화번호, 주소, 주문번호는 필요한 범위만 남기고 나머지는 삭제하거나 가립니다.

실무용 확인 순서

1. 병합 목적을 한 문장으로 적습니다.
2. 기준 파일과 붙일 파일을 정합니다.
3. 병합 키를 고릅니다.
4. 키 열을 텍스트로 맞춥니다.
5. 공백, 하이픈, 대소문자를 정리합니다.
6. 중복 키와 빈 키를 확인합니다.
7. XLOOKUP이나 Power Query로 병합합니다.
8. 못 찾은 값을 따로 필터링합니다.
9. 병합 전후 행 수를 기록합니다.
10. 샘플 행을 원본과 눈으로 대조합니다.
11. 제출 파일에는 필요한 열만 남깁니다.

데이터 병합에서 중요한 것은 빠르게 붙이는 속도가 아니라 틀린 값을 남기지 않는 과정입니다. 주문번호 하나가 어긋나면 고객 안내, 배송, 정산까지 같이 틀어질 수 있습니다. 그래서 병합은 수식보다 확인 순서가 먼저입니다.

참고한 공식 자료

• Microsoft 지원: Excel에서 쿼리 만들기, 로드 또는 편집
• Microsoft 지원: 쿼리 관리
• Microsoft Learn: Power Query란?
• Google Docs 편집기 고객센터: XLOOKUP 함수
• Google Docs 편집기 고객센터: 텍스트 분할, 중복 데이터 삭제, 공백 제거

질문별 빠른 답

데이터 병합 전 가장 먼저 확인할 것은 무엇인가요?

기준 키입니다. 주문번호나 고객ID처럼 두 파일을 정확히 연결할 열이 있어야 합니다.

병합 후 검수는 어떻게 하나요?

병합 전후 행 수, 빈 값, 중복 키, 합계 금액을 비교해야 합니다.