Как добавить линию тренда в диаграмму Excel

В учебнике объясняется, как выполнять анализ тренда в Excel: как вставить линию тренда в диаграмму, отобразить ее уравнение и получить наклон линии тренда.

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

Линия тренда в Excel

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

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

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

Графики Excel, поддерживающие линии тренда

Линию тренда можно добавить к различным диаграммам Excel, включая XY разбрасывать, пузырь, запаса также несложенные 2-D бар, столбец, область а также линия графики.

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

Ниже приведен пример точечной диаграммы с расширенная линия тренда:
Линия тренда в Excel

Как добавить линию тренда в Excel

В Excel 2019, Excel 2016 и Excel 2013 добавление линии тренда — это быстрый трехэтапный процесс:

  1. Щелкните в любом месте диаграммы, чтобы выбрать ее.
  2. В правой части диаграммы нажмите кнопку Элементы диаграммы (кнопка с крестиком), а затем выполните одно из следующих действий:
    • Проверить линия тренда поле для вставки линейной линии тренда по умолчанию:
      Добавьте линейную линию тренда по умолчанию на диаграмму Excel.
    • Нажмите на стрелку рядом с линия тренда поле и выберите один из предложенных типов:
      Выберите тип линии тренда для вставки.
    • Щелкните стрелку рядом с линия тренда, а затем щелкните Дополнительные параметры. Это откроет Формат линии тренда панель, где вы переключаетесь на вкладку «Параметры линии тренда», чтобы просмотреть все типы линий тренда, доступные в Excel, и выбрать тот, который вам нужен. По умолчанию Линейный линия тренда будет предварительно выбрана автоматически. По желанию вы также можете отображать уравнение линии тренда по графику.
      Откройте панель, чтобы увидеть все доступные типы линий тренда.

Кончик. Еще один быстрый способ добавить линию тренда на диаграмму Excel — щелкнуть правой кнопкой мыши ряд данных и выбрать «Добавить линию тренда…».

Как сделать линию тренда в Excel 2010

Чтобы добавить линию тренда в Excel 2010, вы следуете другим путем:

  1. На диаграмме щелкните ряд данных, для которого вы хотите нарисовать линию тренда.
  2. Под Инструменты диаграммыиди в Макет вкладка > Анализ нажмите «Линия тренда» и выполните одно из следующих действий:
    • Выберите один из предустановленных вариантов или
    • Нажмите «Дополнительные параметры линии тренда…», а затем выберите тип линии тренда для диаграммы.

Вставьте линию тренда в Excel 2010.

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

Microsoft Excel позволяет добавлять на диаграмму более одной линии тренда. Есть два сценария, которые следует обрабатывать по-разному.

Добавьте линию тренда для каждого ряда данных

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

  1. Щелкните правой кнопкой мыши интересующие точки данных (синие в этом примере) и выберите «Добавить линию тренда…» в контекстном меню:
    Добавьте линию тренда для первого ряда данных.
  2. Откроется вкладка «Параметры линии тренда» на панели, где вы можете выбрать нужный тип линии:
    Выберите нужный тип линии.
  3. Повторите вышеуказанные шаги для других рядов данных.

В результате у каждого ряда данных будет своя линия тренда соответствующего цвета:
Диаграмма Excel с несколькими линиями тренда

Кроме того, вы можете нажать кнопку Диаграмма Элементы кнопку, затем щелкните стрелку рядом с линией тренда и выберите нужный тип. Excel покажет список рядов данных, нанесенных на диаграмму. Вы выбираете нужный и нажимаете ХОРОШО.
Другой способ добавить несколько линий тренда на диаграмму Excel

Нарисуйте разные типы линий тренда для одного и того же ряда данных

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

  • Щелкните правой кнопкой мыши ряд данных, выберите «Добавить линию тренда…» в контекстном меню, а затем выберите другой тип линии тренда на панели.
  • Нажмите на Элементы диаграммы нажмите кнопку , щелкните стрелку рядом с линией тренда и выберите тип, который вы хотите добавить.

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

