Точечная диаграмма со стрелками "было-стало"
На тренинге по визуализации недавно один из слушателей озвучил интересную задачу: необходимо наглядно показать изменения затрат и прибыли по определенным товарам в динамике за последние два года. Конечно, можно не напрягаться и пойти по привычному пути, рисуя банальные графики, столбики или даже, прости господи, "тортики". Но если чуть поднапрячься, то неплохим решением в такой ситуации может быть использование особого типа точечной диаграммы со стрелками ("было-стало"):
Само-собой, это подходит не только для товаров и затрат-прибыли. С ходу можно придумать много сценариев, где этот тип диаграммы будет "в тему", например:
- Изменение уровня доходов (X) и продолжительности жизни (Y) для разных стран за последние два года.
- Изменение количества клиентов (X) и среднего чека (Y) заказа ресторанов
- Соотношение стоимости компании (X) и количества сотрудников в ней (Y)
- ...
Если что-то похожее встречается и в вашей практике, то имеет смысл разобраться, как такую красоту построить.
Про пузырьковые диаграммы (даже анимированные) я уже неоднократно писал. Точечная диаграмма (XY Scatter Chart) - это частный случай пузырьковой (Bubble Chart), но без третьего параметра - размера пузырьков. Т.е. каждая точка на графике описывается всего двумя параметрами: X и Y. Таким образом, построение начинается с подготовки исходных данных в виде двух таблиц:
Сначала построим то, что "было". Для этого выделим диапазон A3:C8 и выберем на вкладке Вставка (Insert) команду Рекомендуемые диаграммы (Recommended Charts), а затем перейдем на вкладку Все диаграммы (All charts) и выберем тип Точечная (XY Scatter Chart):
После нажатия на ОК получим заготовку нашей диаграммы.
Теперь добавим на неё данные из второй таблицы "Стало". Проще всего это сделать копированием. Для этого выделим диапазон E3:F8, скопируем его и, выделив диаграмму, выполним в неё специальную вставку через Главная - Вставить - Специальная вставка (Home - Paste - Paste Special):
В появившемся окне выберем соответствующие опции вставки:
После нажатия на ОК на нашей диаграмме появится второй комплект точек ("стало"):
Теперь самое интересное. Для имитации стрелок нужно будет из данных первой и второй таблиц подготовить третью таблицу вот такого вида:
Обратите внимание как она устроена:
- строки из исходных таблиц попарно чередуются, фиксируя начало и конец каждой стрелки
- каждая пара отделена от других пустой строкой, чтобы на выходе получились отдельные стрелки, а не одна большая
- если данные могут изменяться в будущем, то имеет смысл использовать не числа, а ссылки на исходные таблицы, т.е. в ячейке H4 ввести формулу =B4, в ячейку H5 ввести формулу =E4 и т.д.
Выделим созданную таблицу, скопируем ее в буфер и добавим к нашей диаграмме при помощи специальной вставки, как это уже делали ранее:
После нажатия на ОК на диаграмме появятся новые точки начала и конца каждой стрелки (у меня они серого цвета), закрывающие уже построенные синие и оранжевые. Щелкнем по ним правой кнопкой мыши и выберем команду Изменить тип диаграммы для ряда (Change Series Chart Type). В открывшемся окне для исходных рядов "было" и "стало" оставим тип Точечная, а для ряда "стрелок" зададим Точечная с прямыми отрезками:
После нажатия на ОК наши точки "было" и "стало" будут соединены прямыми отрезками. Останется щелкнуть по ним правой кнопкой мыши и выбрать команду Формат ряда данных (Format Data Series), а затем настроить параметры линий: толщину, тип стрелки и их размеры:
Для наглядности ещё неплохо бы добавить названия товаров. Для этого:
- Щелкните по любой точке и выберите в контекстном меню Добавить подписи данных (Add Data Labels) - добавятся числовые подписи точек
- Щелкните по подписям правой и выберите команду Формат подписей (Format Labels)
- В открывшейся панели включите флажок Значения из ячеек (Values from cells), нажмите кнопку Выбрать диапазон и выделите названия товаров (A4:A8).
Вот и всё - пользуйтесь :)
Понятно и наглядно! Пригодилось для решения прикладной задачи!