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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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      

Значения коэффициентов уравнения регрессии Модели (использование надстройки MS Excel «Поиск решения») - student2.ru и Модели (использование надстройки MS Excel «Поиск решения») - student2.ru находятся в ячейках B14 и B15 соответственно.

Наши рекомендации