VBA Solver - Exemplu pas cu pas pentru a utiliza Solver în Excel VBA

Excel VBA Solver

Cum rezolvați probleme complicate? Dacă nu sunteți sigur cum să rezolvați aceste probleme, atunci nu vă faceți griji, avem soluții în Excel. În articolul nostru anterior „Excel Solver” am învățat cum să rezolvăm ecuațiile în Excel. Dacă nu știți, „SOLVER” este disponibil și cu VBA. În acest articol, vă vom prezenta cum să utilizați „Solver” în VBA.

Activați Solver în foaia de lucru

Un rezolvator este un instrument ascuns disponibil sub fila date în Excel (dacă este deja activat).

Pentru a utiliza SOLVER în Excel mai întâi, trebuie să activăm această opțiune. Urmați pașii de mai jos.

Pasul 1: Accesați fila FILE. Sub fila FILE alegeți „Opțiuni”.

Pasul 2: în fereastra Opțiuni Excel, alegeți „Complimente”.

Pasul 3: în partea de jos alege „Excel Add-Ins” și faceți clic pe „Go”.

Pasul 4: bifați acum caseta „Solver Add-in” și faceți clic pe, Ok.

Acum trebuie să vedeți „Solver” sub fila date.

Activați Solver în VBA

Și în VBA, Solver este un instrument extern; trebuie să-i permitem să-l folosească. Urmați pașii de mai jos pentru ao activa.

Pasul 1: Accesați Instrumente >>> Referință în fereastra Editorului Visual Basic.

Pasul 2: Din lista de referințe, alegeți „Solver” și faceți clic pe Ok pentru al utiliza.

Acum putem folosi Solver și în VBA.

Funcții de rezolvare în VBA

Pentru a scrie un cod VBA, trebuie să folosim trei „Funcții Solver” în VBA și acele funcții sunt „SolverOk, SolverAdd și SolverSolve”.

SolverOk

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

SetCell: Aceasta va fi referința celulei care trebuie modificată, adică celula Profit.

MaxMinVal: Acesta este un parametru opțional, mai jos sunt numere și specificatori.

  • 1 = Maximizați
  • 2 = Minimizați
  • 3 = Potriviți o anumită valoare

ValueOf: Acest parametru trebuie furnizat dacă argumentul MaxMinVal este 3.

ByChange: Schimbând ce celule, această ecuație trebuie rezolvată.

SolverAdd

Acum să vedem parametrii SolverAdd

CellRef: Pentru a stabili criteriile pentru rezolvarea problemei, ce este celula trebuie schimbată.

Relație: În acest sens, dacă valorile logice sunt satisfăcute, putem folosi numerele de mai jos.

  • 1 este mai mic decât (<=)
  • 2 este egal cu (=)
  • 3 este mai mare decât (> =)
  • 4 trebuie să aibă valori finale care sunt întregi.
  • 5 trebuie să aibă valori cuprinse între 0 sau 1.
  • 6 trebuie să aibă valori finale care sunt toate diferite și întregi.

Exemplu de Solver în Excel VBA

Pentru un exemplu, priviți scenariul de mai jos.

Folosind acest tabel, trebuie să identificăm suma „Profit”, care trebuie să fie de minimum 10000. Pentru a ajunge la acest număr avem anumite condiții.

  • Unitățile de vânzare ar trebui să fie o valoare întreagă.
  • Prețul / unitatea ar trebui să fie între 7 și 15.

Pe baza acestor condiții, trebuie să identificăm câte unități să vândem la ce preț pentru a obține valoarea profitului de 10000.

Ok, hai să rezolvăm acum această ecuație.

Step 1: Start the VBA subprocedure.

Code:

Sub Solver_Example() End Sub

Step 2: First we need to set the Objective cell reference by using the SolverOk function.

Step 3: First argument of this function is “SetCell”, in this example we need to change the value of Profit cell i.e. B8 cell.

Code:

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

Step 4: Now we need to set this cell value to 10000, so for MaxMinVal use 3 as the argument value.

Code:

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

Step 5: The next argument ValueOf value should be 10000.

Code:

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

Ok, rulați codul apăsând tasta F5 pentru a obține rezultatul.

Când rulați codul, veți vedea următoarea fereastră.

Apăsați Ok și veți obține rezultatul într-o foaie Excel.

Deci, pentru a obține un profit de 10000, trebuie să vindem 5000 de unități la 7 pe preț, unde prețul de cost este de 5.

Lucruri de amintit

  • Pentru a lucra cu Solver în Excel și VBA, mai întâi, activați-l pentru foaia de lucru, apoi activați pentru referința VBA.
  • Odată ce este activat pe ambele foi de lucru și VBA, atunci numai noi putem accesa toate funcțiile Solver.

Articole interesante...