Microsoft Excel에서 데이터를 효과적으로 정리하는 방법

들어가기 전에

데이터 정리는 데이터 분석의 필수 단계입니다. 부정확하거나 일관성이 없는 데이터는 잘못된 결론과 의사결정으로 이어질 수 있기 때문이죠. Excel은 효과적인 데이터 정리를 촉진하는 다양한 기능을 제공합니다. Excel에서 데이터를 정리하여 데이터 세트의 정확성과 신뢰성을 높일 수 있는 방법을 소개합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

이미지: 아이엑셀러 닷컴

 

※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

---

• 원문: How to clean data effectively in Microsoft Excel
• URL: https://www.geeky-gadgets.com/cleaning-data-in-excel/

---

개요: 데이터 정리의 중요성

데이터 정리에는 데이터 세트 내의 오류, 불일치 및 부정확성을 식별하고 수정하는 작업이 포함됩니다. 이 프로세스는 데이터 품질을 향상시키고 후속 분석에서 의미 있고 유효한 결과를 얻을 수 있도록 보장합니다. 데이터 정리 중에 해결되는 일반적인 문제는 다음과 같습니다.

• 누락된 값
• 중복
• 일관되지 않은 형식
• 특이치(Outliers)
• 잘못된 데이터 유형

 

Excel에서 효과적인 데이터 정리 단계

초기 데이터 검토 데이터세트를 검토하여 구조와 내용을 이해하는 것부터 시작하세요. 존재하는 데이터 유형을 숙지하고 명백한 문제를 식별하세요. 스크롤하는 동안 머리글을 표시하는 '틀 고정' 같은 Excel의 기본 제공 기능을 사용하면 대규모 데이터 세트를 더 쉽게 탐색할 수 있습니다.
 

• 중복 항목 제거: 중복 항목으로 인해 분석 결과가 왜곡될 수 있습니다. Excel에서는 중복 항목을 제거하는 간단한 방법을 제공합니다.
  
  • 데이터 범위 또는 전체 시트를 선택합니다.
  • [데이터] 탭 - [데이터 도구] 그룹 - [중복된 항목 제거]를 선택합니다.
  • 중복을 확인할 열을 선택하고 [확인]을 클릭합니다.
• 누락된 값 처리: 누락된 데이터는 분석 및 모델링을 방해할 수 있습니다. 누락된 값을 해결하는 몇 가지 전략이 있습니다.
  
  • 삭제: 누락된 값이 최소이고 중요하지 않은 경우 행이나 열을 제거합니다.
    
    • 행/열을 우클릭하고 삭제합니다.
  • 대치: 누락된 값을 평균, 중앙값 또는 모드와 같은 통계 측정값으로 바꿉니다.
    
    • =IF(ISBLANK(A2), MEAN(A:A), A2): 공백을 열 평균으로 바꿉니다.
  • 예측: 예측 모델을 사용하여 누락된 값을 추정합니다. 이 방법은 더 고급 기능이며 Excel 이상의 도구가 필요할 수 있습니다.
• 데이터 유형 수정: 데이터 유형이 열 간에 일관성이 있는지 확인합니다.
• 텍스트를 숫자나 날짜로 변환하려면 텍스트를 열로 변환을 사용합니다.
  
  • 열을 선택하고 [데이터] 탭 - [테이터 도구] 그룹 - [텍스트 나누기]를 선택한 다음, 마법사의 안내를 따릅니다.
  • 열을 선택하고 홈 탭에서 형식(숫자, 날짜, 텍스트 등)을 선택하여 적절한 서식을 적용합니다.
• 데이터 형식 표준화 정확한 분석을 위해서는 일관된 형식이 중요합니다.
  
  • 텍스트 케이스: UPPER, LOWER, PROPER 같은 함수를 사용하여 텍스트 대소문자를 표준화합니다.
    
    • 예: =UPPER(A2)텍스트를 대문자로 변환합니다.
  • 날짜: 모든 날짜가 표준 형식을 따르는지 확인하세요.
    
    • 일관되게 날짜 형식을 지정하는 데 사용합니다: (예) =TEXT(A2, "YYYY-MM-DD")
  • 숫자: SUBSTITUTE나 CLEAN 함수를 사용하여 숫자에서 불필요한 문자를 제거합니다.

• 이상값 처리이상값은 분석 결과에 큰 영향을 미칠 수 있습니다. 이상값을 식별하고 관리하세요.
  
  • 평균 및 표준 편차와 같은 통계적 측정값을 사용하여 이상값을 감지합니다.
    
    • 예시: 평균 =AVERAGE(A:A) 및 표준 편차 =STDEV(A:A)를 계산한 다음 조건부 서식을 사용하여 이상값에 플래그를 지정합니다.
  • 컨텍스트와 분석에 미칠 수 있는 잠재적 영향에 따라 이상값을 제거하거나 조정합니다.
