목차
엑셀 피벗 테이블로 복잡한 데이터 쉽게 분석하는 방법
피벗 테이블은 엑셀에서 제공하는 강력한 데이터 분석 도구로, 대량의 데이터를 요약하고 다양한 관점에서 분석할 수 있게 해 줍니다. 이번 포스팅에서는 피벗 테이블의 개념과 주요 기능, 그리고 그 장점에 대해 설명합니다. 또한, 피벗 테이블을 만드는 방법과 이를 통해 데이터를 요약하는 방법을 안내합니다. 더불어, 필터링과 정렬하는 방법을 소개하고, 실전 예제로 피벗 테이블 차트를 만들어 보면서 데이터 정리와 분석이 얼마나 쉬워질 수 있는지 경험해 보세요.
1. 엑셀 피벗 테이블의 개념, 주요 기능과 장점
1-1. 피벗 테이블이란
엑셀 피벗 테이블은 대량의 자료를 효율적으로 요약하고 다양한 관점에서 분석할 수 있게 해주는 도구입니다. 원본 자료를 변형하지 않은 상태에서 행과 열을 기준으로 자료를 정렬하고 필터링할 수 있으며, 합계나 평균 등 다양한 방식으로 자료를 요약할 수 있습니다. 이를 통해 복잡한 자료를 간단히 요약하고 필요한 정보에 집중할 수 있어 자료 분석이 훨씬 더 효율적이고 간편해집니다.
1-2. 주요 기능과 장점
- 빠른 데이터 요약: 복잡한 자료를 단 몇 번의 클릭만으로 행, 열, 값별로 쉽게 요약하여 한눈에 파악할 수 있습니다.
- 다양한 분석 가능: 합계, 평균, 최대값 등 다양한 함수를 활용하여 자료를 분석하고, 원하는 정보를 추출할 수 있습니다.
- 시각화: 차트를 활용하여 자료를 시각적으로 표현하여 더욱 명확하게 이해할 수 있습니다.
- 효율성 증대: 반복적인 자료분석 작업을 자동화하여 시간과 노력을 절약할 수 있습니다.
2. 엑셀 피벗 테이블 만들기
2-1. 원본 데이터 준비하기
- 분석하고 싶은 자료를 엑셀 시트에 정리합니다.
- 각 변수는 별도의 열에 입력하고, 자료 형식을 일관되게 유지합니다.
2-2. 피벗 테이블 삽입 및 기본 설정
- [삽입] 탭 - [피벗 테이블]을 클릭합니다.
- 분석할 자료 범위를 선택하고, 새 시트 또는 기존 시트에 피벗 테이블을 삽입합니다.
3. 피벗 테이블로 데이터 요약하기
3-1. 행, 열, 값 필드 설정:
- 피벗 테이블의 필드 목록에서 필드를 드래그하여 행, 열, 값, 필터 영역에 배치합니다.
- 예를 들어, 제품명을 행 필드에, 판매 지역을 열 필드에, 판매 금액을 값 필드에 배치하면 제품별, 지역별 판매 금액을 요약할 수 있습니다.
3-2. 데이터 요약 및 집계 방법 설정:
- 값 필드에 자료를 배치하면 기본적으로 합계가 계산되지만, 값을 클릭하여 '값 필드 설정'을 통해 평균, 개수 등으로 변경할 수 있습니다.
- 예를 들어, 평균 판매 금액을 계산하거나, 판매 건수를 계산할 수 있습니다.
4. 피벗 테이블 필터링과 정렬
4-1. 필터를 사용한 데이터 분석
- 특정 조건에 맞는 자료만 선택적으로 분석할 수 있습니다.
- 예를 들어, 특정 제품의 판매 자료만 보고 싶다면 해당 제품명을 필터 필드에 추가하고 원하는 제품을 선택합니다.
- 특정 지역의 매출만 확인하거나, 특정 기간의 자료만 분석할 때 유용합니다.
4-2. 정렬 기능을 활용한 데이터 정리
- 데이터를 오름차순 또는 내림차순으로 정렬하여 원하는 정보를 빠르게 찾을 수 있습니다.
- 예를 들어, 판매 금액을 기준으로 자료를 정렬하여 가장 많이 팔린 제품 순서로 자료를 볼 수 있습니다.
- 매출액이 가장 높은 제품 순으로 정렬하면 중요한 자료를 쉽게 확인할 수 있습니다
5. 실전 예제: 피벗 테이블로 차트 만들기
실전 예제로 '소득공제' 표를 이용하여 주어진 조건에 맞는 피벗 테이블을 단계별로 작성해 보겠습니다. 소득공제 내용을 '필터', 소득공제를 '행', 법인명을 '열'로 설정하고 '값'에는 합계와 금액의 평균을 계산한 후 'Σ 값'을 '행'으로 추가하여 피벗 테이블 보고서를 작성합니다. 레이아웃과 위치는 F3에 설정합니다.
▶ 보고서 레이아웃은 '개요 형식으로 표시'하고 열의 총합계만 설정합니다.
▶ 금액은 기호 없는 회계 형식으로 표시합니다.
▶ 빈 셀은 '*' 기호로 표시하고 '레이블이 있는 셀 병합 및 가운데 맞춤'을 설정합니다.
▶ 피벗 테이블 스타일은 '연한 주황, 피벗 스타일 보통 3'으로 설정합니다.
5-1. 1단계: 피벗 테이블 생성
① 데이터 선택: 주어진 표의 전체 자료를 선택합니다. (A2:D16)
② 피벗 테이블 삽입:
- 상단 메뉴에서 [삽입] - [피벗 테이블] - [테이블/범위에서]를 선택합니다.
- 피벗 테이블 옵션 창에서 위치를 [기존 워크시트]로 설정하고, 위치를 [F3] 셀로 지정한 후 [확인] 버튼을 누릅니다.
5-2. 2단계: 피벗 테이블 필드 설정
▶ 소득공제 내용은 '필터', 소득공제는 '행', 법인명은 '열'로, '값'에 합계와 금액의 평균을 계산한 후 '값'을 '행'으로 설정
① 소득공제 내용은 '필터' 영역으로, '소득공제' 필드를 '행' 영역으로 드래그합니다.
② '법인명' 필드를 '열' 영역으로 드래그합니다.
③ '금액' 필드를 '값' 영역으로 두 번 드래그합니다.
- 첫 번째 '금액'은 '합계'로 설정하여 총금액을 계산합니다.
- 두 번째 '금액'은 '평균'으로 설정하여 평균 금액을 계산합니다.
④ 'Σ 값'이라는 새로운 필드를 '행' 영역으로 드래그하여 총합계 행을 추가합니다.
5-3. 3단계: 보고서 레이아웃 레이아웃 설정
▶보고서 레이아웃은 '개요 형식으로 표시'하고 열의 총합계만 설정하시오
① 개요 형식으로 표시
- 피벗 테이블을 선택한 후 [디자인 탭] - [보고서 레이아웃] - [개요 형식으로 표시]를 선택합니다.
② 열의 총합계: [디자인 탭] - [총합계] - [열의 총합계만 설정]을 선택합니다.
5-4. 4단계: 값 형식 설정
▶ 금액은 기호 없는 회계 형식으로 표시합니다.
① 금액 형식 변경:
- 피벗 테이블에서 합계 금액 필드의 셀에서 [값필드 설정] - [표시 형식]을 클릭합니다.
- 평균 금액 필드의 셀- [값필드 설정] - [표시 형식]을 클릭합니다.
② 합계 금액과 평균 금액에서 차례로 [값 필드 설정] - 범주에서 [회계] - [기호 없음] - [확인]을 클릭합니다.
5-5. 5단계: 빈 셀 기호 설정 및 레이블 병합 및 가운데 맞춤 설정
▶ 빈 셀은 '*' 기호로 표시하고 '레이블이 있는 셀 병합 및 가운데 맞춤'을 설정합니다.
① 빈 셀 기호 설정
- 피벗 테이블 선택 - [피벗 테이블 분석] - [옵션] - 서식 탭에서 [빈 셀 표시] - [ * 입력] 합니다.
② 레이블 병합 및 가운데 맞춤 설정
- 레이아웃 탭에서 [레이블이 있는 셀 병합 및 가운데 맞춤 체크] - [확인]을 선택합니다.
5-6. 6단계: 피벗 테이블 스타일 설정
▶ 피벗 테이블 스타일은 '연한 주황, 피벗 스타일 보통 3'으로 설정합니다.
① 피벗 테이블 선택: 피벗 테이블을 선택- [디자인 탭]을 선택합니다.
② 스타일 변경: [피벗 테이블 스타일] - [연한 주황, 피벗 스타일 보통 3]을 선택합니다.
5-7. 피벗 테이블 확인
피벗 테이블이 주어진 조건을 만족하는지 각 단계를 다시 확인하고 문제가 있을 경우 필요한 부분을 수정합니다.
엑셀 피벗 테이블은 복잡한 데이터를 간단하게 요약하고 시각화하는 데 뛰어난 도구입니다. 이번 포스팅에서 배운 피벗 테이블의 다양한 기능과 활용법을 익혀 실무에서 적용해 보세요. 여러분의 업무 효율성이 향상되길 기대합니다.
