엑셀 파워쿼리, 기초부터 응용까지 총정리 가이드 - 직장인 필수!

실무에 필요한 대부분의 업무 자동화를 쉽게 구현할 수 있는 엑셀 파워쿼리의 모든 것! - 기초 이론부터 실전 예제까지

홈페이지 » 엑셀 파워쿼리, 기초부터 응용까지 총정리 가이드 - 직장인 필수!

엑셀 파워쿼리, 기초부터 응용까지 총정리 가이드

엑셀 파워쿼리 기초 - 목차 바로가기
영상 강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀고급] 초보자를 위한 파워쿼리 총정리 강의
    예제파일
  • [엑셀고급] 초보자를 위한 파워쿼리 총정리 강의
    PPT자료
  • [엑셀고급] 초보자를 위한 파워쿼리 총정리 강의
    완성파일

.

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.


.

이전 강의와 이어지는 내용이 있습니다

파워쿼리를 잘 활용하기 위한 올바른 데이터 구조에 대한 설명은아래 영상강의를 참고하세요.


엑셀 파워쿼리 지원 버전 & 설치 방법

엑셀 파워쿼리는 윈도우 버전에서만 사용가능합니다. (2021년 7월 기준, 365 버전의 Mac 엑셀에서는 일부 파워쿼리 기능이 제한적으로 제공됩니다.) 각 엑셀 버전별 파워쿼리 지원 여부는 아래 그림과 같습니다.

파워쿼리 버전 별 지원 여부
파워쿼리 버전 별 지원 여부

추가기능 설치로 제공되는 파워쿼리의 기능은 매우 제한적이므로, 되도록이면 M365 버전에서 파워쿼리 기능을 사용하시길 권장드립니다.

버전 라이선스 지원여부
2007 - 미지원
2010 Home/Personal 미지원
Professional Plus 추가기능 설치 (비공식지원)
2013 Home/Personal 추가기능 설치 (비공식지원)
Professional Plus 미지원
2016 Home/Personal 기본 내장 (신규기능 미포함)
Professional Plus 미지원
2019 - 기본 내장 (신규기능 미포함)
M365 - 모든 기능 지원

엑셀 2013 이전 버전 사용자를 위한 파워쿼리 추가기능 설치 방법은 아래 관련 포스트를 참고해주세요.

엑셀 파워쿼리, 실무에서는 어떻게 사용되나요?

파워쿼리는 '데이터 변환과 가공을 위한 도구이자 프로그램' 입니다. M365 버전을 사용 중이라면 파워쿼리의 모든 신규 기능을 사용할 수 있으며, 엑셀 뿐만 아니라 파워BI와 파워오토메이트 등 다양한 MS프로그램에서 데이터 관리 도구로 사용되고 있습니다.

많은 분깨서 파워쿼리를 '데이터 가공에 사용되는 도구'로 알고 계시지만, 사실 실무에서는 데이터 가공과 더불어 '업무 자동화'의 주축으로 사용될 수 있습니다. 특히,

  1. 회사에서 중앙 관리 프로그램을 사용 중이다.
  2. 매번 새로운 데이터를 인터넷에서 다운 받아서 누적되는 형태로 관리한다.
  3. 동일한 형태의 데이터에서 새로운 열을 추가하거나 삭제하는 작업을 매번 반복하고 있다.

위 상황이라면, 현재 겪고 있는 80% 이상의 반복 업무를 복잡한 코딩이나 외부 프로그램의 도움 없이 파워쿼리를 사용해서 마우스 클릭 만으로 손쉽게 자동화할 수 있습니다.

파워쿼리 업무 자동화
파워쿼리를 사용하면 실무 대부분의 업무를 손쉽게 자동화할 수 있습니다.

파워쿼리 사용 전 반드시 알아야 할 내용

파워쿼리의 근간이자 파워쿼리를 사용하는 핵심 목표는 'Raw Data를 효율적으로 가공하는 것' 입니다. 따라서 파워쿼리를 제대로 활용하려면 올바른 데이터 구조를 우선 이해해야 하는데요.

파워쿼리 핵심 원본 데이터
파워쿼리를 잘 사용하려면 우선 올바른 Raw Data 구조를 이해해야 합니다.

파워쿼리를 잘 활용하기 위해 알아야 할 올바른 데이터 구조의 규칙 3가지는 아래와 같습니다.

  1. 머리글은 반드시 1줄로 입력하고, 각 행을 대표하는 고유값(ID)를 가져야 한다.
  2. 병합된 셀이 없어야 한다.
  3. 집계된 데이터(합계, 소계)는 원본데이터에 포함되면 안 된다.

