안녕하세요.
오늘은 피벗테이블에 대해서 알아보겠습니다. 많은 양의 복잡한 데이터가 주어졌을 때 항목별 개수, 합계 등의 수식을 계산함에 있어 필수인 기능입니다. 직접 함수를 입력할 필요 없이 마우스 클릭만으로 엑셀이 척척 알아서 계산해 주거든요!
피벗테이블이란?
pivot의 뜻을 먼저 살펴보겠습니다. 이 뜻을 알면 피벗테이블이 무엇인지, 어떻게 사용하는 것인지 바로 이해할 수 있습니다.
pivot
동사) 회전하다, 회전시키다
-출처 : 네이버 영어사전
pivot 이란 '회전하다'라는 의미입니다. 그렇다면 피벗테이블이란 '회전하는 표'로 해석할 수 있겠죠. '회전하는 표'가 무슨 말인지 아직 이해가 되지 않으실 텐데요. 표가 회전하는 것이 아니라, 표의 행과 열을 자유롭게 바꿀 수 있다는 것으로 이해해 주시면 됩니다.
행과 열을 자유롭게 바꿀 수 있다는 것은 단순히 위 이미지처럼 위치만 바꾸는 수준이 아닙니다. raw데이터엔 보이지 않는 사과의 1~3월 총합계값을 표에 나타낼 수도 있고, 각 과일의 평균값을 나타낼 수도 있죠. 이렇게 사용자가 설정한 항목에 따라 표의 행과 열이 자유롭게 변경되는 것이 바로 피벗테이블입니다.
제가 생각한 피벗테이블의 정의를 아래와 같이 정리해 보았습니다.
피벗테이블 (PivotTable) 이란?
많은 데이터의 계산 값을 사용자가 선택한 항목에 따라 빠르게 계산해 주는 표
피벗테이블 만들기
피벗테이블이 무엇인지 간단하게 정리했으니 이제 직접 피벗테이블을 만들어봅시다. 직접 만들어보는 것만큼 이해를 빠르게 하는 방법은 없죠!
1. 데이터 준비
피벗테이블을 만들기 위해서는 raw데이터가 있어야 합니다. 데이터를 요약해서 표를 만드는 게 피벗테이블이니까요. 전국에 과일 판매점을 냈다고 상상하며 가상의 매출 데이터를 만들어보았습니다. :)
판매 날짜, 지점, 품목, 판매개수, 매출액이 나열된 RAW데이터입니다. 총 200개 행으로 구성되어 있습니다. 여기서 '대전' 지점의 총매출을 알고 싶다면 어떻게 해야 할까요?
방법은 여러 가지가 있습니다. 필터 기능을 활용해서 '지점'을 '대전'만 선택하고 SUM 함수를 사용해서 매출액 합계를 구할 수도 있겠죠.
하지만 지점별 매출액을 모두 알고 싶거나 품목별 매출액을 알고 싶다면 어떻게 해야 할까요? 하나하나 원하는 값을 필터링하고 다른 시트에 그 값을 하나하나 입력하기에는 시간이 너무 오래 걸리겠죠. 이럴 때 피벗테이블을 활용하면 1분 만에 모든 값을 정리할 수 있습니다.
2. 피벗테이블 만들기
피벗테이블을 만들어 봅시다. 리본메뉴의 '삽입'에서 가장 왼쪽에 있는 '피벗 테이블'을 클릭해 주세요.
그럼 팝업창이 하나 나타납니다. 엑셀이 데이터 범위를 인식해서 피벗테이블을 만들 범위를 지정해 주는데요. 저는 표로 데이터세트를 구성해 두었기 때문에 '표/범위'가 '표 1'로 자동인식되었습니다. 데이터세트를 '표'로 설정하지 않으신 분들은 셀주소로 하셔도 됩니다. (EX. A1:E201)
피벗 테이블을 배치할 위치는 '새 워크시트' 또는 '기존 워크시트' 중에서 선택하실 수 있습니다. '새 워크시트'를 선택하시면 새로운 시트에 피벗테이블이 만들어지고, '기존 워크시트'를 선택하시면 현재 시트에 피벗테이블을 만들 수 있습니다. 처음 만들어보는 것이니 저는 '새 워크시트'를 선택할게요!
범위와 위치를 선택하신 후, '확인'을 클릭하시면 아래와 같이 새로운 시트가 만들어집니다.
우측의 '피벗 테이블 필드'에 집중해 주세요! 이 부분을 통해 우리는 우리가 원하는 대로 자유롭게 표를 만들 수 있습니다.
먼저 상단에 체크박스가 나타나있는 부분은 RAW데이터의 항목들입니다.
이 항목들을 하단에 있는 영역에 끌어넣어서 표의 행과 열을 자유롭게 구성할 수 있습니다.
아까 궁금했던 지점별 매출액 표를 만들어봅시다. '지점별'로 알아보고 싶으니까 행 영역에 '지점'을 넣고, 값 영역에는 매출액을 넣으면 됩니다.
이렇게 하면 행에는 각 지점이, 값에는 매출액의 합계가 정리된 표를 확인할 수 있습니다!
이번엔 품목별 매출액 표를 만들어볼까요? 이미 표가 만들어졌지만 우리는 마우스 드래그만으로 표를 다시 바꿀 수 있습니다. 행 영역에 있는 '지점'을 체크박스 영역으로 다시 넣어두고 '품목'을 행 영역으로 넣어주세요.
행 영역에 어떤 것을 넣느냐에 따라 표의 내용이 달라지는 것을 확인할 수 있습니다.
내가 원하는 대로 행을 자유롭게 바꿀 수 있는 것! 행의 데이터가 변경(회전)되는 것! 이게 바로 피벗테이블의 핵심입니다.
3. 행, 열을 모두 활용하여 피벗테이블 만들기
지금까지 한 가지 항목에 대한 표를 만들어봤습니다. 행 영역만 사용했는데, 행 영역과 열 영역을 함께 사용하면 훨씬 더 유의미한 표를 얻을 수 있습니다.
지점별 월 매출액과 지점별 품목별 월 매출액을 정리한 표를 만들어보겠습니다.
행 영역에는 '지점'을, 열 영역에는 '날짜'를 넣어주세요. RAW데이터의 '날짜' 항목이 날짜 서식이었다면, 엑셀에서 알아서 연도, 분기, 월을 인식해서 아래 이미지와 같이 RAW데이터에는 없는 '개월(날짜)', '분기(날짜)', '년(날짜)' 항목들을 추가로 보여줍니다. 저는 월별로 데이터를 보고 싶기 때문에 '년(날짜)', 개월(날짜)'를 열 영역에 넣어주었습니다.
지점별 월 매출액이 한 번에 정리되었습니다. 이렇게 보니 군데군데 월 매출이 없는 지점도 보이네요ㅎㅎ 제가 정말 사장이었다면 이 표를 보고 지점별 판매계획을 다시 세웠을 것 같습니다.
이번엔 조금 더 깊게 들어가서 지점별 품목별 월 매출액 표를 만들어보겠습니다. 어렵지 않습니다. 행 영역의 '지점' 아래에 '품목'을 넣기만 하면 됩니다.
짠- 지점별 품목 매출액이 정리되었습니다. 각 품목별 매출액뿐만 아니라 지점별 매출액도 요약해서 보여주니 보기가 좋네요. :) 간단한 클릭만으로 멋진 데이터 요약 표가 완성되었습니다.
오늘은 피벗테이블을 활용해서 데이터 요약하는 방법을 알아보았습니다. 실제로 업무를 하다 보면 방대한 데이터를 날짜별로 또는 항목별로 정리해야 하는 경우가 많은데요. 복잡하게 함수가 필터를 사용하는 것보다 피벗테이블을 활용하시면 빠르고 쉽게 데이터를 정리할 수 있습니다. 쓰다 보면 더 좋은 피벗테이블! 꼭 활용해 보세요. :)
'템플릿' 카테고리의 다른 글
| 엑셀 체크리스트 조건부 서식을 활용해서 완료된 일에 취소선 긋기 (TO-DO list 업그레이드) (0) | 2023.08.16 |
|---|---|
| 엑셀로 체크리스트 만들기(TO-DO list, 할일 생성) (0) | 2023.08.16 |