VBA sprendėjas - Žingsnis po žingsnio „Solver“ naudojimo „Excel VBA“ pavyzdys

„Excel VBA Solver“

Kaip išspręsti sudėtingas problemas? Jei nesate tikri, kaip spręsti šias problemas, tada nieko nerimaujame, kurį turime „Excel“ sprendėjas. Ankstesniame straipsnyje „Excel Solver“ sužinojome, kaip „Excel“ spręsti lygtis. Jei nežinote, „SOLVER“ galima įsigyti ir su VBA. Šiame straipsnyje mes jums paaiškinsime, kaip naudoti „Sprendėją“ VBA.

Įgalinti „Solver“ darbalapyje

Sprendėjas yra paslėptas įrankis, pasiekiamas duomenų skirtuke „Excel“ (jei jis jau įgalintas).

Norėdami pirmiausia naudoti „SOLVER“ programoje „Excel“, turime įgalinti šią parinktį. Atlikite toliau nurodytus veiksmus.

1 žingsnis: Eikite į skirtuką Failas. Skirtuke FILE pasirinkite „Options“.

2 žingsnis: „Excel“ parinkčių lange pasirinkite „Priedai“.

3 žingsnis: Apačioje pasirenkate „Excel Add-Ins“ ir spustelėkite „Go“.

4 žingsnis: Dabar pažymėkite laukelį „Solver Add-in“ ir spustelėkite Gerai.

Dabar duomenų skirtuke turite pamatyti „Sprendėjas“.

Įjunkite „Solver“ VBA

VBA srityje „Solver“ yra išorinis įrankis; turime įgalinti jį naudotis. Norėdami jį įjungti, atlikite toliau nurodytus veiksmus.

1 žingsnis: „Visual Basic“ redaktoriaus lange eikite į Įrankiai >>> Nuoroda.

2 žingsnis: Iš nuorodų sąrašo pasirinkite „Sprendėjas“ ir spustelėkite Gerai, jei norite jį naudoti.

Dabar „Solver“ galime naudoti ir VBA.

Sprendėjo funkcijos VBA

Norėdami parašyti VBA kodą, VBA turime naudoti tris „Solver Functions“ ir šios funkcijos yra „SolverOk, SolverAdd ir SolverSolve“.

„SolverOk“

„SolverOk“ („SetCell“, „MaxMinVal“, „ValueOf“, „ByChange“, variklis, „EngineDesc“)

„SetCell“: Tai bus langelio nuoroda, kurią reikia pakeisti, ty „Pelno“ langelis.

„MaxMinVal“: tai neprivalomas parametras, žemiau pateikiami skaičiai ir specifikacijos.

  • 1 = padidinti
  • 2 = sumažinti
  • 3 = sutampa su konkrečia verte

„ValueOf“: Šis parametras turi būti pateiktas, jei „ MaxMinVal“ argumentas yra 3.

„ByChange“: keičiant langelius, reikia išspręsti šią lygtį.

Pridėti

Dabar pažiūrėkime „ SolverAdd “ parametrus

„CellRef“: norint nustatyti problemos sprendimo kriterijus, reikia pakeisti langelį.

Santykis: Jei tenkinamos loginės vertės, galime naudoti žemiau pateiktus skaičius.

  • 1 yra mažesnis nei (<=)
  • 2 yra lygus (=)
  • 3 yra didesnis nei (> =)
  • 4 is turi turėti galutines vertes, kurios yra sveiki skaičiai.
  • 5 is vertės turi būti nuo 0 iki 1.
  • 6 turi turėti visas skirtingas galutines reikšmes ir sveikus skaičius.

„Excel VBA“ sprendėjo pavyzdys

Pavyzdžiui, pažvelkite į toliau pateiktą scenarijų.

Naudodamiesi šia lentele, turime nustatyti pelno sumą, kuri turi būti mažiausiai 10000. Norėdami pasiekti šį skaičių, turime tam tikras sąlygas.

  • Vienetai, kuriuos reikia parduoti, turėtų būti sveikojo skaičiaus vertė.
  • Kaina / vienetas turėtų būti nuo 7 iki 15.

Remiantis šiomis sąlygomis, turime nustatyti, kiek vienetų parduoti už kokią kainą, kad gautume 10000 pelno vertę.

Gerai, spręskime šią lygtį dabar.

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

Gerai, paleiskite kodą paspausdami klavišą F5, kad gautumėte rezultatą.

Kai paleisite kodą, pamatysite šį langą.

Paspauskite Gerai ir rezultatą gausite „Excel“ lape.

Taigi, norėdami uždirbti 10000 pelno, turime parduoti 5000 vienetų už 7 už kainą, kai savikaina yra 5.

Ką reikia atsiminti

  • Norėdami dirbti su „Excel“ ir „VBA“ sprendėju, pirmiausia įgalinkite jį darbalapyje, tada įgalinkite VBA nuorodą.
  • Kai jis bus įjungtas abiejuose darbalapiuose ir VBA, tik mes galėsime pasiekti visas „Solver“ funkcijas.

Įdomios straipsniai...