Модели (использование надстройки MS Excel «Поиск решения»)

Примечания 1:

- Определение коэффициентов уравнения регрессии можно представить как оптимизационный процесс по минимизации целевой функции методом линейного программирования [9]. Надстройка «Поиск решения» (рис. 2) позволяет итерационным методом определить коэффициенты любого уравнения регрессии, если в качестве целевой функции выбрать сумму квадратов остатков, то есть используя МНК. (Под остатком понимается разность между фактическим и расчетным значением зависимой переменной, то есть отклонение фактического значения отклика от его теоретического значения, определяемого в соответствии с предлагаемой регрессионной моделью).

Рис. 2. Диалоговое окно надстройки "Поиск решения"

- Надстройка "Поиск решения" работает с двумя группами ячеек: ячейками переменных решения, которые используются при расчете формул в целевых ячейках, и ячейками ограничения.

- Надстройка "Поиск решения" изменяет значения в ячейках переменных решения согласно пределам, заложенным в ячейках ограничения, и выводит результат в целевой ячейке.

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

2.1. Ввести в таблицу листа «Поиск решения» файла «однофакторный регрессионный анализ.xls» как показано на рис. 3 значения фактора Х (столбик B) и соответствующие значения отклика Y (столбик C) из таблицы листа «исходные данные», полученной Вами по п.п. 1.4.1.-1.4.3. (предварительно транспонировать их).

Рис. 3. Параметры программы «Поиск решения»

2.2. Назначить ячейки В14 и В15 соответственно для коэффициентов и уравнения линейной регрессии Y= + X. (Первоначально в этих ячейках значения должны отсутствовать - в дальнейшем в них будет внесен результат выполнения программы «Поиск решения».)

2.3. Ввести в диапазон ячеек D2:D11 результат расчетов по формуле Y= + X. В качестве значений и необходимо взять значения ячеек соответственно В14 и В15, значения X надо последовательно выбирать из массива B2:B11. Например, в ячейке D2 должна быть введена следующая формула =$В$14+$В$15*B2, в ячейке DЗ должна быть введена формула =$В$14+$В$15*B3 и т. д.

2.4. Ввести в массив ячеек E2:E11 квадраты отклонений. Квадраты отклонений вычисляются по формуле . Например, в ячейке E2 должна быть введена следующая формула: =(C2-D2)^2.

2.5. Ввести в ячейку E13 целевую функцию, которая будет равна сумме квадратов отклонений, т. е. сумме значений в ячейках массива E2:E11. То есть в ячейку E13 должна быть введена формула: =СУММ(E2:E11). Наэтом операции собственно линейного программирования можно считать законченными.

2.6. Выполнение программы «Поиск решения», осуществляющей перебор численных значений коэффициентов и с выбором таких, при которых значение целевой функции (в нашем случае – суммы квадратов отклонений) минимально.

2.6.1. Открыть надстройку «Поиск решения» (см. диалоговое окно на рис. 2), которая, как и надстройка «Пакет анализа», устанавливается по-разному в зависимости версии MS Excel и находится в меню «Сервис» или (в последних версиях MS Excel) в меню «Данные» [8]. (В Windows 2003 и Windows XP следует поставить соответствующую «галочку» в «Севис» - «Надстройки»; во всех других версиях MS Excel команду «Надстройки» следует искать в «Параметрах»).

2.6.2. Заполнить элементы диалогового окна «Поиск решения» в соответствии с рис. 3 и нажать кнопку «Выполнить», получив результат, аналогичный представленному в табл. 1.

Таблица 1.

Результат расчета с помощью программы «Поиск решения»

	A	B	C	D	E
	№ опыта	X	Y	Y расчётное	квадрат остатка
				13,08333112	1,173606316
				13,66666499	1,777782257
				14,83333272	3,361108864
				14,83333272	1,361112541
				16,00000045	0,999999092
				17,16666819	0,694441909
				18,33333592	5,444456522
				18,91666979	0,006943924
				20,08333752	0,006945143
				20,08333752	0,840270099
	коэффициенты		Целевая функция:	15,66666667
	B0	11,9167
	B1	0,58333

Значения коэффициентов уравнения регрессии и находятся в ячейках B14 и B15 соответственно.