Создание стрелочной диаграммы вариации в 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. В открывшемся диалоговом окне Выбор источника данных в списке Диапазон имени ряда выберите ряд Максимальный левый и нажмите кнопку стрелки вверх, чтобы переместить этот ряд данных вверх, как показано в демонстрации ниже:

11. Затем нажмите кнопку ОК, чтобы закрыть диалоговое окно, и щелкните правой кнопкой мыши по ряду данных Максимальный левый, нажмите кнопку Элементы диаграммы, чтобы развернуть список, в списке отметьте Линии ошибок, как показано на скриншоте:

12. Продолжайте щелкать правой кнопкой мыши по ряду данных Максимальный правый, а затем нажмите кнопку Элементы диаграммы, чтобы развернуть список, в списке отметьте Линии ошибок, как показано на скриншоте:

13. Затем щелкните правой кнопкой мыши по линиям ошибок Y Максимального левого и выберите Формат линий ошибок из контекстного меню, как показано на скриншоте:

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

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

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

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

16. Затем вам нужно сделать невидимыми столбцы данных Максимальный левый и Максимальный правый, щелкните правой кнопкой мыши по столбцу данных Максимальный левый и выберите Формат ряда данных, в открывшейся панели Формат ряда данных на вкладке Заливка и границы выберите Без заливки в разделе Заливка, как показано на скриншоте:

17. Выполните ту же операцию для скрытия столбцов данных Максимальный правый, как показано на скриншоте:

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

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

19. Оставьте панель Формат линий ошибок открытой, затем щелкните по линиям ошибок Y Максимального правого, в панели Формат линий ошибок на вкладке Заливка и границы выполните следующие действия:

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

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

21. Далее щелкните правой кнопкой мыши по столбцу данных Максимальный левый и выберите Формат ряда данных из контекстного меню, в открывшейся панели Формат ряда данных на вкладке Параметры ряда выберите опцию Вспомогательная ось в разделе Построить ряд на, затем щелкните по столбцу данных Максимальный правый и выполните ту же операцию, как показано в демонстрации ниже:

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

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

23. На этом шаге вы можете добавить метки данных для диаграммы, щелкните по ряду данных Максимальный левый, а затем нажмите Элементы диаграммы, в расширенном списке Элементы диаграммы отметьте опцию Метки данных, как показано на скриншоте:

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

25. В открывшейся панели Формат меток данных на вкладке Параметры метки отметьте опцию Значение из ячеек, и в открывшемся диалоговом окне Диапазон меток данных выберите ячейки данных из столбца Метка данных вверх, затем нажмите ОК, как показано на скриншоте:

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

27. Затем продолжайте добавлять метки данных вниз, щелкните правой кнопкой мыши по столбцу данных Максимальный правый и нажмите Элементы диаграммы, чтобы развернуть список, в списке Элементы диаграммы отметьте опцию Метки данных, как показано на скриншоте:

28. Затем щелкните правой кнопкой мыши по новым добавленным меткам данных и выберите Формат меток данных, в открывшейся панели Формат меток данных на вкладке Параметры метки отметьте опцию Значение из ячеек, и в открывшемся диалоговом окне Диапазон меток данных выберите ячейки данных из столбца Метка данных вниз, затем нажмите ОК, как показано на скриншоте:

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

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

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

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

Видео: Создание стрелочной диаграммы вариации или различий в Excel