Надстройки Подбор параметра и Поиск решения

В электронных таблицах Excel существует возможность с помощью надстроек Подбор параметра и Поиск решения решать экономические и инженерные задачи. [3], [4]

Решение задач подбора параметра

Надстройка Подбор параметра запускается командой Сервис – Подбор параметра. Если в меню отсутствует команда Подбор параметра, следует воспользоваться командой Сервис – Надстройки и установить флажок Подбор параметра.

Задачи с использованием надстройки Подбор параметра сводятся к получению требуемое значение в одной ячейке путем изменения значения другой ячейки. Первая ячейка называется целевой, а значение второй ячейки – параметром. Целевая ячейкадолжна содержать формулу, прямо или косвенно ссылающуюся на ячейку с изменяемым параметром.

Пример 7.1. Известен размер вклада, который будет помещен в банк на некоторый срок под определенный процент (табл. 7.1).

Таблица 7.1

Исходные данные

Подзадача 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.1. Диалоговое окно Подбор параметра

В MS Excel существует возможность с помощью надстройки Поиск решения найти решение, оптимальное в некотором смысле при нескольких входных значениях и наборе ограничений на решение. Диспетчер сценариев способен запомнить несколько решений, найденных данным средством и сгенерировать на этой основе отчет. С помощью надстройки Поиск решения можно решать как линейные задачи, так и нелинейные.

Надстройка Поиск решения запускается командой Сервис – Поиск решения. Если в меню отсутствует команда Поиск решения, следует воспользоваться командой Сервис – Надстройки и установить флажок Поиск решения.

Работа по решению некоторой оптимизационной задачи всегда начинается с построения математической модели. На данном этапе делаются выводы об исходных данных, искомых переменных, о пределах, в которых могут находиться значения искомых величин, о зависимостях между переменными, о критериях, по которым необходимо находить оптимальное решение. Сюда же входит преодоление несовместимости, а также неограниченности целевой функции: при максимизации целевой функции область допустимых решений должна быть ограничена сверху, при минимизации – снизу.

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

Решая задачи с нелинейными зависимостями, следует:

- ввести предварительно предположительные значения искомых переменных (иногда легко получить графическое представление решения и сделать приблизительные выводы о решении);

- в окне Параметры поиска решения снять (если установлен) флажок Линейная модель.

При необходимости проводится анализ решения. Часто добавляют также представление решения в виде графиков или диаграмм. Можно получить и отчет о поиске решения. Отчеты бывают трех типов: Результаты, Устойчивость, Пределы. Тип отчета выбирается по окончании поиска решения в окне Результаты поиска решения в списке Тип отчета (можно выбрать сразу два или три типа).

- Отчет типа Результаты содержит окончательные значения параметров задачи целевой функции и ограничений.

- Отчет типа Устойчивость показывает результаты малых изменений параметров поиска решений.

- Отчет типа Пределы показывает изменения решения при поочередной максимизации и минимизации каждой переменной при неизменных других переменных.