엑셀 고급 · 대화형 레슨 12개 중 5번째
고급 피벗 테이블
기초 과정과 중급 과정에서 피벗 테이블(PivotTable)을 만들어 봤어요. 이번에는 피벗 테이블이 무엇을 보여 줄지 직접 조절해요. 날짜별이 아니라 분기별로 묶고, 단순 합계 대신 전체 대비 비율로 보여 주고, 실적이 가장 좋은 항목만 남기도록 필터링하는 거예요.
화면 기준: Windows용 Microsoft 365 Excel. 연습은 어느 브라우저에서나 할 수 있어요.
글로 보는 튜토리얼엑셀 고급 피벗 테이블: 그룹화, 값 표시 형식, 계산 필드, 상위 N 필터실력 점검
먼저 해 보세요:
- 피벗 테이블 안에서 날짜 열을 분기 단위로 그룹화해요.
- "합계 : Revenue"를 총합계 대비 비율로 표시되도록 바꿔요.
- 피벗 테이블을 값 기준 상위 3개 행만 남도록 필터링해요.
- 피벗 테이블의 숫자가 새로 고침(Refresh)하기 전까지 바뀌지 않는 이유를 설명해요.
이미 다 아는 내용인가요? A6 — 파워 쿼리 기초로 건너뛰세요.
개념 익히기
이번 레슨에서 배울 내용
- 날짜 그룹화
- 행 영역(Rows area)에 있는 날짜 필드를 마우스 오른쪽 단추로 클릭하고 그룹(Group) > 분기(또는 월, 연)를 선택하세요. 18개의 개별 날짜가 몇 개의 깔끔한 구간으로 정리돼요.
- 값 표시 형식(Show Values As)
- 단순 합계를 넘어서 볼 수 있어요. "열 합계 비율(% of Column Total)"을 선택하면 각 행이 전체에서 차지하는 비중으로 바뀌어요. 누군가 "얼마나 팔았어?"가 아니라 "매출에서 그게 몇 퍼센트야?"라고 묻는 순간 바로 쓸모가 있어요.
- 계산 필드(Calculated Field)
- 피벗 테이블 안에서 다른 필드들로 계산하는 필드예요. 예를 들어 Gross Margin = Revenue − Cost처럼요. 원본 데이터에 열을 추가할 필요가 없어요.
- 상위 N 필터
- 값 필터(Value Filters) > 상위 10(Top 10)을 사용하세요. 이름은 상위 10이지만 개수와 기준 값은 마음대로 정할 수 있어서, "매출 기준 상위 3개"도 똑같이 가능해요.
- 피벗 캐시 새로 고침
- 피벗 테이블은 실시간 원본이 아니라 캐시에 저장된 스냅숏을 읽어요. 그래서 원본 데이터를 수정해도 새로 고침을 누르기 전까지는 아무 변화가 없어요.
엑셀에서 찾는 위치
피벗 테이블 분석›필드 그룹화 · 필드, 항목 및 집합 · 새로 고침
자동 저장 Book1.xlsx · 저장됨 검색–□✕
파일홈삽입페이지 레이아웃수식데이터검토보기자동화도움말피벗 테이블 분석
활성 필드:합계 : 매출
필드 설정
활성 필드
선택 항목 그룹화
그룹 해제
필드 그룹화
그룹
새로 고침
데이터 원본 변경
데이터
필드, 항목 및 집합
OLAP 도구
관계
계산
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | 행 레이블 | 합계 : 매출 | 총합계 비율 | |||||
| 2 | Qtr1 | 15,900 | 22.4% | |||||
| 3 | Qtr2 | 18,240 | 25.7% | |||||
| 4 | Qtr3 | 17,610 | 24.8% | |||||
| 5 | Qtr4 | 19,300 | 27.2% | |||||
| 6 | 총합계 | 71,050 | 100.0% | |||||
| 7 | ||||||||
| 8 |
◀ ▶Sheet1+
준비접근성: 계속 진행 가능▦ ▤ ▥ — + 100%
옆으로 밀어 창 전체를 볼 수 있어요.
- 1필드 그룹화로 날짜를 월, 분기, 연도로 묶어요.
- 2필드 설정 › 값 표시 형식에서 총합계 비율, 누계 등을 골라요.
- 3필드, 항목 및 집합 › 계산 필드로 수식으로 만든 필드를 추가해요.
- 4새로 고침(Alt+F5)은 원본이 바뀐 뒤 피벗 캐시를 다시 읽어요.
- 5행 레이블 화살표 › 값 필터 › 상위 10으로 상위 N개만 보여요.
Microsoft 365용 Excel(Windows) 화면을 재현한 그림입니다. Mac, 웹용 Excel 또는 좁은 창에서는 리본 배치가 조금 다를 수 있습니다.
따라 만들기
두 분기에 걸친 거래 18건이에요. 분기별 또는 지역별로 묶어 보고, 값 표시 방식을 바꿔 보고, 실적이 가장 좋은 항목만 남겨 보세요.
혼자 응용하기
내 데이터 요약하기
실제 판매 데이터나 활동 데이터로 피벗 테이블을 만들어 보세요. 월별 또는 분기별로 요약하고, 측정 값 하나를 전체 대비 비율로 표시하고, 상위 N 필터를 추가해 눈에 띄는 항목만 드러내 보세요. 그다음 마진이나 비율 같은 계산 필드를 추가하고, 피벗 테이블 밖에서 만든 수식과 결과를 비교해 확인해 보세요.
이 레슨에서 틀린 부분을 찾았거나 제안이 있나요? 의견 보내기