엑셀은 피벗(Pivot)이라는 아주 유용한 기능을 제공하는데, 이를 이용하면 다음과 같이 버튼(슬라이서)을 선택함에 따라 값이 달라지는 반응형 테이블과, 반응형 차트를 쉽게 만들 수 있고, 이러한 테이블과 차트들만을 보기 좋게 모아서 반응형 대시보드를 만들 수 있다.
본 글에서는 다음과 같은 APT 관리비 데이터를 피벗을 사용하여, 위에서 소개한 반응형 테이블과 반응형 차트로 만드는 과정을 소개한다.
우선 Ctrl+T 명령을 입력하면 다음과 같은 표 만들기 창이 나오는데, 여기서 사용할 데이터 범위를 지정해 준다. 첫 행에 항목명이 있는 경우 “머리글 포함” 부분을 선택해 준다.
그러면 다음과 같은 형태로 테이블이 변환되어 사용하기에 좋은 형태가 된다.
이 상태에서 “삽입”–>”피벗테이블” 메뉴를 선택해 준다.
이때 다음과 같이 테이블의 범위를 재확인하는 창이 열린다.
그리고 이 단계에서 표의 이름을 지정할 수도 있는데, 그냥 두어도 무방하다. (본 글에서는 변경하지 않았다.)
위의 창에서 “확인”을 해주면 다음과 같이 “피벗 테이블 필드” 창이 나타나는데, 아랫부분에서 붉은색으로 표기된 부분을 드래그해주면,
사용할 수 있는 필드들의 목록이 나타난다.
여기서 사용할 항목들을 선택해 주면, 다음과 같이 시트에 값들이 표기된다.
또한 각 항목별로 각 항목의 통계치(예: 합계, 평균 등)의 값이 나타난다.(기본값은 합계이다.)
만일 각 항목별 통계치의 종류를 변경하고자 하면, 해당 항목을 마우스로 선택 후, 마우스 우측 버튼을 클릭하면 나타나는 다음 메뉴에서, 원하는 대로 변경해 주면 된다.
참고) 만일 통계치로 “합계” 대신 “평균”을 선택하는 경우, 다음과 같이 변경되어 반영됨을 볼 수 있다.
이러한 방법으로 만들어진 다음 피벗 테이블에서
피벗테이블의 첫행/첫 열에 포인터를 선택하면 다시 다음과 같이 피벗 테이블이 나타나는데,
여기서 선택의 기준으로 사용할 “Month”를 선택해제한다.
그 대신 이 부분(“Month” 부분)에서 마우스 우측 버튼 메뉴를 눌러 나타나는 다음 화면에서,
다음과 같이 “슬라이서로 추가” 메뉴를 선택해 준다.
그러면 다음과 같이 Month 부분이 일종의 선택 버튼 메뉴 형태로 나타나게 된다. 이것을 슬라이서(slicer) 라고 한다. 또한 표시되는 테이블은 특정 월의 값이 변화하면서 나타나게 된다.
이런 식으로 반응형 테이블이 구현되었다.
여기서 슬라이서를 선택함에 따라 변화하는 차트를 추가하려면, 다음과 같이 “삽입”–>”피벗 차트”를 선택해 준다.(주의 : 일반 차트와 별개의 메뉴임)
피벗 차트 메뉴를 선택하면 나타나는 다음 화면에서, 일반 차트에서처럼 원하는 차트 형태를 선택해 준다.
“확인”을 눌러주면 해당 차트가 자동으로 삽입되는데, 상단에 나타나는 필드 단추들은 보기에 불편할 수 있으므로, 해당 부분을 클릭한 상태에서 마우스 우측 버튼 메뉴를 이용하여 모두 숨길 수가 있다.
그러면 보기에 깔끔한 피벗 차트가 되는데
이 상태에서 범례의 위치를 원하는 대로 조절하고,
이 상태에서 글자들의 크기나 색상 등의 서식을 원하는 대로 조절하고, “보기” 메뉴에서 엑셀 시트의 눈금선을 해제해주면 다음과 같이 깨끗한 화면을 얻을 수가 있다.
그리고, 슬라이서를 선택함에 따라, 차트의 크기가 변하지 않는 상태를 유지하려면, 다음과 같이 차트를 클릭한 상태에서 “피벗 차트 옵션” 메뉴에 들어가
다음과 같이 “업데이트 시 열 자동 맞춤” 버튼의 선택을 해제해 준다. (주의: 해제하지 않는 경우, 값이 바뀔 때마다, 차트의 크기가 변한다.)
이렇게 하면, 슬라이서 선택에 따라 변화하는 반응형 차트와 반응형 테이블을 만드는 과정을 소개했다.
참고로 본 글에서 다룬 예제 파일은 다음 링크에 첨부해 두었다.