Как сделать линии на графике в excel

Foodband

Как добавить линию на график

Иногда требуется обозначить на графике некоторые контрольные значения или коридор, например, среднее значение и сигму-окрестность. Однажды, выступая на конференции с докладом «Как повысить качество управленческих решений» мне потребовалось проиллюстрировать разброс числа рекламаций в течение года (рис. 1). Наряду со значениями (красные точки), я вывел на графике линию среднего значения (зеленая линия) и верхнюю границу диапазона, соответствующую отклонению 3s от среднего значения (синяя пунктирная линия):

Рис. 1. График со средним и границей

Скачать заметку в формате Word, примеры в формате Excel

Построение такого графика не должно вызвать затруднений. Рассмотрим лист «Коридор» Excel-файла:

Во второй колонке указаны значения средней дебиторской задолженности по месяцам, в третьей – среднее значение по году, вычисленное по формуле =СРЗНАЧ($B$2:$B$13), в четвертой – нижняя граница, соответствующая значениям на s меньше среднего =C2-СТАНДОТКЛОН($B$2:$B$13), в пятой – верхняя граница, соответствующая значениям на s больше среднего =C2+СТАНДОТКЛОН($B$2:$B$13).

Примечание. Чтобы отразить данные в миллионах рублей, я использовал специальный (пользовательский) формат:

Обратите внимание, что после # ##0,0 имеются два пробела.

Строим стандартный график:

Форматируем линии: убираем маркеры на опорных линиях, меняем цвет и тип линий, убираем линию на основных данных. Удаляем линии сетки, чтобы наши контрольные линии лучше выделялись. Добавляем релевантный заголовок нашему графику:

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

Выделите одну из контрольных линий, например, линию среднего, и правой кнопкой мыши вызовите контекстное меню; выберите пункт «Формат ряда данных»:

Поставьте переключатель в положение «По вспомогательной оси»:

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

Перейдите на вкладку Макет и пройдите по меню Оси — Вспомогательная горизонтальная ось — Слева направо

Выделите вспомогательную горизонтальную ось и переключите «Положение оси» в позицию «по делениям»:

Диаграмма должна выглядеть так:

Выделите и удалите вспомогательную вертикальную ось. При этом контрольные линии расположатся в соответствии с основной вертикальной осью. А вот горизонтальную вспомогательную ось удалить нельзя, так как настройки контрольных линий (вспомогательная горизонтальная ось в положении «по делениям») пропадут. Поэтому нужно выделить горизонтальную вспомогательную ось и отключить основные деления и подписи оси:

Вот что у нас получилось:

Небольшое домашнее задание. Попробуйте сделать невидимой вспомогательную горизонтальную ось.

Как сделать линии на графике в excel

  • Советы по Excel
  • Функции Excel
  • Формулы Excel
  • Советы по словам
  • Советы по Outlook

Как добавить на график в Excel горизонтальную среднюю линию?

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

Foodband

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

1. Вычислить среднее значение данных с помощью Средняя функции, например, в столбце средних значений C2 введите следующую формулу: = Среднее (2 млрд долларов США: 8 млрд долларов США), а затем перетащите маркер автозаполнения этой ячейки в нужный диапазон. Смотрите скриншот:

2. Затем выберите этот диапазон и выберите один формат диаграммы, который вы хотите вставить, например столбец 2-D под Вставить таб. Смотрите скриншот:

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

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

5, Нажмите OK кнопка. Теперь у вас есть горизонтальная линия, представляющая среднее значение на вашем графике, см. Снимок экрана:

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

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

Если вам нужно добавить горизонтальную среднюю линию к столбчатой ​​диаграмме в Excel, обычно вам нужно добавить столбец среднего значения к исходным данным, затем добавить ряд данных средних значений в диаграмму, а затем изменить тип диаграммы для нового добавленного ряд данных. Однако с Добавить линию в диаграмму особенность Kutools for Excel, вы можете быстро добавить такую ​​среднюю линию на график всего за 2 шага! Полнофункциональная бесплатная 30-дневная пробная версия!

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Предположим, вы создали столбчатую диаграмму со своими данными на листе, и следующий код VBA также может помочь вам вставить среднюю линию на диаграмму.

1.Щелкните один из столбцов данных на диаграмме, после чего будут выбраны все столбцы данных, см. Снимок экрана:

2. Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.

3. Нажмите Вставить > модульи вставьте следующий код в Окно модуля.

VBA: добавить на график среднюю линию

4, Затем нажмите F5 для запуска этого кода, и в столбчатую диаграмму была вставлена ​​горизонтальная средняя линия. Смотрите скриншот:

Внимание: Этот VBA может работать только в том случае, если формат столбца, который вы вставляете, представляет собой столбец 2-D.

Этот метод порекомендует потрясающий инструмент, Добавить линию в диаграмму особенность Kutools for Excel, чтобы быстро добавить горизонтальную среднюю линию к выбранной гистограмме всего за 2 клика!

Kutools for Excel— Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

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

1. Выберите столбчатую диаграмму и щелкните Kutools > Графики > Добавить линию в диаграмму для включения этой функции.

2. В диалоговом окне "Добавить строку в диаграмму" установите флажок Средняя и нажмите Ok кнопку.

Теперь горизонтальная средняя линия сразу добавляется к выбранной гистограмме.

Вставить и распечатать среднее значение на каждой странице в Excel

Kutools для Excel Промежуточные итоги по страницам Утилита может помочь вам легко вставить все виды промежуточных итогов (например, Sum, Max, Min, Product и т. д.) на каждую печатную страницу. Полнофункциональная бесплатная 30-дневная пробная версия!
Промежуточный итог средней страницы 3

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Foodband

Ссылка на основную публикацию