꼭 알아야할 엑셀(excel) 팁: 엑셀 피벗테이블

기획업무/엑셀 잘하기

꼭 알아야할 엑셀(excel) 팁: 엑셀 피벗테이블

스위트스팟 2021. 1. 31. 20:43

엑셀(Excel)은 회사 업무에서 필수적인 툴입니다. 데이터 분석 업무를 담당하고 있고, 데이터는 많다면 엑셀 스킬이 업무 효율성, 성과에 매우 중요한데요. 이는 사용자의 퇴근시간과도 직결된다고 생각합니다 :)

오늘은 퇴근 시간을 ~1시간 이상 단축시켜줄 엑셀팁 피벗테이블의 꿀팁에 대해 알려드립니다.

엑셀피벗테이블이 왜 중요할까요?

피벗테이블은 데이터를 다양한 관점에서 재분석을 쉽게 할 수 있다는데 가장 큰 장점이 있습니다.

엑셀은 정말 다양하고 유용한 사용법이 많은데요. 그중 사용 빈도가 높은 경우는 판매 통계 데이터의 현황 분석이라고 생각됩니다. 10만 줄이 넘는 데이터를 추출했는데 이걸 다시 합치고/나누고/결합 분석해야 하는 경우가 많죠. 이럴때 단순하게 소팅/필터링을 사용한다면 한계가 많습니다. 이럴때 엑셀피벗테이블의 진가를 알수 있습니다.

그럼 예제 데이터와 함께 실행해보시죠.

데이터셋 파악

아래 데이터 셋이 있습니다. 고객별로 영화 타이틀을 구매한 가상의 mock 데이터 입니다.

구매 id, 사용자의 성, 이름, 구매 날짜(2015년 1월 부터 2021년 1월까지 기간 설정), 이메일, 성별, 지불카드종류, 영화장르, 영화 타이틀, 구매가격, 도시, 구매자의 회사명으로 칼럼을 구성해봤습니다. (데이터는 mockaroo라는 사이트에서 생성했으며 아래 간단히 알려드리겠습니다. )

분석

그럼 어떤 분석 질문을 시작해볼까요.

남여 성별 영화 장르별 구매 금액 합계
남여 성별 영화장르별 구매 횟수 합계
도시별 지불카드종류별 구매고객수 통계
도시별 성별별 영화장르별 영화타이틀의 구매 평균 가격

이런 분석을 기본적으로 하시지 않을까 생각합니다.

그럼 남여 성별 영화장르별 구매금액 합계를 계산해보겠습니다.

그럼 먼저 엑셀피벗테이블을 시작합니다. 제 엑셀이 영어로 설정되어 있는점 양해해주시구요.

데이터가 있는 시트에서 Insert 메뉴에 보시면 왼쪽 앞에 테이블이라고 있습니다. 거기에서 피벗 테이블을 누르세요.

피벗테이블을 누리시면 다음 데이터의 범위와 위치를 설정하는 단계로 이어집니다. data!$A$1:$L$1001로 나오는데요 이건 여기서 설정된 데이터는 data시트에 데이터 위치 A1부터 L1001까지 셀을 포함한다는 뜻입니다.

그리고 아래 어디에 피벗테이블을 위치할 건가를 묻습니다. 저는 New worksheet를 선택했습니다. 대부분은 이게 편합니다.

이제 새로운 sheet가 생겼고, 빈 피벗 테이블이 생겼습니다. 오른쪽 아래 보시면 피벗테이블필드, 필터, 컬럼, 로우, 밸류라는 칸이 뜨는 데요.

피벗테이블 필드는 위에 데이터셋에서 컬럼으로 정의된 기준들이 보이게 됩니다. 피벗테이블에서 이 필드들을 아래 필터/컬럼/로우/밸류에 요리조리 조합해가면서 다양한 분석을 할수 있습니다.

필터는 말 그래로 피벗테이블에 보여주는 통계를 필터시킬수 있는 옵션입니다. 예를 들어 필터에 지불수단을 넣고 visa, master 카드 중에 visa만의 통계를 보여주게 할수 있는 방법입니다.

칼럼은 피벗테이블에 가로축에 해당하는 컬럼을 어떤 값들로 채울것인가 인데요. 위에서 데이터 sheet중 컬럼 (피벗테이블에서 피벗테이블 필드로 표시되는 부분입니다. )을 넣어주면 그 칼럼안에 있는 칼럼의 데이터 종류별로 뿌려주게 됩니다.

로우도 컬럼과 마찬가지 방식입니다. 데이터셋에 있는 컬럼(피벗테이블에서 피벗테이블 필드로 표시되는 부분) 중 하나를 로우로 가져가게 되면 로우에 값들을 뿌려주게 됩니다.

시작전 제가 좋아하는 설정을 공유드립니다. 최근의 엑셀 버전을 아래 처럼 좌측으로 피벗테이블 디폴트로 레이아웃 설정되어 있습니다. 그리고 오른쪽 피벗은 '클래식 피벗테이블 레이아웃' 입니다.

제가 클래식 레이아웃을 선호하는 이유는 1. 최근 레이아웃은 상위 칼럼에 하위 칼럼값들을 연결해서 보여주는데, 구조가 좀 명확히 보이지 않는 점이 있구요. 2. 그리고 분석을 하다보면 피벗으로 만든 테이블로 다시 피벗분석을 하게 되는 경우도 발생합니다. 이 경우 데이터 테이블을 만들기우해서는 칼럼별로 데이터를 나눠놓아야 피벗을 돌릴수 있습니다.

피벗페이블에서 우 클릭후 피벗테이블 옵션에서 디스플레이를 보시면 클래식 피벗테이블을 선택할수 있어요. 그리고 클래식 피벗테이블 안에서 우클릭후 subtotal(소계) 도 클릭을 지우시는게 나중에 편할수 있습니다.

남여 성별 영화 장르별 구매 금액 합계

컬럼에 성별(gender)를 넣고 아래 로우에 장르를 넣어봤는데요. Mokaroo에서 제공하는 데이터가 그리 깔금하진 않습니다. 먼저 젠더가 여러 종류가 많네요. 단순하게 남성/여성이 아닌 bigender 등 다양한 데이터를 포함해주고 있습니다. 실제 업무에서도 여러 예상치 못한 데이터가 튀어 나오기 마련입니다.

그리고 장르도 상당히 종류가 다양하네요. 대표장르외에 복합장르를 포함한 데이터로 이해됩니다.

컬럼과 로우에 피벗테이블필드를 드래그 그리고 밸류에 프라이스를 드래그 합니다. 밸류는 합계로 자동으로 계산되었는데, 우클릭해서 count 옵션도 많으 쓰니 해보시기바랍니다.

그럼 우선 젠더에서 남성/여성만 필터 그리고 장르에서 대표 장르인 action, adventure, drama, crime 만 필해보겠습니다.

칼럼에 있는 gender 오른쪽에 회색박스의 화살표를 클릭하면 아래 같은게 나오는데요, 주황색으로 클릭되어 있는 칼럼의 특성값중에서 male, female만 선택하면 두개의 값만 필터 됩니다. 저는 주로 select all로 모든 필터를 없애고 제가 원하는 값을 클릭해서 지정하는 방식을 사용합니다.