들어가며
엑셀에는 여러 가지 분석 도구가 있습니다. 정렬, 필터, 부분합, 데이터 통합, 차트 등 일일이 열거하기도 벅찰 정도죠. 그 중 군계일학 같은 존재가 있으니, 바로 '피벗 테이블(Pivot Table)'입니다. 피벗 테이블은 방대한 데이터를 효율적으로 분석하고 요약할 수 있는 기능입니다. 강력한 피벗 테이블의 세계로 출발합니다.
피벗 테이블 기본과 응용
엑셀에 있는 수많은 분석 도구, 데이터 집계 방법 중 최고는 피벗 테이블입니다. 원하는 결과를 그만큼 쉽고 빠르게 만들 수 있도록 도와줍니다.
[참고] 엑셀 버전에 따라 메뉴 구조나 명칭이 다를 수 있습니다만, 전후 문맥을 잘 살펴보면 해결할 수 있습니다.
(1) 피벗 테이블을 만들 데이터 내부의 셀을 클릭하고 [삽입] 탭 - [표] 그룹 - [피벗 테이블]을 선택합니다.
(2) 피벗 테이블 대화상자가 나타납니다. [표/범위]가 자동으로 지정됩니다. 피벗 테이블을 작성할 위치는 새로운 위크시트나 기존 테이블 중 선택할 수 있습니다. 여기서는 원본 데이터와의 비교를 위해 [기존 워크시트]를 선택하고 G3 셀을 클릭합니다.
(3) [확인] 버튼을 클릭하면 화면 오른쪽에 [피벗 테이블 필드] 작업창이 나타납니다. 여기에서 피벗 테이블의 레이아웃을 설정할 수 있습니다. 필드(품목, 날짜, 수량 등)를 원하는 영역으로 끌어다 놓으면 됩니다.
품목별로 수량과 금액을 집계해 보겠습니다. '품목'을 '행' 영역으로, '수량'과 '금액'을 '값' 영역으로 각각 드래그합니다.
필드를 영역으로 드래그하는 것만으로 피벗 테이블을 만들 수 있습니다. 이처럼 피벗 테이블은 딱 단계만 알면 만들 수 있습니다. 수많은 현란한 내용은 기본에 바탕을 둔 변주일 따름입니다.
피벗 테이블 필드 제거하는 방법
피벗 테이블 필드를 제거하는 것은 추가하는 것보다 더 간단합니다. '수량' 필드를 제거해 볼까요? '값' 영역에 있는 '수량'을 클릭하고 [필드 제거'를 선택하면 피벗 테이블에서 해당 필드가 제거됩니다.
필드를 추가하고 서식 지정하기
원본 데이터에는 없는 평균을 피벗 테이블에 추가해 보겠습니다.
(1) 금액 필드를 '값' 영역으로 드래그하여 추가합니다. 같은 필드를 여러 번 추가하는 것도 가능합니다.
(2) '금액2' 필드를 클릭하고 [값 필드 설정]을 누릅니다.
(3) [값 필드 설정] 대화상자에서 [선택한 필드의 데이터] - [평균]을 선택합니다. 필요에 따라 [사용자 지정 이름]의 텍스트 상자에 원하는 이름을 입력할 수 있습니다.
(4) [확인] 버튼을 클릭합니다. 기존의 '금액' 오른쪽에 '평균'이 새롭게 추가되었습니다. 숫자가 들어 있는 영역을 범위로 지정하고 쉼표 스타일을 적용하여 완성합니다.
피벗 테이블 사용 시 주의할 점
피벗 테이블을 작성한 이후 원본 데이터가 변경되더라도 기존 피벗 테이블에 자동으로 반영되지 않습니다. 원본 데이터가 변경되었다면 피벗 테이블을 새로 고침해야 하고, 데이터가 추가되었다면 피벗 테이블 원본 범위를 수정해야 합니다.
데이터 새로 고침
피벗 테이블 내부에 있는 임의의 셀을 우클릭하고 [새로 고침]을 선택합니다.
원본 데이터 범위 변경
- 피벗 테이블 내부의 셀을 하나 선택하고 [피벗 테이블 분석] 탭 - [데이터] 그룹 - [데이터 원본 변경]을 클릭합니다.
- [피벗 테이블 데이터의 원본 변경] 대화상자에서 [표/범위]를 수정하고 [확인] 버튼을 누릅니다.
데이터를 새로 고침하거나 원본 데이터 범위를 변경하는 것은 어렵지 않습니다만, 원본 데이터에 변경이 생겼다면 잊지 말고 업데이트를 해 주어야 한다는 점을 반드시 기억해 두세요.
슬라이서—피벗 테이블 전용 필터
슬라이서(Slicer)는 원래 빵이나 베이컨 같은 것을 자를 때 사용하는 칼을 말합니다. 엑셀에서 슬라이서는 피벗 테이블을 위한 전용 필터입니다. 슬라이서를 이용하면 피벗 테이블에서 원하는 데이터를 쉽게 볼 수 있습니다.
(1) 피벗 테이블을 선택한 다음, [피벗 테이블 분석] 탭 - [필터] 그룹 - [슬라이서 삽입]을 선택합니다.
(2) [슬라이서 삽입] 대화상자에서 [품목]에 체크를 하고 [확인] 버튼을 누릅니다.
(3) 품목 슬라이서를 이용하여 원하는 품목을 필터링할 수 있습니다. 특정 품목을 선택할 때마다 실시간으로 피벗 테이블이 필터링됩니다. Ctrl이나 Shift 키를 이용하여 여러 항목을 선택할 수도 있습니다. 필터 상태를 해제하려면 슬라이서 우측 상단에 있는 [필터 해제] 아이콘(깔대기 모양)을 클릭합니다.
슬라이서 제거하는 방법
대개의 일이 그러하듯, 만드는 것보다 허무는 것이 쉽습니다. 슬라이서를 제거하는 방법 또한 간단합니다. 제거하고자 하는 슬라이서를 선택하고 Delete 키를 누르면 됩니다.
피벗 차트—피벗 테이블 시각화 도구
피벗 테이블을 이용하여 피벗 차트를 만들 수 있습니다. 몇 번의 마우스 클릭만으로 말이죠.
(1) 피벗 테이블 내부의 아무 곳이나 선택하고 [삽입] 탭 - [차트] 그룹 - [묶은 세로 막대형]을 선택합니다.
[주의] 필자의 경우 Microsoft 365 버전을 사용하고 있습니다. 사용하는 엑셀 버전에 따라 리본의 위치가 달라질 수 있습니다(예: [피벗 테이블 분석] 탭 - [도구] 그룹 - [피벗 차트])
(2) 피벗 차트를 선택하면 리본 오른쪽에 새로운 탭이 나타납니다. [디자인] 탭 - [차트 레이아웃] 그룹 - [빠른 레이아웃]에서 적당한 레이아웃을 선택합니다.
(3) [피벗 차트 필드] 작업창을 이용하여 추가로 작업을 할 수 있습니다. 피벗 차트가 일반 차트와 다른 점 중 하나는 여기에 있습니다. 필드를 재배치하면 실시간으로 피벗 테이블과 피벗 차트에 반영됩니다.
그 밖에 피벗 테이블 관련 참고 자료 (동영상)
피벗 테이블은 한 권의 책으로 다루어도 부족할 정도로 기능이 방대합니다. 이번 시간에 소개해 드린 내용은 가장 기본적이고 필수적인 내용들이라고 생각하시면 되겠습니다. 추가로 공부해 두면 좋을 영상 몇 개를 추천해 드리는 것으로 마무리 하고자 합니다. 입문 수준을 넘을 수 있으므로 기억해 뒀다가 나중에 보세요 (일부 영상은 배경 음악이 클 수 있습니다).
💎 엑셀에서 기간 비교를 쉽게 할 수 있는 슬라이서 만들기
💎 이것 모르면 나만 손해 ㅡ 엑셀 피벗 테이블 활용 팁 3가지
💎 피벗 테이블에 숨겨진 기능 3가지 ㅡ Pivot Table에서 증감, 순위 표시
나가며
엑셀의 강력한 분석 도구 피벗 테이블까지 알게 되었습니다. [Excel 입문] 정규 강의는 이번 시간이 마지막입니다. 아직은 끝이 아닙니다. 에필로그 시간이 남았습니다. 그동안의 소회와 몇 가지 추가 학습 자료 등 몇 가지가 남았습니다. 인사는 그때 나누도록 하죠. 피벗 테이블을 아시게 된 것, 축하합니다.
'Excel' 카테고리의 다른 글
| Power BI를 Excel로 내보내기 하는 방법 (6) | 2024.04.30 |
|---|---|
| [Excel 입문] 19. 에필로그 (7) | 2024.04.27 |
| TOP 3 자동 강조 Excel 차트 만드는 방법 (94) | 2024.04.23 |
| [Excel 입문] 17. 데이터 통합과 부분합—3차원 참조의 한계 극복 (7) | 2024.04.22 |
| [Excel 입문] 16. 조건에 맞는 데이터 자동 강조하기—조건부 서식 (42) | 2024.04.15 |
