이 글의 목적은 여러 파일·시트의 데이터를 병합할 때 중복 데이터를 정확히 식별·정리하여 신뢰도 높은 통합 테이블을 만드는 실무 절차와 도구 사용법을 제공하는 것이다.
1. 중복 데이터가 생기는 근본 원인과 분류
데이터 병합 과정에서 중복은 다양한 경로로 발생한다. 근본 원인을 식별하면 처리 전략이 단순해진다.
- 정확히 동일한 행 중복 : 모든 열 값이 일치하는 복제 행이다.
- 키 중복 : 기본키(예: 고객ID, 품목코드)가 같으나 일부 속성이 다르다.
- 표기 차이로 인한 준중복 : 공백, 대소문자, 특수문자, 지역 형식 차이로 값이 달라 보인다.
- 근접 일치 : ‘홍길동’ vs ‘홍 길동’, ‘(주)세이프케미’ vs ‘세이프케미’처럼 유사하지만 완전 일치하지 않는다.
- 기간 중복 : 동일 키가 기간 열(시작일·종료일)에서 겹친다.
주의 : 병합 전에 ‘중복의 정의’를 명시적으로 합의해야 한다. 기본키 기준인지, 열 조합 기준인지, 또는 유사도 기준인지 결정해야 한다.
2. 병합 전략 설계: 키 정의와 표준화
병합의 품질은 키 정의와 사전 표준화에 좌우된다.
- 기본키 선정 : 단일 키가 없으면 열 조합으로 복합키를 만든다. 예: 고객ID+거래일자+품목코드이다.
- 스키마 정렬 : 열 이름·데이터 형식을 통일한다. 텍스트 vs 숫자, 날짜 형식을 사전에 일치시킨다.
- 정규화 : 불필요한 접두사·괄호·하이픈 등을 규칙적으로 제거한다.
| 표준화 함수 | 용도 | 예시 |
|---|---|---|
| TRIM | 앞뒤·중복 공백 제거 | =TRIM(A2) |
| CLEAN | 인쇄 불가 문자 제거 | =CLEAN(A2) |
| UPPER/LOWER/PROPER | 대소문자 정규화 | =UPPER(A2) |
| SUBSTITUTE | 특정 문자 치환 | =SUBSTITUTE(A2,"-","") |
| NUMBERVALUE | 지역 구분자 숫자 변환 | =NUMBERVALUE(A2,",",".") |
| TEXTSPLIT/TEXTBEFORE/TEXTAFTER | 토큰 분리 | =TEXTSPLIT(A2,",") |
| DATEVALUE | 텍스트→날짜 | =DATEVALUE(A2) |
주의 : 통화·날짜·소수점 구분자는 지역 설정에 따라 다르다. 시스템 언어와 서식 코드를 일관되게 맞춘다.
3. 상황별 중복 처리 도구 선택
| 상황 | 권장 도구 | 장점 | 단점 |
|---|---|---|---|
| 완전 동일 행 중복 | 데이터 탭 → 중복 제거 | 빠르고 간단하다 | 되돌리기 어려우므로 사본 필요하다 |
| 키 기준 중복 | 피벗테이블·파워쿼리 그룹화 | 집계·최신값 선택 가능하다 | 설정이 필요하다 |
| 유사 문자열 중복 | 파워쿼리 퍼지 매치(Fuzzy) | 근접 일치 자동 연결이다 | 임계값 튜닝 필요하다 |
| 수식 기반 동적 목록 | UNIQUE/FILTER/VSTACK | 변경 즉시 반영된다 | Microsoft 365 필요하다 |
| 대용량·반복 병합 | 파워쿼리·VBA | 자동화에 유리하다 | 학습 곡선이 있다 |
4. 빠른 정리: ‘중복 제거’ 대화형 기능
- 중복 점검 대상 범위를 선택한다.
- 데이터 탭 → 중복 제거를 클릭한다.
- 중복 판단 열을 지정한다. 복합키면 여러 열을 체크한다.
- 확인을 눌러 중복을 제거한다.
주의 : 직접 삭제 기능은 복구가 어렵다. 먼저 복사본 시트에서 시도하거나, ‘고급 필터’로 고유 레코드만 다른 위치에 복사하여 안전하게 검증한다.
5. 고급 필터로 ‘고유 레코드’ 추출
- 데이터 탭 → 고급을 클릭한다.
- 목록 범위와 대상 복사 위치를 설정한다.
- ‘고유 레코드만’ 체크하고 확인한다.
해법은 비파괴적이므로 품질 검증에 유리하다.
6. 수식 기반 동적 중복 제거(365 권장)
6.1 UNIQUE로 고유 목록 생성
=UNIQUE(A2:D1000, , TRUE)열 조합으로 고유 행을 만들려면 병합 키를 보조열에 만든다.
=UNIQUE(CHOOSECOLS(A2:D1000,1,3))6.2 중복만 추출
=FILTER(A2:D1000, COUNTIF(A2:A1000, A2:A1000)>1)6.3 최신 레코드만 남기기
=SORTBY(UNIQUE(A2:C1000), BYROW(A2:C1000, LAMBDA(r, MAX(IF(A2:A1000=TAKE(r,1), D2:D1000)))), -1)위 예시에서 열 D는 날짜이며 동일 키의 최대 날짜를 기준으로 정렬한다.
7. 다중 파일 병합과 중복 제어: 파워쿼리(Power Query)
7.1 폴더의 모든 파일 병합
- 데이터 탭 → 데이터 가져오기 → 파일에서 → 폴더에서를 선택한다.
- 폴더 경로를 지정하고 ‘파일 결합’을 실행한다.
- 변환 에디터에서 열 형식을 지정하고 필요한 열만 선택한다.
- 중복 제거: 홈 → 중복 항목 제거 또는 그룹화를 사용한다.
7.2 키 기준 그룹화와 집계
- 홈 → 그룹화를 클릭한다.
- 그룹화 기준 열(또는 열 조합)을 지정한다.
- 작업에서 ‘모든 행’을 선택하여 상세 행 테이블을 만든다.
- 추가 열에서 최신 날짜 행만 선택하거나 합계·최대 등 규칙을 정의한다.
7.3 퍼지 매치로 근접 일치 병합
- 홈 → 쿼리 병합을 클릭한다.
- 조인할 두 테이블과 열을 지정한다.
- ‘근사치 일치 사용’을 선택하고 유사도 임계값 및 변형 옵션(대소문자 무시, 공백 무시 등)을 조정한다.
- 검토용으로 유사도 점수(변환 열) 또는 일치 카운터를 추가하여 튜닝한다.
주의 : 퍼지 매치는 편리하지만 잘못된 매칭을 유발할 수 있다. 대량 적용 전 표본 검증을 통해 임계값을 보정한다.
8. 키 충돌 해결 정책 설계
동일 키의 여러 후보 행이 있을 때 한 줄로 정리하는 규칙이 필요하다.
- 최신 우선 : 날짜 열의 최댓값을 남긴다.
- 우선순위 소스 : 시스템별 신뢰도 가중치로 선택한다.
- 완전성 우선 병합 : 비어 있지 않은 값을 우선 채택한다.
'VBA 예시: 키 기준으로 최신 날짜 행만 남기기(정렬 기반) Sub KeepLatestByKey() Dim rng As Range Set rng = Range("A1").CurrentRegion ' A열=Key, D열=Date rng.Sort Key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("D1"), Order2:=xlDescending, Header:=xlYes Dim i As Long For i = rng.Rows.Count To 2 Step -1 If Cells(i, 1).Value = Cells(i - 1, 1).Value Then Rows(i).Delete End If Next i End Sub 9. 병합 전 정제 파이프라인 템플릿
- 열 맵핑 표 작성(원본 열→표준 열)이다.
- 데이터 형식 캐스팅(숫자, 날짜, 텍스트)이다.
- 문자 정규화(TRIM, CLEAN, UPPER, SUBSTITUTE)이다.
- 키 생성 또는 보정(복합키 결합)이다.
- 중복 탐지 뷰 생성(고급 필터 또는 UNIQUE)이다.
- 충돌 정책 적용(최신·우선순위·병합)이다.
- 품질 점검 지표 산출(중복률, 키 누락률, 형식 오류율)이다.
10. 실무 예시: 고객 테이블 병합
10.1 보조열로 표준화
'고객명 표준 텍스트 =UPPER(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"(주)",""),"㈜","")))
'전화번호 하이픈 제거
=SUBSTITUTE(B2,"-","")
'복합키 생성: 고객ID가 없을 때
=CONCAT( [@표준고객명], "|", [@전화번호] )
10.2 동적 중복 뷰
=LET(t, A2:E10000, k, INDEX(t,,1), '키 열 dup, COUNTIF(k, k)>1, FILTER(t, dup))10.3 최신 값 병합(수식)
=BYROW(UNIQUE(A2:A10000), LAMBDA(k, LET(rows, FILTER(A2:E10000, A2:A10000=k), idx, XMATCH(MAX(INDEX(rows,,5)), INDEX(rows,,5)), TAKE(DROP(rows, idx-1), 1) )))위 예시는 키별 최신 수정일(5열)을 가진 행을 반환한다.
11. 충돌 해소: 우선순위 소스 병합
=LET(u, UNIQUE(A2:A10000), result, MAP(u, LAMBDA(k, LET(r1, FILTER(B2:E10000, A2:A10000=k), pr, XLOOKUP("Source_Priority", HeaderRow, HeaderRow), '개념적 예시 TAKE(r1,1)))), result)실제 구현에서는 파워쿼리의 조건 열 또는 사용자 지정 열로 우선순위를 점수화하고 정렬 후 첫 행을 확정한다.
12. 기간 중복 처리
동일 키에서 기간이 겹치는 레코드는 다음 규칙으로 정리한다.
- 경계 정렬: 시작일 ≤ 종료일로 강제 변환한다.
- 중복 병합: 겹치는 구간은 MIN(시작)과 MAX(종료)로 합친다.
- 구간 분할: 속성이 다르면 겹치는 구간을 분리한다.
'파워쿼리 단계 요약 정렬(키, 시작일), 인덱스 추가, 다음 행과 비교하여 시작일 ≤ 이전 종료일이면 병합 처리13. 품질 지표와 검증 체크리스트
| 지표 | 정의 | 권장 기준 |
|---|---|---|
| 중복률 | 중복 행 수 / 전체 행 수 | 5% 이하 유지 |
| 키 누락률 | NULL 키 수 / 전체 행 수 | 0% 목표 |
| 형식 오류율 | 형식 불일치 셀 수 / 전체 셀 수 | < 1% |
| 매칭 정확도 | 표본 감사에서 올바른 매칭 비율 | > 98% |
14. 대량 데이터 성능 팁
- 표 개체(CTRL+T)로 변환하여 구조적 참조를 사용한다.
- 揀선택 열만 유지하고 계산 열은 최소화한다.
- 파워쿼리에서는 형식 지정을 앞단에서 한 번만 수행한다.
- 수식 대신 파워쿼리 단계로 로직을 옮겨 새로고침 시간을 단축한다.
15. 예외 테이블과 로그 설계
자동화 파이프라인에는 항상 ‘예외 테이블’이 필요하다. 매칭 실패·다중 매칭·형식 오류 행을 별도 시트에 기록하고 검토한다.
=LET(t, A2:E10000, k, INDEX(t,,1), dup, COUNTIF(k,k)>1, IF(dup, "DUP", IF(k="", "NULL", "")))상태 열을 만들어 ‘DUP’, ‘NULL’, ‘OK’를 표시하고 필터링으로 예외만 추출한다.
16. XLOOKUP·VLOOKUP·JOIN 계열 응용
- XLOOKUP은 기본적으로 정확히 일치한다. 근접 일치는 사용할 수 있으나 병합 품질 관점에서는 키 정규화 후 정확 일치를 추천한다.
- VSTACK/HSTACK으로 파일 간 세로·가로 병합을 구성하고 UNIQUE로 중복을 제어한다.
=UNIQUE(VSTACK(TableA, TableB, TableC))17. 파워쿼리 단계 예시 스크립트(M)
// 중복 제거 및 최신값 선택 예시 let Source = Excel.CurrentWorkbook(){[Name="Table_All"]}[Content], ChangedTypes = Table.TransformColumnTypes(Source,{{"Key", type text},{"Date", type date}}), Sorted = Table.Sort(ChangedTypes,{{"Key", Order.Ascending},{"Date", Order.Descending}}), Grouped = Table.Group(Sorted, {"Key"}, {{"Top1", each Table.FirstN(_,1), type table [Key=nullable text, Date=nullable date, ...]}}), Expanded = Table.ExpandTableColumn(Grouped, "Top1", {"Date","ColA","ColB"}) in Expanded 18. VBA로 ‘고유만 새 시트로’ 복사
Sub CopyUniques() Dim rng As Range, dst As Range Set rng = Range("A1").CurrentRegion Worksheets.Add After:=ActiveSheet Set dst = ActiveSheet.Range("A1") rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=dst, Unique:=True End Sub 19. 버전별 유의사항
- Microsoft 365/2021: UNIQUE, FILTER, VSTACK, TEXTSPLIT 지원이다.
- 2016/2013/2010: 동적 배열 미지원이다. 고급 필터·파워쿼리 중심으로 구성한다.
- 파워쿼리는 2010/2013에서는 추가 기능, 2016 이후는 기본 포함이다.
20. 표준 운영 절차(SOP) 체크리스트
- 스키마 맵 작성 및 열 형식 통일을 수행한다.
- 문자 정규화와 키 생성 규칙을 적용한다.
- 파일 로드 및 병합은 파워쿼리로 자동화한다.
- 중복 탐지 뷰와 예외 테이블을 생성한다.
- 충돌 해소 정책을 적용하고 로그를 보관한다.
- 표본 감사를 통해 매칭 정확도를 측정한다.
- 전 과정 매크로 또는 쿼리로 재현 가능하게 문서화한다.
21. 자주 쓰는 수식 스니펫 모음
'공백·특수문자 제거 후 키 생성 =LET(x, UPPER(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))), y, SUBTOTAL(9, 1), x)
'다중 열 결합 키
=CONCAT( UPPER(TRIM(A2)), "|", UPPER(TRIM(B2)), "|", TEXT(C2,"yyyy-mm-dd") )
'중복 플래그
=IF(COUNTIF($A$2:$A$10000, A2)>1, "DUP", "OK")
22. 데이터 보호와 원복 전략
- 원본은 읽기 전용으로 보관하고 처리용 사본에서 작업한다.
- 중복 제거를 실행하기 전 반드시 표본 스냅샷을 남긴다.
- 파워쿼리 단계명은 의미 있게 작성하여 재현성을 높인다.
23. 흔한 오류와 해결
- 텍스트 숫자 불일치 : NUMBERVALUE 또는 값*1로 강제 변환한다.
- 날짜가 텍스트로 유지 : DATEVALUE와 지역 형식 코드로 변환한다.
- 숨은 공백 : CLEAN+TRIM 조합으로 제거한다.
- 한글+영문 혼용 : 규칙표를 만들어 SUBSTITUTE로 표준 용어를 치환한다.
24. 최종 검수 보고 양식 예시
· 총 행 수: 125,340 · 제거된 중복: 6,214 (4.96%) · 키 누락: 0 · 형식 오류: 327건 수정 완료 · 매칭 정확도(표본 n=500): 99.2% · 잔여 예외: 18건(수동 검토 예정) FAQ
중복 제거 후 일부 값이 사라졌다. 되돌리는 방법은 무엇인가?
직접 삭제 기능을 사용했다면 즉시 실행 취소를 활용하거나 백업 시트에서 다시 가져와야 한다. 차후에는 고급 필터나 파워쿼리로 비파괴적 파이프라인을 구성하는 것이 안전하다.
근접 일치 퍼지 매치를 사용할 때 안전한 유사도 임계값은 얼마인가?
데이터 특성에 따라 다르다. 일반적으로 0.8 전후에서 시작하여 표본 100~200건을 수작업 검증하며 조정하는 것이 합리적이다. 상호 교차 검증을 위해 ‘일치 후보 수’가 1을 초과하는 케이스를 예외 테이블로 분리한다.
키가 없는 데이터는 어떻게 병합하는가?
표준화한 이름·전화·주소 등 열을 결합한 복합키를 설계하고, 일치 불가 케이스는 후보군 테이블로 보류하여 수동 매핑한다. 장기적으로 시스템 발급 키를 도입한다.
동일 키에 서로 다른 주소가 있을 때 어떤 값을 남기는가?
정책을 사전에 명시한다. 최신 날짜 우선, 우선순위 소스 우선, 비어 있지 않은 값 우선 등의 규칙을 문서화하고 동일 규칙을 전 과정에 적용한다.
수식과 파워쿼리 중 무엇을 추천하는가?
반복·대용량·다중 파일 병합은 파워쿼리가 우수하다. 단발성 소규모 정리는 수식이 빠르다. 혼합 전략으로 초기 표준화는 수식, 병합·집계는 파워쿼리로 구성한다.