- 최초 작성일: 2025-06-28
- 최종 수정일: 2025-06-28
- 조회수: 292 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: X0594 — 지저분한 Excel 데이터를 정리하는 7가지 방법
들어가기 전에
지저분한 Excel 데이터는 시간을 낭비할뿐만 아니라 데이터로부터 통찰력을 얻는 데 큰 장애물이 될 수 있습니다. 일관되지 않은 항목, 누락된 값 및 중복 행이 가득한 Excel 데이터로 작업하는 것은 누구에게나 고통을 줍니다.
이런 과정을 흔히 '데이터 전처리 Data preprocessing'라고 하며, 분석이나 모델링 전에 데이터를 정제하고 변환하여 품질을 높이는 과정을 말합니다. 지저분한 Excel 데이터를 정리하기 위한 7가지 효과적인 방법을 소개합니다. 언뜻 보면 지루해 보일 수 있지만 나중에 엄청난 시간을 줄일 수 있습니다.
지저분한 Excel 데이터를 정리하는 7가지 방법
Table Of Contents
1. 중복 제거하기
워크북에 중복된 데이터나 항목이 있다면 데이터를 수동으로 검토하는 대신, 내장된 도구를 사용하여 작업을 완료할 수 있습니다. 이렇게 하면 시간과 노력은 물론, '휴먼 오류'를 줄일 수 있습니다.
(1) 중복된 데이터가 있는 영역을 범위로 지정하고 [데이터] 탭 - [데이터 도구] 그룹 - [중복된 항목 제거]를 선택합니다.
(2) 중복을 제거할 열을 선택하고 [확인]을 클릭합니다.
2. 맞춤법 검사 & 바꾸기 기능 사용
다른 Microsoft 365 앱과 마찬가지로 Excel에는 성가신 오타를 즉시 수정할 수 있는 기본 제공 철자 검사기가 있습니다.
(1) Excel에서 통합 문서를 열고 [검토] 탭 - [언어 교정] 그룹 - [맞춤법 검사]를 선택합니다.
(2) 특정 단어에 오타가 있는 패턴을 발견한 경우 찾기 및 바꾸기 도구를 사용하여 이를 수정할 수 있습니다. [홈] 탭 - [편집] 그룹 - [찾기 및 검색] - [바꾸기]를 선택합니다. Ctrl + H를 눌러도 됩니다.
(3) [찾을 내용]과 [바꿀 내용]을 입력하고 [모두 바꾸기] 버튼을 클릭합니다.
3. 텍스트 나누기
Excel 통합 문서를 받았는데 한 열에 쉼표로 구분된 여러 개의 텍스트 정보가 있다고 생각해 보세요. Excel의 분석 기능이나 다른 작업을 하려면 한 열의 텍스트를 여러 열로 나누어야 합니다. 이런 경우 텍스트 나누기 기능을 사용하면 쉽게 처리할 수 있습니다.
(1) 분할하려는 열을 선택하고 [데이터] 탭 - [데이터 도구] 그룹 - [텍스트 나누기]를 선택합니다.
(2) 데이터 성격을 가장 잘 나타내는 파일 유형을 선택하고 [다음] 버튼을 클릭합니다.
(3) 현재 데이터 형태에 해당하는 구분 기호를 선택하고 [다음]을 클릭합니다.
(4) [열 데이터 서식] - [일반]을 선택하고 [마침]을 클릭합니다.
4. 불필요한 서식 제거
서식을 과도하게 사용한 Excel 통합 문서를 받는 경우가 있습니다. 색상이 많고, 정렬이 잘못되었으며, 배경 음영이 있는 통합 문서는 가독성이 낮고 완성도도 떨어져 보입니다. 일일이 수작업으로 처리하는 대신 간단하게 Excel 통합 문서에서 서식을 제거할 수 있습니다.
서식을 제거할 영역을 범위로 지정합니다. [홈] 탭 - [편집] 그룹 - [지우기] - [서식 지우기]를 선택합니다.
일반 서식이 아닌 조건부 서식이 지정되었다면 [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [규칙 지우기]에서 적절한 것을 선택합니다.
5. 데이터 유효성 검사 사용
데이터 유효성 검사는 Excel 셀에 대한 그룹 규칙을 설정하는 것과 같습니다. 올바른 종류의 데이터만 들어오도록 하고 오류나 오타를 제거합니다.
(1) 데이터 유효성 검사에서 사용할 목록을 워크시트에 작성합니다(예: 팀 정보).
(2) [데이터] 탭 - [데이터 도구] 그룹 - [데이터 유효성 검사]를 선택합니다.
(3) [제한 대상] - [목록]을 선택하고, [원본] 항목을 클릭한 다음, (1)에서 입력한 범위를 지정합니다.
(4) [확인]을 클릭합니다. 이제 열에 새 데이터를 입력할 때마다 드롭다운 목록이 나타납니다. 이 목록을 사용하여 원하는 항목을 쉽게 입력할 수 있습니다.
6. 강력한 Excel 함수 사용
Excel의 강력한 함수와 수식을 사용하여 다양한 처리가 가능합니다. 여분의 공간과 인쇄할 수 없는 문자를 정리하고 대문자를 표준화할 수도 있습니다.
- TRIM: 텍스트 문자열 내의 선행, 후행 및 추가 공백을 제거합니다.
- CLEAN: 텍스트에서 인쇄할 수 없는 문자를 제거합니다.
- PROPER: 텍스트 문자열에 있는 각 단어의 첫 글자를 대문자로 표시합니다.
- UPPER, LOWER: 텍스트를 대문자나 소문자로 변환합니다.
- LEFT, RIGHT, MID : 텍스트 문자열의 특정 부분을 추출합니다.
CONCATENATE 또는 &를 사용할 수도 있는데, 이 함수는 여러 셀의 텍스트 문자열을 결합합니다.
TEXT 함수는 값을 특정 숫자 형식의 텍스트로 변환합니다.
통합 문서에 날짜 및 시간 정보가 포함되어 있는 경우 DATE , DATEVALUE , TIME 및 TIMEVALUE 함수를 사용하여 잘못된 날짜와 시간을 수정합니다.
7. 시트 나누기
지저분한 Excel 데이터를 정리하는 또 다른 중요한 팁입니다. 단일 시트에 표, 피벗 테이블, 차트, 슬라이서, 타임라인 등을 채우는 대신 특정 목적에 따라 다른 시트를 사용하세요.
예를 들어, Excel 표로는 단일 시트를 사용하고, 피벗 테이블과 차트로는 별도의 시트를 만들며, 모든 차트에 대한 중앙 대시보드를 만들어 데이터를 더 잘 표현할 수 있습니다.
마치며
Excel은 다양한 데이터 정리 기술을 제공합니다. 깨끗하고 읽기 쉬운 데이터는 정확한 분석과 정보에 입각한 의사 결정의 기초가 됩니다. 혼란스러운 스프레드시트를 마주할 때마다 이러한 요령을 적용하여 데이터에 숨겨진 잠재력을 발견하세요.