Решение задач подбора параметра
Надстройка Подбор параметразапускается командой Сервис – Подбор параметра. Если в меню отсутствует команда Подбор параметра, следует воспользоваться командой Сервис – Надстройки и установить флажок Подбор параметра.
Задачи с использованием надстройки Подбор параметра сводятся к получению требуемого значения в одной ячейке путем изменения значения другой ячейки. Первая ячейка называется целевой, а значение второй ячейки – параметром. Целевая ячейкадолжна содержать формулу, прямо или косвенно ссылающуюся на ячейку с изменяемым параметром.
Пример 7.1. Вклад будет помещен в банк на 5 лет под 5% годовых.
Вводим исходные данные (рис. 7.*)
рис. 7.*. Исходные данные
Подзадача 1. Требуется рассчитать сумму возврата вклада в конце периода.
Для расчета коэффициента увеличения вклада в ячейку B4вводится формула =(1+B3)^B2, а для расчета суммы возврата вклада в ячейку B5вводится формула =B1*B4.
Подзадача 2. Определить условия помещения вклада, наиболее подходящие для его владельца. Например, рассчитать процентную ставку вклада, при которой сумма возврата вклада будет составлять 8 000 рублей.
Значение в ячейке B5зависит от значения ячейки B3. Необходимо подобрать параметр в ячейке B3таким образом, чтобы значение в целевой ячейке стало равным заданному значению, в данном случае 8 000 рублей.
Решается задача методом последовательных приближений по следующей схеме:
1) указатель устанавливается в целевую ячейку B5;
2) вызываем процедура Подбор параметраиз меню Сервис(рис. 7.1);
3) в диалоговом окне Подбор параметра задается значение в целевой ячейке, равное 8 000;
4) в поле Изменяя значение ячейки вводится адрес варьируемой ячейки B3.
После удачного завершения решения задачи откроется окно с соответствующим сообщением, а в ячейке B3появится результат решения.
Окончательно таблица примет следующий вид (рис. 7.№):
 |  |
| Рис. 7.1. Диалоговое окно Подбор параметра | рис. 7.№. Результаты расчетов |
В MS Excel существует возможность с помощью надстройки Поиск решения найти решение, оптимальное в некотором смысле при нескольких входных значениях и наборе ограничений на решение. Диспетчер сценариев способен запомнить несколько решений, найденных данным средством и сгенерировать на этой основе отчет. С помощью надстройки Поиск решения можно решать как линейные задачи, так и нелинейные.
Надстройка Поиск решения запускается командой Сервис – Поиск решения. Если в меню отсутствует команда Поиск решения, следует воспользоваться командой Сервис – Надстройки и установить флажок Поиск решения.
Работа по решению некоторой оптимизационной задачи всегда начинается с построения математической модели. На данном этапе делаются выводы об исходных данных, искомых переменных, о пределах, в которых могут находиться значения искомых величин, о зависимостях между переменными, о критериях, по которым необходимо находить оптимальное решение. Сюда же входит преодоление несовместимости, а также неограниченности целевой функции: при максимизации целевой функции область допустимых решений должна быть ограничена сверху, при минимизации – снизу.
Бόльшую часть задач представляют собой задачи линейного программирования, т. е. такие, у которых критерий оптимизации и ограничения – линейные функции. В этом случае для решения задачи следует установить флажок Линейная модель в окне Параметры поиска решения. Это обеспечит применение симплекс-метода. В противном случае, даже для решения линейной задачи, будут использоваться более общие (т.е. медленные) методы.
Решая задачи с нелинейными зависимостями, следует:
- ввести предварительно предположительные значения искомых переменных (иногда легко получить графическое представление решения и сделать приблизительные выводы о решении);
- в окне Параметры поиска решения снять (если установлен) флажок Линейная модель.
При необходимости проводится анализ решения. Часто добавляют также представление решения в виде графиков или диаграмм. Можно получить и отчет о поиске решения. Отчеты бывают трех типов: Результаты, Устойчивость, Пределы. Тип отчета выбирается по окончании поиска решения в окне Результаты поиска решения в списке Тип отчета (можно выбрать сразу два или три типа).
- Отчет типа Результаты содержит окончательные значения параметров задачи целевой функции и ограничений.
- Отчет типа Устойчивость показывает результаты малых изменений параметров поиска решений.
- Отчет типа Пределы показывает изменения решения при поочередной максимизации и минимизации каждой переменной при неизменных других переменных.