도시별 미세먼지 지도 차트 만들기(엑셀 버블 지도차트)

https://www.youtube.com/watch?v=2rhKb01PQv0    유튜브유튜브 - 오빠두엑셀 님의 강의 참고하여 제작함.

 

 

 

KOSIS 국가통계포털

https://kosis.kr/statisticsList/statisticsListIndex.do?publicationYN=Y&statId=1980006#content-group 

KOSIS 국가통계포털

 

국내통계 - 주제별 통계

환경-대기오염도현황 -미세먼지(PM10)-월별 도시별

조회 설정 클릭

(구) 일괄설정 클릭

필요한 내용 설정 후 적용

아래의 체크를 통해 오른쪽의 13개 정도 도시를 설정한다.

온도 설정은 긴 기간을 선택하여 조회 후 필요한 것에만 체크하여 일부부만 조회한다.

필요한 내용을 설정한 후 조회한 화면

(구) 필요한 내용 설정 후 적용

(구) 적용 결과

싸이트 아래쪽에 적당한 차트도 제공된다.

다운로드 클릭

엑셀 화면

셀 I2에 날짜 목록 만들기

I2셀 선택

데이터 - 데이터유효성검사 -데이터유효성검사

목록 - 셀범위선택

셀 I2의 버튼 모양 클릭 - 목록이 나타남

셀 꾸미기 (J~K열)

원하는 값 찾아오기 가로 2017.02 세로 서울 의  미세먼지 값 가져 오기 함수 : index, mtach

 

셀 K2의 수식 =INDEX($B$1:$F$14,MATCH(J2,$B$1:$B$14,0),MATCH($I$2,$B$1:$F$1,0))  수식 설명은 아래에 있음 설명하지 않는 필요 선행지식 : 절대주소와 상대주소

수식 해석 1 Match : 값을 찾아 위치를 숫자로 말해줘 J2의 값을 테이블 B1:B14 에 정확히(0) 찾아서 위치를 말해줘 결과 예시 3 : 니가 말한 그 내용은 테이블의 범위에서            세번째에 위치해 있어.

수식 해석 2 Match : 값을 찾아 위치를 숫자로 말해줘 I2의 값을 테이블 B1:F1 에 정확히(0) 찾아서 위치를 말해줘 결과 예시 5 : 니가 말한 그 내용은 테이블의 범위에서            다섯번째에 위치해 있어.

수식 해석 3 Match 두 함수의 값이 3과 5일 때 Index 표에서 위치의 값을 보여주는 함수 =INDEX( B1:F14 ,  3   ,  5) 테이블 B1:F14에서 3행 5열의 값을 가져와 3행 : 아래로 3칸 5열 : 오른쪽으로 5칸

수식 재해석 테이블 B1:F18 에서 도시명(서울)에 해당하는 행의 값과 날짜(2017.02)에 해당하는 열의 값을 찾아서 미세먼지 값을 가져오면 그 결과는 28이야 즉 서울의 2017.02의 미세먼지는 28이야!

채우기 핸들

K2 셀 선택 후 채우기 핸들로 K14까지 드래그하여 수식 채우기!!

전 후

빅데이터 수정 안동 46* 을 46으로 수정

K:L 열 선택 후 마우스 오른쪽 버튼 메뉴 "삽입"

X, Y 임의의 값 적당히 입력

적당한 지도 이미지 찾기

엑셀에 지도 이미지 넣기

표에 인접하지 않은 빈셀 선택 후 삽입 - 분산형 - 거품형차트 선택

새로 만든 비어있는 차트에서 마우스 우클릭 메뉴 데이터 선택

추가 클릭

각 계열에 제목줄을 제외하고  범위를 드래그 하여 입력

차트 제목 삭제

거품형 차트 선택 후 서식 - 채우기 없음

 

가로축 잘~ 선택 후 마우스 오른쪽 버튼 메뉴 축 서식

 

축 서식 최소값 0 최대값 100 기본 단위 5

 

y 축 서식도 동일하게 적용

 

지도 선택 후 서식 - 그림테두리 - 검정 선택

 

지도의 외곽선과 거품형 차트의 축의 크기를 조정하여 맞추기

 

서울의 위치 입력 x 22 y 80

각 도시의 좌표를 임의로 입력

 

축서식 - 레이블 - 레이블 위치 - 없음 이후 차트 크기 조절하여 지도에 맞추기

 

레이블이 없는 차트 모습

 

눈금선 선택 마우스 오른쪽 버튼  눈금선 서식  선 - 없음 세로, 가로 선 모두 없음

 

눈금선이 없는 모습

 

테두리 없애기 홈 - 찾기 및 선택 - 선택창

 

차트 눈 모양 off 그림을 선택 서식 - 그림 테두리 - 윤곽선 없음

 

윤곽선 없는 모습

 

차트 on

 

차트의 축 테두리 없애기 x축 선택 - 축서식 - 페인트통 모양 - 선 - 선 없음. y축도 동일하게 설정

 

깔끔한 모습

 

차트의 거품 선택 - 마우스 우클릭 메뉴 - 데이터 레이블 추가

 

다시 한번 마우스 우클릭  - 데이터 레이블 서식

 

레이블 위치 가운데

 

Y값 체트 해제 거품 크기 체크 O

 

