What I Do

엑셀 피벗테이블은 무엇이고, 어떨 때 쓰는 기능일까?

엑셀데이터 분석피벗테이블피벗테이블 만들기엑셀 데이터 분석피벗테이블 사용법엑셀 피벗엑셀 보고서피벗테이블 활용
엑셀 피벗테이블은 무엇이고, 어떨 때 쓰는 기능일까?

피벗테이블, 왜 쓰는 걸까?

흔히들 '엑셀을 잘 하기 위해서는 피벗테이블을 잘 쓸 줄 알아야 한다' 라고 이야기 합니다. 그래서 오늘은 피벗테이블이 뭐고, 어떨 때 쓰는 것인지. 그리고 조금 더 편하게 피벗테이블을 활용하기 위해서는 어떻게 하면 좋은지 함께 알아보려고 합니다.

먼저, 피벗의 사전적 의미에 대해서 알아보면 피벗은 '회전 축'이라는 뜻을 가지고 있는데요, 엑셀의 피벗 테이블도 축과 연관지어서 생각해볼 수 있습니다.

피벗테이블 개념

우리가 표로 정리해서 보고자 하는 관점의 축을 잡고, 표로 만들어 주는 기능이 바로 피벗 테이블이라고 볼 수 있는데요, 아래의 예시로 왜 피벗테이블이 좋은 지 보여드리겠습니다.

피벗테이블 구조

예를 들어, 우리가 특정 기간에 판매된 내역이 있다면 구매가 발생되는 요인을 파악하기위해서 어떤 고객이 가장 많이 우리 매장에 방문했는지, 어떤 품목이 제일 잘 나갔는지, 다양한 관점에서 분석을 해보고 싶을 때가 있습니다. 그렇게 다양한 관점에서 데이터를 확인해볼 수 있는 게 바로 피벗테이블입니다. 저도 피벗테이블을 알기 전에는 그때 그때 보고싶은 기준에 맞추어 함수를 써서 다시 표를 만들곤했는데, 피벗테이블은 바로바로 변경해서 볼 수 있으니까 복잡하게 수식을 쓰거나 기준이 달라질 때마다 새로 작성을 할 필요가 없게 되는 거죠.

피벗테이블 사용하는 방법

1단계: 데이터 선택 및 피벗테이블 삽입

데이터 선택

피벗테이블로 확인하고 싶은 데이터 영역을 추가해준 뒤, [삽입] > 피벗테이블을 선택해줍니다.

2단계: 범위 및 위치 설정

범위 설정

팝업창이 뜨면 데이터 범위를 다시 점검하고, 새 시트에 피벗테이블을 추가할 것인지, 기존 시트에 추가할 것인지 선택해줍니다.

3단계: 행과 값 설정

행과 값 설정

보고싶은 데이터 기준을 '행' 부분에, 기준에 맞추어 보고싶은 항목을 '값' 부분에 선택하여 넣어줍니다.

과일 중 포도 판매량이 가장 많아서 포도에 대한 데이터만 보고 싶을 때는 필터 기능을 써줄 수 있습니다. 필터에 과일 필드를 넣어 주고, 포도만 필터를 걸어주면 포도를 구매한 고객과, 고객별 포도 구매 수량과 총 매출을 볼 수도 있습니다.

필터 적용

4단계: 계산 함수 변경

계산 함수 변경

값에 대한 내용은 기본적으로 개수 혹은 합계 데이터로 나오는데요, 때에 따라 평균이나, 최대/최소값 등이 필요할 수 있습니다. 그럴 때는 값 항목 옆에 (i)버튼을 누르면 팝업창이 뜨는데, 팝업창에서 함수를 변경해서 사용할 수도 있습니다.

피벗 안에 있는 항목들을 내가 원하는 수식으로 계산할 수 있는 방법도 있는데, 다음 글에서 작성해보도록 하겠습니다.

피벗테이블 실무 활용 예시

개념은 알겠는데, 실무에서 어떻게 쓰이는지 감이 잘 안 올 수 있어서 몇 가지 실전 활용 사례를 정리해보았습니다.

월별/분기별 매출 추이 분석

가장 흔하게 쓰이는 활용법입니다. 날짜 데이터가 포함된 매출 내역이 있다면, 행에 날짜를 넣고 그룹화 기능을 사용해서 월별 또는 분기별로 묶어볼 수 있습니다. 예를 들어 1년치 일별 매출 데이터가 365행이나 되더라도, 그룹화 한 번이면 12개 월로 깔끔하게 정리되죠. 여기서 열에 '상품 카테고리'를 추가하면, 월별 × 카테고리별 매출 매트릭스가 바로 만들어집니다. 일별로 흩어져 있던 데이터가 한눈에 트렌드로 보이기 때문에, "이번 분기 매출이 왜 떨어졌지?" 같은 질문에 빠르게 답을 찾을 수 있습니다.

월별 매출 추이 분석 - Raw 데이터에서 피벗테이블로 변환되는 과정

카테고리별 / 담당자별 실적 비교

행에 카테고리나 담당자를, 값에 매출이나 건수를 넣으면 바로 실적 비교표가 완성됩니다. 저는 실무에서 광고 매체별 성과를 비교할 때 이 방법을 많이 사용했는데요, 행에 매체명을 넣고 값에 노출수, 클릭수, 전환수를 동시에 넣으면 매체별 성과 비교표가 수식 하나 없이 바로 나옵니다. 여기에 열을 추가하면 월별 × 담당자별 또는 매체별 × 기간별 매트릭스도 바로 만들어볼 수 있어요. 보고서 만들 때 SUMIFS로 하나하나 조건 걸어가며 표를 만들던 시절이 떠오르면 피벗테이블의 소중함을 느끼게 됩니다.

고객 세그먼트 분석

구매 횟수나 구매 금액대별로 고객을 분류하고 싶을 때도 피벗테이블이 편리합니다. 예를 들어 구매 금액을 행에 넣고 그룹화를 사용해서 "05만원, 510만원, 10만원 이상" 같은 구간으로 나누면, 각 금액대별 고객 수와 매출 비중을 한눈에 볼 수 있습니다. 값 필드의 표시 형식을 '비율'로 바꾸면 전체 매출에서 각 세그먼트가 차지하는 비중도 바로 파악할 수 있고요. 이런 분석은 VIP 고객 관리 전략을 세울 때 정말 유용합니다.

피벗테이블을 더 잘 쓰기 위한 팁

날짜 그룹화 활용하기

날짜 필드를 행에 넣으면 기본적으로 하루 단위로 나오는데요, 이러면 데이터가 너무 많아서 트렌드를 파악하기 어렵습니다. 날짜 셀 아무 곳이나 우클릭하고 '그룹화'를 선택하면 월, 분기, 연도 단위로 묶을 수 있습니다. 여기서 꿀팁 하나! 그룹화할 때 '월'과 '연도'를 동시에 선택하면, 연도별 × 월별로 깔끔하게 정리됩니다. '월'만 선택하면 여러 해의 같은 월 데이터가 합쳐져 버리니까 주의하세요.

날짜 그룹화 전후 비교 - 365행에서 12행으로 깔끔하게 정리

슬라이서로 인터랙티브하게 만들기

피벗테이블 메뉴에서 '슬라이서 삽입'을 하면, 버튼 형태의 필터를 만들 수 있습니다. 드롭다운 필터와 비교했을 때 훨씬 시각적으로 직관적이고, 현재 어떤 항목이 선택되어 있는지 한눈에 보입니다. 더 좋은 건, 하나의 슬라이서를 여러 피벗테이블에 동시에 연결할 수 있다는 점인데요, 매출 피벗테이블과 주문 건수 피벗테이블을 나란히 놓고 슬라이서로 카테고리를 클릭하면 둘 다 동시에 필터링되기 때문에 엑셀만으로도 꽤 그럴듯한 대시보드를 만들 수 있습니다.

슬라이서를 활용한 대시보드 개념도 - 하나의 슬라이서로 여러 피벗테이블 동시 필터링

피벗 차트 연동하기

피벗테이블을 선택한 상태에서 '피벗 차트'를 삽입하면, 피벗테이블과 연동된 차트가 만들어집니다. 이 차트가 일반 차트와 다른 점은, 피벗테이블의 필터나 행/열 구성을 바꾸면 차트도 자동으로 업데이트된다는 것입니다. 보고서에 차트까지 넣어야 할 때 별도로 데이터 범위를 잡고 차트를 만들 필요 없이 클릭 몇 번이면 완성되고, 데이터가 추가되어도 새로고침만 하면 차트까지 자동 반영되니 유지보수도 편합니다.

피벗테이블 사용 시 주의사항

피벗테이블은 편리하지만, 원본 데이터가 깔끔하지 않으면 제대로 작동하지 않습니다. 몇 가지 주의할 점을 알아두세요.

자주 묻는 질문

피벗테이블에 데이터가 안 나오는데, 왜 그런 건가요?
가장 흔한 원인은 원본 데이터에 빈 행이 있거나, 헤더가 없는 경우입니다. 데이터 범위가 올바르게 선택되었는지 확인하고, 중간에 빈 행이 없는지 점검해보세요. 또한 원본 데이터를 수정한 후에는 피벗테이블에서 우클릭 → 새로고침을 해야 반영됩니다.
피벗테이블에서 날짜를 월별로 묶으려면 어떻게 하나요?
행에 날짜 필드를 넣은 뒤, 날짜 셀 아무 곳이나 우클릭하고 '그룹화'를 선택하면 됩니다. 월, 분기, 연도 등 원하는 단위를 선택할 수 있습니다. 단, 날짜 형식이 텍스트로 저장되어 있으면 그룹화가 작동하지 않으니, 셀 서식이 '날짜'인지 먼저 확인하세요.
피벗테이블 vs SUMIFS, 어떤 걸 써야 하나요?
고정된 기준으로 한두 가지 조건만 보려면 SUMIFS가 간편합니다. 하지만 기준을 자주 바꿔가며 여러 관점에서 데이터를 탐색해야 하는 경우에는 피벗테이블이 훨씬 효율적입니다. 피벗테이블은 드래그 앤 드롭으로 즉시 기준을 변경할 수 있어, 탐색적 분석에 적합합니다.
피벗테이블로 만든 결과를 다른 시트에서 참조할 수 있나요?
GETPIVOTDATA 함수를 사용하면 피벗테이블의 값을 다른 셀에서 참조할 수 있습니다. 피벗테이블 셀을 클릭해서 수식을 만들면 자동으로 이 함수가 입력됩니다. 다만, 피벗테이블 구조가 바뀌면 참조가 깨질 수 있으니 주의하세요.

이 글과 함께 읽으면 좋은 글: