Ch4. 엑셀 기초~고급 — 피벗테이블과 차트

피벗테이블이 왜 강력한가?

수천 행의 데이터를 수식 없이 몇 초 만에 집계·분석할 수 있는 도구입니다. 영업 실적 분석, 부서별 비용 집계, 월별 매출 추이 등 실무에서 가장 많이 쓰이는 분석 도구입니다.

피벗테이블 생성

데이터 준비 조건

피벗테이블을 만들기 전 데이터가 아래 조건을 충족해야 합니다.

조건	설명
첫 행이 헤더	열 제목이 명확히 있어야 함
빈 행·열 없음	데이터 중간에 공백 없어야 함
병합 셀 없음	병합 셀은 피벗 분석 불가
일관된 데이터 형식	날짜 열은 모두 날짜, 숫자 열은 모두 숫자

생성 순서

1. 데이터 내 아무 셀 클릭
2. 삽입 → 피벗테이블
3. 범위 확인 (자동 감지) → 새 시트에 배치 권장
4. 필드 패널에서 원하는 필드 드래그

4가지 영역 이해

┌─────────────────────────────────┐
│           필터 영역              │  ← 전체 데이터 필터 (예: 연도)
├─────────────┬───────────────────┤
│   행 영역    │     값 영역        │  ← 집계값 (합계·개수·평균)
│  (행 레이블) │  (숫자 집계)       │
├─────────────┴───────────────────┤
│           열 영역                │  ← 가로 축 (예: 분기)
└─────────────────────────────────┘

영역	역할	예시
행	세로 축 그룹핑	지역, 부서, 제품명
열	가로 축 그룹핑	분기, 월, 연도
값	집계 계산	매출 합계, 수량 평균
필터	전체 보기 필터	특정 연도만 보기

피벗테이블 실전 설정

값 집계 방식 변경

기본은 합계(SUM)이지만 다양하게 바꿀 수 있습니다.

값 영역의 필드명 클릭 → 값 필드 설정
- 합계, 개수, 평균, 최대, 최소, 표준편차 선택 가능
- "값 표시 형식" → 총합계 대비 %, 행 대비 % 등 설정

날짜 그룹핑

날짜 필드를 행/열에 넣으면 자동으로 연·분기·월 단위로 그룹핑됩니다.

날짜 필드 우클릭 → 그룹화
→ 연도, 분기, 월, 주, 일 선택
→ 복수 선택 시 계층 구조 생성

정렬 및 상위 N개 필터

행 레이블 우클릭 → 정렬 → 내림차순 (매출 높은 순)
행 레이블 우클릭 → 필터 → 값 기준 상위 10개

슬라이서 (Slicer)

클릭 한 번으로 피벗테이블을 필터링하는 버튼 UI입니다.

슬라이서 삽입

피벗테이블 선택 → 피벗테이블 분석 탭 → 슬라이서 삽입
→ 필터로 사용할 필드 선택 (예: 지역, 제품 카테고리)

여러 피벗테이블 연결

슬라이서 우클릭 → 보고서 연결
→ 같은 데이터 원본을 쓰는 다른 피벗테이블과 동기화 가능
→ 대시보드에서 여러 차트를 한 번에 필터링

타임라인 (Timeline)

날짜 필드에 특화된 슬라이서입니다.

피벗테이블 선택 → 피벗테이블 분석 탭 → 타임라인 삽입
→ 연도·분기·월·일 단위로 기간 선택 가능
→ 드래그로 기간 범위 조정

피벗차트

피벗테이블과 연동되는 동적 차트입니다.

생성 방법

피벗테이블 선택 → 피벗테이블 분석 탭 → 피벗차트
→ 차트 종류 선택 → 삽입

특징:

피벗테이블 변경 시 차트 자동 업데이트
슬라이서·타임라인과 연동
필드 버튼으로 차트 내에서 바로 필터 가능

일반 차트 — 종류와 선택 기준

차트 종류별 용도

차트 종류	최적 용도	예시
세로 막대	항목 간 비교	부서별 매출 비교
가로 막대	긴 레이블 항목 비교	제품명이 긴 경우
꺾은선	시간 흐름에 따른 추이	월별 매출 트렌드
원형(파이)	전체 대비 구성 비율	시장 점유율
도넛	원형 + 중앙 텍스트	KPI 달성률
영역	누적 추이	제품별 누적 매출
분산형	두 변수의 상관관계	광고비 vs 매출

차트 만들기 기본 절차

1. 데이터 범위 선택 (헤더 포함)
2. 삽입 → 차트 → 종류 선택
3. 차트 제목, 축 레이블, 범례 편집
4. 차트 스타일/색상 조정

이중 축 차트 (콤보 차트)

단위가 다른 두 데이터를 한 차트에 표현할 때 사용합니다.

예시: 월별 매출(억 원)과 성장률(%) 동시 표현

생성 방법:
1. 두 데이터 계열이 포함된 차트 생성
2. 보조 축으로 표시할 계열 우클릭
3. "데이터 계열 서식" → "보조 축"
4. 해당 계열 차트 종류 변경 (예: 꺾은선으로)

콤보 차트 직접 생성

삽입 → 차트 → 콤보
→ 각 계열의 차트 종류와 축(기본/보조) 설정

스파크라인 — 셀 내 미니 차트

단일 셀 크기의 초소형 트렌드 차트입니다.

삽입 → 스파크라인 → 꺾은선/세로막대/승패
→ 데이터 범위와 표시할 위치 선택

활용: 테이블 옆 열에 각 행의 추세를 시각화

차트 꾸미기 — 실무 팁

빠른 서식 적용

차트 선택 → 오른쪽 +버튼:
- 차트 요소 추가/제거 (제목, 데이터 레이블, 눈금선 등)
차트 선택 → 오른쪽 붓 아이콘:
- 차트 스타일, 색상 테마 변경

데이터 레이블 추가

차트 우클릭 → 데이터 레이블 추가
레이블 클릭 → 레이블 서식 → 표시 옵션 선택
(값, 계열명, 범주명, 퍼센트 등)

차트 축 서식

축 더블클릭 → 축 서식
- 최소/최대값 고정 (자동 범위가 마음에 안 들 때)
- 표시 단위 변경 (1000단위로 표시 시 "천")
- 날짜 축 간격 설정

조건부 집계 피벗 활용 예시

월별 부서별 매출 분석 순서:

1. 원본 데이터 준비:
   날짜 | 부서 | 담당자 | 매출금액

2. 피벗테이블 설정:
   행: 날짜 (월 단위 그룹핑)
   열: 부서
   값: 매출금액 (합계)

3. 슬라이서: 담당자 필터 추가

4. 피벗차트: 세로 막대 + 꺾은선 콤보
   (막대: 각 부서 매출, 꺾은선: 전월 대비 성장률)

실전 퀴즈 5문항

Q1. 피벗테이블에서 날짜 필드를 “행” 영역에 배치하면 기본적으로 어떻게 그룹핑되는가?

① 하루 단위로만 표시된다
② 연도·분기·월 단위로 자동 그룹핑된다
③ 아무 변화 없이 날짜 목록 그대로 표시된다
④ 오류가 발생한다

정답: ② (Excel이 날짜 필드를 자동으로 계층 그룹핑)

Q2. 슬라이서와 일반 필터의 차이로 옳은 것은?

① 슬라이서는 숫자 필드에만 사용 가능하다
② 슬라이서는 여러 피벗테이블에 동시 연결이 가능하다
③ 일반 필터가 더 많은 옵션을 제공한다
④ 슬라이서는 날짜 필드에만 사용 가능하다

정답: ② (슬라이서의 핵심 장점 — 보고서 연결 기능)

Q3. 이중 축 차트가 필요한 상황으로 가장 적절한 것은?

① 같은 제품의 색상별 판매량 비교
② 월별 매출액(억 원)과 고객 만족도(1~5점)를 함께 표시
③ 지역별 매출 순위 표시
④ 연도별 매출 성장 추이

정답: ② (단위가 다른 두 지표를 비교할 때 이중 축 사용)

Q4. 피벗테이블에서 매출 합계 대신 “행 합계 대비 %” 를 표시하려면?

① 값 영역에 ”%” 기호를 직접 입력한다
② 값 필드 설정 → 값 표시 형식 → “행 합계 대비 %”
③ 조건부 서식을 적용한다
④ SUMIF 수식으로 별도 계산해야 한다

정답: ②

Q5. 스파크라인에 대한 설명으로 옳은 것은?

① 일반 차트와 동일하게 별도 시트에 생성된다
② 단일 셀 내부에 표시되는 초소형 차트다
③ 최대 100개의 데이터 계열을 지원한다
④ 인쇄 시 출력되지 않는다

정답: ②

OIYO 편집부