구글 스프레드시트에는 피벗 테이블 (Pivot Table)이라는 아주 강력한 기능이 있다. 스프레드시트에서 데이터 분석을 위해 가장 중요한 기능이며 알아두면 굉장히 유용하게 사용할 수 있는 기능이다. 피벗 테이블의 기본 사용법을 쉬운 예제를 통해 알아보자.
피벗 테이블이란?
피벗 테이블은 큰 데이터를 요약하여 통계표를 만들 수 있는 기능이다. 원하는 정보만 요약해서 합계, 평균 등의 통계를 볼 수 있도록 데이터를 재구성하는 기능을 한다.
피벗 테이블(Pivot Table)에서 피벗(Pivot)이란 말의 의미를 알면 이해하기 더 쉽다.
피벗(Pivot)
(축을 중심으로) 회전하다, 회전시키다
피벗은 축을 회전시킨다는 의미이다. 그래서 피벗 테이블이라고 하면 테이블의 축을 회전시킨다는 의미이다. 테이블의 축을 회전시키면 행과 열의 위치를 바꿀 수 있다.
보통 스프레드시트에서 테이블 형태의 데이터 시트를 만든다. 테이블은 행과 열에 어떠한 항목의 값을 넣을지 미리 정해놓고 데이터를 채워넣게 된다. 따라서 테이블의 행과 열은 이미 고정된 상태인 것이다. 피벗 테이블을 이용하면 고정되어 있는 행과 열을 회전시켜서 위치를 바꿀 수 있다. 더 나아가서 원하는 항목을 행 또는 열에 자유롭게 재배치할 수 있고, 요약하거나 필터링해서 볼 수 도 있다.
데이터를 요약해서 볼 수 있기 때문에 통계 작업을 하는데 아주 편리하다. 요약한다는 의미는 같은 항목끼리 묶어서 합산을 하거나 평균값을 구하는 등의 연산을 한다는 의미이다. 이러한 요약 작업이 아주 쉽게 되기 때문에 통계 작업을 많이 하는 사용자에게 피벗 테이블은 반드시 알아야 할 기능이라고 할 수 있다.
정리하면 피벗 테이블은 아래와 같은 기능들을 이용해 테이블을 재구성할 수 있다.
- 재배치
- 요약
- 필터링
피벗 테이블 만들기
아래의 샘플 데이터를 사용하여 피벗 테이블을 만들어보자. 확인하기 쉽도록 최대한 데이터를 간단하게 만들어보았다. 월별 가계부 내역이며 수입/지출 항목별 금액을 나열한 테이블이다.
피벗 테이블 생성
테이블 영역 내부의 셀을 아무거나 선택한 다음에 상단 메인 메뉴에서 삽입 – 피봇 테이블 을 선택한다.
그러면 아래와 같이 피봇 테이블 만들기 팝업창이 뜨고, 어디에 만들지를 선택할 수 있다.
- 새 시트: 새로운 시트를 생성
- 기존 시트: 기존의 시트에서 위치를 직접 선택
기존 시트를 선택하면 아래와 같이 셀참조를 입력할 수 있는 상자가 나오는데, 오른쪽의 아이콘을 눌러서 직접 셀을 선택할 수 있다. 위치 선택 화면에서 원하는 시트와 셀을 선택하면 자동으로 셀참조 번호가 채워진다.
일단은 새 시트를 선택하여 진행해보자. 그러면 새로운 시트가 만들어지고 피벗 테이블이 생성될 것이다.
피벗 테이블 설정
피벗테이블 만들기를 완료하면 아래와 같이 왼쪽에 피벗테이블 영역과 오른쪽에 피벗테이블 설정 영역이 보이게 된다.
- 피벗테이블 영역 : 시트에 피봇 테이블이 보여지는 영역
- 피벗테이블 편집기 영역 : 피벗테이블을 설정할 수 있는 영역
피벗 테이블 편집기를 이용하여 피벗 테이블을 구성할 수 있다. 편집기에서 설정을 바꾸면 피벗테이블 영역에 테이블이 바로 적용되어서 보여지게 되므로 작업이 매우 편리하다.
편집기를 이용해 아래의 항목을 설정할 수 있다. 설정할 수 있는 항목의 설명은 아래 표와 같다.
| 항목 | 설명 |
|---|---|
| 행 | 테이블의 행에 추가할 항목 |
| 열 | 테이블의 열에 추가할 항목 |
| 값 | 테이블의 값으로 사용할 항목 요약기준은 합산, 평균 등 선택 가능 |
| 필터 | 테이블의 필터링 조건 |
피벗 테이블 예제 1
아래와 같이 수입/지출의 합계가 월별로 보여지도록 해보자.
원본 테이블에 있는 행과 열의 항목을 재배치할 것이다. 구분, 월 필드만 필요하며 아래와 같이 피봇 테이블 편집기에 행, 열, 값 필드에 추가해주면 된다.
- 행: 구분 필드 (합계 표시 제외)
- 열: 월 필드 (합계 표시)
- 값: 금액 필드 (요약기준: SUM)
피봇 테이블 편집기 설정은 아래 그림과 같다.
행에 추가한 구분 필드에서 합계 표시는 제외하도록 하자. 수입과 지출의 합계는 필요 없기 때문이다. 월 필드에서의 합계 표시는 체크하여 월별 수입/지출의 합계가 보여지도록 하자. 값의 금액 필드의 요약 기준은 SUM이다. 같은 항목의 값들을 모두 더해주는 역할을 한다.
정말 1분도 안되는 시간에 피벗테이블을 이용하여 월별 수입/지출의 총액을 나타내주는 테이블을 만들었다.
피벗 테이블 예제 2
이번에는 아래 그림과 같이 지출 내역의 항목별 합계를 요약해주는 테이블을 만들어보자.
이번에는 항목 필드를 열에 두고 월 필드를 행에 두었다. 이렇게 언제든 원하는 곳에 재배치가 가능하다. 또한 필터를 통해 지출 내역만 필터링해서 보이도록 한다.
- 행 : 월 필드
- 열 : 항목 필드
- 값 : 금액 필드 (요약기준: SUM)
- 필터 : 구분 필드 (값별 필터링: 지출)
아래 그림과 같이 피벗 테이블 편집기를 설정한다.
그러면 월별 지출 항목별 금액 합계 통계를 보여준다.
피벗 테이블 예제 3
행이나 열에 여러개의 필드가 올 수 있다. 아래 그림의 예제는 열에 구분, 항목 필드 2개를 추가하여 월별 수입/지출 항목을 합계를 보여주도록 하였다.
- 행 : 구분 필드(합계 표시 제외), 항목 필드(합계 표시)
- 열 : 월 필드(합계 표시)
- 값 : 금액 필드(요약기준: SUM)
구글 스프레드시트의 피벗 테이블은 큰 데이터를 가지고 다양한 통계 테이블을 만드는데 아주 편리하다. 기본적인 통계 작업을 위해서는 반드시 알아두어야 할 기능 중의 하나이다. 따라서 여기서 설명한 예제를 한번씩 따라해보면서 동작원리를 쉽게 이해할 수 있으니 당장 필요가 없더라도 반드시 익혀두고 넘어가도록 하자.