엑셀 파워피벗, 이제는 꼭 알아야 할 피벗테이블 필수 기능 | 실전 보고서 예제

여러 시트를 동시에 비교하기 위한 데이터 관계 형성 방법 & 파워피벗을 활용해 실시간으로 여러 시트를 비교하는 실전 보고서 만들기

홈페이지 » 엑셀 파워피벗, 이제는 꼭 알아야 할 피벗테이블 필수 기능!

엑셀 파워피벗, 이제는 꼭 알아야 할 피벗테이블 필수 기능

엑셀 파워피벗 보고서 만들기 목차 바로가기
영상 강의

예제파일 다운로드

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

  • [엑셀고급] 엑셀 파워피벗 & 데이터 모델 피벗테이블 예제
    예제파일
  • [엑셀고급] 엑셀 파워피벗 & 데이터 모델 피벗테이블 예제
    완성파일

.

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

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


엑셀 파워피벗 사용 가능한 버전 (2021년 3월 기준)

현재 파워피벗 기능을 제공하는 엑셀 버전은 아래와 같습니다.

  • 마이크로소프트 365 구독 (최신버전) [Mac 제외]
  • Office 2019 모든 버전(Home, Business, Student, Professional)
  • Office 2013/2016 Professional Plus
  • Excel 2010

그 외 엑셀 버전에서는 파워피벗 추가기능을 사용할 수 없습니다.

  • Office 2016 Professional, Home&Student, Home&Business
  • Office 2013 Professional, Home&Student, Home&Business
  • Mac용 Office

현재 MS에서 파워피벗을 지원하는 엑셀 버전에 대한 보다 자세한 설명은 아래 MS 홈페이지 관련 링크를 참고하세요.

엑셀 2013/2016 Professional Plus 이후 버전에는 파워피벗이 기본으로 포함됩니다. 엑셀 2010 사용자는 아래 링크로 이동하셔서 파워피벗 추가기능을 다운받아 설치해주세요. (엑셀 2010 SP2 버전(정식 설치버전)만 사용가능합니다.)

파워피벗 추가기능 설치하기

파워피벗이 제공되는 엑셀 버전을 사용중이라면 파워피벗 추가기능을 아주 손쉽게 설치할 수 있습니다.

  1. [파일] - [옵션] - [추가기능] - [COM 추가기능] 선택 후 [이동] 버튼을 클릭합니다.

    추가기능 COM 추가기능 이동
    파일 - 옵션 - 추가기능 - COM 추가기능으로 이동합니다.

  2. Microsoft Power Pivot for Excel 을 선택한 후, [확인] 버튼을 누르면 파워피벗 추가기능 설치가 완료됩니다.
    엑셀 파워피벗 추가기능 추가
    파워피벗 추가기능을 선택 후 [확인] 버튼을 눌러 활성화합니다.
  3. 파워피벗 추가기능이 올바르게 설치되면, 엑셀 화면 오른쪽 상단에 Power Pivot 탭이 추가됩니다.

    파워피벗 추가기능 완료
    파워피벗 탭이 추가됩니다.

엑셀 데이터베이스 생성 시 주의사항 3가지

엑셀은 데이터베이스에서 파생된 스프레드시트 프로그램이지만, 데이터베이스보다 더 자유롭게 데이터를 입력하고 수정할 수 있는 반면 그로인해 발생하는 여러가지 문제가 있는데요. 우리가 실무에서 엑셀로 데이터베이스를 구축하면서 발생하는 문제는 크게 아래 3가지로 요약할 수 있습니다.

한 시트에는 하나의 표만 있어야 합니다.

한 시트안에 하나 이상의 표가 들어있으면, 필드나 값이 추가되면서 두개의 표가 서로 겹치는 상황이 발생합니다. 따라서 한 시트 안에는 반드시 하나의 표만 관리하는 것이 좋습니다.

한 시트 하나의 표
시트안에 표가 여러개 있을 경우 서로 겹치는 문제가 발생할 수 있습니다.
정규화 된 데이터를 사용합니다.

데이터 정규화에 대한 내용은 아래 영상 강의에서 자세히 다뤄드렸습니다.

표에는 고유값 필드가 반드시 포함되어야 합니다.

서로 다른 두개의 표가 관계를 형성하려면 각 레코드를 대표할 수 있는 고유값이 반드시 있어야 합니다. 관계형 데이터베이스에 대한 자세한 설명은 아래 관계형 데이터베이스란 무엇인가요? - 5분 안에 살펴보기 강의를 참고해주세요.

