Функции, программирующие поиск решения
Прежде чем пользоваться в программе на 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. Числовой код ограничения.
Числовой код | Ограничение |
1 | <= |
2 | = |
3 | >= |
4 | Ячейки на которые ссылается CellRefдолжны в конце концов получать целочисленные значения |
5 | Ячейки на которые ссылается CellRefдолжны в конце концов получать значения 0 (ноль) или 1 |
Если задается 4 или 5, CellRefдолжно ссылаться на изменяющиеся ячейки, аFormulaTextзадавать нельзя.
FormulaTextНеобязательноVariant. Правая часть ограничения.
Очень странная функция. При изменении необходимо бы задавать старое значение и новое значение. Этого нет. Вместо функции SolverChangeследует использовать вместе две функцииSolverDeleteиSolverAdd, при протоколировании (автоматической записи макроса) так и происходит.
Функция SolverDelete.
Удаляет существующее ограничение. Действие эквивалентно щелчку по кнопке Deleteв диалоговом окне Поиск решения (SolverParameters).
Синтаксис функции следующий.
SolverDelete(CellRef, Relation, FormulaText)
CellRefТребуетсяVariant. Ссылка на ячейку или диапазон образующих левую часть ограничения.
RelationТребуетсяInteger. Числовой код ограничения.
Числовой код | Ограничение |
1 | <= |
2 | = |
3 | >= |
4 | Ячейки на которые ссылается CellRefдолжны в конце концов получать целочисленные значения |
5 | Ячейки на которые ссылается CellRefдолжны в конце концов получать значения 0 (ноль) или 1 |
Если задается 4 или 5, CellRefдолжно ссылаться на изменяющиеся ячейки, аFormulaTextзадавать нельзя.
FormulaTextНеобязательноVariant. Правая часть ограничения.
Функция SolverFinish.
Сообщает MicrosoftExcelкак поступить с результатами и какие отчеты сформировать, если процесс решения завершен.
Синтаксис функции следующий.
SolverFinish(KeepFinal, ReportArray)
KeepFinal НеобязательноVariant. Код действия с результатами.
Числовой код | Действие |
1или пусто | Полученное решение сохраняется в ячейках, подлежавших изменению. |
2 | Полученное решение пропадает, ячейки подлежавшие изменению, сохраняют прежние значения |
ReportArrayНеобязательноVariant. Вид отчета, создаваемого, если процесс решения завершенSolver.
Числовой код | Ограничение |
1 | Результаты |
2 | Чувствительность |
3 | Пределы |
Значение параметра следует задавать используя 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 .
TypeNum | Returns |
1 | Ссылка из поля SetTargetCell, или значение ошибки #N/AеслиSolverне используется на заданном листе. |
2 | Число, соответствующее выбору в группе EqualTo: 1 представляетMax, 2 представляетMin, а представляетValueOf. |
3 | Значение из поля ValueOf. |
4 | Ссылка, возможно множественная, из поля ByChangingCells. |
5 | Число ограничений |
6 | Массив левых частей ограничений, в виде текста |
7 | Массив чисел соответствующих отношениям между левыми и правыми частями ограничений: 1 представляет <=, 2 представляет =, 3 представляет >=, 4 представляет целое, 5 представляет 0 (ноль) или 1 |
8 | Массив правых частей ограничений, в виде текста. |
Коды для окна Solver Options dialog.
TypeNum | Returns |
9 | Максимальное время вычислений |
10 | Предельное число итераций |
11 | Относительная погрешность |
12 | Допустимое отклонение |
13 | Истина, если поднят флажок Линейная модель |
14 | Истина, если поднят флажок Показывать результаты итераций |
15 | Истина, если поднят флажок Автоматическое масштибирование |
16 | Тип оценки,1 — линейная, 2 — квадратичная |
17 | Тип разности,1 — прямые, 2 — центральные |
18 | Метод поиска, 1 — Ньютона, 2 — сопряженные градиенты |
19 | Сходимость |
20 | Истина, если поднят флажок Неотрицательные значения |
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.
Значение MaxMinVal | Определяет |
1 | максимизация. |
2 | минимизация. |
3 | Равенство значению. |
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. Числовой код ограничения.
Числовой код | Ограничение |
1 | <= |
2 | = |
3 | >= |
4 | Ячейки на которые ссылается CellRefдолжны в конце концов получать целочисленные значения |
5 | Ячейки на которые ссылается CellRefдолжны в конце концов получать значения 0 (ноль) или 1 |
Если задается 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. Результат работы приложения
- Особенности (ис) на платформе электронных таблиц
- Формулы
- Операторы
- Операнды
- Результат
- Функции
- Ввод значений, формул, функций
- Функции Microsoft Excel
- Ссылки и массивы
- Функции проверки свойств и значений, информационные функции
- Переменные имена
- Преодоление ограничения на длину формулы
- Использование имен в формулах массивов
- Сводная таблица
- Итерация
- Порядок вычислений
- Подбор параметра
- Команда меню Подбор параметра
- Метод объекта Range
- Поиск решения (Оптимизация)
- Команда меню Поиск решения
- Функции, программирующие поиск решения