Сервис Поиск решения (Solver)

Использование сервиса Поиск решения позволяет наглядно продемонстрировать суть метода наименьших квадратов (МНК). Вызывается он так же, как и Анализ данных: в Excel-2003 и более ранних версиях через меню Сервис (если не вызывается, то Сервис-Надстройки) ; в Excel-2007 и 2010 в меню Данные (если не вызывается, то Пуск – Параметры Excel – Надстройки – Перейти). Схема расчетов та же, что и в задачах математического программирования:

- задать произвольные коэффициенты аппроксимирующей функции f(X) ,

- построить функцию Y^= f(X)в заданном диапазоне Х,

- вычислить отклонения Y – Y^ для диапазона, в котором значения Yиспользуются для настройки модели, то есть оценки коэффициентов,

- вычислить все (Y – Y^)² и их сумму S(Y – Y^)² (сумма квадратов отклонений (остатков) ,

- вызвать Поиск решения, целевая ячейка S(Y – Y^)², Изменяя ячейки коэффициенты, ограничений нет, Выполнить.

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

Метод наименьших квадратов с Поиском решения может применяться для настройки нелинейных моделей. Его использование для настройки Модели 2 – параболической – показано в Таблице ….

Показатель качества модели – коэффициент корреляции Х и Y R_xy и его квадрат – коэффициент детерминации R².

Вычисленные для обеих моделей R², DW, GQ представлены в таблицах, а также показаны графики остатков. Видно, что качество обеих моделей высокое, применение МНК правомерно. Применение для прогноза одной из двух моделей зависит от дополнительной информации и личного опыта.