Типы линий тренда Excel, уравнения и формулы
В этом руководстве вы найдете подробное описание всех параметров линии тренда, доступных в Excel, и того, когда их использовать. Вы также узнаете, как отобразить уравнение линии тренда на диаграмме и найти наклон линии тренда.
Добавить линию тренда в Excel очень просто. Единственная реальная проблема состоит в том, чтобы выбрать тип линии тренда, который лучше всего соответствует типу данных, которые вы анализируете. В этом руководстве вы найдете подробное описание всех параметров линии тренда, доступных в Excel, и того, когда их использовать. Если вы ищете, как вставить линию тренда в диаграмму Excel, ознакомьтесь с приведенным выше руководством.
Типы линий тренда Excel
При добавлении линии тренда в Excel у вас есть 6 различных вариантов на выбор. Кроме того, Microsoft Excel позволяет отображать на диаграмме уравнение линии тренда и значение R-квадрата:
- Уравнение линии тренда — это формула, которая находит линию, которая лучше всего соответствует точкам данных.
- Значение R-квадрата измеряет надежность линии тренда: чем ближе R2 к 1, тем лучше линия тренда соответствует данным.
Ниже вы найдете краткое описание каждого типа линии тренда с примерами графиков.
Линейная линия тренда
Линейную линию тренда лучше всего использовать с линейными наборами данных, когда точки данных на диаграмме напоминают прямую линию. Как правило, линейная линия тренда описывает непрерывный рост или падение во времени.
Например, следующая линейная линия тренда показывает устойчивый рост продаж в течение 6 месяцев. А значение R2, равное 0,9855, указывает на довольно хорошее соответствие оценочных значений линии тренда фактическим данным.
Экспоненциальная линия тренда
Экспоненциальная линия тренда — это изогнутая линия, которая иллюстрирует рост или падение значений данных с возрастающей скоростью, поэтому линия обычно более изогнута с одной стороны. Этот тип линии тренда часто используется в науке, например, для визуализации роста населения или сокращения популяций диких животных.
Обратите внимание, что экспоненциальная линия тренда не может быть создана для данных, содержащих нули или отрицательные значения.
Хорошим примером экспоненциальной кривой является убыль всей популяции диких тигров на Земле.
Логарифмическая линия тренда
Логарифмическая линия наилучшего соответствия обычно используется для отображения данных, которые быстро увеличиваются или уменьшаются, а затем выравниваются. Он может включать как положительные, так и отрицательные значения.
Примером логарифмической линии тренда может быть уровень инфляции, который сначала увеличивается, но через некоторое время стабилизируется.
Полиномиальная линия тренда
Полиномиальная криволинейная линия тренда хорошо работает для больших наборов данных с колеблющимися значениями, которые имеют более одного подъема и падения.
Как правило, многочлен классифицируется по степени наибольшего показателя. Степень полиномиальной линии тренда также можно определить по количеству изгибов на графике. Как правило, линия тренда квадратичного полинома имеет один изгиб (холм или впадина), кубический полином имеет 1 или 2 изгиба, а полином четвертой степени имеет до 3 изгибов.
При добавлении полиномиальной линии тренда в диаграмму Excel вы указываете степень, вводя соответствующее число в поле «Порядок» на панели инструментов. Формат линии тренда панель, которая по умолчанию равна 2:
Например, квадратичный полиномиальный тренд виден на следующем графике, который показывает зависимость между прибылью и количеством лет, в течение которых продукт находится на рынке: рост в начале, пик в середине и падение ближе к концу.
Линия тренда мощности
Линия тренда мощности очень похожа на экспоненциальную кривую, только имеет более симметричную дугу. Он обычно используется для построения графиков измерений, которые увеличиваются с определенной скоростью.
Линия тренда мощности не может быть добавлена к диаграмме Excel, содержащей нулевые или отрицательные значения.
В качестве примера, давайте нарисуем линию тренда мощности, чтобы визуализировать скорость химической реакции. Обратите внимание на значение R-квадрата 0,9918, что означает, что наша линия тренда почти идеально соответствует данным.
Линия тренда скользящей средней
Когда точки данных на диаграмме имеют много взлетов и падений, линия тренда скользящего среднего может сгладить экстремальные колебания значений данных, чтобы более четко показать закономерность. Для этого Excel вычисляет скользящее среднее за указанное вами количество периодов (по умолчанию 2) и помещает эти средние значения в виде точек на линии. Чем выше Период значение, тем ровнее линия.
Хорошим практическим примером является использование линии тренда скользящей средней для выявления колебаний цены акции, которые в противном случае было бы трудно наблюдать.
Для получения дополнительной информации см.: Как добавить линию тренда скользящего среднего на диаграмму Excel.
Уравнения и формулы линий тренда в Excel
В этом разделе описываются уравнения, которые Excel использует для различных типов линий тренда. Вам не нужно создавать эти формулы вручную, просто скажите Excel, чтобы отобразить уравнение линии тренда на диаграмме.
Также мы обсудим формулу для определения наклона линии тренда и другие коэффициенты. В формулах предполагается, что у вас есть 2 набора переменных: независимая переменная x и зависимая переменная y. В своих рабочих листах вы можете использовать эти формулы, чтобы получить предсказанный у значения для любых заданных значений Икс.
Для согласованности мы будем использовать один и тот же набор данных с немного различающимися значениями для всех примеров. Однако имейте в виду, что это только для демонстрационных целей. В ваших реальных рабочих листах вы должны выбрать тип линии тренда соответствующий вашему типу данных.
Важная заметка! Формулы линий тренда следует использовать только с точечными диаграммами XY, потому что только эта диаграмма Икс а также у оси как числовые значения. Для получения дополнительной информации см. Почему уравнение линии тренда в Excel может быть неправильным.
Линейное уравнение линии тренда и формулы
Линейное уравнение линии тренда использует метод наименьших квадратов для поиска склон а также перехватывать коэффициенты такие, что:
у = Ьх + а
Где:
- б представляет собой наклон линии тренда.
- а представляет собой точку пересечения по оси Y, которая представляет собой ожидаемое среднее значение у когда все Икс переменные равны 0. На графике это точка, в которой линия тренда пересекает у ось.
Для линейной регрессии Microsoft Excel предоставляет специальные функции для получения коэффициентов наклона и пересечения.
Наклон линии тренда
б: = НАКЛОН (у, х)
Y-перехват
а: =ПЕРЕХОД (у, х)
Предполагая Икс диапазон B2:B13 и у диапазон C2:C13, реальные формулы выглядят следующим образом:
=НАКЛОН(C2:C13, B2:B13)
=ПЕРЕХОД(C2:C13;B2:B13)
Таких же результатов можно добиться, используя функцию ЛИНЕЙН в качестве формулы массива. Для этого выделите 2 соседние ячейки в одной строке, введите формулу и нажмите Ctrl+Shift+Enter для ее завершения:
=ЛИНЕЙН(C2:C13;B2:B13)
Как показано на снимке экрана ниже, коэффициенты наклона и пересечения, возвращаемые формулами, полностью соответствуют коэффициентам в уравнении линейной линии тренда, отображаемым на диаграмме, только последние округлены до 4 знаков после запятой:
Экспоненциальное уравнение линии тренда и формулы
Для экспоненциальной линии тренда Excel использует следующее уравнение:
у = аебх
Где а а также б – вычисляемые коэффициенты, а e – математическая константа e (основание натурального логарифма).
Коэффициенты можно рассчитать, используя следующие общие формулы:
a: =EXP(ИНДЕКС(ЛИНЕЙН(LN(y), x), 1, 2))
б: =ИНДЕКС(ЛИНЕЙН(LN(y), x), 1)
Для нашего примера набора данных формулы принимают следующий вид:
a: =EXP(ИНДЕКС(ЛИНЕЙН(LN(C2:C13), B2:B13), 1, 2))
б: =ИНДЕКС(ЛИНЕЙН(LN(C2:C13), B2:B13), 1)
Логарифмическое уравнение линии тренда и формулы
Вот логарифмическое уравнение линии тренда в Excel:
у = а * пер (х) + б
Где а а также б константы и п функция натурального логарифма.
Чтобы получить константы, используйте эти общие формулы, которые отличаются только последним аргументом:
a: =ИНДЕКС(ЛИНЕЙШ(y, LN(x)), 1)
б: =ИНДЕКС(ЛИНЕЙН(y, LN(x)), 1, 2)
Для нашего примера набора данных мы используем следующие:
a: =ИНДЕКС(ЛИНЕЙН(C2:C13, LN(B2:B13)), 1)
b: =ИНДЕКС(ЛИНЕЙШ(C2:C13, LN(B2:B13)), 1, 2)
Полиномиальное уравнение линии тренда и формулы
Чтобы разработать полиномиальную линию тренда, Excel использует это уравнение:
у = b6x6 + … + b2x2 + b1x + а
Где б1…b6 а также а являются константами.
В зависимости от степени вашей полиномиальной линии тренда используйте один из следующих наборов формул, чтобы получить константы.
Квадратичная (2-го порядка) полиномиальная линия тренда
Уравнение: у = b2x2+ b1x + а
b2: =ИНДЕКС(ЛИНЕЙН(y, x^{1,2}), 1)
b1: =ИНДЕКС(ЛИНЕЙН(y, x^{1,2}), 1, 2)
a: =ИНДЕКС(ЛИНЕЙН(y, x^{1,2}), 1, 3)
Кубическая (3-го порядка) полиномиальная линия тренда
Уравнение: y = b3x3 + b2x2+ b1x + a
b3: =ИНДЕКС(ЛИНЕЙН(y, x^{1,2,3}), 1)
b2: =ИНДЕКС(ЛИНЕЙН(y, x^{1,2,3}), 1, 2)
b1: =ИНДЕКС(ЛИНЕЙН(y, x^{1,2,3}), 1, 3)
a: =ИНДЕКС(ЛИНЕЙН(y, x^{1,2,3}), 1, 4)
Формулы для полиномиальных линий тренда более высокой степени могут быть построены с использованием того же шаблона.
Для нашего набора данных лучше подходит полиномиальная линия тренда 2-го порядка, поэтому мы используем следующие формулы:
b2: =ИНДЕКС(ЛИНЕЙН(C2:C13, B2:B13^{1,2}), 1)
b1: =ИНДЕКС(ЛИНЕЙН(C2:C13, B2:B13^{1,2}), 1, 2)
a: =ИНДЕКС(ЛИНЕЙН(C2:C13, B2:B13^{1,2}), 1, 3)
Уравнение и формулы линии тренда мощности
Линия тренда мощности в Excel рисуется на основе этого простого уравнения:
у = ахб
Где а а также б константы, которые можно вычислить по следующим формулам:
a: =EXP(ИНДЕКС(ЛИНЕЙН(LN(y), LN(x),,), 1, 2))
b: =ИНДЕКС(ЛИНЕЙН(LN(y), LN(x),,), 1)
В нашем случае работают следующие формулы:
a: =EXP(ИНДЕКС(ЛИНЕЙН(LN(C2:C13), LN(B2:B13),,), 1, 2))
b: =ИНДЕКС(ЛИНЕЙН(LN(C2:C13), LN(B2:B13),,), 1)
Уравнение линии тренда в Excel неверно — причины и исправления
Если вы считаете, что Excel неправильно нарисовал линию тренда или формула линии тренда, отображаемая на диаграмме, неверна, следующие два пункта могут пролить свет на ситуацию.
Уравнение линии тренда Excel верно только в точечных диаграммах
Формулы линий тренда Excel следует использовать только с диаграммами XY (разброс), потому что только в этом типе диаграммы и ось Y, и ось X отображаются в виде числовых значений.
В линейных диаграммах, столбчатых и гистограммах числовые значения отображаются только по оси Y. Ось X представлена линейным рядом (1, 2, 3,…) независимо от того, являются ли метки осей числами или текстом. Когда вы создаете линию тренда на этих диаграммах, Excel использует эти предполагаемые значения x в формуле линии тренда.
Числа округлены в уравнении линии тренда Excel
Чтобы занимать меньше места на диаграмме, Excel отображает очень мало значащих цифр в уравнении линии тренда. Хороший с точки зрения дизайна, он значительно снижает точность формулы, когда вы вручную указываете значения x в уравнении.
Простое решение — показать больше знаков после запятой в уравнении. Кроме того, вы можете рассчитать коэффициенты, используя формулу, соответствующую вашему типу линии тренда, и отформатировать ячейки формулы так, чтобы в них отображалось достаточное количество знаков после запятой. Для этого просто нажмите кнопку Увеличить десятичное число кнопка на Дом вкладка в Число группа.
Вот как вы можете создавать различные типы линий тренда в Excel и получать их уравнения. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