올바른 데이터 구조에 대한 자세한 설명은 아래 피벗테이블을 잘 활용하기 위한 핵심 규칙! - 세로방향 블록쌓기 강의에서 단계별로 자세히 설명해드렸습니다. (매우 중요한 내용이니 이전 강의를 놓치셨다면 꼭 예습하시길 권장드립니다!)

파워쿼리 실습 - 데이터 가공 & 셀병합 문제 해결

예제파일 실습을 통해 파워쿼리로 데이터를 가공하고 셀병합 문제를 해결하는 방법에 대해 단계별로 살펴보겠습니다.

  1. 범위를 표로 변경하기 : 예제파일의 영업팀-1월 시트로 이동합니다. B2:F15 범위를 선택 후, [삽입] - [표]를 선택하거나 단축키 Ctrl + T 로 표 만들기로 이동합니다. 이후 '머릿글 포함'을 체크해제 후, [확인]버튼을 눌러 범위를 표로 변환합니다.

    범위 표 변환
    범위 선택 후 삽입 - 표를 클릭하여 범위를 표로 변환합니다.

  2. 표를 선택 후, [표 디자인] - 표 스타일 옵션에서 '머릿글 행'을 체크 해제합니다. 이후 표 스타일을 없음으로 변경하면 기존 범위와 동일한 디자인으로 표가 만들어집니다.

    엑셀 표 디자인 변경
    표 스타일을 변경합니다.

  3. 파워쿼리 실행하기 : 표를 선택 후, [데이터] - [테이블 범위에서]를 클릭하면 파워쿼리 편집기가 실행됩니다. 추가기능 설치로 파워쿼리를 추가했다면, [파워쿼리] - [테이블에서] 를 클릭합니다.
    표 데이터 파워쿼리 실행
    표를 선택 - 데이터 - 테이블/범위에서를 클릭하면 파워쿼리가 실행됩니다.
    오빠두Tip : 엑셀 버전에 따라 화면에 표시되는 레이아웃이 다를 수 있습니다. 만약 [테이블/범위에서]가 보이지 않을 경우, [테이블에서]버튼을 클릭합니다.
  4. 파워쿼리 편집기가 실행되면서 기본적인 데이터 가공 단계가 추가됩니다. 추가된 단계는 파워쿼리 오른쪽 [적용된 단계]에서 확인할 수 있습니다. 각 단계에 대한 설명은 영상강의를 참고하세요.
    파워쿼리 기본 적용된 단계
    파워쿼리가 실행되면서 기본 단계가 적용됩니다.
    오빠두Tip : 엑셀 버전에 따라 기본 가공 단계가 추가되지 않을 수도 있습니다.
  5. 데이터 가공하기 : 기존 단계가 추가되었다면, 단계 왼쪽에 있는 [X] 버튼을 클릭해서 기존 단계를 모두 제거합니다.

    파워쿼리 기본 단계 지우기
    적용된 기본 단계를 모두 제거합니다.

  6. 범위의 첫번째 행은 불필요한 머리글이므로 제거합니다. [홈] - [행 제거] - [상위 행 제거]를 선택합니다. 이후 제거할 행 개수로 1을 입력 후 [확인] 버튼을 클릭하면 표의 첫번째 행이 제거됩니다.
    파워쿼리 상위 행 제거
    표의 첫번째 행 (불필요한 머리글)을 제거합니다.
    오빠두Tip : 또는 표의 좌측 상단의 [표 모양 아이콘]을 클릭 - [상위 행 제거]를 선택해도 동일한 작업이 실행됩니다.
    파워쿼리 상위 행 제거 아이콘
  7. 이후 첫 행을 머리글로 승격합니다. [홈] - [첫 행을 머리글로 사용]을 클릭하거나, 표 좌측 상단 [표 모양 아이콘]을 클릭 - [첫 행을 머리글로 사용]을 클릭하면 첫번째 행이 머리글로 승격됩니다.

    엑셀 파워쿼리 첫 행을 머리글로 사용
    표의 첫번째 행을 머리글로 승격합니다.

  8. 병합된 셀 채우기 : 범위가 표로 변환되면서 셀 병합이 해제되면, 병합된 범위의 첫번째 셀에만 데이터가 입력되고 나머지 셀은 빈칸이 반환됩니다. 따라서 셀 병합이 풀리면서 추가된 비어있는 셀을 채워줍니다. 표의 '담당부서' 열을 선택 후, [변환] - [채우기] - [아래로]를 클릭하면 데이터가 아래 방향으로 채워집니다.

    엑셀 파워쿼리 셀 병합 채우기
    셀 병합이 해제되면서 생긴 빈 셀을 모두 채워줍니다.

