Функции, программирующие поиск решения

Прежде чем пользоваться в программе на VBAфункциями, программирующими поиск решения, следует установить ссылку на надстройкуSolver. Находясь внутри активного модуляVBA, надо выполнить командуReferencesиз менюTools. Необходимо активировать флажокSolver.xla. Если такого флажка нет среди спискаAvailableReferences, следует найти (Browse) и открытьSolver.xlaв поддиректории \Office\Library\Solver\.

Функция SolverChange.

Изменяет существующее ограничение. Действие эквивалентно щелчку по кнопке Changeв диалоговом окне Поиск решения (SolverParameters).

Синтаксис функции следующий.

SolverChange(CellRef, Relation, FormulaText)

CellRefТребуетсяVariant. Ссылка на ячейку или диапазон образующих левую часть ограничения.

RelationТребуетсяInteger. Числовой код ограничения.

Если задается 4 или 5, CellRefдолжно ссылаться на изменяющиеся ячейки, аFormulaTextзадавать нельзя.

FormulaTextНеобязательноVariant. Правая часть ограничения.

Очень странная функция. При изменении необходимо бы задавать старое значение и новое значение. Этого нет. Вместо функции SolverChangeследует использовать вместе две функцииSolverDeleteиSolverAdd, при протоколировании (автоматической записи макроса) так и происходит.

Функция SolverDelete.

Удаляет существующее ограничение. Действие эквивалентно щелчку по кнопке Deleteв диалоговом окне Поиск решения (SolverParameters).

Синтаксис функции следующий.

SolverDelete(CellRef, Relation, FormulaText)

CellRefТребуетсяVariant. Ссылка на ячейку или диапазон образующих левую часть ограничения.

RelationТребуетсяInteger. Числовой код ограничения.

Если задается 4 или 5, CellRefдолжно ссылаться на изменяющиеся ячейки, аFormulaTextзадавать нельзя.

FormulaTextНеобязательноVariant. Правая часть ограничения.

Функция SolverFinish.

Сообщает MicrosoftExcelкак поступить с результатами и какие отчеты сформировать, если процесс решения завершен.

Синтаксис функции следующий.

SolverFinish(KeepFinal, ReportArray)

KeepFinal НеобязательноVariant. Код действия с результатами.

ReportArrayНеобязательноVariant. Вид отчета, создаваемого, если процесс решения завершенSolver.

Значение параметра следует задавать используя VBAфункциюArray. Например, ReportArray:=Array(1,3) или ReportArray:=Array(1).

Функция SolverFinishDialog.

Эта функция в дополнение к функции SolverFinish, показывает окно диалога Результаты поиска решения (SolverResults).

Синтаксис функции следующий.

SolverFinishDialog(KeepFinal, ReportArray)

Описание параметров такое же, как у функции SolverFinish.

Функция SolverGet.

Возвращает информацию о текущих установках Solver. Установки задаются в диалоговых окнахSolverParametersиSolverOptions.

Синтаксис функции следующий.

SolverGet(TypeNum, SheetName)

TypeNumТребуетсяInteger. Числовой код, запрашиваемой информации. Можно запросить сведения относящиеся к окну диалогаSolverParametersили к окну диалогаSolverOptions.

Коды для окна Solver Parameters .

Коды для окна Solver Options dialog.

SheetNameНеобязательноVariant. Имя листа, содержащего модельSolver, для которой запрашивается информация. Если параметрSheetNameопущен, предполагается активный лист.

Функция SolverLoad.

Загружает сохраненные на листе параметры модели для Solver.

Синтаксис функции следующий.

SolverLoad(LoadArea)

LoadAreaТребуетсяVariant. Ссылка на диапазон ячеек, из которого будет загружено описание модели. Диапазон может находится на любом листе, однако если этот лист не является активным листом, то следует задавать имя листа. Например,SolverLoad("Лист2!A1:A3") загрузит описание модели с листа Лист2 даже если он не активен.

Функция SolverOk.

Определяет основную модель Поиска решения. Эквивалентна выполнению команды Solverв менюToolsи последующему заданию опций в диалоговом окнеSolverParameters.

Синтаксис функции следующий.

SolverOk(SetCell, MaxMinVal, ValueOf, ByChange)

SetCellНеобязательноVariant. Ссылается на целевую ячейку на активном рабочем листе. Соответствует полюSetTargetCellв диалоговом окнеSolverParameters.

MaxMinValНеобязательноVariant. Соответствует опцииMax,MinилиValueвыбранной в группеEqualToв диалоговом окнеSolverParameters.

ValueOfНеобязательноVariant. Если параметрMaxMinValравен 3, то следует задать значение, которому будет равна целевая ячейка.

ByChangeНеобязательноVariant. Диапазон ячеек, которые будут изменяться так, чтобы получить желаемый результат в целевой ячейке. Соответствует полюByChangingCellsв диалоговом окнеSolverParameters.

Функция SolverOkDialog.

В дополнение к функции SolverOKfunction, показывает диалоговое окноSolverParameters.

Синтаксис функции следующий.

