들어가기 전에
정리해야 할 엄청난 양의 Excel 데이터에 압도당해 본 적이 있었나요? 데이터 분석가들은 데이터 분석을 시작하기 전에 최대 80%의 시간을 데이터 정리에 사용합니다. 데이터를 보다 효율적으로 관리하고 정리하여 더 중요한 작업에 시간을 할애할 수 있도록 도와주는 10가지 전문가 기술을 소개합니다.
※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Advanced Excel data cleaning methods professionals use
- URL: https://www.geeky-gadgets.com/excel-data-cleaning-2024/
전문가처럼 Excel 데이터를 정리하는 방법
워크시트에서 모든 서식을 제거하여 깨끗한 상태에서 시작합니다. 이 깨끗한 서식 트릭은 일관성이 없거나 산만해지는 서식이 있는 상속된 스프레드시트를 처리할 때 특히 유용합니다. 데이터 조작을 위한 새로운 캔버스를 제공하여 작업을 더 관리하기 쉽게 만듭니다. 이 기술을 적용하려면 다음을 참고합니다.
- 시트의 왼쪽 상단 모서리에 있는 삼각형을 클릭하여 전체 워크시트를 선택합니다.
- 홈 탭으로 이동하여 편집 그룹에서 지우기 버튼을 클릭합니다.
- 데이터를 보존하면서 모든 서식을 제거하려면 서식 지우기를 선택합니다.
깔끔하고 서식이 지정되지 않은 워크시트를 사용하면 일관된 서식을 적용하여 가독성과 분석을 향상시킬 수 있습니다.
집계 수식: 오류 없는 평균
오류가 있는 데이터 집합에서 평균을 계산하는 것은 어려울 수 있습니다. 집계 수식을 사용하면 존재하는 오류를 무시하면서 평균을 계산할 수 있습니다. 이렇게 하면 계산이 정확하고 실제 데이터를 반영하여 분석의 무결성을 유지할 수 있습니다. 집계 수식의 구문은 다음과 같습니다.
=AGGREGATE(function_num, options, ref1, [ref2], ...)
- function_num: 적용할 함수(예: AVERAGE의 경우 1)
- options: 오류 처리 방법(예: 오류를 무시하려면 1)
- ref1, [ref2], ...: 계산에 포함할 범위 또는 참조입니다.
집계 수식을 사용하면 데이터 집합의 오류로 인한 왜곡 없이 자신 있게 평균을 계산할 수 있습니다.
오류 강조 표시: 문제 발견
오류를 신속하게 식별하는 것은 데이터 무결성을 위해 매우 중요합니다. 오류 하이라이트를 사용하면 오류가 있는 셀을 정확히 찾아내어 쉽게 볼 수 있습니다. 이 기술을 사용하면 문제를 즉시 해결하여 데이터 집합의 신뢰성을 유지할 수 있습니다. 오류를 강조 표시하려면 다음과 같이 하세요
- 오류를 확인하려는 셀 범위를 선택합니다.
- [홈] 탭 - [스타일] 그룹 - [조건부 서식]을 선택합니다.
- [셀 강조 규칙] - [기타 규칙]을 클릭합니다.
- [새 서식 규칙] 대화상자에서 [다음을 포함된 셀만 서식 지정]을 선택합니다.
- [규칙 설명 편집] 섹션의 드롭다운 메뉴에서 [오류]를 선택합니다.
- 서식 버튼을 클릭하고 원하는 스타일에 맞는 강조 표시 스타일을 선택합니다.
- [확인]을 클릭합니다.
오류 강조 표시를 적용하면 데이터 세트의 모든 문제를 신속하게 식별하고 해결할 수 있습니다.
숫자 변환: 큰 숫자 단순화
큰 숫자는 읽고 분석하기가 번거로울 수 있습니다. 숫자 변환은 이러한 숫자를 수천, 수백만 또는 수십억 단위로 변환하여 수치를 단순화합니다. 이렇게 하면 데이터를 더 쉽게 읽을 수 있을 뿐만 아니라 더 빠르게 분석할 수 있습니다. 큰 숫자를 변환하려면 다음과 같이 하세요.
- 큰 숫자가 포함된 셀 범위를 선택합니다.
- 홈 탭으로 이동하여 숫자 그룹에서 숫자 형식 드롭다운을 클릭합니다.
- 원하는 형식을 선택합니다(예: 쉼표 구분 기호 및 소수점 이하 자릿수 0이 있는 숫자).
- 필요에 따라 형식을 조정하여 숫자를 수천, 수백만 또는 수십억 단위로 표시합니다.
큰 숫자를 단순화하면 데이터의 가독성과 해석 가능성을 높일 수 있습니다.
와일드카드 바꾸기: 텍스트 패턴 제거
텍스트 데이터를 다룰 때 특정 패턴을 제거해야 할 수도 있습니다. 와일드카드 바꾸기 기술을 사용하면 와일드카드를 사용하여 이러한 패턴을 효율적으로 식별하고 제거할 수 있습니다. 이 기능은 일관되지 않은 형식을 따르는 데이터 항목을 정리할 때 특히 유용합니다. 와일드카드 바꾸기를 사용하려면 다음과 같이 합니다.
- 제거하려는 텍스트 패턴이 포함된 셀 범위를 선택합니다.
- Ctrl + H를 눌러 찾기 및 바꾸기 대화상자를 엽니다.
- 찾기 대상 필드에 제거하려는 와일드카드 패턴을 입력합니다(예: *텍스트*).
- 다음으로 바꾸기 필드를 비워 둡니다.
- 모두 바꾸기를 클릭하여 지정된 패턴의 모든 인스턴스를 제거합니다.
와일드카드 바꾸기를 사용하면 텍스트 데이터를 효율적으로 정리하고 데이터 집합의 일관성을 유지할 수 있습니다.
텍스트 표시 수식: 날짜 결합
연도, 월, 일을 단일 날짜 형식으로 결합하면 데이터를 간소화할 수 있습니다. 텍스트 표시 수식을 사용하면 이러한 요소를 일관성 있는 날짜로 병합하여 데이터 집합의 일관성과 사용성을 향상시킬 수 있습니다. 수식 구문은 다음과 같습니다.
=TEXT(year_cell&”-“&month_cell&”-“&day_cell, “yyyy-mm-dd”)
- year_cell, month_cell, day_cell: 각 날짜 구성 요소를 포함하는 셀 참조
- “yyyy-mm-dd": 원하는 날짜 형식
텍스트 표시 수식을 사용하면 개별 날짜 구성요소를 표준화된 날짜 형식으로 쉽게 결합할 수 있습니다.
날짜 값 수식: 날짜 변환
텍스트 형식의 날짜는 데이터 분석을 방해할 수 있습니다. 날짜 값 수식은 이러한 텍스트 입력을 실제 날짜 값으로 변환하여 Excel의 날짜 함수가 날짜를 올바르게 인식할 수 있도록 합니다. 이 변환은 정확한 날짜 기반 계산 및 분석을 위해 필수적입니다. 수식 구문은 다음과 같습니다.
=DATEVALUE(text_date)
- text_date: 날짜를 나타내는 셀 참조 또는 텍스트 문자열
날짜 값 수식을 적용하면 텍스트 기반 날짜를 실제 날짜 값으로 변환하여 원활한 날짜 계산 및 비교가 가능합니다.
퍼지 매칭: 데이터 병합
유사하지만 동일하지 않은 데이터 항목이 있는 테이블을 병합하는 작업은 복잡할 수 있습니다. 퍼지 매칭(Fuzzy Matching)은 거의 일치하는 항목을 식별하고 결합하여 이 프로세스를 간소화합니다. 이 기술은 약간의 차이가 있는 여러 소스의 데이터를 통합하는 데 매우 유용합니다. 퍼지 매칭을 수행하려면 다음과 같이 합니다.
- Microsoft 웹사이트에서 퍼지 조회 애드인을 설치합니다.
- 일치시키려는 데이터를 선택하고 데이터 탭으로 이동합니다.
- 데이터 도구 그룹에서 퍼지 조회 버튼을 클릭합니다.
- 유사도 임계값 및 출력 열과 같은 일치 옵션을 구성합니다.
- 실행을 클릭하여 퍼지 매칭을 수행하고 데이터를 병합합니다.
퍼지 매칭을 사용하면 데이터 항목에 약간의 차이가 있는 경우에도 데이터를 효율적으로 통합할 수 있습니다.
파워 쿼리 - 중복 제거: 중복 제거
중복 항목은 데이터 분석을 왜곡할 수 있습니다. 파워 쿼리의 중복 제거 기능을 사용하면 이러한 중복 행을 식별하고 제거하여 데이터 집합이 고유하고 정확하게 유지되도록 할 수 있습니다. 이 기능은 중복을 수동으로 식별하는 것이 비현실적인 대규모 데이터 집합에 특히 유용합니다. 파워 쿼리를 사용하여 중복을 제거하려면 다음과 같이 합니다.
- 데이터 범위를 선택하고 [데이터] 탭으로 이동합니다.
- [데이터 가져오기 및 변환] 그룹에서 [테이블/범위에서]를 클릭하여 파워 쿼리 편집기를 엽니다.
- 중복 여부를 확인하려는 열을 선택합니다.
- [홈] 탭으로 이동하여 [행 제거]를 클릭한 다음 [중복 제거]를 선택합니다.
- 쿼리를 닫고 로드하여 데이터 집합에 변경 사항을 적용합니다.
파워 쿼리의 중복 제거 기능을 활용하면 중복 항목을 효율적으로 제거하고 데이터 무결성을 유지할 수 있습니다.
파워 쿼리 - 열 분할: 데이터 정리
구분 기호에 따라 단일 열을 여러 열로 분할하면 데이터 정리를 향상시킬 수 있습니다. 파워 쿼리의 열 분할 기능을 사용하면 데이터를 효율적으로 분할하여 분석 및 관리가 더 쉬워집니다. 이 기술은 여러 정보를 하나의 열로 결합하는 데이터 집합에 특히 유용합니다. 파워 쿼리를 사용하여 열을 분할하려면 다음과 같이 합니다.
- 데이터 범위를 선택하고 [데이터] 탭으로 이동합니다.
- [데이터 가져오기 및 변환] 그룹에서 [테이블/범위에서]를 클릭하여 파워 쿼리 편집기를 엽니다.
- 분할하려는 열을 선택합니다.
- [변환] 탭으로 이동하여 [열 분할]을 클릭한 다음 적절한 구분 기호를 선택합니다.
- 분할할 열의 수를 지정하거나 구분 기호가 발생할 때마다 분할하도록 선택합니다.
- 쿼리를 닫고 로드하여 데이터 집합에 변경 사항을 적용합니다.
파워 쿼리의 열 분할 기능을 사용하면 데이터를 효율적으로 재구성하고 분석에 대한 유용성을 높일 수 있습니다.
마치며
고급 Excel 데이터 정리 기술은 대규모 데이터 집합을 효과적으로 관리하고 분석하려는 모든 사람에게 필수적입니다. 서식을 제거하거나, 오류 없는 평균을 계산하거나, 유사한 데이터 항목을 병합할 때 이러한 기술을 사용하면 깔끔하고 신뢰할 수 있는 데이터 집합을 유지하는 데 도움이 됩니다. 이러한 기술을 익히면 시간과 노력을 절약할 수 있을 뿐만 아니라 Excel 기술을 한 단계 업그레이드하여 복잡한 데이터 문제도 자신 있게 해결할 수 있습니다.
'Excel' 카테고리의 다른 글
| Excel용 최고의 무료 프로젝트 관리 및 플래너 템플릿 8가지 (6) | 2024.10.06 |
|---|---|
| '확인란' 기능이 Excel에서 가장 과소평가된 이유와 활용법 (6) | 2024.10.05 |
| Excel 표를 Word에 삽입하는 3가지 방법 (4) | 2024.10.02 |
| 2024년 알아야 할 Excel 필수 개념 정리 (4) | 2024.10.01 |
| Excel용 무료 예산 및 재무 템플릿 10개 (16) | 2024.09.26 |
