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

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

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

1-й метод при помощи графика;
2-й метод при помощи функции Excel =ЛИНЕЙН();
3-й метод при помощи Forecast4AC PRO;

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

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

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

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

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

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

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

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

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

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

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

1-й метод при помощи графика;
2-й метод при помощи функции Excel =ЛИНЕЙН;
3-й метод при помощи Forecast4AC PRO;

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

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

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

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

Опосля этого уравнение выводится на график 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 =ЛИНЕЙН()

Интересно почитать: Что такое в excel функция впр

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

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

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

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

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

^ <1:2:3:4:5:6>— для расчета коэффициентов полинома 6-й степени
^ <1:2:3:4:5>— для расчета коэффициентов полинома 5-й степени
^ <1_2>— для расчета коэффициентов полинома 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 примыкающих ячеек справа, как на рисунке:

2. Надавить на кнопку F2

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

Получаем 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» возводим в подобающую степень (значок в 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-й метод поточнее, чем 1-ый, т.к. коэффициенты тренда мы получаем без округления, также этот расчет резвее.

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

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

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

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

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

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

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

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

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

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

Тестируйте способности платных решений:

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

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

Комбинаторика в Excel

Комбинаторика — раздел арифметики, изучающий дискретные объекты, огромного количества (сочетания, перестановки, размещения частей) и дела на их. Термин комбинаторика был введён в математический обиход Лейбницем, который в 1666 году опубликовал собственный труд «Рассуждения о комбинаторном искусстве». Excel поддерживает ряд функций комбинаторики. Чтоб разобраться, какую формулу употреблять, следует ответить на ряд вопросцев:

Начальное огромное количество содержит лишь неповторимые элементы, либо некие из их могут повторяться?
Операция производится со всеми элементами огромного количества, либо лишь с некой подборкой из их?
Важен ли порядок частей в выборке?
Опосля выбора элемента мы его возвращаем вспять?

Рис. 1. Дерево решений, какую формулу комбинаторики употреблять

Скачать заметку в формате Word либо pdf, примеры в формате Excel

Перестановки без повторений

Возьмем несколько разных частей (предметов) и будем переставлять их различными методами, оставляя постоянным их число и меняя лишь их порядок (рис. 2). Любая из получившихся таковым образом композиций носит заглавие перестановки. Перестановкой из n частей называется упорядоченное огромное количество, составленное из всех частей огромного количества.

Рис. 2. Перестановки (картина взята тут)

Если все n элементы различные, то число перестановок обозначается P_n от perturbation.

С иной стороны, произведение n первых натуральных чисел называется n-факториал и обозначается n!

По определению: 1! = 1; 0! = 1.

Функция в Excel =ФАКТР(n). Факториал вырастает весьма стремительно. Значительно резвее экспоненты (рис. 3).

Рис. 3. Расчет числа перестановок без повторений при помощи факториала

Перестановки с повторениями

Если в основном n огромном количестве не все элементы различные, то число перестановок будет меньше n! К примеру, если наше огромное количество состоит из 3-х яблок и одной груши, то всего может быть 4 перестановки (рис. 4). Груша быть может первой, 2-ой, третьей либо четвертой, а яблоки неразличимы).

Рис. 4. Перестановки с повторениями (картина найдена тут)

В общем случае, можно сказать: последовательность длины n, составленная из k различных знаков, 1-ый из которых повторяется n₁ раз, 2-ой – n₂ раз, 3-ий – n₃ раз, …, k-й – n_k раз (где n₁ + n₂ + … + n_k = n) называется перестановкой с повторениями из n частей.

Пример. Сколько разных пятибуквенных слов можно составить из букв слова «манна»?

Решение. Буковкы а и н повторяются 2 раза, а буковка м один раз.

Размещение без повторений

Размещением из n частей по m называется упорядоченный набор из m разных частей, избранных из n-элементного огромного количества (все элементы огромного количества неповторимы; позиции частей в выборке важны). Число размещений обозначается от arrangement.

К примеру, два элемента из 3-х можно избрать и расположить шестью методами (рис. 4):

Рис. 5. Размещение без повторений (картина из презентации)

Если m = n количество частей совпадает с количеством имеющихся мест для размещения. Знаменатель в формуле (4) преобразуется в 0! = 1. Остается лишь числитель n! А это – изученная выше перестановка без повторений; см. формулу (1).

Заглавие функции в Excel несколько обескураживает. Но… что поделаешь: =ПЕРЕСТ(n;m)

Рис. 6. Размещение без повторений; направьте внимание на смешанные ссылки, которые разрешают протянуть формулу на всю таблицу

Размещение с повторениями

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

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

Рис. 7. Размещение с повторениями

В общем случае размещение с повторениями либо подборка с возвращением – это размещение «предметов» в предположении, что любой «предмет» может участвовать в размещении пару раз. По правилу умножения количество размещений с повторениями из n по k:

В Excel употребляется функция ПЕРЕСТА(n;k).

Задачка. Сколько разных номеров можно составить в одном коде региона?

Подсказка. В номере употребляется 12 букв алфавита, также имеющихся и в латинском алфавите (А, В, Е, К, М, Н, О, Р, С, Т, У, Х).

Решение. Можно пользоваться формулой для размещения с повторениями:

Каждую цифру можно избрать 10 методами, а всего цифр 3, при всем этом они могут повторяться, и их порядок важен. Каждую буковку можно избрать 12 методами, при всем этом буковкы могут повторяться, и их порядок важен.

Сочетания без повторений

Сочетаниями из n огромного количества по m частей именуются композиции, составленные из данных n частей по m частей, которые различаются хотя бы одним элементом (в сочетаниях не учитывается порядок частей).

К примеру, два элемента из 4 смешиваются 6 методами (порядок следования не важен):

Сочетания без повторений образуют известный треугольник Паскаля (рис. 10). В этом треугольнике на верхушке и по краям стоят единицы. Каждое число равно сумме 2-ух расположенных над ним чисел. Строчки треугольника симметричны относительно вертикальной оси. Числа в строчках, составляющие треугольник Паскаля, являются сочетаниями

где n – номер строчки, m – номер элемента в строке, начиная с нулевого. К примеру, в строке 7:

Рис. 10. Треугольник Паскаля; чтоб прирастить изображение кликните на нем правой клавишей мыши и изберите Открыть картину в новейшей вкладке

В Excel употребляется функция =ЧИСЛКОМБ(n;m).

Сочетания с повторениями

Сочетания с повторениями по смыслу похожи на размещение с повторениями – это подборки из огромного количества с возвращением избранного элемента вспять перед каждым новеньким выбором. При всем этом порядок в выборке не важен.

К примеру, два предмета из 4 можно избрать 10 методами, если опосля всякого выбора предмет ворачивается вспять (рис. 11).

В общем случае, число сочетаний с повторениями:

Для нашего примера с фруктами

В Excel для подсчета числа сочетаний с повторениями употребляется функция =ЧИСЛКОМБА(n;m). В нашем примере =ЧИСЛКОМБА(4;2) = 10.

1 комментарий для “Комбинаторика в Excel”

Спасибо огромное за понятное и приятное изложение данной для нас сложной темы! Не в 1-ый раз Ваши публикации выручают меня, это просто энциклопедия полезнейшего контента!