Использование линии тренда для нахождения коэффициентов нелинейной аппроксимирующей зависимости

В Microsoft Excel включены средства построения некоторых наиболее часто встречающихся типов нелинейной регрессии, таких как: логарифмической, полиномиальной, степенной и экспоненциальной регрессий, а также построение зависимости скользящего среднего.

Пример 3.2.

Менеджер по закупкам отдела «Книга-почтой» недавно разослал клиентам новый каталог, рекламирующий роман, получивший очень высокую оценку критиков. Менеджер считает, что следует заранее заказать дополнительное количество экземпляров, чтобы не оказаться в ситуации, когда книга закончится раньше, чем перестанут приходить заявки на нее. Менеджер начал отслеживать ежедневные заказы на роман, и регистрировать недельные объемы продаж. Результаты наблюдений приведены в таблице 3.2.

Требуется спрогнозировать необходимый менеджеру объем заказа на книгу на ближайшие 4 недели с помощью полиномиальной зависимости.

Таблица 3.2 - Недельные объемы продаж книг.

Решение:

1 В ячейки А2: В12 введём исходные данные (рисунок 3.6).

2 Построим точечную диаграмму зависимости объёмов продаж от номера недели.

3 Для получения линии тренда в контекстном меню одной из точек диаграммы выберем опцию «Добавить линию тренда…».

Рисунок 3.6 – Ввод исходных данных в таблицу Excel и отображение их на графике

4 В диалоговом окне «Линия тренда» на вкладке «Тип» выберем тип диаграммы (рисунок 3.3). Microsoft Excel позволяет построить линию тренда для следующих зависимостей:

• Линейная y = a*x + b;

• Логарифмическая y=a*ln(x)+b;

• Полиномиальная y = a*x2 + b*x + c (можно задать порядок) – выберем для данного примера;

• Степенная y = a*x^b;

• Экспоненциальная y = a*е^bх;

• Линейная фильтрация по заданному числу точек.

5 На вкладке «Параметры» можно задать название аппроксимирующей кривой.

Для получения уравнения регрессии на диаграмме активируем флажок «показывать уравнение на диаграмме» (рисунок 3.4).

При необходимости можно задать прогноз нужного количества значений.

6 По окончании выбора всех параметров щёлкаем кнопку ОК.

7 Полученные коэффициенты полиномиальной зависимости запишем соответственно в ячейки В18, В19 и В20 (рисунок 3.7).

8 В колонке С, используя найденные коэффициенты, получим значения для теоретической кривой с прогнозом на следующие 4 недели (рисунок 3.7).

Рисунок 3.7 – Результаты решения задачи аппроксимации полиномиальной
зависимостью

3.3.2 Нахождение коэффициентов нелинейной аппроксимирующей зависимости путём сведения её к линейной. Выбор лучшей аппроксимирующей зависимости.

Пусть известно, что полученные экспериментальные данные { x_i }, { y_i }, i=1, 2, …, n описываются нелинейной зависимостью общего вида:

y = f(x, a, b)

Задача состоит в нахождении параметров этой зависимости, т. е. в вычислении коэффициентов a и b.

Часто нелинейная зависимость путём элементарных математических преобразований может быть сведена к линейной. В этом случае для вычисления коэффициентов в Excel можно будет воспользоваться функциями НАКЛОН(…) и ОТРЕЗОК(…), рассмотренными ранее.