2.3. Технология работы с надстройкой Поиск решения
Установить курсор на ячейку, в которую записана формула целевой функции.
Выбрать в меню Сервис Поиск решения.
Откроется диалоговое окно Поиск решения (Рис.4).
Рис.4. Диалоговое окно «Поиск решения»
Переключатель «Равной» установить в положение «минимальному значению».
Ввести диапазон изменяемых ячеек a2:b2, которые выделены под значения определяемых параметров .
Щелкнуть на кнопке Параметры диалогового окна Поиск решения. Откроется диалоговое окно Параметры поиска решения (Рис.5).
В полях Линейная модель и Неотрицательные значения не должно быть флажков.
Щелкнуть на кнопке ОК. Откроется диалоговое окно Поиск решения.
Щелкнуть на кнопке Выполнить диалогового окна Поиск решения. После окончания расчёта появится диалоговое окно Результаты поиска решения (Рис.6).
Щелкнуть на кнопке ОК, если требуется сохранить найденное решение.
Щелкнуть в поле Восстановить исходные значения, если не устраивает по каким-либо причинам полученное решение или требуется перейти к решению другой задачи.
Рис. 5. Диалоговое окно “Параметры поиска решения”
Рис.6. Диалоговое окно “Результаты поиска решения”
Полученные значения параметров аппроксимирующей функции запишутся в ячейки A2:B2, значения аппроксимирующей функции - в ячейки C4:C10, значение целевой функции - в ячейку D2. Следовательно, аппроксимирующая функция имеет вид:
(6)
Среднеквадратичная погрешность
значительно меньше значений абсолютных
частот статистического ряда, т.е. можно
считать, что аппроксимирующая функция
подобрана удачно.
2.4. Технология подбора теоретической кривой путем построения линий тренда
Построить с помощью мастера диаграмм точечную диаграмму по данным статистического ряда, размещенного в диапазоне ячеек А3:В10 таблицы 5, в виде функции
.
Щелкнуть мышью на любой точке диаграммы. Все точки выделятся квадратиками (рис.7).
Войти в меню Диаграмма и выбрать операцию Добавить линию тренда.
Откроется диалоговое окно Линия тренда (рис.8). Из предлагаемых видов аппроксимирующих функций выбрать Экспоненциальная.
Рис.7. Точечная диаграмма
Для получения на графике аналитического выражения аппроксимирующей функции необходимо в этом же окне (рис.8) щелкнуть на кнопке Параметры и в открывшемся окне (рис.9) установить флажки в поле Показывать уравнение на диаграмме и Показывать на диаграмме величину достоверности аппроксимации. Щелкнуть на кнопке ОК.
Рис. 8. Диалоговое окно Линия тренда
Рис.9. Диалоговое окно Параметры/Линия тренда
В итоге на графике (рис.10) появится
уравнение аппроксимирующей функции,
получаемое по методу наименьших
квадратов, и значение достоверности
аппроксимации
.
Чем ближе это значение к единице, тем
точнее аппроксимация.
Рис.10. Линия тренда
Сравнивая уравнение линии тренда на
диаграмме с уравнением (6) отмечаем, что
аппроксимирующая функция может быть
не единственной. Поэтому для решения
задач оптимизации определяют среднее
значение параметра
показательного закона распределения.
Для рассматриваемого примера
,
и
Следовательно,
