Создание диаграммы с разностными стрелками в Excel

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

Например, чтобы наглядно показать изменение продаж по сравнению с предыдущим годом, можно использовать зелёные и красные стрелки для отображения процентного изменения — как на снимке экрана ниже. В этой статье я расскажу, как создать такой продвинутый тип диаграммы в Excel.

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

Создание диаграммы с разностными стрелками в Excel

Чтобы создать диаграмму с разностными стрелками для сравнения процентного изменения за 2020 год относительно года 2019, сначала необходимо добавить вспомогательные столбцы для расчёта исходных данных, как показано на снимке экрана ниже:

Сначала создайте данные во вспомогательных столбцах

1. Введите следующую формулу в ячейку D2 рядом с исходными данными, а затем скопируйте её до ячейки D10 (см. снимок экрана):

2. Введите следующую формулу в ячейку E2, а затем скопируйте её до ячейки E10 (см. снимок экрана):

3. Далее введите приведённую ниже формулу в ячейку F2 и скопируйте её до ячейки F10 (см. снимок экрана):

4. Затем введите приведённую ниже формулу в ячейку G2 и протяните её до ячейки G10 (см. снимок экрана):

5. Затем введите следующую формулу в ячейку H2 и протяните её до ячейки H10 (см. снимок экрана):

6. Затем введите приведённую ниже формулу в ячейку I2 и протяните маркер заполнения до ячейки I10 (см. снимок экрана):

Во-вторых, создайте диаграмму с разностными стрелками на основе данных во вспомогательных столбцах

7. После создания вспомогательных данных выделите диапазон в столбцах A, B, C, D и E, затем нажмите Вставка > Вставить столбец или гистограмму > Групповая гистограмма (см. снимок экрана):

8. На лист будет вставлена диаграмма с группированными столбцами, как показано на снимке экрана ниже:

9. Затем щёлкните правой кнопкой мыши по диаграмме и выберите в контекстном меню пункт Выбрать данные (см. снимок экрана):

10. В появившемся диалоговом окне Источник данных — Выбрать данные в списке Диапазон имени ряда выберите ряд данных Max Left и нажмите кнопку со стрелкой вверх, чтобы переместить этот ряд данных в начало списка (см. пример ниже):

11. Затем нажмите кнопку OK, чтобы закрыть диалоговое окно. После этого щёлкните правой кнопкой мыши по ряду данных Max Left и выберите Элементы диаграммы, чтобы развернуть список. Установите флажок Планки погрешностей (см. снимок экрана):

12. Далее щёлкните правой кнопкой мыши по ряду данных Max Right и нажмите Элементы диаграммы, чтобы развернуть список. Установите флажок Планки погрешностей (см. снимок экрана):

13. Затем щёлкните правой кнопкой мыши по планкам погрешностей Max Left Y и выберите Формат планок погрешностей в контекстном меню (см. снимок экрана):

14. Затем в открывшейся области Формат планок погрешностей на вкладке Параметры планок погрешностей в разделе Вертикальные планки погрешностей выполните следующие действия:

• Выберите Минусв разделе Направление;
• Затем выберите Без окончанияв разделе Стиль окончания;
• Затем в разделе Величина погрешности выберите Пользовательский и нажмите кнопку Указать значение. В появившемся диалоговом окне Пользовательские планки погрешностей оставьте поле Положительное значение погрешности пустым, а в поле Отрицательное значение погрешности укажите данные из столбца «Стрелка вверх», после чего нажмите кнопку OK, чтобы закрыть диалоговое окно.

15. Не закрывая область Формат планок погрешностей, щёлкните по планкам погрешностей Max Right Y на диаграмме. На вкладке Параметры планок погрешностей в разделе Вертикальные планки погрешностей выполните следующие действия:

• Выберите Минусв разделе Направление;
• Затем выберите Без окончанияв разделе Стиль окончания;
• Затем в разделе Величина погрешности выберите Пользовательский и нажмите кнопку Указать значение. В появившемся диалоговом окне Пользовательские планки погрешностей оставьте поле Положительное значение погрешности пустым, а в поле Отрицательное значение погрешности укажите данные из столбца «Стрелка вниз», после чего нажмите кнопку OK, чтобы закрыть диалоговое окно.

16. Теперь сделайте невидимыми столбцы рядов данных Max Left и Max Right. Щёлкните правой кнопкой мыши по столбцу Max Left и выберите Формат ряда данных. В открывшейся области Формат ряда данных на вкладке Заливка и границы выберите Нет заливки в разделе Заливка (см. снимок экрана):

