Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

Создать стрелочную диаграмму отклонений в 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 for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее ...) и экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.