데이터 관계 생성하기

예제파일을 실행하면 각 시트별로 구매내역, 판매내역, 그리고 날짜와 함께 각 제품에 대한 정보인 제품정보, 구매처 정보가 입력되어 있습니다. 우선 파워피벗을 활용하여 데이터 관계를 연결해주겠습니다.

  1. [범위를 표로 변환하기] 각 시트에 입력된 범위의 아무 셀이나 선택한 후, Ctrl + A 를 누르면 범위가 한 번에 선택됩니다.
    범위가 선택된 상태에서 [삽입] - [표]를 선택하거나, 단축키 Ctrl + T를 누르면 표 만들기 대화상자가 나타납니다.
  2. '머릿글 포함'을 체크한 뒤, [확인]을 누르면 범위가 표로 변환됩니다.

    표 변경 머릿글 포함
    표 만들기 - 머릿글 포함을 체크하여 범위를 표로 변환합니다.

  3. 표를 선택한 뒤, 상단에 [표 디자인] 탭으로 이동하면 표 이름을 변경할 수 있습니다. 각각의 표 이름을 알아보기 쉬운 이름으로 변경합니다. 똑같은 과정으로 모든 시트에서 반복합니다.

    표 이름 변경
    동일한 과정을 모든 시트에서 반복합니다. 이후 표의 이름을 알아보기 쉬운 이름으로 변경합니다.

  4. [표를 데이터모델에 추가하기] 표를 선택한 뒤 [Power Pivot] 탭으로 이동합니다. [데이터 모델에 추가] 버튼을 클릭하면 파워피벗 편집기가 실행되면서 선택한 표가 데이터모델에 추가됩니다. 동일한 작업을 나머지 모든 표에서 반복합니다.

    파워피벗 편집기 데이터 모델 추가
    표를 데이터 모델에 추가합니다.

  5. 5개 표가 모두 데이터모델에 추가되었습니다.

    데이터 모델 추가 완료
    모든 표가 데이터모델에 추가되었습니다.

  6. [데이터 관계 형성하기] 파워피벗 편집기에서 [홈] - [다이어그램 보기]를 선택하면 다이어그램 보기로 변경됩니다. 관계를 보다 쉽게 연결할 수 있도록 테이블의 위치를 적절히 변경합니다.

    파워피벗 다이어그램 보기
    다이어그램 보기로 이동한 뒤, 테이블 위치를 적절히 이동합니다.

  7. 필드를 연결합니다.
    1. tbl날짜[날짜] (1) - tbl판매내역[날짜] (*)
    2. tbl날짜[날짜] (1) - tbl구매내역[날짜] (*)
    3. tbl제품[제품명] (1) - tbl판매내역[제품명] (*)
    4. tbl제품[제품명] (1) - tbl구매내역[제품명] (*)
    5. tbl공급처[공급처] (1) - tbl제품[공급처] (*)

    [오빠두Tip] 관계를 형성할 때는 고유값 필드(1)에서 외래키 필드(*)로 연결하는 것이 좋습니다.

  8. 데이터의 관계 형성이 완료되었습니다.

    파워피벗 관계형 데이터 만들기
    테이블 관계 형성이 완료되었습니다.

데이터모델 피벗테이블 만들기

