Как добавить линию тренда в диаграмму Excel
В учебнике объясняется, как выполнять анализ тренда в Excel: как вставить линию тренда в диаграмму, отобразить ее уравнение и получить наклон линии тренда.
При отображении данных на графике вам часто может понадобиться визуализировать общую тенденцию ваших данных. Это можно сделать, добавив линию тренда на график. К счастью, Microsoft Excel сделал вставку линии тренда очень простой, особенно в более новых версиях. Тем не менее, есть несколько маленьких секретов, которые имеют большое значение, и я поделюсь ими с вами через минуту.
Линия тренда в Excel
Линия тренда, также называемая линией наилучшего соответствия, представляет собой прямую или изогнутую линию на диаграмме, которая показывает общую закономерность или общее направление данных.
Этот аналитический инструмент чаще всего используется для отображения движения данных за определенный период времени или корреляции между двумя переменными.
Визуально линия тренда чем-то похожа на линейную диаграмму, но она не соединяет фактические точки данных, как это делает линейная диаграмма. Линия наилучшего соответствия показывает общую тенденцию всех данных без учета статистических ошибок и незначительных исключений. В некоторых случаях его также можно использовать для прогнозирования тенденций.
Графики Excel, поддерживающие линии тренда
Линию тренда можно добавить к различным диаграммам Excel, включая XY разбрасывать, пузырь, запаса также несложенные 2-D бар, столбец, область а также линия графики.
Вы не можете добавить линию тренда к трехмерным диаграммам или диаграммам с накоплением, круговой диаграмме, радару и подобным визуальным элементам.
Ниже приведен пример точечной диаграммы с расширенная линия тренда:
Как добавить линию тренда в Excel
В Excel 2019, Excel 2016 и Excel 2013 добавление линии тренда — это быстрый трехэтапный процесс:
- Щелкните в любом месте диаграммы, чтобы выбрать ее.
- В правой части диаграммы нажмите кнопку Элементы диаграммы (кнопка с крестиком), а затем выполните одно из следующих действий:
- Проверить линия тренда поле для вставки линейной линии тренда по умолчанию:
- Нажмите на стрелку рядом с линия тренда поле и выберите один из предложенных типов:
- Щелкните стрелку рядом с линия тренда, а затем щелкните Дополнительные параметры. Это откроет Формат линии тренда панель, где вы переключаетесь на вкладку «Параметры линии тренда», чтобы просмотреть все типы линий тренда, доступные в Excel, и выбрать тот, который вам нужен. По умолчанию Линейный линия тренда будет предварительно выбрана автоматически. По желанию вы также можете отображать уравнение линии тренда по графику.
- Проверить линия тренда поле для вставки линейной линии тренда по умолчанию:
Кончик. Еще один быстрый способ добавить линию тренда на диаграмму Excel — щелкнуть правой кнопкой мыши ряд данных и выбрать «Добавить линию тренда…».
Как сделать линию тренда в Excel 2010
Чтобы добавить линию тренда в Excel 2010, вы следуете другим путем:
- На диаграмме щелкните ряд данных, для которого вы хотите нарисовать линию тренда.
- Под Инструменты диаграммыиди в Макет вкладка > Анализ нажмите «Линия тренда» и выполните одно из следующих действий:
- Выберите один из предустановленных вариантов или
- Нажмите «Дополнительные параметры линии тренда…», а затем выберите тип линии тренда для диаграммы.
Как вставить несколько линий тренда на один график
Microsoft Excel позволяет добавлять на диаграмму более одной линии тренда. Есть два сценария, которые следует обрабатывать по-разному.
Добавьте линию тренда для каждого ряда данных
Чтобы поместить линию тренда на диаграмму с двумя или более рядами данных, выполните следующие действия:
- Щелкните правой кнопкой мыши интересующие точки данных (синие в этом примере) и выберите «Добавить линию тренда…» в контекстном меню:
- Откроется вкладка «Параметры линии тренда» на панели, где вы можете выбрать нужный тип линии:
- Повторите вышеуказанные шаги для других рядов данных.
В результате у каждого ряда данных будет своя линия тренда соответствующего цвета:
Кроме того, вы можете нажать кнопку Диаграмма Элементы кнопку, затем щелкните стрелку рядом с линией тренда и выберите нужный тип. Excel покажет список рядов данных, нанесенных на диаграмму. Вы выбираете нужный и нажимаете ХОРОШО.
Нарисуйте разные типы линий тренда для одного и того же ряда данных
Чтобы создать две или более разных линий тренда для одного и того же ряда данных, добавьте первую линию тренда, как обычно, а затем выполните одно из следующих действий:
- Щелкните правой кнопкой мыши ряд данных, выберите «Добавить линию тренда…» в контекстном меню, а затем выберите другой тип линии тренда на панели.
- Нажмите на Элементы диаграммы нажмите кнопку , щелкните стрелку рядом с линией тренда и выберите тип, который вы хотите добавить.
В любом случае Excel отобразит на диаграмме несколько линий тренда, в нашем случае линейную и скользящую среднюю, для которых вы можете установить разные цвета:
Как отформатировать линию тренда в Excel
Чтобы сделать ваш график еще более понятным и легко интерпретируемым, вы можете изменить внешний вид линии тренда по умолчанию. Для этого щелкните его правой кнопкой мыши и выберите «Формат линии тренда…». Или просто дважды щелкните линию тренда, чтобы открыть Формат линии тренда панель.
На панели переключитесь на Заполнение и линия и выберите цвет, ширину и тип штриха для линии тренда. Например, вы можете сделать это сплошной линией, а не пунктирной:
Как расширить линию тренда в Excel
Чтобы спроецировать тенденции данных в будущее или прошлое, вам нужно сделать следующее:
- Дважды щелкните линию тренда, чтобы открыть Формат линии тренда панель.
- На Вкладка «Параметры линии тренда» (последний), введите нужные значения в поля «Вперед» и/или «Назад» в Прогноз:
В этом примере мы решили продлить линию тренда на 8 периодов за пределы последней точки данных:
Уравнение линии тренда Excel
Уравнение линии тренда — это формула, которая математически описывает линию, которая лучше всего соответствует точкам данных. Уравнения различаются для разных типов линий тренда, хотя в каждом уравнении Excel использует метод наименьших квадратов, чтобы найти наилучшее соответствие для линии через точки данных. В этом руководстве вы можете найти уравнения для всех типов линий тренда Excel.
При рисовании линии наилучшего соответствия в Excel можно отобразить ее уравнение на диаграмме. Кроме того, вы можете отображать Значение R-квадрата.
Значение R-квадрата (Коэффициент детерминации) указывает насколько хорошо линия тренда соответствует данным. Чем ближе значение R2 к 1, тем лучше соответствие.
Как отобразить уравнение линии тренда на графике
Чтобы отобразить уравнение и значение R-квадрата на графике, сделайте следующее:
- Дважды щелкните линию тренда, чтобы открыть ее панель.
- На панели переключитесь на Вкладка «Параметры линии тренда» и установите эти флажки:
- Отображение уравнения на графике
- Отображение значения R-квадрата на графике
Это поместит формулу линии тренда и значение R2 в верхнюю часть графика, и вы сможете свободно перетаскивать их туда, куда считаете нужным.
В этом примере значение R-квадрата равно 0,957, что означает, что линия тренда соответствует примерно 95% значений данных.
Примечание. Уравнение, отображаемое на диаграмме Excel, верно только для точечных диаграмм XY. Дополнительные сведения см. в статье Почему уравнение линии тренда в Excel неверно.
Показать больше цифр в уравнении линии тренда
Если уравнение линии тренда в Excel дает неточные результаты, когда вы вводите в него значения x вручную, скорее всего, это связано с округлением. По умолчанию числа в уравнении линии тренда округляются до 2–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. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!