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

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

Столбчатая диаграмма с горизонтальной линией	Гистограмма с вертикальной линией

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

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

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

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

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

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

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

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

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

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

8. Затем нажмите Элементы диаграммы, отметьте флажок Метки данных, и метка данных будет добавлена для средней линии, как показано на скриншоте:

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

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

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

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

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

3. Затем нажмите кнопку ОК, дважды щелкните по целевой линии и выберите Формат ряда данных, как показано на скриншоте:

4. В открывшейся панели Формат ряда данных, на вкладке Заливка и линия, выберите Без линии из раздела Линия, как показано на скриншоте:

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

6. Затем установите цвет для целевых линий по вашему выбору из раздела Заливка, как показано на скриншоте:

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

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

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

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

Сначала вставьте несколько радиокнопок

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

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

3. В открывшемся диалоговом окне Формат объекта, на вкладке Управление, выберите ячейку для связи с радиокнопкой из текстового поля Связь с ячейкой и нажмите ОК, чтобы закрыть его, как показано на скриншоте:

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

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

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

6. Затем вы можете рассчитать средние значения, используя приведенную ниже формулу, как показано на скриншоте:

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

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

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

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

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

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

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

4. Гистограмма будет вставлена в лист, затем щелкните правой кнопкой мыши по гистограмме и выберите Выбрать данные из контекстного меню, как показано на скриншоте:

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

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

7. Нажмите ОК > ОК, чтобы закрыть диалоговые окна, и новый ряд данных теперь добавлен в вашу гистограмму (два оранжевых столбика), затем щелкните правой кнопкой мыши по оранжевому столбику и выберите Изменить тип диаграммы для ряда, как показано на скриншоте:

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

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

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

11. Затем появится окно Редактирование ряда, в поле Значения X ряда выберите две ячейки X (D2:D3), а в поле Значения Y ряда выберите две ячейки Y (E2:E3), как показано на скриншоте:

12. Нажмите ОК > ОК, чтобы закрыть диалоговые окна, и вы увидите, что вертикальная средняя линия была добавлена на диаграмму, как показано на скриншоте:

13. Затем щелкните правой кнопкой мыши вторичную вертикальную ось (правую ось) и выберите Формат оси из контекстного меню, как показано на скриншоте:

14. В открывшейся панели Формат оси, на вкладке Параметры оси, в разделе Параметры оси введите 1.0 в поле Максимальное значение, и вы получите диаграмму, как показано на скриншоте ниже:

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

Добавление целевой линии с разными значениями в гистограмму

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

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

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

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

4. Все еще находясь в панели Формат ряда данных, переключитесь на вкладку Заливка и линия, выберите Без заливки из раздела Заливка, как показано на скриншоте:

5. Далее перейдите на вкладку Эффекты, в разделе Тень выполните следующие операции:

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

6. Теперь снова переключитесь на вкладку Параметры ряда, выберите Вторичная ось из раздела Параметры ряда и измените ширину зазора на 90% по мере необходимости. Смотрите скриншот:

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

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

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