Řešitel VBA - Krok za krokem příklad použití Řešitele v aplikaci Excel VBA

Řešitel aplikace Excel VBA

Jak řešíte složité problémy? Pokud si nejste jisti, jak tyto problémy řešit, pak se nemusíte bát, že máme řešení v naší aplikaci Excel. V našem dřívějším článku „Řešitel aplikace Excel“ jsme se naučili, jak řešit rovnice v aplikaci Excel. Pokud nevíte, „SOLVER“ je k dispozici také pro VBA. V tomto článku vás provedeme používáním „Řešitele“ ve VBA.

Povolit Řešitele v listu

Řešitel je skrytý nástroj dostupný na kartě dat v aplikaci Excel (pokud je již povolen).

Chcete-li nejprve použít SOLVER v aplikaci Excel, musíme povolit tuto možnost. Postupujte podle následujících kroků.

Krok 1: Přejděte na kartu SOUBOR. Na kartě SOUBOR zvolte „Možnosti“.

Krok 2: V okně Možnosti aplikace Excel zvolte „Doplňky“.

Krok 3: V dolní části vyberete „Doplňky aplikace Excel“ a klikněte na „Přejít“.

Krok 4: Nyní zaškrtněte políčko „Doplněk Řešitele“ a klikněte na, Ok.

Nyní musíte na kartě s daty vidět „Řešitel“.

Povolit Řešitele ve VBA

Také ve VBA je Solver externím nástrojem; musíme mu umožnit to používat. Povolte jej podle níže uvedených kroků.

Krok 1: Přejít na Nástroje >>> Reference v okně editoru jazyka.

Krok 2: V seznamu odkazů zvolte „Řešitel“ a klikněte na Ok pro jeho použití.

Nyní můžeme použít Řešitel i ve VBA.

Funkce řešiče ve VBA

K napsání kódu VBA musíme ve VBA použít tři funkce „Řešitele“ a tyto funkce jsou „SolverOk, SolverAdd a SolverSolve“.

Řešitel

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

SetCell: Toto bude odkaz na buňku, který je třeba změnit, tj. Zisková buňka.

MaxMinVal: Toto je volitelný parametr, níže jsou čísla a specifikátory.

  • 1 = Maximalizovat
  • 2 = Minimalizovat
  • 3 = Přiřadit konkrétní hodnotu

ValueOf: Tento parametr musí být zadán, pokud je argument MaxMinVal 3.

ByChange: Změnou, které buňky je třeba tuto rovnici vyřešit.

ŘešitelPřidat

Nyní se podívejme na parametry SolverAdd

CellRef: Chcete-li nastavit kritéria pro vyřešení problému, je třeba změnit buňku.

Vztah: Pokud jsou logické hodnoty splněny, můžeme použít níže uvedená čísla.

  • 1 je menší než (<=)
  • 2 se rovná (=)
  • 3 je větší než (> =)
  • 4 is must have final values ​​that are integers.
  • 5 musí mít hodnoty mezi 0 nebo 1.
  • 6 is must have final values ​​that are different and integers.

Příklad řešení v aplikaci Excel VBA

Například se podívejte na níže uvedený scénář.

Pomocí této tabulky musíme určit částku „Zisk“, která musí být minimálně 10 000. K dosažení tohoto čísla máme určité podmínky.

  • Jednotky k prodeji by měly být celočíselnou hodnotou.
  • Cena / jednotka by měla být mezi 7 a 15.

Na základě těchto podmínek musíme určit, kolik jednotek za jakou cenu prodat, abychom získali hodnotu zisku 10 000.

Dobře, pojďme nyní vyřešit tuto rovnici.

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

Dobře, spusťte kód stisknutím klávesy F5, abyste získali výsledek.

Po spuštění kódu se zobrazí následující okno.

Stiskněte OK a výsledek získáte v listu aplikace Excel.

Abychom získali zisk 10 000, musíme prodat 5 000 jednotek za 7 za každou cenu, kde je cena 5.

Věci k zapamatování

  • Chcete-li pracovat s Řešitelem v Excelu a VBA, nejprve jej povolte pro list a poté povolte pro odkaz VBA.
  • Jakmile je povoleno na obou pracovních listech a VBA, pak máme přístup ke všem funkcím Řešitele pouze my.

Zajímavé články...