엑셀 지도 차트 만들기, 오류 없이 쉽고 빠르게 만드는 방법

중앙재난안전대책본부에서 제공하는 원본 데이터를 참고하여 코로나 실시간 발생현황 지도 차트 제작 방법을 단계별로 알아봅니다.

홈페이지 » 엑셀 지도 차트 만들기, 오류 없이 쉽고 빠르게 만드는 방법

엑셀 지도 차트 만들기, 오류 없이 쉽고 빠르게 만드는 방법

엑셀 지도 차트 만들기 목차 바로가기
영상 강의

예제파일 다운로드

강의 소개

이번 강의에서는 중앙재난안전대책본부에서 제공하는 코로나 바이러스 발생현황 원본 데이터를 참고하여 대한민국의 코로나 발생 현황을 지도 차트로 시각화하는 방법을 알아봅니다. 엑셀의 기본 기능만 활용하여 중앙재난안전대책본부에서 제공하는 지도 차트 양식과 동일하게 제작할 수 있습니다.

코로나 발생 현황 대시보드 맵차트
이번 강의에서는 코로나 발생현황 지도 차트를 엑셀 기본 기능만 사용하여 제작합니다.

본 강의에서 사용된 지도 차트는 아래 링크를 참고하여 제작되었습니다.

라이브 강의에서 제작한 코로나 발생현황 대시보드 완성파일은 아래 링크에서 다운로드 가능합니다.

코로나 발생현황 원본 데이터 링크

지도차트에 사용된 코로나 바이러스 지역별 발생 현황 원본 데이터는 아래 링크에서 확인할 수 있습니다. 아래 링크로 이동한 뒤, 페이지에 있는 표를 복사하여 엑셀파일 [Raw] 시트의 [시도별 발생동향] 표 위에 그대로 붙여넣기하면 [데이터] 시트의 값이 자동으로 업데이트 됩니다.

코로나 발생 현황 원본 데이터 링크
링크로 이동한 뒤, 지역별 발생 현황 테이블을 엑셀 시트 위에 그대로 복사/붙여넣기 합니다.

엑셀 지도 차트 만들기 (엑셀 2019 이후 버전)

이번 강의에 사용된 지도 차트는 엑셀 2019 이후 버전에서만 지원됩니다. 따라서 엑셀 2019 이전 버전 사용자는 이번 단계를 넘어가고 다음 단계부터 진행합니다.

  1.  예제파일 [데이터] 시트로 이동한 뒤, [B6:D23] 범위를 선택합니다. (나라:누적환자) 이후 [삽입] - [차트] - [추천차트]를 클릭하거나 단축키 Alt - N - R 로 추천차트 대화상자를 실행합니다.

    지도 차트 생성 범위 선택
    차트를 생성할 범위를 선택한 뒤, 추천 차트 대화상자를 실행합니다.
  2. 추천차트 목록에서 '지도 차트'를 선택한 뒤, [확인] 버튼을 눌러 지도 차트를 생성합니다. (지도차트가 추천차트 목록에 표시되지 않을 경우, [모든차트] - [지도] 로 이동하여 차트를 생성합니다.)
    엑셀 지도 차트 만들기
    지도 차트를 선택한 뒤, [확인] 버튼을 눌러 차트를 생성합니다.
  3. 차트의 제목과 범례를 지워 차트를 간소화합니다. 이후 차트의 지도범위를 우클릭하여 [데이터 계열 서식]으로 이동한 뒤, [계열 색] - [분기 3색]을 선택합니다.

    차트 계열 분기 3색
    데이터계열서식 - 계열색 - 분기 3색을 선택합니다.
  4. 현재 데이터의 경우 '대구'의 값이 6,930 으로 월등이 높으므로 최대값과 최소값을 기준으로 차트색상을 표시할 경우 대구지역만 진한색으로 표시되는 문제가 발생합니다. 따라서 보편적으로 인식가능한 값으로 최대값과 중간값, 최소값을 변경합니다.
    최소값 : [숫자] 50
    중간값 : [숫자] 800
    최대값 : [숫자] 2000
    지도 차트 불균형 데이터
    최대값 데이터가 나머지 데이터에 비해 비정상적으로 높은 상황입니다.

    지도차트 데이터 계열 값 변경
    계열색의 최소값, 중간값, 최대값을 임의값으로 변경합니다.
  5. 최대값은 진한 파란색, 중간값은 하늘색, 최소값은 옅은 회색으로 채우기 색상을 변경합니다.

    엑셀 지도 차트 색상 변경
    최대값, 중간값, 최소값의 지도 색상을 변경합니다.
  6. 지도 차트가 완성되었습니다. 완성된 차트를 잘라내기 한 뒤, [코로나 대시보드] 시트 위로 붙여넣기합니다. 기존 대시보드 시트에 있는 안내문구와 물음표 아이콘을 우클릭 한 뒤, [맨 앞으로 가져오기]를 선택하여 차트 위에 표시되도록 위치를 변경합니다.
    안내 문구 맨 앞으로 가져오기
    기존 안내문구를 우클릭하여 [맨 앞으로 가져오기]를 선택하면, 안내문구가 맨 앞으로 이동합니다.
  7. 차트의 윤곽선을 제거하면 대시보드 위로 지도 차트 삽입이 완료됩니다.

    지도 차트 완성
    코로나 발생현황 지도 차트가 완성되었습니다.

엑셀 지도 그림 삽입하기 (엑셀 2019 이전)

엑셀 2019 이전 버전에서는 지도차트를 지원하지 않습니다. 따라서 2019 이전 버전을 사용중일 경우 [코로나 대시보드] 시트에 미리 넣어드린 지도 그림 이미지를 잘라낸 뒤 위에 붙여넣기 하여 지도차트 대용으로 사용합니다.

엑셀 2019 이전 버전 지도 그림 사용
엑셀 2019 이전 버전 사용자는 [대시보드] 시트의 지도 그림을 차트 대용으로 사용합니다.

연결된 이미지로 지역별 발생현황 레이블 만들기

  1. 시트 목록 오른쪽의 (+) 더하기 버튼을 클릭하거나 단축키 Shift + F11 키로 새로운 시트를 추가합니다.

    새 시트 추가하기
    새로운 시트를 추가합니다.
  2. [B2:S4] 범위를 선택한 뒤, 아래 수식을 입력 후 Ctrl + Shift + Enter 로 배열수식을 입력합니다. (Microsoft 365 사용자는 B2셀 선택 후 아래 수식을 Enter 로 입력하면 파란색 테두리가 쳐지면서 배열이 자동으로 반환됩니다.)

    transpose 범위 선택
    TRANSPOE 함수를 배열수식으로 입력합니다. (365 버전 사용자는 일반 수식으로 입력해도 괜찮습니다.)
  3. 각 범위에 폰트를 적용하여 깔끔하게 변경한 뒤, 범위 채우기 색상을 흰색으로 변경합니다. 강의에서는 Noto Sans (본고딕) 폰트를 사용하였습니다.
  4. [B4:S4] 범위 (신규 확진자수) 를 선택한 뒤, [우클릭] - [셀 서식]을 선택하거나 단축키 Ctrl + 1을 눌러 셀 서식 대화상자를 실행합니다. [사용자 지정]을 선택한 뒤, 형식에 아래 서식을 복사/붙여넣기 후 [확인]을 눌러 셀 서식을 적용합니다.
    (+0);;(0);

    셀 서식 변경하기
    셀 서식을 변경합니다.
  5. 연결될 이미지로 만들 지역별 발생현황 레이블이 완성되었습니다. 첫번째 값인 대구 범위를 선택한 후 Ctrl + C 로 복사합니다.

    첫번째 범위 복사
    첫번째 범위를 선택한 뒤 복사합니다.
  6. B6 셀을 선택한 뒤 우클릭하여 [선택하여 붙여넣기] - [연결된 그림] 으로 삽입하면 복사한 범위가 이미지 형태로 삽입됩니다. 이와 같은 작업을 18번 반복하여 연결된 이미지를 삽입할 수도 있지만, 반복되는 작업을 최소화하기 위해 아래 매크로를 사용하여 연결된 이미지를 한 번에 삽입할 수도 있습니다.

    연결된 그림 붙여넣기
    복사한 범위를 연결된 그림으로 붙여넣기 합니다.

매크로를 사용하여 연결된 이미지를 한 번에 만드는 방법

  1. 기존에 삽입했던 연결된 그림을 모두 삭제합니다. 화면 상단의 [개발도구] - [Visual Basic]을 클릭하거나 단축키 Alt + F11 를 눌러 매크로 편집기를 실행합니다.

    개발도구 visual basic 이동
    개발도구 - Visual Basic 버튼을 클릭하여 매크로 편집기를 실행합니다.
  2. [삽입] - [모듈]을 선택하여 새로운 모듈을 추가한 뒤, 아래 명령문을 복사하여 붙여넣기 합니다.
    Sub CreateLinkedImage()
     
    Dim rng As Range
    Dim ws As Worksheet
    Dim sFormula As String
    Dim pic As Shape
     
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
     
    Set ws = ActiveSheet
    Set rng = ws.Range("B2").CurrentRegion   '<- 시작셀 주소를 입력하세요
     
    With ws
        For i = rng.Column To rng.Column + rng.Columns.Count - 1
            .Range(.Cells(rng.Row, i), .Cells(rng.Row + rng.Rows.Count - 1, i)).Copy
            .Cells(rng.Row + rng.Rows.Count + 1, i).Select
            .Pictures.Paste link:=True
        Next
     
        For Each pic In ws.Shapes
            sFormula = pic.DrawingObject.Formula
            .Pictures(pic.Name).Formula = "='" & ws.Name & "'!" & Trim(sFormula)
        Next
    End With
     
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
     
    End Sub
  3. 매크로 편집기 상단의 재생버튼을 클릭하거나 단축키 F5키를 눌러 명령문을 실행하면 각 지역별 발생현황 레이블을 한 번에 생성할 수 있습니다.

    매크로 편집기 실행
    매크로를 실행하면 지역별 발생현황 연결된 이미지를 한번에 만들 수 있습니다.

지도 차트 위로 레이블 추가하기

  1. 연결된 이미지를 모두 선택하여 잘라내기 한 뒤, [코로나 대시보드] 시트에 붙여넣기 합니다.

    연결된 그림 대시보드 위 붙여넣기
    지역별 발생현황 연결된 그림을 잘라낸 뒤, 대시보드에 붙여넣기 합니다.
  2. 각 지역별로 레이블을 하나씩 이동합니다. 해외 감염자는 대시보드 아래쪽에 있는 도형 위에 표시합니다.

    지도 차트 위 레이블 추가
    각 레이블을 지도 차트 위로 이동합니다.
  3. 감염자 수 크기에 따라 레이블 크기를 조절하면 대한민국 코로나 발생현황 지도 차트 제작이 마무리됩니다.

    엑셀 지도 차트 완성
    레이블 값에 따라 크기를 조절하면 코로나 발생현황 지도 차트가 완성됩니다.
5 4 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
5 Comments
Inline Feedbacks
View all comments
오일깜
오일깜
2020년 8월 25일 8:54 오후
게시글평점 :
     

상상 그이상에 강의 입니다!! 실무로 물품 지역별 분배(안) 작성하면서 매번 엑셀로 계산해서 한글로 편집하여 표로 완성 했었는데.... 이제 한번(엑셀)에 작성 도전해보겠습니다. 매크로도 점점 흥미가 생기는데 아직은 어렵기만 하네요. 쉽고 명쾌한… 더보기 »

Myung
Myung
2020년 8월 26일 8:27 오전
게시글평점 :
     

캬.. 감사합니다!

neokimy2k
neokimy2k
2020년 8월 26일 10:15 오전
게시글평점 :
     

꼭 필요한 내용 덕분에 잘 배웠습니다. 고맙습니다.

ki72****
ki72****
2020년 9월 1일 11:16 오전
게시글평점 :
     

재미있게 따라가고 있습니다.

사랑채
사랑채
2020년 10월 18일 8:05 오후
게시글평점 :
     

안녕하세요?
엑셀365 추천 차트에 한국이 나오지 않는데, 어떻게 하나요?

5
0
여러분의 생각을 댓글로 남겨주세요.x