146 엑셀 필터 설정의 숨겨진 기능

146_엑셀 초급 강좌 – 피벗 테이블 기본 (6)

엑셀의 피벗테이블은 필터 기능을 활성화 시켜주지 않아도 기본적으로 필터링 기능을 모두 제공하고 더 다양한 필터링 옵션을 제공 합니다. 특히 슬라이서나 그룹화로 인한 필터링 기능은 피벗 테이블의 강력한 필터 기능입니다. 또 지난 번 예고 했듯이 피벗 테이블을 이용하면 워크시트에서 소개했던 부분합 기능은 거의 필요가 없어 집니다. 이번 포스팅은 엑셀의 피벗테이블에서의 필터링과 부분합 기능, 그리고 레이아웃에 따른 옵션을 소개하는 시간을 가지도록 하겠습니다.

이번 포스팅은 엑셀 피벗 테이블 만들기와 그룹화를 이해 하셨다고 보고 진행하며 연습파일에도 이미 피벗 테이블이 만들어져 있습니다. 피벗 테이블 만들기와 그룹화는 3포스팅에 걸쳐 자세히 소개 하였습니다.

146연습파일.xlsx

146연습파일_완성본.xlsx

1. 그룹화 접기

   엑셀의 피벗테이블의 그룹화를 하면 보고 싶은 데이터를 체계적으로 볼 수 있다는 장점도 있지만 그 그룹의 요약을 쉽게 볼 수 있다는 장점도 생김니다. 모든 그룹의 대그룹들은 그 소분류를 열고 닫을 수 있으며 소분류를 닫으면 바로 그 소분류의 요약본이 자동으로 계산 되어 집니다.

    

   2000년의 대분류, 2001년의 대분류를 – 버튼을 눌러서 닫고 2002년 1사분기의 중분류를 닫아 보겠습니다.

   

    

    

   이렇게 대분류를 닫는 순간 –버튼은 + 버튼으로 바뀌며 평균 급여는 2000년과 2001년에 입사한 전 직원의 평균 급여로 자동 계산 되어 집니다.

   

    

2. 머리글 행을 이용한 필터링

   피벗 테이블에서는 필터링 기능을 활성화 하지 않아도 이미 머리글 행에 필터링 기능이 있으며 워크시트에서 사용하던 필터링 방식과 동일 합니다. 즉, 날짜, 숫자, 텍스트 등 그 데이터 값에 따라 필터링 옵션이 다르며 모두 활용 가능 합니다.

    

   머리글 행의 분기를 클릭하면 분기별로 필터링 할 수 있는 옵션 박스가 나타납니다.

   

    

    

   여기서 1,2 분기를 지워 보겠습니다.

   

    

    

   모든 데이터는 이제 3분기와 4분기에 근거한 데이터 입니다.

   

    

    

   연의 머리글 행 필터를 클릭하면 연 단위로 필터링이 가능 하고 중복 필터링도 가능 합니다.

   

    

    

   2013년에 입사한 직원들의 평균 급여 표 입니다.

   

    

3. 필터 영역을 통한 필터링

   만약 매우 자주 사용하는 필터 라면 머리글 행에서 필터링을 해도 되지만 아예 따로 빼놓고 필터링을 하면 더 직관적이고 편합니다.

    

   행의 연을 필터 영역으로 옮기면 데이터 시트에 필터 영역이 따로 생성 됩니다.

   

    

    

   머리글 행이 하나 줄었으므로 데이터도 보기 편하고 이렇게 직관적으로 필터링을 할 수가 있습니다.

   

    

    

   머리글 행이 하나 줄었을 뿐인데 데이터는 훨씬 압축 되었고 가독성도 높아졌습니다.

   

    

4. 압축 형식vs 개요 형식vs테이블 형식

   레이블 반복과 레이블 반복 안함 옵션은 명확하지만 이 세 옵션은 헛갈릴 수 있습니다. 압축 형식은 가장 압축된 형태로 보여 주고 또 행 레이블과 열 레이블의 이름값을 필드로부터 가져오지 않고 "행 레이블", "열 레이블" 이라고 표기 합니다. 개요 형식과 테이블 형식은 거의 비슷하지만 테이블 형식은 셀의 구분선을 표기해 주고 기능적인 차이점도 존재 합니다.

    

   압축 형식 입니다. "행 레이블"이라는 머리글 행이 보이죠? 수동으로 바꿔줄 수는 있습니다.

   

    

    

   개요 형식 입니다. 행 레이블이 "연" 열 레이블이 "평균 급여"로 바뀌어서 더 직관적이고 가독성도 압축형 보다 높습니다.

   

    

    

   테이블 형식입니다. 개요 형식과 거의 같지만 셀의 구분선을 표기 합니다.

   

    

    

   모든 항목 레이블 반복 옵션입니다. 잘 쓰지 않겠죠?

   

5. 부분합 표시 .개요 형식 vs 테이블 형식

   개요 형식과 테이블 형식은 셀 구분선 이외에는 거의 같지만 이 부분합에서 약간의 기능 차이가 납니다. 개요 형식은 부분합을 위, 아래 둘다 표시해 줄 수 있지만 테이블 형식은 부분합을 아래에만 표기 가능 합니다.

    

   기본 옵션으로 행 열의 총합계만 표기 되어 있습니다 (테이블 형식)

   

    

    

   그룹 하단에 모든 부분합 표시로 설정 합니다.

   

    

    

   모든 분기 별로 이제 부분합이 표시 되었습니다. (테이블 형식)

   

    

    

   그룹 상단에 모든 부분합 표시를 클릭 합니다. (테이블 형식)

   

    

    

   변화가 없죠? 테이블 형식은 상단에 부분합을 표시 할 수 없습니다.

   

    

    

   개요 형식으로 바꾸고 한번 해 보겠습니다.

   

    

    

   그룹 상단에 부분합이 표기가 됩니다.

   

    

    

   엑셀의 피벗 테이블은 이렇게 필터링에서도 일반 워크시트 보다 훨씬 더 다양하고 강력한 필터링을 제공합니다. 지금껏 행만으로 피벗 테이블의 기능을 소개 했다면 다음 포스팅에 최초로 열이 등장하고 고급 그룹화의 개념을 소개 하겠습니다..