VBA Solver - Przykład krok po kroku korzystania z dodatku Solver w programie Excel VBA

Spisie treści

Excel VBA Solver

Jak rozwiązujesz skomplikowane problemy? Jeśli nie jesteś pewien, jak rozwiązać te problemy, nie martw się, mamy rozwiązanie w naszym programie Excel. Z naszego wcześniejszego artykułu „Excel Solver” dowiedzieliśmy się, jak rozwiązywać równania w programie Excel. Jeśli nie wiesz, „SOLVER” jest również dostępny w VBA. W tym artykule pokażemy, jak używać „Solvera” w języku VBA.

Włącz dodatek Solver w arkuszu

Solver to ukryte narzędzie dostępne na karcie danych w programie Excel (jeśli jest już włączone).

Aby najpierw użyć SOLVER w programie Excel, musimy włączyć tę opcję. Wykonaj poniższe kroki.

Krok 1: Przejdź do zakładki PLIK. W zakładce PLIK wybierz „Opcje”.

Krok 2: W oknie opcji programu Excel wybierz „Dodatki”.

Krok 3: Na dole wybiera „Dodatki programu Excel” i kliknij „Idź”.

Krok 4: Teraz zaznacz pole „Dodatek Solver” i kliknij OK.

Teraz w zakładce danych musisz zobaczyć „Solver”.

Włącz Solver w VBA

Również w VBA Solver jest narzędziem zewnętrznym; musimy umożliwić mu korzystanie z niego. Wykonaj poniższe kroki, aby go włączyć.

Krok 1: Przejdź do Narzędzia >>> Odniesienie w oknie Edytora Visual Basic.

Krok 2: Z listy odniesień wybierz „Solver” i kliknij OK, aby go użyć.

Teraz możemy używać Solvera również w VBA.

Funkcje solvera w języku VBA

Aby napisać kod VBA, musimy użyć trzech „Funkcji Solvera” w VBA, a te funkcje to „SolverOk, SolverAdd i SolverSolve”.

SolverOk

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

SetCell: będzie to odwołanie do komórki, które należy zmienić, np. Komórka zysku.

MaxMinVal: jest to parametr opcjonalny, poniżej znajdują się liczby i specyfikatory.

  • 1 = Maksymalizuj
  • 2 = Minimalizuj
  • 3 = Dopasuj określoną wartość

ValueOf: ten parametr musi być podawany, jeśli argument MaxMinVal ma wartość 3.

JakZmień: Zmieniając komórki, to równanie musi zostać rozwiązane.

SolverAdd

Zobaczmy teraz parametry SolverAdd

CellRef: Aby ustawić kryteria rozwiązania problemu, należy zmienić komórkę.

Relacja: W tym przypadku, jeśli wartości logiczne są spełnione, możemy użyć poniższych liczb.

  • 1 to mniej niż (<=)
  • 2 jest równe (=)
  • 3 jest większe niż (> =)
  • 4 musi mieć końcowe wartości będące liczbami całkowitymi.
  • 5 musi mieć wartości z przedziału od 0 do 1.
  • 6 musi mieć końcowe wartości, które są różne i są liczbami całkowitymi.

Przykład dodatku Solver w Excel VBA

Na przykład spójrz na poniższy scenariusz.

Korzystając z tej tabeli, musimy określić kwotę „Zysk”, która musi wynosić co najmniej 10000. Aby otrzymać tę liczbę, musimy spełnić określone warunki.

  • Jednostki do sprzedaży powinny być liczbą całkowitą.
  • Cena / jednostka powinna wynosić od 7 do 15.

Na podstawie tych warunków musimy określić, ile jednostek sprzedać za jaką cenę, aby uzyskać wartość zysku wynoszącą 10000.

Ok, rozwiążmy teraz to równanie.

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, uruchom kod, naciskając klawisz F5, aby uzyskać wynik.

Po uruchomieniu kodu zobaczysz następujące okno.

Naciśnij Ok, a otrzymasz wynik w arkuszu Excela.

Aby więc osiągnąć zysk w wysokości 10000, musimy sprzedać 5000 jednostek po 7 za cenę, gdzie koszt własny wynosi 5.

Rzeczy do zapamiętania

  • Aby pracować z Solwerem w programie Excel i VBA, najpierw włącz go dla arkusza, a następnie włącz dla odniesienia VBA.
  • Po włączeniu w obu arkuszach roboczych i VBA tylko my mamy dostęp do wszystkich funkcji Solvera.

Interesujące artykuły...