들어가기 전에
Excel 보고서를 받았는데, 어수선한 줄 간격, 맞춤법 오류, 쓸모없는 행으로 가득 차 있다면 어떻게 해야 할까요? 예전에는 오랜 시간 동안 수동으로 수정해야 했지만, 파워 쿼리(Power Query)를 사용하면 편리하게 처리할 수 있습니다. 파워 쿼리를 사용하여 엉망진창인 텍스트를 처리하고, 구조를 정리하고, 불필요한 요소를 쉽게 제거하는 방법을 소개합니다.
[참고] Excel의 파워 쿼리 편집기는 Microsoft 365용 Excel과 2016년 이후에 출시된 데스크톱 앱의 독립 실행형 버전에서 기본 도구로 사용할 수 있습니다. 파워 쿼리는 웹용 Excel 에서도 사용할 수 있지만 데스크톱 버전에 비해 제한 사항이 있습니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 4 ways I clean up messy Excel data using Power Quer
- URL: https://www.howtogeek.com/microsoft-excel-power-query-ways-clean-up-messy-data/
파워 쿼리 편집기 열기
복잡한 데이터를 정리하려면 Power Query 편집기에 데이터를 로드해야 합니다.
(1) 데이터에 머리글 행이 포함되어 있고 Excel 표 형식으로 지정 되었는지 확인한 후 표의 아무 셀이나 클릭하고 데이터 탭에서 "표/범위에서"를 클릭합니다.
(2) 파워 쿼리 편집기가 열리고 데이터가 표시됩니다.
(3) 아래 정리 단계를 모두 완료한 후 홈 탭에서 "닫기 및 로드"를 클릭하여 데이터를 일반 Excel 워크시트로 다시 전송합니다.
데이터 프로파일링을 통한 혼란 진단
새 Excel 데이터 세트를 처음 열 때 가장 먼저 하는 일은 오류 와 불일치가 정확히 어디에 숨어 있는지 찾아내는 것입니다 . 파워 쿼리의 프로파일링 도구를 사용하면 데이터 상태를 시각적으로 즉시 진단할 수 있습니다.
[참고] 기본적으로 Power Query는 상위 1,000개 행만 프로파일링하므로, 데이터에 이보다 많은 행이 포함된 경우 창 하단의 녹색 리본에서 "상위 1,000개 행을 기준으로 열 프로파일링"을 클릭하고 "전체 데이터 세트를 기준으로 열 프로파일링"을 선택하세요. 또한, 헤더에 있는 아이콘을 클릭하여 각 열에 올바른 숫자 형식이 지정되었는지 확인하세요.
(1) 파워 쿼리 편집기에서 "보기" 탭을 엽니다. "열 품질", "열 분포", "열 프로필"을 선택합니다.
열 품질: 데이터 상태 지표
열 머리글 바로 아래에 있는 막대와 백분율 통계를 통해 각 열의 데이터 품질에 대한 개요를 확인할 수 있습니다.
색상의 의미는 다음과 같습니다.
- 녹색: 모든 데이터 값이 유효합니다.
- 진한 회색 또는 검정색: 열에 비어 있거나 null 값이 포함되어 있습니다.
- 빨간색: 열에 데이터 유형 불일치 등의 오류가 있습니다.
- 빨간색과 흰색 줄무늬: 특정 오류는 종종 끊어진 데이터 연결이나 적용된 단계와 관련되어 있으며, 이로 인해 Power Query에서 데이터를 처리하는 데 문제가 발생합니다.
- 녹색과 흰색 줄무늬: 오류가 있으며, 현재로서는 나머지 데이터의 품질을 알 수 없습니다.
열 분포: 값 빈도 차트
열 품질 지표 아래의 막대 차트와 텍스트는 열에서 각 값의 빈도를 보여줍니다. 이는 불일치하는 데이터 포인트를 발견하는 데 이상적입니다. 예를 들어, 여기 "부서" 열에 고유한 값이 하나 있는 것을 분명히 알 수 있으므로 어딘가에 오타가 있을 가능성이 높습니다.
컬럼 프로필: 통계 및 분포 정보
열 머리글을 클릭하면 프로필 창에 열 내용에 대한 자세한 정보가 표시됩니다.
수평 분포 막대를 마우스 오른쪽 버튼으로 클릭하여 필터링 옵션을 엽니다. 예를 들어, 오류 값만 표시되도록 데이터를 필터링할 수 있습니다.
텍스트 값 정리, 트리밍 및 서식 지정
이제 데이터 진단이 완료되었으므로 어떤 열에 텍스트 문제가 있는지 알 수 있습니다. 대개 보이지 않는 공백이나 일관성 없는 대소문자가 문제입니다. Clean, Trim, 대소문자 도구를 사용하여 이러한 문제를 해결합니다.
데이터 정리 및 트리밍
텍스트에서 가장 흔한 문제는 인쇄할 수 없는 문자와 공백입니다. 특히 PDF, 웹사이트 또는 이전 버전의 Excel에서 데이터를 복사할 때 문제가 발생합니다.
인쇄할 수 없는 문자를 지우려면 텍스트 열의 머리글을 선택하고 변형 탭에서 서식 > 지우기를 클릭합니다.
이 도구는 캐리지 리턴과 줄 바꿈과 같이 데이터 처리를 방해하지만 보이지 않는 것들을 즉시 제거합니다.
다음으로, 같은 메뉴에서 "트리밍"을 클릭하여 불필요한 앞뒤 공백을 모두 제거하고 단어 사이의 공백은 모두 그대로 둡니다.
대문자 일관성 확보
이 예에서 Department 열에는 "Mexico", "mexico", "MEXICO"가 포함되어 있습니다. 나중에 필터링 , 그룹화 및 병합 시 이러한 항목을 동일한 국가로 간주해야 하므로 대소문자가 일관되도록 해야 합니다.
열 머리글을 우클릭하고 "변환" 위에 마우스를 올려놓고 "모든 단어를 대문자로"를 클릭합니다.
하지만 이렇게 하면 US가 Us로, UK가 Uk로 변환되므로, 변환 탭에서 "값 바꾸기"를 클릭하여 이 문제를 해결해야 합니다.
조건부 및 사용자 정의 열을 사용하여 구조 만들기
원시 데이터는 단순한 정리 이상의 것이 필요할 때가 있습니다. 기존 값을 기반으로 새로운 구조가 필요한데, 이럴 때 Power Query의 열 추가 기능이 유용합니다.
조건 열을 사용하여 IF-THEN 시나리오 만들기
정의된 임계값을 기준으로 수치형 데이터를 분류하기 위해 조건부 열을 사용하여 데이터를 더 쉽게 읽고 요약할 수 있습니다.
기존 '판매' 열을 기반으로 '판매 분류' 라는 새 열을 만들고 , 10,000달러 이상의 판매액은 "높음"으로, 그 외 모든 값은 "낮음"으로 분류한다고 가정해 보겠습니다. Power Query 편집기의 '열 추가' 탭에서 '조건부 열'을 클릭합니다.
이제 조건 열 대화 상자에서 다음을 수행합니다.
| 필드 | 입력 또는 선택 |
| 새로운 열 이름 | "Sales Classification" 입력 |
| 열 이름 | "Sales" 선택 |
| 연산자 | "보다 크거나 같음" 선택 |
| 값 | "10000" 입력 |
| 산출 | "High" 입력 |
| 기타 | "Low" 입력 |
howtogeek
M 언어를 사용하여 사용자 정의 열 만들기
'국가' 열의 값을 '지역' 이라는 새 열로 분류한다고 가정해 보겠습니다 . 구체적으로, 멕시코, 미국, 미국, 캐나다는 "아메리카"로, 영국과 영국은 "유럽"으로 분류하려고 합니다.
Power Query 편집기에서 "열 추가" 탭으로 이동하여 "사용자 지정 열"을 클릭합니다.
if [Country] = "Mexico" or [Country] = "US" or [Country] = "United States" or [Country] = "Canada" then "The Americas"
else if [Country] = "UK" or [Country] = "England" then "Europe"
else "Other"
"확인"을 클릭하면 나중에 필터링하고 그룹화할 수 있도록 지저분한 텍스트 데이터를 표준화된 그룹으로 분류하는 새 열이 표시됩니다.
새 열은 항상 데이터세트의 오른쪽 가장자리에 추가됩니다. 열을 이동하려면 열 머리글을 클릭하고 드래그하세요. 또한, 열 머리글 텍스트 왼쪽에 있는 아이콘을 클릭하여 올바른 형식이 적용되었는지 확인하는 것을 잊지 마세요.
행 제거로 어수선함 정리
마지막 주요 정리 단계는 소개 텍스트, 요약 줄, 빈 공간 등 정리된 데이터세트를 둘러싼 구조적 불필요한 요소를 모두 제거하는 것입니다. 표준 필터는 머리글 바깥의 셀에는 작동하지 않으므로, 대신 파워 쿼리의 행 제거 도구를 사용합니다.
이 방법은 매우 중요합니다. 원본 데이터가 Excel 표 형식으로 되어 있더라도, 특히 방대한 보고서를 다룰 때 인접한 행을 캡처하는 경우가 많기 때문입니다. 또한 정확한 시작점과 종료점을 직접 찾지 않아도 됩니다. 저는 이 작업을 위해 3단계 과정을 사용합니다. 상단과 하단의 불필요한 행을 제거하고, 헤더를 승격시키고, 빈 행을 제거합니다.
위쪽과 아래쪽 불필요한 내용 제거하기
먼저, 홈 탭에서 줄 줄이기 그룹의 “줄 제거” 드롭다운 메뉴를 펼칩니다.
다음 옵션을 클릭하세요.
- 상위 행 제거: 데이터세트 상단에서 지정된 개수의 행을 제거할 수 있습니다. 예를 들어, 원하는 열 머리글 위의 1행과 2행에 텍스트가 있는 경우, 2를 입력하면 해당 두 행이 제거됩니다.
- 아래쪽 행 제거: 이 도구를 사용하면 보고서 날짜나 요약과 같은 바닥글 정보가 포함된 지정된 수의 행을 제거할 수 있습니다.
이 작업을 수행하면 Power Query 편집기에서 불필요한 주변 잡음이 제거된 깔끔한 데이터 세트를 보여줍니다.
헤더 지정
상단 잡음이 제거되었으므로 열 제목이 첫 번째 행에 위치하게 됩니다. 이제 이를 제대로 인식되는 열 헤더로 지정할 준비가 되었습니다. 홈 탭에서 “첫 번째 행을 헤더로 사용”을 클릭하세요.
빈 행 제거
마지막으로, 완전히 비어 있지만 여전히 공간을 차지하고 향후 분석 프로세스를 방해할 수 있는 행을 삭제합니다. 홈 탭에서 '행 제거' > '빈 행 제거'를 클릭합니다.
마치며
엑셀의 파워 쿼리는 오류 프로파일링, 텍스트 정리, 열 추가, 원치 않는 행 제거 외에도 셀 분할, 오류 바꾸기, 데이터 피벗 해제, 위 셀을 기준으로 빈칸 채우기 등 다양한 데이터 구성 작업을 지원합니다 . 파워 쿼리는 시작이 어려울 수 있지만, 사용할 수록 이 기능이 얼마나 유용한지 알게 됩니다.
'Excel' 카테고리의 다른 글
| 매주 몇 시간씩 절약할 수 있는 잘 알려지지 않은 엑셀 기능 5가지 (0) | 2026.04.09 |
|---|---|
| 수식 작성 방식을 바꿔 놓을 Excel 기호 하나 (0) | 2026.04.07 |
| 피벗 테이블로 고생하기 전에 이 Excel 함수를 알았더라면 (0) | 2026.04.03 |
| Excel의 배열 상수에 대해 알아야 할 모든 것 (0) | 2026.04.02 |
| Excel에서 여전히 IF를 중첩 사용하고 있다면 이제 이렇게 하세요 (0) | 2026.03.31 |