관계형 데이터 만들기를 완료했으니, 이제 데이터모델 피벗테이블로 관계형 데이터에서 피벗테이블을 추가하겠습니다.

  1. [파워피벗 편집기 종료] 파워피벗 편집기에서 [파일] - [저장] 으로 저장한 뒤, [닫기]로 종료합니다.

    파워피벗 저장
    파워피벗 편집기를 저장 후 종료합니다.

  2. [데이터모델 피벗테이블 만들기] 통합문서에 아무 표를 선택한 뒤, [삽입] - [피벗테이블]을 클릭하여 피벗테이블 만들기 대화상자로 이동합니다. [데이터 모델에 이 데이터 추가]를 체크한 뒤, [확인]을 눌러 데이터모델 피벗테이블을 생성합니다.

    데이터모델 피벗테이블 만들기
    피벗테이블을 추가합니다. 데이터 모델에 이 데이터 추가를 반드시 활성화합니다.

  3. 생성된 피벗테이블을 선택한 뒤, 피벗테이블 필드 창을 보면 [모두]라는 탭이 추가되었습니다. 이전에 파워피벗 편집기에서 데이터의 관계를 형성해주었기 때문에, 연결된 모든 시트를 동시에 확인할 수 있습니다.
    피벗테이블 모든 필드 모두
    피벗테이블 필드에 [모두]가 추가되었습니다
  4. 피벗테이블에 각 필드를 추가합니다.
    행: tbl날짜[날짜(월)], tbl제품[제품명]
    값: tbl구매내역[구매수량], tbl판매내역[판매수량]

    피벗테이블 필드 추가
    각 피벗테이블 필드를 적절히 이동합니다.

  5. 단, 관계형 데이터에서 필드를 추가할 때는 반드시 '고유값' 필드를 추가하는 것에 주의합니다. 예를 들어, tbl구매내역[날짜]는 고유값이 아니므로, tbl구매내역의 날짜를 필드에 추가하고, tbl 판매내역의 판매수량을 값에 추가하면 '테이블 사이에 관계가 필요할 수 있습니다' 라는 오류메시지가 출력됩니다.

    고유값 필드 추가하는 것 주의
    데이터모델 피벗테이블에서는 '고유값 필드'를 추가하는 것에 주의해야합니다.

  6. [피벗테이블 가독성 높이기] 피벗테이블을 선택한 뒤, [디자인] - [보고서 레이아웃] - [테이블 형식으로 표시]를 선택하여 테이블 형식을 변경하면 보고서의 가독성을 높일 수 있습니다.

    테이블 보기형식 변경
    보고서 보기 형식을 테이블 형식으로 변경합니다.

실시간 필터링 - 슬라이서 추가하기

데이터모델 피벗테이블에 슬라이서를 추가하면 데이터 분석을 정말로 편리하게 할 수 있습니다. 단, 피벗테이블에 필드를 추가하는 것과 동일하게, 슬라이서도 '고유값 필드'를 추가하는 것에 주의합니다.

  1. [슬라이서 추가하기] 피벗테이블을 선택한 뒤, [삽입] - [슬라이서] 를 클릭하면 슬라이서 삽입 대화상자가 출력됩니다.

    슬라이서 만들기
    삽입 - 슬라이서로 이동한 뒤 슬라이서를 추가합니다.

  2. [모두] 탭으로 이동한 뒤, 원하는 슬라이서를 추가합니다. 이번 강의에서는 날짜, 제품명, 공급처 슬라이서를 추가하겠습니다.
    1. tbl공급처[공급처]
    2. tbl날짜[날짜(월)]
    3. tbl제품[제품명]
  3. 슬라이서가 추가되었습니다.

    슬라이서 완료
    슬라이서가 추가되었습니다.

  4. [슬라이서 꾸미기] 슬라이서를 선택한 뒤, [슬라이서] 탭 - 슬라이서 스타일에서 원하는 스타일로 슬라이서를 꾸며줍니다.

    슬라이서 꾸미기
    슬라이서를 원하는 스타이롤 꾸며줍니다.

  5. 슬라이서 버튼을 클릭하면 피벗테이블이 실시간으로 필터링됩니다.

    슬라이서 삽입 완료
    버튼을 클릭하면 피벗테이블이 실시간으로 필터링됩니다.

  6. [계산필드, 측정값 추가하기] 이 외에도 파워피벗의 계산필드를 추가하거나 측정값 기능을 활용하면 보다 다양한 데이터 분석을 진행할 수 있는데요. 이에 대한 자세한 내용은 영상강의를 참고해주세요.

    피벗테이블 측정값 추가
    측정값 과 계산필드 기능으로 보다 다양한 데이터 분석이 가능합니다.

보고서를 차트로 시각화하기

피벗테이블로 만든 보고서를 차트로 시각화하면 원하는 내용을 더욱 효과적으로 상대방에게 전달할 수 있습니다.

  1. [차트 만들기] 피벗테이블을 선택한 뒤, [삽입] - [추천차트]로 이동합니다. [가로막대형 차트]를 선택한 뒤, [확인]을 눌러 차트를 추가합니다.

    차트 만들기
    차트 만들기 - 묶은 가로막대형 차트를 추가합니다.

  2. 차트의 버튼, 제목, 범례, 눈금선, 가로축을 모두 제거하여 차트를 간소화합니다.

    가로막대형 차트 삽입
    차트의 버튼, 가로축, 눈금선, 범례를 지워 차트를 간소화합니다.

  3. 막대를 우클릭한 뒤 '데이터 레이블'을 추가합니다.

    데이터레이블 추가
    막대의 데이터레이블을 추가합니다.

  4. 차트의 색상을 적절하게 변경합니다.

    차트 시각화 완료
    차트 색상을 적절하게 변경하면 차트 시각화가 완료됩니다.

  5. 차트 시각화가 완료되었습니다. 각 상황별 차트 사용법 및 차트 시각화에 대한 보다 자세한 설명은 아래 상황별 차트 사용법 총정리 강의를 참고하세요.
