Как построить полином в excel
Перейти к содержимому

Как построить полином в excel

  • автор:

Как построить полином в 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 с решением

Скриншот файла 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,

Курсор для растягивания в Excel, «чёрный крестик»

Формула растянута вниз

Формула растянута вниз

3. Отпустите кнопку мыши, снова так же подведите курсор к уголку ячейки C5 (опять чёрный крестик) и при зажатой левой кнопке мыши растяните выделение вправо до столбца F .

Заполнение таблицы формулой в Excel

Заполнение таблицы формулой в Excel

Пояснение 2. Как ввести формулу массива

1. Выделить диапазон ячеек, в которые будет помещён результат матричной или векторной операции (мышкой при зажатой левой кнопке за любое место, на котором курсор имеет вид по умолчанию или при зажатой Shift клавишами со стрелками):

Вид курсора по умолчанию в Excel

Вид курсора по умолчанию в Excel

Мы сами отвечаем за правильность выделения ячеек диапазона результата, например, Excel не обязан знать, что в результате обращения матрицы размерностью 3×3 получится тоже матрица размерностью 3×3 :

Выделение диапазона ячеек результата в Excel

Выделение диапазона ячеек результата в Excel

2. Не снимая выделения, ввести формулу массива в первую ячейку выделенного диапазона, это можно сделать «вручную», просто нажав клавишу F2 и начав набирать формулу со знака » = «, или с помощью Мастера Функций (см. п.3 документа по Excel здесь).

Ввод формулы массива в первую ячейку выделенного диапазона

Ввод формулы массива в первую ячейку выделенного диапазона

3. При зажатых клавишах Ctrl и Shift , нажать клавишу Enter , то есть, ввести комбинацию клавиш Ctrl+Shift+Enter .

22.02.2020, 18:58 [4949 просмотров]

Как построить полиномиальную кривую в Excel (шаг за шагом)

Как построить полиномиальную кривую в Excel (шаг за шагом)

Вы можете использовать функцию ЛИНЕЙН() в Excel, чтобы подобрать полиномиальную кривую с определенной степенью.

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

=LINEST( known_ys , known_xs ^) 

Функция возвращает массив коэффициентов, описывающих полиномиальную подгонку.

В следующем пошаговом примере показано, как использовать эту функцию для подбора полиномиальной кривой в Excel.

Шаг 1: Создайте данные

Во-первых, давайте создадим некоторые данные для работы:

Шаг 2: Подберите полиномиальную кривую

Далее воспользуемся функцией ЛИНЕЙН() , чтобы подобрать полиномиальную кривую степени 3 к набору данных:

Полиномиальная подгонка Excel

Шаг 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 . Поэтому для нахождения коэффициентов полинома мы можем использовать функцию ЛИНЕЙН() . Этот подход также продемонстрирован в файле примера .

3 способа расчета полинома в Excel.

полином

Есть 3 способа расчета значений полинома в Excel:

  • 1-й способ с помощью графика;
  • 2-й способ с помощью функции Excel =ЛИНЕЙН();
  • 3-й способ с помощью Forecast4AC PRO;

Подробнее о полиноме и способе его расчета в Excel далее в нашей статье.

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

Что такое полином? Полином — это степенная функция y=ax 2 +bx+c (полином второй степени) и y=ax 3 +bx 2 +cx+d (полином третей степени) и т.д. Степень полинома определяет количество экстремумов (пиков), т.е. максимальных и минимальных значений на анализируемом промежутке времени.

У полинома второй степени y=ax 2 +bx+c один экстремум (на графике ниже 1 максимум).

один экстремум

У Полинома третьей степени y=ax 3 +bx 2 +cx+d может быть один или два экстремума.

Один экстремум

один экстремум полинома

Два экстремума

2 экстремума полинома третьей степени

У Полинома четвертой степени не более трех экстремумов и т.д.

Как рассчитать значения полинома в Excel?

Есть 3 способа расчета значений полинома в Excel:

  • 1-й способ с помощью графика;
  • 2-й способ с помощью функции Excel =ЛИНЕЙН;
  • 3-й способ с помощью Forecast4AC PRO;

1-й способ расчета полинома — с помощью графика

Выделяем ряд со значениями и строим график временного ряда.

график полинома

На график добавляем полином 6-й степени.

добавляем линию тренда в Excel

polinom 6 stepeni

Затем в формате линии тренда ставим галочку «показать уравнение на диаграмме»

polinom na grafik

После этого уравнение выводится на график y = 3,7066x 6 — 234,94x 5 + 4973,6x 4 — 35930x 3 — 7576,8x 2 + 645515x + 5E+06 . Для того чтобы последний коэффициент сделать читаемым, мы зажимаем левую кнопку мыши и выделяем уравнение полинома

выделяем уравнение тренда

Нажимаем правой кнопкой и выбираем «формат подписи линии тренда»

формат подписи полинома

В настройках подписи линии тренда выбираем число и в числовых форматах выбираем «Числовой».

