앞선 글에서 엑셀의 피벗 기능을 사용하여 반응형 테이블 및 반응형 차트를 생성하는 방법을 소개한 바 있었다. (https://blog.naver.com/mythee1/223363279701)
이렇게 피벗에 대한 기본적 사용법을 익히게 되면 이를 활용하여 좀 더 보기 좋은 형태로 다음과 같은 반응형 대시보드를 만들 수가 있다는데, 본 글에서는 동일한 데이터를 활용하여 다음과 같은 반응형 대시보드를 만드는 자세한 과정을 소개한다.
먼저 다음과 같은 데이터를 사용한다. 이 데이터는 앞선 예제와 동일한 데이터인데, 행과 축만 바뀐 상태로 준비된 상태이다.
먼저 사용할 데이터 범위를 선택하고 Ctrl+T를 눌러주고
여기서, 확인을 눌러주면 다음과 같이 변경되는데,
이 상태에서, “삽입”–“피벗테이블” 순으로 메뉴를 선택해 준다.
그러면 다음과 같이 피벗 테이블에 사용할 필드와 선택에 사용할 슬라이서 필드를 지정할 수 있게 된다.
다음 부분을 드래그하여,
구체적으로는, 우선 피벗 테이블에 사용할 데이터들의 해당 필드를 선택해 주고,
마지막으로 선택의 기준으로 사용할 항목이 담긴 열(Column)에 해당되는 Month 필드를 선택한 상태에서 마우스 우측 버튼 메뉴를 활용하여 “슬라이서로 추가”해 준다.
그러면 다음과 같이 슬라이서 메뉴가 생성되고,
피벗 테이블에는 슬라이서에서 선택한 항목의 값들이 표시된다.
슬라이서에서 다른 항목을 선택하면 테이블의 값들이 반응하여 변경되는 것을 볼 수가 있다.
다음은 슬라이서에 따라 변화하는 반응형 피벗 차트를 추가하는 것이 필요하다.
먼저 “삽입”–> “피벗 차트” 순으로 메뉴를 선택해 준다.
이때 나타나는 화면에서 원하는 차트 종류를 선택후, 확인해주면
다음과 같이 피벗 차트가 시트에 삽입된다.
차트의 필드 버튼들은 보기에 좋지 않으므로, 차트를 선택한 후, 마우스 우측 메뉴로 “필드 단추 숨기기”를 선택해 준다.
그러면 다음과 같이 피벗 차트가 보기 좋게 정리된다.
그리고 나머지 필요한 차트 서식들을 원하는 대로 변경해 준다. 다음은 데이터 레이블을 추가하는 경우인데,
데이터 레이블 외에도 축제목이나, 축서식 등도 원하는 대로 변경해준다.
그리고, 피벗 테이블에서 다음 부분들에 있는 “합계 : ” 부분을 공란으로 대체해 준다.
(간혹 변경되지 않는 항목들이 있을 수도 있는데, 수작업으로 변경해 주어도 된다.)
다음과 같이 피벗테이블이 정리되고, 차트 내에 표시되는 축의 값들도 깔끔하게 정리된다.
이렇게 피벗 차트의 서식이 정리되면, 마지막으로 피벗 차트를 선택한 상태에서 마우스 우측 메뉴에 있는 “피벗 테이블 옵션”을 선택하고,
다음 부분을 해제해 준다.
이 부분의 해제는 데이타값이 변할때 차트의 크기가 변하지 않도록 하는데 필요하다.
이제는 이렇게 만들어진 슬라이서와, 피벗 차트를 별도의 시트로 옮겨 대시보드로 만들어줄 차례이다.
먼저, 슬라이서와, 차트를 Ctrl-X로 잘라낸 후, 대시보드용 시트에 와서 Ctrl-V로 붙여 넣어주면 끝이다.
그리고, 이 대시보드에 원하는 데이터를 넣어주기 위해,
다음과 같이 테이블을 선택해 주고, “삽입”–> “피벗테이블”의 형태로 메뉴를 선택해 준다.
그리고, 이 테이블이 들어갈 위치를 대시보드에서 지정해 준다.
그러면 다음과 같이 지정한 부분에, 또 하나의 피벗 테이블 형태로 데이터 테이블이 들어가게 된다.
이때 필드를 선택해주는 순서대로, 테이블에 해당 월의 자료가 추가되는 것을 주의해야 한다.
(참고: 만일 복잡한 것이 싫으면 이 부분을 단순 복사 및 붙여넣기로 가져와 사용해도 된다. )
그리고 피벗테이블의 각 월별 이름에 있는 “합계 :”부분을 “바꾸기” 메뉴를 사용하여 공란으로 대체하여 정리해 준다.
이 상태에서는 결과적으로 다음과 같은 형태가 된다.
다만, 아랫부분에 있는 테이블은 슬라이서와 연결되지 않은 별개의 독립적인 피벗 테이블이므로, 슬라이서에서 특정 항목을 선택하더라도 테이블 자체에는 변화가 없는 상태이다.(차트는 변화하지만..).
이 때문에 슬라이서 선택값과, 이 테이블을 연결해 주는 게 필요한데, 본 예제에서는 조건부 서식을 활용하여, 슬라이서의 선택 값에 따라, 테이블의 값에 변화를 주는 방법을 이용하여 이를 해결할 수 있다.
먼저 연결할 슬라이서 값을 표시하기 위해, 맨 처음 만들었던 피벗 테이블을 복사하여
그 밑에 붙여 넣어주고,
이를 선택한 후, 마우스 우측 버튼 메뉴에서 “필드 목록 표시”를 선택하면,
다음과 같은 화면이 나와 피벗테이블에 표시되는 항목을 변경할 수 있는데, 다음과 같이 슬라이서 값에 해당되는 필드인 “Month”만 선택하고 나머지는 모두 선택을 해제해 준다.
그러면, 대시보드의 슬라이서에서 선택하는 값에 해당되는 값이 다음과 같이 표시된다.
다음은 이렇게 표시되는 슬라이서 선택값에 따라, 대시보드의 테이블이 하이라이트 표시하는 것으로,
피벗 테이블에서 테이블의 범위를 선택하고,
이 상태에서, “삽입”–> “조건부 서식” –> “새 규칙”을 선택해 준다.
그러면 조건부 서식의 규칙을 지정하는 화면이 나오는데, 여기서 다음 항목을 선택한다.
그리고, 서식을 적용할 조건을 지정하면 되는데, 다음과 같이 기준이 되는 셀값을 선택하고
(주의 : $표시 사용에 주의한다- 서식을 적용할 범위모두에서 해당 행의 A셀에 있는 값을 이용해 조건비교가 되도록 지정해준다.)
이를 슬라이서 값과 연결해 주면 서식을 적용하는 조건식은 완성된다.
이렇게 충족해야 할 조건의 지정이 완료되면,
해당 경우에 적용할 서식을 “서식” 버튼을 이용하여 지정해 준다.
본 예제에서는 “굵은 글씨”와
노란색 바탕색으로 채우기를 선택했다.
“확인”을 눌러주면 다음과 같이 슬라이서 선택에 따라 해당 항목이 속한 행이 지정한 대로 노란색 바탕의 굵은 글씨로 표시됨을 볼 수 있다.
다른 슬라이서 항목을 선택하면 당연히 차트가 업데이트되고, 피벗 테이블에서 하이라이트 되는 부분도 변경된다.
필요시,
대시보드 상단부에 적당한 배경색을 넣어주고,
제목을 넣어주면 기본적인 반응형 대시보드가 완성된다.
이처럼 기본적인 반응형 대시보드 만드는 방법을 소개했는데,
본 글에서 사용한 예제 파일 다음 링크에 첨부해둔다.