셀 값 체크하여 팝업창의 범위에 도시 이름 범위 선택

 

구분 기호 - 줄바꿈

 

텍스트 옵션 흰색 선택

 

날짜를 바꾸어 가며 차트 변화를 확인함

부드러운 가장 자리

B1:F14 까지 셀 선택

 

삽입 - 세로 막대 - 3차원 세로 막대 선택

 

차트제목 삭제

 

차트에서 마우스 우클릭 데이터 선택

 

데이터 순서 변경

 

3차원 회전

 

완성 파일

데이터 확장 및 편집

위의 엑셀 파일에  6개 도시들의 자료를 추가 날짜도 추가해보겠다.

국가통계포털에 접속

https://kosis.kr/statisticsList/statisticsListIndex.do?publicationYN=Y&statId=1980006#content-group 

KOSIS 국가통계포털

아래와 같이 19개 도시를 선택

Shift키를 이용하여 2021.04~2019.05월까지 한 번에 선택한다. (2년 데이터) 적용 후 엑셀을 다른이름으로 저장한다.

데이터의 문제점과 단순화 *의 의미를 알 수 없으므로 단순 숫자 데이터로 변경

엑셀의 Iferror, Left 함수를 이용하여 데이터를 한번에 수정한다. 또는 하나씩 수정한다. 많은 데이터를 일괄수정하기 위해서는 엑셀과 같이 데이터를 한번에 다를 수 있는 프로그램을 익힐 필요가 있다. 그런 의미에서 파이썬이 주목 받고 있다.

=IFERROR(INT(C2),LEFT(C2,2))

엑셀의 값만 붙여 넣기 등을 통해 원본 데이터 자체를 붙여넣기 하여 수정한다.

새로 만든 데이터는 Z열까지 자리가 필요하다.

G~AA까지 열을 선택하여 마우스 우클릭하여 삽입을 클릭한다.

데이터를 마련된 자리에 붙여넣기 한다.

셀 AH2에 수식을 다음과 같이 수정하여 에러난 수식을 수정한다. =INDEX($B$1:$Z$20,MATCH(AE2,$B$1:$B$20,0),MATCH($AD$2,$B$1:$Z$1,0))

셀 AD2를 선택한 후 데이터 유효성 검사 - 목록의 범위를 변경한다.

도시의 좌표를 다시 입력하기 위해 거품차트의 축을 다시 On한다. 축의 위치쯤 클릭 마우스 우클릭하여 축서식 클릭

레이블의 위치를 없음 에서 축의 옆으로 설정한다.

세로축의 레이블로 축의 옆으로 변경한다.

 

이제 각각의 위치 좌표를 입력한다.

입력하다 보면 15행의  전북의 전주는 입력해도 거품이 보이지 않는다. 거품차트의 데이터 범위가 14행 까지이기 때문이다. 차트 선택 후 마우스 우클릭 데이터 범위 선택

계열1을 선택하여 편집한다.

X, Y, 거품 크기를 설정한다. 범위를 잡을 때는 이미 입력된 된 범위를 Del 키를 눌러 삭제한 후 범위를 드래그 하여 입력한다.

계열1 데이터 레이블 선택 레이블 옵션의 셀 값에서 범위를 20행까지 다시 입력한다.

나머지 도시의 좌표를 입력한다. 거품이 나타나지 않는 경우는 미세먼지 측정값이 없는 날짜이므로 최근 날짜로 변경 후 좌표를 확인한다.

축서식 - 레이블의 위치를 없음으로 변경한 후 차트의 위치와 크기를 변경하여 도시와 위치를 맞춘다.

 

각 도시의 시간에 따른 변화를 살펴보는 그래프를 그려보고자 한다. 먼저 데이터 처음과 끝만 보이도록 숨김 한다. E~X열까지 숨기기 한다.

셀 AB2를 선택한 후 틀고정 한다.

AS열에 2개의 도시 이름 칸을 만들고 데이터 유효성 검사의 목록을 통해 도시 이름을 선택할 수 있도록 한다.

날짜를 복사한다.

AT2  셀에 다음과 같이 수식을 입력한다. =INDEX($C$2:$Z$20,MATCH($AS2,$B$2:$B$20,0),MATCH(AT$1,$C$1:$Z$1,0))

수식을 입력 후 검정 십자 모양을 이용하여 BQ열까지 수식을 드래그한다.

2행을 선택, 복사하여 아래 도시의 옆에 붙여넣기 한다.

아래 도시 전체를 잘라내기하여 3행에 붙여넣기 한다.

표를 선택하여 묶은 세로 막대의 차트를 만든다. 도시명을 변경하며 차트를 활용한다.

춘천과 강릉을 선택하여 미세먼지 정도를 비교할 수 있다.

 

Sub TEST0()
 
Range("L2") = 1
 
If Range("L2") < 8 Then
    Application.OnTime Now + TimeValue("00:00:01"), "TEST1"
    
End If
 
End Sub
 

Sub TEST1()
 
If Range("L2") < 8 Then
    'Application.OnTime Now + TimeValue("00:00:01"), "TEST2"
    Application.OnTime Now + Range("S6").Value, "TEST2"
    
Else
MsgBox "끝."
End If
 
End Sub
 

Sub TEST2()
 
    Range("L2") = Range("L2") + 1
    Call TEST1
 
End Sub