파워쿼리 실습 - 매출이익률/달성률 자동 계산

이제 가공된 데이터에 계산이 필요한 열을 자동으로 추가하는 방법을 알아보겠습니다.

  1. 매출이익률 추가하기 : 매출이익률은 매출이익 ÷ 실적으로 계산됩니다. 따라서 [매출이익] 열을 선택 후, [열추가] - [표준] - [나누기]를 선택합니다.

    엑셀 파워쿼리 필드 나누기
    매출이익열 선택 - 열 추가 - 표준 - 나누기로 이동합니다.

  2. 나눠줄 범위로 [열의 값에서]를 선택 후, [실적] 범위를 선택합니다. 이후 [확인] 버튼을 클릭하면 매출이익률이 계산됩니다.
    매출이익률 계산
    계산할 값으로 [실적] 열을 선택합니다.

    오빠두Tip : 만약 나눠줄 범위로 '열의 값에서'를 선택할 수 없을 경우, [열 추가] - [사용자 지정 열] 에서 아래와 같이 사용자 지정열을 추가합니다.사용자 지정 열 매출이익률 계산
  3. 달성률 추가하기 : 달성률은 실적 ÷ 목표로 계산합니다. 따라서 [실적] 열을 선택 후, [열추가] - [표준] - [나누기] - [목표] 열을 선택 후 [확인]을 누르면 달성률이 계산됩니다.

    파워쿼리 달성률 계산
    실적 열을 선택 후, 달성률 열을 계산합니다.

  4. 머리글 변경하기 : 각 머리글을 더블클릭하거나, 열 선택 후 키보드 F2키를 누르면 머리글을 변경할 수 있습니다. 각 머리글을 '매출이익률'과 '달성률'로 변경합니다.

    파워쿼리 머리글 변경
    새롭게 추가된 열의 머리글을 변경합니다.

  5. 데이터 형식 변경하기 : 방금 계산한 매출이익률과 달성률은 백분율(%) 입니다. 따라서 각 머리글 왼쪽에 있는 데이터 형식 아이콘을 클릭 후, '백분율'로 변경합니다.
    파워쿼리 데이터 형식
    추가된 열의 데이터 형식을 변경합니다.
    오빠두Tip : 만약 머리글 왼쪽에 데이터형식 아이콘이 안 보일 경우, [변환] - [데이터 형식]에서 백분율로 변경합니다.

    데이터 형식 변경

  6. 가공된 쿼리를 시트 위로 출력 : [파일] - [닫기 및 다음으로 로드]를 클릭합니다. 이후 [기존 워크시트] 를 선택 후, 출력할 위치로 H5셀을 선택합니다. 표시할 방법으로 [표]를 선택 후, [확인] 버튼을 클릭하면 가공된 쿼리가 시트 위로 출력됩니다.

    파워쿼리 시트 표 출력
    쿼리를 시트 위 표로 출력합니다.

  7. 기존에 작성된 쿼리 수정하는 방법 : 작성한 쿼리를 다시 수정하거나 시트 위에 표시된 서식을 변경하는 방법은 영상 강의에서 자세히 설명해드렸습니다.

파워쿼리 Tip - 표 열 넓이를 고정하는 방법

파워쿼리로 반환되는 표는 데이터를 새로고침 할 때마다 표 안에 입력된 값에 따라 넓이가 자동맞춤 됩니다. 만약 표의 열 넓이를 항상 고정해야 할 경우, 표를 선택 - [표 디자인] - [속성] - [열 너비 조정]을 체크 해제하면 표의 열 너비를 항상 고정할 수 있습니다.

파워쿼리 표 열 너비 고정
표의 속성을 변경하면, 쿼리로 추가된 표의 열 너비를 항상 고정할 수 있습니다.
4.9 14 투표
게시글평점
guest
26 댓글
Inline Feedbacks
모든 댓글 보기
엑셀빌런
엑셀빌런
2021년 7월 8일 8:16 오전
게시글평점 :
     

안녕하세요. 유튭에서 매번 좋은 강의 보고 있습니다. ^^ 회사업무 중에 파워쿼리를 가끔 사용하는데, 늘 같은 문제로 어려움을 겪고 있어 문의드려 봅니다. 파워쿼리 실행 후 폴더에서 불러오기 실행 Content 칼럼 외… 더보기 »

