구글 스프레드 시트 14 - 피봇 테이블 활용

안녕하세요.

오늘은 스프레드 시트에서 피봇 테이블을 활용하는 법에 대해서 알아보도록 하겠습니다.

 

피봇 테이블은 데이터를 요약해서 보거나 데이터와 데이터 간의 관계를 파악하는데 활용됩니다. 예를 들어 영업부의 각 직급(과장,대리,사원) 별로 평균나이를 구하고싶다. 이럴때 유용하게 활용 할 수 있습니다.

 

수정사항 직책 -> 직급으로 수정

안에 이미지에 있는 직책을 직급으로 봐주셔야 될 것 같습니다.

---

1. 피벗테이블 만들기

원하는 범위를 드레그한 후에 데이터 -> 피봇 테이블을 클릭합니다. 

스프레드 시트 피봇 테이블 만들기 1

 

새시트 혹은 기존시트 어디에 피봇 테이블을 만들지 정합니다.

스프레드 시트 피봇 테이블 만들기 2

 

피봇 테이블 생성

스프레드 시트 피봇 테이블 만들기 3

---

2. 생성한 피봇테이블에서 원하는 테이블 만들기

아래에 예제가 있다고 가정해봅시다.

스프레드 시트 피봇 테이블 예제

 

이 시트에서 각 부서별 직급별 나이 평균을 구하려면 어떻게 할까요?

교차되는 테이블 형태로 데이터가 나와야 할 것 입니다.

 

그렇다면 이런식으로 한번 넣어볼까요? 

행: 부서

열: 직급

값: 나이 (평균값)

 

피봇 테이블 셋팅

 

결과가 어떻게 나올까요?

피봇 테이블 결과

 

원하는 데로 결과가 나오게 됩니다.

현재는 average를 활용했지만 sum, count등 다양한 집계함수들을 활용 할 수 있습니다.

 

피봇 테이블 집계함수

---

3. 정렬과 필터

 

- 정렬: 특정한 기준으로 데이터를 나열하는 것

 

각 행과 열에서 오름차순과 내림차순 정렬이 가능합니다.

 

오름차순: 가장 작은 수나 텍스트부터 쭉 나열하는 방식

내림차순: 가장 큰 수나 텍스트 부터 쭉 나열하는 방식

 

 

구글 스프레드 시트 피봇 테이블 정렬

 

위의 예시에서 부서가 오름차순으로 정리가 되어있었는데요.

 

피봇 테이블 결과

이를 내림차순으로 변경해봅시다.

피봇테이블 부서 내림차순 정렬

내림차순으로 정렬을 하니 개발 -> 마케팅 -> 영업 순에서 영업 -> 마케팅 -> 개발 순으로 바뀌었지요?

텍스트의 경우 ㄱㄴㄷ순을 따르기 때문에 ㅇ으로 시작하는 영업이 내림차순이 맨위로 올라와야 정상입니다.

 

- 필터: 원하는 값에 대한 조건을 걸어서 데이터를 보는것

 

예를들어 부서별 직급별로 30대이상인 인원이 몇명인지 알아보고 싶다고 가정해봅시다.

 

맨 아래 필터에서 추가를 누릅니다.

 

구글 스프레드 시트 피봇 테이블 필터 추가1 

 

기준이 되는 나이를 선택합니다.

구글 스프레드 시트 피봇 테이블 필터 추가2

 

원하는 필터를 설정합니다.

 

값별 필터링: 정확한 값을 하나만 설정해서 필터를 합니다. ex) 나이가 딱 30인 사람

구글 스프레드 시트 피봇 테이블 값별 필터

 

조건별 필터링: 몇이상, 몇이하등 다양한 조건의 값을 범위형태로 필터합니다. ex) 나이가 30이상인 사람

구글 스프레드 시트 피봇 테이블 조건별 필터1

 

저희는 30대이상 범위로 설정할 것이니 조건형 필터에서 "보다 크거나 같음"을 활용해 보겠습니다.

구글 스프레드 시트 피봇 테이블 조건별 필터2

 

그리고 값에서 집계 함수를 average에서 count로 바꿔줍니다.

average에서 count로 변경

 

그러면 결과는 어떻게 나올까요?

피봇 테이블 필터링 결과

각 직급별 부서별 30대 이상의 인원수가 나오게 됩니다.

---

마치며

피봇 테이블의 경우 정말정말 많이 활용되는 기능으로써 사실상 안쓰는 사람이 없다고 생각하셔도 무방할 것 같습니다. 실제로 실무에 나가서도 커스텀 테이블을 만들기 위해 sumifs나 averageifs, countifs등을 활용할 때가 있지만 일반적인 집계 테이블을 만들때는 대부분 피봇을 활용합니다. 잘 배워서 요긴하게 써먹읍시다.