Как выполнить полиномиальную регрессию в Excel
Регрессионный анализ используется для количественной оценки взаимосвязи между одной или несколькими независимыми переменными и переменной отклика .
Наиболее распространенным типом регрессионного анализа является простая линейная регрессия , которая используется, когда независимая переменная и переменная отклика имеют линейную связь.
Однако иногда связь между объясняющей переменной и переменной отклика нелинейна.
В этих случаях имеет смысл использовать полиномиальную регрессию , которая может учитывать нелинейную связь между переменными.
В этом руководстве объясняется, как выполнить полиномиальную регрессию в Excel.
Пример: полиномиальная регрессия в Excel
Предположим, у нас есть следующий набор данных в Excel:
Используйте следующие шаги, чтобы подогнать уравнение полиномиальной регрессии к этому набору данных:
Шаг 1: Создайте диаграмму рассеяния.
Во-первых, нам нужно создать диаграмму рассеяния. Перейдите в группу Charts на вкладке Insert и щелкните первый тип диаграммы в Scatter :
Автоматически появится диаграмма рассеяния:
Шаг 2: Добавьте линию тренда.
Далее нам нужно добавить линию тренда на диаграмму рассеяния. Для этого щелкните любую из отдельных точек на диаграмме рассеивания. Затем щелкните правой кнопкой мыши и выберите «Добавить линию тренда…».
Появится новое окно с возможностью указать линию тренда. Выберите Polynomial и выберите число, которое вы хотите использовать для Order.Мы будем использовать 3. Затем установите флажок рядом с надписью Display Equation on chart внизу.
На диаграмме рассеяния автоматически появится линия тренда с уравнением полиномиальной регрессии:
Шаг 3: Интерпретируйте уравнение регрессии.
Для этого конкретного примера наше подобранное уравнение полиномиальной регрессии:
у = -0,1265 х 3 + 2,6482 х 2 – 14,238 х + 37,213
Это уравнение можно использовать для нахождения ожидаемого значения переменной отклика на основе заданного значения объясняющей переменной. Например, предположим, что x = 4. Ожидаемое значение переменной ответа y будет следующим:
у = -0,1265(4) 3 + 2,6482(4) 2 – 14,238(4) + 37,213 = 14,5362 .
Как построить полиномиальную кривую в Excel (шаг за шагом)
Вы можете использовать функцию ЛИНЕЙН() в Excel, чтобы подобрать полиномиальную кривую с определенной степенью.
Например, вы можете использовать следующий базовый синтаксис, чтобы подогнать полиномиальную кривую со степенью 3:
=LINEST( known_ys , known_xs ^) Функция возвращает массив коэффициентов, описывающих полиномиальную подгонку.
В следующем пошаговом примере показано, как использовать эту функцию для подбора полиномиальной кривой в Excel.
Шаг 1: Создайте данные
Во-первых, давайте создадим некоторые данные для работы:
Шаг 2: Подберите полиномиальную кривую
Далее воспользуемся функцией ЛИНЕЙН() , чтобы подобрать полиномиальную кривую степени 3 к набору данных:
Шаг 3: Интерпретация полиномиальной кривой
Как только мы нажмем ENTER , появится массив коэффициентов:
Используя эти коэффициенты, мы можем построить следующее уравнение, описывающее взаимосвязь между x и y:
у = 0,0218x 3 – 0,2239x 2 – 0,6084x + 30,0915
Мы также можем использовать это уравнение для вычисления ожидаемого значения y на основе значения x.
Например, предположим, что x = 4. Ожидаемое значение y будет следующим:
у = 0,0218(4) 3 – 0,2239(4) 2 – 0,6084(4) + 30,0915 = 25,47
МНК: Приближение полиномом в EXCEL
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью полинома (до 6-й степени включительно).
В основной статье про МНК было рассмотрено приближение линейной функцией. В этой статье рассмотрим приближение полиномиальной функцией (с 3-й до 6-й степени) следующего вида: y=b 0 +b 1 x+b 2 x 2 +b 3 x 3 +…+b 6 x 6
Примечание : В инструменте MS EXCEL Линия тренда , который доступен для диаграмм типа Точечная и График , можно построить линию тренда на основе полинома с максимальной степенью 6. В файле примера продемонстрировано полное совпадение линии тренда диаграммы и линии, вычисленной с помощью формул.
Покажем, как вычислить коэффициенты b линии тренда, заданной полиномом.
Как известно, квадратичная зависимость y=b 0 +b 1 x+b 2 x 2 , подробно рассмотренная в статье МНК: Квадратичная зависимость в MS EXCEL , является частным случаем полиномиальной y=b 0 +b 1 x+b 2 x 2 +b 3 x 3 +… зависимости (в этом случае степень полинома равна 2). Соответственно, используя тот же подход (приравнивание к 0 частных производных), можно вычислить коэффициенты любого полинома.
Примечание : Существует еще один метод вычисления коэффициентов – замена переменных, который рассмотрен в конце статьи.
Для нахождения m+1 коэффициента полинома m-й степени составим систему из m+1 уравнения и решим ее методом обратной матрицы . Для квадратного уравнения (m=2) нам потребовалось вычислить сумму значений х с 1-й до 4-й степени, а для полинома m-й степени необходимо вычислить значения х с 1-й до 2*m степени.
Примечание : Для удобства суммы степеней значений х можно вычислить в отдельном диапазоне ( файл примера столбцы К:М).
В файле примера создана универсальная форма для вычисления коэффициентов полиномов.
Выбрав с помощью элемента управления Счетчик нужную степень полинома, автоматически получим аппроксимацию наших данных выбранным полиномом (будет построен соответствующий график).
Примечание: При использовании полиномов высокой степени необходимо следить за тем, чтобы количество пар значений (х i ; y i ) превышало степень полинома хотя бы на несколько значений (для обеспечения точности аппроксимации). Кроме того, график функции полинома степени m имеет m-1 точку перегиба. Понятно, что точек данных должно быть гораздо больше, чем точек перегиба, чтобы такой изменчивый тренд стал очевидным (если утрировать, то бессмысленно строить по двум точкам параболу, логичнее построить прямую).
Как видно из расчетов, в MS EXCEL этот путь является достаточно трудоемким. Гораздо проще в MS EXCEL реализовать другой подход для вычисления коэффициентов полинома — с помощью замены переменных.
С помощью замены переменных x i =x i полиномиальную зависимость y=b 0 +b 1 x+b 2 x 2 +b 3 x 3 +… можно свести к линейной. Теперь переменная y зависит не от одной переменной х в m разных степенях, а от m независимых переменных x i . Поэтому для нахождения коэффициентов полинома мы можем использовать функцию ЛИНЕЙН() . Этот подход также продемонстрирован в файле примера .
Параметры линии тренда в Office
Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Excel для Microsoft 365 для Mac Word для Microsoft 365 для Mac PowerPoint для Microsoft 365 для Mac Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Excel 2021 для Mac Word 2021 для Mac PowerPoint 2021 для Mac Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2019 для Mac Word 2019 для Mac PowerPoint 2019 для Mac Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Еще. Меньше
В этом разделе рассматриваются различные параметры линии тренда, доступные в Office.
Прямая линия тренда
Используйте линию тренда этого типа для создания прямой линии, которая наилучшим образом описывает простой линейный набор данных. Она применяется в случаях, когда точки данных расположены близко к прямой. Иначе говоря, прямая линия тренда хорошо подходит для величины, которая возрастает или убывает с постоянной скоростью.
Для расчета точек методом наименьших квадратов прямая линия тренда использует следующее уравнение:
где m — это наклон, а b — смещение.
Следующая прямая линия тренда отображает стабильный рост продаж холодильников на протяжении 8 лет. Обратите внимание, что величина достоверности аппроксимации (число от 0 до 1, отображающее степень соответствия ожидаемых значений для линии тренда фактическим данным) равна 0,9792, что свидетельствует о хорошем совпадении расчетной линии с данными.
Логарифмическая линия тренда
Отображая оптимизированную кривую, эта линия тренда полезна для описания величины, которая вначале быстро растет или убывает, а затем постепенно стабилизируется. Логарифмическая линия тренда может использовать отрицательные и положительные значения данных.
Для расчета точек методом наименьших квадратов логарифмическая линия тренда использует следующее уравнение:
где c и b — константы и ln — функция натурального логарифма.
Следующая логарифмическая линия тренда показывает прогнозируемый рост популяции животных в области фиксированного пространства, где популяция выравнивалась по мере уменьшения пространства для животных. Обратите внимание, что значение в квадрате R равно 0,933, что относительно хорошо соответствует строке данных.
Полиномиальная линия тренда
Эта линия тренда полезна для описания величин, попеременно возрастающих и убывающих. Например, при анализе большого набора данных о нестабильной величине. Степень полинома определяется количеством экстремумов (максимумов и минимумов) кривой. Обычно полином второй степени имеет только один экстремум, полином третьей степени — один или два экстремума, а полином четвертой степени — до трех экстремумов.
Для расчета точек методом наименьших квадратов полиномиальная (или криволинейная) линия тренда использует следующее уравнение:
Приведенная ниже полиномиальная линия тренда второй степени (один максимум) отображает зависимость расхода топлива от скорости движения. Близкая к единице величина достоверности аппроксимации (0,979) свидетельствует о хорошем совпадении кривой с данными.
Степенная линия тренда
Отображая кривую, эта линия тренда полезна для отображения зависимости, которая содержится в данных, и характеризуется постоянной скоростью роста. Примером такой зависимости может служить ускорение гоночного автомобиля за каждый интервал времени, равный одной секунде. Если в данных имеются нулевые или отрицательные значения, использование степенной линии тренда невозможно.
Для расчета точек методом наименьших квадратов степенная линия тренда использует следующее уравнение:
где c и b — константы.
Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.
Приведенная ниже диаграмма измерения расстояний отображает зависимость пройденного расстояния от времени. Расстояние выражено в метрах, время — в секундах. Эти данные точно описываются степенной линией тренда, о чем свидетельствует величина достоверности аппроксимации, равная 0,986.
Экспоненциальная линия тренда
Отображая кривую, эта линия тренда полезна, если скорость изменения данных непрерывно возрастает. Однако для данных, которые содержат нулевые или отрицательные значения, экспоненциальная линия тренда неприменима.
Для расчета точек методом наименьших квадратов экспоненциальная линия тренда использует следующее уравнение:
где c и b — константы и e — основание натурального логарифма.
Приведенная ниже экспоненциальная линия тренда отображает содержание радиоактивного углерода-14 в зависимости от возраста органического объекта. Величина достоверности аппроксимации равна 0,990, что означает очень хорошее соответствие кривой данным.
Линии тренда с линейной фильтрацией
Эта линия тренда позволяет сгладить колебания данных и таким образом более наглядно отображает характер зависимости. Линейный фильтр строится по определенному числу точек данных (задается параметром Точки). Элементы данных усредняются, и полученный результат используется в качестве точки линии тренда. Так, если параметр Точки равен 2, первая точка линии тренда с линейной фильтрацией определяется как среднее значение первых двух элементов данных, вторая точка — как среднее второго и третьего элементов, и так далее.
Линия тренда с линейной фильтрацией использует такое уравнение:
Число точек в линии тренда с скользящее среднее равно общему числу точек ряда за вычетом числа, указанного для параметра «Точки».
В точечной диаграмме линия тренда базируется на порядке расположения значений X в диаграмме. Для получения оптимального результата перед добавлением линейной фильтрации отсортируйте значения X.
Приведенная ниже линия тренда с линейной фильтрацией отображает тенденцию числа продаж домов на протяжении 26 недель.
Важно: Начиная с Excel версии 2005, Excel корректировал способ вычисления значения R 2 для линейных линий тренда на диаграммах, где для перехвата линии тренда задано значение 0. Эта корректировка исправляет вычисления, которые дали неправильные значения R 2 , и выравнивает вычисление R 2 с функцией LINEST. В результате на диаграммах, созданных ранее в предыдущих версиях Excel, могут отображаться разные значения R 2 . Дополнительные сведения см. в разделе Изменения во внутренних вычислениях линейных линий тренда на диаграмме.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.