Печать
PDF

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

Автор: Алексей Батурин.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

polinom 6 stepeni

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

polinom na grafik

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

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

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

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

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

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

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

 y = 3,71x6 - 234,94x5 + 4 973,59x4 - 35 929,91x3 - 7 576,79x2 + 645 514,77x + 4 693 169,35

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

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

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

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

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

Рассчитаем значения полинома для каждого периода. Для этого вводим формулу полинома y = 3,71x6 - 234,94x5 + 4 973,59x4 - 35 929,91x3 - 7 576,79x2 + 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

=ЛИНЕЙН(R[-4]C:R[-4]C[24];R[-5]C:R[-5]C[24];1;0),

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

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

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

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

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

=ЛИНЕЙН(R[-4]C:R[-4]C[24]; R[-5]C:R[-5]C[24]^{1:2:3:4:5:6}; 1; 0)

Вводим формулу в ячейку, получаем 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 одним нажатием клавиши, легко и быстро.

Зарегистрируйтесь и протестируйте Forecast4AC PROскачать программу для прогнозирования Forecast4AC PRO

график модель прогноза + трендграфик сезенностьграфик прогноз + границы прогноза + трендграфик прогноз к предыдущим периодам

Forcast4AC Pro 4ddabd6bbee26 250x250Подписка "Прогноз с точностью 90% и выше!"

Присоединяясь к нам Вы получаете:

  • Более 10 рекомендаций по прогнозированию продаж!
  • Практические примеры!
  • Ссылку на Forecast4AC PRO - 40 дней бесплатно!
  • + советы по работе с программой!

Forecast4AC PRO - прогноз одним нажатием клавиши!

Статья полезная? Поделитесь с друзьями

Ваши комментарии и вопросы:

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


Защитный код
Обновить