VBA risinātājs Soli pa solim Solver izmantošanas piemērs programmā Excel VBA

Satura rādītājs

Excel VBA risinātājs

Kā jūs atrisināt sarežģītas problēmas? Ja neesat pārliecināts, kā risināt šīs problēmas, tad nekas nav jāuztraucas. Mūsu agrākajā rakstā “Excel Solver” mēs esam iemācījušies, kā atrisināt vienādojumus programmā Excel. Ja jūs nezināt, “SOLVER” ir pieejams arī ar VBA. Šajā rakstā mēs iepazīstināsim jūs ar to, kā VBA izmantot “Solver”.

Iespējot risinājumu darblapā

Risinātājs ir slēpts rīks, kas pieejams Excel datu cilnē (ja tas jau ir iespējots).

Lai vispirms izmantotu SOLVER programmā Excel, šī opcija ir jāiespējo. Veiciet tālāk norādītās darbības.

1. darbība: dodieties uz cilni FILE. Cilnē FILS izvēlieties “Opcijas”.

2. solis: Excel opciju logā izvēlieties “Add-Ins”.

3. solis: Apakšā izvēlieties “Excel pievienojumprogrammas” un noklikšķiniet uz “Iet”.

4. solis: Tagad atzīmējiet izvēles rūtiņu “Solver Add-in” un noklikšķiniet uz Labi.

Tagad datu cilnē ir jāredz “Solver”.

Iespējot risinātāju VBA

Arī VBA risinātājs ir ārējs rīks; mums jāļauj tai to izmantot. Lai to iespējotu, veiciet tālāk norādītās darbības.

1. darbība: Visual Basic redaktora logā dodieties uz Rīki >>> Atsauce.

2. solis: no atsauču saraksta izvēlieties “Solver” un noklikšķiniet uz Labi, lai to izmantotu.

Tagad mēs varam izmantot Solver arī VBA.

Risinātāja funkcijas VBA

Lai rakstītu VBA kodu, VBA ir jāizmanto trīs “Solver Functions”, un šīs funkcijas ir “SolverOk, SolverAdd un SolverSolve”.

SolverOk

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

SetCell: Šī būs šūnu atsauce, kas jāmaina, ti, peļņas šūna.

MaxMinVal: Šis ir izvēles parametrs, zemāk ir skaitļi un specifikatori.

  • 1 = palielināt
  • 2 = minimizēt
  • 3 = atbilst noteiktai vērtībai

ValueOf: Šis parametrs jānorāda, ja MaxMinVal arguments ir 3.

ByChange: Mainot, kuras šūnas, šis vienādojums ir jāatrisina.

SolverAdd

Tagad apskatīsim SolverAdd parametrus

CellRef: Lai iestatītu kritērijus problēmas risināšanai, ir jāmaina šūna.

Saistība: Ja loģiskās vērtības ir apmierinātas, mēs varam izmantot šādus skaitļus.

  • 1 ir mazāks par (<=)
  • 2 ir vienāds ar (=)
  • 3 ir lielāks par (> =)
  • 4 ir jābūt galīgajām vērtībām, kas ir veseli skaitļi.
  • 5 ir jābūt vērtībām starp 0 vai 1.
  • 6 ir jābūt visām atšķirīgajām galīgajām vērtībām un veseliem skaitļiem.

Risinātāja piemērs programmā Excel VBA

Piemēram, apskatiet zemāk redzamo scenāriju.

Izmantojot šo tabulu, mums jāidentificē “Peļņas” summa, kurai jābūt vismaz 10000. Lai sasniegtu šo skaitli, mums ir noteikti nosacījumi.

  • Pārdodamo vienību vērtībai jābūt veselai skaitlim.
  • Cenai / vienībai jābūt no 7 līdz 15.

Pamatojoties uz šiem nosacījumiem, mums jānosaka, cik vienību par kādu cenu pārdot, lai iegūtu 10000 peļņas vērtību.

Labi, atrisināsim šo vienādojumu tūlīt.

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

Labi, palaidiet kodu, nospiežot taustiņu F5, lai iegūtu rezultātu.

Palaidot kodu, tiks parādīts šāds logs.

Nospiediet Labi, un jūs iegūsiet rezultātu Excel lapā.

Tātad, lai gūtu 10000 peļņu, mums jāpārdod 5000 vienību par 7 par cenu, ja pašizmaksa ir 5.

Atceramās lietas

  • Lai strādātu ar Solver programmā Excel un VBA, vispirms iespējojiet to darblapai, pēc tam iespējojiet VBA atsauci.
  • Kad tas ir iespējots abās darblapās un VBA, tikai mēs varam piekļūt visām Solver funkcijām.

Interesanti raksti...