Перейти к основному содержанию

Создать стрелочную диаграмму отклонений в Excel

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

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


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

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

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

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

=MAX(B2:C2)

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

=D2

3. Продолжайте вводить приведенную ниже формулу в ячейку F2 и скопируйте формулу в ячейку F10, см. Снимок экрана:

=IF(C2>B2,C2-B2,NA())

4. Затем в ячейке G2 введите приведенную ниже формулу и перетащите ее в ячейку G10, чтобы заполнить формулу, см. Снимок экрана:

=IF(C2<B2,B2-C2,NA())

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

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

=IF(ISERROR(F2),"","+" & TEXT(F2/B2,"0%"))

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

=IF(ISERROR(G2),"","-"&TEXT(G2/B2,"0%"))

Советы: Вспомогательные столбцы H и I используются для добавления меток данных вверх и вниз для диаграммы.
Во-вторых, создайте стрелочную диаграмму отклонения на основе данных вспомогательных столбцов.

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

8. И вставлена ​​кластерная столбчатая диаграмма, как показано на скриншоте ниже:

9. Затем щелкните, чтобы выбрать диаграмму, а затем выберите Выберите данные из контекстного меню см. снимок экрана:

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

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

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

13. Затем щелкните правой кнопкой мыши Макс. Столбцы ошибок слева по оси Y, и выберите Полосы ошибок формата из контекстного меню см. снимок экрана:

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

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

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

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

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

17. Проделайте ту же операцию, чтобы скрыть панели данных Max Right, см. Снимок экрана:

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

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

19. Держать Полосы ошибок формата панель все еще открывается, затем щелкните, чтобы выбрать Максимальные полосы ошибок Y справа, В Полосы ошибок формата панель, под Заливка и линия Вкладка, выполните следующие действия:

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

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

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

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

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

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

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

25. В открытом Форматирование меток данных панель, под Параметры метки вкладка, проверка Значение из ячеек вариант, а в выскочившем Диапазон меток данных В диалоговом окне выберите ячейки данных в столбце Up Data Label, затем щелкните OK кнопку, см. снимок экрана:

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

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

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

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

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


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

Если вы испытываете головную боль при выполнении вышеупомянутых сложных шагов, здесь я представлю простой инструмент -Kutools for Excel, С его Стрелочная диаграмма разницы вы можете создать линейчатую или столбцовую стрелочную диаграмму разницы, как вам нужно, всего за несколько щелчков мышью.  Нажмите, чтобы загрузить Kutools for Excel для бесплатной пробной версии!


Загрузите образец файла диаграммы отклонений или разностей стрелок


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


Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...

Описание


Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Excellent Staff
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations