금융이나 회계 분야에 종사하고 있다면, 대부분의 채용 공고에서 중급 이상의 Excel 활용 능력을 요구한다는 사실을 이미 알고 계실 겁니다. 이러한 직무에서 가장 많이 활용되는 Excel 기능은 단연 피벗 테이블(Pivot Table)과 VLOOKUP입니다.
이 글에서는 피벗 테이블의 기본 개념과 사용법을 소개합니다. VLOOKUP에 대해 더 알고 싶다면 별도 가이드를 참고하시고, VLOOKUP의 강력한 대안인 INDEX-MATCH 함수도 함께 확인해 보시기 바랍니다.
Excel 피벗 테이블이란?
피벗 테이블이란 무엇일까요? 간단히 말해, 피벗 테이블은 Excel에서 대량의 데이터를 기반으로 요약 테이블을 빠르게 작성할 수 있도록 도와주는 기본 제공 기능입니다.
예를 들어, 다양한 모델의 휴대폰을 판매하는 온라인 쇼핑몰을 운영 중이고 아래와 같은 판매 데이터를 보유하고 있다고 가정해 보겠습니다. 예시 스프레드시트를 내려받아 직접 따라 해볼 수도 있습니다.
약 두 달간 영업을 진행한 뒤, 첫 달과 두 번째 달 중 어느 달에 제품을 더 많이 판매했는지 궁금해질 것입니다. 또한 Apple 제품과 Samsung 제품 중 어느 쪽 판매가 더 많았는지, 그리고 월별 총 매출액은 얼마인지도 알고 싶을 겁니다.
피벗 테이블은 COUNT나 SUM 같은 수식 없이도 빠르게 요약 결과를 얻을 수 있는 최적의 도구입니다. 사용법만 익히면 위 질문들의 답을 몇 초 만에 확인할 수 있습니다.
지금부터 피벗 테이블을 만드는 단계별 방법을 살펴보겠습니다.
1단계 – 데이터 테이블 안의 아무 셀이나 클릭한 뒤, Excel 상단 메뉴에서 삽입(Insert) → 피벗 테이블(Pivot Table)을 선택합니다.
2단계 – 설정 창이 열리면, 앞서 클릭한 셀을 기준으로 데이터 범위가 자동으로 지정됩니다. 이 예시에서는 결과를 한눈에 확인하기 쉽도록 피벗 테이블을 새 워크시트에 배치합니다.
3단계 – 새 시트에 생성된 빈 피벗 테이블을 클릭하면, 화면 오른쪽에 피벗 테이블 필드(Pivot Table Fields) 창이 나타납니다. 여기서 필드를 드래그 앤 드롭하여 원하는 요약 테이블을 손쉽게 만들 수 있습니다.
4단계 – 월별 판매 대수를 확인하려면 Month Sold(판매 월) 필드를 ROWS(행) 영역으로, Brand(브랜드) 필드를 VALUES(값) 영역으로 드래그합니다.
그러면 피벗 테이블이 자동으로 업데이트되어 월별 행 개수, 즉 월별 휴대폰 판매 대수가 표시됩니다.
Brand 대신 Model이나 Warehouse Location을 VALUES에 넣어도 동일한 숫자가 나오는데, 이는 각 월의 총 행 개수를 단순히 세기 때문입니다. 결과를 보면 2월(FEB)보다 1월(JAN)에 휴대폰을 더 많이 판매한 것으로 나타납니다.
5단계 – Apple과 Samsung 중 어느 제품을 더 많이 판매했는지 확인하려면, 새 피벗 테이블을 만들 필요 없이 기존 테이블을 그대로 재사용하면 됩니다.
먼저 불필요한 필드를 영역 밖으로 드래그해 스프레드시트의 빈 곳에 놓아 선택을 해제합니다.
그다음 ROWS 상자에 Brand를 대신 넣습니다.
피벗 테이블이 즉시 업데이트되어 브랜드별 누적 판매 수량이 표시됩니다. 결과적으로 Samsung보다 Apple 제품을 더 많이 판매한 것을 확인할 수 있습니다.
6단계 – 마지막으로 월별 매출액을 확인해 보겠습니다. 역시 동일한 피벗 테이블을 활용합니다.
Brand 필드를 제거하고 Month Sold를 다시 ROWS 영역으로 드래그합니다. 총 매출을 알고 싶으므로 VALUES 영역을 비운 뒤, 아래와 같이 Sale Price(판매 가격)를 드래그합니다.
원본 데이터의 Sale Price 열이 숫자 형식이기 때문에, 피벗 테이블은 행 개수를 세는 대신 자동으로 합계를 계산합니다. 그 결과 1월(JAN)에 $7,550, 2월(FEB)에 $7,100의 매출을 기록했습니다.
이제 아래와 같이 필드를 자유롭게 배치하며 피벗 테이블 결과가 어떻게 달라지는지 직접 실험해 보세요.
여기서 소개한 내용은 피벗 테이블이 할 수 있는 일의 극히 일부에 불과하지만, 데이터 분석을 시작하기에 충분한 기초가 될 것입니다. 즐거운 탐색 되시길 바랍니다!
팁: 화면 오른쪽의 피벗 테이블 필드 창이 사라졌다면, 피벗 테이블 위에 마우스를 올린 상태에서 마우스 오른쪽 버튼을 클릭하고 필드 목록 표시(Show Field List)를 선택하세요. 그러면 창이 다시 나타납니다.