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

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

Конечно, это подходит не только для товаров и рентабельности. На ходу можно придумать множество сценариев, где такой тип диаграммы будет «в теме», например:

• Изменение дохода (X) и продолжительности жизни (Y) для разных стран за последние два года.
• Изменение количества клиентов (X) и среднего чека (Y) ресторанных заказов
  
• Соотношение стоимости компании (X) и количества сотрудников в ней (Y)
  
• ...
  

Если что-то подобное встречается в вашей практике, то имеет смысл разобраться, как построить такую ​​красоту.

Я уже писал о пузырьковых диаграммах (даже анимированных). Точечная диаграмма (Точечная диаграмма XY) – это частный случай пузырька (Bubble Chart), но без третьего параметра – размера пузырьков. Те. каждая точка на графике описывается всего двумя параметрами: X и Y. Таким образом, построение начинается с подготовки исходных данных в виде двух таблиц:

Давайте сначала построим то, что «было». Для этого выделите диапазон A3:C8 и на вкладке выберите Вставить (Вставить) Command Рекомендуемые графики (Рекомендуемые графики), а затем перейдите на вкладку Все диаграммы (Все графики) и выберите тип Точка (Точечная диаграмма XY):

После нажатия на OK мы получаем заготовку нашей диаграммы.

Теперь добавим в него данные из второй таблицы «Стало». Самый простой способ сделать это — скопировать. Для этого выделите диапазон E3:F8, скопируйте его и, выделив график, выполните в него специальную вставку с помощью Главная — Паста — Специальная паста (Главная страница — Вставка — Специальная вставка):

В появившемся окне выберите соответствующие параметры вставки:

После нажатия на ОК на нашей диаграмме появится второй набор точек («стали»):

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

Обратите внимание, как оно настроено:

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

Выделим созданную таблицу, скопируем ее в буфер обмена и добавим на нашу диаграмму с помощью Специальной вставки, как мы это делали ранее:

После нажатия на ОК на схеме появятся новые начальная и конечная точки для каждой стрелки (у меня они серого цвета), закрывающие уже построенные синие и оранжевые. Щелкните правой кнопкой мыши по ним и выберите команду Изменение типа диаграммы для серии (Изменить тип диаграммы серий). В открывшемся окне для исходных строк «до» и «до» оставьте тип Точка, а для серии «стрелок» задаём Точка с прямыми линиями:

После нажатия на ОК наши точки «было» и «стало» соединятся прямыми линиями. Остается только щелкнуть по ним правой кнопкой мыши и выбрать команду Формат ряда данных (Формат серии данных), а затем задайте параметры линий: толщину, тип стрелок и их размеры:

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

1. Нажмите на любую точку и выберите в контекстном меню Добавьте метки данных (Добавить метки данных) – будут добавлены числовые метки точек
   
2. Щелкните правой кнопкой мыши по меткам и выберите команду. Формат подписи (Формат меток)
3. В открывшейся панели поставьте галочку Значения из ячеек (Значения из ячеек), нажми на кнопку Выбрать диапазон и выделите названия продуктов (A4:A8).