[오피스 팁] 엑셀의 피벗테이블을 이용하여 데이터를 분석하는 방법

[오피스 팁] 엑셀의 피벗테이블을 이용하여 데이터를 분석하는 방법

오피스＆협업

PCWorld

데이터를 목록으로 작성할 때 "지난 달에 서부 해안(West Coast) 사무소의 매출이 어느 정도였는가?" 또는 "지난 해 각 사무소의 분기별 평균 고객 수는 어떠했는가?" 등의 질문에 대답해야 하는 경우가 종종 발생한다.

엑셀의 피벗테이블(PivotTable - 마이크로소프트는 두 단어를 붙여서 하나의 단어로 사용하지만 다른 스프레드시트 프로그램에서도 피벗 테이블을 찾아볼 수 있다.)로 이런 질문에 대한 답을 제공할 수 있다.

데이터를 범주 별로 묶고 싶은가? 피벗테이블을 사용하자. 사람, 분기, 범주 별로 결과를 분석하고 싶은가? 피벗테이블을 사용하자. "얼마나 많은?"으로 시작하는 질문에 대한 답을 제공해야 하는가? 이것도 피벗테이블로 해결할 수 있다. 이런 작업을 어떻게 처리해야 하는지 보여주도록 하겠다.

이 기사에서 필자가 제공하는 예제로 실습을 원하는 독자들은 필자의 엑셀 스프레드시트를 다운로드 할 수 있다.

피벗테이블이란 무엇인가?

우측에 보이는 것이 좌측의 목록에서 얻은 데이터를 요약한 피벗테이블이다. 컨설턴트(Consultant) 필드의 데이터는 열로 표시되어 있으며 범주(Category) 필드의 데이터를 행으로 표시되어 있고, 해당 표는 각 컨설턴트가 상대하고 있는 고객의 수를 요약하고 있다.

피벗테이블은 목록을 단순한 형태로 요약하기 위한 엑셀 툴이다. 사용자는 목록으로부터 피벗테이블을 생성할 때 어떤 필드를 행으로 표시하며 어떤 필드를 열로 표시하고 어떤 데이터를 요약하고 싶은지 정의한다. 이 때, 스프레드시트의 모든 데이터를 사용할 필요가 없으며, 질문에 대한 답을 제공하기 위해 필요한 데이터와 필드만을 사용하면 된다. 일단 표를 생성하면 자신이 원하는 답을 얻을 수 있다. 또한 추후에 해당 피벗테이블을 재정렬하여 다른 질문에 대한 답도 얻을 수 있다.

피벗테이블을 생성하자

피벗테이블을 생성하기에 앞서 자신이 답을 얻고자 하는 질문 또는 자신의 데이터로부터 추출하고 싶은 정보에 대해 생각해 보자. 이 단계에서 테이블을 어떻게 구성할지 결정하게 된다.

이 워크시트(Worksheet)의 데이터와 "2011년에 우리가 개발을 통해 분기별로 얻은 수익은 얼마인가?"라는 질문에 대해 생각해 보자.

우리는 이 데이터를 이용해 "2011년에 우리가 개발을 통해 분기별로 얻은 수익은 얼마인가?"라는 질문에 대한 답을 제공하는 피벗테이블을 생성하는 방법을 설명할 것이다.

피벗테이블을 생성하려면 데이터 목록의 한 부분을 클릭하고 삽입탭을 선택한 후, 피벗테이블을 클릭한다. 그러면 엑셀이 제목(Heading)을 포함하여 해당 데이터를 포함하고 있는 필드를 자동으로 선택한다. 필드가 제대로 선택되지 않으면, 드래그하여 수동으로 필드를 선택한다. 새로운 시트에 피벗테이블을 위치시키는 것이 가장 좋기 때문에, 위치에서 새 워크시트를 클릭하고 확인를 클릭한다.

이 때, 피벗테이블을 처음 생성해본 사용자들은 화면이 전혀 익숙하지 않기 때문에 혼란스러울 수 있다. 하지만 사실은 보기보다 간단하다. 피벗테이블 필드 목록 패널은 그 이름에서 알 수 있듯이 목록의 필드가 포함된다. 따라서, 패널의 밑단에 있는 상자에 필드를 정렬하면 된다. 이 작업을 마치면, 좌측의 도표에 피벗테이블이 생성된다.

피벗테이블 필드 목록 패널은 데이터의 모든 필드가 포함된다. 사용자는 이것들을 아래의 상자에 정렬하면 된다.

