Модели (использование надстройки 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. Результат расчета с помощью программы «Поиск решения»
Значения коэффициентов уравнения регрессии и находятся в ячейках B14 и B15 соответственно.
Популярное: Личность ребенка как объект и субъект в образовательной технологии: В настоящее время в России идет становление новой системы образования, ориентированного на вхождение... Модели организации как закрытой, открытой, частично открытой системы: Закрытая система имеет жесткие фиксированные границы, ее действия относительно независимы... Как построить свою речь (словесное оформление):
При подготовке публичного выступления перед оратором возникает вопрос, как лучше словесно оформить свою... ©2015-2024 megaobuchalka.ru Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. (444)
|
Почему 1285321 студент выбрали МегаОбучалку... Система поиска информации Мобильная версия сайта Удобная навигация Нет шокирующей рекламы |