Поиск решения. В занятии 4 мы рассматривали пример автоматического нахождения функциональной зависимости Y = f(X)

В занятии 4 мы рассматривали пример автоматического нахождения функциональной зависимости Y = f (X). Напомним, что нахождение подобной зависимости необходимо для предсказания значений отклика – параметра Y на выходе эксперимента от фактора – независимых переменных X на входе в систему.

В некоторых случаях представленных в Excel функций бывает недостаточно. Поэтому важно уметь подобрать такую функцию самостоятельно, используя какой-нибудь из математических методов оптимизации, например, метод наименьших квадратов. Суть его состоит в том, чтобы минимизировать сумму квадрата разности экспериментальных (Y_экпер) и расчетных (Y_расчет) данных:

, (3)

где n в нашей задаче было равно 10.

Рис.106. Рабочий лист вычислений

Откройте задачу занятия 4 и продолжите заполнение таблицы. Экспериментальные Y уже введены. Теперь заполним таблицу расчетными Y.

Для этого нам понадобится дополнительная таблица коэффициентов, значения которых мы для начала приравняем к 1 (рис.106).

Теперь введите формулу полинома второй степени (1) для Y_расчет (Рис.106).

Далее задача заключается в том, чтобы подобрать коэффициенты уравнения таким образом, чтобы разница между Y_расчет и Y_экспер была минимальной. Для этого нужно ввести формулу расчета квадрата разности (3) и формулу расчета критерия Пирсона для оценки точности нашего расчета (Рис.107). И та и другая формулы являются встроенными в Excel и являются примером функций, для которых можно обойтись без ввода табличных формул (Занятие 5).

Откройте Мастер функций любым известным вам способом.В категории «Математические» выберите формулу СУММКВРАЗН и нажмите Ok. Во втором окне Мастера функций в качестве массива_x введите массив Y_экспер, в качестве массива_y – массив Y_расчет и нажмите Ok.

Формула для расчета критерия Пирсона находится в категории «Статистические» (функция ПИРСОН). Во втором окне Мастера функций в качестве массива_x также введите массив Y_экспер, в качестве массива_y – массив Y_расчет и нажмите Ok.

Рис.107. Ввод формул

Чтобы найти значения коэффициентов, в Excel имеется надстройка «Поиск решения», которая позволяет решать задачи отыскания наибольших и наименьших значений, а также решать различные уравнения.

Рис.108. Включение опции Поиск решения

Выделите ячейку, где введена формула расчета квадрата разности и выполните команду Сервис Þ Поиск решения. Если в меню Сервис такая команда отсутствует, то нужно сначала выполнить команду Сервис Þ Надстройки и в открывшемся диалоговом окне поставить переключатель в графе Поиск решения (Рис.108), и только затем выполнить команду Сервис Þ Поиск решения.

В диалоговом окне Поиск решения (Рис.109) введите параметры:

Рис.109. Диалоговое окно Поиск решения

ü адрес целевой ячейки с подбираемым значением (адрес ячейки с формулой суммы квадрата разности), если вы заранее выделили ее, то адрес помещается автоматически;

ü в поле «Равной» установите переключатель на «минимальному значению»;

ü в поле Изменяя ячейки введите диапазон ячеек изменяемых коэффициентов.

Кнопка Параметры служит для изменения и настройки параметров поиска. В их число входят: способ решения задачи, время проведения вычислений и точность результатов. Однако в большинстве случаев достаточно использовать настройки по умолчанию. Поиск решения осуществляется после щелчка по кнопке Выполнить.

Рис.111. Диалоговое окно Исходные данные

Если поиск решения успешно завершен, то результаты вычислений заносятся в исходную таблицу, а на экране появляется диалоговое окно Результаты поиска решения (Рис.110), с помощью которого можно сохранить найденные решения в исходной таблице, восстановить исходные значения, сохранить результаты поиска решения в виде сценария, сформировать отчет по результатам выполнения операции поиска решения.

Рис.110. Диалоговое окно Результаты поиска решения

Сравните полученные значения коэффициентов с коэффициентами в уравнении линии тренда.

Добавьте расчетные значения Y на график. Для этого перейдите в окно диаграммы, щелкните правой кнопкой мыши в любом ее месте и выберите в контекстном меню команду Исходные данные. В открывшемся одноименном диалоговом окне (Рис.111) перейдите на закладку Ряд и нажмите кнопку Добавить. В поле Имя щелкните по кнопке свертывания окна , перейдите на лист с вашими данными, выделите ячейку заголовка столбцу Y_расчет и вернитесь в окно с помощью кнопки разворачивания окна . Аналогичным образом введите Значения X (диапазон ячеек со значениями X или температуры) и Значения Y (диапазон ячеек со значениями расчетного Y). По окончании ввода нажмите кнопку Ok. Обратите внимание, что точки Y_расчет легли на линию тренда, построенную нами ранее (Рис.112).

Рис.112. График результата вычислений

В завершение обязательно сохраните свой файл, мы будем его использовать на следующем занятии 7.