화면 우측의 피벗테이블 필드 목록에서 분기(Quarter), 범주(Category), 수수료(Fees) 체크상자를 클릭한다. 이 데이터가 "2011년에 우리가 개발을 통해 분기별로 얻은 수익은 얼마인가?"라는 질문에 대한 답을 얻기 위해 필요한 데이터이다. 이렇게 하면, 피벗테이블이 생성된다. 자신이 원하는 대로 데이터가 정렬되지 않을 수 있지만, 간단하게 해결할 수 있다.

예를 들어, 범주를 행으로 표시하고 분기를 열로 표시하고 싶다면, 분기를 열 레이블 상자로 드래그하고 범주를 행 레이블 상자로 드래그한다. 이제 행 B에 4분기에 대해 개발로부터 얻은 수수료에 대한 결과뿐만이 아니라 연간 총계가 포함되어 있기 때문에 피벗테이블에서 자신이 원하는 답을 얻을 수 있다.

피벗테이블 구성하기

피벗테이블에서 데이터를 구성하면 읽기가 훨씬 쉬워진다.

피벗테이블로 단순히 답을 얻는 수준을 넘어 더욱 깔끔한 구성도 가능하다. 수치 데이터를 구성하기 위해서는 값을 마우스로 우 클릭하고 필드 표시 형식을 선택한다.

범주 목록에서 숫자를 선택하고 원하는 숫자 형식을 설정한다. 필자의 경우, 천 단위 구분점을 추가하고 소숫자리는 0으로 설정했다. 확인을 클릭하면 테이블의 모든 숫자 데이터가 이에 따라 구성된다. 피벗테이블의 디자인을 변경하려면 내부를 클릭한 후, 피벗테이블 도구 > 디자인 을 선택하고 피벗테이블 스타일 갤러리에서 스타일을 클릭한다.

하나의 피벗테이블로 다양한 데이터 도출하기

피벗테이블을 생성했다면 이제는 동일한 테이블을 이용하여 다른 질문에 대한 답을 구할 수도 있다. 예를 들어, "데이비슨(Davidson)이 개발에 대해 각 분기별로 얻은 수익은 얼마인가?"라는 질문에 대한 답을 얻기 위해서는 테이블을 클릭하여 피벗테이블 필드 목록을 다시 표시한 후, 컨설턴트(Consultant)를 클릭하여 열 레이블 상자에 추가한다. 피벗테이블 필드 목록이 화면에서 사라질 경우, 피벗테이블의 내부를 마우스로 우 클릭하고 필드 목록 표시하기를 클릭한다.

사용자는 피벗테이블을 필터링하여 자신이 원하는 정보만을 표시하도록 할 수 있다.

비록 피벗테이블에서 이미 자신이 원하는 답을 얻을 수 있다 하더라도 피벗테이블에 필터를 적용하여 원하지 않는 데이터를 숨길 수 있다. 컨설턴트의 이름 중 하나를 선택하고 피벗테이블에서 열 레이블 드롭다운 화살표를 클릭한다. 그리고 데이비슨을 제외한 나머지 체크상자를 선택 해제한다. 드롭다운 상자에서 분기 목록화되어 있다면, 뒤로 이동하여 드롭다운 화살표를 클릭하기 전에 컨설턴트를 반드시 선택하도록 하자.

이제 범주(개발 또는 지원(Support)) 중 하나를 선택하고 행 레이블 드롭다운 화살표를 클릭한 후, 개발을 제외한 나머지를 선택 해제한다. 그러면 피벗테이블에 필터가 적용되어 해당 질문에 대한 답을 제공하는 정보만 표시된다. 이런 방식으로 피벗테이블을 계속해서 활용하여 다양한 데이터를 도출할 수 있다.

슬라이서(Slicer)를 이용한 더욱 스마트한 필터링

사용자는 슬라이서를 추가하고 사용하여 항목을 선택함으로써 피벗테이블에 더욱 시각화된 필터를 적용할 수 있다.

엑셀 2010 사용자의 경우, 새로운 슬라이서 툴을 사용하여 피벗테이블의 사용 편의성을 높일 수 있다. 슬라이서를 이용하면 피벗테이블에서 표시하는 데이터를 더욱 시각적인 방법으로 선택할 수 있다. 슬라이서를 추가하려면 피벗테이블의 내부를 선택하고 피벗테이블 도구 > 옵션 > 슬라이서 삽입(Insert Slicer)를 선택한다. 워크시트에 추가하기 위해 각 슬라이서에 대한 체크상자를 클릭하고 확인을 클릭한다. 필자의 경우, 분기, 범주, 컨설턴트에 대해 슬라이서를 추가했다.

