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

Некоторые моменты по сравнению с тепловой картой мира были оптимизированы, поэтому есть смысл просмотреть статью даже тем, кто читал предыдущую.

Подготовка

Карта

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

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

Регионы

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

Начало

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

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

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

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

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

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

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

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

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

Ввод данных

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

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

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

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

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

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

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

Пример:

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

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

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

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

Для красного спектра: =ОКРУГЛ(255-red)/11;0)
Для зеленого спектра: =ОКРУГЛ(255-green)/11;0)
Для синего спектра: =ОКРУГЛ(255-blue)/11;0)

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

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

Пример:

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

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

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

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

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

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

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

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

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

Исходные данные

=ПОИСКПОЗ(E2;mins)

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

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

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

Легенда

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

Легенда

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

Sub SetLegend()

    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 As Integer
    Dim countryName As String
    Dim colorInd, cRed, cGreen, cBlue As Integer

    Dim n As Integer
    n = Range("count").Value

    For i = 1 To n
        countryName = Range("table").Cells(i, 1).Value
        colorInd = Range("table").Cells(i, 3).Value
        cRed = Range("color").Cells(colorInd, 2).Value
        cGreen = Range("color").Cells(colorInd, 3).Value
        cBlue = Range("color").Cells(colorInd, 4).Value
        ThisWorkbook.Worksheets("Map").Shapes("ru" & countryName).Fill.ForeColor.RGB = RGB(cRed, cGreen, cBlue)
    Next i

    Application.ScreenUpdating = True
End Sub

Завершение

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

Готовая тепловая карта регионов России включает все 83 региона.

Республики

  • Республика Адыгея (Адыгея)
  • Республика Башкортостан
  • Республика Бурятия
  • Республика Алтай
  • Республика Дагестан
  • Республика Ингушетия
  • Кабардино-Балкарская Республика
  • Республика Калмыкия
  • Карачаево-Черкесская Республика
  • Республика Карелия
  • Республика Коми
  • Республика Марий Эл
  • Республика Мордовия
  • Республика Саха (Якутия)
  • Республика Северная Осетия — Алания
  • Республика Татарстан (Татарстан)
  • Республика Тыва
  • Удмуртская Республика
  • Республика Хакасия
  • Чеченская Республика
  • Чувашская Республика — Чувашия

Округа

  • Ненецкий автономный округ
  • Ханты-Мансийский автономный округ — Югра
  • Чукотский автономный округ
  • Ямало-Ненецкий автономный округ

Области

  • Амурская область
  • Архангельская область
  • Астраханская область
  • Белгородская область
  • Брянская область
  • Владимирская область
  • Волгоградская область
  • Вологодская область
  • Воронежская область
  • Ивановская область
  • Иркутская область
  • Калининградская область
  • Калужская область
  • Камчатский край
  • Кемеровская область
  • Кировская область
  • Костромская область
  • Курганская область
  • Курская область
  • Ленинградская область
  • Липецкая область
  • Магаданская область
  • Московская область
  • Мурманская область
  • Нижегородская область
  • Новгородская область
  • Новосибирская область
  • Омская область
  • Оренбургская область
  • Орловская область
  • Пензенская область
  • Пермский край
  • Псковская область
  • Ростовская область
  • Рязанская область
  • Самарская область
  • Саратовская область
  • Сахалинская область
  • Свердловская область
  • Смоленская область
  • Тамбовская область
  • Тверская область
  • Томская область
  • Тульская область
  • Тюменская область
  • Ульяновская область
  • Челябинская область
  • Забайкальский край
  • Ярославская область
  • Еврейская автономная область

Края

  • Алтайский край
  • Краснодарский край
  • Красноярский край
  • Приморский край
  • Ставропольский край
  • Хабаровский край

Города

  • Город Москва
  • Санкт-Петербург

показать/скрыть

Тепловая карта России

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

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


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

Вверх