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

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

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

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

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

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

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

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

Зарегистрируйтесь и скачайте решения

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

 

Комментарии   

#32 +1 Ольга Куликова 08.06.2018 09:05
Благодарю! Интересно как )
Цитировать
#31 Анастасия. 16.01.2018 08:54
Цитирую Алексей:
Цитирую Анастасия.:
Добрый день. Бьюсь над регрессионным анализом в эксель. Подскажите, можно ли посчитать полином степени более чем 3 от нескольких факторов? (в вашем примере полином от одного фактора)

Анастасия, добрый вечер!
Можете на форум файлик опубликовать с данными
https://4analytics.ru/oglavlenie.html

Попробуем вместе разобраться!
Есть разные варианты, надо посмотреть на данные!



Добрый день, Алексей. Заскочила по ссылочке, но не могу найти, где там можно написать вопрос или прикрепить файл.
Цитировать
#30 Алексей Батурин 15.01.2018 14:54
Цитирую Анастасия.:
Добрый день. Бьюсь над регрессионным анализом в эксель. Подскажите, можно ли посчитать полином степени более чем 3 от нескольких факторов? (в вашем примере полином от одного фактора)

Анастасия, добрый вечер!
Можете на форум файлик опубликовать с данными
https://4analytics.ru/oglavlenie.html

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

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

Андрей, может у вас региональные настройки на это влияют. степень в стандартных настройках - это ":" - двоеточие! - смотрите изображения в статье и в файле пример.
Цитировать
#25 Алексей Батурин 04.05.2015 20: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} - в фигурных скобках задается степень полинома
Цитировать
#24 тимур 04.05.2015 19:53
А как выглядит формула для расчёта коэффициента полинома вручную?
Цитировать
#23 Алексей Батурин 04.05.2015 19:49
Цитирую тимур:
Здравствуйте,

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


Тимур, здравствуйте. Внизу статьи на этой странице в зеленом прямоугольнике можете скачать :-)
Цитировать

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