엑셀에서 피벗테이블을 사용하면 복잡하게 저장된 데이터베이스의 데이터를 구미에 맞게(동적으로) 요약할 수 있습니다. 피벗(pivot)은 중심이 되는, 또는 기준이 되는 것을 의미하는데 기준에 따라서 데이터를 보여준다는 의미입니다.
우선 피벗테이블을 사용하기 위해서는 적합한 형태로 정규화가 된 데이터가 필요합니다. 정규화 형식은 열 단위로 카테고리와 데이터가 늘어선 모양입니다. 이 부분은 예를 들어 설명하겠습니다.
데이터와 카테고리
여기서 말하는 데이터 필드는 알고자 하는 결과값을 의미합니다. 카테고리는 이 값의 범위를 나타내는데 데이터 필드와 카테고리 수가 많아질수록 복잡한 형태의 데이터베이스가 됩니다. 여기에 피벗테이블을 이용하면 카테고리를 선별하여 (유효한 데이터만 선별하여) 보고서를 만들 수 있습니다.
정규화 형식
언제나 그렇듯 아주 간단한 형태의 예를 들었습니다. 위 표는 지역, 성별, 나이, 구매액 이라는 4개의 열로 된 필드가 있습니다. 여기서 데이터에 해당하는 필드는 구매액이 될 것입니다. 그리고 지역, 성별, 나이는 범위 값을 나타냅니다. 피벗 테이블을 만들기 위해서는 이런 형식의 정규화가 필요합니다.
범위 필드 : 지역, 성별, 나이
데이터 필드 : 구매액
잘못된 정규화
1월, 2월, 3월은 동일한 카테고리로 묶을 수 있습니다. 따라서 다음과 같은 정규화가 필요합니다.
각 열 필드 간 서로 독립적인 범위와 데이터를 지니고 있습니다.(지역과 달과 값은 서로 동일한 범위가 아님)
피벗 테이블 만들기
정규화된 표를 피벗테이블로 만들기는 매우 간단합니다. 그냥 따라 하시면 됩니다.
표의 아무 셀이나 선택 후
삽입->피벗테이블
확인을 누릅니다.
여기서는 새 워크시트가 새로 생성이 되고, 그 곳에 피벗 테이블이 만들어집니다.
이런 화면이 뜨는데, 아직 피벗테이블이 완성되지 않았습니다. 피벗 테이블이 완성되려면, 우리가 기준을 만들어 줘야합니다.
기준 만들기
오른편에 위와 같은 창이 뜨는데 정규화된 표의 필드들이 4개를 각각 열 레이블, 행 레이블, 값에 넣으면 됩니다. 카테고리(범주)필드는 열과 행 레이블에 들어가면 되고 데이터 필드는 값에 넣으시면 됩니다. 행과 열을 정하는 순서는 정답은 없습니다. 직접 보기 좋게 넣으시면 되지만, 주요한 기준이 되는 것을 행 레이블의 첫 번째로 넣으면 편합니다.
아래와 같이 작성을 했습니다.(마우스 드래그를 하여 집어 넣으면 됩니다.) 기본적으로 체크밖스를 클릭하면 자동으로 필드가 채워지는데 직접 여러가지 형태로 작성을 해보길 바랍니다.(정답은 없습니다.)
질문을 만들어보자.
행 레이블의 첫 번째 필드를 성별로 했습니다. 성별에 따른 총 합계를 쉽게 파악할 수 있습니다. 하지만 지역에 따른 총 합계는 바로 파악되지 않네요.
피벗 테이블을 제대로 작성하기 위해서는 피벗 테이블을 통해서 얻고자 하는 데이터가 어떤 것인지, 문장으로 만들어 볼 필요가 있습니다.
대전 지역의 20세에서 30세 사이의 여성이 얼만큼을 구매했는지에 대한 피벗 테이블을 만들어 보겠습니다.
지역과 성별을 행에 넣고 나이를 열에 넣었습니다.(절대적인 방법이 아님.)
대전 레이블이 중복 되었습니다. 이상하군요. 한번 지역과 성별 위치를 바꿔 보겠습니다.
이번엔 제대로 나오네요.(사실 문제가 뭔지는 모르겠지만, 범위의 크기가 좁은 필드가 레이블에서 위로 가는 게 좋은 듯 합니다. 이런 논리라면 나이 필드를 지역 아래 두어도 될 듯 싶네요.
대전 지역의 20세에서 30세 사이의 여성이 얼만큼을 구매했는지에 대한 값을 찾기 쉽게 됐네요.
필터의 사용
필터를 사용하여 값을 구할 수도 있습니다.
필터를 사용하는 방법은 고급필터와 비슷합니다.
행 레이블과 열 레이블의 필터를 열면
필터의 범위를 정하여 원하는 값을 얻을 수 있습니다.
체크박스를 통해서 필터링 하던가
레이블 필터를 통해 아래와 같이 범위를 지정하면 됩니다.
아래와 같이 첫 번째 기준이 되는 레이블에 대해서는 위와 같이 하면 되고
두 번째 기준이 되는 지역(대구,대전,부산,서울)에 대해서는
항목을 오른쪽 클릭한 후 필터 ->선택한 항목만 유지를 해주시면 됩니다.
열 레이블도 마찬가지로 필터링 하면 됩니다. 여기서는 굳이 할 필요가 없네요. 필요하다면 행 레이블에서 했던 것처럼 하면 됩니다.
피벗 테이블은 더 고급 기능들도 있지만, 이 정도만으로도 강력한 도구입니다. 고급기능은 나중에 알아보죠.