SolverOkDialog(SetCell, MaxMinVal, ValueOf, ByChange)

Параметры те же, что у функции SolverOk.

Функция SolverOptions

Позволяет задать развитые опции модели Поиска решения. Эта функция соответствует диалоговому блоку SolverOptions.

Синтаксис функции следующий.

SolverOptions(MaxTime, Iterations, Precision, AssumeLinear, StepThru, Estimates, Derivatives, Search, IntTolerance, Scaling, Convergence, AssumeNonNeg)

MaxTimeНеобязательноVariant. Наибольшее время (в секундах) отводимоеMicrosoftExcelна решение задачи. Значение должно быть положительным целым. Значение по умолчанию 100 достаточно для большинства небольших задач, не может быть больше 32 767.

IterationsНеобязательноVariant. Наибольшее число итераций отводимоеMicrosoftExcelна решение задачи. Значение должно быть положительным целым. Значение по умолчанию 100 достаточно для большинства небольших задач, не может быть больше 32 767.

PrecisionНеобязательноVariant. Число в диапазоне от 0 до 1, которое задает степень точности, которая будет использоваться при решении задачи. Значение по умолчанию 0,000001. Чем больше число (например, 0.0001) тем ниже точность. В общем случае, чем выше точность, тем больше времени потребуется, чтобыSolverдостиг решения.

AssumeLinearНеобязательноVariant. Если все связи в модели линейны, то может быть заданоTrue. Это ускорит процесс решения. Значение по умолчаниюFalse.

StepThruНеобязательноVariant. Если задать значениеTrue, тоSolverбудет приостанавливаться при достижении допустимого решения. Затем, используя параметрShowRefфункцииSolverSolveможно выполнить подпрограмму при каждой приостановкеSolver. Если задать значениеFalse, тоSolverне будет приостанавливаться при достижении допустимого решения. Значение по умолчаниюFalse.

EstimatesНеобязательноVariant. Определяет подход, используемый при получении начальных оценок для базисных переменных при каждом одномерном поиске: 1 означает линейные оценки, а 2 означает квадратичные оценки. Линейные оценки используют линейную экстраполяцию вдоль касательной. Квадратичные оценки используют квадратичную экстраполяцию; что может улучшить результаты для сильно нелинейных задач. Значение по умолчанию 1 (линейные оценки).

DerivativesНеобязательноVariant. Определяет прямые или центральные разности используются при оценке частных производных целевой функции и функций ограничений: 1 представляет прямые разности, а 2 представляет центральные разности. Центральные разности требуют больших пересчетов рабочих листов, но они могут помочь при получении сообщения о том , чтоSolverне может улучшить решение. Центральные разности рекомендуется использовать и в случае ограничений быстро меняющихся на границах. Значение по умолчанию 1 (прямые разности).

SearchНеобязательноVariant. Задает поисковый алгоритм, применяемый на каждой итерации для определения направления поиска: 1 задает поиск методом Ньютона, а 2 задает поиск методом сопряженных градиентов. Значение по умолчанию 1 (метод Ньютона). Этот метод, чтобы достичь той же точности, что и метод сопряженных градиентов, обычно требует больше памяти, но меньше итераций. Для больших задач при недостатке памяти рекомендуется метод сопряженных градиентов. Он особенно полезен если итерация от одного допустимого решения до другого незначительно улучшает целевую функцию.

IntToleranceНеобязательноVariant. Число в диапазоне от 0 до 1, задает степень отклонения от целого. Применяется только, если заданы целочисленные ограниченияна переменные. Чем выше погрешность, тем быстрее будет идти процесс решения.

ScalingНеобязательноVariant. Если два и более ограничений отличаются по порядку величины, то задание значенияTrueзаставитSolverво время вычислений масштабировать ограничения к одному порядку величины. Это также полезно сделать если переменные модели и целевая функция сильно отличаются по порядку величины, например при максимизации процента прибыли при миллионных инвестициях. Задание значенияFalseзаставитSolverвычислять без мастабирования. Значение по умолчаниюFalse.

ConvergenceНеобязательноVariant. Число в диапазоне от 0 до 1, которое определяет степень сходимости для нелинейных задач. Когда относительное изменение значения целевой ячейки для последних пяти итераций станет меньше этого числа,Solverостановится и выдаст сообщение: «Solverconvergedtothecurrentsolution.Allconstraintsaresatisfied.»

AssumeNonNegНеобязательноVariant. Задание значенияTrueзаставитSolverсчитать 0 (ноль) нижним пределом для всех изменяемых ячеек, для которых не установлен нижний предел в списке ограничений. Задание значенияFalseзаставитSolverиспользовать только явно заданные ограничения.

Функция SolverReset.

Сбрасывает все выборы ячеек и все ограничения, заданные в диалоговом окне SolverParametersи восстанавливает значения по умолчанию в диалоговом окнеSolverOptions. Эквивалент щелчку по кнопкеResetAllв диалоговом окнеSolverParameters.Функция SolverResetавтоматически вызывается при вызове функцииSolverLoad.

Синтаксис функции следующий.

SolverReset( )

