들어가기 전에
지저분한 Excel 데이터는 시간을 낭비할뿐만 아니라 통찰력을 얻는 데 큰 장애물이 될 수 있습니다. 일관되지 않은 항목, 누락된 값 및 중복 행이 가득한 Excel 통합 문서로 작업하는 것은 고통스럽기 마련입니다. 지저분한 Excel 데이터를 정리하기 위한 7가지 검증된 방법을 소개합니다.
권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft MVP · 엑셀 솔루션 프로바이더 · 작가
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: How I clean my messy data in Excel
- URL: https://www.xda-developers.com/how-clean-messy-data-excel/
Excel 데이터 정리가 필요한 이유
Excel에서 데이터를 정리해야 하는 이유는 여러 가지가 있습니다. 대표적으로 다음과 같은 것들이 있죠.
- 분석의 품질은 전적으로 데이터의 품질에 달려 있습니다.
- 깨끗한 데이터를 통해 패턴, 추세 및 기타 숨겨진 인사이트를 발견할 수 있습니다.
- 언뜻 보면 지루해 보일 수 있지만 나중에 엄청난 시간과 좌절감을 줄일 수 있습니다.
1. 맞춤법 검사 실행
다른 Microsoft 365 앱과 마찬가지로 Excel에는 성가신 오타를 즉시 수정할 수 있는 기본 제공 철자 검사기가 있습니다.
(1) Excel에서 통합 문서를 열고 [검토] 탭 - [언어 교정] 그룹 - [맞춤법 검사]를 선택합니다.
(2) 특정 단어에 오타가 있는 패턴을 발견한 경우 찾기 및 바꾸기 도구를 사용하여 이를 수정할 수 있습니다. [홈] 탭 - [편집] 그룹 - [찾기 및 검색] - [바꾸기]를 선택합니다. 단축키인 Ctrl + H를 눌러도 됩니다.
(3) [찾을 내용]과 [바꿀 내용]을 입력하고 [모두 바꾸기] 버튼을 클릭합니다.
2. 중복 제거
워크북에 중복된 데이터나 항목이 있는 경우가 많습니다. 데이터를 수동으로 검토하는 대신, 내장된 도구를 사용하여 작업을 완료할 수 있습니다.
(1) 중복된 데이터가 있는 영역을 범위로 지정하고 [데이터] 탭 - [데이터 도구] 그룹 - [중복된 항목 제거]를 선택합니다.
(2) 중복을 제거할 열을 선택하고 [확인]을 클릭합니다.
3. 데이터 유효성 검사 사용
데이터 유효성 검사는 Excel 셀에 대한 그룹 규칙을 설정하는 것과 같습니다. 올바른 종류의 데이터만 들어오도록 하고 오류나 오타를 제거합니다. 모든 매장에 대한 재고 세부 정보를 추가한다고 가정해 보겠습니다. 데이터 검증을 사용하여 지역 항목을 정확하게 유지할 수 있습니다.
(1) 데이터 유효성 검사에서 사용할 목록을 워크시트에 작성합니다(예: 매장 정보).
(2) [데이터] 탭 - [데이터 도구] 그룹 - [데이터 유효성 검사]를 선택합니다.
(3) [제한 대상] - [목록]을 선택하고, [원본] 항목을 클릭한 다음, (1)에서 입력한 범위를 지정합니다.
(4) [확인]을 클릭합니다. 이제 열에 새 데이터를 입력할 때마다 드롭다운 메뉴가 나타납니다. 이를 사용하여 항목을 쉽게 입력할 수 있습니다.
4. 텍스트 나누기
Excel 통합 문서를 받았는데 한 열에 여러 텍스트가 있다고 생각해 보세요. Excel의 분석 기능이나 다른 작업을 하려면 한 열의 텍스트를 여러 열로 나누어야 합니다. 이런 경우 텍스트 나누기 기능을 사용하면 쉽게 처리할 수 있습니다.
(1) 분할하려는 열을 선택하고 [데이터] 탭 - [데이터 도구] 그룹 - [텍스트 나누기]를 선택합니다.
(2) 데이터 성격을 가장 잘 나타내는 파일 유형을 선택(여기서는 '구분 기호로 분리됨' 선택)하고 [다음] 버튼을 클릭합니다.
(3) 현재 데이터 형태에 해당하는 구분 기호를 선택하고 [다음]을 클릭합니다.
(4) [열 데이터 서식] - [일반]을 선택하고 [마침]을 클릭합니다.
5. 모든 서식 삭제하기
때때로 동료들은 Excel에서 서식 옵션을 과도하게 사용할 수 있습니다. 색상이 많고, 정렬이 잘못되었으며, 배경 음영이 있는 통합 문서는 엉망인 것처럼 보일 수 있습니다. 개별 열과 행을 선택하는 대신 간단한 요령으로 Excel 통합 문서에서 서식을 제거할 수 있습니다.
서식을 제거할 영역을 범위로 지정합니다. [홈] 탭 - [편집] 그룹 - [지우기] - [서식 지우기]를 선택합니다.
일반 서식이 아닌 조건부 서식이 지정되었다면 [홈] 탭 - [스타일] 그룹 - [조건부 서식] - [규칙 지우기]에서 적절한 것을 선택합니다.
6. 수식과 함수 사용
수식과 함수를 사용하여 다양한 처리가 가능합니다. 여분의 공간과 인쇄할 수 없는 문자를 정리하고 대문자를 표준화할 수도 있습니다.
- TRIM: 텍스트 문자열 내의 선행, 후행 및 추가 공백을 제거합니다.
- CLEAN: 텍스트에서 인쇄할 수 없는 문자를 제거합니다.
- PROPER: 텍스트 문자열에 있는 각 단어의 첫 글자를 대문자로 표시합니다.
- UPPER, LOWER: 텍스트를 대문자나 소문자로 변환합니다.
- LEFT, RIGHT, MID : 텍스트 문자열의 특정 부분을 추출합니다.
CONCATENATE 또는 &를 사용할 수도 있는데, 이 함수는 여러 셀의 텍스트 문자열을 결합합니다. 워크플로에 가장 적합한 Excel 함수를 알아보려면 [여기]를 참조하세요.
DOLLAR를 사용하여 숫자를 텍스트 형식으로 변환하고 더 나은 데이터 표현을 위해 통화 기호를 적용할 수도 있습니다. 마찬가지로 TEXT 함수는 값을 특정 숫자 형식의 텍스트로 변환합니다.
통합 문서에 날짜 및 시간 정보가 포함되어 있는 경우 DATE , DATEVALUE , TIME 및 TIMEVALUE 함수를 사용하여 잘못된 날짜와 시간을 수정합니다.
7. 통합 문서를 여러 개의 시트로 나누기
Excel 통합 문서에서 지저분한 데이터를 정리할 때 또 다른 중요한 부분입니다. 단일 시트에 표, 피벗 테이블, 차트, 슬라이서, 타임라인 등을 채우는 대신 특정 목적에 따라 다른 시트를 사용하세요.
예를 들어, Excel 표로는 단일 시트를 사용하고, 피벗 테이블과 차트로는 별도의 시트를 만들며, 모든 차트에 대한 중앙 대시보드를 만들어 데이터를 더 잘 표현할 수 있습니다.
마치며
Excel은 다양한 데이터 정리 기술을 제공합니다. 깨끗하고 읽기 쉬운 데이터는 정확한 분석과 정보에 입각한 의사 결정의 기초가 됩니다. 혼란스러운 스프레드시트를 마주할 때마다 이러한 요령을 적용하여 데이터에 숨겨진 잠재력을 발견하세요.
'Excel' 카테고리의 다른 글
| 전문가처럼 Excel 수식을 평가하고 디버깅하는 4가지 팁 (4) | 2025.02.20 |
|---|---|
| Excel에서 DGET 함수를 사용하는 방법 (2) | 2025.02.19 |
| 차별화 된 Excel 차트를 만들기 위한 6가지 팁 (6) | 2025.02.16 |
| 엑셀을 데이터베이스로 사용할 경우의 장단점 (5) | 2025.02.14 |
| 사람들이 잘 모르는 Excel 팁과 요령 7가지 (4) | 2025.02.12 |
