Как выполнить квадратичную регрессию в Excel

Регрессия — это статистический метод, который мы можем использовать для объяснения взаимосвязи между одной или несколькими переменными-предикторами и переменной-откликом. Наиболее распространенным типом регрессии является линейная регрессия , которую мы используем, когда связь между переменной-предиктором и переменной-откликом является линейной .

То есть, когда предикторная переменная увеличивается, переменная отклика также имеет тенденцию к увеличению. Например, мы можем использовать модель линейной регрессии для описания взаимосвязи между количеством часов обучения (переменная-предиктор) и оценкой, которую студент получает на экзамене (переменная-ответ).

Однако иногда связь между переменной-предиктором и переменной-ответом нелинейна.Одним из распространенных типов нелинейных отношений является квадратичная зависимость , которая может выглядеть как U или перевернутая U на графике.

То есть, когда переменная-предиктор увеличивается, переменная-отклик также имеет тенденцию к увеличению, но после определенного момента переменная-отклик начинает уменьшаться, поскольку переменная-предиктор продолжает расти.

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

Давайте рассмотрим пример выполнения квадратичной регрессии в Excel.

Квадратичная регрессия в Excel

Предположим, у нас есть данные о количестве отработанных часов в неделю и сообщаемом уровне счастья (по шкале от 0 до 100) для 16 разных людей:

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

Выделите ячейки A2:B17.Затем щелкните вкладку «ВСТАВИТЬ» на верхней ленте, затем нажмите « Разброс » в области « Диаграммы ». Это создаст диаграмму рассеяния данных:

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

Прежде чем мы подгоним модель квадратичной регрессии к данным, нам нужно создать новый столбец для квадратов значений нашей переменной-предиктора.

Сначала выделите все значения в столбце B и перетащите их в столбец C.

Затем введите формулу =A2^2 в ячейку B2. Это дает значение 36.Затем щелкните в правом нижнем углу ячейки B2 и перетащите формулу вниз, чтобы заполнить оставшиеся ячейки в столбце B.

Далее мы подгоним модель квадратичной регрессии.

Нажмите «ДАННЫЕ» на верхней ленте, затем нажмите « Анализ данных» справа. Если вы не видите эту опцию, то вам сначала нужно установить бесплатный Analysis ToolPak .

После того, как вы нажмете « Анализ данных» , появится всплывающее окно. Нажмите «Регрессия», а затем нажмите «ОК» .

Затем заполните следующие значения в появившемся окне Регрессия.Затем нажмите ОК .

Будут отображены следующие результаты:

Вот как интерпретировать различные числа из вывода:

Квадрат R: также известный как коэффициент детерминации, это доля дисперсии переменной отклика, которая может быть объяснена предикторными переменными. В этом примере R-квадрат равен 0,9092 , что указывает на то, что 90,92% дисперсии зарегистрированных уровней счастья можно объяснить количеством отработанных часов и количеством отработанных часов^2.

Стандартная ошибка: Стандартная ошибка регрессии — это среднее расстояние, на которое наблюдаемые значения отклоняются от линии регрессии. В этом примере наблюдаемые значения отклоняются от линии регрессии в среднем на 9,519 единиц .

F-статистика : F-статистика рассчитывается как регрессия MS/остаточная MS. Эта статистика показывает, обеспечивает ли регрессионная модель лучшее соответствие данным, чем модель, которая не содержит независимых переменных. По сути, он проверяет, полезна ли регрессионная модель в целом. Как правило, если ни одна из переменных-предикторов в модели не является статистически значимой, общая F-статистика также не является статистически значимой. В этом примере статистика F равна 65,09 , а соответствующее значение p <0,0001. Поскольку это p-значение меньше 0,05, регрессионная модель в целом является значимой.

Коэффициенты регрессии. Коэффициенты регрессии в последней таблице дают нам числа, необходимые для написания оценочного уравнения регрессии:

у шляпа = б 0 + б 1 х 1 + б 2 х 1 2

В этом примере расчетное уравнение регрессии имеет вид:

сообщаемый уровень счастья = -30,252 + 7,173 (отработанные часы) -0,106 (отработанные часы) 2

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

сообщаемый уровень счастья = -30,252 + 7,173(30) -0,106(30) 2 = 88,649 .

Дополнительные ресурсы

Как добавить квадратную линию тренда в Excel
Как читать и интерпретировать таблицу регрессии
Что такое хорошее значение R-квадрата?
Понимание стандартной ошибки регрессии
Простое руководство по пониманию F-теста общей значимости в регрессии