SolverSaveFunction

Сохраняет описание задачи для Solverна рабочем листе.

Синтаксис функции следующий.

SolverSave(SaveArea)

SaveAreaТребуетсяVariant. Диапазон в котором сохраняется модельSolver. Диапазон может находится на любом листе, однако если этот лист не является активным листом, то следует задавать имя листа. Например,SolverSave("Лист2!A1:A3") сохранит модель на листе Лист2, даже если он не активен.

Функция SolverSolve.

Начинает поиск решения с помощью Solver. Эквивалентно щелчку по кнопкеSolveв диалоговом окнеSolverParameters.

Синтаксис функции следующий.

SolverSolve(UserFinish, ShowRef)

UserFinishНеобязательноVariant. Задание значенияTrueвозвратит результаты поиска без отображения диалогового окнаSolverResults. Если опустить параметр или задать значениеFalseто результату будут возврашены и будет показано диалоговое окноSolverResults.

ShowRefНеобязательноVariant. Используется только если для аргументаStepThruфункцииSolverOptionsбыло задано значениеTrue. Значением может быть имя макроса, как строка. Этот макрос будет вызван когдаSolverвернет промежуточные результаты.

Функция SolverAdd.

Добавляет ограничение к текущей задаче Эквивалент щелчку по кнопке Addв диалоговом окнеSolverParameters.

Синтаксис функции следующий.

SolverAdd(CellRef, Relation, FormulaText)

CellRef Требуется Variant. Ссылка на ячейку или диапазон образующих левую часть ограничения.

RelationТребуетсяInteger. Числовой код ограничения.

Если задается 4 или 5, CellRefдолжно ссылаться на изменяющиеся ячейки, аFormulaTextзадавать нельзя.

FormulaTextНеобязательноVariant. Правая часть ограничения.

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

Пример взят из книги А.Гарнаева и чуть-чуть изменен

Первоначально следует создать пользовательскую форму в VBA.

Программа может быть следующей. Результат применения приложения показан на рис. 4.26.

Option Explicit

Private Sub cmdSolve_Click()

Dim vars As String

Dim Costs As String

Dim Target As String

Dim sOut As String

Dim sIn As String

Dim varIn As String

Dim varOut As String

Dim r As Integer

Dim c As Integer

Dim res As Integer

Dim vIn As Double

Dim vOut As Double

'-------

vars = refVar.Text

sIn = refIn.Text

sOut = refOut.Text

Costs = refCosts.Text

'Проверка сбалансированности модели

vIn = Evaluate("Sum(" & sIn & ")")

vOut = Evaluate("Sum(" & sOut & ")")

If vIn <> vOut Then

MsgBox "Задача не сбалансирована", vbExclamation, _

"Транспортная задача"

Exit Sub

End If

With Range(vars)

r = .Rows.Count

c = .Columns.Count

Target = .Cells(r + 1, c + 1).Address(RowAbsolute:=True, _

ColumnAbsolute:=True)

End With

Range(Target).FormulaLocal = "=СУММПРОИЗВ(" & _

vars & ";" & Costs & ")"

With Range(vars)

varIn = Range(.Cells(r + 1, 1), _

.Cells(r + 1, c)).Address(RowAbsolute:=True, _

ColumnAbsolute:=True)

varOut = Range(.Cells(1, c + 1), _

.Cells(r, c + 1)).Address(RowAbsolute:=True, _

ColumnAbsolute:=True)

End With

' TranAbsRef varIn, varIn

' TranAbsRef varOut, varOut

'Ввод в ячейки формул для ограничений

Range(vars).Cells(r + 1, 1).Select

ActiveCell.FormulaR1C1Local = "=СУММ(R[-" & CStr(r) & "]C:R[-1]C)"

Selection.AutoFill Destination:=Range(varIn), _

Type:=xlFillDefault

Range(vars).Cells(1, c + 1).Select

ActiveCell.FormulaR1C1Local = "=СУММ(RC[-" & CStr(c) & "]:RC[-1])"

Selection.AutoFill Destination:=Range(varOut), _

Type:=xlFillDefault

'Поиск Решения

SolverReset

SolverOk SetCell:=Target, _

MaxMinVal:=2, _

ValueOf:=0, _

ByChange:=vars

SolverAdd CellRef:=varIn, _

Relation:=2, _

FormulaText:=sIn

SolverAdd CellRef:=varOut, _

Relation:=2, _

FormulaText:=sOut

SolverOptions MaxTime:=100, _

Iterations:=100, _

Precision:=0.000001, _

AssumeLinear:=True, _

StepThru:=False, _

Estimates:=1, _

Derivatives:=1, _

SearchOption:=1, _

IntTolerance:=5, _

Scaling:=False, _

Convergence:=0.0001, _

AssumeNonNeg:=True

res = SolverSolve(UserFinish:=True)

With Range(vars).Cells(r + 2, c + 2)

If res = 0 Then

.Value = "Решение Найдено"

Else

.Value = "Решение НЕ Найдено"

End If

End With

End Sub

Рис. 4.26. Результат работы приложения