엑셀 피벗테이블쯤은 사용해야지!
데이터 분석 꿀팁 모음
보고서·현황표를 10배 빠르게 만드는 피벗테이블 실전 가이드예요.
- 피벗테이블이란? 언제 쓰면 좋을까
- 피벗테이블 만들기 — 5단계로 끝내기
- 꼭 알아야 할 꿀팁 6가지
- 피벗테이블과 함께 쓰면 강력한 기능들
- 초보자가 자주 실수하는 것들
피벗테이블은 긴 데이터를 원하는 기준으로 자동 집계·요약해주는 기능입니다. 함수를 전혀 몰라도, 드래그&드롭만으로 월별 매출 집계, 부서별 비용 현황, 제품별 판매량 비교 같은 보고서를 몇 분 안에 만들 수 있어요.
- 수백~수천 행의 데이터를 특정 기준으로 합계·평균·개수로 요약하고 싶을 때
- 월별, 부서별, 제품별 등 기준을 바꿔가며 여러 방식으로 분석하고 싶을 때
- SUMIF, COUNTIF 함수 없이 클릭만으로 집계 보고서를 뽑고 싶을 때
- 원본 데이터는 그대로 두고 별도 요약 시트를 자동 생성하고 싶을 때
처음이라도 아래 순서만 따라 하면 누구나 만들 수 있어요.
- 데이터 범위 선택 후 Ctrl+T를 누르면 엑셀 표로 변환돼요.
- 표로 만들어두면 나중에 데이터가 추가돼도 피벗테이블 범위가 자동으로 확장됩니다.
- 새로운 데이터 추가 후 피벗테이블 우클릭 → [새로 고침]만 누르면 반영 완료!
날짜 필드를 행 영역에 넣으면 일별로 길게 늘어나서 보기 불편해요. 날짜 셀 하나를 우클릭 → [그룹]을 선택하면 월·분기·연도 단위로 자동 묶어줍니다.
예: 일자별 매출 데이터 → 월별 매출 집계표로 1초 만에 변환 가능합니다.
합계 금액 대신 "전체 대비 몇 %인지" 보고 싶다면 값 필드 설정에서 바꿀 수 있어요.
값 영역 클릭 → [값 필드 설정] → [값 표시 형식] 탭 → 열 합계 기준 % / 행 합계 기준 % 선택. 부서별 비용 비중, 제품별 매출 구성비 보고서가 바로 완성됩니다.
피벗테이블 선택 → [피벗테이블 분석] 탭 → [슬라이서 삽입]을 누르면 원하는 필드의 필터 버튼이 생겨요.
예: 부서 슬라이서를 만들면 "영업팀" 버튼을 클릭할 때마다 해당 부서 데이터만 표시됩니다. 보고 자료 프레젠테이션에서 특히 유용합니다.
피벗테이블을 선택한 상태에서 Alt+F1을 누르면 현재 시트에 피벗 차트가 즉시 생성됩니다. 피벗 차트는 피벗테이블과 연동되어, 슬라이서나 필터를 바꾸면 차트도 자동으로 업데이트됩니다.
피벗테이블에 빈 셀이 생기면 지저분해 보여요. 피벗테이블 우클릭 → [피벗테이블 옵션] → '빈 셀 표시' 란에 0을 입력하면 모든 빈 셀이 0으로 채워집니다.
피벗테이블의 특정 셀 값을 다른 시트에서 참조하고 싶을 때 쓰는 함수예요. 피벗테이블 셀을 클릭하면 자동으로 수식이 생성됩니다.
| 기능 | 조합 효과 | 사용 난이도 |
|---|---|---|
| 조건부 서식 | 피벗테이블 값에 색상 히트맵 적용 → 한눈에 높은 값/낮은 값 파악 | ★★☆☆☆ |
| 슬라이서 + 차트 | 슬라이서 버튼 클릭 시 차트까지 실시간 변경 → 인터랙티브 보고서 | ★★☆☆☆ |
| 타임라인 | 날짜 기반 슬라이서 → 기간을 마우스로 드래그해 필터링 | ★★☆☆☆ |
| 계산 필드 | 피벗 내에서 직접 수식 추가 (예: 영업이익률 = 이익/매출) | ★★★☆☆ |
| 파워 쿼리 | 여러 시트·파일의 데이터를 합쳐 피벗에 연결 → 대용량 분석 | ★★★★☆ |
- 부서 슬라이서 + 날짜 타임라인을 함께 만들면 "3월 영업팀 실적"을 버튼 클릭 2번으로 볼 수 있어요.
- 이 조합으로 만든 파일을 팀장에게 제출하면 스스로 조회할 수 있는 셀프서비스 보고서가 됩니다.
- 원본 데이터에 빈 행·빈 열이 있으면 피벗테이블 범위가 잘려요. 데이터 준비 단계에서 꼭 제거하세요.
- 셀 병합은 절대 금지. 병합된 셀이 있으면 집계 오류가 납니다. 병합 해제 후 빈 셀을 채워야 해요.
- 숫자가 텍스트로 입력된 경우 합계가 0으로 나와요. 셀 왼쪽 상단에 작은 녹색 삼각형이 보이면 텍스트로 저장된 숫자예요 — 숫자로 변환 후 새로 고침하세요.
- 원본 데이터를 추가한 뒤 새로 고침을 안 하면 피벗테이블이 업데이트되지 않아요. 우클릭 → [새로 고침] 또는 Alt+F5를 습관화하세요.
- 피벗테이블 안에서 직접 값을 수정하려 하면 오류가 납니다. 값은 반드시 원본 데이터에서 수정 후 새로 고침하세요.
날짜 그룹화 + 슬라이서 + 차트 조합으로 인터랙티브 보고서 제작 가능
데이터 추가 후엔 항상 Alt+F5로 새로 고침!
※ 메뉴 위치와 기능명은 엑셀 버전(2016·2019·Microsoft 365)에 따라 다소 다를 수 있습니다.