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

or

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

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

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


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

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

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

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

=A2&REPT(" ",10)
Советы: Эта формула используется для увеличения длины метки и перемещения текста влево. В противном случае метка будет расположена под центром видимых и невидимых полос столбцов.

2. Продолжайте вводить приведенную ниже формулу в ячейку D2, а затем перетащите и скопируйте формулу в ячейку D10, см. Снимок экрана:

=B2
Советы: Эта формула ссылается на порядковые номера в столбце B.

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

=B3
Советы: Эта формула используется для ссылки на заказы следующего года. Он будет использоваться для невидимых полос, которые являются вершиной или основанием полос ошибок.

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

=B3-B2
Советы: Эта формула рассчитает отклонение или разницу между текущим годом и следующим годом.

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

=IF(F2>0,-F2,"")
Советы: Эта формула используется для возврата значения дисперсии, если оно положительное, и будут отображаться некоторые отрицательные числа, которые используются для того, чтобы полосы ошибок начинались с верхней части невидимой полосы и опускались вниз.

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

=IF(F2<0,F2,"")
Советы: Эта формула используется для возврата значения дисперсии, если оно отрицательное, и будут отображаться некоторые отрицательные числа, которые используются для того, чтобы полосы ошибок начинались с нижней части полосы ошибок и поднимались вверх.

7. Теперь вставьте последний вспомогательный столбец, примените приведенную ниже формулу в ячейку I2 и перетащите ее в ячейку I9, а затем отформатируйте десятичные результаты в процентном стиле. см. снимок экрана:

=F2/B2
Советы: Эта формула вычисляет процентную разницу между предыдущим годом и следующим годом. Он будет использоваться как метки в невидимом столбце под полосами ошибок.

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

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

9. А затем была вставлена ​​столбчатая диаграмма, вы можете удалить ненужные элементы диаграммы, такие как заголовок диаграммы, легенда или линии сетки, см. Снимки экрана:

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

11. В открытом Полосы ошибок формата панель, под Параметры панели ошибок Вкладка:

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

12, Затем нажмите OK кнопку, и вы получите диаграмму, как показано на скриншоте ниже:

13. А теперь щелкните правой кнопкой мыши столбец столбца, который отображает данные заказа 1, и выберите Форматировать ряд данных из контекстного меню см. снимок экрана:

14. В открытом Форматировать ряд данных панель, под Варианты серий вкладку, измените значения в Перекрытие серий и Ширина зазора разделы к 0%, см. снимок экрана:

15. Теперь вы должны скрыть невидимую панель данных, щелкните правой кнопкой мыши любую из них, в появившемся контекстном меню выберите Без заливки из Заполнять раздел, см. снимок экрана:

16. Не снимая выделения с невидимой панели данных, щелкните Элементы диаграммы кнопку, выберите Этикетки данных > Дополнительные параметры, см. снимок экрана:

17. В Форматирование меток данных панель, под Параметры метки вкладка, проверка Значение из ячеек, а в выскочившем Диапазон метки данных выберите диапазон данных дисперсии I2: I9, см. снимки экрана:

18. Затем нажмите OK, все еще в Форматирование меток данных панель:

  • Снимите флажок с Значение и Показать линии выноски вариантов под Параметры метки;
  • Затем укажите положение метки как Внешний конец из Положение ярлыка.

19). Теперь вы можете видеть, что метки данных были добавлены в диаграмму, вы можете установить отрицательные процентные метки на Внутренний конеци отформатируйте метки данных по своему усмотрению, см. снимок экрана:


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

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

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

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

1. Чтобы вставить вспомогательные данные первой части, примените следующие формулы:

C2: =REPT(A2,1)        (drag the formula to cell C10)
D2
: = B2 (перетащите формулу в ячейку D10)
E2: = B3-B2 (перетащите формулу в ячейку E9)
F2: = E2 / B2 (перетащите формулу в ячейку F9)

2. Затем примените следующие формулы для создания вспомогательных данных второй части:

G2: Enter 1.5, 2.5, 3.5, 4.5…plus one to the previous cell.          (fill the data to from G2 to G9)
H2
: = IF (B3> = B2, B3, NA ()) (перетащите формулу в ячейку H9)
I2: = ЕСЛИ (B3

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

3. Выберите данные в столбце C и столбце D, затем щелкните Вставить > Вставить столбец или гистограмму > Кластерный столбец чтобы вставить столбчатую диаграмму, как показано на скриншоте ниже:

4, Затем нажмите Ctrl + C чтобы скопировать данные в столбец G, столбец H, столбец I, а затем щелкните, чтобы выбрать диаграмму, см. снимок экрана:

5. После выбора диаграммы нажмите Главная > макаронные изделия > Специальная вставка, В Специальная вставка диалоговое окно, выберите Новая серия, Параметры колонки, а затем проверьте Название серии в первой строке и Категории (метки X) в первом столбце варианты см. скриншоты:

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

7. Щелкните правой кнопкой мыши любую полосу столбца на диаграмме и выберите Изменить тип диаграммы серииe из контекстного меню, см. снимок экрана:

8. В Изменить тип диаграммы диалоговое окно, измените оба Увеличение и Уменьшить в Точечная диаграмма, затем снимите флажок Вторичная ось коробка для каждого из Выберите тип диаграммы и ось для вашей серии список. Смотрите скриншот:

9. А затем нажмите OK , вы получите комбинированную диаграмму, в которой маркеры расположены между соответствующими столбцами. Смотрите скриншот:

10. Затем нажмите, чтобы выбрать серию увеличения (оранжевая точка), а затем нажмите Элементы диаграммы кнопку, проверьте Полосы ошибок из окна списка, а на диаграмму добавлены полосы ошибок, см. снимок экрана:

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

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

  • Выберите Оба из файла Руководство;
  • Выберите Нет крышки из Конец Стиль;
  • Из Сумма ошибки раздел, выберите На заказ, А затем нажмите Укажите значение кнопка, в выдвинутой Настраиваемые полосы ошибок диалоговое окно в Положительное значение ошибки введите, введите = {0}И в Отрицательное значение ошибки В поле выберите значение дисперсии E2: E9.
  • Затем нажмите OK кнопку.

13. Теперь все еще в Полосы ошибок формата панели, нажмите Заливка и линия tab выполните следующие операции:

  • Выберите Сплошная линия в линия раздел и выберите нужный вам цвет, затем укажите нужную ширину линии;
  • Из Начать тип стрелки раскрывающийся список, выберите один Arrow тип.

14. На этом этапе вы должны скрыть маркеры (оранжевые точки), выбрать оранжевые точки и щелкнуть правой кнопкой мыши, выбрать Форматировать ряд данных из контекстного меню в открывшемся Форматировать ряд данных панель, под Заливка и линия вкладку нажмите Маркер раздел, затем выберите все из Параметры маркера, см. снимок экрана:

15. Повторите шаг 10-14 выше, чтобы вставить стрелку вниз для серии данных уменьшения и скрыть серые маркеры, и вы получите диаграмму, как показано на скриншоте ниже:

16. После вставки стрелок вы должны добавить метки данных, нажмите, чтобы выбрать скрытые серии увеличения, а затем нажмите Элементы диаграммы > Этикетки данных > Выше, см. снимок экрана:

17. Затем щелкните правой кнопкой мыши любую метку данных и выберите Форматирование меток данных из контекстного меню в развернутом Форматирование меток данных панель, под Параметры метки вкладка, проверка Значение из ячеек вариант, то в выскочившем Диапазон метки данных диалоговом окне выберите ячейки процента отклонения (F2: F9), см. снимок экрана:

18. Нажмите OK чтобы закрыть диалоговое окно, все еще в Форматирование меток данных панель, снимите флажок Значение Y и Показать линии выноски варианты, см. снимок экрана:

19. Затем вам просто нужно повторить шаг 16-18 выше, чтобы добавить метки с отрицательным процентным соотношением (на этот раз вы должны добавить метки под точками данных уменьшения, выберите Ниже в подменю Этикетки данных in Элементы диаграмм), и столбчатая диаграмма с процентным изменением была успешно создана, см. снимок экрана:


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

Для большинства из нас вышеуказанные методы слишком сложны в использовании, но, если у вас есть Kutools for Excel, он предоставляет различные специальные типы диаграмм, которых нет в Excel, например Bullet Chart, Целевая и фактическая диаграмма, График промокания и так далее. С его простым инструментом- Столбчатая диаграмма с измененным процентом, вы можете быстро и легко создать столбчатую диаграмму с процентным изменением, используя стрелки вверх и вниз в Excel. Нажмите, чтобы загрузить Kutools for 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.