엑셀 79강] 복잡한 데이터를 요약하는 피벗테이블

 

피벗테이블은 워크시트에 입력된 많은 양의 자료를 효율적으로 분석하고 요약하는 기능입니다.

데이터를 집계하는 엑셀 기능의 종결자라고 할 수 있습니다.

 

실습 데이터 다운로드 ↓

피벗테이블.xlsx

다운로드

 

 

피벗테이블을 만들 데이터 셀을 선택하고 [삽입]-[피벗테이블] 메뉴를 선택합니다.

2013버전 이상부터 ‘추천 피벗 테이블 표’라고 하는 메뉴도 추가되었는데 데이터에 적합한 피벗테이블 모음이 제공되기 때문에 모양을 선택하는 것만으로 쉽게 피벗테이블을 만들 수 있습니다.

 

 

피벗테이블 범위가 자동으로 선택되고 피벗테이블 보고서가 작성될 위치를 새 워크시트 또는 기존 워크시트로 선택할 수 있습니다.

여기서는 새 워크시트에 피벗테이블을 만들어 보겠습니다.

 

 

 

다음 단계는 피벗테이블의 레이아웃을 설정합니다.

집계표의 레이아웃을 정하는 영역 입니다. 오른쪽 보고서에 추가할 필드 선택 작업창에 원본 데이터의 필드 이름들이 보입니다.

 

 

 

피벗테이블의 레이아웃을 정할 때 그림처럼 크로스 모양을 생각하면 쉽습니다.

피벗테이블을 또 다른 말로 크로스 집계표라고도 부릅니다.

 

그림은 작성하려고 하는 피벗테이블의 완성 모양입니다. 한눈에 지점별, 품목의 합계 금액이 정리되어 보입니다.

크로스 영역의 왼쪽이 행 레이블. 위쪽이 열 레이블, 아래쪽 값 영역이 됩니다.

 

 

 

필드 목록 작업 창에서 행 레이블 범위에 ‘거래처’ 필드를 끌어 놓습니다.

같은 방법으로 열 레이블에도 ‘품명’ 필드를 끌어 놓고 값에 ‘미수금’ 필드를 끌어 놓습니다.

 

 

 

간단하게 지점별 품목의 금액을 표시하는 집계표가 완성되었습니다.

집계금액에 블록을 지정하고 ‘천 단위 구분기호’를 표시하면 금액을 좀 더 쉽게 확인할 수 있습니다.

 

 

피벗 테이블의 장점은 완성된 후에도 집계표 모양을 다양하게 변형해서 볼 수 있다는 겁니다.

피벗 테이블 필드의 위치를 바꾸는 것만으로도 쉽게 피벗 테이블을 변형할 수 있습니다.

지점명 필드를 필터 영역으로 보내고 품명 필드를 행 레이블 쪽으로 끌어 놓습니다.

 

 

품목별 금액의 합계를 보여주는 집계표가 완성되었습니다.

 

 

지점명 필드를 필터 영역에 배치했습니다.

필터 버튼을 클릭하면 특정 지점의 판매 금액을 필터해 볼 수 있습니다.

 

이때 '여러 항목 선택' 옵션에 체크를 하면 여러 개의 지점을 선택하여 필터링 된 집계표를 확인할 수 있습니다.

 

 

 

피벗테이블에서 필드를 제거하려면 필드명에 체크를 없애거나 또는 필드를 작업창 밖으로 끌어내면 피벗테이블에서 제거가 됩니다.

 

 

이번에는 지점명 필드를 행 레이블로 다시 끌어 놓습니다.

지점별 금액을 순식간에 집계할 수 있습니다.

 

 

  

 

이번에는 월별 수량과 금액을 집계해 보겠습니다.

행 레이블에 일자 필드를, 값에 금액 필드를 끌어 놓습니다.

일자별로 금액이 표시된 집계표입니다. 일자 필드를 월별로 정리해보겠습니다.

 

 

 

 

이번에는 행 레이블에 지점명과 품명을 놓고 값 영역에 수량과 금액 필드를 배치했습니다.

각 지점별 아래로 품명이 정리되고 수량과 금액을 일목요연하게 볼 수 있도록 집계표가 만들어 집니다.

 

 

 

완성된 피벗테이블 내의 날짜를 선택합니다. [피벗 테이블 도구]-[분석]-[그룹 필드] 메뉴를 실행합니다.

 

 

 

피벗테이블에 사용된 데이터는 2015-1-7에서 2015-12-31까지의 데이터입니다.

이 데이터를 월별 또는 분기별 등의 형태로 그룹 할 수 있습니다.

단위 선택은 한 번 클릭하면 파란색으로 선택되고 한 번 더 클릭하면 선택이 해제됩니다.

월 단위가 기본값 입니다.

 

 

 

동영상 강좌도 준비했습니다.

https://youtu.be/6DJfvo9zatY

 

다음편에서는 피벗테이블의 원본 데이터가 수정되거나 내용이 추가되었을 때 데이터 새로 고침과 원본 데이터 변경에 대해서 소개합니다.

다음 강좌를 보려면 아래 URL을 클릭하면 바로 볼 수 있습니다.

http://hantip.net/84

 

짤막한 강좌 한정희 강사였습니다.