0
7장
피벗 테이블과 차트 보고서
학습목표
•
피벗 테이블의 정의와 특징을 알아 본다.•
피벗 테이블 기능을 이용해서 데이터를 분석하고 요약해본다.•
작성된 피벗 테이블을 변형하고 서식을 지정해본다.•
피벗 테이블을 토대로 피벗 차트를 작성하고 변형해본다.2
피벗 테이블
z 피벗 테이블의 정의
• 피벗(Pivot)이란 단어는 중심축을 따라 움직이는 베어링(기계 공구)의 일종이며, 한 발 을 중심축으로 붙이고 몸을 돌리는 기술을 의미하는 농구용어로도 사용된다.
• 피벗 테이블이란가상의 중심축을 기준으로행방향에 있던 필드를 열 방향으로 또는 반대로 할 수 있기 때문에 붙인 이름이다.
• 작성된 데이터 목록의 필드를 축으로 하여 재구성하면, 필드의 위치에 따라, 데이터 통계를 한 눈에 파악할 수 있도록 정리된 표로 만들어 주는 기능이다.
z 피벗 테이블의 특징
• 원본 데이터 목록의 행이나 열의 위치를 변경해서 다양한 형태로 표시할 수 있다.
• 각 필드에 다양한 조건을 지정할 수 있고, 그룹별로 데이터를 집계할 수도 있다.
• 현재 작업 중에 시트에 작성할 수도 있고, 별도 시트에 작성할 수도 있다.
• 원본 데이터가 변경되면, 피벗 테이블 데이터가 자동으로 고쳐지지는 않는다. 데이터 를 갱신하려면 ‘새로 고침’ 메뉴를 실행한다.
피벗테이블 작성하기:
[데이터]-[피벗 테이블/피벗 차트 보고서]
z 1단계
▲ 분석할 데이터의 위치와 작성할 보고서의 데이터 형태의 종류 선택
z 2단계
▲ 데이터 목록의 범위 지정
z 3단계 - 작성 위치및 레이아웃 설정
피벗테이블 작성하기 (계속)
옵션 버튼 클릭, 피벗 테이블에 대한 다양한 내용을 설정할 수 있다.
열의 총합계: 열방향 총합계의 표시 여부설정
행의 총합계: 행방향 총합계의 표시 여부설정
표 자동서식: 기본 자동 서식 적용
빈 셀 표시: 데이터 영역의 빈 셀에 표시할 값을 설정
서식 유지: 레이아웃을 변경하거나 보고서를 고칠 때마다 적용된 서식을 계속 유지
열 때 마다 새로 고침: 통합 문서를 열 때마다 원본 데이터의 변경 사항을 자동으로 피벗 테이블 에 적용 시킨다.
피벗 테이블의 구성요소
행 필드 페이지 필드 열 필드
열의 총합계 데이터 필드 행의 총합계
피벗 테이블의 구성요소 (계속)
• 페이지 필드 : 데이터 영역에 페이지별로 구분하여 나타낼 필드가 들어있다.
모두 나타내거나 특정 필드만 나타낼 수 있다. 페이지 필드 단추를 클릭해서 표시할 필드를 선택할 수 있다.
• 데이터 필드 : 데이터가 들어 있는 원본 목록으로, 분석할 대상을 나타낸다.
일반적으로 숫자 형식의 데이터가 있는 필드를 사용하면 평균, 개수, 최대값 등을 구하지만 개수를 구할 때는 문자 형식의 필드도 사용된다.
• 행 필드, 열 필드 : 행 방향, 열 방향으로 지정된 필드를 나타낸다. 각 필드 단추를 클릭하여 표시할 필드를 선택, 해제할 수 있다.
• 열의 총합계, 행의 총합계 : 열 방향, 행 방향의 데이터의 합계를 표시한다.
피벗테이블 옵션에서 표시되지 않게 선택할 수 있다.
피벗테이블 필드 그룹 만들기
• 특정 필드를 일정한 단위로 묶어 표현할 때 사용
• 숫자, 날짜, 시간으로 된 필드에만 사용할 수 있음
•그룹 지정할 필드를 선택, 마우스 오른쪽 버튼, [그룹 및 하위 수준표시]-[그룹] 메뉴 선택, 그룹화 대화상자에서 묶을 단위를 선택
8
피벗테이블 도구모음
피벗테이블 메뉴:피벗 테이블 작업시 사용되는 각종 메뉴를 제공
보고서 서식:피벗 테이블 의 서식 지정을 위한 자동 서식을 제공
차트 마법사:피벗 테이블 을 차트로 표시
하위 수준 숨기기:선택한 항목의 하위 수준을 숨김
하위 수준 표시:선택한 항 목의 하위 수준을 표시하 거나 새로운 시트에 하위 수준을 만듬
데이터 새로고침:원본데이터의 변경내용을 피벗테이블에 반영
필드설정:피벗 테이블 필드에서 사용할 함수, 표시형식 등을 설정
필드 목록 표시/숨기기:피벗테이블 필드 목록을 표시하거나 숨김
실습1. 실습 1. 피벗 테이블을 사용하여 표 만들기
실습1. 실습 1. 피벗 테이블을 사용하여 표 만들기(계속)
(1) (2)
(3)
실습1. 실습 1. 피벗 테이블을 사용하여 표 만들기(계속)
(4)
페이지 필드에 ‘품목’ 필드 버튼 드래그
행 필드에는 ‘날짜’,’대리점’ 필드 버튼 드래 그
열 필드에는 ‘제품명’ 필드 버튼 드래그
데이터 필드에는 ‘수량’과 ’금액’ 필드를 드래 그해서 놓고 <확인> 버튼 클릭
(5)
실습1. 실습 1. 피벗 테이블을 사용하여 표 만들기(계속)
• 필드 그룹 설정
• ‘날짜’ 필드를 주 단위로 묶기
• ‘날짜’ 필드를 선택, 마우스의 오른쪽 버튼을 누르고, [그룹 및 하위 수준 표 시]-[그룹] 메뉴 선택
• 단위목록에서 기존에 선택되어 있던 ‘월’을 클릭하여 선택해제하고, ‘일’을 클 릭 선택, 7일 단위로 묶을 것이므로 날짜 수를 ‘7’로 설정한 후, <확인> 버튼 클릭
• 날짜 필드의 부분합 없애기
• ‘날짜’ 필드를 선택, 마우스 오른쪽 버튼 클릭, [필드 설정] 메뉴 선택
• 부분합 옵션에서 ‘없음’을 선택하고 <확인> 버튼 클릭
• 데이터 필드의 함수 변경하기
• 데이터 필드에서 ‘합계:수량’ 선택, 마우스 오른쪽 버튼 클릭, [필드 설정] 선 택
• 사용할 함수 목록에서 ‘평균’ 선택하고, 이름란에 ‘수량평균’ 입력, <확인>
• 데이터 필드의 ‘합계: 금액’ 없애기
• 데이터 필드의 드롭다운 버튼 클릭, ‘합계: 금액’ 항목을 체크 해제한 후 <확 인>
• 다시 ‘합계:금액’을 표시하려면 피벗 테이블 필드 목록에서 ‘금액’ 필드를 데이 터 필드 영역에 드래그
실습1. 실습 1. 피벗 테이블을 사용하여 표 만들기(계속)
• 필드 이동/삭제하기
• 품목 필드를 제품명 필드가 있는 열 필드로 드래그하여 이동
• 이번에는 제품명 필드를 페이지 필드를 피벗 테이블 바깥 영역이나 피벗 테 이블 필드 목록 상자로 드래그하여 삭제
• 날짜 필드의 항목 이름 바꾸기
• [A5]셀에 ‘1주’, [A9] ‘2주’, [A13] ‘3주’, [A17] ‘4주’, [A21] ‘5주’ 라고 입력
• 피벗 테이블에서 총합계 없애기
• 피벗 테이블 안에서 마우스의 오른쪽 버튼을 누르고, [테이블 옵션] 선택
• 열의 총합계와 행의 총합계 옵션의 체크 표시 해제, <확인>
• 피벗 테이블 보고서 서식 지정하기
• 피벗 테이블 안에 셀 포인터를 두고, 피벗 테이블 도구 모음의 보고서 서식 아이콘 클릭
• ‘보고서4’ 서식을 선택, <확인>
• 다시 <보고서 서식> 버튼을 누르고, 자동 서식 대화상자의 서식목록 중 ‘표2’
서식을 선택
• 레이아웃이 다시 바뀌는 것을 볼 수 있다.
피벗 차트 : 피벗 테이블의 요약된 데이터를 그래프 형식 으로 보여줌으로써 요약된 데이터에 시각적인 효과를 줌
z 피벗 차트의 구성
페이지 필드 영역
데이터 필드 영역
계열 필드 영역
16
실습 실습 2. 피벗 차트 만들기 2.
z 아래와 같이 대리점별 제품 판맨 현황을 보여주는 피벗 차트 만들기
실습2. 실습 2. 피벗 차트 만들기(계속)
이전에 만든 피벗 테이블 안에 셀 포인터를 두고, 피벗 테이블 도구 모음에서 차트 마법사 아이콘 클릭
새로운 차트 시트가 만들어 지면서 자동으로 차트 생성
실습 실습 2. 피벗 차트 만들기(계속) 2.
피벗 차트 필드 변경하기: 위의 그림과 같이 되도록 ‘날짜’ 필드 드래그 삭제, ‘제품명’ 필드 드래그 삽입,
‘품목’ 필드 이동 등의 필드를 변경하여 차트를 완성한다.
