VBA lahendaja - Samm-sammult näide lahendi kasutamiseks Exceli VBA-s

Lang L: none (table-of-contents)

Exceli VBA lahendaja

Kuidas keerulisi probleeme lahendate? Kui te pole kindel, kuidas nende probleemidega toime tulla, siis pole meie Excelis lahendaja midagi muret. Oma varasemas artiklis “Exceli lahendaja” oleme õppinud, kuidas võrrandeid Excelis lahendada. Kui te pole sellest teadlik, on „SOLVER” saadaval ka VBA-ga. Selles artiklis tutvustame teile, kuidas VBA-s kasutada lahendust.

Luba töölehel lahendaja

Lahendaja on peidetud tööriist, mis on Exceli andmete vahelehel saadaval (kui see on juba lubatud).

Kõigepealt SOLVERi kasutamiseks Excelis peame selle võimaluse lubama. Järgige alltoodud samme.

1. samm: minge vahekaardile FILIS. Valige vahekaardil FILI „Suvandid“.

2. samm: valige Exceli suvandite aknas „Lisandmoodulid“.

3. samm: valib allosas valiku „Exceli lisandmoodulid” ja klõpsake nupul „Mine”.

4. samm: märkige nüüd ruut „Lahendaja lisandmoodul” ja klõpsake nuppu OK.

Nüüd peate andmete vahekaardil nägema lahendajat.

Luba lahendaja VBA-s

Ka VBA-s on Solver väline tööriist; peame võimaldama tal seda kasutada. Selle lubamiseks toimige järgmiselt.

1. samm: minge Visual Basic Editor Editori aknas Tööriistad >>> Viide.

2. samm: valige viidete loendist „Lahendaja“ ja klõpsake selle kasutamiseks nuppu OK.

Nüüd saame lahendust kasutada ka VBA-s.

Lahendaja funktsioonid VBA-s

VBA-koodi kirjutamiseks peame VBA-s kasutama kolme „lahendaja funktsiooni“ ja need funktsioonid on „SolverOk, SolverAdd ja SolverSolve“.

SolverOk

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

SetCell: see on lahtriviide, mida tuleb muuta, st kasum Kasum.

MaxMinVal: see on valikuline parameeter, allpool on numbrid ja täpsustajad.

  • 1 = maksimeeri
  • 2 = minimeeri
  • 3 = sobib konkreetse väärtusega

ValueOf: See parameeter tuleb esitada, kui MaxMinVal argument on 3.

ByChange: muutes lahtrid, tuleb see võrrand lahendada.

LahendajaLisa

Nüüd vaatame SolverAddi parameetreid

CellRef: probleemi lahendamiseks kriteeriumide seadmiseks tuleb muuta seda, mis on lahter.

Seos: Kui loogilised väärtused on täidetud, siis saame kasutada järgmisi numbreid.

  • 1 on väiksem kui (<=)
  • 2 on võrdne (=)
  • 3 on suurem kui (> =)
  • 4 peab olema lõplikud väärtused, mis on täisarvud.
  • 5 on väärtused peavad olema vahemikus 0 või 1.
  • 6-l peavad olema kõik erinevad väärtused ja täisarvud.

Näide lahendajast Exceli VBA-s

Näiteks vaadake allolevat stsenaariumi.

Selle tabeli abil peame tuvastama kasumi summa, mis peab olema vähemalt 10000. Selle numbri saamiseks on meil teatud tingimused.

  • Müüdavad ühikud peaksid olema täisarv.
  • Hind / ühik peaks olema vahemikus 7 kuni 15.

Nende tingimuste põhjal peame tuvastama, mitu ühikut millise hinnaga müüa, et saada 10000 kasumiväärtus.

Ok, lahendame selle võrrandi nüüd.

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, tulemuse saamiseks käivitage kood, vajutades klahvi F5.

Koodi käivitamisel näete järgmist akent.

Vajutage Ok ja saate tulemuse Exceli lehele.

Nii et 10000 kasumi teenimiseks peame müüma 5000 ühikut hinnaga 7, kui omahind on 5.

Asjad, mida meeles pidada

  • Excelis ja VBA-s lahendiga töötamiseks lubage kõigepealt see töölehe jaoks, seejärel lubage VBA viide.
  • Kui see on mõlemal töölehel ja VBA-l lubatud, pääseme kõigile Solveri funktsioonidele juurde ainult meie.

Huvitavad Artiklid...