피벗 테이블의 기능에 대해선 이전 강좌까지로 대부분의 설명된 것 같습니다. 이번 강좌는 만들어진 피벗 테이블을 정리하는 몇 가지 방법을 알아보겠습니다.
저의 경우는 피벗 테이블을 처음 보았을 때 좀 당황스러웠습니다. 데이터베이스형 표(레이블과 레코드만으로 만들어진 표)에 익숙한 저한테는 많은 통계치가 뭘 말하는 건지 이해하기 힘들었던 것이죠. 나중에 설명을 듣고야 알 수 있었습니다. 아마도 저뿐 아니라 설명 없이 피벗 테이블을 바로 접하신 분들은 대부분 경험했을 것으로 생각합니다. 그런 이유로 보는 사람들에게 만들어진 통계가 무엇인지 쉽게 전달하기 위해선 완성된 피벗 테이블을 정리할 필요가 있습니다. 불필요한 통계는 줄이고 항목들의 구조를 이해하기 좋게 만들어 주는 것이죠.
예제는 지금껏 사용한 피벗 테이블입니다. 반복해서 사용한 피벗 테이블이니, 이제는 작업창에서 "필터, 열, 행, 값" 영역을 확인해 어떻게 구성된 녀석인지 알 수 있을 것으로 생각합니다. 어렵다면 이전 강좌를 참고하시길 바랍니다. 이 피벗 테이블로 전달하고자 하는 주요 내용은 "제품분류별, 지역별, 배송사별 금액 평균과 금액 합"이겠죠.
그런데 순수 목적이 그렇다면, 사실 열의 끝 항목인 "전체 금액 평균과 전체 금액 합"은 없어도 됩니다. 행의 끝 항목인 "총합계"도 필요 없습니다. 전체적인 값과 비교할 필요가 없다면 말이죠. 열의 끝과 행의 끝에 자동으로 추가된 통계는 우리가 요구한 것은 아니고 엑셀이 자동으로 만든 통계치였습니다. 이런 걸 처리하는 기능이 "피벗 테이블 옵션"입니다.
이 기능은 "피벗 테이블 필드" 작업창은 필요 없으니 작업창은 닫고 진행하겠습니다.
1. 총합계 지우기
예제에는 엑셀이 자동으로 만든 총합계를 확인할 수 있습니다. 행 총합계, 열 총합계로 구분됩니다. 따지고 보면 2열과 1행이지만 얘네들만 없어도 생각보다 정리된 느낌을 받을 수 있습니다.
- 피벗 테이블의 아무 셀이나 우클릭합니다. ⇒ 우클릭 팝업메뉴가 나옵니다.
- "피벗 테이블 옵션"을 클릭합니다. ⇒ "피벗 테이블 옵션" 대화상자가 나옵니다.
- "요약 및 필터" 탭을 클릭합니다.
- 기본으로 "행 총합계 표시, 열 총합계 표시"에 체크가 되어 있습니다. 피벗 테이블의 총합계가 표시됐던 이유입니다. - "행 총합계 표시"를 클릭해서 체크를 해제합니다.
- "열 총합계 표시"도 클릭해서 체크를 해제합니다.
- 최종 확인을 클릭합니다.
결과, 행 총합계와 열 총합계가 사라집니다. 이것만으로도 이 표는 "제품분류"별 통계라는 것이 확실해지죠. 물론 열의 총합계는 보고 싶다면 체크를 해제하지 않으시면 됩니다. 혹시나 열 머리글이나 행 머리글에서 삭제해 보셨다면 아셨겠지만 피벗 테이블은 열 머리글, 행 머리글에서 삭제할 수 없습니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
2. 레이블이 있는 셀 병합 및 가운데 맞춤
병합하고 가운데 맞춤도 알아보겠습니다.
피벗 테이블의 레이블에는 테두리가 없습니다. 그래서 확인이 좀 불편하지만, 레이블의 구성은 위 그림처럼 된 것입니다. 보기 좋지 않으니 가운데 맞춤하는 것이 좋겠습니다.
K5:P6을 범위로 선택하고 "홈 탭 > 가운데 맞춤"을 진행합니다.
결과, 위 그림처럼 일부만 가운데 맞춤 됩니다. "가구, 사무용품, 전자제품"은 하부로 "금액 평균과 금액 합"이 있으므로 각각 오른쪽 빈 셀과 병합하고 가운데 맞춤을 진행할 필요가 있어 보입니다. 그런데 K5:L5를 범위 선택하고 일반적인 방식으로 "홈 탭 > 병합하고 가운데 맞춤"을 클릭하면,
그림 같은 경고가 나옵니다. 피벗 테이블의 레이블은 일반적인 "병합하고 가운데 맞춤"은 사용할 수 없다는 얘기입니다.
이때 역시 "피벗 테이블 옵션"을 이용하면 간단히 해결할 수 있습니다.
- 역시 피벗 테이블의 아무 셀이나 우클릭합니다. ⇒ 우클릭 팝업메뉴가 나옵니다.
- "피벗 테이블 옵션"을 클릭합니다. ⇒ "피벗 테이블 옵션" 대화상자가 나옵니다.
- "피벗 테이블 옵션" 대화상자의 하부 탭 메뉴는 기본으로 "레이아웃 및 서식" 탭에 위치해 있습니다. 아니라면 클릭합니다.
- "레이블이 있는 셀 병합 및 가운데 맞춤"을 클릭합니다.
- 이 녀석이 지금 하고 싶었던 "병합하고 가운데 맞춤"을 해결해 줄 녀석입니다. - "빈 셀 표시"도 해보겠습니다. 현재 피벗 테이블에 통계 중 셀 값이 없는 것에는 "-"로 표현하고 싶습니다. 값으로 표현하고 싶은 그대로 입력란에 "-"를 입력합니다.
- 최종 확인 버튼을 클릭합니다.
결과 그림처럼 "가구, 사무용품, 전자제품" 레이블 셀이 합쳐지고 가운데 맞춤 되는 것을 알 수 있습니다. 추가로 "빈 셀 표시"에 입력한 "-" 도 확인할 수 있습니다.
추가로 "레이블이 있는 셀 병합 및 가운데 맞춤"을 진행하면 합칠 필요가 없는 셀 또한 모두 가운데 맞춤 되고 선택하지 않았던 "행 레이블, 열 레이블" 셀도 가운데 맞춤 됩니다. 즉 K6:P6 셀을 위에서처럼 굳이 가운데 맞춤을 할 필요는 없었던 것이죠.
이 정도가 보통 "피벗 테이블 옵션"에서 가장 많이 처리하는 기능입니다. "총합계, 병합하고 가운데 맞춤, 빈 셀" 처리로 정리하면 되겠습니다.
물론 다른 기능들도 있지만 나머지는 시간 날 때 한 번씩 해보시길 바랍니다. 이후 기본적인 서식 기능을 통해 "채우기 색, 테두리, 글꼴, 표시 형식"을 원래 하던 방법대로 처리하시면 됩니다.
3. 테이블 형식 레이아웃
다음, 이 정도만 해도 어느 정도 정리된 느낌이지만, 조금 거슬리는 것은 행 레이블의 구조입니다. 현재는 "지역" 그룹 하부로 "배송사"가 나오는 형태입니다. 이런 구조를 "압축형"이라 합니다. 마치 윈도우즈 탐색기처럼 "상위 > 중위 > 하위"로 계속해서 나눠지는 형태입니다. 하지만 엑셀뿐 아니라 어디서든 표를 만들 때 하나의 항목에는 그 항목에 해당하는 값이 들어오는 것이 보기 좋습니다. 지금에 경우 "행 레이블" 항목에 "지역"과 "배송사"가 존재하니 좋지 않은 것이죠. "지역" 항목 따로 "배송사" 따로 구성되는 편이 보기가 좋죠. 이런 형태 구조를 "테이블형"이라 합니다. 피벗 테이블도 이런 "테이블형" 구조로 만들 수 있습니다.
- 역시 피벗 테이블의 아무 셀이나 클릭하여 선택합니다. ⇒ 탭 메뉴에 "피벗 테이블 분석" 탭과 "디자인" 탭이 보입니다.
- "디자인 > 보고서 레이아웃 > 테이블 형식으로 표시" 순으로 클릭합니다.
결과, 그림처럼 아주 친숙한 구조로 된 통계를 확인할 수 있습니다.
4. 부분합 제거
위에서 만들어진 통계는 지역별 통계가 요약돼 있습니다. "강원 요약, 경기 요약, ..."으로 보입니다. 이 통계를 "부분합"이라 합니다. "지역"별로 "배송사"가 나뉘었기 때문에 나타난 녀석들입니다. 만약 "배송사"가 없다면 불필요한 통계죠. 현 상태는 부분합이 "지역별" 하단에 표시된 형태입니다. 이 "부분합"도 역시 거슬린다면 뺄 수 있습니다.
피벗 테이블의 아무 셀이나 선택된 상태에서 "디자인 > 부분합 > 부분합 표시 안 함"을 클릭합니다.
참고로 "테이블형 레이아웃(구조)"에서 부분합은 그룹 상단에는 표시되지 않고 항상 그룹 하단에만 부분합이 표시됩니다. "압축형"이나 "개요형"일 경우에 그룹 상단에 부분합을 표시할 수 있습니다.
결과, 위 그림처럼 익숙하고 보기 편한 구조의 피벗 테이블이 완성됩니다.
그리고 이렇게 부분합을 지울 경우 보통 "피벗 테이블 옵션"에서 "열 총합계"는 나타내 주는 것이 일반적입니다. 이건 직접 해 보시길 바랍니다.
이 정도로 피벗 테이블은 마치겠습니다. 세부적으로 따지고 들어가면 또 알아봐야 할 것이 있겠지만, 기능과 용어들을 알면 그다지 어렵지 않게 해결하실 것으로 생각합니다. 피벗 테이블은 클릭과 클릭 드래그만으로 구성된 기능이니까요.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
의견글 입니다.
()