формат подписи полинома

Получаем уравнение полинома в читаемом формате:

y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35

уравнение полинома

Из этого уравнения берем коэффициенты a, b, c, d, g, m, v, и вводим в соответствующие ячейки Excel

коэффициенты полинома

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение вместо X.

номер временного ряда для полинома

Рассчитаем значения полинома для каждого периода. Для этого вводим формулу полинома y = 3,71x 6 — 234,94x 5 + 4 973,59x 4 — 35 929,91x 3 — 7 576,79x 2 + 645 514,77x + 4 693 169,35 в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

вводим формулу полинома в ячейку

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

Теперь протягиваем формулу до конца временного ряда и получаем рассчитанные значения полиномиального тренда для каждого периода.

2-й способ расчета полинома в Excel — функция ЛИНЕЙН()

Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel =ЛИНЕЙН()

Для расчета коэффициентов в формулу =ЛИНЕЙН(известные значения y, известные значения x, константа, статистика) вводим:

  • «известные значения y» (объёмы продаж за периоды),
  • «известные значения x» (порядковый номер временного ряда),
  • в константу ставим «1»,
  • в статистику «0»

Получаем следующего вида формулу:

Линейн формула Excel

Теперь, чтобы формула Линейн() рассчитала коэффициенты полинома, нам в неё надо дописать степень полинома, коэффициенты которого мы хотим рассчитать.

Для этого в часть формулы с «известными значениями x» вписываем степень полинома:

  • ^ — для расчета коэффициентов полинома 6-й степени
  • ^ — для расчета коэффициентов полинома 5-й степени
  • ^ — для расчета коэффициентов полинома 2-й степени

вводим степень полинома

Получаем формулу следующего вида:

Вводим формулу в ячейку, получаем 3,71 —- значение (a) для полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v

Для того, чтобы Excel рассчитал все 7 коэффициентов полинома 6-й степени y=ax^6+bx^5+cx^4+dx^3+gx^2+mx+v, необходимо:

1. Установить курсор в ячейку с формулой и выделить 7 соседних ячеек справа, как на рисунке:

ustanovit kursor

2. Нажать на клавишу F2

uravnenie polinoma 6stepeni 2sposob

3. Затем одновременно — клавиши CTRL + SHIFT + ВВОД (т.е. ввести формулу массива, как это сделать читайте подробно в статье «Как ввести формулу массива»)

uravnenie polinoma 6stepeni 2sposob

Получаем 7 коэффициентов полиномиального тренда 6-й степени.

Рассчитаем значения полиномиального тренда с помощью полученных коэффициентов. Подставляем в уравнение y=3,7* x ^ 6 -234,9* x ^ 5 +4973,5* x ^ 4 -35929,9 * x^3 -7576,7 * x^2 +645514,7* x +4693169,3 номера периодов X, для которых хотим рассчитать значения полинома.

Каждому периоду во временном ряду присваиваем порядковый номер, который будем подставлять в уравнение полинома вместо X.

номер временного ряда для полинома

Рассчитаем значения полиномиального тренда для каждого периода. Для этого вводим формулу полинома в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. статью как зафиксировать ссылки)

вводим формулу полинома в ячейку

Получаем формулу следующего вида:

= R2C8 *RC[-3]^6+ R3C8 *RC[-3]^5+ R4C8 *RC[-3]^4+ R5C8 *RC[-3]^3+ R6C8 *RC[-3]^2+ R7C8 *RC[-3]+ R8C8

в которой коэффициенты тренда зафиксированы и вместо «x» мы подставляем ссылку на номер текущего временного ряда (для первого значение 1, для второго 2 и т.д.)

Также «X» возводим в соответствующую степень (значок в Excel «^» означает возведение в степень)

=R2C8*RC[-3] ^6 +R3C8*RC[-3] ^5 +R4C8*RC[-3] ^4 +R5C8*RC[-3] ^3 +R6C8*RC[-3] ^2 +R7C8*RC[-3]+R8C8

Теперь протягиваем формулу до конца временного ряда и получаем рассчитанные значения полиномиального тренда для каждого периода.

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

3-й способ расчета значений полиномиальных трендов — Forecast4AC PRO

Устанавливаем курсор в начало временного ряда

уравнение полинома

Заходим в настройки Forecast4AC PRO, выбираем «Прогноз с ростом и сезонностью», «Полином 6-й степени», нажимаем кнопку «Рассчитать».

функция полинома

Заходим в лист с пошаговым расчетом «ForPol6», находим строку «Сложившийся тренд»:

копируем полином

Копируем значения в наш лист.

Получаем значения полинома 6-й степени, рассчитанные 3 способами с помощью:

  1. Коэффициентов полиномиального тренда выведенных на график;
  2. Коэффициентов полинома рассчитанных с помощью функцию Excel =ЛИНЕЙН
  3. и с помощью Forecast4AC PRO одним нажатием клавиши, легко и быстро.

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *