[엑셀 스터디 #16] 실무 엑셀의 핵심, 피벗 테이블

 

 

 

서론

엑셀 고수라면 모름지기 피벗 테이블은 발로도 할 수 있어야 한다. (내가 지어냈다.) 무튼 그 정도로 피벗 테이블이 중요하고 실무에서 가장 많이 쓰인다는 말을 수도 없이 들어왔다. 과거 어떤 면접에서는 내게 피벗 테이블을 잘하냐고 콕 집어서 물어봤을 정도다.

 

※ 피벗이란 무엇인가?

피벗은 무언가가 회전하는 중심축이라는 의미를 가지고 있다. 또, 농구 경기에서는 공을 가지고 있는 선수가 한 발을 딛고 제자리에서 방향을 바꿀 때 필요한 기술을 의미한다. 이 기술이 필요한 이유는 다른 곳으로 몸을 틀어 패스를 하거나 방향을 바꾸거나 → 전략을 바꿀 수 있기 때문이다. 뜬금 없이 농구를 비유로 들었지만.

 

'어떤 중심을 기준으로 돌린다.' 이는 즉슨, '관점을 바꾼다' 라는 말로도 바꿀 수 있다. 동일하게 엑셀에서 피벗 테이블을 사용하는 이유는 데이터를 다른 기준으로, 다른 관점에서 바라보기 위함이다. 

 

피벗 테이블

피벗 테이블은 데이터를 다양한 기준으로 재배치하여 요약, 분석하는 기능이다.

 

1. 사용법

오랜만에 직접 만든 예시

 

예시 만들어야지 하면 문방구 밖에 생각이 안난다. A, B, C, D 문방구가 있고 품목이 있고 문구점마다 품목이 있거나 없고 금액이 다 다르다. 정렬을 해도, 필터를 해도 한 눈에 비교하기는 쉽지 않다. 이제 피벗 테이블을 사용해보자.

 

 

표를 다 잡고 삽입 - 피벗 테이블을 누르면 피벗 테이블을 만들 수 있는 '필드 목록'이라는 도구를 내 손에 쥐여준다. 처음엔 우측에 붙어있는데 머리글에 커서 올리면 사방선으로 바뀌고 예시와 같이 떼서 사용할 수 있다.

 

* 단축키: alt + n + v 

 

 

피벗 테이블 사용시 한 가지 규칙만 알고 있으면 쉽다. 바로 '십자가'다. 필드 목록을 보면 상단에 표의 항목들인 필드들이 있고, 하단에 피벗테이블을 구성할 필터, 열, 행, 값 영역이 있다. 예시를 보면 알다시피 십자가 형태를 똑같이 유지하면서 테이블을 만든다. 고로 어렵게 생각할 것 없이 내가 테이블을 어떻게 구상할지만 잘 알고 있으면 만드는 건 쉽다.

 

 

* 피벗 테이블 아무 곳이나 선택하면 피벗 테이블 도구가 등장한다. 다양한 기능이 존재한다. 참고로 필드 목록은 껐다 킬 수 있다. 

 

2. 필드 표시 형식

 

필드에서 값에 해당하는 영역의 셀들은 셀 서식으로 서식을 바꾸기 보단, 한 셀만 우클릭해서 필드 표시 형식으로 바꾸는 게 더 효율적이다. 

 

 

3. 다양한 집계 방식&주의사항

예시에선 합계만 다루고 있지만, 개수, 평균 등 다양한 집계 방식으로 테이블을 구성할 수 있다. 필요에 따라.

 

주의사항

- 합계나 평균 등 연산이 필요한 집계 방식에서, 숫자 데이터는 집계 방식을 따르지만 문자 데이터는 기본적으로 개수를 반환한다. 그래서 간혹 숫자 데이터인데 집계가 제대로 안됐거나 개수가 표시될 경우 문자가 섞여 있는 것이다 (문자 형식의 숫자 또는 문자 형식의 날짜.)

 

- 백분율을 집계할 때는 합계로 하게 되면 1000% 이상으로 높아지는 참사가 발생한다. 그러므로 특별한 상황이 아니라면 보통 평균으로 계산해서 집계한다. 

 

예시와 같은 피벗 테이블이 있다. 보다시피 할인율에 백분율을 적용했을 때 몇 천, 몇 만 퍼센트씩 해버린다. 그래서 합계는 잘못된 데이터가 생기고, 평균을 해야 올바르게 할인율을 구할 수 있다. 아래와 같이 해결하면되고 센스 있게 표시 형식에서 소수점 둘째자리까지 만들어주자.

 

 

4. 깔끔한 분석 보고서 만들어보기

 

① 행과 열을 바꿔서 가독성 높이기

간혹 열에 있는 항목들이 너무 많아 보고서가 우측으로 길어지는 경우가 있다. 이럴 경우 행과 열만 바꿔도 가독성이 늘어난다.

열이 너무 길어 가독성이 망해버린 보고서

가독성 UP

 

 

② 테이블 형식으로 보고서 바꿔서 범용성 UP

보통 피벗 테이블을 만들 때 행에 두 가지 항목이 들어가면 압축 형태로 만들어진다. 이럴 경우 표에 함수를 적용하는 등 추가 작업이 어려울 수 있다. 예를 들어 제조사에만 함수를 적용하고 싶은데, 압축 형태란 아래 예시와 같이 제조사와 제품명이 같은 열에 있는 형태다. 그래서 따로 분리시키려면.

 

피벗 테이블 선택된 상태에서 [디자인] 탭 - 보고서 레이아웃을 테이블 형태로 바꿔주면 된다.

 

 

③ 디자인으로 가독성 높이기

 

 

④ 피벗 테이블 옵션

 

피벗 테이블에 있는 아무 셀이나 우클릭하면 저렇게 피벗 테이블 옵션에 들어갈 수 있다. 옵션에서 피벗 테이블에 대한 다양한 설정을 조절할 수 있다. 이번엔 오빠두엑셀 님이 알려준 2가지 옵션을 다뤄보겠다.

 

 

- 하단의 업데이트 시 열 자동 맞춤, 셀 서식 유지를 통해 피벗 테이블을 새로 고침해도 내가 조정했던 옵션들이 유지되도록 설정할 수 있다.

 

 

- 확장/축소 단추 표시를 하면 아래 예시와 같이 피벗 테이블이 훨씬 더 깔끔하고 보고서처럼 바뀐다.