Как выполнить квадратичную регрессию в 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.

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

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

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

у _шляпа = б ₀ + б ₁ х ₁ + б ₂ х ₁ ²

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

заявленный уровень счастья = -30,252 + 7,173 (Отработанные часы) -0,106 (Отработанные часы) ²

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

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

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

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