Googlesheet_피벗 테이블 사용하기

 

자료 출처 : 일잘러의 비밀, 구글 스프레드 시트, 강남석 지음

 

다량의 데이터를 한번에 다루려면 피벗 테이블을 사용할 수 있다. 

피벗 테이블을 써서 두 가지 기준으로 데이터 합계 내기

피벗테이블을 만들기 위해 테이블화 시킬 데이터를 설정해 주자.

행, 열, 값에 어떤 데이터가 들어갈 것인지 선정해주면 그에 대한 데이터 테이블이 출력된다. 

 

피벗 테이블에 필드 추가 및 레이아웃 변경하기

위 테이블에서 판매목표 값을 추가 해주었더니 아래와 같이 하나의 column이 column-A, column-B로 쪼개지면서 두 가지 관점의 데이터를 볼 수 있게 되었다. 종종 비즈니스 상황에서 이런 테이블을 만들어야 하는 경우가 생기는데, 직접 만들게 되면 해당 테이블을 지속 가능하게 활용하기 어려운데, 피벗 테이블을 사용하면 쉽게 구현할 수 있어 유용하다고 생각한다. 

 

위의 경우 column 구성의 복잡도가 높아져서 데이터가 한 번에 파악이 되지 않는다. 아래와 같이 column과 row를 transpose 시켜주자.

 

필터링과 슬라이서

필터와 슬라이서를 활용하면 조건에 맞는 데이터만 피벗 테이블에서 집계할 수 있다.

 

피벗 테이블에 필터 적용하기

아래와 같은 피벗 테이블에

필터를 먹이면 이렇게 된다.

 

 

특정일만 필터링한 데이터 결과값을 피벗 테이블로 볼 수 있다. 

 

피벗 테이블에서 한번 더 필터 기능을 활용해 섬세하게 데이터를 다룰 수 있게 된다. 

 

조건부 필터링

아래와 같이 필터링을 condition으로 응용할 수 있다. 특정 날짜에 해당하는 데이터로만 피벗 테이블을 구성해보자.

 

 

아래와 같이 나왔다. 2025년도 실적이 없는 구역의 데이터는 자동으로 집계되지 않기 때문에 해당하는 ‘강남구’row도 없어진 상태다.

 

필터 항목 쉽게 바꾸기 위한 슬라이서 사용

필터 항목을 자주 바꾼다면 슬라이서를 활용하면 좋다.

 

우선 필터를 없애주자.

아래와 같이 슬라이스를 추가해주었다. 슬라이스 추가는 위 Data - Add a slice 메뉴를 선택해주면 된다.

우측에 apply to pivot tables 옵션도 체크되어 있다. 

 

슬라이스에 특정일만 필터링 해주었더니 필터링한 것과 동일하게 데이터가 추려졌다.

 

슬라이스는 일종의 인스턴트 필터링과 같은 느낌이다. 

 

단, 슬라이스로 변경한 사항은 사이트를 새로 열때 초기화된다. 만약 당신이 슬라이스로 필터링한 데이터 피봇 테이블을 보여주고 싶다면 슬라이스 창의 우측 상단 점 세 개 메뉴를 누른다. 그리고 Set current as default를 누르면 해당 창이 세팅되어 있을 것이다. 

 

요약 기준과 계산 필드로 피벗 테이블 값 바꿔주기

 

피벗 테이블은 합계는 물론이고 다양한 데이터 요약 기준을 적용할 수 있다.

• sum
• counta : 총 상품의 수를 가져올 수 있겠다
• count
• countunique : 상품의 SQU 데이터를 가져올 수 있겠다
• average
• max
• min
• median : 중앙값
• product : 곱
• stdev : 표본 표준편차
• dtdevp : 전체 모집단 표준편차
• var : 표본분산
• varp : 전체 모집단 분산

 

Values에 상품 코드를 추가해준다. 

 

상품 코드는 아래와 같이 문자열로 되어있다.

따라서 SUM보다는 COUNTA로 합계를 내주는 것이 좋다.

여기서 한번 더 들어가보자.

값을 추가하고 계산된 필드(Calculated field)를 추가해준다. 그렇게 되면 엄청난 테이블이 생성된다. 

 

아래와 같이 calculated field 값을 넣어준다.

뭔가 무시무시한 테이블이 만들어졌다.

 

그룹화와 정렬

아래와 같은 기본적인 피벗테이블이 있다.

 

여기서 데이터들을 그룹화해보자.

 

아래와 같이 판매개시일 Row를 추가해보자.

 

아래와 같이 판매개시일 column이 새로 생기면서 각 구별 데이터들이 판매개시일 별 데이터들로 세부화되어 나타났다. 

 

하지만 개시일이 DATE data 형식이 아닌 상태이기 때문에 format을 수정해주자.

 

하지만 일(Day)단위로 데이터를 보려니 벅차다. 일 대신 연 단위로 데이터를 분류하면 좀 큼직하게 한 눈에 바라볼 수 있을 것 같다.

 

아래와 같이 판매개시일 날짜 데이터에서 오른쪽 클릭을 하면 피봇 날짜들을 그루핑할 수 있는 옵션이 나타난다. 다른 컬럼에서는 이 옵션이 보이지 않는다. 여기서 년 단위로 그루핑을 해주자.

 

아래와 같이 분기별 데이터들을 한 눈에 볼 수 있는 멋진 테이블이 완성되었다. 마치 근사한 기계 같지 않은가?

하지만 아직 총계 수치들을 보니 들쭉날쭉하다. 이를 내림차순으로 깔끔하게 정리해주자.

위치 Row의 Sort by 옵션을 바꿔주었다. 그리고 해당 Row를 판매개시일보다 아래로 내려주었다. 정말 근사한 분기별 통계 데이터가 완성되었다.

 

피벗 테이블 사용 팁

피벗 테이블 사용에 있어서 핵심은 주요 지표들만 사용할 것 그리고 집계에 적절한 데이터 유형을 선정하는 것이다. 

 

피벗 테이블은 데이터를 집계하고 요약하기 위해 사용하는 도구다. 세부 분석은 별도 세부 리포트로 수행하고, 피벗 테이블에는 전체적인 그림이나 핵심 데이터만 넣는 것이 중요하다.

 

집계에 적절한 데이터 유형을 준비하기 위해 아래와 같은 규칙을 따라야 한다.

1. 첫 번째 행은 필드명을 입력하는 행으로 사용한다. 즉, 한 행은 독립적이어야 한다.
2. 필드명이 누락된 열은 없도록 한다
3. 모든 행과 열을 채워둔다
4. 각 필드는 다른 필드와 서로 다른 성격을 가져야 한다