들어가기 전에
Excel의 파워 쿼리(Power Query)는 엑셀 전문가만 사용할 수 있다고 생각하는 경향이 있습니다. 파워 쿼리는 사용자 친화적으로 설계되었으며, 무엇보다도 데이터 정리 시 많은 번거로움을 덜어줍니다. 파워 쿼리를 처음 사용하는 사람들을 위한 4가지 사용 예제를 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 4 Excel Power Query Commands You Need to Know
- URL: https://www.howtogeek.com/microsoft-excel-power-query-commands-need-to-know/
1. 구분 기호로 셀을 분할하여 값 분리
Excel에는 텍스트를 열로 나누는 도구, Flash Fill, 기본 제공 함수 등 데이터를 여러 열로 나누는 다양한 방법이 있습니다 . 하지만 가장 직관적인 방법은 Power Query 편집기를 실행하여 작업을 수행하는 것입니다.
(1) 위키백과에서 파워 쿼리를 사용하여 변환하려는 표를 찾았다고 가정해 보겠습니다. [데이터] 탭 - [데이터 가져오기 및 변환] 그룹 - [데이터 가져오기] - [기타 원본에서] - [웹]을 선택합니다.
(2) 대화상자의 URL 입력란에 해당 주소를 붙여넣고 [확인]을 클릭합니다.
(3) [웹 콘텐츠 액세스] 대화상자에서 [연결]을 클릭합니다.
(4) 탐색 창 왼쪽 영역에서 변환하고자 하는 테이블을 선택하고 [데이터 변환]을 클릭합니다.
Power Query 편집기에서 테이블을 로드할 때 Career Span 열에 두 가지 데이터가 포함되어 있는 것을 확인했습니다. 각 선수가 경력을 시작한 해와 마지막으로 경기를 했던 해입니다. 이 두 데이터를 별도의 열에 넣고자 합니다.
열 머리글을 우버튼으로 클릭하고 [열 분할] - [구분 기호 기준]을 선택합니다.
Power Query 편집기는 열의 데이터를 검토하여 잠재적 구분 기호를 감지할 수 있는지 확인합니다. 이 경우, 각 셀에 대시가 포함되어 있음을 확인하고, 이 지점이 셀을 분할해야 할 지점이라고 올바르게 가정합니다. 하지만 필요한 경우 첫 번째 드롭다운 메뉴에서 다른 구분 기호를 선택할 수 있습니다.
이 예에서 선택된 열의 각 셀에 구분 기호가 하나만 있으므로 대화 상자의 옵션을 추가로 변경할 필요가 없습니다. 따라서 [확인]을 클릭합니다. 두 데이터가 별도의 열로 분할됩니다.
새 열의 이름을 바꾸려면 열 머리글을 두 번 클릭하고 새 데이터 레이블을 입력합니다(필요 시).
Power Query 편집기를 사용하여 데이터를 성공적으로 분할했습니다. 그런데 두 개의 새 열의 셀이 서로 다르게 정렬되어 있습니다. 앞의 열은 숫자인 반면, 뒷 열은 텍스트로 되어 있습니다. 이 문제를 해결하려면 해당 열 머리글의 [ABC] 아이콘을 클릭하고 [정수]를 선택합니다.
[열 형식 변경] 대화상자에서 [현재 전환 바꾸기]를 클릭합니다.
두 번째 열도 숫자 형식으로 변경되었습니다. 하지만 Error가 보이는군요. 이 문제는 이어서 해결하겠습니다.
2. 오류 바꾸기
Excel의 Power Query는 데이터에 나타나는 모든 오류를 처리하는 강력한 도구입니다. 이전 예제의 쿼리를 살펴보면, 변환한 두 번째 열의 일부 셀에 오류가 있습니다. 이는 이전에 정수로 서식이 지정된 열에 "present"라는 텍스트 값이 포함되어 있었기 때문입니다.
현재 Power Query 편집기 왼쪽 상단의 "닫기 및 로드"를 클릭하면 오류 값이 있는 셀은 결과 표에서 빈 셀로 표시됩니다. 이상적으로 보일 수 있지만, Excel 열의 빈 셀은 데이터를 정렬 및 필터링하거나 수식에서 열 머리글을 참조할 때 문제를 일으킬 수 있으므로, 의미 있는 값으로 채우는 것이 좋습니다.
(1) 열 머리글을 우클릭하고 [오류 바꾸기]를 선택합니다.
[주의] 이 때 [오류 제거]를 선택하지 않도록 주의하세요. [오류 제거]를 선택하면 오류뿐만 아니라 행 전체가 삭제됩니다.
(2) [오류 바꾸기] 대화 상자에서 오류 대신 표시할 값을 입력합니다. 이 경우, 셀에 "present"라고 표시된 부분에 오류가 표시되었으므로 현재 년도를 입력합니다.
(3) [확인]을 클릭하면 Error가 현재 년도로 대체됩니다.
가장 오른쪽에 있는 두 열의 모든 셀에는 오류 없이 동일한 데이터 유형이 모두 포함되어 있으므로 각 선수의 총 경기 연도를 계산하는 새 열을 만들 수 있습니다.
(1) [열 추가] 탭 - [사용자 지정 열]을 클릭하고, 열 이름을 "활동년수"로 지정합니다.
(2) [사용 가능한 열] 메뉴의 열 목록을 사용하여 간단한 뺄셈 수식을 작성합니다.
(3) [확인]을 클릭합니다. Career2에서 Career1이 차감된 "활동년수"가 자동으로 구해집니다.
(4) Power Query 편집기의 홈 탭에서 "닫기 및 로드" 아이콘을 클릭하여 해당 표를 Excel 통합 문서의 새 스프레드시트로 보냅니다.
3. 분석에 도움이 되는 Unpivot 데이터
Microsoft Excel에서 새로운 데이터 세트를 만들 때 가급적이면 다음과 같은 레코드-필드 원칙을 따릅니다.
- 각 행에는 관련되지만 서로 다른 데이터 유형('레코드'라고도 함 )의 컬렉션이 포함되어 있습니다.
- 각 열에는 각 레코드와 관련된 단일 고유 데이터 유형('필드'라고도 함)이 포함됩니다.
다음과 같은 간단한 예에서 각 국가는 레코드이고, 대륙, 인구, 통화는 필드입니다.
이 형식을 사용하면 데이터를 쉽게 필터링하고 정렬하고, 수식에서 열 머리글을 참조하고, 통계를 시각화할 수 있으므로 추가 분석을 위해 데이터를 준비할 수 있습니다.
위의 예시 표를 다음 스크린샷과 비교해 보세요. 각 열(필드)에는 동일한 데이터 유형이 포함되어 있습니다.
연도별로 데이터를 필터링하는 것이 불가능하고, 각 회사에서 어느 연도가 가장 수익성이 높았는지 쉽게 파악할 수 없습니다. 이러한 분석을 수행하려면 데이터 피벗 해제 (또는 평면화)가 필요합니다. 즉, 각 연도의 열을 행으로 변환하여 넓은 표의 데이터를 긴 표로 변환해야 합니다. 즉, 모든 재무 데이터를 단일 필드에 저장해야 합니다.
(1) 데이터의 아무 셀이나 선택하고 리본의 데이터 탭에서 "표/범위에서"를 클릭합니다.
데이터가 아직 Excel 표 형식으로 지정되지 않은 경우, 이를 수정하라는 대화상자가 표시됩니다. 이는 Power Query 편집기가 데이터를 더 쉽게 읽을 수 있도록 하기 위한 것입니다.
(2) Power Query 편집기에서 피벗 해제할 모든 열의 머리글을 선택합니다. 이 예시에서는 "2020" 머리글을 클릭하고 Shift 키를 누른 상태에서 "2024" 머리글을 클릭합니다.
(3) 리본의 [변환] 탭에서 "열 피벗 해제" 드롭다운 메뉴를 확장하고 "선택한 열만 피벗 해제"를 클릭합니다.
(4) 열 머리글을 두 번 클릭하여 해당 열이 나타내는 필드와 일치하도록 이름을 바꾸고, 숫자 형식 아이콘을 클릭하여 각 열에 올바른 데이터 유형이 포함되어 있는지 확인합니다.
(5) [Power Query 편집기]의 [홈] 탭에서 [닫기 및 로드]를 클릭하면 피벗되지 않은 테이블이 Excel 통합 문서의 새 스프레드시트로 전송됩니다.
그 결과, 표를 평면화하기 전에는 불가능했던 데이터 분석(예: 연도 및 회사별로 데이터를 필터링하거나 회사 이익을 내림차순으로 정렬)을 수행할 수 있습니다.
4. 위(또는 아래) 셀을 기준으로 빈 셀 채우기
앞서 언급했듯이 데이터 집합의 빈 셀은 데이터를 정렬하고 필터링하거나 열 머리글을 참조하는 수식을 사용할 때 문제가 발생할 수 있으므로 빈 셀을 채우는 것이 좋습니다.
이 예시에서는 각 팀의 첫 번째 선수에게만 A열에 팀 번호가 지정되어 있으므로, 데이터를 재배열하면 어떤 선수가 어느 팀에 있는지 파악하기 어려워집니다. 또한, 현재는 팀 번호로 데이터를 필터링할 수 없습니다.
A3부터 A5까지 셀에 숫자 1, A7부터 A9까지 셀에 숫자 2 등이 포함되어야 합니다. 이러한 숫자를 직접 입력하는 것은 시간이 많이 소요되며, 특히 이 예처럼 데이터 집합에 행이 많은 경우 더욱 그렇습니다. 대신 파워 쿼리를 사용하면 빈 셀을 몇 초 만에 채울 수 있습니다.
(1) 리본의 [데이터] 탭에서 데이터의 아무 셀이나 선택한 다음 "표/범위에서"를 클릭합니다.
(2) Power Query 편집기가 열리면 해당 열의 빈 셀에 "null"이라는 단어가 포함되어 있는 것을 확인할 수 있습니다. 이 문제를 해결하려면 열 머리글을 클릭하여 열을 선택하고 [변환] 탭에서 "채우기"를 클릭합니다.
(3) 그런 다음 데이터를 아래쪽으로 채울지 위쪽으로 채울지 선택합니다. "아래쪽"을 클릭하면 선택한 범위에서 값이 포함된 셀을 찾아 그 아래의 빈 셀에 같은 값을 채웁니다. 반면 "위쪽"을 클릭하면 값이 포함된 셀 위의 빈 셀에 값을 채웁니다. 이 경우 각 팀의 첫 번째 행에만 숫자가 있으므로 아래쪽으로 채워야 합니다.
(4) 이제 각 플레이어에게 팀 번호가 올바르게 지정되었으므로 홈 탭에서 "닫기 및 로드"를 클릭하여 쿼리를 새 워크시트로 보낼 수 있습니다.
이 빠르지만 중요한 변경 사항 덕분에 데이터 세트를 정렬하고 필터링할 수 있으며, 각 팀에 어떤 선수가 있는지 추적하는 데 지장이 없을 것이라는 확신을 가질 수 있습니다.
마치며
이상의 사례를 통해, 파워 쿼리가 엑셀 전문가들만 사용하는 도구가 아니라는 것을 알게 되셨으리라 생각합니다. 파워 쿼리는 이것 말고도 다양한 용도로 사용할 수 있습니다.
'Excel' 카테고리의 다른 글
| 더 일찍 알았으면 좋았을 10가지 Excel 수식 기호 (0) | 2025.10.03 |
|---|---|
| Python 통합을 시도하지 않았다면 Excel을 잘못 사용하고 있는 것 (0) | 2025.10.02 |
| Excel에서 휘발성 함수 재계산을 중지하는 3가지 방법 (0) | 2025.09.29 |
| 신규 콘텐츠 오픈 안내 (0) | 2025.09.27 |
| 생산성 향상을 위한 27가지 필수 Excel 단축키 (0) | 2025.09.25 |
