이전 강좌에서 만들어진 피벗 테이블의 행을 그룹으로 묶고, 필터 단추를 통해 열 항목의 정렬을 변경했습니다. 남은 것은 피벗 테이블 옵션에 대한 것들입니다. 피벗 테이블 옵션은 만들어진 테이블의 레이아웃, 데이터 처리 방식, 표식 형식 등 외부적으로 보이는 모습을 좀 더 세부적으로 설정하는 기능입니다. 간단하게 클릭 몇 번 하면 끝나는 작업이지만 무슨 작업인지 이해하는 것이 중요합니다.
피벗 테이블 옵션과 관련해 자세히 소개한 강좌는 아래를 클릭해 확인하시길 바랍니다.
피벗 테이블 ❼ 피벗 테이블 정리(피벗 테이블 옵션과 보고서 레이아웃)
만들어진 피벗 테이블을 보기 좋게 정리하는 방법을 소개합니다. 피벗 테이블 옵션에서 행 총합계, 열 총합계를 알아보고 빼거나 추가하는 방법을 소개합니다. 피벗 테이블의 레이블 셀을 병합하고 가운데 맞춤하는 방법을 설명하고 피벗 테이블에 값이 없는 빈 셀에 특정 문자나 기호를 넣는 방법을 소개합니다. 피벗 테이블 보고서 레이아웃의 "압축형"과 "테이블형"을 비교하고 부분합을 제거하는 방법을 설명합니다.
https://flylight.kr/
이번에 해결할 문제는 위와 같습니다. ≪조건≫은 2가지입니다만, 세부적으로 나누면 위 그림처럼 총 4가지로 구분할 수 있습니다. 그 중 "❶, ❷, ❸"은 모두 피벗 테이블 옵션으로 처리하는 부분입니다. 하나의 대화 상자에서 처리하는 작업이니 한꺼번에 하는 것이 효율적입니다. 그리고 나머지 "❹"는 셀의 정렬이나 표시 형식 등을 처리하는 사항으로 대부분 "셀 서식"을 이용해서 처리할 수 있습니다.
이번 강좌는 바로 풀이를 진행하는 것보다 각 작업이 무슨 의미인지부터 파악하는 것이 좋겠습니다. 그러기 위해선 현재 작업 중인 피벗 테이블의 모습과 ≪출력형태≫를 잘 비교해 볼 필요가 있습니다.
1. 레이블이 있는 셀 병합 및 가운데 맞춤
≪출력형태≫를 확인하면 위와 같이 열 레이블에 "서울"에 해당하는 C2:D2 범위는 "병합하고 가운데 맞춤"을 한 것처럼 보입니다. "대전"과 "경기"도 마찬가지입니다.
하지만 현재 작업 중인 피벗 테이블은,
위 그림처럼 각 셀이 나뉘어 있고 정렬도 다릅니다. "대전, 경기"도 마찬가지입니다.
일반적인 형태라면 두 셀을 범위로 선택하고 "홈 > 병합하고 가운데 맞춤"을 클릭하면 되지만, 피벗 테이블의 레이블은 그것이 되지 않습니다. "피벗 테이블에서는 사용할 수 없다"라는 에러 메시지를 뿜어냅니다. 다른 방법을 사용하라는 것이죠. 해결 방법은 잠깐 미루겠습니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
2. 빈 셀 표시 변경
다음은 값으로 반환된 셀에 값이 없는 경우입니다. 이것도 ≪출력형태≫와 작업 중인 피벗 테이블을 확인하면 무엇인지 알 수 있습니다.
≪출력형태≫를 확인하면 위와 같이 값이 없는 셀에는 ***로 표시돼 있습니다. 반면 작업 중인 피벗 테이블은,
그럼처럼 아무 값도 없는 것을 알 수 있습니다.
역시 일반적인 형태라면 각 셀을 클릭해서 값을 입력할 수 있지만, 피벗 테이블에서 그냥 입력할 수 없습니다. 특정 데이터의 통계를 값으로 가져왔으니, 값을 변경할 수 없는 것은 당연합니다. 값이 바뀌어야 한다면 원본 데이터가 바뀌는 것이 정상이겠죠. 그렇다면, 이것도 어떤 다른 방법을 통해 변경해야 한다는 것이겠죠.
3. 행 총합계와 열 총합계
이 문제는 작업 중인 피벗 테이블에서 무엇이 "행 총합계"인지, 무엇이 "열 총합계"인지 알면 됩니다.
위 그림처럼 마지막 행에 표시된 각 열의 합계가 "열 총합계"입니다. 행 합계는 "가맹점명"과 "최고일매출"로 구분했으니 마지막 2열로 나타난 부분입니다. 역시 각 항목의 행을 합계 낸 부분입니다. 이 두 총합계(행 총합계, 열 총합계)는 피벗 테이블이 만들어지면 기본으로 생성되는 녀석들입니다.
즉 "행의 총합계를 지워라."라는 것은 만들어진 현재의 피벗 테이블에서 마지막 2개의 열을 삭제하라는 것입니다. ≪출력형태≫를 확인하면 바로 알 수 있습니다.
하지만 역시 늘 해오던 방식(열 머리에서 우클릭 후 삭제)은 사용할 수 없습니다. 이유는 계속 같습니다. 피벗 테이블에 영향을 미치기 때문에 안 된다는 것이죠.
피벗 테이블 옵션
이제 각 문제가 무엇인지 파악했으니, 위 3개 작업 모두 기본적인 방식으로 처리할 수 없다는 것까지 이해하셨을 것으로 생각합니다. 뭔가 다른 방법으로 해결해야 한다는 얘기이고 그 방법이 "피벗 테이블 옵션"입니다.
- 피벗 테이블의 아무 곳이나 우클릭합니다. ⇒ 우클릭 팝업이 나옵니다.
- "피벗 테이블 옵션"을 클릭합니다. ⇒ "피벗 테이블 옵션" 대화상자가 나옵니다.
- 레이아웃 부분에서 "레이블이 있는 셀 병합 및 가운데 맞춤"을 클릭해 체크 박스에 체크가 표시되게 변경합니다.
- 첫 번째 문제를 해결하는 방법입니다. "홈 > 병합하고 가운데 맞춤"을 피벗 테이블 옵션에서 대신하는 역할입니다. - 서식 부분 "빈 셀 표시" 입력란에 빈 셀에 표시하고 싶었던 "***"을 입력합니다.
- 두 번째 문제를 처리하는 방법입니다. 당연히 어떤 문자든 사용할 수 있습니다.
이제 결과를 확인하려면 대화상자의 "확인" 버튼을 클릭하면 되지만, 세 번째 문제였던 "행 총합"을 지우는 것도 이 대화상자에서 처리해야 하므로 그것까지 처리하고 "확인" 버튼을 클릭하는 것이 효율적입니다.
- "요약 및 필터" 탭을 클릭합니다.
- 기본적으로 총합계 두 곳(행 총합계 표시, 열 총합계 표시)에 체크가 되어 있습니다. - "행 총합계 표시"를 클릭해 체크를 해제합니다.
- 최종 대화상자의 확인 버튼을 클릭합니다.
결과 그림처럼 3가지 문제가 모두 처리되는 것을 알 수 있습니다. 간단하죠.
4. ≪출력형태≫로 만들기
주요 작업은 끝났습니다. 이제 남은 것을 ≪출력형태≫와 작업 중인 피벗 테이블을 비교하고 다른 점만 기본적인 방법으로 처리하면 됩니다. 현재는 "***"으로 표시한 셀의 정렬만 가운데로 처리하면 ≪출력형태≫와 동일해지겠습니다.
- "***"으로 표시된 모든 셀을 선택해야 합니다. E5:F5 범위를 클릭 드래그해 선택합니다.
- 떨어진 셀은 ctrl 키를 누른 상태에서 클릭(드래그)하면 되었죠. ctrl 키를 누른 상태에서 E7:F7 범위를 클릭 드래그합니다.
- 역시 ctrl 키를 누른 상태에서 G6:H6 범위도 클릭 드래그합니다.
- 변경할 모든 셀을 선택했으니, "홈 > 가운데 정렬" 버튼을 클릭합니다.
결과, ≪출력형태≫와 동일한 피벗 테이블이 완성됩니다.
참고로 엑셀 2021 버전에서는 이 정도로 완성되지만, 이전 버전의 엑셀에서는 통계치들에 천 단위 구분기호가 없을 수도 있습니다. 피벗 테이블 통계에 천 단위 구분기호는 엑셀 2021년 버전 이상에서 적용되기 때문이죠. 즉 그 이전 버전이라면 "표시 형식"을 변경하는 작업을 더 해줘야 합니다.
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
의견글 입니다.
()