제프
제프
2021년 7월 8일 12:04 오후
게시글평점 :
     

그냥 선생님. 데이터 관련 거의 모든 프로그램, 코딩 다 하시는 듯.
정말 감사합니다. 시간이 아깝지 않아요.

비오는날커피향기
비오는날커피향기
2021년 7월 8일 2:34 오후
게시글평점 :
     

파워쿼리의 중요성은 인지하고 있었는데 오늘 강의를 통해서 정리가 되었습니다~ 진짜 감사해요!!! 배움에 감사!! 활용에 즐거움!! 직장인에게는 필수 도구네요!!!

거리
거리
2021년 7월 8일 8:03 오후
게시글평점 :
     

좋은 내용 감사드립니다.

min****
min****
2021년 7월 9일 12:34 오후
게시글평점 :
     

쿼리를 어찌접근해야할지 몰랐는데 정말 좋은강의 감사합니다.
다만 쿼리를 적용한파일을 365 웹에서
열었을때 새로고침에 문제가 있습니다.
비즈리스365 사용중입니다.
아 데스크탑에서편집 에서는 새로고침 에 문제가 없습니다

Last edited 4 월 전 by min****
SEANPAUL
SEANPAUL
2021년 7월 12일 6:04 오전
게시글평점 :
     

최고에요!!

나아두
나아두
2021년 7월 12일 10:35 오전
게시글평점 :
     

유튜브 보고 바로 가입했습니다~~

엑셀어려워
엑셀어려워
2021년 7월 16일 11:08 오전
게시글평점 :
     

파워커리에서 데이터형식이 안나오는데 어떻게 해야하나요?ㅠㅠ

jacool82@naver.com
jacool82@naver.com
2021년 7월 16일 3:56 오후
게시글평점 :
     

이 영상보고 가입하게 되었습니다. 제가 엑셀은 10%기능도 쓰지 않고 있다는걸 느끼게 하네요.

긍정적인마인드
긍정적인마인드
2021년 7월 18일 7:53 오후
게시글평점 :
     

파워쿼리 머리글 왼쪽에 아이콘이 안나오는 관계로 data taype을 누르고 Percentage로 변경하려고 하는데 안나오네요/혹시 해결 방법이 있을까요?
현재 Excel2016 사용하고 있습니다.

남성현
남성현
2021년 8월 9일 5:54 오후
게시글평점 :
     

너무나 유용한 정보 감사합니다

신나는조남석
신나는조남석
2021년 8월 9일 9:11 오후
게시글평점 :
     

좋은 강의 감사합니다.
열심히 잘 보고 있습니다.

엑셀에서 Access DB와 실시간 연동하려면 어떻게 하나요?
엑셀에서 Access DB에 입력하고 조회해서
파워쿼리로 보고서 분석해 보면 좋겠네요.

엑셀에서 Access DB와 실시간 연동 가능하죠?

hongto****
hongto****
2021년 8월 16일 4:45 오후
게시글평점 :
     

쿼리 작업을 완료하여 홈에서 닫기 및 로드 아래 화살표를 누르면 닫기 및 다음으로 로드가 비활성화 되어 있어 클릭을 할 수 없습니다. 왜 그런지 알 수 있을까요? 엑셀 버전은 MS Office… 더보기 »

문성진
문성진
2021년 8월 17일 11:06 오전
게시글평점 :
     

선생님 안녕하세요.
고급편집기의 syntax 자동완성 기능은 365버전부터 지원인걸까요?

웅이닷
웅이닷
2021년 9월 1일 2:58 오후
게시글평점 :
     

이제 저도 파워쿼리로 넘어가야할 시점이 오고있는것 같아요. 데이터양이 너무너무 많아져서 힘드네요 ㅠㅠ
강의 많은 도움 되네요. 감사합니다 ^^

아이올로스
아이올로스
2021년 9월 2일 1:20 오후
게시글평점 :
     

정말 좋은 강의 감사합니다.
유투브로 자주 보긴 하는데 이렇게 글남기는건 처음이네요~~

레체이레체
레체이레체
2021년 9월 12일 9:44 오전
게시글평점 :
     

감사합니다

Su Lee
Su Lee
2021년 10월 21일 8:57 오전
게시글평점 :
     

안녕하세요 쿼리에디터에서 customer column 을 add 하려고 하는데,customer column formula 를 작성하는곳에 제가 첫 몇개 만 넣으면, 해당하는 formula 가 자동으로 보여져서 제가 선택을 할 수 있어야 하는데 전 왜… 더보기 »

26
0
여러분의 생각을 댓글로 남겨주세요.x