Riešiteľ VBA - Krok za krokom príklad použitia riešenia v programe Excel VBA

Riešiteľ Excel VBA

Ako riešite komplikované problémy? Ak si nie ste istí, ako na tieto problémy, nemusíte sa ničoho obávať, máme riešiteľa v našej exceli. V našom staršom článku „Riešiteľ Excel“ sme sa naučili, ako riešiť rovnice v programe Excel. Ak neviete, „SOLVER“ je k dispozícii aj vo VBA. V tomto článku vás prevedieme tým, ako používať „Riešiteľ“ vo VBA.

Povoliť riešiteľa v pracovnom hárku

Riešiteľ je skrytý nástroj, ktorý je k dispozícii na karte údajov v programe Excel (ak je už povolený).

Ak chcete najskôr použiť SOLVER v programe Excel, musíme povoliť túto možnosť. Postupujte podľa nasledujúcich krokov.

Krok 1: Prejdite na kartu SÚBOR. Na karte SÚBOR zvoľte „Možnosti“.

Krok 2: V okne Možnosti programu Excel zvoľte „Doplnky“.

Krok 3: V dolnej časti vyberte možnosť „Doplnky programu Excel“ a kliknite na tlačidlo „Prejsť“.

Krok 4: Teraz začiarknite políčko „Doplnok riešiteľa“ a kliknite na, OK.

Teraz musíte na karte údajov vidieť „Riešiteľ“.

Povoliť riešiteľa vo VBA

Aj vo VBA je Riešiteľ externým nástrojom; musíme mu umožniť používať to. Povolte ho podľa nasledujúcich pokynov.

Krok 1: Prejdite na Nástroje >>> Odkaz v okne editora Visual Basic.

Krok 2: V zozname referencií zvoľte „Riešiteľ“ a kliknite na OK, aby ste ho mohli použiť.

Teraz môžeme použiť Riešiteľ aj vo VBA.

Funkcie riešiteľa vo VBA

Na napísanie kódu VBA musíme vo VBA použiť tri „Riešiace funkcie“ a tieto funkcie sú „SolverOk, SolverAdd a SolverSolve“.

SolverOk

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

SetCell: Toto bude odkaz na bunku, ktorý je potrebné zmeniť, tj. Zisková bunka.

MaxMinVal: Toto je voliteľný parameter, nižšie sú čísla a špecifikátory.

  • 1 = Maximalizovať
  • 2 = Minimalizovať
  • 3 = Priraďte konkrétnu hodnotu

ValueOf: Tento parameter je potrebné zadať, ak je argument MaxMinVal 3.

ByChange: Zmenou ktorých buniek je potrebné túto rovnicu vyriešiť.

SolverAdd

Teraz sa pozrime na parametre SolverAdd

CellRef: Ak chcete nastaviť kritériá na vyriešenie problému, je potrebné zmeniť to, čo je v bunke.

Vzťah: Ak sú logické hodnoty splnené, môžeme použiť čísla uvedené nižšie.

  • 1 je menej ako (<=)
  • 2 sa rovná (=)
  • 3 je väčšie ako (> =)
  • 4 is musí mať konečné hodnoty, ktoré sú celé čísla.
  • 5 musí mať hodnoty medzi 0 alebo 1.
  • 6 musí mať konečné hodnoty, ktoré sú rôzne a celé čísla.

Príklad riešiteľa v programe Excel VBA

Napríklad sa pozrite na scenár uvedený nižšie.

Pomocou tejto tabuľky musíme určiť sumu „Zisk“, ktorá musí byť minimálne 10 000. Aby sme sa dostali k tomuto číslu, máme určité podmienky.

  • Jednotky na predaj by mali byť celé číslo.
  • Cena / jednotka by mala byť medzi 7 a 15.

Na základe týchto podmienok musíme zistiť, koľko jednotiek sa má predať za akú cenu, aby sme dosiahli hodnotu zisku 10 000.

Dobre, poďme teraz vyriešiť túto rovnicu.

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

Dobre, spustite kód stlačením klávesu F5, aby ste dosiahli výsledok.

Po spustení kódu sa zobrazí nasledujúce okno.

Stlačte tlačidlo Ok a dostanete výsledok v hárku programu Excel.

Takže aby sme dosiahli zisk 10 000, musíme predať 5 000 kusov za 7 za každú cenu, kde je nákladová cena 5.

Na čo treba pamätať

  • Ak chcete pracovať so Riešiteľom v programoch Excel a VBA, najskôr ho povoľte pre pracovný hárok, potom povoľte odkaz na VBA.
  • Akonáhle je to povolené na oboch pracovných hárkoch a vo VBA, potom iba my máme prístup ku všetkým funkciám Riešiteľa.

Zaujímavé články...