Печать
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 - прогноз одним нажатием клавиши!

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

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

Comments  

 
#28 Алексей Батурин 19.04.2016 11:36
Цитирую futurama3000:
добрый день! значения полиномиального ряда можно получить сразу с помощью функции ТЕНДЕНЦИЯ. В ней к известным значениям Х и к новым значениям Х аналогичным образом добавляется степень полинома ^{1:2:3:4:5:6}.

Иван, спасибо большое за комментарий. - это 4-й способ и очень удобный, единственное формулу надо вводить, как формулу массива.
Quote
 
 
#27 futurama3000 19.04.2016 11:24
добрый день! значения полиномиального ряда можно получить сразу с помощью функции ТЕНДЕНЦИЯ. В ней к известным значениям Х и к новым значениям Х аналогичным образом добавляется степень полинома ^{1:2:3:4:5:6}.
Quote
 
 
#26 Алексей Батурин 20.07.2015 20:27
Цитирую Андрей:
Обращаю внимание, что в массиве с указанием степени полинома разделителем является точка с запятой, а не двоеточие.

Андрей, может у вас региональные настройки на это влияют. степень в стандартных настройках - это ":" - двоеточие! - смотрите изображения в статье и в файле пример.
Quote
 
 
#25 Алексей Батурин 04.05.2015 23:12
Цитирую тимур:
А как выглядит формула для расчёта коэффициента полинома вручную?


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

^{1:2:3:4:5:6} - в фигурных скобках задается степень полинома
Quote
 
 
#24 тимур 04.05.2015 22:53
А как выглядит формула для расчёта коэффициента полинома вручную?
Quote
 
 
#23 Алексей Батурин 04.05.2015 22:49
Цитирую тимур:
Здравствуйте,

а помните у вас по каждой из тем есть шаблон Excel с готовыми формулами. Для полиномов имеется такой?


Тимур, здравствуйте. Внизу статьи на этой странице в зеленом прямоугольнике можете скачать :-)
Quote
 
 
+1 #22 тимур 04.05.2015 22:33
Здравствуйте,

а помните у вас по каждой из тем есть шаблон Excel с готовыми формулами. Для полиномов имеется такой?
Quote
 
 
#21 Алексей Батурин 21.01.2015 22:28
Ярослав,
вы мастер находить ошибки :lol: , это у нас еще одна "старая ошибка" старой версии программы (2 или 3, сейчас 4 версия), тогда считали дисперсию относительно тренда, т.к. в литературе предлагается считать относительно средней, потом перешли на расчет относительно модели, но разница не велика.

В этой статье дисперсия к теме не относится, так и болтается, надо удалить .

Кстати, описание, как рассчитывается дисперсия относительно модели можно найти в этой статье
http://www.4analytics.ru/prognozirovanie/kak-rasschitat-doveritelniie-interval-v-excel.-pravilo-trex-sigm-primenenie-na-praktike.html

Спасибо за комментарии!
Quote
 
 
#20 Ярослав 21.01.2015 07:42
Алексей, спасибо за ответ.
По п. 2 я так понял, что дисперсия в этом случае вычисляется по формуле ((факт. - тренд)^2/24)^0, 5, где 24 - это количество мес. = n. А почему в данном случае используется эта формула, а не та, которая была в примере расчета сигмы
КОРЕНЬ((Сумма(Xi-Ximod)^2/(n-1)).
Спасибо
Quote
 
 
#19 Алексей Батурин 20.01.2015 22:34
Цитирую Ярослав:
Здравствуйте, Алексей

1. А почему при расчете прогноза периодов 26, 27 берется тренд 2,3 месяцев, получается цикл повторяется и прогнозные значения 26,27 периода равны 2,3? Т.е. полином используется для расчета попеременно возрастающих и убывающих временных рядов, но не учитывает рост от сезона к следующему сезону?
2. Как рассчитывается дисперсия во вкладке расчета 26,27 периода. У меня получаются почему-то другие цифры.
3. И СКО не совпадает по формуле (ошибка модели)^2/факт. значение^2. Или используется другая формула?
Спасибо


Ярослав, здравствуйте.

1. Полиномы используются как описательная функция для стабильных рядов без роста или падения, можно взять ср. полином за несколько циклов и использовать усредненный полином для прогноза. Рост полиномами правильно не учтешь, можете попробовать продлить полином вперед, он или завалится или будет резко возрастать.
Также полиномы можно использовать для оценки поведения данных ряда по годам. Для каждого года строим свой полином и исключаем года, в которых функция ведет себя не в соответствии с текущей или последними годами.

2. выложите на форум свой расчет, скажу в чем отличия. Проверил в файле правильно.

3. Да, в используется другая формула (ошибка модели)^2/(знач ение модели)^2., она не правильная - но не критичная. Пару лет назад эта ошибка была в алгоритме программы, но она давно исправлена, но вот в статье осталась.
Ваша формула правильная (ошибка модели)^2/факт. значение^2

Спасибо!
Quote
 

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


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