Как добавить вертикальную линию или линию среднего значения на гистограмму в Excel?
При работе с гистограммой в Excel для визуального представления данных нередко возникает необходимость выделить среднее значение — это помогает зрителям наглядно оценить, как отдельные точки соотносятся со средним по совокупности. Добавив на гистограмму вертикальную линию, обозначающую среднее значение (как показано на скриншоте ниже), вы значительно повысите ясность и интерпретируемость диаграммы в презентациях, отчётах и при принятии решений. В этом руководстве подробно описаны несколько способов добавления вертикальной или средней линии на гистограмму в Excel — от встроенных инструментов работы с диаграммами до автоматизированных макросов VBA.
Добавить Вертикальная линия на Гистограмма
Решение с помощью кода VBA: добавление вертикальной/средней линии программным способом
Добавить Вертикальная линия на Гистограмма
Для начала убедитесь, что ваши данные подготовлены и готовы к визуализации в виде гистограммы.
1. Выделите свой диапазон данных, затем перейдите в меню Вставка > Гистограмма > Гистограмма. Это создаст базовую гистограмму на основе вашего набора данных.
2. Перейдите в неиспользуемую область листа, например в ячейку A10, и введите в неё «Y», а в соседнюю ячейку (B10) — «X». Такая структура заложит основу для построения линии среднего значения.
3. Введите 0 и 1 в две ячейки непосредственно под ячейкой Y (A11 и A12), чтобы создать вертикальный ряд данных, необходимый для построения линии среднего значения. Этот шаг критически важен для обеспечения правильной вертикальной ориентации линии на графике.
4. В ячейку под X (B11) введите формулу =AVERAGE($B$2:$B$9), где $B$2:$B$9 — ваш фактический диапазон данных. Нажмите Enter, затем протяните маркер заполнения вниз до ячейки B12, чтобы скопировать формулу. Так вы продублируете значение среднего для обеих точек, к которым будет привязана линия.
Совет: Внимательно проверьте ссылки на ячейки в формуле, особенно если ваши данные находятся не в столбце B или начинаются с других строк.
5. Щёлкните правой кнопкой мыши в любом месте гистограммы и выберите в контекстном меню пункт Выбрать данные. Этот шаг необходим для добавления на диаграмму данных средней линии.
6. В окне Исходные данные нажмите кнопку Добавить в разделе Диапазон имени ряда. Это позволит вставить новый ряд данных специально для вашей средней линии.
7. В открывшемся окне Изменение ряда задайте подходящее имя серии и укажите диапазон B11:B12 в качестве значений ряда (или скорректируйте его при необходимости, если расчёт среднего находится в другом месте).
8. Нажмите ОК дважды, чтобы закрыть окна и вернуться на лист. Теперь щёлкните по недавно добавленному ряду на гистограмме, вызовите контекстное меню и выберите команду Изменить тип диаграммы для ряда, чтобы обновить формат его отображения.
9. В окне Изменение типа диаграммы выберите тип X Y (точечная) > Точечная с прямыми отрезками. Нажмите ОК, чтобы применить изменения и закрыть окно.
Примечание: В Excel 2013 и новее выберите раздел Комбинированная в категории Все диаграммы, затем примените тип Точечная с прямыми отрезками к вашему ряду среднего значения.
10. Вернитесь к диаграмме и снова щёлкните правой кнопкой мыши, чтобы в контекстном меню выбрать пункт Выбрать данные.
11. В окне Выбрать данные выберите ряд Среднее и нажмите Изменить, чтобы точнее задать значения X и Y.
12. В окне Изменение ряда укажите диапазоны для значений X и Y (обычно это две ячейки под метками X и Y). Если вы изменили расположение, убедитесь, что ссылки соответствуют структуре вашего листа.
13. После нажатия ОК в окнах щёлкните правой кнопкой мыши по вторичной оси Y (справа) и выберите пункт Формат оси.
14. В окне Формат оси установите флажок Фиксированное рядом с параметром Максимум и задайте значение 1. Это заставит линию среднего значения проходить через всю высоту диаграммы.
Совет: Установка максимума оси равным 1 создаёт эффект «полной» вертикальной линии; если пропустить этот шаг, линия может получиться короткой или окажется не на том месте.
В Excel 2013 и новее можно напрямую ввести значение 1 в поле Максимум на панели «Формат оси».
15. После закрытия окна на гистограмме появится вертикальная линия среднего значения, которая визуально обозначает среднее значение данных для быстрого сравнения.
Для дальнейшего визуального улучшения щёлкните правой кнопкой мыши по линии и выберите пункт Формат ряда данных, чтобы открыть параметры форматирования цвета, стиля и толщины. Настройка этих элементов не только повышает читаемость презентации, но и помогает выдержать фирменный стиль вашей организации.
Общие рекомендации по устранению проблем: убедитесь, что диапазоны X и Значение Y не содержат пустых ячеек и что вы всегда выбираете правильные диапазоны ячеек в окнах Выбрать данныеИсходные данные
Решение с помощью кода VBA: добавление вертикальной/средней линии программным способом
Для пользователей, регулярно обновляющих диаграммы или составляющих периодические отчёты, автоматизация добавления вертикальной или средней линии с помощью VBA может существенно сэкономить время. Этот подход особенно эффективен при работе с несколькими похожими диаграммами или когда линии нужно обновлять по мере изменения данных — он полностью исключает необходимость вручную повторять одни и те же действия. Хотя использование VBA требует рабочих книг с поддержкой макросов и определённого знакомства с редактором Visual Basic, оно обеспечивает высокую гибкость и продуктивность.
Примечание: Перед запуском этого кода убедитесь, что на вашем листе есть гистограмма.
1. Перейдите на вкладку Разработчик и нажмите кнопку Visual Basic, чтобы открыть редактор Microsoft Visual Basic для приложений. Если вкладка «Разработчик» не отображается, ознакомьтесь с этим руководством: Отображение вкладки «Разработчик» в Excel.
2. В редакторе VBA нажмите Вставка > Модуль, чтобы создать новый модуль. Затем вставьте следующий код в окно редактора:
Sub AddAverageLineToBarChart()
Dim cht As ChartObject
Dim ws As Worksheet
Dim dataRange As Range
Dim avgValue As Double
Dim i As Integer
Dim nameSeries As String
Dim xValues(1 To 2) As Double
Dim yValues(1 To 2) As Double
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set cht = ws.ChartObjects(1) ' Use the first chart on the active sheet
Set dataRange = Application.InputBox("Select the data range for average calculation", xTitleId, Type:=8)
nameSeries = Application.InputBox("Name for the average series", xTitleId, "Average")
avgValue = Application.WorksheetFunction.Average(dataRange)
xValues(1) = avgValue
xValues(2) = avgValue
yValues(1) = 0
yValues(2) = 1
With cht.Chart
.SeriesCollection.NewSeries
With .SeriesCollection(.SeriesCollection.Count)
.Name = nameSeries
.XValues = xValues
.Values = yValues
.ChartType = xlXYScatterLines
.AxisGroup = 1
End With
End With
End Sub3После ввода кода нажмите кнопку 
, чтобы запустить макрос. Появится диалоговое окно с запросом на выбор диапазона данных для расчёта среднего значения, а затем — на указание имени ряда со средним значением. Макрос автоматически добавит вертикальную линию среднего значения на первый объект диаграммы на вашем текущем листе.
Совет: Если у вас несколько диаграмм, измените строку ws.ChartObjects(1), чтобы указать нужный индекс диаграммы.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек