엑셀 피벗테이블은 엑셀에서 가진 가장 강력한 도구라고해도 과언이 아닙니다. 엑셀 피벗테이블만 잘 활용해도 복잡한 데이터 분석을 몇 번의 클릭만으로 짧은 시간 안에 완성할 수 있습니다. 이번 포스팅에서는 엑셀 피벗테이블 사용법을 다양한 예제를 통해 마스터해 보겠습니다.
엑셀 피벗테이블을 사용하는 이유
엑셀 피벗테이블은 몇 번의 클릭만으로 방대한 데이터를 빠르게 분석하고 요약할 수 있는 엑셀의 강력한 도구입니다. 엑셀을 처음 사용하더라도 피벗 테이블을 아주 쉽게 사용할 수 있는데요. 다음의 예시를 한번 같이 보겠습니다.
이 데이터는 약 1000개의 행으로 구성된 슈퍼마켓의 판매에 관련한 데이터로 여러가지 데이터를 다운로드할 수 있는 Kaggle(캐글)이라는 사이트에서 받은 데이터입니다. (같은 데이터를 이용하여 실습을 해보고 싶으신 분들은 이 링크를 클릭하여 다운로드 받으시면 됩니다.)
만약 회사에서 상사가 이 데이터를 주면서 다음의 내용들을 빨리 파악해 보라고 지시한다고 생각해 보세요.
- 도시별 총 판매량(Total)은 얼마인지?
- 도시별, 고객 타입별 판매량은 어떻게 되는지?
- 양곤의 제품 카테고리(Product line)별, 고객 타입별 판매량은?
- 도시와 고객의 성별에 따른 평균 만족도는?
- 브랜치별 고객 성별에 따른 판매량과 판매 비중은?
위의 질문들은 엑셀의 함수를 써서도 해결할 수는 있습니다. 도시별 총 판매량 같은 경우, 우선 필터를 적용하고 각 도시별 데이터를 각 시트로 나눈 다음에 SUM 함수를 사용하면 구할 수 있듯이 말이죠. 그런데 이 다섯 가지 질문을 엑셀 함수만 사용해서 데이터를 분석하려면 엑셀 필터를 여러 번 조작해야 하고, 데이터를 여러 개의 시트로 나눠야 하는 등의 번거로움이 수반됩니다.
이럴 때 엑셀 피벗테이블이 정말 유용하게 사용될 수 있습니다. 엑셀 피벗 테이블을 사용하면 이런 질문들을 순식간에 답변할 수 있게 됩니다. 이제 피벗테이블을 어떻게 생성하고 어떻게 사용하는지 하나 하나 단계별로 알아보겠습니다.
엑셀 피벗테이블 사용법 Step-by-Step 가이드
피벗테이블 생성하는 방법(새 워크시트에 생성)
먼저 엑셀에서 피벗테이블 생성하는 방법을 알아보겠습니다. 데이터가 있는 시트에서 컨트롤 A를 눌러 데이터 영역을 모두 선택한 다음 상단 메뉴 '삽입' - '피벗 테이블' - '테이블/범위에서' 를 선택합니다.
그럼 아래와 같이 기본적으로 '새 워크시트' 로 체크되어 있는데, 그대로 '확인'을 누르면 새로운 엑셀 워크시트에 피벗테이블이 생성되는 것을 확인할 수 있습니다.
피벗테이블 생성하는 방법(기존 워크시트에 생성)
기존 워크시트의 내가 원하는 위치에 피벗테이블을 생성하고자 하는 경우에는, 피벗테이블 생성을 눌렀을 때 뜨는 대화상자에서 '기존 워크시트'를 선택합니다. 그리고 우측 화살표를 누르면, "표 또는 범위의 피벗 테이블" 대화상자가 뜹니다. 여기서 피벗테이블을 넣고자 하는 곳을 클릭하여 활성화해주시고 다시 우측 화살표를 누른 후, 확인을 누르면 내가 선택한 위치에 피벗테이블이 생성된 것을 확인할 수 있습니다.
엑셀 피벗 테이블 필드 행, 값 추가하기
이제 피벗 테이블을 생성했으니, 위에서 보았던 질문들을 답변하기 위해 피벗 테이블 필드를 추가해보겠습니다. 첫 번째 질문은 "도시별 총 판매량(Total)은 얼마인지?" 였는데요. 이 문제를 풀기 위해서는 City별 Total의 합계(sum)를 계산해야 합니다. 피벗테이블에서는 이렇게 구분하는 항목(ex. 도시'별')들은 행과 열로 끌어다가 놓습니다. 피벗 테이블 필드에서 City를 클릭한 채로 드래그하여 '행'으로 끌어와 보겠습니다. 그럼 아래와 같이 피벗테이블에 '행 레이블'이라는 것이 생겼고, 도시이름들이 나오는 것을 볼 수 있습니다.
도시별 총합계를 구하기 위해 이번에는 'Total' 값을 "값"으로 드래그앤드롭 해보겠습니다. 피벗 테이블에서는 이렇게 합계나 평균 등 계산을 해야 하는 항목을 '값'으로 끌어다 놓습니다. 그럼 아래와 같이 '합계 : Total'이라는 열이 피벗 테이블에 생긴 것을 볼 수 있고, 우리가 구하고자 했던 '도시별 총합계'를 두 번의 클릭만에 구했습니다.
엑셀 피벗 테이블 필드 - 열 추가하기
이번에는 위에서 봤던 질문중 두 번째 질문("도시별, 고객 타입별 판매량은 어떻게 되는지?")을 한번 풀어볼까요? 위에서 도시별 판매량은 구했으니 여기서 '고객 타입별'로만 세분화를 하면 됩니다. 이번에는 피벗 테이블 필드 '열'쪽에 고객 타입(Customer type)을 드래그 앤 드롭해보겠습니다. 그럼 아래와 같이 '열 레이블'에 Customer type이 추가되어 우리가 구하고자 했던 "도시별, 고객 타입별 판매량"을 간단하게 구할 수 있게 되었습니다.
엑셀 피벗 테이블 정렬하는 방법
여기서 잠깐 피벗 테이블을 오름차순 또는 내림차순으로 정렬하는 방법을 알아보도록 하겠습니다. 위에서 도시별 고객타입별 판매량 합계를 구했는데, 판매량이 높은 도시부터 내림차순 정렬을 하고 싶습니다. 피벗테이블에서 정렬을 할 때는 피벗테이블 안쪽에 정렬하고자 하는 컬럼에 해당하는 셀을 클릭하고 - 우클릭 - 정렬 - 숫자 오름차순 정렬 또는 숫자 내림차순 정렬 선택하면 됩니다. 우리는 내림차순을 원했기 때문에 여기서 내림차순을 선택하도록 합니다.
그러면 아래와 같이 총합계 내림차순으로 정렬이 된 것을 볼 수 있습니다.
엑셀 피벗 테이블 필터 설정하기
이번에는 세번째 질문 "양곤의 제품 카테고리(Product line) 별, 고객 타입별 판매량은?"을 해결해 보겠습니다. 우리는 이제 모든 데이터가 아닌 '양곤' 데이터만 분석을 해야 합니다. 피벗 테이블에서 이렇게 특정 데이터만 구분해서 봐야 할 때 '필터'를 이용합니다. 'City'를 피벗 테이블 필드에서 선택하여 드래그한 후 '필터'쪽으로 드롭해 줍니다. 그럼 아래와 같이 피벗테이블에 필터가 생긴 것을 볼 수 있고, 좌측에 생긴 필터에서 양곤(Yangon)을 선택해 주면 양곤에 해당하는 데이터만 보이게 됩니다.
피벗 테이블 값 필드 설정 - 값 요약 기준 변경하는 방법
이번에는 네번째 질문 "도시와 고객의 성별에 따른 평균 만족도는?"을 해결해 보겠습니다. 평균 만족도를 구해야 하기 때문에 'Rating' 항목을 우선 '값'으로 끌어오고, 합계를 -> 평균을 계산하도록 변경해야 합니다. 합계를 평균으로 바꾸는 방법은 값 부분에서 화살표를 클릭한 후 - 값 필드 설정을 눌러줍니다.
그럼 아래와 같이 값 필드 설정 대화창이 뜨는데, 여기서 '값 요약 기준'을 '평균'을 선택하고 확인을 눌러줍니다.
그럼 아래와 같이 합계에서 평균으로 값이 변경 적용된 것을 확인할 수 있습니다.
이렇게 엑셀 피벗테이블의 '값 필드 설정' 메뉴를 이용해서 합계 뿐만 아니라, 개수, 평균, 최댓값, 최솟값, 곱 등을 구할 수 있습니다.
피벗 테이블 값 필드 설정 - 값 표시 형식 변경하는 방법
이번에는 다섯번 째 질문인 "브랜치별 고객 성별에 따른 판매량과 판매 비중은?"을 해결해 보겠습니다. 이 문제를 풀기 위해서는 판매 비중을 계산하기 위해 값 필드 설정에서 '값 표시 형식'을 변경해줘야 합니다. 값에서 우클릭 - 값 필드 설정 대화창에서 값 표시 형식 탭으로 이동 - 상위 행 합계 비율을 클릭' 하면 됩니다.
그러면 아래와 같이 브랜치별 각 성별이 차지하는 비중을 손쉽게 구할 수 있습니다. 피벗테이블의 값 필드 설정 - 값 표시 형식은 상위행 합계 비율 말고도 총합계 비율, 행 합계 비율, 열 합계 비율 등 다양한 설정이 가능합니다.
엑셀 피벗 테이블 디자인 변경하는 방법
피벗 테이블을 아무 곳이나 클릭하면 상단 바에 '디자인'이라는 부분이 활성화됩니다. 이 메뉴에서 피벗 테이블의 디자인과 관련된 설정을 할 수가 있습니다. 가장 우측에서는 피벗 테이블 스타일을 변경할 수 있습니다. 그리고 부분합, 총합계, 보고서 레이아웃 등을 변경할 수 있는데 하나하나 아래에서 알아보겠습니다.
피벗테이블 부분합 표기 변경
피벗테이블의 부분합의 경우, 아래와 같이 세가지 설정이 가능합니다. 부분합 표시 안함, 그룹 하단에 모든 부분합 표시, 그룹 상단에 모든 부분합 표시가 있습니다. 보통 직관적으로 보기 편한 것은 '그룹 상단에 모든 부분합 표시'이고, 부분합을 굳이 볼 필요가 없다면 '부분합 표시 안함'을 선택하여 해제해 주시면 됩니다.
피벗 테이블 총합계 설정 및 해제
피벗 테이블 총합계 설정에서는, 행 및 열의 총합계 해제 및 설정이 가능합니다. 각각 설정에 따라 어떻게 달라지는지 아래 예시를 통해 확인해보세요.
피벗 테이블 보고서 레이아웃
피벗 테이블 보고서 레이아웃 메뉴를 통해, 엑셀 피벗테이블의 레이아웃을 변경할 수 있습니다. 디폴트로 '압축 형식으로 표시'로 설정이 되고, 개요 형식으로 바꾸거나 테이블 형식으로 변경이 가능합니다. 직관적으로 보기 편한 것은 '개요 형식'입니다.
그리고 모든 항목 레이블 반복 및 항목 레이블 반복 안함 설정이 있는데, 모든 항목 레이블 반복의 경우 아래 예시에서 보는 것처럼 City 부분이 모든 행에 내용이 채워지도록 하는 것입니다. 그에 반해 항목 레이블 반복 안 함은 City 중복되는 부분은 빈칸으로 표기한 것처럼 말 그대로 '반복'을 안 하도록 설정하는 것입니다. 주로 요약용 테이블을 만들 때는 '레이블 반복 안함'을 선택해 주면 깔끔하게 볼 수 있고, 피벗테이블을 다시 데이터 분석에 활용하려면, '모든 항목 레이블 반복'으로 두고 쓰는 게 좋습니다.
지금까지 엑셀 피벗테이블 사용법을 예제를 통해 알아보았습니다. 피벗 테이블을 잘 활용하여 여러가지 함수를 써서 구해야 하는 계산식을 단 몇 번의 클릭만으로 작업을 완료해 보세요. 업무효율도 늘어남은 물론이고 자료의 가독성 또한 향상될 것입니다. 엑셀 관련 꿀팁이 궁금하시다면 '엑셀' 카테고리 내 다른 글들도 함께 참고해 보세요.
(같이 보면 좋은 글)
엑셀 피벗테이블 선택시 GetPivotData 대신 셀 위치 가져오는 방법
엑셀 피벗 테이블 활용시 피벗테이블의 특정 셀을 선택하여 추가적으로 연산이 필요할 때가 있는데요. 피벗테이블의 특정 셀을 선택했을 때 셀의 위치가 아닌 GetPivotData함수가 떠서 당황하신 적
datasciencediary.tistory.com
'엑셀' 카테고리의 다른 글
| [엑셀 꿀팁] 엑셀 간트차트 만드는 아주 쉬운 방법 (조건부 서식 이용) (0) | 2023.04.18 |
|---|---|
| [엑셀 꿀팁] 엑셀 표 헤더 첫칸 대각선 긋기, 셀 대각선으로 나누는 방법 (0) | 2023.04.17 |
| 엑셀 SUMPRODUCT 함수 사용 방법 다양한 예제와 함께 마스터 (0) | 2023.04.15 |
| 엑셀 INDEX 함수 및 엑셀 MATCH 함수 사용 방법 (다양한 예제) (0) | 2023.04.13 |
| 엑셀 피벗테이블 선택시 GetPivotData 대신 셀 위치 가져오는 방법 (0) | 2023.04.12 |
댓글