피벗테이블을 활용하는 방법에 대해 알아보겠습니다. 엑셀은 기본적으로 많은 데이터를 가지고 이런저런 분석을 하는데 특화되어 있습니다. 보통 분류나 항목별로 합계나 개수, 평균 등을 구하죠. 이러한 요약(집계)을 할때 피벗테이블을 많이 사용합니다.
목 차
1. 피벗테이블 만들기
2. 피벗테이블 가공하기
<조건1> : 필터 이용하기(지역별로 따로 보고 싶을 때)
<조건2> : 필드 그룹화 하기(날짜를 월별, 분기별로 그룹화)
<조건3> : 단계를 나누기(월별/상품별 매출 합계 보기)
<조건4> : 데이터 값 변경하기(합계가 아니라 평균 보기)
<조건5> : 데이터 표시방식 바꾸기
<조건6> : 피벗 테이블을 자동으로 디자인 하기
"피벗테이블 필드 이름이 잘못되었습니다." 해결 방법
피벗테이블 만들기
피벗테이블을 만드는 방법은 매우 쉬운데요.
① 데이터가 있응 영역 아무 곳이나 클릭(데이터를 설정해주거나)하고, ② [메뉴]에서 [삽입] → [피벗 테이블]을 클릭하면 영역이 지정이 됩니다.
이때 [확인] 버튼만 누르면 됩니다. (쉽죠^^)
만들 때 옵션으로 "새 워크시트", "기존 워크시트"를 선택하는 옵션이 있는데 "새 워크시트"를 선택하면 피벗테이블을 새로운 워크시트에 만들어 주고, "기존 워크시트"를 선택하면 피벗테이블을 데이터가 있는 워크시트에 만들어 줍니다.
보통 데이터가 많을 때 피벗테이블을 사용하기 때문에 "새 워크시트"에 만드는 것을 추천합니다.
이제 피벗테이블이 만들어졌습니다. (쉽죠^^)
③ 이제 우리의 입맛(조건)에 맞게 데이터를 가공해야 합니다.
예를 들어, "날짜별(행) 지점별(열)로 매출액 합계(값)"를 알고 싶다면, 항목 중에서 "날짜"를 "행" 칸에 "지점"을 "열" 칸에 "매출액"을 "값"칸에 마우스로 끌어다 놓습니다.
그러면 아래와 같이 결과물이 나옵니다.
피벗테이블 가공하기
위에 결과를 보시면 복잡하죠?
이제, 피벗테이블을 <조건>에 따라 깔끔하게 만들어 봅시다.
<조건1> : 필터 이용하기(지역별로 따로 보고 싶을 때)
지역(서울, 성남, 평택)별로 따로 데이터를 보고 싶다면, 필터를 이용하면 됩니다.
① [지역] 필드를 [필터]에 끌어다 놓으면, ② 피벗테이블 위에 "지역"이라는 필터가 생깁니다.
이때 오른쪽 ▼를 선택하면 원하는 지역을 선택할 수 있는 창이 나옵니다.
여러 항목을 선택한다면 "여러 항목 선택"을 클릭하고 지정해주면 됩니다.
"서울"만 선택하면 아래와 같은 결과가 나옵니다.
(깔끔해졌죠^^)
<조건2> : 필드 그룹화 하기(날짜를 월별, 분기별로 그룹화)
월별 실적이나, 분기별 실적을 보고할 때 날짜별로 나온 데이터를 월별, 분기별로 바꿔줘야 합니다.
이럴 때 사용하는 기능이 "필드 그룹화"기능입니다.
① 날짜가 있는 필드 중 아무 곳이나 선택하고,
② [메뉴] → [피벗 테이블 분석] → [필드 그룹화]를 선택합니다.
그러면 "그룹화"창이 뜨는데 여기서 "월", "분기" 등을 선택할 수 있습니다.
"월"을 선택하면 아래와 같은 결과가 나옵니다.
날짜별로 있던 데이터가 월별로 그룹핑이 된 것을 확인할 수 있죠?
만약 다시 날짜별로 보고 싶다면 [피벗 테이블 분석] → [그룹 해제] 를 선택하면 됩니다.
<조건3> : 단계를 나누기(월별/상품별 매출 합계 보기)
① 월별로 세부 상품별 매출액을 봐야 한다면 "상품"이라는 필드를 "행" 칸에 "날짜", "상품" 순으로 넣어주면 됩니다.
이때는 순서가 중요합니다.
② 만약, 상품별로 월별 매출액을 봐야한다면 "행" 칸에 "상품", "날짜" 순으로 넣어주면 됩니다.
<조건4> : 데이터 값 변경하기(매출 합계가 아니라 매출 평균 보기)
지금까지는 매출액 합계를 봤는데요.
이번에는 매출액 평균을 보는 방법을 알아보겠습니다.
(피벗 테이블에서 값을 표시할 때는 기본적으로 합계를 보여줍니다.)
① [값] 칸에 있는 [합계]를 마우스로 클릭하고, [값 필드 설정]을 선택합니다.
② [값 필드 설정] 창이 뜨면 [데이터] 중에서 "평균"을 선택하면 피벗테이블의 데이터가 평균값으로 바뀌게 됩니다.
평균뿐만 아니라 개수, 최대값, 최소값, 곱하기 값도 선택이 가능합니다.
<조건5> : 데이터 표시방식 바꾸기
데이터가 금액을 나타낼 때는 천 단위 구분(,) 기호를 넣어줘야 합니다. 표시 방식을 바꿔볼까요?
[값] → [합계] → [값 필드 설정] 창에서 [표시형식]을 선택하면, [셀 서식] 창이 뜨는데 여기서 "표시 형식" 바꿀 수가 있습니다.
<조건6> : 피벗 테이블을 자동으로 디자인 하기
엑셀에서는 피벗 테이블을 자동으로 디자인하는 기능이 있습니다.
피벗 테이블도 이왕이면 이뻐야 겠죠 ^^
[메뉴] → [디자인]을 선택하고 원하는 디자인을 선택하면 피벗 테이블이 자동으로 바뀝니다.
"피벗테이블 필드이름이 잘못되었습니다."라고 나오는 경우가 있죠?
이럴 때는 당황하지 마시고 한 가지만 체크해 보세요~
필드명이 누락된 경우입니다.
피벗테이블을 만들기 위해서는 필드명이 모두 있어야 합니다.
마지막으로 한가지 팁!!
데이터를 바꾸거나 변경을 했다면 반드시, 피벗 테이블 위에서 [마우스] 우클릭 후 "새로 고침"을 해줘야 합니다.
그래야, 피벗테이블이 업데이트 됩니다.
본 포스팅 내용을 설명과 동영상으로 보시고 싶다면 아래 영상을 참조하시기 바랍니다.
이상으로 직장인이라면 한번쯤을 사용해봤을 피벗테이블에 대해 알아보았습니다.
엑셀의 다른 기능은 아래 포스팅을 참조해 주세요
2022.04.06 - [보고서 작성기술/엑셀] - 엑셀 랜덤 함수 : 엑셀 랜덤 추출, 순위 표시, 당첨자 뽑기
2022.04.12 - [보고서 작성기술/엑셀] - 엑셀 선그래프 구간별로 선 색상/유형 바꾸기
2022.04.11 - [보고서 작성기술/엑셀] - 엑셀 그래프 선 굵기, 선 유형 변경
2022.03.24 - [보고서 작성기술/엑셀] - 엑셀 텍스트 줄바꾸기 방법(3가지)
2022.09.02 - [보고서 작성기술/엑셀_차트(그래프) 작성법] - 보고서 차트작성기술 : 올바른 차트(그래프) 선택
2021.08.05 - [보고서 작성기술/엑셀] - 엑셀 원하는 텍스트 추출(LEFT, RIGHT, LEN, FIND 함수)
2022.04.13 - [보고서 작성기술/엑셀] - 선그래프 다중 선 색상 자동으로 바꾸기
2022.04.12 - [보고서 작성기술/엑셀] - 엑셀 선그래프 구간별로 선 색상/유형 바꾸기
2022.04.11 - [보고서 작성기술/엑셀] - 엑셀 그래프 선 굵기, 선 유형 변경
2022.04.10 - [보고서 작성기술/엑셀] - 엑셀 선 그래프 색상 조정
2022.04.09 - [보고서 작성기술/엑셀] - 엑셀 꺾은선 그래프 만들기
2022.05.18 - [보고서 작성기술/엑셀] - 막대그래프 이미지 막대 만들기(픽토그램)
2022.05.27 - [보고서 작성기술/엑셀] - 엑셀 양방향 비교 막대그래프 만들기
2022.05.09 - [보고서 작성기술/엑셀] - 엑셀 막대그래프 간격조절하기(계열겹치기, 간격너비)
2022.04.15 - [보고서 작성기술/엑셀] - 엑셀 선그래프 항목구간 강조하기(혼합차트 활용)
2022.04.16 - [보고서 작성기술/엑셀] - 엑셀 선그래프 수치구간 강조하기(혼합차트 활용)
2022.09.01 - [보고서 작성기술/엑셀_차트(그래프) 작성법] - 보고서 차트작성기술 : 가독성 있는 차트 만드는 방법
2022.03.02 - [보고서 작성기술/엑셀_차트(그래프) 작성법] - 엑셀차트작성기술 : 차트(그래프) 기본 구성 요소 이해
'보고서 작성기술 > 엑셀 실무기초' 카테고리의 다른 글
| 엑셀 꺾은선 그래프 만들기, 레이블 추가 (0) | 2022.04.09 |
|---|---|
| 엑셀 RAND, RANDBETWEEN, RANK, Vlookup 함수 : 엑셀 랜덤 추출, 순위 표시, 당첨자 뽑기 (0) | 2022.04.06 |
| 엑셀 정렬하기(오름차순, 내림차순, 사용자 정의, 필터) (0) | 2022.03.27 |
| 엑셀 틀고정(셀, 행, 열 고정) 방법과 단축키 (1) | 2022.03.26 |
| 엑셀 반올림, 올림, 버림 함수 한 번에 알아보기 (0) | 2022.03.25 |
댓글