Финансист - оптимизатор. Проект "MasterF"

Страница "Ограничения"

В начальный момент эта страница почти пуста. Вот как она выглядит:

увеличить изображение
Рис. 9.5. Страница формирования оптимизационной задачи в начальный момент работы

Как видите на странице две надписи и, соответственно, две командные кнопки - "Нажми меня" и "Решатель". По нажатию первой из этих кнопок автоматически создаются переменные задачи, формируется целевая функция и ограничения задачи. Все эти данные размещаются на странице в полном соответствии с требованиями решателя Solver, и в таком виде, чтобы пользователь сумел понять и оценить правильность работы своего помощника. Конечно же, это наиболее серьезная часть той работы, которую выполняет MasterF. Чтобы дать некоторое представление о том, как решаются задачи, возникающие на этом этапе, я приведу лишь одну процедуру, задающую первое балансное ограничение:

Public Sub FormFirstBalance()
'Формирование краевого балансного ограничения первого этапа
	Dim Myr As Range, i As Byte
	Dim Bound As String, NameVar As String
	Set Myr = Worksheets("Ограничения").Range("Bounds").Offset(1, 0)
	'Формирование формулы, задающей краевое ограничение
	Bound = "="
	For i = 1 To ProjectsNumber
		If ProjectStages(i) > 0 Then
		NameVar = "Sum_" & i & "_1"
		Bound = Bound & NameVar & "+"

		End If
	Next i
	Bound = Left(Bound, Len(Bound) - 1)
	'Формирование трех ячеек рабочего листа, содержащих
	'имя ограничения, левую и правую часть.
	Myr.Offset(0, 1).Value = Bound
	Myr.Offset(0, 1).Name = "Bal0"
	If MaxMin = 1 Then
		'Краевое условие задает ограничение
		Myr.Value = "Balance0"
		Myr.Offset(0, 2).Value = Capital
		Myr.Offset(0, 2).Name = "Bar0"
	Else 'Краевое условие задает целевую функцию
		Myr.Value = "Goal"
		'Переменная Goal - содержит имя ячейки, задающей цель или ограничение.
		'Используется при вызове Решателя
		Goal = "Bal0"
	End If
End Sub

Вот несколько моментов, на которые следует обратить внимание:

• Имена переменных я строю по определенным правилам, - в них используется имя проекта и имя этапа. Это позволяет мне динамически строить формулу, задающую краевое ограничение.
• Для размещения ограничения на рабочем листе, я использую три ячейки. В первой из них помещаю имя ограничения. Заметьте, все ограничения именованы, что позволяет пользователю проанализировать работу, которую выполнил MasterF. Во второй и третьей ячейках размещаются левая и правая часть ограничения.
• В зависимости от постановки задачи формируемое уравнение может задавать цель или быть первым балансным ограничением.

Всем, кого решение оптимизационных задач на Excel интересует в большей степени, рекомендую обратиться к полному коду проекта. А сейчас давайте взглянем на результаты работы, которую выполнил MasterF по формированию переменных и полной системы ограничений:

увеличить изображение
Рис. 9.6. Автоматическое формирование переменных и ограничений задачи

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

Страница "Решение"

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

увеличить изображение
Рис. 9.7. Отчет о решении

Как следует из отчета, чтобы уплатить на втором месяце 150000, и получить по окончании шести месяцев сумму в 600000, необходимо иметь начальный капитал в размере 684628$. В первый месяц этот капитал следует поровну распределить между двумя проектами - A и C. На третьем и пятом месяце деньги следует вкладывать в проект B. Проект D в решении не используется - у него слишком большая длительность, так что ограничения на среднюю длительность не позволяют использовать этот проект.

Я надеюсь, что при желании Вы сумеете самостоятельно реализовать подобный проект, и уж, по крайней мере, разобраться в программном коде моей реализации проекта. Хочу отметить, что проект был написан мной довольно давно, так что при работе с базой данных я использовал объекты DAO, а не ADO. Но, может быть, в этом есть свое достоинство.