구글 스프레드시트 사용법 (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월 기록)

피봇 테이블 만들기

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

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

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

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

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

행 항목 추가

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

값 항목 추가

값 > 추가 > ‘메뉴’를 클릭합니다.

B열에 COUNTA(메뉴) 데이터가 표시됩니다.

정렬하기

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

B열의 수치로 내림차순 정렬됩니다.

결과

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

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

[많이 간 식당]

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

필터에서 ‘각자’의 체크를 제외.

결과: 식당의 순서가 조금 다르게 나오지만 동일한 내용이 나옵니다.

여기서 다른점: Query 결과는 limit 를 사용하여 결과 데이터의 갯수를 제한할 수 있지만,
피봇 테이블에서는 결과 갯수를 제한 할 수 없습니다.

[6월 한달 메뉴 분석]

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

필터에서 6월 날짜만 선택.


결과: 메뉴의 순서가 조금 다르게 나오지만 동일한 내용이 나옵니다.

여기서 다른점: Query에서는 “where C >= date ‘2018-06-01’ and C < date ‘2018-07-01’ ” 처럼 날짜를 구간으로 지정할 수 있지만, 피봇 테이블에서는 필터에서 해당 항목을 선택하는 것으로 지정해야 합니다.

[최근 간 식당]

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

결과: 거의 동일한 내용으로 나오지만, 조금 다르게 나옵니다.

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

 

 

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