Общий подход к построению уравнения регрессии на примере линейной модели.

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

Рис. 4.1. Нахождение коэффициентов линейной регрессии

Необходимо построить линейную модель y=mx+b, наилучшим образом описывающею наблюдаемое значение.Обычно m и b подбирают так, чтобы минимизировать суммусцмму квадратов разностей между наблюдаемыми и теоретиескими знаениями переменной у.

Для решения этой задачи отведем под переменные m и b ячейки D3, E3 соответственно, а в ячейку F3 введем минимизируемую функцію

{= СУММКВРАЗН(В2: В7; Е3+D3*А2: А7)}

Эта функция вычисляет сумму квадратов разностей для элементов указанных массивов.

Теперь выберем команду Сервис, Поиск решений и заполним открывшееся диалоговое окно Поиск решений как показано на рис.

Рис. 4.2. Диалоговое окно Поиск решений

Функция рабочего листа для уравнения линейной регрессии.

Отметим, что на переменные m и b ограничения не налагаются. В результате вычислений средство поиска решений найдет m = 1.885714 и b =5, 400.

Параметры m и b линейной модели y=mx+b из предыдущего примера можно определить с помощью функции НАКЛОН и ОТРЕЗОК. Первая функция определяет коэффициент наклона линейного тренда. Синтаксис функции:

НАКЛОН ( известные_значения_у; известные_значения_х)

Функция ОТРЕЗОК определяет току пересеения линии линейного тренда с осью ординат. Синтаксис:

ОТРЕЗОК (известные_значения_х; известные_значения_у).

Рис. 4.3.Линия линейного тренда

В ячейках D2 и Е2 соотвктственно найдены m и b по формулам:

= НАКЛОН( В2: В7; А2: А7)

= ОТРЕЗОК( В2: В7; А2: А7).