[ 엑셀 Tip ] 히트맵( Heatmap ) 만들기( Feat. 한라산 탐방객 )

한라산 탐방객 데이터를 이용해서 히트맵을 만들어 보도록 하겠습니다.

제주특별자치도_한라산탐방객현황_20240430

데이터는 위의 공공데이터서 받아 주시면 됩니다.

물론 테이터를 선택 후 우측과 같이 라인 차트를 만들어 트렌드를 쉽게 확인하는 것도 가능하겠으나 

만약 다양한 궁금증이 발생하다면 빠르게 대응하기 어려운 경우가 발생하는 상황이 많이 생기게 됩니다.

만약 탐방로별 계절별 방문 트렌드는 어떠한지? 탐방로별 방문각 방문 가중치는 어떠한지?

이러한 부분들을 즉각적으로 대응하기 위해서는 테이블 형식보다는 분석이 용이한 피벗테이블 사용이 용이할 것입니다.

---

① 피벗테이블 만들기

데이터를 선택 후 삽입 - 피벗테이블 - 테이블/범위서를 클릭하여 피벗테이블을 만들어 줍니다.

컬럼을 하나하나 값에 넣어 주었으나 합계가 생성되지 않는 것을 확인할 수 있습니다.

사실 분석이라는 것은 큰 것을 쪼개서 해석하는 것인데 이렇게 하면 큰 항목을 보기 어려워 보입니다.

 

② 파워피벗 테이블 만들기

피봇테이블을 뛰어넘기 위한 파워 피벗을 만들어 보도록 하겠습니다.

해당 파일은 CSV 파일이기 때문에 새로운 엑셀을 하나 만들어 해당 파일을 불러와 보도록 하겠습니다.

데이터 - 파일에서 - 텍스트/CSV에서를 클릭 후 

 

파워쿼리 창을 열어 첫행을 머리글로 사용까지 해 보도록 하겠습니다. 필요 없는 데이터 기준일자는 제거해 주도록 하겠습니다.

구분년월을 클릭 후 다른 열 피벗 해제를 눌러 줍니다.

다음과 같이 가로형 데이터가 세로로 변경되는 것을 볼 수 있습니다.

특성, 값이라는 머리글을 "탐방로", "탐방객수" 라는 항목으로 변경해 보도록 하겠습니다.

머리글을 더블 클릭하여 다음과 같이 이름을 변경하며 바꾼 열 수의 단계를 추가할 수도 있지만 

단계가 추가 되는 것은 빅데이터 처리에서 효율성이 떨어지기 때문에 열피벗 해제를 하면서 바로 머리글을 변경해 보도록 하겠습니다.

열피벗 해제 시 생성되는 "특성"을 "탐방로"로 "값"을 "탐방객수"로 변경하면 바로 머리글이 변경되는 것을 볼 수 있습니다.

닫기 및 다음으로 로드 클릭 후 연결만 만들기 & 데이터 모델로 로드를 눌러 파워 쿼리 편집기를 닫아 줍니다.

삽입 - 피벗테이블 - 데이터 모델에서를 클릭 후 피벗테이블을 다시 만들어 주도록 하겠습니다.

아까와는 다르게 컬럼이 3개만 존재하고 탐방객수를 넣었더니 전체 탐방객수가 나오는 것을 확인할 수 있습니다.

탐방로를 열에 넣었더니 아까와는 다르게 총합계가 나오는 것을 확인 할 수 있습니다.

반응형

 

③ 데이터 모델링

Power Pivot - 관리를 누르자 다음과 같은 창이 나오는 것을 볼 수 있습니다.

사실 구분년월은 "2014-01"의 형식이었으나 01이 추가 되면서 날짜 형식으로 변경된 것을 확인할 수 있습니다

효율적인 시계열 분석을 하기 위해서 데이터 모델링을 해 보도록 하겠습니다.

불필요한 항목을 지워주고 디자인 날짜 테이블 - 새로만들기는 눌러줍니다.

Calendar 시트가 추가되면서 다음과 같은 창이 만들어 지는 것을 볼 수 있습니다.

다 사용해도 상관은 없지만 필요한 항목은 년도, 월, 년월이 필요하기 때문에 YYYY-MM 컬럼을 YearMonth로 이름을 바꾸고

형식을 "yy-mm"으로 지정해 년월 항목을 만들어 주었습니다.

다이어그램 보기를 누른 후 Calendar의 Date를 마우스로 잡은 후 제주특별자치도_...의 구분년월 위로 가저가면 다음과 모델링이 완료되었습니다.

데이터 모델링 : 1 에서 *쪽으로 필터를 적용 ( 엑셀의 vlookup과 유사 )
                        [ 목적 : 하나의 테이블에서 날짜 및 다양한 항목을 만들면 가로로 넓어져 효율성 저하 ]

연도별 / 탐방로별 탐방객수 

년월 / 탐방로별 탐방객수

다이나믹한 확인이 가능하며 소계가 있는 것이 좋을 것 같아 디자인 - 부분합 - 그룹 상단에 모든 부분합 표시를 눌러

우측과 같이 소계를 만들어 주었습니다.

728x90

④ 데이터 시각화 ( 히트맵 )

홈 - 조건부 서식 - 색조에서 해당 색을 선택해 주면 피벗테이블 히트맵 만드는 것이 가능합니다.

그런데 돈내코는 다른 탐방로 대비 탐방객 수가 적기 때문에 항상 적은 것처럼 보일 수 있습니다.

이때 각 탐방로별 독립적으로 만들어 해당 탐방로별 히트맵 만드는 것도 가능합니다.

Dax 수식을 사용해서 탐방로별 구성비를 구해 보도록 하겠습니다.

구성비에 calculate 함수를 사용해서 피벗 테이블의 YearMonth의 필터를 풀어줍니다

그러면 각 탐방로별 탐방객수의 계가 들어가는 것을 볼 수 있습니다.

탐방객수 / 탐방로별 총 탐방객수로 나누어 구성비를 만들어 주도록 하겠습니다.

다음과 같이 탐방로별 구성비를 구할 수 있습니다.

탐방로별 구성비를 쭉 내려서 합을 구해 보시면 100%가 되는 것을 확인할 수 있습니다.

아까와는 다른 히트맵의 모습이 되었습니다.

행렬을 바꿔주면 시계열 방문자 현황 트렌드를 확인 할 수 있습니다.

텍스트 때문에 전체가 한 번에 눈에 들어오지 않아서 텍스를 없애고 다듬어 보도록 하겠습니다.

커스텀 항목에 ;;;을 입력해 주고 확인을 눌러 줍니다. 년월은 텍스트가 너무 넓어 년월로 변경해주면 아래와 같이 히트맵을 만들 수가 있습니다.

세로로 변경해 주면 탐방로별 방문객수 확인이 가능합니다.