Оптимизация и анализ "Что, если ...?"

Средства оптимизации - мощные инструменты, используемые в анализе "Что, если ...?". Рассмотрим вначале то, что попроще. "Подбор Параметра" позволяет для функции одного параметра F(a) подобрать, если можно, такое значение параметра a^, что функция в этой точке будет иметь заранее заданное значение F* = F(a^). Наш менеджер, найдя наилучший вариант сценария, спросил себя: "Что, если слегка увеличить тираж? Увеличу ли я за счет этого доход до 200 000?" Чтобы ответить на эти вопросы, он выбрал в меню "Сервис" пункт "Подбор параметра". В появившемся окне он задал доход как целевую функцию, 200000 - как желаемое значение дохода, Тираж - как параметр (изменяемую ячейку), значение которого нужно подобрать так, чтобы достичь заданной величины дохода. Но сделать этого невозможно. В такой постановке у задачи решения нет, о чем и было ему сообщено. Менеджеру пришлось умерить свои аппетиты: он повторно вызвал "Подбор параметра

"µ ", задав теперь значение дохода, равное 180000. Теперь решение удалось найти. Оно достигается при тираже, равном 10900. Менеджер округлил значение тиража до 10500, что принесло увеличение дохода еще на 10000. На этом менеджер и остановился. Найденное им решение практически оптимально.

Подбор параметра можно осуществлять и программно. Для этого следует вызвать метод GoalSeek объекта Range. Вызывает этот метод объект Range, задающий целевую функцию. Синтаксис метода:

Function GoalSeek(Goal, ChangingCell As Range) As Boolean

Параметр Goal задает значение целевой функции, а параметр ChangingCel - изменяемую ячейку (параметр целевой функции), значение которой будет изменяться, в попытке достичь заданного значения целевой функции. Функция GoalSeek возвращает True в случае успеха и False в противном случае. Вот пример процедуры, вызывающей эту функцию:

Sub ПодборПараметра() Dim Res As Boolean 'Доход - имя целевой ячейки - H49, вычисляющей доход Res = Range("H49").GoalSeek(Goal:=200000, ChangingCell:=Range("J37")) If Res Then Доход = 200000 Else 'Восстанавливаем нормальные значения в ячейках ActiveSheet.Scenarios(3).Show Res = Range("H49").GoalSeek(Goal:=180000, ChangingCell:=Range("J37")) If Res Then Доход = 180000 End If MsgBox ("Ваш Доход = " & Доход) End Sub

И в заключение этой главы об анализе офисной деятельности и используемых для этого средствах Excel взгляните на окошко, уведомляющее о достигнутом доходе:

Рис. 8.37. Уведомление о достигнутом доходе

Содержание раздела