파워 쿼리는 4가지 작업 단계만 정확히 이해하면 어렵지 않습니다.
평소 파워 쿼리가 어려울 것이라는 선입견을 가진 수강생분들이 많이 있는데 이번 강의를 통해 선입견을 해결하고 현업에서 파워 쿼리를 쉽게 적용할 수 있도록 사례를 통해 소개하겠습니다.
실습 파일 다운로드
현재 데이터는 이벤트 응모 명단에서 당첨된 고객명과 고객 주소를 찾아 당첨 명단을 정리하려고 합니다. 이미 여러분의 머릿속에는 VLOOKUP 함수를 떠올리고 있을 텐데, 맞습니다! 하지만 찾으려는 조건이 두 개 이상이거나 입력된 데이터의 위치에 따라 VLOOKUP 함수만으로는 수식 작성이 어려울 수 있습니다.
이렇게 함수로 처리하기 까다로운 데이터를 파워 쿼리(PowerQuery)를 사용하면 아주 쉽게 해결할 수 있습니다.
파워 쿼리는 엑셀 데이터 가져오기 및 변환 도구로 다양한 데이터 원본에서 데이터를 추출하고 조작하는 데 사용됩니다.
4가지 단계 중 1단계는 연결 작업으로 데이터 가져오기 시작 단계입니다. 다양한 소스에서 데이터를 가져올 수 있는데, 예를 들어 텍스트 파일, 엑셀 파일, 데이터베이스, 웹 등에서 가져올 수 있습니다.
2단계는 변환입니다. 가져온 데이터를 정리하고 필요에 맞게 변환하는 단계죠. 데이터를 정제하고 필터링, 정렬, 열 추가/제거, 데이터 형식을 변환하는 등의 적업을 수행해 데이터를 적절한 형태로 가공합니다.
3단계는 결합입니다. 여러 데이터 소스를 결합해 하나의 데이터로 만드는 단계입니다. 데이터를 공통 열을 기준으로 결합하거나 연결해 더 풍부한 데이터를 생성합니다. 이를 통해 데이터 간의 관계를 구축하고 분석에 용이한 형태로 만들게 되는 거죠.
4단계는 로드입니다. 최종적으로 정리된 데이터를 엑셀 시트나 데이터 모델로 로드하는 단계입니다. 데이터를 활용하고 분석하기 위해 필요한 위치에 데이터를 저장합니다.
이렇게 로드한 데이터로 피벗 테이블, 피벗 차트 보고서를 작성합니다.
그럼 위에서 소개한 4단계를 거쳐 내역 시트와 펀드 시트의 테이블을 병합해 펀드명과 펀드사명이 일치하는 세부유형을 구해보겠습니다.
원본 데이터는 그대로 유지한 상태에서 새 통합 문서를 실행해 작업을 진행하겠습니다.
단축키 <Ctrl + N>을 누릅니다.
1단계: 연결
1. [데이터] - [데이터 가져오기 및 변환] - [데이터 가져오기] - [파일에서] - [Excel 통합 문서에서]를 선택합니다. 여러 개 시트의 테이블을 가져올 때 이 메뉴를 사용하면 됩니다.
2. 준비된 실습파일.xlsx 파일을 선택한 후 [가져오기]를 누릅니다.
3. [탐색 창]이 실행됩니다. 실습파일.xlsx 파일에는 내역, 이벤트당첨명단, 펀드 시트가 있는데 사용할 테이블은 내역과 펀트 시트에 있으므로 '여러 항목 선택'에 체크 표시를 하고 '내역'과 '펀드' 시트에 체크 표시를 한 후 [데이터 변환]을 누릅니다.
2단계: 변환
[Power Query 편집기]가 실행되고 왼쪽 쿼리 창에 내역과 펀드 테이블이 보입니다. 각 테이블을 선택해 각 필드의 데이터 형식이 제대로 설정되어 있는지 확인하고 빈 행/열이 있으면 제거해 줍니다.
펀드코드, 펀드명, 판매사명은 텍스트 형식으로 제대로 설정되어 있습니다. 설정액 또한 정수로 제대로 설정되어 있어 형식을 변경할 일이 없습니다.
세부유형 필드는 이후 내역과 펀드 테이블을 병합해 정확한 세부유형을 가져올 것이므로 제거하겠습니다.
1. '세부유형' 필드를 선택하고 마우스 오른쪽 버튼을 눌러 [제거]를 선택합니다.
2. '펀드' 테이블을 선택합니다. 펀드코드에서 판매사명 그리고 세부유형은 텍스트 형식으로 제대로 설정되어 있어 그대로 둡니다. 설정액도 정수로 설정되어 있습니다. 10진수로 설정된 억단위 필드의 데이터 형식 [정수]로 변경하고 [열 형식 변경] 대화 상자가 실행되면 [현재 전환 바꾸기]를 누릅니다.
3단계: 결합
이제 내역과 펀드 테이블을 병합해 보겠습니다.
1. 내역 테이블을 선택한 후 [홈] 탭 - [결합] 그룹 - [쿼리 병합] - [쿼리를 새 항목으로 병합]을 선택합니다. 그러면 새 쿼리에 병합 결과를 나타냅니다. [쿼리 병합]을 선택하면 내역 테이블에 병합 결과를 나타냅니다.
2. 내역 테이블을 선택한 상태에서 병합 메뉴를 실행했기 때문에 내역 테이블이 병합할 첫 번째 테이블로 선택되어 있습니다. 펀드 테이블을 두 번 테이블로 선택하고 각 테이블에 펀드명과 펀드사명 필드를 선택합니다. 두 개 이상의 필드를 선택할 때 <Ctrl>을 누르면 됩니다. 조인 종류와 나머지 옵션은 기본값 그대로 두고 [확인]을 누릅니다.
3. 왼쪽 [쿼리] 창을 보면 '병합1' 쿼리가 생성되었죠? '펀드' 필드에서 [확장] 버튼을 눌러 '(모든 열 선택)'에 체크 표시를 해제하고 '세부유형'에 체크 표시를 합니다. 그리고 '원래 열 이름을 접두사로 사용'에 체크 표시를 해제하고 [확인]을 누릅니다. '원래 열 이름을 접두사로 사용'에 체크 표시가 되면 필드명 앞에 테이블 명도 표시됩니다.
어려운 함수식을 작성하지 않아도 두 테이블에 일치하는 세부유형을 쉽게 구했습니다.
4단계: 로드
이제 통합 문서로 병합한 결과를 로드하겠습니다.
1. [홈] 탭 - [닫기] 그룹 - [닫기 및 로드] - [닫기 및 다음으로 로드]를 선택합니다.
3. '병합1', '펀드', '내역' 시트가 생성되고 '병합1' 시트가 원하는 결과를 표시한 테이블이 됩니다. 보기 편하도록 [테이블 디자인] 탭 - [표 스타일 옵션] 그룹에서 '줄무늬 행'에 체크 표시를 해제하고 표 스타일도 변경하면 됩니다.
이번 강의에서 소개한 파워 쿼리가 함수보다 훨씬 쉽죠?
다시 정리하면 파워 쿼리는 엑셀 통합 문서 또는 외부 데이터를 가져와 연결한 후 필요 없는 열은 제거하고 데이터 형식을 변경해 쿼리를 결합하는 과정을 거쳐 통합 문서로 로드하는 작업까지. 현업에서 VLOOKUP 함수의 한계를 느꼈다면 파워 쿼리를 활용해 보세요.
마치겠습니다.
즐거운 하루 보내세요(❁´◡`❁)
📢 회사에서 쓰는 진짜 엑셀! [된다! 7일 실무 엑셀] 구매하기
- 예스24 http://www.yes24.com/Product/Goods/76182394
- 교보문고 https://product.kyobobook.co.kr/detail/S000001817934
- 알라딘 https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=198405108
강좌가 도움이 되셨다면 구독, 좋아요, 알림 설정을 잊지마세요! 업데이트 되는 새로운 강좌 소식을 받을 수 있습니다.
그리고 구독자 분들의 응원 댓글은 더 좋은 영상을 만드는데 원동력이 됩니다.
쉽게 배워 바로 써먹는 엑셀 32강 순서대로 학습하려면 https://youtube.com/playlist?list=PLxKIudZ9zp0MO-gHV3en8oBCKyoHzU7KZ&si=2SEuO_mg486LwYlY
'파워쿼리 PowerQuery' 카테고리의 다른 글
| 데이터 전처리가 쉬워지는 파워 쿼리 | 파워 쿼리만 잘 써도 업무가 쉬워진다 (0) | 2024.04.29 |
|---|---|
| 엑셀 파워 쿼리] 열 피벗 해제 | 피벗 테이블 작성이 안 되는 데이터가 있어요~ 해결 방법을 알려드립니다 (0) | 2023.02.18 |
| 엑셀 파워 쿼리] 두 시트를 비교해 일치 or 불일치 데이터 추출 | 칼퇴를 부르는 파워 쿼리 (0) | 2022.11.17 |
| 엑셀 파워 쿼리] 실무 예제를 사용해 중복 데이터 추출하기 (0) | 2022.11.16 |
| 엑셀 파워 쿼리] 두 개 이상의 조건에 일치하는 값을 찾을 때 VLOOKUP(×) 파워 쿼리(○) | 너무 쉽게 원하는 결과를 얻을 수 있습니다! 강추👍 (0) | 2022.11.10 |