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

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

Круговая диаграмма с горизонтальной линией	Гистограмма с Вертикальная линия

• Создайте Круговая диаграмма со средней или целевой линией по горизонтали
• Добавьте целевую линию с помощью Разное значение на Круговая диаграмма
• Создайте Круговая диаграмма со средней линией динамически с использованием Переключатель
• Создайте Гистограмма со средней или целевой линией по вертикали
• Добавьте целевую линию с помощью Разное значение на Гистограмма
• Добавьте целевую / среднюю линию на гистограмму или Гистограмма с помощью удобной функции
• Скачать образец файла «Диаграмма со средней / целевой линией»

Создание Круговая диаграмма со средней или целевой линией по горизонтали

Чтобы создать Круговая диаграмма со средней или целевой линией по горизонтали, выполните следующие действия:

1. Чтобы создать круговую диаграмму со средней линией, сначала рассчитайте среднее значение набора данных. Введите приведённую ниже формулу в пустую ячейку, а затем перетащите маркер автозаполнения, чтобы скопировать её в другие ячейки (см. снимок экрана):

2. Затем выделите диапазон данных, включая значения среднего, и нажмите Вставка > Вставка гистограммы или гистограмма > Гистограмма с группировкой (см. снимок экрана):

3. На лист будет вставлена круговая диаграмма; при необходимости вы можете удалить ненужные элементы, например заголовок диаграммы и линии сетки (см. снимки экрана):

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

5. В диалоговом окне Изменение Тип диаграммы в списке Выберите тип диаграммы и ось для ряда данных выберите тип График из раскрывающегося списка Тип диаграммы для ряда данных Среднее (см. снимок экрана):

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

7. Теперь добавьте метку данных для этой средней линии: дважды щёлкните по самой правой точке данных на линии (см. снимок экрана):

8. Затем нажмите кнопку Элементы диаграммы, установите флажок Метки данных — и метка данных появится на средней линии (см. снимок экрана):

Добавление целевой линии с помощью Разное значение на Круговая диаграмма

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

Чтобы создать такой тип диаграммы, выполните следующие действия:

1. Сначала выделите диапазон данных и нажмите Вставка > Вставка гистограммы или гистограмма > Гистограмма с группировкой, чтобы вставить диаграмму.

2. Затем щелкните правой кнопкой мыши по ряду данных целевых значений (оранжевые столбцы) и в контекстном меню выберите пункт Изменить тип ряда диаграммы. В диалоговом окне Изменение типа диаграммы выберите тип График для ряда данных целевых значений из раскрывающегося списка Тип диаграммы (см. снимок экрана):

3. Нажмите кнопку ОК, затем дважды щёлкните целевую линию и выберите пункт Формат ряда данных (см. снимок экрана):

4. В открывшейся области Формат ряда данных на вкладке Заливка и границы в разделе Линия выберите параметр Линия «Нет линии» (см. снимок экрана):

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

6. Затем задайте желаемый цвет для целевых линий в разделе Заливка (см. снимок экрана):

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

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

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

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

Сначала вставьте несколько Переключатель

1. Нажмите Разработчик > Вставка > Переключатель (элемент управления формы), затем нарисуйте четыре переключателя и отредактируйте подписи по своему усмотрению (см. снимки экрана):

2. Затем щелкните правой кнопкой мыши по первому переключателю и выберите в контекстном меню пункт Формат объекта (см. снимок экрана):

3. В открывшемся диалоговом окне Format Object на вкладке Control выберите ячейку для связывания с переключателем в поле Cell и нажмите OK, чтобы закрыть его. См. снимок экрана:

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

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

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

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

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

Затем выделите созданные данные и примените указанный выше метод шаги 2–8 из метода 1, чтобы построить круговую диаграмму со средней линией — она будет обновляться динамически. См. демонстрацию ниже:

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

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

1. Сначала создайте вспомогательную таблицу данных: введите X и Y в две ячейки, а затем рассчитайте средние значения в ячейках D2 и D3. См. снимок экрана:

2. Затем введите числа 0 и 1 в ячейки E2 и E3 для значения Y. См. снимок экрана:

3. Затем выделите исходный диапазон данных и нажмите Insert > Insert Column or Гистограмма > Clustered Bar. См. снимок экрана:

4. На лист вставлена гистограмма. Щёлкните правой кнопкой мыши по столбцу и выберите в контекстном меню пункт Выбрать данные. См. снимок экрана:

5. Во всплывающем диалоговом окне Выбрать данные Source нажмите кнопку Add. См. снимок экрана:

6. Затем в диалоговом окне Edit Series введите имя серии в поле Имя серии, а затем выделите ячейки со значениями X (в данном примере — D2:D3) в поле Значения ряда. См. снимок экрана:

7. Нажмите OK > OK, чтобы закрыть диалоговые окна. Новый ряд данных теперь добавлен на вашу гистограмму (два оранжевых столбца). Щёлкните правой кнопкой мыши по оранжевому столбцу и выберите пункт Change Series Тип диаграммы. См. снимок экрана:

8. Во всплывающем диалоговом окне Change Тип диаграммы в раскрывающемся списке Scatter with Smooth Lines выберите для ряда данных «Среднее» тип диаграммы Тип диаграммы. См. снимок экрана: Average.

9. Затем нажмите OK, чтобы закрыть диалоговое окно. Новый ряд данных преобразуется в точку данных вдоль основной оси Y. Щёлкните правой кнопкой мыши по столбцу и выберите в контекстном меню пункт Выбрать данные. См. снимок экрана:

10. В диалоговом окне Выбрать данные Source в списке «Диапазон имени ряда» выберите параметр Average и нажмите кнопку Edit. См. снимок экрана:

11. Затем появится диалоговое окно Edit Series. В поле Значение серии по оси X выделите две ячейки X (D2:D3), а в поле Значение серии по оси Y — две ячейки Y (E2:E3). См. снимок экрана:

12. Нажмите OK > OK, чтобы закрыть диалоговые окна. Вы увидите, что к диаграмме добавлена вертикальная линия среднего значения. См. снимок экрана:

13. Затем щелкните правой кнопкой мыши по вторичной вертикальной оси (правой оси) и в контекстном меню выберите пункт Format Axis. См. снимок экрана:

14. В открывшейся области Format Axis на вкладке Axis Options, в разделе Axis Options, введите значение 1,0 в поле «Максимум» Maximum и получите диаграмму, как показано на снимке экрана ниже:

15. Наконец, при необходимости удалите вторичную вертикальную ось и лишние линии сетки. Готово! Теперь диаграмма «Гистограмма с вертикальной средней линией» полностью готова. См. снимок экрана:

Добавление целевой линии с помощью Разное значение на диаграмме Гистограмма

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

1. Выделите диапазон данных и нажмите Insert > Insert Column or Гистограмма > Clustered Bar, чтобы вставить гистограмму. См. снимок экрана:

2. Затем щёлкните правой кнопкой мыши по ряду данных цели (оранжевые столбцы) и в контекстном меню выберите пункт Format Data Series. См. снимок экрана:

3. В открывшейся области Format Data Series на вкладке Series Options измените перекрытие рядов на 100 %. См. снимок экрана:

4. Продолжая работу в области Format Data Series, перейдите на вкладку Fill & Line и выберите вариант No fill в разделе Fill. См. снимок экрана:

5. Далее перейдите на вкладку Effects, в разделе Shadow выполните следующие настройки:

• Укажите нужный вам цвет из раскрывающегося списка Цвет;
• Установите размытие на 0 пт;
• Затем установите угол на 0°;
• Наконец, измените расстояние на 2 пт по вашему усмотрению.

6. Теперь снова перейдите на вкладку Series Options и выберите опцию Secondary Axis в разделе Series Options, а также при необходимости измените ширину зазора на 90 %. См. снимок экрана:

7. Наконец, удалите ненужные элементы — такие как заголовок диаграммы, верхнюю ось X и линии сетки. После этого вы получите гистограмму с различными целевыми линиями, как показано на скриншоте ниже:

Добавьте целевую / среднюю линию к гистограмме или Гистограмма с помощью удобной функции

Скачать образец файла «Диаграмма со средней / целевой линией»