들어가기 전에
수많은 행과 열, 피벗 테이블을 처리하다 보면 완벽하지 못한 데이터 세트가 생성될 수 있습니다. 데이터가 방대할수록 불일치, 오류, 누락된 값, 중복 및 불필요한 서식 등으로 인해 분석을 방해할 가능성이 높아집니다. 혼란스러운 데이터를 잘 정리되고 분석에 적합한 형태로 변환하는 데이터를 정리하는 방법을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 7 simple tips to clean up your datasets in Excel
- URL: https://www.xda-developers.com/simple-tips-clean-datasets-excel/
1. 불필요한 공백 제거하기
이것은 Excel에서 데이터베이스를 만든 후 해야 할 첫 번째 일입니다. 추가 공백은 무해해 보이지만 우리가 깨닫지 못하는 방식으로 불편함을 더합니다. 이러한 추가 공백은 분석을 왜곡하고, 정렬 및 필터링을 엉망으로 만들고, 심지어 불일치와 오류를 발생시킵니다.
찾기 및 바꾸기 도구 사용
찾기 및 바꾸기 도구는 Excel에서 불필요한 공백을 제거하는 가장 쉬운 방법 중 하나입니다.
- 작업 대상 통합 문서를 엽니다.
- Ctrl + H(Mac에서는 Control + H) 키를 눌러 찾기 및 바꾸기 대화 상자를 엽니다.
- [찾을 내용] 입력란을 클릭하고 스페이스 키를 두 번 누릅니다.
- [바꿀 내용] 입력란을 클릭하고 스페이스 키를 한 번 누릅니다.
- [모두 바꾸기] 버튼을 클릭합니다.
이것은 실수로 두 개의 공백을 입력한 경우, 하나의 공백으로 바꾸는 과정입니다.
TRIM 함수 사용
찾기 및 바꾸기 도구를 사용하고 싶지 않으면 Excel의 TRIM 함수를 사용할 수도 있습니다.
(1) 불필요한 공백이 있는 열 옆에 새 열을 만듭니다.
(2) 새 열의 첫 번째 셀에 TRIM 수식을 입력합니다. 예를 들어, 원래 데이터가 열 A에 있고 새 열 B를 추가한 경우 셀 B1에 =TRIM(A1)을 입력합니다.
(3) B1에 정리된 결과가 표시됩니다. 셀을 선택하고 오른쪽 하단 모서리로 이동합니다.
(4) 채우기 핸들을 클릭하고 열 아래로 끌어서 데이터의 마지막 행에 도달할 때까지 드래그합니다. 그러면 TRIM 공식이 열 B의 모든 셀에 복사됩니다.
(5) B 열의 모든 셀을 선택하고 Ctrl + C를 눌러 데이터를 복사합니다.
(6) A 열에 있는 값과 같은 값을 붙여넣습니다.
(7) B 열을 삭제합니다.
2. 모든 빈 셀 제거하기
Excel의 빈 셀은 까다로울 수 있습니다. 때로는 오류를 발생시키거나 분석을 방해할 수 있습니다. 빈 셀을 시각적으로 처리할 수 있습니다. 하지만 대용량 데이터 세트를 다루는 경우 아래 단계에 따라 처리하세요.
(1) Excel 통합 문서를 열고 범위를 선택한 다음, Ctrl + G 키를 누릅니다.
(2) [이동] 대화상자에서 [옵션]을 클릭합니다.
(3) [이동 옵션] 대화상자에서 [빈 셀] 항목을 선택하고 [확인]을 클릭합니다.
(4) 지정한 영역에서 빈 셀이 모두 선택됩니다. 전체 행이나 열을 제거하거나, 특정 값으로 대체하거나, 인접한 값으로 채워넣는 등 어떻게 처리할 지는 여러분에게 달려 있습니다.
3. 맞춤법 검사 실행
다른 사람과 Excel 통합 문서를 공유하기 전에 맞춤법 검사를 한 번 실행하여 당혹스러운 오류가 발생하지 않도록 하세요.
(1) Excel 통합 문서를 열고 [검토] 탭 - [언어 교정] 그룹 - [맞춤법 검사]를 선택합니다.
(2) 제안 사항을 살펴보고 필요한 버튼을 클릭합니다.
자주 사용하지만 Excel 사전에 없는 단어가 있는 경우 사전에 추가를 클릭하면 앞으로 플래그가 지정되는 것을 방지할 수 있습니다.
4. 중복 제거하기
이것은 Microsoft Excel에서 데이터 세트를 단순화하는 또 다른 편리한 방법입니다. 이 도구는 Excel 기본 기능으로 내장되어 있습니다.
(1) [데이터] 탭 - [데이터 도구] 그룹 - [중복된 항목 제거]를 선택합니다.
(2) 중복을 제거할 열을 선택하고 [확인]을 클릭합니다.
(3) 제거된 중복 값의 수를 표시하는 메시지 상자가 표시됩니다.
5. 서식 지우기
일반 서식 지우기
Excel 통합 문서에서 서식을 지워야 하는 경우가 있습니다. 색상, 강조 표시 또는 정렬과 같은 일반 서식인 경우 셀을 선택하고 [홈] 탭 - [편집] 그룹 - [지우기] - [서식 지우기]를 선택합니다.
조건부 서식 지우기
조건부 서식인 경우 아래 단계를 따릅니다.
(1) [홈] - [스타일] 그룹 - [조건부 서식]을 선택합니다.
(2) [규칙 지우기] 아래에서 원하는 명령을 선택합니다.
6. 대소문자 변경
한글로 된 통합 문서를 사용하는 경우라면 크게 필요성을 느끼지 못할 수도 있습니다만, 영문과 관련된 데이터를 다루는 경우 참고할 만 합니다. Excel 시트의 데이터를 문자 케이스에 따라 데이터를 처리해야 한다면 다양한 함수를 사용하여 변경하세요.
=UPPER(셀 주소): 대문자로 변경
=LOWER(셀 주소): 소문자로 변경
=PROPER(셀 주소): 문장 대소문자 변환용(단어의 첫 글자를 대문자로 변경)
셀을 끌어서 맨 아래까지 확장하면 열의 모든 셀에 동일한 내용이 적용됩니다.
7. 텍스트 나누기
Excel 통합 문서에 고객 정보, 기기 모델 번호, 주소 및 기타 정보가 공백, 쉼표 또는 세미콜론으로 구분되어 있는 경우 여러 열에 있는 텍스트를 구분할 수 있습니다.
(1) 텍스트를 구분할 열을 선택합니다.
(2) 데이터를 선택하고 [데이터] 탭 - [데이터 도구] 그룹 - [텍스트 나누기]를 선택합니다.
(3) 데이터 성격에 따라 [구분 기호로 분리됨]이나 [너비가 일정함] 중에서 선택하고 [다음]을 클릭합니다. 보통은 Excel이 알아서 판단합니다.
(4) 텍스트를 구분할 구분 기호를 선택하고 [다음]을 클릭합니다.
(5) 데이터가 제대로 구분이 되었는지 확인하고 [마침] 버튼을 클릭합니다.
8. 텍스트 결합하기
여러 텍스트 문자열을 하나의 셀로 결합해야 하는 경우도 있습니다. 여러 가지 방법이 있지만 여기서는 CONCAT 함수를 사용하여 해결합니다.
(1) 새 열을 만들고 첫 번째 셀을 선택합니다.
(2) =CONCAT(셀 참조) 수식을 작성합니다. 예를 들어 =CONCAT(A3,B3)라고 입력합니다.
(3) Enter를 누르면 지정한 셀의 내용이 연결된 결과가 표시됩니다.. 셀을 아래로 끌어서 놓아 전체 열에 동일하게 적용합니다.
마치며
Excel에서 일관되지 않은 데이터를 처리하는 것은 좋은 생각이 아닙니다. 위에서 소개한 방법을 사용하면 Excel에서 데이터 정리의 기술을 쉽게 익힐 수 있습니다. 데이터 분석과 관련하여 Microsoft Copilot을 사용하여 지루하고 수동적인 작업을 제거할 수도 있습니다. 이와 관련해서는 [여기]를 참고하세요.
'Excel' 카테고리의 다른 글
| 탁월한 프로젝트 관리를 위한 Excel 활용 아이디어 7가지 (10) | 2024.12.18 |
|---|---|
| 교육 관련 최고의 Excel 템플릿 10가지 (32) | 2024.12.17 |
| SEQUENCE 함수 활용 ㅡ 업무배정표, 회의스케쥴, 사번생성기 만들기 (33) | 2024.12.16 |
| 완벽한 Excel 인쇄물을 얻기 위한 8가지 필수 설정 (31) | 2024.12.16 |
| Excel LAMBDA를 사용하여 사용자 정의 함수 만드는 법 (31) | 2024.12.15 |
