시스템에서 내려받은 데이터를 엑셀로 열면 같은 코드가 여러 번 나오거나 이름 뒤에 보이지 않는 공백이 붙어 있는 경우가 많다. 이 상태로 피벗테이블이나 SUMIFS를 돌리면 같은 거래처가 둘로 나뉘어 집계된다.
분석에 들어가기 전에 정리 단계를 거치면 이후 작업의 오류가 크게 줄어든다. 이 글에서는 중복 제거, 공백 정리, 텍스트 나누기를 순서대로 다룬다.
1. 중복값 찾아서 제거하기
가장 간단한 방법은 데이터 탭의 중복 항목 제거다. 범위를 선택하고 기준이 되는 열을 고르면 같은 값이 있는 행이 삭제된다. 원본을 건드리고 싶지 않다면 UNIQUE 함수나 조건부 서식을 쓴다.
| 방법 | 동작 | 적합한 경우 |
|---|---|---|
| 중복 항목 제거 | 데이터 탭에서 실행, 원본에서 행 삭제 | 한 번만 정리할 때 |
| UNIQUE | =UNIQUE(A2:A100), 원본 유지, 결과가 아래로 펼쳐짐 | Excel 2021, 365 사용 시 |
| 조건부 서식 중복 값 | 삭제 없이 색만 표시 | 확인만 할 때 |
2. 보이지 않는 공백 정리하기
TRIM 함수는 문자열 앞뒤의 공백을 없애고 단어 사이의 여러 공백을 하나로 줄인다. 그런데 웹이나 시스템에서 복사한 데이터에는 줄 바꿈 없는 공백이라는 특수 문자가 섞여 있을 수 있고, 이는 TRIM으로 지워지지 않는다. SUBSTITUTE로 먼저 일반 공백으로 바꿔 준다.
| 증상 | 수식 |
|---|---|
| 앞뒤 공백이 있다 | =TRIM(A2) |
| 줄 바꿈 없는 공백이 섞여 있다 | =TRIM(SUBSTITUTE(A2,CHAR(160),” “)) |
| 눈에 안 보이는 제어 문자가 있다 | =TRIM(CLEAN(A2)) |
정리한 결과는 새 열에 수식으로 만들어지므로, 값만 필요하다면 복사한 뒤 선택하여 붙여넣기에서 값을 선택해 원래 열을 덮어쓴다.
3. 텍스트 나누기로 열 분리하기
하나의 셀에 여러 정보가 들어 있으면 데이터 탭의 텍스트 나누기로 열을 나눈다. 구분 기호(쉼표, 탭, 공백, 기타)를 지정하거나 고정 너비로 나눌 수 있다.
| 원본 | 구분 기호 | 결과 |
|---|---|---|
| A001/볼펜/120 | / | A001, 볼펜, 120 세 열 |
| 서울-강남-A01 | – | 서울, 강남, A01 세 열 |
4. 권장 정리 순서
순서가 바뀌면 결과가 달라진다. 공백을 정리하기 전에 중복을 제거하면 겉보기에 다른 값이 남기 때문이다.
| 순서 | 작업 | 이유 |
|---|---|---|
| 1 | 시트 복사 | 원본 보존 |
| 2 | TRIM, SUBSTITUTE로 공백 정리 | 같은 값이 다르게 인식되는 문제 방지 |
| 3 | 텍스트 나누기 | 열 구조 확정 |
| 4 | 중복 항목 제거 | 정리된 값 기준으로 중복 판단 |
| 5 | COUNTIF로 재확인 | 남은 중복 점검 |
5. 정리 후 확인하는 방법
정리가 끝났다면 실제로 중복이 남아 있는지 숫자로 확인한다. 빈 열에 COUNTIF를 넣고 값이 2 이상인 행이 있는지 보면 된다. 필터에서 2 이상만 골라 보면 남은 중복을 한 번에 찾을 수 있다.
| 확인 항목 | 수식 | 정상 결과 |
|---|---|---|
| 코드 중복 여부 | =COUNTIF(A:A,A2) | 모든 행이 1 |
| 공백 잔존 여부 | =LEN(A2)-LEN(TRIM(A2)) | 모든 행이 0 |
| 고유 값 개수 | =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) | 기대한 코드 수와 일치 |
핵심정리
- 작업 전에 원본 시트를 복사해 둔다.
- 공백은 TRIM, 줄 바꿈 없는 공백은 SUBSTITUTE와 함께 처리한다.
- 중복은 공백 정리 후에 제거해야 정확하다.
- UNIQUE는 원본을 유지한 채 중복 없는 목록을 만든다.
다음 글부터는 실무 자동화 편으로 넘어가 피벗테이블로 월간 출고 현황을 만드는 방법을 정리한다. 막히는 부분이 있다면 댓글로 남겨 주면 예제로 다루겠다.
이 글의 표와 수식 예제에 쓰인 데이터는 설명을 위해 만든 가상 예시입니다.