17. Повторите те же действия, чтобы скрыть столбцы Max Right (см. снимок экрана):

18. После скрытия столбцов данных «Max Left» и «Max Right» щелкните правой кнопкой мыши по столбцам погрешностей по оси Y «Max Left», затем выберите в контекстном меню пункт Формат столбцов погрешностей. В области Формат столбцов погрешностей на вкладке Заливка и линии выполните следующие действия:

• Выберите Сплошная линияи задайте понравившийся цвет для стрелок вверх в раскрывающемся списке Цвет;
• Затем установите ширину линии 1,25 птпо необходимости в разделе Ширина;
• Затем выберите значок Стрелка в раскрывающемся списке типа Начало стрелки.

19. Не закрывая область Формат столбцов погрешностей, щелкните, чтобы выбрать столбцы погрешностей по оси Y «Max Right». В области Формат столбцов погрешностей на вкладке Заливка и линии выполните следующие действия:

• Выберите Сплошная линияи задайте другой понравившийся цвет для стрелок вниз в раскрывающемся списке Цвет;
• Затем установите ширину линии 1,25 птпо необходимости в разделе Ширина;
• Затем выберите значок Стрелка в раскрывающемся списке типа Конец стрелки.

20. Теперь диаграмма будет выглядеть так, как показано на скриншоте ниже:

21. Далее щелкните правой кнопкой мыши по столбцу данных «Max Left» и в контекстном меню выберите пункт Формат ряда данных. В открывшейся области Формат ряда данных перейдите на вкладку Параметры ряда, найдите раздел Вторичная ось и выберите опцию Построение ряда по. Затем щелкните, чтобы выбрать столбец данных «Max Right», и повторите те же действия, что показаны на демонстрации ниже:

22. Затем щелкните правой кнопкой мыши по любому столбцу на диаграмме и выберите пункт Формат ряда данных. В области Формат ряда данных на вкладке Параметры ряда введите значения 0 % и 100 % соответственно в поля Перекрытие рядов и Ширина зазора.

Затем в области Формат ряда данных в выпадающем списке Параметры ряда выберите пункт Ряд «Год 2020» или Ряд «Год 2019» и введите значения 0 % и 100 % соответственно в поля Перекрытие рядов и Ширина зазора. См. демонстрацию ниже:

23. На этом шаге можно добавить к диаграмме метки данных. Щёлкните, чтобы выбрать ряд данных «Max Left», затем нажмите кнопку Элементы диаграммы. В развернутом списке Элементы диаграммы установите флажок напротив пункта Метки данных. См. снимок экрана:

24. Затем щелкните правой кнопкой мыши по метке Метки данных и выберите пункт Формат Метки данных. См. снимок экрана:

25. В открывшейся области Формат метки данных на вкладке Параметры подписей установите флажок напротив пункта Значения из ячеек. В появившемся диалоговом окне Диапазон метки данных выберите ячейки из столбца «Up Data Label», затем нажмите кнопку ОК. См. снимок экрана:

26. В области Формат метки данных снимите флажки напротив пунктов Значение и Показывать направляющие линии. В результате вы получите диаграмму, показанную ниже. См. снимок экрана:

27. Далее добавьте нижние метки данных. Щелкните правой кнопкой мыши по столбцу данных «Max Right» и выберите Элементы диаграммы, чтобы развернуть список. В списке Элементы диаграммы установите флажок напротив пункта Метки данных. См. снимок экрана:

28. Затем щелкните правой кнопкой мыши по новой добавленной метке данных и выберите пункт Формат метки данных. В открывшейся области Формат метки данных на вкладке Параметры подписей установите флажок напротив пункта Значения из ячеек. В появившемся диалоговом окне Диапазон меток данных выберите ячейки из столбца «Down Data Label», затем нажмите кнопку ОК. См. снимок экрана:

29. В разделе Формат подписей данных снимите флажки напротив пунктов Значение и Показывать направляющие линии. После этого на диаграмму будут добавлены метки процентного изменения. См. снимок экрана:

30. Наконец, удалите ненужные элементы диаграммы по своему усмотрению — например, линии сетки, заголовки диаграммы и вторичную ось. В итоге вы получите готовую диаграмму, как показано на снимке экрана ниже:

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

Скачать образец файла диаграммы с разностными стрелками

Видео: создание диаграммы с разностными стрелками в Excel