피벗테이블에 필터를 적용하려면, 슬라이서 안의 항목을 클릭하여 피벗테이블 내에서 해당 데이터를 표시한다. 한 번에 여러 항목을 표시하려면, Ctrl을 누른 채로 자신이 원하는 항목을 클릭한다. 표시하고 있는 항목은 슬라이서에서 색상으로 표시된다. 슬라이서 우측 상단의 아이콘이 활성화된 상태는 슬라이서가 피벗테이블에서 데이터에 필터를 적용하고 있음을 나타내며, 해당 아이콘을 클릭하면 해당 필터가 제거된다.

사용자는 슬라이서를 선택하고, 슬라이서 도구 > 옵션을 선택한 후, 각 슬라이서에 대해 사용하고자 하는 슬라이서 스타일(Slicer Style)을 클릭함으로써 슬라이서를 구성할 수 있다.

데이터가 바뀔 때 피벗테이블을 업데이트 하기

피벗테이블을 생성한 원래 목록에서 데이터에 수정을 가할 경우, 해당 변경사항을 반영하기 위해 피벗테이블을 업데이트해야 한다. 이를 위해서는 피벗테이블을 마우스로 우 클릭하고 새로고침을 클릭한다.

피벗테이블을 이용하여 피벗차트 생성하기

사용자는 피벗테이블의 데이터로 손쉽게 차트를 구성할 수 있다. 엑셀이 자동으로 피벗테이블에 연결하는 피벗차트를 생성할 수 있는 것이다. 차트의 구성에 변경을 가하게 되면 해당 변경사항이 피벗테이블에도 반영되며, 반대의 경우도 마찬가지이다.

피벗차트를 생성하려면 피벗테이블의 안쪽을 클릭한 후, 삽입을 선택하고 원하는 차트를 선택한다. (단순한 차트가 가장 좋다.) 피벗차트는 피벗테이블 옆에 나타나며, 피벗테이블로부터 얻은 데이터를 표시한다.

선택한 피벗테이블로 차트를 생성하면 피벗테이블이 표시하는 정보를 나타내는 피벗차트가 된다.

그리고 나서 슬라이서를 이용하거나 해당 테이블의 레이아웃을 변경하여 데이터를 업데이트하면 차트가 이에 따라 자동으로 업데이트된다.

드릴 다운으로 데이터 확인하기

피벗테이블에서 값을 더블 클릭하여 드릴 다운(Drill Down), 엑셀이 이와 관련된 값을 추출하여 새로운 시트에 위치시킨다.

피벗테이블에는 드릴 다운 기능이 내장되어 있다. 총합 등 피벗테이블에서 특정 값을 탐색하려면, 해당 값을 더블 클릭하면 된다. 그러면 더블 클릭한 값과 관련된 모든 데이터를 포함하는 새로운 시트가 표시된다. 이 시트에서 데이터를 분석하여 데이터에 대한 이해를 높일 수 있다.

피벗테이블에서 값을 더블 클릭할 때마다 워크북(Workbook)에 새로운 시트가 나타난다. 사용을 마친 시트를 제거하지 않으면 나중에 화면이 매우 혼잡스러워진다. 이를 위해서는 해당 시트의 탭을 마우스로 우 클릭하고 삭제를 클릭한다.

피벗테이블 데이터에 기타 함수 사용하기

피벗테이블에서 합계 외에 다른 함수를 표시하도록 설정할 수 있다. 여기에는 평균이 표시되고 있다.

기본적으로, 엑셀은 피벗테이블에서 값 필드를 더하지만 사용자는 이런 설정을 변경하여 최대, 최소, 평균 등의 다른 함수를 적용할 수 있다. 예를 들어, 피벗테이블에서 값의 평균을 구하려면, 데이터 영역에서 해당 항목을 마우스로 우 클릭하고 데이터 요약 기준을 선택한 후, 평균을 클릭한다. 이 때, 표 좌측 상단의 제목이 변경되면서 평균값을 보고 있음을 나타내고, 평균을 표시하기 위해 피벗테이블의 값이 다시 계산된다. 변경사항을 취소하고 총합 데이터를 보려면, 값을 마우스로 우 클릭하고 데이터 요약 기준을 선택한 후, 합계를 클릭한다.

엑셀의 피벗테이블과 피벗차트에 관해서 간단히 알아보았다. 하지만 이런 툴에 관한 기본적인 지식을 통해 사용자는 손쉽게 피벗테이블을 생성하여 자신의 데이터를 요약하고 분석할 수 있을 것이다. editor@itworld.co.kr