5 11 투표
게시글평점
guest
20 댓글
Inline Feedbacks
모든 댓글 보기
[무쏘]시대
[무쏘]시대
2021년 3월 21일 11:32 오전
게시글평점 :
     

좋은 정보 감사합니다

SEANPAUL
SEANPAUL
2021년 3월 22일 3:31 오후
게시글평점 :
     

좋은 정보 너무 감사해요

g****
g****
2021년 3월 24일 12:01 오전
게시글평점 :
     

너무 감사해요

후잉잉
후잉잉
2021년 3월 28일 12:23 오전
게시글평점 :
     

볼수록 놀라운 엑셀기능에 감탄하며 보고있습니다.
년/월/기관별/직군별/재직인원/사직인원/사직율을 한눈에 볼 수 있는 피벗테이블을 만들고싶은데.ㅠㅠ 파워피벗을 활용해서 만들수 있을까요?
강의내용에서 항목을 바꿔서 생각해보았는데; 매칭이 될꺼 같으면서도 안되네요...

thdtj****
thdtj****
2021년 3월 28일 10:37 오후
게시글평점 :
     

감사합니다.

연찬파
연찬파
2021년 4월 19일 1:39 오후
게시글평점 :
     

안녕하세요. 좋은 정보 감사드립니다. 질문이 한가지 있는데요... 저는 파워피벗을 사용할 수 없는 버전 사용자입니다. 혹시 파워피벗을 사용할 수 있는 버전에서 작성 후, 파워피벗을 사용할 수 없는 버전에서 파일을 열어서 사용할… 더보기 »

Stan
Stan
2021년 5월 1일 3:52 오전
게시글평점 :
     

안녕하세요 저는 365 사용자인데,
COM Add-ins 에서 설치오류가 뜨는데,
"Not loaded. A runtime error occurred during the loading of the COM Add-in."
이것을 어떻게 해결하면 좋을까요? 알려 주십시오. 감사합니다.

슝슝
슝슝
2021년 7월 7일 9:33 오전
게시글평점 :
     

정말 복 받으실 거예요~^^ 좋은 하루 되세요~

선화밍
선화밍
2021년 7월 11일 2:58 오후
게시글평점 :
     

제가 가입하게 된 강의네요 ㅎㅎ 감사합니다.

모니카
모니카
2021년 7월 26일 6:21 오후
게시글평점 :
     

다이어그램을 연결하려면 다른 작업이 진행 중이므로 작업을 수행할 수 없습니다. 다른 작업이 완료될 때까지 기다린 후 다시 시도하십시오. 이런 메세지가 나옵니다. 어떻게 해야하나요

모니카
모니카
2021년 8월 5일 4:26 오후
답글 남기기  오빠두엑셀

다시하니 다이어그램연결이 잘되었습니다. 감사합니다.

엑셀 초보
엑셀 초보
2021년 10월 13일 3:33 오후
게시글평점 :
     

좋은 강의 감사합니다. 피벗테이블 생성하면 그룹/그룹해제 부분이 비활성화되어 있어 날짜가 그룹핑이 안되는데, 방법이 없을까요?

hu****
hu****
2021년 11월 2일 11:05 오전
게시글평점 :
     

슬라이서 1,2,3 이 각각 있을 때, 슬라이서 1의 항목A를 선택하면 슬라이서 2의 항목이 부분 활성화 되지 않습니다. 슬라이서의 어떤 항목을 선택해도 모든 항목이 활성화 되어있는데 해결 방안이 있을까요??

짐
2021년 11월 21일 5:50 오후
게시글평점 :
     

안녕하세요 내용 잘 보았습니다 🙂 파워피벗으로 피벗테이블 생성후에 궁금한 점이 있습니다.

보통 피벗테이블에서는 값이 없는 데이터를 표시하기가 활성화 되어 있는데 파워피벗으로 형성한 피벗테이블에서는 체크가 불가하게 나옵니다. 무엇이 문제가 있는지 궁금합니다.

화면 캡처 2021-11-21 174942.png
20
0
여러분의 생각을 댓글로 남겨주세요.x