피벗(Pivot)이란 무엇일까? 사전을 찾아보면 추축, 선회축(旋回軸), 맷돌 등의 중쇠라고 나와있다. 무용에서는 한 발로 돌기를 피벗이라 하고, 골프를 칠 때 허리를 트는 것을 피벗이라고 한다. 그리고 농구를 할 때 한쪽 발을 축으로 해서 다른 발로 도는 것도 피벗이라고 부른다. 대충 피벗이 무엇인지 감이 왔으리라 믿는다. 그런데, 엑셀에서 피벗이 왜 나오는 걸까? 무엇을 축으로 무엇을 하는 걸까?
피벗 테이블은 많은 양의 데이터를 빨리 요약할 때 사용할 수 있는 대화형 테이블이다. 테이블의 행과 열을 중심축으로 원본 데이터를 다르게 요약하여 보거나, 다른 페이지를 표시해 데이터를 필터링하거나, 관심 있는 영역의 하위 수준을 나타낼 때 사용되는 기능이다.
수백 또는 수천 개의 데이터 행으로 이루어진 Excel 판매량 워크시트가 있다고 가정해보자. 이 워크시트에는 두 국가의 직원과 각 직원의 일별 판매량에 대한 데이터가 레이아웃되어 있다. 그러나 여러 열과 수 많은 행으로 구성된 이 워크시트에는 처리할 데이터가 너무 많다. 그렇다면 이 워크시트에서 어떻게 원하는 정보를 얻을 수 있을까? 또 원하는 정보를 다시 보기 편하게 재구성할 수 있을까?
전체 판매량이 가장 많은 직원은 누구이며, 또한 분기나 연도별로 판매량이 가장 많은 직원은 누구일까? 판매량이 가장 많은 국가는 어디일까? 피벗 테이블 보고서를 사용하면 이 모든 질문에 대한 답을 얻을 수 있다. 마치 무질서하게 섞인 단추를 색깔별, 용도별로 정리하는 것과 같다. 피벗 테이블 보고서는 모든 데이터를 꼭 필요한 내용만을 보여 주는 작고 간결한 보고서로 바꾸어준다. 여기까지 읽어도 뭐가 뭔지 모르겠지만 일단 시작해보자.
1) 원본 데이터의 검토
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_02.jpg)
피벗 테이블 보고서를 작성하기 전에 Excel 워크시트가 보고서에 맞게 잘 준비되었는지 살펴보자. 피벗 테이블 보고서에서 원본 데이터의 각 열은 보고서에서 사용할 수 있는 필드가 된다. 필드는 원본 데이터에 있는 여러 행의 정보를 요약하는 역할도 한다. 보고서의 필드 이름은 원본 데이터의 열 제목에서 가져오기 때문에 원본 데이터 워크 시트의 첫 번째 행에 각 열의 이름이 있는지 꼭 확인한다.
또한 동일한 열에서 머리글 아래의 나머지 행은 비슷한 항목들을 포함해야 한다. 예를 들어 텍스트가 한 열에 나오면, 숫자는 다른 열에 나와야 하며, 날짜는 또 다른 열에 나와야 한다. 즉, 숫자를 포함하는 열은 텍스트를 포함할 수 없다.
마지막으로 피벗 테이블 보고서에 사용할 데이터에는 빈 열과 빈 행이 없어야 한다. 그러므로 데이터 블록을 구분하는 데 사용되는 빈 행은 제거해야 한다.
2) 피벗 테이블을 만들어볼까?
피벗 테이블을 만들려면 커서를 원본 데이터의 아무 곳에나 두고 메뉴의 [삽입]에서 [피벗테이블]을 누르면 된다. [피벗 테이블]과 [피벗 차트] 두 개의 메뉴가 뜨는데 [피벗 테이블]을 클릭하자.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_03.jpg)
[피벗 테이블]을 누르면 나오는 대화창에는 표/범위 선택이 이미 선택되어 있는 것을 볼 수 있는데, 표/범위 상자에는 선택한 데이터의 범위가 표시된다. 또한 보고서가 들어갈 위치로 새 워크시트가 자동으로 선택되어 있는데, 보고서로 새 워크시트를 만들지 않으려면 기존 워크시트를 클릭하면 된다.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_04.jpg)
3) 피벗 테이블 보고서 기본 사항
피벗 테이블 만들기 대화상자에서 확인을 누른 후 나타나는 새 워크시트에 표시되는 항목은 다음과 같다.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_05.jpg)
한 쪽에는 피벗 테이블 보고서용으로 준비된 레이아웃 영역이 있고, 다른 쪽에는 피벗 테이블 필드 목록이 표시된다. 이 목록에는 원본 데이터의 열 제목이 표시된다. 국가, 직원, 주문량 등의 각 제목은 필드가 된다.
필드를 피벗 테이블 보고서 레이아웃 영역으로 이동하여 피벗 테이블 보고서를 만들 수 있다. 필드 이름 옆의 확인란을 선택하거나, 필드 이름을 마우스 오른쪽 단추로 클릭하고 필드를 이동할 위치를 선택하면 된다.
피벗 테이블 보고서에서 레이아웃 영역의 외부를 클릭하면 피벗 테이블 필드 목록이 사라진다. 필드 목록을 다시 보려면 피벗 테이블 레이아웃 영역이나 보고서 내부를 클릭하면 된다.
4) 직원별 판매량은?
먼저 직원별 판매량을 살펴보자. 이 정보를 얻으려면 직원에 대한 데이터가 필요하다. 따라서 직원 필드 옆의 피벗 테이블 필드 목록에 있는 확인란을 선택한다. 판매량도 필요하므로 주문량 필드 옆의 확인란을 선택하면 다음과 같은 보고서가 나온다. 보고서를 만들 때는 꼭 필요한 필드만 사용하자.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_06.jpg)
필드를 선택하면 기본 레이아웃 영역에 자동으로 배치된다. 원할 경우 필드를 다른 영역으로 이동할 수도 있다. 숫자가 포함되지 않은 직원 필드에 있는 데이터(직원 이름)가 보고서 왼쪽 행에 자동으로 표시된다. 숫자가 포함된 주문량 필드에 있는 데이터는 오른쪽 영역에 정확히 표시된다.
직원 데이터에 대한 머리글은 필드 위의 “행 레이블”이며 주문량에 대한 머리글은 “합계:주문량”이다. 머리글에 합계라고 표시되는 것은 Sum함수를 사용하여 숫자가 포함된 필드를 더하기 때문이다.
직원 필드 옆의 확인란을 주문량 필드보다 먼저 선택하든 나중에 선택하든 상관없이 두 필드는 항상 올바른 위치에 자동으로 배치된다. 즉, 선택 순서에 상관없이 숫자가 포함되지 않은 필드는 왼쪽에, 숫자가 포함된 필드는 오른쪽에 자동 배치된다.
5) 국가별 직원 판매 실적은?
위에서 우리는 각 직원의 판매량 보고서를 만들었다. 그런데 원본 데이터에는 두 국가(대한민국, 캐나다)의 직원에 대한 정보가 있다. 국가별로 각 직원의 판매량이 얼마인지 궁금해진다면 국가별 보고서를 만들어보자. 이 정보를 얻으려면 피벗 테이블 보고서에 국가 필드를 보고서 필터로 추가하면 된다. 보고서 필터는 보고서에서 하위 데이터 집합(제품군, 시간 범위 또는 지역)에 초점을 맞출 경우 사용한다. 국가 필드를 보고서 필터로 사용하여 대한민국이나 캐나다의 판매량을 별개의 보고서로 보거나 두 국가의 판매량을 함께 볼 수 있다.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_07.jpg)
국가 필드를 보고서 필터로 추가하려면 피벗 테이블 필드 목록에서 국가 필드를 마우스 오른쪽 버튼을 클릭하고 보고서 필터에 추가를 클릭한다. 그러면 보고서 위쪽에 새 국가 보고서 필터가 추가된다. 대한민국의 데이터만 보거나 캐나다의 데이터만 보려면 화살표를 클릭하고 해당 국가를 선택하면 된다. 두 국가의 데이터를 모두 보려면 화살표를 클릭하고 (모두)를 클릭한다.
6) 날짜별 직원 판매 실적을 알아보자.
원본 데이터에 수주일 정보 열이 있으므로 피벗 테이블 필드 목록에도 수주일 필드가 생성되어 있다. 따라서 이를 활용하면 각 직원의 날짜별 판매량을 알 수 있다. 이 정보를 얻으려면 수주일 필드 옆의 확인란을 선택해 해당 필드를 보고서에 추가한다.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_08.jpg)
수주일 필드가 행 레이블 방향으로 왼쪽에 자동으로 추가되는데, 이는 필드에 숫자가 포함되어 있지 않기 때문이다. 날짜는 숫자처럼 보일 수 있지만 숫자가 아닌 날짜 형식의 데이터이다. 수주일 필드는 숫자가 아닌 두 번째 필드로 보고서에 추가되기 때문에 직원 필드 내에 중첩되고 오른쪽으로 들여쓰기 된다.
이제 보고서에 각 직원의 판매량이 개별 날짜별로 표시되었다. 그런데 한 번에 보기에는 데이터가 너무 많다. 이런 경우에는 일별 데이터를 월, 분기 또는 연도별로 그룹화하여 보다 효율적으로 볼 수 있다.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_09.jpg)
날짜를 그룹화하려면 보고서에 날짜를 클릭한다. 그런 다음 옵션 탭의 그룹에서 그룹 필드를 클릭하고 그룹화 대화 상자에서 원하는 그룹을 클릭한 다음 확인을 클릭한다. 이 경우 분기로 그룹화해 보자.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_10.jpg)
자, 이제 캐나다 국적을 가진 직원의 분기별 보고서가 만들어졌다.
7) 행을 열 영역으로 이동하여 보고서 피벗하기
피벗 테이블 보고서를 통해 원하는 정보를 얻었지만 전체 보고서를 읽으려면 아래로 스크롤 해야 한다. 참 귀찮은 일이다. 귀찮은 스크롤의 사용 없이 보고서 자체를 피벗하여 다른 보기를 얻을 수 있다. 이렇게 하려면 행 레이블 영역에서 사용해 본 적이 없는 레이아웃 영역인 보고서의 열 영역(열 레이블)으로 필드를 이동면 된다. 보고서를 피벗하면 필드의 세로 또는 가로 보기가 바뀌어 행이 열 영역으로 이동하거나 열이 행 영역으로 이동한다. 이 작업은 아주 간단하다.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_11.jpg)
이 보고서를 피벗하려면 “분기”행 중 하나를 마우스 오른쪽 단추로 클릭하고 이동을 가리킨 다음 열로 “분기”이동을 클릭한다. 그러면 보고서의 행 레이블 영역에서 열 레이블 영역으로 전체 수주일 필드가 이동한다.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_12.jpg)
이제 직원 이름과 함께 모여 있으며 보고서에서 열로 레이아웃 되는 1분기 판매 데이터 위에 열 레이블이 나타난다. 또한 각 열 아래쪽에는 분기별 총합계가 표시된다. 이제 페이지를 아래로 스크롤 하지 않아도 모든 데이터를 한 눈에 볼 수 있다.
8) 끌어서 놓기 방법 사용하기
이전 버전의 Excel을 사용했던 엑셀러들은 2007버전에서도 끌어서 놓는 방법을 사용할 수 있는지 궁금할지도 모르겠다. 답을 미리 얘기하자면, ‘된다’이다. 피벗 테이블 필드 목록 아래쪽에 보고서 필터, 행 레이블, 열 레이블, 값의 네 상자가 있는데 필드를 이 영역에 끌어다 놓을 수 있다. 바로 이 부분이다.
![](https://img1.daumcdn.net/relay/cafe/original/?fname=http%3A%2F%2Fimage.ahnlab.com%2Fcomm%2Finfo%2Farticle_090602_13.jpg)
일반적으로 Excel 2007에서는 필드 이름 옆의 확인란을 선택하거나, 필드를 마우스 오른쪽 단추로 클릭하고 메뉴에서 위치를 선택해 필드 목록에서 보고서 레이아웃으로 필드를 추가하는 방법을 사용한다. 이때 필드는 레이아웃에 자동으로 배치됨과 동시에 목록 아래쪽의 해당 상자에 배치된다.
그러나 끌어다 놓기 방법이 더 편한 경우에는 필드 목록 위쪽에서 필드 목록 아래쪽의 상자로 필드를 끌고 가기만 하면 된다. 각 상자 위쪽의 레이블은 필드가 이동해야 할 보고서 영역을 알려 준다. 예를 들어 주문량 필드를 값 상자로 끌면 해당 필드가 보고서의 값 영역에 표시된다. 상자 간에 필드를 끌어 보고서 레이아웃 영역에서 위치를 변경할 수도 있고 필드를 상자 밖으로 끌어 보고서에서 제거할 수도 있다.@ |