엑셀 피벗과 대시보드 1 – 피벗테이블과 반응형 차트 만들기

엑셀은 피벗(Pivot)이라는 아주 유용한 기능을 제공하는데, 이를 이용하면 다음과 같이 버튼(슬라이서)을 선택함에 따라 값이 달라지는 반응형 테이블과, 반응형 차트를 쉽게 만들 수 있고, 이러한 테이블과 차트들만을 보기 좋게 모아서 반응형 대시보드를 만들 수 있다.

​

​

본 글에서는 다음과 같은 APT 관리비 데이터를 피벗을 사용하여, 위에서 소개한 반응형 테이블과 반응형 차트로 만드는 과정을 소개한다.​

​

​

우선 Ctrl+T 명령을 입력하면 다음과 같은 표 만들기 창이 나오는데, 여기서 사용할 데이터 범위를 지정해 준다. 첫 행에 항목명이 있는 경우 “머리글 포함” 부분을 선택해 준다.

​

그러면 다음과 같은 형태로 테이블이 변환되어 사용하기에 좋은 형태가 된다.

​

이 상태에서 “삽입”–>”피벗테이블” 메뉴를 선택해 준다.

​

이때 다음과 같이 테이블의 범위를 재확인하는 창이 열린다.

그리고 이 단계에서 표의 이름을 지정할 수도 있는데, 그냥 두어도 무방하다. (본 글에서는 변경하지 않았다.)

​

​

위의 창에서 “확인”을 해주면 다음과 같이 “피벗 테이블 필드” 창이 나타나는데, 아랫부분에서 붉은색으로 표기된 부분을 드래그해주면,

​

사용할 수 있는 필드들의 목록이 나타난다.

​

​

여기서 사용할 항목들을 선택해 주면, 다음과 같이 시트에 값들이 표기된다.

또한 각 항목별로 각 항목의 통계치(예: 합계, 평균 등)의 값이 나타난다.(기본값은 합계이다.)

​

​

만일 각 항목별 통계치의 종류를 변경하고자 하면, 해당 항목을 마우스로 선택 후, 마우스 우측 버튼을 클릭하면 나타나는 다음 메뉴에서, 원하는 대로 변경해 주면 된다.

​

참고) 만일 통계치로 “합계” 대신 “평균”을 선택하는 경우, 다음과 같이 변경되어 반영됨을 볼 수 있다.

​

이러한 방법으로 만들어진 다음 피벗 테이블에서

​

피벗테이블의 첫행/첫 열에 포인터를 선택하면 다시 다음과 같이 피벗 테이블이 나타나는데,

여기서 선택의 기준으로 사용할 “Month”를 선택해제한다.

그 대신 이 부분(“Month” 부분)에서 마우스 우측 버튼 메뉴를 눌러 나타나는 다음 화면에서,

다음과 같이 “슬라이서로 추가” 메뉴를 선택해 준다.

​

그러면 다음과 같이 Month 부분이 일종의 선택 버튼 메뉴 형태로 나타나게 된다. 이것을 슬라이서(slicer) 라고 한다. 또한 표시되는 테이블은 특정 월의 값이 변화하면서 나타나게 된다.

이런 식으로 반응형 테이블이 구현되었다.

​

여기서 슬라이서를 선택함에 따라 변화하는 차트를 추가하려면, 다음과 같이 “삽입”–>”피벗 차트”를 선택해 준다.(주의 : 일반 차트와 별개의 메뉴임)

​

​

피벗 차트 메뉴를 선택하면 나타나는 다음 화면에서, 일반 차트에서처럼 원하는 차트 형태를 선택해 준다.

​

“확인”을 눌러주면 해당 차트가 자동으로 삽입되는데, 상단에 나타나는 필드 단추들은 보기에 불편할 수 있으므로, 해당 부분을 클릭한 상태에서 마우스 우측 버튼 메뉴를 이용하여 모두 숨길 수가 있다.

그러면 보기에 깔끔한 피벗 차트가 되는데

이 상태에서 범례의 위치를 원하는 대로 조절하고,

​

이 상태에서 글자들의 크기나 색상 등의 서식을 원하는 대로 조절하고, “보기” 메뉴에서 엑셀 시트의 눈금선을 해제해주면 다음과 같이 깨끗한 화면을 얻을 수가 있다.

​

​

그리고, 슬라이서를 선택함에 따라, 차트의 크기가 변하지 않는 상태를 유지하려면, 다음과 같이 차트를 클릭한 상태에서 “피벗 차트 옵션” 메뉴에 들어가

다음과 같이 “업데이트 시 열 자동 맞춤” 버튼의 선택을 해제해 준다. (주의: 해제하지 않는 경우, 값이 바뀔 때마다, 차트의 크기가 변한다.)

​

이렇게 하면, 슬라이서 선택에 따라 변화하는 반응형 차트와 반응형 테이블을 만드는 과정을 소개했다.

참고로 본 글에서 다룬 예제 파일은 다음 링크에 첨부해 두었다.

​