Как построить полином в excel
БлогNot. Excel: как построить степенной полином функцией ЛИНЕЙН
Excel: как построить степенной полином функцией ЛИНЕЙН
Сейчас мы хотим, во-первых, построить в Excel интерполирующий полином тоже стандартной функцией, во-вторых, не вдаваясь в детали теории, понять смысл этой простой задачи — как построить кривую, проходящую через несколько известных точек на плоскости.
Итак, по известному набору из N значений функции f(xi)=yi , заданному парой векторов xi, yi=f(xi) , i=1, 2, . N , нужно построить кривую, проходящую через все точки.
Через N различных между собой по оси x точек всегда можно построить кривую, зависящую от x N-1 , её уравнение будет иметь общий вид
В этом уравнении нам неизвестны коэффициенты сi . Из условия, что кривая проходит через все заданные в постановке задачи точки, можно записать систему линейных алгебраических уравнений:
или, в матричном виде
Система линейных алгебраических уравнений, записанная в матричном виде
Решив эту систему уравнений, то есть, найдя обратную к матрице Вандермонда матрицу и умножив её на вектор y , найдём коэффициенты сi . Теперь, подставив их в уравнение (1), мы можем аналитически оценить значение функции в произвольной точке x .
Ниже показано «ручное» решение в Excel и решение с помощью стандартной функции ЛИНЕЙН.
Скриншот файла Excel с решением
- C2 — формируем матрицу из степеней значений x ; избегаем при этом возведения нуля в нулевую степень, заменяя любое число, возводимое в нулевую степень, единицей; ввести формулу в ячейку C2 ; затем растягиваем формулу на ячейки C2:C5 , отпускаем левую кнопку мыши и, не снимая выделения, растягиваем на столбцы D:F (см. Пояснение 1 ниже);
- G2:G5 — вычисляем коэффициенты полинома ci «вручную», обратив матрицу и умножив её на вектор значений yi ; выделить диапазон G2:G5 ; не снимая выделения, ввести формулу в ячейку G2 ; не снимая выделения, нажать комбинацию клавиш Crl+Shift+Enter (см. Пояснение 2 ниже);
- I2 — вычисляем полином третьей степени в точках, не обязательно совпадающих с исходными; по выделенным жирным шрифтом значениям полинома видно, что он прошёл через исходные точки; ввести формулу в ячейку I2 , растянуть за уголок до I8 ;
- J2:J5 — вычисляем коэффициенты полинома ci с помощью функции ЛИНЕЙН , пример в справке (пример 2), к сожалению, прямо ошибочен, плюс не показывает вычисление нескольких коэффициентов полинома; выделить диапазон J2:J5 ; не снимая выделения, ввести формулу в ячейку G2 ; не снимая выделения, нажать комбинацию клавиш Crl+Shift+Enter ; коэффициенты возвращаются в «перевёрнутом» по отношению к нашему ручному расчёту виде;
- K2 — для единообразия расчёта переворачиваем массив коэффициентов, готовой функции для этого нет, показан образец, как перевернуть диапазон в Excel; ввести формулу в ячейку K2 , растянуть за уголок до K5 ;
- L2 — вычисляем полином третьей степени в тех же точках H2:H8 , в которых вычисляли его значения первым способом; ввести формулу в ячейку L2 , растянуть за уголок до L8 ; видно, что кривая также прошла через исходные точки данных.
Пояснение 1. Как растянуть формулу на матрицу значений
1. Введите требуемую формулу и нажмите Enter , на рисунке показан вид экрана перед нажатием:
Ввод "матричной" формулы со смешанными ссылками
2. Подведите курсор мыши к нижнему правому уголку ячейки C2 , уголок превратился в чёрный крестик, зажмите левую кнопку мыши и растяните формулу вниз до ячейки C5 .
Курсор для растягивания в Excel, "чёрный крестик"
Формула растянута вниз
3. Отпустите кнопку мыши, снова так же подведите курсор к уголку ячейки C5 (опять чёрный крестик) и при зажатой левой кнопке мыши растяните выделение вправо до столбца F .
Заполнение таблицы формулой в Excel
Пояснение 2. Как ввести формулу массива
1. Выделить диапазон ячеек, в которые будет помещён результат матричной или векторной операции (мышкой при зажатой левой кнопке за любое место, на котором курсор имеет вид по умолчанию или при зажатой Shift клавишами со стрелками):
Вид курсора по умолчанию в Excel
Мы сами отвечаем за правильность выделения ячеек диапазона результата, например, Excel не обязан знать, что в результате обращения матрицы размерностью 3×3 получится тоже матрица размерностью 3×3 :
Выделение диапазона ячеек результата в Excel
2. Не снимая выделения, ввести формулу массива в первую ячейку выделенного диапазона, это можно сделать «вручную», просто нажав клавишу F2 и начав набирать формулу со знака » = «, или с помощью Мастера Функций (см. п.3 документа по Excel здесь).
Ввод формулы массива в первую ячейку выделенного диапазона
3. При зажатых клавишах Ctrl и Shift , нажать клавишу Enter , то есть, ввести комбинацию клавиш Ctrl+Shift+Enter .
МНК: Приближение полиномом в 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 . Поэтому для нахождения коэффициентов полинома мы можем использовать функцию ЛИНЕЙН() . Этот подход также продемонстрирован в файле примера .
Найти корни уравнения в Excel
Отметим, что у полинома третьей степени имеется не более трех вещественных корней. Для нахождения корней их предварительно надо локализовать. С этой целью необходимо полином протабулировать. Построим таблицу значений полинома на отрезке [—1,1] с шагом 0,2 и график полинома. Результат приведен на рис. 29, где в ячейку В2 введена формула
=A2^3 — 0,01*A2^2 — 0,7044*A2 + 0,139104.
На рис. 29 видно, что полином меняет знак на интервалах [—1,—0,8], [0,2, 0,4] и [0,6, 0,8]. Это означает, что на каждом из них имеется корень данного полинома. Поскольку полином третьей степени имеет не более трех действительных корней, мы локализовали все его корни.
Найдем корни полинома методом последовательных приближений с помощью команды Сервис / Подбор параметров. Относительная погрешность вычислений и предельное число итераций задаются на вкладке Вычисления диалогового окна Параметры, открываемого командой Сервис / Параметры (рис. 30).
Зададим относительную погрешность и предельное число итераций равными 0,00001 и 1000 соответственно. В качестве начальных приближений к корням можно взять любые точки из отрезков локализации корней. Возьмем в качестве начальных приближений их средние точки: —0.9, 0.3, 0,7 и введем их в диапазон ячеек С2:С4. В ячейку D2 введем формулу
=C2^3 — 0,01*C2^2 — 0,7044*C2 + 0,139104.
Выделим эту ячейку и с помощью маркера заполнения протащим введенную в нее формулу на диапазон D2:D4. В ячейках D2:D4 будут вычислены значения полинома при значениях аргумента, введенных в ячейки C2:C4.
Теперь выберем команду Сервис / Подбор параметров и заполним диалоговое окно Подбор параметров следующим образом (рис. 31).
В поле Установить в ячейке введем D2. В этом поле дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения. В поле Значение введем 0 (в этом поле указывается правая часть уравнения). В поле Изменяя значение ячейки введем С2 (в этом поле дается ссылка на ячейку, отведенную под переменную).
Вводить ссылки на ячейки в поля диалогового окна Подбор параметров удобнее не с клавиатуры, а щелчком на соответствующей ячейке. При этом Excel автоматически будет превращать их в абсолютные ссылки (в нашем примере в $D$2 и $C$2).
После нажатия кнопки ОК средство подбора параметров находит приближенное значение корня, которое помещается в ячейку С2. В данном случае оно равно —0,920. Аналогично в ячейках С3 и С4 находим два оставшихся корня. Они равны 0,210 и 0,720.