Решение задачи с помощью функции Поиск решения

 

Для решения задач, в которых требуется отыскать опти­мальное решение, используется функция Поиск решения. В ее основе лежат итерационные методы.

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

1. Имеется целевая ячейка, содержащая формулу, значение которой должно быть максимальным, минимальным или же равным некоторому числу.

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

3. Кроме того, может быть задано некоторое количество ограничений - условий, которым должны удовлетворять некоторые из изменяемых ячеек.

 

Задача. В качестве примера рассмотрим уже знакомую задачу о ссуде. Исходные значения остаются без изменения, но вводятся дополнительные условия:

· фирма может выплачивать только 16000 руб. ежемесячно за взятую ссуду;

· годовая процентная ставка не должна превышать 15%, но и не может быть меньше 9%;

· срок возврата не превышает 20 лет.

1. Создайте на новом листе таблицу с такими же исходными данными, как в предыдущей задаче.

2. В имеющемся рабочем листе щелкните мышкой на ячейке, для которой требуется найти конкретное решение (целевая ячейка). В нашем случае это будет С6.

3. Выберите команду Сервис - Поиск решения. В открывшемся окне адрес выделенной ячейки должен появиться в поле Установить целевую ячейку.

4. Далее необходимо указать результат, на который следует ориентироваться при поиске решения - максимум, минимум или значение. В нашем случае мы выбираем значение - ежемесячные платежи составляют 16 000 руб. (Рис. 42).

5. Теперь необходимо выбрать изменяемые ячейки, которые программа будет изменять для достижения результата. Адреса можно ввести вручную или щелкнуть на кнопке Предположить. EXCEL проверит целевую ячейку, ссылки на влияющие и затем автоматически подставит адреса последних в окно Поиск решения.

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

· сумма ссуды не должна быть менее 1 500 000 руб.;

· процентная ставка не больше 15%, но и не меньше 9%;

· срок возврата не больше 20 лет.

7. Для задания ограничений нажмите кнопку Добавить. Откроется окно, в котором введите первое ограничение (Рис. 41).

 

 

Рис. 41. Задание ограничений

 

8. Нажмите кнопку Добавить и аналогично введите остальные ограничения.

9. Все введенные условия будут помещены в поле Ограничения. В итоге у вас должны быть заданы следующие параметры:

 

 

Рис. 42. Параметры поиска решения

 

10. Когда ввод всех значений будет закончен, щелкните на кнопке Выполнить. Программа начнет подставлять значения в ячейки, учитывая все ограничения и наблюдая за целевой ячейкой. Найдя нужный набор параметров, она подставит их значения в рабочий лист и сообщит о найденном решении в окне диалога Результаты поиска решения (Рис. 43).

 

 

Рис. 43. Окно результатов поиска решения

 

11. Укажите, хотите ли вы сохранить найденное решение или восстановить исходные значения. Если щелкнуть на кнопке Сохранить сценарий, то можно ввести его название, и использовать при помощи Диспетчера сценариев значения в изменяемых ячейках. Также можно создать для представления результатов отчеты трех типов:

· Результаты используется для отчета, состоящего из целевой ячейки и списка влияющих ячеек модели, их исходных и конечных значений, а также формул и дополнительных сведений о наложенных ограничениях.

· Устойчивость содержит сведения о чувствительности решения к малым изменениям в формулах модели или в ограничениях.

· Пределы состоит из целевой ячейки и списка влияющих ячеек модели, их значений, а также нижних и верхних границ.

12. В данной задаче сценарии можно не создавать, а просто в окне Результаты поиска решения нажмите кнопку ОК. В итоге получится окончательное решение (Рис. 44).

 

 

Рис. 44. Итоговое решение

 

Таким образом, оптимальными условиями для фирмы будут:

· сумма займа (кредита) – 1 500 000 руб.;

· процентная ставка – 9 %;

· срок кредита – 13, 5 лет.

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