Excel

Аппроксимация excel: Аппроксимация табличных функций в Excel

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

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

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

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

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

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

 

 

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

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

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

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

После этого уравнение выводится на график 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 в первую ячейку и фиксируем ссылки на коэффициенты тренда (см. 2+R7C8*RC[-3]+R8C8

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

Скачать файл с примером расчета значений полинома.

 

 

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

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

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

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

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

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

Теперь, чтобы формула Линейн() рассчитала коэффициенты полинома, нам в неё надо дописать степень полинома, коэффициенты которого мы хотим рассчитать. 2+R7C8*RC[-3]+R8C8

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

Скачать файл с примером расчета значений полинома.

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

 

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

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

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

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

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

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

Скачать файл с примером расчета значений полинома.

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

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

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

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

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

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

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

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

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

 

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

Для проведения расчетов, данные целесообразно расположить в виде таблицы 3, используя средства табличного процессора Microsoft Excel. 2*C2.

Шаг 12. В ячейки h4:h26 эта формула копируется.

Шаг 13. В ячейку I2 вводим формулу =LN(C2).

Шаг 14. В ячейки I3:I16 эта формула копируется.

Шаг 15. В ячейку J2 вводим формулу =B2*LN(C2).

Шаг 16. В ячейки J3:J16 эта формула копируется.

Последующие шаги делаем с помощью автосуммирования .

Шаг 17. В ячейку B17 вводим формулу =СУММ(B2:B16).

Шаг 18. В ячейку C17 вводим формулу =СУММ(C2:C16).

Шаг 19. В ячейку D17 вводим формулу =СУММ(D2:D16).

Шаг 20. В ячейку E17 вводим формулу =СУММ(E2:E16).

Шаг 21. В ячейку F17 вводим формулу =СУММ(F2:F16).

Шаг 22. В ячейку G17 вводим формулу =СУММ(G2:G16).

Шаг 23. В ячейку h27 вводим формулу =СУММ(h3:h26).

Шаг 24. В ячейку I17 вводим формулу =СУММ(I2:I16).

Шаг 25. В ячейку J17 вводим формулу =СУММ(J2:J16).

Аппроксимируем функцию линейной функцией .

Используя итоговые суммы таблицы 3, расположенные в ячейках B17,C17,D17 и E17, запишем систему в виде:

решив которую, получим и .

Таким образом, линейная аппроксимация имеет вид .

Решение системы проводили, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 4.

Результаты коэффициентов линейной аппроксимации.

Таблица 4.

В таблице 4 в ячейках A24:B25 записана формула {=МОБР(A20:B21)}.

В ячейках E24:E25 записана формула {=МУМНОЖ(A24:B25,C20:C21)}.

Далее аппроксимируем функцию квадратичной функцией .

Используя итоговые суммы таблицы 3, расположенные в ячейках B17, C17, D17, E17, F17, G17 и h27 запишем систему в виде

решив которую, получим, a1=4,5846265, a2=0,2044012 , a3=0,696981.

Таким образом, квадратичная аппроксимация имеет вид

.

Решение системы проводили, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 5.

Результаты коэффициентов квадратичной аппроксимации.

Таблица 5.

В таблице 5 в ячейках A33:C35 записана формула {=МОБР(A28:C30)}.

В ячейках F33:F35 записана формула {=МУМНОЖ(A33:C35,D28:D30)}.

Теперь аппроксимируем функцию экспоненциальной функцией . Для определения коэффициентов и прологарифмируем значения и используя итоговые суммы таблицы 3, расположенные в ячейках A16, C16, h26 и I16 получим систему

где c=ln(a

1)

Решив систему, найдем , .

После потенцирования получим .

Таким образом, экспоненциальная аппроксимация имеет вид

.

Решение системы проводили, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 6.

Функция ОКРУГЛ — служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно… Меньше

ОКРУГЛ возвращает число, округленное до нужного кратного.

Синтаксис

ОКРУГ (число, кратное)

Синтаксис функции ОКРУГЛ имеет следующие аргументы:

Замечания

  • ОКРУГЛ округляет в большую сторону от нуля, если остаток от деления числа на кратное больше или равен половине значения кратного.

  • Аргументы Number и Multiple должны иметь одинаковый знак. В противном случае возвращается ошибка #NUM.

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.

Формула

Описание

Результат

= КРУГЛЫЙ (10, 3)

Округляет 10 до ближайшего числа, кратного 3.

9

= КРУГЛЫЙ (-10, -3)

Округляет -10 до ближайшего кратного -3.

-9

= КРУГЛЫЙ (1,3; 0,2)

Округляет 1,3 до ближайшего кратного 0,2.

1,4

= КРУГЛЫЙ (5, -2)

Возвращает #ЧИСЛО! сообщение об ошибке, потому что -2 и 5 имеют разные знаки.

#ЧИСЛО!

Известные ограничения

Если для аргумента Multiple задано десятичное значение, направление округления не определено для средних чисел. Например, КРУГЛЫЙ(6.05,0.1) возвращает 6.0, а КРУГЛЫЙ(7.05,0.1) возвращает 7.1.

функция ОКРУГЛВНИЗ — служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно… Меньше

В этой статье описаны синтаксис формулы и использование функции ОКРУГЛВНИЗ в Microsoft Excel.

Описание

Округляет число в меньшую сторону до нуля.

Синтаксис

ОКРУГЛВНИЗ(число, число_разрядов)

Синтаксис функции ОКРУГЛВНИЗ имеет следующие аргументы:

Замечания

  • ОКРУГЛВНИЗ ведет себя как ОКРУГЛ, за исключением того, что всегда округляет число в меньшую сторону.

  • Если num_digits больше 0 (ноль), то число округляется до указанного количества знаков после запятой.

  • Если num_digits равно 0, то число округляется в меньшую сторону до ближайшего целого числа.

  • Если num_digits меньше 0, то число округляется в меньшую сторону слева от десятичной точки.

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.

Формула

Описание

Результат

= ОКРУГЛ ВНИЗ (3.2, 0)

Округляет 3.2 до нуля знаков после запятой.

3

= ОКРУГЛ ВНИЗ (76,9,0)

Округляет 76,9 до нуля знаков после запятой.

76

= ОКРУГЛ ВНИЗ (3.

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

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