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

Создание Диаграмма процентного изменения с помощью мощной функции

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