Для полного понимания этих отчетов и извлечения из них полезной информации требуются существенные знания методов оптимизации. Эта задача перед нами не ставится

Регрессия. Метод наименьших квадратов

В экономике часто возникает задача подбора функциональной зависимости для двух наборов данных. Независимые переменные X_i называют факторами, а зависимые Y_i - откликами. Функция Y=f(X) позволяет предсказывать значение отклика для факторов, не входящих в исходный набор данных.

Решим следующую задачу. Дан набор точек (X_i, Y_i), i=1, …, n. Пусть имеется класс функций (линейные, квадратичные, экспоненциальные функции и т.д.). Требуется найти функцию Y=f(X) из данного класса такую, чтобы ее значения f(X_i) приближали значения Y_i наилучшим образом.

В качестве критерия качества выбора функции часто принимают сумму квадратов отклонений величин f(X_i) от величин Y_i, и решают задачу минимизации этой суммы:

S(Y_i - f(X_i))² ® min.

ЗАДАЧА 2. Дан набор точек (X_i, Y_i), представленный в таблице. Найти коэффициенты m и b прямой линии Y=mX + b, наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов.

Решение. Решим задачу в Excel. Создайте новую рабочую книгу, сохраните ее под именем Задача2. Дайте первому листу имя Регрессия1.

В ячейки А1 и В1 впишем текстовые обозначения X и Y. Разместим координаты точек в диапазоне А2:В6, как показано ниже. В ячейки А8 и В8 впишем текстовые обозначения m и b. Ячейкам А9 и В9 дадим имена коэффициентов m и b, вписывая эти буквы в поле имени ячейки и нажимая затем клавишу Enter. В ячейках А9 и В9 поместим начальные значения коэффициентов m и b., равные нулю (=0 и клавиша Enter).

В ячейках С2:С6 будем вычислять Y_i =m X_i + b. Для этого в ячейку С2 впишем формулу =m*A2+b и нажмем клавишу Enter. Аналогично в С3 впишем формулу =m*A3+b и нажмем клавишу Enter. Аналогично для ячеек С4, С5, С6. Начиная с ячейки С3 операция вписывания формул можно проделать копированием содержимого ячейки С2 в ячейки С3 – С6 (кнопки Копировать и Вставить на панели инструментов). При этом программа Excel сама скорректирует формулы нужным образом, заменяя А2 на А3 и т.д.

В ячейках D2:D6 будем вычислять ошибки приближения Y_i – (m X_i + b). Для этого в D2 поместим формулу =В2-С2 (Enter). В в D3 поместим формулу =В3-С3 (Enter) и т.д. Начиная с ячейки D3 операция вписывания формул можно проделать копированием содержимого ячейки D2 в ячейки D3 – D6 (кнопки Копировать и Вставить на панели инструментов). При этом программа Excel сама скорректирует формулы нужным образом, заменяя В2 на В3 и С2 на С3 и т.д.