엑셀 데이터 정리

깨끗한 데이터가 필수인 이유

더러운 데이터는 엑셀 생산성의 조용한 킬러입니다. 불필요한 공백, 일관되지 않은 형식, 중복 레코드, 누락된 값은 수식을 망가뜨리고 피벗 테이블을 혼란스럽게 하며 잘못된 숫자에 기반한 의사 결정으로 이어집니다. 연구에 따르면 데이터 전문가들은 시간의 60-80%를 분석이 아닌 데이터 정리에 사용합니다.

좋은 소식은 엑셀에 데이터 정리를 위해 특별히 설계된 강력한 내장 도구가 있다는 것입니다. 수천 행을 수동으로 스캔할 필요가 없습니다. 이 가이드는 데이터 준비 시간을 절반으로 줄여줄 핵심 기법을 다룹니다.

분할 화면 비교: 왼쪽은 일관성 없는 날짜 형식(MM/DD/YYYY vs DD-MM-YYYY), 이름의 여분 공백, 도시/주/우편번호가 한 열에 혼합, 중복 행이 노란색으로 강조된 지저분한 데이터. 오른쪽은 정리 후 동일 데이터: 통일된 날짜, 정리된 텍스트, 분할된 주소 열, 중복 제거됨 — 그림 1 — 전후 비교: 동일한 데이터셋을 변환. 오른쪽의 정리된 데이터는 신뢰할 수 있는 수식, 정확한 피벗 테이블, 믿을 수 있는 분석을 가능하게 합니다

단계별: 실제 데이터셋 정리하기

시나리오: 레거시 시스템에서 CSV 내보내기를 받았습니다. A열에는 불필요한 공백이 포함된 이름이, B열에는 '도시, 주 우편번호'가 하나의 셀에 결합되어 있고, C열에는 혼합된 형식의 날짜가 있으며, 전체에 중복 행이 흩어져 있습니다.

1단계 — 항상 사본에서 먼저 작업하세요

시트 탭을 마우스 오른쪽 클릭 > 이동/복사 > 복사본 만들기. 사본 이름을 '정리됨'으로 지정하세요.
데이터의 유일한 버전을 정리하지 마세요. 정리 단계에서 문제가 발생해도 언제든지 원본으로 돌아갈 수 있습니다.

2단계 — 중복 행 제거하기

데이터 내 아무 셀이나 클릭하고 Ctrl+A를 눌러 전체를 선택합니다.
데이터 > 중복된 항목 제거로 이동합니다.
고유성을 정의하지 않아야 하는 열(동일한 레코드라도 다른 타임스탬프 등)의 선택을 해제합니다. 예를 들어 이름과 날짜 같은 키 열만 선택하세요.
확인을 클릭합니다. 엑셀이 제거된 중복 수와 남은 고유 행 수를 보고합니다.

각 열의 확인란이 있는 중복 제거 대화 상자: 이름, 주소, 날짜, 금액. 이름과 날짜만 선택됨. 대화 상자 아래 워크시트에는 제거될 중복 행이 노란색으로 강조 표시됩니다 — 그림 2 — 중복 제거 대화 상자. 중복을 정의할 열을 신중하게 선택하세요. 모든 열을 선택해도 중복이 거의 발견되지 않습니다

3단계 — TRIM과 CLEAN으로 텍스트 정리하기

이름 열 옆에 새 열을 삽입합니다(B열 우클릭 > 삽입). 레이블을 'Name_Clean'으로 지정하세요.
첫 번째 데이터 행에 입력: =TRIM(CLEAN(A2))
채우기 핸들을 더블 클릭하여 아래로 복사합니다. TRIM은 앞뒤 및 중복 공백을 제거합니다. CLEAN은 인쇄되지 않는 문자(시스템 내보내기에서 흔히 발생)를 제거합니다.
정리된 열을 복사하고 원본을 우클릭 > 선택하여 붙여넣기 > 값으로 수식을 깨끗한 텍스트로 대체합니다. 도우미 열을 삭제합니다.

4단계 — 텍스트 나누기로 '도시, 주 우편번호' 분할하기

결합된 주소 열을 선택합니다. 데이터 > 텍스트 나누기.
구분 기호로 분리됨을 선택하고 다음을 클릭합니다. 구분 기호로 쉼표를 선택합니다.
미리 보기에 분할 결과가 표시됩니다. 다음을 클릭합니다.
각 대상 열의 데이터 형식을 설정합니다. '도시'는 텍스트, '주 우편번호'는 텍스트로 설정합니다. 마침을 클릭합니다.
이제 '주 우편번호'를 다시 분할합니다. 선택한 후 텍스트 나누기 > 구분 기호로 분리됨 > 공백. 이제 도시, 주, 우편번호 세 개의 깨끗한 열이 생겼습니다.

Text to Columns wizard Step 2: Delimiters section with Comma checked. Data preview below shows the column split into two parts: 'San Francisco' and 'CA 94105'. The original combined column is visible in the background. — 그림 3 — 텍스트 나누기 실행. 미리 보기에서 데이터 분할 방식을 확인한 후 적용할 수 있습니다

5단계 — 날짜 표준화하기

날짜 열을 선택합니다. 데이터 > 텍스트 나누기 > 구분 기호로 분리됨 > 모든 구분 기호 선택 해제 > 다음.
'열 데이터 서식'에서 날짜를 선택하고 데이터와 일치하는 형식(MDY, DMY 등)을 선택합니다.
마침을 클릭합니다. 엑셀이 모든 날짜를 일관되고 정렬 가능한 형식으로 변환합니다.
여전히 잘못 표시되는 날짜가 있다면 균일한 서식을 적용하세요. Ctrl+1 > 표시 형식 > 날짜 > 원하는 표시 형식을 선택합니다.

핵심 기법

기법 1 — 패턴 인식을 위한 빠른 채우기

빠른 채우기(Ctrl+E)는 수동 편집을 감시하고 감지된 패턴을 기반으로 나머지를 자동 완성합니다.

전체 이름 열 옆에 첫 번째 셀의 이름을 입력합니다. Enter를 누릅니다.
두 번째 이름을 입력하기 시작합니다. 엑셀이 제안된 완성 목록의 회색 미리 보기를 표시합니다.
Ctrl+E를 눌러 수락합니다. 빠른 채우기가 모든 행에서 즉시 이름을 추출합니다. 분할, 결합, 서식 지정, 텍스트 일부 추출 등에 사용할 수 있습니다.

기법 2 — 와일드카드로 찾기 및 바꾸기

Ctrl+H를 눌러 찾기 및 바꾸기를 엽니다.
제품 코드에서 하이픈 이후의 모든 것을 제거하려면: 찾을 내용 -*, 바꿀 내용은 비워둡니다.
*는 임의 개수의 문자와 일치합니다. ?는 정확히 한 문자와 일치합니다.
바꾸기를 실행하기 전에 항상 모두 찾기를 먼저 클릭하여 일치 항목을 미리 확인하세요.

흔한 실수

원본 파일 정리. 항상 사본에서 작업하세요. 정리는 종종 되돌릴 수 없습니다. TRIM과 텍스트 나누기는 원본 데이터 형식을 파괴합니다.
분석 없이 누락된 데이터가 있는 행 삭제. 빈 셀은 쓸모없는 레코드가 아니라 데이터 수집 문제를 나타낼 수 있습니다. 삭제하기 전에 누락된 데이터가 무작위인지 체계적인지 확인하세요.
TRIM은 줄 바꿈 없는 공백(char 160)을 제거하지 않습니다. 웹 데이터에 이러한 공백이 자주 포함됩니다. TRIM 전에 =SUBSTITUTE(A2, CHAR(160), " ")를 사용하여 공백을 완전히 정리하세요.
엑셀은 숫자에서 앞자리 0을 제거합니다. 우편번호, 제품 ID, 사원 번호의 경우 가져오기 전에 열을 텍스트로 서식 지정하거나 =TEXT(A2, "00000")를 사용하여 앞자리 0을 복원하세요.

고급 팁

데이터 품질 대시보드 구축: COUNTA, COUNTBLANK 및 조건부 서식을 사용하여 각 열의 완성도(%)를 보여주는 요약을 만듭니다. COUNTIF로 데이터 유효성 검사 규칙을 추가하여 잘못된 항목을 자동으로 표시하세요.
반복 가능한 정리를 위한 파워 쿼리: 데이터 > 데이터 가져오기 > 테이블/범위에서 파워 쿼리를 엽니다. 정리 단계를 한 번만 구축하고(자르기, 분할, 필터, 바꾸기), 매주 새 파일을 넣고 새로 고침만 하면 모든 단계가 자동으로 재생됩니다.
파워 쿼리의 유사 일치: 유사 병합 옵션은 유사하지만 동일하지 않은 텍스트를 일치시킵니다. 서로 다른 시스템의 테이블을 병합할 때 'IBM Corp.'과 'International Business Machines'를 조정하는 데 완벽합니다.
빠른 중복 제거 참조를 위한 UNIQUE 및 SORT: Excel 365에서 =SORT(UNIQUE(A2:A1000))는 모든 고유 값의 알파벳순 정렬 목록을 반환합니다. 각 열에 실제로 무엇이 있는지 즉시 참조할 수 있습니다.

연습 템플릿 다운로드

이 기사에 대해 질문이 있거나 오류를 발견하셨나요? 피드백 제출