VBA Solver - Пример стъпка по стъпка за използване на Solver в Excel VBA

Съдържание

Excel VBA Solver

Как решавате сложни проблеми? Ако не сте сигурни как да се справите с тези проблеми, тогава няма какво да се притеснявате, имаме решение в нашия Excel. В по-ранната ни статия „Excel Solver“ научихме как да решаваме уравнения в Excel. Ако не сте наясно, „SOLVER“ се предлага и с VBA. В тази статия ще ви преведем как да използвате “Solver” във VBA.

Активирайте Solver в работен лист

Решителят е скрит инструмент, наличен в раздела с данни в Excel (ако вече е активиран).

За да използваме първо SOLVER в Excel, трябва да активираме тази опция. Следвайте стъпките по-долу.

Стъпка 1: Отидете в раздела ФАЙЛ. Под раздела ФАЙЛ изберете „Опции“.

Стъпка 2: В прозореца Опции на Excel изберете „Добавки“.

Стъпка 3: В долната част избира „Добавки за Excel“ и кликнете върху „Go“.

Стъпка 4: Сега поставете отметка в квадратчето „Solver Add-in“ и кликнете върху, Ok.

Сега трябва да видите “Solver” под раздела с данни.

Активирайте Solver във VBA

И при VBA Solver е външен инструмент; трябва да му дадем възможност да го използва. Следвайте стъпките по-долу, за да го активирате.

Стъпка 1: Отидете на Инструменти >>> Справка в прозореца на редактора на Visual Basic.

Стъпка 2: От списъка с референции изберете „Solver“ и кликнете върху Ok, за да го използвате.

Сега можем да използваме Solver и във VBA.

Функции за решаване във VBA

За да напишем VBA код, трябва да използваме три „Функции за решаване“ във VBA и тези функции са „SolverOk, SolverAdd и SolverSolve“.

SolverOk

SolverOk (SetCell, MaxMinVal, ValueOf, ByChange, Engine, EngineDesc)

SetCell: Това ще бъде референтната клетка, която трябва да бъде променена, т.е. клетка печалба.

MaxMinVal: Това е незадължителен параметър, по-долу са числата и спецификаторите.

  • 1 = Увеличете
  • 2 = Минимизиране
  • 3 = Съответства на определена стойност

ValueOf: Този параметър трябва да предостави, ако аргументът MaxMinVal е 3.

ByChange: Чрез промяна на кои клетки това уравнение трябва да бъде решено.

SolverAdd

Сега нека видим параметрите на SolverAdd

CellRef: За да зададете критериите за решаване на проблема, трябва да се промени какво представлява клетката.

Връзка: При това, ако логическите стойности са удовлетворени, тогава можем да използваме числата по-долу.

  • 1 е по-малко от (<=)
  • 2 е равно на (=)
  • 3 е по-голямо от (> =)
  • 4 е трябва да има крайни стойности, които са цели числа.
  • 5 е трябва да има стойности между 0 или 1.
  • 6 е трябва да има крайни стойности, които са различни и цели числа.

Пример за Solver в Excel VBA

За пример разгледайте сценария по-долу.

Използвайки тази таблица, трябва да идентифицираме сумата „Печалба“, която трябва да бъде минимум 10000. За да стигнем до това число, имаме определени условия.

  • Единиците за продажба трябва да са цяло число.
  • Цена / Единица трябва да бъде между 7 и 15.

Въз основа на тези условия трябва да определим колко единици да продадем на каква цена, за да получим стойността на печалбата от 10000.

Добре, нека решим това уравнение сега.

Стъпка 1: Стартирайте подпроцедурата VBA.

Код:

Sub Solver_Example () Край Sub

Стъпка 2: Първо трябва да зададем препратка към клетката Objective с помощта на функцията SolverOk .

Стъпка 3: Първият аргумент на тази функция е “SetCell”, в този пример трябва да променим стойността на клетка Profit, т.е. клетка B8.

Код:

Sub Solver_Example () SolverOk SetCell: = Range ("B8") End Sub

Стъпка 4: Сега трябва да зададем тази стойност на клетката на 10000, така че за MaxMinVal използвайте 3 като стойност на аргумента.

Код:

Sub Solver_Example () SolverOk SetCell: = Обхват ("B8"), MaxMinVal: = 3 End Sub

Стъпка 5: Следващият аргумент ValueOf стойност трябва да бъде 10000.

Код:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000 End Sub

The next argument is ByChange i.e. by changing which cells this equation needs to be solved. In this case by changing Units to Sell (B1) and Price Per Unit (B2) cell needs to be changed.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") End Sub

Note: remaining arguments are not required here.

Step 6: Once the objective cell is set, now we need to construct other criteria’s. For this open “SolverAdd” function.

Step 7: First Cell Ref we need to change is Price Per Unit cell i.e. B2 cell.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2") End Sub

Step 8: This cell needs to be>= 7, so the Relation argument will be 3.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3 End Sub

Step 9: This cell value should be>=7 i.e. Formula Text = 7.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 End Sub

Step 10: Similarly the same cell needs to be less than 15, so for this relation is <= i.e. 1 as the argument value.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 End Sub

Step 11: First cell i.e. Units to Sell must be an Integer value for this also set up the criteria as below.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 SolverAdd CellRef:=Range("B1"), Relation:=4, FormulaText:="Integer" End Sub

Step 12: In one final step, we need to add the SolverSolve function.

Code:

Sub Solver_Example() SolverOk SetCell:=Range("B8"), MaxMinVal:=3, ValueOf:=10000, ByChange:=Range("B1:B2") SolverAdd CellRef:=Range("B2"), Relation:=3, FormulaText:=7 SolverAdd CellRef:=Range("B2"), Relation:=1, FormulaText:=15 SolverAdd CellRef:=Range("B1"), Relation:=4, FormulaText:="Integer" SolverSolve End Sub

Добре, стартирайте кода, като натиснете клавиша F5, за да получите резултата.

Когато стартирате кода, ще видите следния прозорец.

Натиснете Ok и ще получите резултата в Excel лист.

Така че, за да спечелим печалба от 10000, трябва да продадем 5000 единици на 7 на цена, където себестойността е 5.

Неща за запомняне

  • За да работите с Solver в Excel и VBA, първо го активирайте за работен лист, след което активирайте за справка за VBA.
  • След като е активиран както на работни листове, така и на VBA, само ние имаме достъп до всички функции на Solver.

Интересни статии...

Препоръчано