Точечная диаграмма со стрелками "было-стало"

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

Точечная диаграмма со стрелками "Было-Стало"

Само-собой, это подходит не только для товаров и затрат-прибыли. С ходу можно придумать много сценариев, где этот тип диаграммы будет "в тему", например:

  • Изменение уровня доходов (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), а затем настроить параметры линий: толщину, тип стрелки и их размеры:

Настраиваем вид стрелок

Для наглядности ещё неплохо бы добавить названия товаров. Для этого:

  1. Щелкните по любой точке и выберите в контекстном меню Добавить подписи данных (Add Data Labels) - добавятся числовые подписи точек
  2. Щелкните по подписям правой и выберите команду Формат подписей (Format Labels)
  3. В открывшейся панели включите флажок Значения из ячеек (Values from cells), нажмите кнопку Выбрать диапазон и выделите названия товаров (A4:A8).


Наверх