이전 강좌에서 피벗 테이블이 어떤 녀석인지 알아보았습니다. 아주 단순한 형태로 합계를 구했죠. 이번에 이전 강좌에 이어 2가지 이상의 조건에 만족하는 통계를 피벗 테이블로 만들어 보겠습니다. 지난 강좌는 "제품분류별 금액 합"에 대한 통계, 즉 "제품분류별"이라는 한 가지 조건이었습니다. 이번엔 하나 조건을 추가해서 "제품분류별 지역별 금액 합"을 만들어 보겠습니다. 추가된 조건은 "지역별"입니다. 이전 강좌와 이어지는 얘기이니 피벗 테이블을 모르시는 분들은 확인하고 돌아와 주세요.
지난 강좌에서 사용한 예제를 이어서 사용하겠습니다. 예제를 열면,
그림처럼 원본 데이터 표와 만들어 두었던 피벗 테이블이 있습니다. 피벗 테이블을 수정해서 만들겠습니다. 피벗 테이블 작업은 "피벗 테이블 필드" 작업창에서 진행해야 합니다. 일단 작업창을 띄우는 것이 먼저겠습니다.
- 일단 만들어진 피벗 테이블의 아무 셀이나 클릭하여 선택합니다. 첫 셀을 클릭하는 것이 일반적입니다. ⇒ "피벗 테이블 분석" 탭 메뉴가 나타납니다.
- 나타난 " 피벗 테이블 분석" 탭 메뉴 클릭합니다. ⇒ 리본 메뉴가 변경됩니다.
- 리본 메뉴 우측 끝 "필드 목록"을 클릭합니다. ⇒ "피벗 테이블 필드" 작업창이 나옵니다. 현재 세팅된 형태를 확인할 수 있습니다.
이제 조건 추가는 이전 강좌에서 했던 방식대로 진행하면 됩니다. 작업창 상단 영역의 필드를 "열"이나 "행"으로 클릭 드래그하여 끌고 오면 됩니다. 일단 "열"로 가져오겠습니다.
가져올 조건은 "지역"이겠죠.
결과, 원하는 통계는 확인할 수 있지만, 그림처럼 항목이 "제품분류" 아래로 모든 "지역"이 추가되면서 열이 너무 많아지는 문제가 생깁니다. 좌우 스크롤을 한참 움직여야 확인할 수 있습니다. 즉 조건을 계속해서 "열"로 추가하면 통계 확인이 힘들어집니다. 이런 경우 "지역"을 "행"으로 추가하면 훨씬 보기 쉽게 정리됩니다.
이전 강좌처럼 클릭 드래그로 끌고 오는 것이 쉽지만, 삭제하는 방법도 아셔야 하니 삭제하고 다시 "행"으로 끌고 오겠습니다.
- "열" 영역에서 필요 없는 필드(지역)를 클릭합니다. ⇒ 펼친 메뉴가 나옵니다.
- "필드 제거"를 클릭합니다. ⇒ 피벗 테이블에서 지역들이 삭제되고 작업창 "열" 영역에서도 삭제됩니다.
간단하죠. 이제 "지역" 필드를 "행" 영역으로 다시 끌고 옵니다.
그림처럼 작업창 필드 영역에서 "지역"을 클릭 드래그하여 "행" 영역으로 클릭 드래그하여 끌고 옵니다.
결과 한눈에 파악할 수 있는 피벗 테이블이 완성됩니다. 경상 지역에 판매된 가구의 금액 합을 보고 싶다면, 행과 열의 교차 셀(K8)을 보면 결과를 알 수 있습니다. 나머지도 마찬가지로 제품분류별 지역별 통계를 한눈에 모두 확인할 수 있게 된 것이죠. "제품분류"의 전체적인 통계는 제일 밑 행(13)을 보면 알 수 있고, "지역" 또한 제일 끝 열(N)을 보면 알 수 있습니다. 그리고 마지막 셀(N13)은 모든 판매 금액 합 역시 알 수 있습니다. 이 표를 "~IFS" 함수로 만들려면 너무 많은 작업을 해야 했겠죠.
다음, 위와 같은 통계를 구했다면 아마도 지역별 총합계 금액을 보기 위함일 것입니다. 그렇다면 총합계가 내림차순으로 정렬된 형태가 보기 좋겠죠. 피벗 테이블도 어쨌든 표이므로 기본적인 정렬 방법대로 정렬을 진행 할 수 있습니다.
- 정렬 기준인 "총합계"에서 아무 셀이나 클릭하여 선택합니다.
- "홈 탭 > 정렬 및 필터 > 숫자 내림차순 정렬"을 클릭합니다.
결과, 그림처럼 "총합계"를 기준으로 내림차순 정렬됩니다. 만약 "사무용품"을 기준으로 하고 싶다면 "사무용품"에 해당하는 셀을 클릭하고 정렬했다면 됐겠죠. 즉 정렬 또한 기본적인 방법을 그대로 사용할 수 있습니다. 한 가지 참고로 말씀드리면 원래 기본 정렬 방법은 데이터베이스형 표에서만 가능합니다. 데이터베이스형 표가 아니라면 범위를 선택하고 "사용자 지정 정렬"을 사용해야 합니다. 그런데 피벗 테이블은 제일 끝 열과, 제일 끝 행을 자동 총합계로 만들기 때문에 사실 데이터베이스형 표라 보기는 어렵습니다. 하지만 자동으로 만들어진 "총합계"는 가상의 통계치로 생각할 수 있으므로 데이터베이스형 표로 생각하셔도 됩니다. 그래서 기본 정렬 방법도 사용할 수 있는 것이죠.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
위 형식이 피벗 테이블의 가장 일반적인 형식입니다. 행과 열로 조건을 만들고 2가지 조건에 해당하는 통계를 보고서 형태로 만드는 것입니다.
하지만 3가지, 4가지 조건도 만들 수 있습니다. 이미 알아보았지만 "열"이나 "행" 영역에 추가 또 추가하는 방법입니다. 이때는 열로 추가하면 필드의 고유 데이터값이 많은 경우 좌우 스크롤을 사용해야 하는 문제를 확인했습니다. "행"으로 추가하는 것이 좋겠죠. 상하 스크롤이 생기겠지만, 상하 스크롤은 익숙하니까요. "배송사"를 추가해 보겠습니다. 그럼 "제품분류별, 지역별, 배송사별 금액 합"을 알 수 있습니다.
동일한 방법으로 "배송사"를 "행" 영역에 "지역" 밑으로 가져왔습니다.
결과, 그림처럼 "지역" 하부로 "배송사" 통계를 또 확인할 수 있습니다. 이때 "지역" 왼쪽
모양 버튼을 클릭하면 "지역" 하부로 위치했던 "배송사" 통계는 감출 수 있습니다. 그리고
모양이
모양으로 바뀝니다. 다시 보고 싶다면 바뀐
모양 버튼을 클릭면 볼 수 있습니다. 즉 행으로 끌고 온 "지역"과 "배송사"는 메인과 서브의 관계가 되어 마치 파일 탐색기의 폴더와 파일처럼 상위, 하위 개념이 적용됩니다. 이 얘기는 "배송사"가 상위, "지역"이 하위인 조건도 만들 수 있다는 얘기입니다.
작업창 "행" 영역에서 "지역"을 클릭 드래그하여 "배송사" 아래로 끌고 옵니다. 이렇게 하면 조건의 상·하위가 바뀌겠죠.
결과, 그림처럼 "배송사"가 상위 조건으로, "지역"이 하위 조건인 피벗 테이블을 완성할 수 있습니다. 이 개념이 파악되었다면 조건이 "4개, 5개, ..."가 되어도 별다른 문제가 없습니다. 그저 "행" 영역으로 필요한 필드를 끌고 오면 되는 것이죠. 상위 하위 조건이 늘어날 뿐이고 원한다면 상·하위 조건은 언제든지 조정할 수 있습니다.
다음, 지금처럼 하위 조건(지역)이 모두 펼쳐진 상태에서 상위 조건(배송사) 통계만 보고 싶다면, 배송사 좌측
모양 버튼을 모두 누르면 되긴 하지만 배송사 고유 데이터가 많다면 이것도 귀찮은 작업입니다. 이런 경우 한꺼번에 하부 조건(지역)을 감추는 기능도 있습니다.
- 하위 조건 아무 셀이나 우클릭 합니다. ⇒ 우클릭 팝업 메뉴가 나옵니다.
- "확장/축소 > 전체 필드 축소"를 클릭합니다.
결과 그림처럼 하부 조건(지역) 데이터가 감춰집니다. 다시 지역을 모두 보고 싶다면 동일한 방법으로 상위 조건(배송사) 셀을 우클릭한 다음 우클릭 팝업 메뉴에서 "확장/축소 > 전체 필드 확장"을 클릭하면 간단히 처리할 수 있습니다.
의견글 입니다.
()