• 데이터 정리를 위한 Excel 함수 사용Excel은 데이터 정리를 용이하게 하는 몇 가지 함수를 제공합니다.
  
  • TRIM(): 텍스트에서 여분의 공백을 제거합니다(예: =TRIM(A2)).
  • SUBSTITUTE(): 텍스트 문자열의 특정 문자를 바꿉니다(예: =SUBSTITUTE(A2, “-”, “”)).
  • CLEAN(): 인쇄할 수 없는 문자를 제거합니다(예: =CLEAN(A2)).
• 조건부 서식 적용: 조건부 서식은 불일치 또는 오류를 시각화하고 식별하는 데 도움이 됩니다.
  
  • 중복, 이상값 또는 특정 데이터 포인트를 강조 표시합니다.
  • 범위를 선택하고 [홈] - [조건부 서식]으로 이동한 다음 원하는 규칙(예: 셀 규칙 강조 표시, 상단/하단 규칙)을 선택합니다.
• 데이터 유효성 검사: 데이터 유효성 검사는 입력할 수 있는 데이터 유형을 제한하여 데이터 무결성을 보장합니다.
  
  • 범위를 선택하고 [데이터] - [데이터 유효성 검사]로 이동합니다.
  • 허용되는 데이터에 대한 기준을 설정합니다(예: 정수, 날짜, 목록).
  • 사용자 지정 오류 메시지를 추가하여 사용자를 안내합니다.
• 파워 쿼리 사용파워 쿼리는 고급 데이터 정리를 위한 Excel의 강력한 도구입니다.
  
  • [데이터] - [데이터 가져오기 및 변환]을 통해 파워 쿼리에 액세스합니다.
  • 다양한 소스에서 데이터를 가져와서 변환(예: 중복 제거, 누락된 값 채우기)을 적용할 수 있습니다.
  • 파워 쿼리 편집기를 사용하여 데이터를 필터링, 정렬, 정리한 후 다시 Excel로 로드합니다.
• 매크로를 사용한 자동화반복적인 청소 작업의 경우 매크로를 사용해 프로세스를 자동화하는 것이 좋습니다.
  
  • [보기] - [매크로] -> [매크로 기록]으로 이동하여 매크로를 기록합니다.
  • 데이터 정리 단계를 수행한 다음 녹화를 중지합니다.
  • 필요에 따라 매크로를 실행해 새 데이터에 동일한 정리 단계를 적용합니다.
• 문서화 및 버전 관리투명성과 재현성을 보장하기 위해 데이터 정리 프로세스를 문서화하세요.
  
  • 날짜, 시간, 각 변경 사유를 포함한 변경 로그를 유지합니다.
  • 필요한 경우 역추적이 가능하도록 다양한 정리 단계에서 데이터 세트의 버전을 저장합니다.

 

Excel에서 데이터 정리를 위한 모범 사례

 

• 데이터 백업: 실수로 데이터가 손실되지 않도록 항상 데이터 집합의 사본을 가지고 작업하세요.
• 점진적으로 작업하기: 단계적으로 데이터를 정리하고 각 단계마다 결과를 확인하여 정확성을 확보하세요.
• 일관성 유지: 일관성을 유지하기 위해 유사한 데이터 세트에 동일한 정리 규칙을 일관되게 적용합니다.
• 정기적으로 검증하기: 주기적으로 데이터를 검증하여 분석 프로세스 전반에 걸쳐 데이터가 깨끗하고 정확한 상태로 유지되도록 합니다.
• 사용 가능한 도구 사용: Excel의 기본 제공 도구와 파워 쿼리 및 매크로와 같은 추가 기능을 활용하여 정리 프로세스를 간소화하세요.

 

마치며

Microsoft Excel에서 효과적인 데이터 정리는 고품질의 신뢰할 수 있는 데이터 집합을 확보하는 데 매우 중요합니다. 중복 제거부터 매크로를 사용한 작업 자동화에 이르기까지 이 문서에서 설명된 단계를 따르면 데이터의 정확성과 일관성을 크게 향상시킬 수 있습니다. 이러한 기술을 사용하면 분석의 무결성이 향상될 뿐만 아니라 장기적으로 시간과 노력을 절약할 수 있습니다. 모범 사례를 준수하고 Excel의 강력한 기능을 활용하면 모든 분석 작업에 대해 깔끔하고 실행 가능한 데이터를 유지하는 데 도움이 됩니다.

[최고] 가성비로 [최신] Office 활용 정보를 보내드립니다.