Использование «Поиска решений»
Доступ сервису «Поиск решений» возможен из меню «Сервис» -> «Поиск решения». Задачи, которые можно решать с помощью Поиска решения, в общей постановке формулируются следующим образом:
Найти:
х1,х2, …, хn
такие, что:
F(х1,х2, …, хn) → {Max; Min; = Value}
при определенных ограничениях:
G(х1,х2, …, хn) → {≤ Value; ≥ Value; = Value}
Искомые переменные – ячейки рабочего листа Excel – называются регулируемыми ячейками. Целевая функция F(х1,х2, …, хn) должна задаваться в виде формулы в ячейке рабочего листа. Эта формула может содержать функции, определенные пользователем, и должна зависеть (ссылаться) от регулируемых ячеек. В момент постановки задачи определяется, что делать с целевой функцией. Возможен выбор одного из вариантов:
• найти максимум целевой функции F(х1,х2, …, хn);
• найти минимум целевой функции F(х1,х2, …, хn);
• добиться того, чтобы целевая функция F(х1,х2, …, хn) имела фиксированное значение: F(х1,х2, …, хn) = Value.
Функции G(х1,х2, …, хn) называются ограничениями. Их можно задать как в виде равенств, так и неравенств. На регулируемые ячейки можно наложить дополнительные ограничения: неотрицательности и/или целочисленности, тогда искомое решение ищется в области положительных и/или целых чисел.
Рассмотрим процесс нахождения экстремума функции одной переменной y = x2+0.2·x на интервале [-1, 1].
Сначала необходимо заполнить ячейки в соответствии с рис. 1. В ячейке B2 записываем формулу «= А2^2 + 0,2*А2». Копируем ее для остальных ячеек (В3-В22). Строим график функции.
В ячейку А26 располагаем начальное значение, например, -1. После решения с помощью «Поиск решения» в этой ячейке будет выведена координата x найденного локального минимума.
|
| Рис. 1. Заполнение ячеек. |
Встаем на ячейку В26 и копируем туда формулу нашей искомой функции. Затем открываем диалог «Поиск решения» (рис. 2) и выполняем следующие действия:
1) в поле «Установить целевую ячейку» нужно ввести адрес ячейки, содержащей формулу для вычисления значений оптимизируемой функции. В нашем примере целевая ячейка – B26;
2) для поиска минимума используется переключатель «минимальному значению»;
3) в поле «Изменяя ячейки» ввести адреса изменяемых ячеек, т.е. аргументов целевой функции (в нашем случае аргумент х – ячейка А26). Для автоматического поиска всех влияющих на решение ячеек используется кнопка «Предположить»;
4) в поле «Ограничения» с помощью кнопки «Добавить» ввести все ограничения, которым должен отвечать результат поиска. Для нашего примера можно написать ограничения A26 >=-1 и A26 <= 1.
5) для запуска процесса поиска решения нажать кнопку «Выполнить».
|
| Рис. 2. Диалог «Поиск решения». |
Для сохранения полученного решения необходимо использовать переключатель «Сохранить найденное решение» в открывшемся окне диалога «Результаты поиска решения».
Полученное решение зависит от выбора ограничений, которые задаются в «Поиске решения».
Варианты заданий
Задача: найти минимум функции одной переменной и построить график средствами Excel описанным выше способом.
|
Литература
1. Пимонов А.Г., Тынкевич М.А.. Решение уравнений средствами MS Excel. Лабораторный практикум. Кемерово: Типография ГУ КузГТУ. 2002. – 23 с.
2. Курицкий Б. Поиск оптимальных решений средствами Excel 7.0. СПб.: BHV – Санкт-Петербург, 1997.– 384 с.
3. Калиткин Н.Н. Численные методы. М.: Наука, 1978. 512 с.
4. Амосов А.А., Дубинский Ю.А., Копченова Н. В. Вычислительные методы для инженеров. М.: Высшая школа, 1994. 543с.
[1] Множество компактно, если из каждого бесконечного и ограниченного его подмножества можно выделить сходящуюся последовательность.
[2] Множество замкнуто, если предел любой сходящейся последовательности его элементов принадлежит этому множеству.