[ 엑셀 Tip ] 피벗테이블 활용 ( feat. 동물병원 )

최근 반려동물 인구가 많이 늘어나고 있고 반려견을 소재로 한 프로그램도 참 많이 방영이 되는 것 같습니다.

최근 행정안전부에서 운영하는 데이터 사이트인 "LOCALDATA"가 있어서 동물병원 데이터로 어떠한 상황이 있는지 알아보도록 하겠습니다.

LOCALDATA - 지방행정인허가데이터개방:데이터다운로드

해당 사이트의 동물 ▶ 동물병원 데이터를 다운받아서 활용해 보도록 하겠습니다.

---

데이터 탐색

다운 받은 데이터를 탐색해 보도록 하겠습니다. 다운로드한 데이터는 아래와 같이 나타나는데 엑셀 피벗테이블을 사용하겠지만 데이터 탐색을 위해서 Power Qeury를 활용해서 해당 컬럼에 대해서 알아보도록 하겠습니다.

엑셀 데이터 탭에서 테이블 / 범위에서를 클릭 후 우측과 같은 창이 나오면 확인을 눌러줍니다.

다음과 같은 창이 나오는데 Power Qeury 편집기 창이라고 불리는 메뉴 입니다.

수식 입력창에 Fx를 누르고 = Table.ColumnNames( #"변경된유형" )이라고 입력하면 아래와 같이 컬럼만 구할 수 있습니다.

1. 번호 : 데이터 index ( 순서 ) 2. 개방서비스명 : 동물병원 4. 개방자치 단체 코드 : 지역명 6. 인허가일자 : 설립일 11. 상영업상태명 : 폐업, 휴업, 영업 12. 폐업일자 : 폐업 날짜 19. 소재지전체주소 : 영업장 주 22. 사업장명 : 사업장 주

전체 컬럼에서 해당 컬럼 정도를 활용하면 간단한 분석이 가능할 것 같습니다.

Power Qeury 편집기 창은 닫아주고 분석을 표를 이용한 분석이 더 용이하기 때문에 표로 변환된 데이터는 그냥 활용해 주도록 하겠습니다.

표의 다양한 활용법은 아래의 글을 참고해 주시면 됩니다.

[ 엑셀 Tip ] 표만들기 및 표 기능 활용 방법

아까와는 다르게 음영이 표시되면서 표로 변환이 된 것을 볼 수 있습니다. 표로 만들어진 음영이 처음 보는 것이라서 낯설게 느껴진다면 테이블 디자인에서 맨 아래 있는 표 지우기를 누르면 표는 유지되고 비주얼도 처음과 같이 익숙하게 만들어 주시면 됩니다.

표의 서식만 지운 것이지 표를 다시 일반으로 변환한 것이 아니기 때문에 테이블 디자인 탭 사용이 가능하고 맨 아래에는 아래에 데이터를 붙이면 자동으로 데이터가 확장될 수 있도록 하는 표시가 남아있습니다.

---

피벗테이블 만들기

테이블의 아무 곳이나 선택 후 삽입 ▶ 피벗 테이블 ▶ 테이블 / 범위에서(T)를 눌러서 피벗 테이블을 만들어 보도록 하겠습니다.

표 또는 범위의 피벗 테이블 창이 나오면 확인을 눌러줍니다.

다음과 같이 피벗테이블이 이용 가능한 상태가 되었습니다.

① 영업상태 확인

행에 영업상태명을 놓고 값에 영업상태명을 드래그래서 옮겨주면 해당 상태의 조건의 개수를 구할 수 있습니다.

총 9634개의 결과가 나오는데 데이터 원본을 확인하면 동일한 개수로 데이터 활용이 가능할 것 같습니다.

과거부터 지금까지 폐업은 현재 영업을 하는 수준과 비슷하게 나타나고 있는 것을 볼 수 있습니다.

반응형

② 시계열 영업장 증감

영업상태명을 필터에 옮기고  영업/정상을 선택한 후에 행에 인허가일자를 넣으면 연도별 설립 수를 볼 수 있습니다.

해당 데이터를 피벗테이블보다는 시각화를 통해서 보는 것이 가시적으로 좋을 것 같아 피벗 차트를 활용해서 그래프를 만들어 보겠습니다.

삽입 ▶ 피벗 차트 ▶ 피벗 차트를 눌러 그래프를 만들어 줍니다.

동물병원이 지속적으로 증가하는 추세를 확인할 수 있으며 병원이 많아진다는 것을 그만큼 수요 있다는 것을 파악할 수 있는 것 같습니다.

범례에 폐업과 같이 데이터를 보니 2000년 초에 엄청난 수가 폐업을 하고 이후 에는 폐업은 감소하고 설립은 증가하여 전체 동물병원의 수가 지속적으로 증가하고 있음을 알 수 있습니다.

③ 지역별 동물병원 현황

개방자치단체코드를 행에 넣으면 지역별 동물병원수를 확인할 수 있는데 코드로 되어 있어서 알기가 쉽지 않습니다.

소재지 주소를 행에 추가하면 대략적으로 어느 지역인지 파악할 수 있지만 데이터가 방대해져서 비효율적으로 결과가 나올 수 있습니다.

사이트의 질의응답을 찾아보니 아래와 같이 코드집이 있는 것을 볼 수 있었습니다. 이것을 원본에 추가하면 보기 쉽게 데이터 정리가 가능할 것 같습니다. 코드에 시도_시군구명을 넣기 위해서 빈 공간을 채우고 데이터를 가공해야 할 것 같습니다.

빈 공간을 채우기 위해서 시도명과 시도(자치단체)코드를 선택 후 Ctrl + G를 누르고 옵션을 눌러 줍니다.

빈셀을 선택하고 확인을 눌러주면 우측과 같이 선택이 되는데 + 누르고 ▲를 누른 후 Ctrl + 엔터를 누르면 아래와 같이 빈칸이 채워집니다.

 

수식을 이용해서 새로운 컬럼을 하나 만들어 주겠습니다. 그리고 vlookup 함수를 사용하기 위해서 텍스트를 숫자로 변환해 주도록 하겠습니다.

원본에 지역명이라는 컬럼을 추가하고 vlookup 함수를 사용하면 지역명이 나타나는 것을 알 수 있습니다.

피벗테이블에서 새로 고침(R)을 눌러 줍니다. 지역명 컬럼이 새로 생성되고 행에 추가하면 다음과 같이 피벗테이블이 만들어집니다.

이전보다 훨씬 가독성이 좋아진 것을 볼 수 있습니다. 

지역도 좋지만 시군구도 같이 보면 더욱 좋을 것 같아서 같은 방법으로 시군구를 추가해 주도록 하겠습니다.

시군구 데이터 활용이 가능하고 등치지역도 지도도 만들어 볼 수 있을 것 같습니다.

[ 엑셀 Tip ] 대한민국 인구현황 맵차트 만들기 ( feat. 등치지역도 )

전체 필드를 축소하고 값 필드를 선택하고 정렬 ▶ 숫자 내림차순 정렬을 하면 병원이 많은 지역부터 순서대로 정렬이 가능합니다.

1. 경기도 2. 서울특별시 3. 경상북도

경기도 내에서는 고양시, 성남시, 용인시 순으로 나타나고 있으며 서울을 강남, 송파, 강동 순으로 나타나고 있습니다.

 

값에서 값 표시 형식에서 총합계 비율을 클릭하면 총합계에서 해당 지역의 구성비를 볼 수 있습니다.

이 외에도 다양한 분석이 가능하니 해보시고 더 많은 인사이트 도출하는 기회가 되셨으면 좋겠습니다.