Создать диаграмму со средней / целевой линией в Excel
В Excel обычной задачей может быть вставка горизонтальной или вертикальной линии в диаграмму, горизонтальная или вертикальная линия может ссылаться на некоторое целевое или среднее значение. Этот тип линии позволяет легко сравнивать фактические значения с целевым или средним значением, и с помощью этой линии вы можете легко увидеть, где значения находятся выше и ниже этого эталонного значения, как показано на скриншотах ниже. В этой статье я расскажу о том, как создать столбчатую или гистограмму со средней или целевой линией в Excel.
| Столбчатая диаграмма с горизонтальной линией | Гистограмма с вертикальной линией |
 |
 |
- Создать столбчатую диаграмму со средней или целевой линией по горизонтали
- Добавьте целевую линию с разными значениями в столбчатую диаграмму
- Динамическое создание столбчатой диаграммы со средней линией с помощью переключателей
- Создание гистограммы со средней или целевой линией по вертикали
- Добавить целевую линию с разными значениями на гистограмму
- Добавьте целевую / среднюю линию в столбчатую или линейчатую диаграмму с помощью удобной функции
- Скачать образец файла диаграммы со средней / целевой линией
Создать столбчатую диаграмму со средней или целевой линией по горизонтали
Чтобы создать столбчатую диаграмму со средней или целевой линией по горизонтали, выполните следующие действия:
1. Если вы хотите создать столбчатую диаграмму со средней линией, сначала вы должны вычислить среднее значение списка данных, введите приведенную ниже формулу в пустую ячейку, а затем перетащите дескриптор автозаполнения, чтобы скопировать формулу в другой ячейки, см. снимок экрана:
2. Затем выберите диапазон данных, включая средние значения, и нажмите Вставить > Вставить столбец или гистограмму > Кластерный столбец, см. снимок экрана:
3. И столбчатая диаграмма вставлена в рабочий лист, вы можете удалить ненужные элементы, такие как заголовок диаграммы, линии сетки, как вам нужно, см. Скриншоты:
 |
 |
 |
4. Затем щелкните правой кнопкой мыши средний ряд данных (оранжевые полосы) и выберите Изменить тип диаграммы серии из контекстного меню см. снимок экрана:
5. В Изменить тип диаграммы диалоговое окно под Выберите тип диаграммы и ось для ряда данных список, выберите Линейный график из Тип диаграммы выпадающий список для Средняя ряд данных, см. снимок экрана:
6. А затем нажмите OK Кнопка, вы получите диаграмму, как показано на скриншоте ниже:
7. Затем вы можете добавить метку данных для этой средней линии, дважды щелкните, чтобы выбрать крайнюю правую точку данных линии, см. Снимок экрана:
8. А затем нажмите Элементы диаграммы, проверить Этикетки данных поле, и метка данных была вставлена для средней линии, см. снимок экрана:
Добавьте целевую линию с разными значениями в столбчатую диаграмму
Иногда вам может потребоваться сравнить фактические значения с целевыми значениями, которые различаются для каждой строки, в этом случае для более четкой визуализации целевых значений вам может помочь приведенный ниже тип диаграммы.
 |
|
 |
Чтобы создать этот тип диаграммы, сделайте следующее:
1. Сначала выберите диапазон данных, а затем нажмите Вставить > Вставить столбец или гистограмму > Кластерный столбец чтобы вставить диаграмму.
2. Затем щелкните правой кнопкой мыши целевой ряд данных (оранжевые полосы) и выберите Изменить тип диаграммы серии из контекстного меню в Изменить тип диаграммы диалоговое окно, выберите Линейный график для целевого ряда данных из Тип диаграммы выпадающий список, см. снимок экрана:
3, Затем нажмите OK кнопку, а затем дважды щелкните целевую линию и выберите Форматировать ряд данных, см. снимок экрана:
4. В открытом Форматировать ряд данных панель, под Заливка и линия вкладка, выберите Нет линии из линия раздел, см. снимок экрана:
5. Все еще в Заливка и линия вкладку, переключитесь на Маркер раздел, выберите Встроенный из Параметры маркера раздел, а затем выберите горизонтальную полосу в Тип раскрывающийся список, наконец, установите размер, соответствующий ширине столбцов в Размер box, см. снимок экрана:
6. А затем установите цвет для целевых линий, как вам нравится, из Заполнять раздел, см. снимок экрана:
7. Наконец, вы можете удалить ненужные элементы, такие как заголовок диаграммы, линии сетки по мере необходимости.
Динамическое создание столбчатой диаграммы со средней линией с помощью переключателей
Если у вас есть большая таблица с продажами продукта за несколько лет, теперь вы хотите показать среднюю линию за каждый год на диаграмме. Для решения этой задачи вы можете использовать диаграмму со средней линией, щелкнув переключатель, как показано ниже в демонстрации. При выборе соответствующего года график со средней линией будет отображаться динамически.
Чтобы создать эту динамическую диаграмму, сначала вы должны вставить несколько переключателей, а затем создать вспомогательную таблицу данных, на которой основана диаграмма, и, наконец, вставить ссылку диаграммы на переключатель. Пожалуйста, выполните следующие действия:
Сначала вставьте несколько переключателей
1. Нажмите Застройщик > Вставить > Кнопка выбора (элемент управления формой), а затем нарисуйте четыре переключателя и отредактируйте текст по своему усмотрению, см. скриншоты:
 |
|
 |
2. Затем щелкните правой кнопкой мыши первую радиокнопку и выберите Управление форматом из контекстного меню см. снимок экрана:
3. В открытом Форматировать объект диалоговое окно под Control вкладку, выберите ячейку, чтобы связать переключатель с Ячейка текстовое поле ссылки и щелкните OK чтобы закрыть его, см. снимок экрана:
Во-вторых, вставьте вспомогательную таблицу данных для создания диаграммы на основе
4. Скопируйте названия продуктов из исходной таблицы в новый диапазон, а затем примените приведенную ниже формулу в пустую ячейку рядом с новыми скопированными данными, а затем перетащите дескриптор заполнения, чтобы получить результат, см. Снимок экрана:
5. Теперь, когда вы нажимаете один переключатель, соответствующий год данных будет отображаться автоматически, как показано ниже:
6. А затем вы можете рассчитать средние значения, используя формулу ниже, см. Снимок экрана:
В-третьих, создайте диаграмму на основе новой таблицы вспомогательных данных.
Затем выберите новые созданные данные, а затем примените вышеуказанное шаг 2- шаг 8 метода 1 для создания столбчатой диаграммы со средней линией, и вы получите динамическую диаграмму со средней линией. См. Демонстрацию ниже:
Создание гистограммы со средней или целевой линией по вертикали
В этом разделе будет рассказано, как создать гистограмму со средним значением или целевым значением по вертикали, пожалуйста, сделайте следующее:
1. Сначала создайте таблицу вспомогательных данных, введите X и Y в две ячейки, а затем вычислите средние значения в ячейках D2 и D3, см. снимок экрана:
2. Затем введите номер 0 и 1 в ячейки E2 и E3 для значений Y, см. снимок экрана:
3. Затем выберите исходный диапазон данных и нажмите Вставить > Вставить столбец или гистограмму > Кластерный бар, см. снимок экрана:
4. Гистограмма была вставлена в лист, затем щелкните ее правой кнопкой мыши и выберите Выберите данные из контекстного меню см. снимок экрана:
5. В выскочившем Выберите источник данных диалога, нажмите Добавить кнопку, см. снимок экрана:
6. А потом в Редактировать серию диалоговом окне введите название серии в Название серии текстовое поле, а затем выберите ячейки со значениями X (D2: D3 в этом примере) в Значения серии текстовое поле, см. снимок экрана:
7. Нажмите OK > OK чтобы закрыть диалоговые окна, и новый ряд данных теперь добавлен к вашей гистограмме (две оранжевые полосы), а затем щелкните оранжевую полосу правой кнопкой мыши и выберите Изменить тип диаграммы серии, см. снимок экрана:
8. В выскочившем Изменить тип диаграммы диалоговое окно, выберите Скаттер с плавными линиями из Тип диаграммы выпадайте для Средняя ряд данных, см. снимок экрана:
9, Затем нажмите OK чтобы закрыть диалоговое окно, и новый ряд данных преобразуется в точку данных вдоль основной оси Y, затем щелкните полосу правой кнопкой мыши и выберите Выберите данные из контекстного меню см. снимок экрана:
10. В Выберите источник данных В диалоговом окне в списке «Записи легенды (серии)» щелкните значок Средняя , а затем нажмите Редактировать кнопку, см. снимок экрана:
11. Затем Редактировать серию всплывающее окно подсказки, в Значения серии X выберите две ячейки X (D2: D3), а в Значения серии Y выберите две ячейки Y (E2: E3), см. снимок экрана:
12. Нажмите OK > OK чтобы закрыть диалоговые окна, и вы увидите, что к диаграмме добавлена вертикальная средняя линия, см. снимок экрана:
13. Затем щелкните правой кнопкой мыши вторичную вертикальную ось (правая ось) и выберите Ось формата из контекстного меню см. снимок экрана:
14. В открытом Ось формата панель, под Параметры оси в Параметры оси раздел, тип 1.0 в максимальная связанная коробка, и вы получите диаграмму, как показано на скриншоте ниже:
15. Наконец, вы можете удалить вторичную вертикальную ось и ненужные линии сетки по мере необходимости. Теперь гистограмма с вертикальной средней линией готова. Смотрите скриншот:
Добавить целевую линию с разными значениями на гистограмму
Чтобы добавить целевую линию с разными значениями в гистограмму, как показано на скриншотах ниже, выполните следующие действия шаг за шагом:
 |
