Как сделать Map Chart для управления логистикой в Excel

Map Chart – это главный объект на дашборде для презентации показателей управления логистикой с помощью визуализации данных. Существует много решений для построения Map Chart в Excel. Рассмотрим еще один необычный пример с интересными возможностями презентации логистических маршрутов на карте USA.

Как нарисовать фигуру карты по контуру картинки в Excel

Нарисовать фигуру в форме карты USA для Excel – не сложно. Просто выберите инструмент: Insert → Illustrations → Shapes → Freedom Shape:

Даже если не получиться при первой попытке красиво очертить контур карты, можно редактировать ключевые точки фигуры, чтобы исправить недостатки. Правой кнопкой кликните по фигуре и из появившегося контекстного меню выберите опцию – Edit Points:

Отредактируйте точки в нужным местах соответственно вашим требованиям.

Так мы будем создавать основу для Map Chart. Но далее нам придется еще создать несколько фигур, чтобы его усовершенствовать. Сделать его презентабельным с интерактивными возможностями для анализа логистических маршрутов между городами США.

Сценарий для проектирования визуализации данных в Excel

В первую очередь определим техническое задание для разработчика визуализации данных в Excel. Для этого изначально моделируем ситуацию с примером практического использования презентации. В одной Нью-Йоркской компании имеется свой логистический отдел с небольшим автопарком и штатом сотрудников. Всего 3 грузовика и 3 водителя. У двоих водителей имеется по два маршрута. А у третьего только один. Пути маршрутов лежат через 8 городов в разные стороны США:

1. Нью-Йорк – штат Нью-Йорк.
2. Чикаго – штат Иллинойс.
3. Нэшвилл – штат Теннесси.
4. Даллас – штат Техас.
5. Денвер – штат Колорадо.
6. Лос-Анджелес – штат Калифорния.
7. Сан-Франциско – штат Калифорния.
8. Сиэтл – Вашингтон.

У каждого водителя, грузовика и маршрута свои определенные характеристики, которые следует учитывать для визуального анализа эффективности логистических цепочек поставок. Поэтому дашборд должен уметь сегментировать данные по этим 3-м ключевым параметрам, чтобы правильно оценить текущую ситуацию:

1. Данные о водителях.
2. Технические характеристики грузовиков.
3. Свойства и особенности маршрутов.

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

Описание технического задания для разработки Map Chart в Excel

В центре дашборда должен находиться интерактивный Map Chart на котором будут хорошо видны логистические маршруты, по которым курсируют грузовики водителей. При выборе маршрута или водителя, закрепленного за данным маршрутом, на карте должна показываться соответственная кривая линия, пересекающая точки ключевых городов. Согласно карте путей выбранного маршрута.

Для комфортного проведения визуального анализа должна присутствовать возможность изменять цветами кривые лини маршрутов прямо на карте.

Весь функционал интерактивности должен быть реализован без использования макросов так как внутренней политикой безопасности компании запрещено использовать макросы во всех типах файлов пакета MS Office.

Как создать Map Chart в Excel шаг за шагом

Для решения такого технического задания без макросов нам потребуется пересобрать карту США из нескольких фигур так, чтобы границы их соединений проходили там, где проходят пути логистических маршрутов. И сделать небольшой отступ между ними:

На заднем плане разместим горизонтальный Bar Chart. Он будет выполнять функцию слайдера при необходимости изменить цвет маршрута.

Интерактивная подсветка цветом логистических путей на карте

Для управления цветами слайдера создадим таблицу из трех столбцов. По значениям последнего столбца строим линейный Bar Chart. Выделяем диапазон C2:C7 и выбираем инструмент – Insert → Charts → 2-D Bar → Clustered Bar:

В первых двух столбцах – исходные значения, а в последнем третьем столбце формулы алгоритма управления:

Нам необходимо настроить наш слайдер цветов. Выполним целый ряд последовательных шагов настроек объекта визуализации в Excel:

1. В первую очередь необходимо поменять местами значения осей XY. Для этого выбираем Bar Chart и используем инструмент – Chart Design → Data → Select Data. Затем пожалуйста нажмите Switch Row/Column:

2. На следующем шаге изменяем внешний вид максимально расширяя ширину бара в двух параметрах Series Options – Series Overlap = 100% и Gap Width = 0%.

3. Теперь мы должны скрыть Bar Chart перекрыв его новой фигурой на первом плане так, чтобы виден был цвет, только в границах карты США. Но не за ее пределами. Для этого нам понадобится программа MS PowerPoint, которая также включена в базовый пакет программ MS Office. Там мы вы в фигуре прямоугольника, в центре, вырезаем отверстие в форме нашей карты США используя нашу фигуру. Открываем программу MS PowerPoint. Там рисуем фигуру прямоугольник и добавляем нашу фигуру Freedom Shape «карту США». Накладываем сверху прямоугольника, выделяем обе фигуры и выбираем инструмент – Insert → Insert Shapes → Merge Shapes → Fragment. Этого инструмента нет в Excel, приходиться использовать MS PowerPoint:

4. Собираем все объекты визуализации данных в один Map Chart для дальнейшей разработки дашборда в Excel:

Уже понятен главный принцип функционирования интерактивной визуализации без макросов на карте.

Формулы алгоритма интерактивного построения маршрутов

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

Для этого применим хитрый прием в Excel с помощью наложения текстовых символов как отдельные фигуры текста, которые могут исчезать в зависимости от значений в ячейках на, которые они имеют ссылки. Это позволит нам устранять лишние кривые линии путей, которые не относятся к карте выбранного маршрута. Фигуры текста будут появляться или исчезать по алгоритму в зависимости от выбранного маршрута. Поэтому нам понадобятся два дополнительных столбца в таблице для управления отображения или скрытия текстовых символов на карте. В последнем столбце формула для вывода текстового символа «I» в зависимости от логических значений ячеек в предыдущем столбце:

Функция CHAR(32) выводит символ пробела – то есть нет видимости символа «I» если значение в предыдущей ячейке TRUE.

Создаем текстовую фигуру выбрав инструмент – Insert → Text → Text Box. Не снимаем выделения с фигуры Text Box вводим в строку формул ссылку на первую ячейку последнего столбца с формулами «F2». Изменяем размер шрифта на 150 пунктов и размещаем символ на карте:

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

Теперь при выборе иного маршрута будут высвечиваться другие линии по алгоритму.

Презентация управления логистикой на Map Chart в Excel

По такому принципу был построен дашборд с Map Chart, который можно скачать бесплатно в конце статьи:

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

Скачать Map Chart для управления логистикой в Excel

Это продолжение статьи:

Анализ логистики и управления цепочками поставок в Excel

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