피벗테이블은 엑셀이 제공하는 가장 강력한 데이터 분석 도구입니다. 아마 저에게 엑셀의 가장 뛰어난 기능이 뭐냐고 물으신다면… 주저없이 피벗테이블이라고 얘기할 수 있습니다. 피벗테이블은 많은 양의 데이터를 빠른 시간에 다양하게 분석할 수 있는 대화형 테이블이라고 할 수 있는데요, 데이터를 분석하는 데 있어서 이만한 기능을 제공하는 프로그램도 흔치 않은 것이 사실입니다.
그런데 피벗(Pivot)이라는건 뭘까요? 피벗은 단어의 의미 그대로 회전중심축을 의미하는데, 원본 데이터를 사용자가 정의한 축을 중심으로 다양하게 분석(회전)해볼 수 있다는 의미입니다. 엑셀 97에서 처음 소개된 피벗테이블은 너무 복잡해보여서 사용자들이 쉽게 접근하지 못했던 것이 사실입니다. 엑셀 2007부터는 클릭 몇번으로 피벗테이블을 만들고 분석할 수 있게 되었는데, 여러분들도 저와 함께 쉽게 이해하실 수 있을겁니다.
다음과 같은 데이터가 있다고 가정을 하고 피벗테이블을 통해 데이터를 다양한 방법으로 분석해보도록 하겠습니다. 물론 실무에서는 수천 라인에 해당하는 방대한 데이터를 가지고 분석할 경우가 많겠죠?
지역별 판매액의 합계
위 데이터를 기준으로 지역별 판매액의 합계 보고서를 만들어야 합니다. 어떻게 하시겠어요? 물론 다양한 방법들이 있겠지만, 피벗테이블을 이용하면 클릭 몇번으로 보고서가 완성됩니다. 피벗테이블을 만드는 방법도 배울겸 간단하게 지역별 판매액의 합계를 구해보도록 하죠.
피벗테이블을 만들기 위해 [삽입-피벗테이블]을 선택합니다. 메뉴에서 알 수 있듯이 피벗차트는 피벗테이블의 결과를 차트로 표시할 수도 있습니다.
피벗테이블 대화상자가 표시되면 원본데이터의 범위와 피벗테이블을 표시할 위치를 선택합니다.
피벗테이블은 별도의 워크시트로 만들 수도 있고 시트 내부에 포함할 수도 있습니다. 우리는 원본데이터와 비교를 해보면서 이해를 돕기 위해 현재 시트내에 삽입을 해보겠습니다. 다음 그림처럼 피벗테이블이 만들어지고, 사용자가 다양한 선택을 할 수 있는 피벗테이블 필드 목록이 표시됩니다.
우측에 표시된 필드목록 상자에서 다양한 선택 작업만 해주면 피벗테이블이 알아서 데이터를 요약/분석을 해주는 것입니다. 우리가 보고 싶은 데이터가 지역별 판매액의 합계죠? 그러면 오른쪽 필드 목록에서 지역과 판매액을 선택합니다. 자동으로 피벗테이블이 선택한 옵션으로 만들어지는 것을 볼 수 있습니다.
굉장히 쉽게 데이터가 요약되었죠? 엑셀 2003버전을 사용하신 분들은 피벗테이블이 너무 많이 바뀌고 간단해진 것에 놀라실겁니다. 자 이 상태에서 피벗테이블의 진짜 핵심적인 기능을 한번 알아보죠. 바로 행/열/값을 실시간으로 변경해서 볼 수 있다는 것입니다. 이것이 피벗!이죠.
아래와 같이 행 레이블에 있는 “지역” 필드를 열 레이블로 끌고 가보세요. 이렇게 변경하면 현재 행으로 표시되던 지역별 합계가 열로 표시가 되겠죠?
요약된 피벗테이블
왜 피벗테이블을 대화형 테이블이라고 하는지 아시겠죠? 자 이번에는 판매액의 합계가 아니라 평균 판매액을 구해봅시다. 이 작업도 마우스 클릭만으로 간단하게 끝납니다. 이게 엑셀의 편의성이죠. 필드 목록 대화상자에서 값 필드인 “합계:판매액” 단추를 클릭하고, [값 필드 설정]을 선택합니다.
값 필드 설정 대화 상자가 표시되면 “평균”을 선택합니다. 이것으로 작업 끝! 정말 간단하게 데이터 요약 & 값에 대한 보고서를 볼 수 있는것이죠.
이제 조금 감이 잡히시나요? 행 레이블을 보면 자동필터에서 배운 필터링 단추가 표시된 것을 알 수 있습니다. 이 필터링 단추를 통해 피벗테이블에서 요약된 데이터 중에서도 다시 필터링을 할 수 있답니다. 그리고 행 레이블이나 평균:판매액 등의 기본 필드 이름도 여러분들 마음대로 변경해서 표시할 수 있습니다.
필터링할 수 있다.
이제 필드 목록을 조금 더 추가해서 각 품목별로 지역에서의 판매액을 한 눈에 볼 수 있는 표를 만들어 봅시다. 저는 아래와 같이 만들었는데, 여러분들도 직접 해보시기 바랍니다.
서식은 별도로 지정을 해준 것이고, 행/열 레이블의 이름도 직접 타이핑해서 수정한 것입니다. 위와 같은 피벗테이블을 만들려면 필드 목록을 아래와 같이 설정하면 됩니다.
정말 간단하게 데이터를 요약하고, 필요한 계산을 할 수 있다는데 놀라셨을겁니다. 실제로 사용하는 복잡한 데이터가 있다면 지금 당장 엑셀로 불러와서 피벗테이블로 다양한 분석을 시도해보세요.
주의!
엑셀의 다른 기능들은 거의 원본데이터가 변경되면 실시간으로 데이터 참조가 되지만, 피벗테이블은 원본 데이터를 수정하더라도 자동으로 업데이트되지 않습니다. 그만큼 복잡한 내부 과정을 거쳤다는 것인데요, 피벗테이블의 단축 메뉴에서 "새로고침"을 해줘야 한다는 것을 꼭 명심해두세요!