|
 |
1. Выберите диапазон данных и нажмите Вставить > Вставить столбец или гистограмму > Кластерный бар чтобы вставить гистограмму, см. снимок экрана:
2. Затем щелкните правой кнопкой мыши целевой ряд данных (оранжевые полосы) и выберите Форматировать ряд данных из контекстного меню см. снимок экрана:
3. В открытом Форматировать ряд данных панель, под Варианты серий вкладку, измените перекрытие серий на 100%, см. снимок экрана:
4. Все еще в Форматировать ряд данных панель, переключитесь на Заливка и линия вкладка, выберите Без заливки из Заполнять раздел, см. снимок экрана:
5, Далее перейдите к Эффекты вкладка под Shadow раздел, установите следующие операции:
- Укажите нужный вам цвет из Цвет падать;
- Установите размытие на 0 pt;
- Затем отрегулируйте угол до 0°;
- Наконец, измените расстояние на 2pt как вам нужно.
6. Теперь переключитесь на Варианты серий вкладку снова, выберите Вторичная ось из Варианты серий раздел и измените ширину зазора на 90% как вам нужно. Смотрите скриншот:
7. Наконец, удалите ненужные элементы, такие как плитка диаграммы, верхняя ось X, линии сетки по мере необходимости, вы получите гистограмму с разными целевыми линиями, как показано ниже:
Добавьте целевую / среднюю линию в столбчатую или линейчатую диаграмму с помощью удобной функции
Kutools for Excel предоставляет более 50 специальных типов диаграмм, которых нет в Excel, например Пулевая диаграмма, Целевая и фактическая диаграмма, Стрелочная диаграмма разницы и так далее. С его удобным инструментом диаграммы - Добавить линию на диаграмму, вы можете добавить целевую или среднюю линию в столбчатую или линейчатую диаграмму одним щелчком мыши в Excel. Нажмите, чтобы загрузить Kutools for Excel для бесплатной пробной версии!
Скачать образец файла диаграммы со средней / целевой линией
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
