구글 스프레드시트 사용법 (4) 피봇 테이블

이번에는 구글 스프레드시트 사용법 (3) QUERY 후속으로 피봇 테이블에 대해 알려드리겠습니다.

피봇 테이블 생성 및 사용
https://support.google.com/docs/answer/1272900?hl=ko

피봇 테이블을 사용하여 큰 데이터 세트의 범위를 좁히거나 데이터 포인트 사이의 관계를 확인할 수 있습니다. 예를 들어 피봇 테이블을 사용하여 특정 달에 가장 많은 수익을 올린 영업 사원이 누구인지 분석할 수 있습니다.

저번에 구글 스프레드시트 (3) QUERY를 올리고나서 생각해보니 쿼리언어를 모르는 분들에게는 어려운 일이겠다는 생각이 들었습니다. 그래서 쿼리 언어를 모르는 분들도 QUERY함수를 이용한 것 처럼 원하는 테이블을 만들 수 있는 피봇 테이블 사용법을 알려드리겠습니다. 피봇 테이블 편집기를 몇번 클릭하는 것만으로 간단하게 QUERY결과와 동일한 테이블을 만들 수 있습니다.

★ 샘플 스프레드시트
https://docs.google.com/spreadsheets/d/1m5ReLX7RpCsTA_y44RlfHQ4DjWFRgbp9hkioCeI_qk4/edit?usp=sharing

[데이터] 시트 준비

피봇 테이블로 분석할 대상 데이터를 준비합니다.
지난번에 사용했던 점심 메뉴 기록에 데이터를 조금 더 추가했습니다. (5월, 6월 기록)
pivottable001

피봇 테이블 만들기

[데이터] 시트가 선택된 상태에서 데이터 > 피봇 테이블 메뉴를 클릭합니다.

pivottable1021
down 새로운 시트가 추가되면서, 화면 오른쪽에 ‘피봇 테이블 편집기’가 표시됩니다.
pivottable1031

얼핏 보기에는 복잡해 보이지만, 피봇 테이블을 구성하는 4가지 항목을 설정하는 메뉴입니다.
pivottable0011

  • : 테이블의 세로 헤더에 넣고 싶은 항목을 추가.
  • : 테이블의 가로 헤더에 넣고 싶은 항목을 추가.
  • : 테이블의 데이터로 넣고 싶은 항목을 추가.
  • 필터: 대상 데이터의 범위를 제한할 때 사용.

[메뉴별 횟수] 만들기 순서

행 항목 추가

행 > 추가 > ‘메뉴’를 클릭합니다.pivottable1041downA열에 ‘메뉴’가 추가됩니다.
pivottable1051

값 항목 추가

값 > 추가 > ‘메뉴’를 클릭합니다.
pivottable1062
downB열에 COUNTA(메뉴) 데이터가 표시됩니다.pivottable107

정렬하기

메뉴 이름으로 정렬되어 있는 것을 횟수로 정렬하려면, 행 메뉴로 돌아와 정렬기준에서 ‘메뉴의 COUNTA’ 를 클릭한다.pivottable108
down순서에서 ‘내림차순’을 클릭한다.
pivottable109
downB열의 수치로 내림차순 정렬됩니다.
pivottable110

결과

왼쪽이 QUERY로 얻은 결과, 오른쪽이 피봇 테이블입니다.
A열 메뉴의 순서가 조금 다르게 나오지만 동일한 내용이 나옵니다.
query_pivot01

이런 방식으로 이전 QUERY 예시의 시트를 피봇 테이블로 만들 수 있습니다.
[많이 간 식당], [6월 한달 메뉴 분석], [최근 간 식당] 시트도 피봇 테이블로 만들어 보겠습니다.

[많이 간 식당]

  1. 행에 ‘식당’ 추가.
  2. 값에 ‘식당’ 추가.
  3. 행의 정렬 기준을 ‘식당의 COUNTA’, ‘내림차순’으로 변경.
  4. 필터에 ‘식당’ 추가.

pivottable201
down필터에서 ‘각자’의 체크를 제외.
pivottalbe206
down결과: 식당의 순서가 조금 다르게 나오지만 동일한 내용이 나옵니다.
query_pivot03
여기서 다른점: Query 결과는 limit 를 사용하여 결과 데이터의 갯수를 제한할 수 있지만,
피봇 테이블에서는 결과 갯수를 제한 할 수 없습니다.

[6월 한달 메뉴 분석]

  1. 행에 ‘메뉴’ 추가.
  2. 값에 ‘메뉴’ 추가.
  3. 행의 정렬 기준을 ‘메뉴의 COUNTA’, ‘내림차순’으로 변경.
  4. 필터에 ‘날짜’ 추가.

pivottable202down필터에서 6월 날짜만 선택.
pivottable_filter
pivottalbe205
down결과: 메뉴의 순서가 조금 다르게 나오지만 동일한 내용이 나옵니다.
query_pivot04
여기서 다른점: Query에서는 “where C >= date ‘2018-06-01’ and C < date ‘2018-07-01’ ” 처럼 날짜를 구간으로 지정할 수 있지만, 피봇 테이블에서는 필터에서 해당 항목을 선택하는 것으로 지정해야 합니다.

[최근 간 식당]

  1. 행에 ‘식당’ 추가.
  2. 값에 ‘날짜’ MAX 추가.
  3. 행의 정렬 기준을 ‘날짜의 MAX’, ‘내림차순’으로 변경.

pivottable203down결과: 거의 동일한 내용으로 나오지만, 조금 다르게 나옵니다.
query_pivot02
여기서 다른점: 최근에 다녀온 식당은 피봇 테이블로 Query와 동일한 결과를 만들 수 없었습니다. 피봇 테이블에서는 단순 나열이 안됩니다. 그래서 최대한 비슷한 결과가 나오도록 식당마다 가장 마지막에 방문한 날짜(날짜의 MAX)를 표시하고 내림차순으로 정렬하였습니다. 식당을 세로 헤더로 지정했기때문에 중복되지 않는 목록으로 표시되므로 여러번 기록된 ‘각자’의 경우 가장 마지막 날짜인 ‘2018. 6. 29’가 표시됩니다.

 

 

구글 스프레드시트의 다른 활용법을 알고싶으시다면!
» G Suite 사용법 #4 스프레드시트 활용 예시 모음

답글 남기기

아래 항목을 채우거나 오른쪽 아이콘 중 하나를 클릭하여 로그 인 하세요:

WordPress.com 로고

WordPress.com의 계정을 사용하여 댓글을 남깁니다. 로그아웃 /  변경 )

Google photo

Google의 계정을 사용하여 댓글을 남깁니다. 로그아웃 /  변경 )

Twitter 사진

Twitter의 계정을 사용하여 댓글을 남깁니다. 로그아웃 /  변경 )

Facebook 사진

Facebook의 계정을 사용하여 댓글을 남깁니다. 로그아웃 /  변경 )

%s에 연결하는 중