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

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

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

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

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

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

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

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

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

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

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

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

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

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

Второй этап: Создание диаграммы на основе данных вспомогательных столбцов

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

9. И затем будет вставлен столбчатый график, вы можете удалить ненужные элементы графика, такие как заголовок графика, легенда или линии сетки, см. скриншоты:

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

11. В открывшейся панели Формат планок погрешностей, на вкладке Параметры планок погрешностей:

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

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

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

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

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

16. При выбранной невидимой полосе данных нажмите кнопку Элементы графика, выберите Метки данных > Дополнительные параметры, см. скриншот:

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

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

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

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

Создание гистограммы с процентным изменением с использованием стрелок вверх и вниз

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

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

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

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

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

Второй этап: Создание диаграммы на основе данных вспомогательных столбцов

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

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

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

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

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

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

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

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

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

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

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

13. Теперь, всё ещё находясь в панели Формат планок погрешностей, нажмите вкладку Заливка и линия, выполните следующие действия:

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

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

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

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

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

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

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

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

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