Как отформатировать линию тренда в Excel

Чтобы сделать ваш график еще более понятным и легко интерпретируемым, вы можете изменить внешний вид линии тренда по умолчанию. Для этого щелкните его правой кнопкой мыши и выберите «Формат линии тренда…». Или просто дважды щелкните линию тренда, чтобы открыть Формат линии тренда панель.
Форматировать линию тренда в Excel.

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

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

  1. Дважды щелкните линию тренда, чтобы открыть Формат линии тренда панель.
  2. На Вкладка «Параметры линии тренда» (последний), введите нужные значения в поля «Вперед» и/или «Назад» в Прогноз:

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

Уравнение линии тренда Excel

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

При рисовании линии наилучшего соответствия в Excel можно отобразить ее уравнение на диаграмме. Кроме того, вы можете отображать Значение R-квадрата.

Значение R-квадрата (Коэффициент детерминации) указывает насколько хорошо линия тренда соответствует данным. Чем ближе значение R2 к 1, тем лучше соответствие.

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

Чтобы отобразить уравнение и значение R-квадрата на графике, сделайте следующее:

  1. Дважды щелкните линию тренда, чтобы открыть ее панель.
  2. На панели переключитесь на Вкладка «Параметры линии тренда» и установите эти флажки:
    • Отображение уравнения на графике
    • Отображение значения R-квадрата на графике

Это поместит формулу линии тренда и значение R2 в верхнюю часть графика, и вы сможете свободно перетаскивать их туда, куда считаете нужным.

В этом примере значение R-квадрата равно 0,957, что означает, что линия тренда соответствует примерно 95% значений данных.
Отобразите уравнение линии тренда и значение R-квадрата на графике.

Примечание. Уравнение, отображаемое на диаграмме Excel, верно только для точечных диаграмм XY. Дополнительные сведения см. в статье Почему уравнение линии тренда в Excel неверно.

Показать больше цифр в уравнении линии тренда

Если уравнение линии тренда в Excel дает неточные результаты, когда вы вводите в него значения x вручную, скорее всего, это связано с округлением. По умолчанию числа в уравнении линии тренда округляются до 2–4 знаков после запятой. Однако вы можете легко сделать видимыми больше цифр. Вот как:

  1. Выберите формулу линии тренда на диаграмме.
  2. На Формат метки линии тренда на появившейся панели перейдите на вкладку «Параметры метки».
  3. в Категория раскрывающемся списке выберите Номер.
  4. в Десятичные разряды введите количество знаков после запятой, которое вы хотите отобразить (до 30), и нажмите Enter, чтобы обновить уравнение на диаграмме.

Показать больше цифр в уравнении линии тренда.

Как найти наклон линии тренда в Excel

Для получения наклона линейной линии тренда в Microsoft Excel предусмотрена специальная одноименная функция:

НАКЛОН (известные_y, известные_x)

Где:

  • Known_y’s — это диапазон зависимых точек данных, нанесенных на ось Y.
  • Известные_x — это диапазон независимых точек данных, нанесенных на ось X.

С Икс значения в B2:B13 и у значения в C2:C13, формула выглядит следующим образом:

=НАКЛОН(C2:C13, B2:B13)

Наклон также можно рассчитать с помощью функции ЛИНЕЙН в обычной формуле:

=ЛИНЕЙН(C2:C13;B2:B13)

Если ввести как формулу массива, нажав Ctrl + Shift + Enter, она вернет наклон линии тренда и точку пересечения по оси Y в двух соседних ячейках в одной строке. Дополнительные сведения см. в разделе Как использовать функцию ЛИНЕЙН в Excel.

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

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

Как удалить линию тренда в Excel

Чтобы удалить линию тренда с диаграммы, щелкните ее правой кнопкой мыши и выберите «Удалить»:
Удаление линии тренда с графика

Или нажмите на Элементы диаграммы и снимите флажок «Линия тренда»:
Удаление линии тренда в Excel.

В любом случае Excel немедленно удалит линию тренда с диаграммы.

Вот как сделать линию тренда в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *