엑셀 피벗테이블 사용법 완벽 가이드 - 클릭 몇 번으로 수천 줄 데이터 요약하기
함수 하나 몰라도 괜찮습니다. 마우스 드래그만으로 매출, 방문자, 후원 내역 같은 데이터를 표로 정리하는 피벗테이블 핵심 사용법을 처음부터 정리했습니다.
매달 정산표를 받아서 항목별로 일일이 더하고 있다면, 그 작업은 사실 클릭 몇 번이면 끝납니다. 수백 줄짜리 표를 보면서 'A상품 매출만 따로 합치고 싶은데' 하고 한숨 쉬어본 적 있으실 겁니다. 바로 그럴 때 쓰는 기능이 피벗테이블입니다. 함수도, VBA도 필요 없습니다.
피벗테이블이 필요한 순간
피벗테이블은 긴 표 형태의 원본 데이터를 항목별로 요약해 주는 도구입니다. 날짜, 상품명, 담당자, 지역처럼 반복되는 항목이 섞여 있는 데이터일수록 효과가 큽니다.
예를 들어 1년치 판매 기록 3,000줄이 있다고 해봅시다. 여기서 '월별 매출 합계'나 '상품별 판매 건수'를 알고 싶을 때, 손으로 SUMIF 함수를 짜지 않아도 됩니다. 항목을 드래그해서 영역에 놓기만 하면 즉시 표가 만들어집니다.
피벗테이블의 핵심은 '계산'이 아니라 '관점 바꾸기'입니다. 같은 데이터를 월별로 봤다가, 상품별로 봤다가, 지역별로 보는 일을 1초 만에 전환할 수 있습니다.
피벗테이블 만드는 기본 순서
버전(엑셀 2016 이상, 마이크로소프트 365)에 상관없이 기본 흐름은 같습니다.
- 1단계 - 원본 데이터의 아무 셀이나 클릭합니다. 표 전체를 드래그할 필요는 없습니다.
- 2단계 - 상단 메뉴에서 [삽입] - [피벗 테이블]을 누릅니다.
- 3단계 - 데이터 범위가 자동으로 잡힌 것을 확인하고 '새 워크시트'를 선택한 뒤 [확인]을 누릅니다.
- 4단계 - 오른쪽에 나타난 필드 목록에서 원하는 항목을 아래 네 영역으로 끌어다 놓습니다.
행·열·값·필터 영역 이해하기
피벗테이블을 이해하는 데 가장 중요한 건 네 개의 영역입니다. 이 구조만 잡으면 나머지는 응용입니다.
| 영역 | 역할 | 예시 |
|---|---|---|
| 행 | 표의 세로 항목으로 나열 | 상품명, 담당자 |
| 열 | 표의 가로 항목으로 나열 | 월, 분기 |
| 값 | 실제로 계산할 숫자 | 매출 합계, 건수 |
| 필터 | 전체 표를 조건으로 거름 | 지역=서울 |
예를 들어 '행'에 상품명, '열'에 월, '값'에 매출을 넣으면 가로축은 월, 세로축은 상품으로 된 매출 표가 완성됩니다. 같은 데이터인데 행과 열에 무엇을 두느냐에 따라 완전히 다른 표가 나오는 것이 피벗테이블 사용법의 핵심입니다.
집계 방식 바꾸고 정렬하기
'값' 영역에 숫자를 넣으면 기본값은 합계입니다. 그런데 합계가 아니라 개수나 평균이 필요할 때가 많습니다.
집계 방식 변경
값 영역에 올려둔 항목을 클릭한 뒤 '값 필드 설정'에 들어가면 합계, 개수, 평균, 최댓값, 최솟값 등을 고를 수 있습니다. 예를 들어 '주문 건수'가 궁금하면 개수로, '평균 단가'가 궁금하면 평균으로 바꾸면 됩니다.
정렬과 상위 항목 추리기
매출이 큰 순서대로 보고 싶다면 값이 표시된 열의 화살표를 눌러 내림차순 정렬을 합니다. 여기에 '값 필터'의 상위 10개 기능을 쓰면 매출 상위 10개 상품만 골라낼 수도 있습니다.
실무에서 바로 쓰는 활용 팁
기본기를 익혔다면 아래 기능들이 시간을 크게 줄여 줍니다.
- 슬라이서 - [피벗 테이블 분석] - [슬라이서 삽입]을 쓰면 버튼식 필터가 생깁니다. 보고용 대시보드에 특히 유용합니다.
- 데이터 새로 고침 - 원본 데이터가 바뀌면 피벗테이블을 우클릭해 '새로 고침'만 누르면 결과가 갱신됩니다. 표를 다시 만들 필요가 없습니다.
- 피벗 차트 - 만든 표를 그대로 그래프로 바꿔 줍니다. 월별 추이를 시각화할 때 편리합니다.
참고로 보고서나 블로그용으로 표 내용을 텍스트로 옮길 때 분량을 맞춰야 한다면, 글자수 세기 같은 도구로 공백 포함 글자 수를 빠르게 확인할 수 있습니다. 정리한 데이터를 고객에게 메시지로 안내해야 한다면 TodayDM 같은 발송 서비스를 활용하는 식으로 업무 흐름을 이어 가면 됩니다.
자주 하는 실수와 해결법
처음 쓸 때 막히는 지점은 대부분 정해져 있습니다.
- 숫자인데 합계가 안 된다 - 셀이 텍스트 형식으로 저장된 경우입니다. 원본에서 해당 열을 숫자 형식으로 바꾼 뒤 새로 고침하세요.
- 새 데이터가 반영 안 된다 - 추가된 행이 원래 범위 밖에 있어서입니다. 원본을 '표'(Ctrl+T)로 만들어 두면 행이 늘어도 범위가 자동 확장됩니다.
- 빈 셀이 0이 아닌 빈칸으로 보인다 - 피벗테이블 옵션에서 '빈 셀 표시'에 0을 입력하면 깔끔하게 정리됩니다.
피벗테이블은 한 번 손에 익으면 매달 반복하던 정산과 집계 시간을 크게 줄여 줍니다. 오늘은 두 가지만 직접 해보세요. 첫째, 가지고 있는 표 하나로 [삽입] - [피벗 테이블]을 눌러 행과 값 영역에 항목을 끌어다 놓아 보기. 둘째, 그 표를 우클릭해 집계 방식을 합계에서 개수로 바꿔 보기. 이 두 동작만 익혀도 데이터를 보는 눈이 달라집니다.
