Умные схемы Excel

Если вы не любите читать статьи, а предпочитаете разбираться сами, то вот ссылки:

Последняя версия
Версия 3.0 от 18-06-2015:

Download “Умная Карта России 3.2” colormap_05-10-2015_Russia.zip – Downloaded 1985 times – 326 KB

Старые версии (архив, найденные ошибки не исправляются)
Версия 2.1 — ручное создание легенды, удобная карта регионов России (ред. от 15-05-2014)
Версия 2.0 — ручное создание легенды
Версия 1.0

Все версии с картой регионов РФ, для Excel 2007 и выше, с открытым программным кодом с комментариями.

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

Рис. 1

А если нужно сделать десятки похожих карт?

Можно воспользоваться специальными картографическими программными продуктами, например Microsoft MapPoint или MapInfo Professional , способными визуализировать данные на карте (и решить многие другие задачи).
Рис. 2 MapInfo
Но мы поступим просто и удобно – встроим макрос, раскрашивающий карты или схемы в книгу Excel с нашими числами, и получим возможность двумя кликами мышки получать симпатичную картинку.

Базовая программа / макрос умеет делать вот что

  • Выводить столбец с названиями фигур, полигонов (на примере каждый регион представлен одним или несколькими полигонами). Вы можете удалить с карты РФ не интересующие Вас области или создать свою карту. Очистите устаревшую таблицу «Данные», и макрос создаст актуальный список элементов.
  • Автоматически разбивать набор числовых данных на указанное количество ступеней (линейно), присваивать соответствующие цвета каждой ступеньке, рисовать легенду для карты.
  • Обновлять цвета карты в соответствии с выбранным набором значений. Макрос может оперировать набором значений от одного и более. Не забывайте указывать название набора данных в первой строке листа ДАННЫЕ, например, «Январь 11».
  • Текущая версия может работать только в Excel 2007, т.к. использует новые свойства объекта Shape.

Порядок работы с картой

  • Определитесь, каким образом вы будете работать с картами. Если Ваши результаты подвержены изменениям, удобнее скопировать листы СХЕМА и ДАННЫЕ в ваш проект Excel и связать формулами числовые значения для карты с листами проекта. Вы сможете быстро получить свежие иллюстрации, если в расчетах сделаны уточнения. Сохраняйте проект в книге с поддержкой макросов, чтобы сохранить возможность обновлять схемы автоматически.
  • Или используйте книгу отдельно, как самостоятельный проект.
  • Отредактируйте карту. Создавайте фигуры в Excel с говорящими названиями для удобства работы с таблицей. Этот макрос выводит в список только полигоны (autoshapetype = 138). По коду 138 найдите в макросе листа «Схема» строку, прерывающую функцию. Удалите строку или отредактируйте, если вам нужно раскрашивать фигуры, отличные от типа полигон (№138). Удобно, если дополнительные элементы оформления карты не будут загромождать список.
  • Очистите устаревшие значения таблицы на листе Данные. Нажмите кнопку Обновить. Будет создан список названий фигур, которые вы собираетесь окрашивать в соответствии с числовыми данными.
  • Перенесите значения, характеризующие, например, один временной период, в один столбец, начиная с четвертого, в соответствующие строки. Не забывайте про необходимость названия столбца данных в первой строке листа Данные.
  • На листе СХЕМА выберите базовый цвет для палитры. Он будет примерно в середине расчетного градиента. В случае ошибки обработки Вашего выбора, автоматически устанавливается синий (тема №4).
  • Укажите количество ступеней или цветовых оттенков. В данном макросе выбор ограничен – от 2 до 7. Использовать больше ступеней вы можете после изменения кода и м.б. дизайна листа.
  • Выберите набор данных и кликните кнопку Обновить. Дождитесь завершения работы программы.

Про работу с макросами

В книге Excel 2007 с поддержкой макросов (.xlsm) может храниться и выполнятся программный код на языке VBA.
Чтобы не подвергаться лишнему риску открыть документ с опасными программами, в настройках Excel отключите макросы с уведомлением:
Рис. 3
Получив уведомление, запустите макрос визуализации:
Рис. 4

Умные схемы Excel: 12 комментариев

  1. Артем

    Случайно наткнулся на Ваш блог, а конкретно на программу Визуализация наборов числовых значений на карте или любом другом рисунке из фигур Excel Версия 1.0. В связи с чем у меня к Вам вопрос, а нет ли случайно у Вас 🙂 более продвинутой версии данной программы, в которой ступени цвета распределяются не от 0 (нуля). Чтобы можно было самостоятельно задавать как например «параметры оси» при построении графика в Excel ну т.е. минимальное, максимальное значение и шаг. А также чтобы можно было бы делать распределение в процентах, сейчас конечно тоже это можно, но в легенде отображаются доли, а не проценты (т.е. легенда воспринимала бы формат числа.
    Ну а если бы на карту можно было бы наносить кружочками города и размер кружка также бы изменялся в зависимости от показателя было бы вообще супер.

  2. Андрей Автор записи

    Новой версии пока нет.
    Полезные интересные функции, спасибо. Попробую реализовать.

  3. clam Автор записи

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

  4. Владимир

    Приветствую. Отличный файл!
    В версиях 2.0 и 2.1 не удается применить значения второго столбца столба с данными (в примере — 2014), всегда возвращает на первый столбец с данными (в примере — 2013). Что нужно поправить в макросе?

  5. clam Автор записи

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

  6. Владимир

    Спасибо за исправление. Отличный файл!

  7. Греф Артем

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

  8. clam Автор записи

    Добрый день!
    Спасибо за проявленный интерес. Добавить очень просто. Действия, касающиеся работы макроса, на примере Крыма:
    1. Копируете Крым с карты Украины на карту России.
    2. Установите название объекта формы Крыма (левый верхний угол, там где адреса ячеек таблицы, после редактирования обязательно нажмите Enter).
    3. Обновляете карту — кнопка «Обновить».
    4. На листе «Данные» в конце списка должно появиться название нового региона — значит все ок.
    5. Вводите числовые значения для визуализации и пользуетесь картой.
    Оформление настраивайте на свой вкус.

  9. Александр

    Добрый день, возможна ли работа данной карты в Excel версии 2003?

  10. Юлия

    Добрый день, скажите, есть возможность добавить карту пермского края вместо карты России??

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

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Лимит времени истёк. Пожалуйста, перезагрузите CAPTCHA.