Анализ деятельности офиса

В следующем окне задаются значения изменяемых ячеек, устанавливаемых сценарием:

Рис. 8.34. Установка значений параметров, заданных сценарием

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

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

увеличить изображение
Рис. 8.35. Отчет по результатам вычисления сценариев

Кнопки в левом поле сценария позволяют скрыть или развернуть для показа его отдельные части. В примере вся информация о сценариях показана. С содержательной точки зрения главным итогом является обоснование принятого менеджером решения применить сценарий "Нормальный тираж" - он и обеспечивает максимальный выигрыш.

Программирование сценариев

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

Sub ДобавитьСценарии()
	'Создание трех сценариев
	Dim mys As Worksheet
	Dim Scen As Scenario, Scens As Scenarios
	Set mys = ThisWorkbook.Worksheets("Лист6")
	With mys
		Set Scens = .Scenarios
		If .Scenarios.Count > 0 Then
			'Удаление сценариев
			For Each Scen In Scens
				Scen.Delete
			Next Scen
		End If
		With Scens
			.Add Name:="Минимальный тираж", ChangingCells:=Range("J37:L37"), _
			Values:=Array("5000", "0", "1,5")
			.Add Name:="Максимальный тираж", ChangingCells:=Range("J37:L37"), _
			Values:=Array("30000", "5", "2,5")
			.Add Name:="Нормальный тираж", ChangingCells:=Range("J37:L37"), _
			Values:=Array("10000", "2", "2")
			'Запуск сценариев на выполнение
				For Each Scen In Scens
				Scen.Show
			Next Scen
			'Построение отчета
			'.CreateSummary ReportType:=xlStandardSummary, _
			' ResultCells:=Range("H49,D49,B49")
			.CreateSummary ReportType:=xlSummaryPivotTable, _
			ResultCells:=Range("H49,D49,B49")
			
		End With
	End With
End Sub

Рассмотрим на этом примере основные объекты и методы, связанные с применением сценариев. Рабочие листы (объекты Sheet ) включают в свой состав коллекцию Scenarios. Новые элементы в эту коллекцию добавляются, как чаще всего бывает, методом Add. Параметр Name задает имя сценария, ChangingCells - изменяемые ячейки. Обычно эти ячейки располагают подряд, чтобы можно было их указать одним смежным интервалом, но делать так не обязательно - объект Range может задавать и несмежные интервалы. В параметре Comment указывается дополнительная информация, по умолчанию задается автор сценария. Эти данные выводятся в итоговом отчете. Остальные два параметра задают возможность скрытия сценария и его защиты от несанкционированного доступа, - не всегда и не всем требуется объяснять принятое решение.

Совсем просто запустить сценарий на выполнение, удалить или изменить. Для этого у объектов Scenario есть методы Show, Delete, ChangeScenario. В нашем примере методом Show все три сценария поочередно запускаются на выполнение. Чтобы процедура работала в случаях ее многократного запуска, то сценарии на рабочем листе предварительно удаляются, для чего используется метод Delete.

Для создания отчета и подведения итогов используется метод CreateSummary. У параметра ReportType, задающего тип отчета, возможны значения xlStandartSummary и xlSummaryPivotTable. С первым отчетом, принимаемым по умолчанию, Вы знакомы, во втором случае отчет определяет сводную таблицу. Параметр ResultCells позволяет задать результирующие ячейки. В отличие от работы вручную я включил в итоговый отчет сведения о трех параметрах: доходе, периоде продажи и количестве проданных книг. Объект Range здесь задает три несмежные ячейки. В процедуру включены два вызова метода CreateSummary, каждый из которых создает свой тип отчета. Один из вызовов, естественно, закомментирован. Поскольку стандартный тип отчета уже приведен, то взгляните, как выглядит сводная таблица, построенная в результате выполнения этой процедуры:

Рис. 8.36. Сводная таблица, программно построенная по результатам выполнения сценариев

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

Средства оптимизации - мощные инструменты, используемые в анализе "Что, если ...?". Рассмотрим вначале то, что попроще. "Подбор Параметра" позволяет для функции одного параметра 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. Уведомление о достигнутом доходе