Тепловая карта мираТема данной статьи будет интересна многим – кому-то по опыту использования VBA, кому-то – со стороны конечного пользователя. Попробуем построить тепловую карту мира, т.е. инструмент для графического представления числовых данных, привязанных к странам на карте мира.

Использование тепловой карты мира поможет с одного взгляда оценить проведенное исследование в мировом масштабе. Примерами тому могут быть:

  • исследование объема валового внутреннего продукта
  • исследование объема экспорта/импорта продукции
  • исследование уровня инфляции
  • исследование показателей промышленности
  • исследование показателей уровня жизни и занятости
  • исследование показателей федеральных бюджетов
  • исследование количества пунктов обслуживания клиентов

и любые другие исследования.

Подготовка

Карта

Одно из условий – карта должна качественно смотреться и распечатываться в любом разрешении, на носителях любого размера, для чего используем векторное изображение. Таким образом, можно масштабировать его без потери качества.

Векторное изображение обладает еще рядом важным для нас качеств: его можно перекрасить в любой цвет и каждому отдельному элементу карты можно присвоить индивидуальное имя.

Наиболее подходящим форматом векторного изображения, которое можно импортировать в Excel является формат Enhanced Metafile (EMF). Непродолжительный поиск в Интернете – и карта у нас.

Страны

Для идентификации стран мира на карте мы будем использовать Общероссийский классификатор стран мира (ОКСМ), включающий данные о буквенном и цифровом коде стран и их наименование.

Начало

Требования и схема работы

Для начала определимся с требованиями к карте:

  • Имена стран должны соответствовать коду классификатора;
  • К карте необходимо иметь легенду;
  • Цвета карты должны быть настраиваемыми;
  • Необходимо иметь отдельный лист, в котором будет производиться ввод данных для каждой страны.

Общая логика работы будет следующей:

  • Разметить карту таким образом, чтобы каждая графическая фигура страны имела имя, соответствующее коду страны согласно классификатору;
  • Разработать систему окрашивания фигур стран в соответствии с введенными данными;
  • Ввести настроечный лист с параметрами цвета карты;
  • Ввести лист для заполнения конечным пользователем.

Подготовка карты

Наиболее трудоемким процессом будет разметка карты – присвоение каждой фигуре страны имени в соответствии с классификатором стран. Это необходимо для того, чтобы по этому имени мы легко находили и окрашивали нужную фигуру из будущего кода VBA.

В помощь – только карта с указанием стран на ней, сам классификатор – для уточнения кода.

Условимся, что карту мы поместим на лист «Maps».

Ввод данных

Вынесем в отдельный лист таблицу стран мира, которые мы отметили на карте, их параметры (коды ISO) и оставим один столбец для заполнения.

Условимся, что данные мы поместим на лист «Данные».

Создание кода

Градации данных

Упрощенно технический смысл тепловой карты – более темным цветом окрасить страны с бОльшим значением. Поэтому нам нужно разбить значения на листе «Данные» по градациям, каждой из которых будет соответствовать свой оттенок цвета.

Создадим таблицу градаций. Условимся, что мы используем 10 градаций и, соответственно, 10 оттенков цвета для их отражения на карте. Таблица будет состоять из 2-х столбцов (верхней и нижней планке градации) и 11 строк (10 градаций + одна для пустых значений).

Минимальное (min) и максимальное (max) значения равны минимуму / максимуму от выборки (функции МИН, МАКС). Шаг градации (diff) – разнице между максимумом и минимумом, отнесенной к количеству градаций (10).

Пример:

Тепловая карта мира - Градации значений

Для использования в формулах имен, необходимо присвоить ячейке имя либо задать имени определенную формулу.

Цветовые градации

Каждой градации данных соответствует один оттенок цвета, но вводить вручную 10 оттенков – не эстетично, поэтому возьмем за базу самый темный цвет и просчитаем остальные оттенки, просто прибавив к его составляющим (красного, зеленого и синего) одно и то же значение (сделаем его также настраиваемым параметром – grad).

Пример:

Тепловая карта мира - Градации цвета

При работе с цветами в Excel необходимо иметь ввиду, что Excel использует внутренний набор цветов и когда мы делаем заливку ячейки, то, как бы заливаем ее не цветом, а номером цвета, которому соответствует уже привычное сочетание RGB. Заливка фигур возможна только из набора цветов, поэтом что нам нужно сделать перед окраской стран – присвоить определенным (произвольным) номерам цветов расчетные цвета градации.

Введем в таблицу указание на номер цвета Excel (столбец A):

Тепловая карта мира - Градации цвета

Столбец A – номер цвета Excel;

Столбец B-D – цвет градации;

Столбец E-F – диапазон градации;

Диапазону E1:E10 присвоим имя mins - для более удобного использования в формулах, о которых в следующем разделе.

Ячейке, находящейся в начале таблицы градаций (на примере – A1) присвоим имя color.

Дополнение исходных данных

Для того чтобы понять, какой цвет применить к той или иной стране, необходимо значение, соответствующее стране, сопоставить с номером цветовой градации.

Тепловая карта мира - Градации цвета

=ЕСЛИ(E2=0;11;11-ПОИСКПОЗ(E2;mins))

Данной формулой мы устанавливаем значению номер градации по таблице градаций. Если значение пустое или рано нулю – устанавливается последняя, 11-ая, градация, в остальных случаях мы пытаемся найти ближайшее к исходному значению значение градации (за счет опущенного 3-го параметра функции ПОИСКПОЗ, Excel находит наибольшее значение, которое меньше или равно значению E2) и используем этот номер.

Диапазону с кодами ISO (на примере – B2:B…) присвоим имя iso.

Диапазону с номерами градации (на примере – F2:F…) присвоим имя group.

Функции подсчета количества значений в диапазоне iso присваиваем имя count – значение будет использовано при подсчете количества стран.

Легенда

Теперь построим легенду для карты. Выделим под картой в горизонтальной плоскости 10 ячеек и присвоим диапазону имя legend. В ячейках слева и справа от легенды введем формулу, которая сошлется на максимальное и минимальное значения.

Тепловая карта мира - Легенда

Для формирования легенды используем следующую процедуру.

SubSetLegend()

    Application.ScreenUpdating = False ' Запрещаем обновление окна до завершения процедуры

    Dim i, j

    j = 1 ' Номер градации

    For i = 20 To 29 ' Номер цвета Excel
        ThisWorkbook.Range("legend").Cells(1, j).Interior.ColorIndex = i
        j = j + 1
    Next i

    Application.ScreenUpdating = True

End Sub

Раскраска карты

Создадим процедуру обновления карты:

Sub MapIt()
    Application.ScreenUpdating = False ' Запрещаем обновление окна до завершения процедуры

    Dim i
    Dim countryName As String
    Dim colorInd, cRed, cGreen, cBlue As Integer
    Dim n As Integer

    n = ThisWorkbook.Range("count").Value ' Общее количество стран

    For i = 1 To n ' Для каждой страны из списка
        ' Понимаем, что это за страна (ее код ISO)
        countryName = ThisWorkbook.Range("iso").Cells(i, 1).Value

        ' Понимаем, к какой градации относится значение данной страны
        colorInd = ThisWorkbook.Range("group").Cells(i, 1).Value

        ' Понимаем, какое значение красного спектра соответствует нужному оттенку градации
        cRed = ThisWorkbook.Range("color").Cells(colorInd, 2).Value

        ' Понимаем, какое значение зеленого спектра соответствует нужному оттенку градации
        cGreen = ThisWorkbook.Range("color").Cells(colorInd, 3).Value

        ' Понимаем, какое значение синего спектра соответствует нужному оттенку градации
        cBlue = ThisWorkbook.Range("color").Cells(colorInd, 4).Value

        ' Устанавливаем цвет страны в соответствии с градацией
        ThisWorkbook.Sheets("Map").Shapes(countryName).Fill.ForeColor.RGB = RGB(cRed, cGreen, cBlue)
    Next i

    Application.ScreenUpdating = True

End Sub

Завершение

В завершение можно облагородить созданную карту – добавить инструмент для настройки базового цвета, справочник цветов, скрыть те столбцы, которые видеть конечному пользователю необязательно (например, градацию), добавить кнопку обновления карты или обновлять ее при переходе на лист.

Готовая тепловая карта мира включает 161 страну показать/скрыть.

  • Австралия
  • Австрия
  • Азербайджан
  • Албания
  • Алжир
  • Ангола
  • Антарктида
  • Аргентина
  • Армения
  • Афганистан
  • Бангладеш
  • Беларусь
  • Белиз
  • Бельгия
  • Бенин
  • Болгария
  • Боливия
  • Босния и Герцеговина
  • Ботсвана
  • Бразилия
  • Буркина-Фасо
  • Бурунди
  • Бутан
  • Венгрия
  • Венесуэла
  • Виргинские Острова, Британские
  • Вьетнам
  • Габон
  • Гаити
  • Гайана
  • Гана
  • Гватемала
  • Гвинея
  • Гвинея-Бисау
  • Германия
  • Гондурас
  • Гренландия
  • Греция
  • Грузия
  • Дания
  • Джибути
  • Доминиканская Республика
  • Египет
  • Замбия
  • Западная Сахара
  • Зимбабве
  • Израиль
  • Индия
  • Индонезия
  • Иордания
  • Ирак
  • Иран, Исламская Республика
  • Ирландия
  • Исландия
  • Испания
  • Италия
  • Йемен
  • Казахстан
  • Камбоджа
  • Камерун
  • Канада
  • Кипр
  • Киргизия
  • Китай
  • Колумбия
  • Конго
  • Конго, Демократическая Республика
  • Корея, Народно-Демократическая Республика
  • Корея, Республика
  • Коста-Рика
  • Кот Д'Ивуар
  • Куба
  • Кувейт
  • Лаосская Народно-Демократическая Республика
  • Латвия
  • Лесото
  • Либерия
  • Ливан
  • Ливийская Арабская Джамахирия
  • Литва
  • Люксембург
  • Мавритания
  • Мадагаскар
  • Малави
  • Малайзия
  • Мали
  • Марокко
  • Мексика
  • Мозамбик
  • Молдова, Республика
  • Монголия
  • Мьянма
  • Намибия
  • Непал
  • Нигер
  • Нигерия
  • Нидерланды
  • Никарагуа
  • Новая Зеландия
  • Новая Каледония
  • Норвегия
  • Объединенные Арабские Эмираты
  • Оман
  • Пакистан
  • Панама
  • Папуа-Новая Гвинея
  • Парагвай
  • Перу
  • Польша
  • Португалия
  • Республика Македония
  • Россия
  • Руанда
  • Румыния
  • Саудовская Аравия
  • Свазиленд
  • Сенегал
  • Сербия
  • Сирийская Арабская Республика
  • Словакия
  • Словения
  • Соединенное Королевство
  • Соединенные Штаты
  • Соломоновы Острова
  • Сомали
  • Судан
  • Суринам
  • Сьерра-Леоне
  • Таджикистан
  • Таиланд
  • Танзания, Объединенная Республика
  • Того
  • Тунис
  • Туркмения
  • Турция
  • Уганда
  • Узбекистан
  • Украина
  • Уругвай
  • Фиджи
  • Филиппины
  • Финляндия
  • Фолклендские Острова (Мальвинские)
  • Франция
  • Французская Гвиана
  • Хорватия
  • Центрально-Африканская Республика
  • Чад
  • Чешская Республика
  • Чили
  • Швейцария
  • Швеция
  • Эквадор
  • Экваториальная Гвинея
  • Эль-Сальвадор
  • Эритрея
  • Эстония
  • Эфиопия
  • Южная Африка
  • Ямайка
  • Япония

Приобрести Тепловую карту мира вы можете в Магазине

Добавить комментарий


Защитный код
Обновить

Вверх