Επίλυση VBA - Βήμα προς βήμα Παράδειγμα χρήσης του Solver στο Excel VBA

Πίνακας περιεχομένων

Επίλυση Excel VBA

Πώς λύνεις περίπλοκα προβλήματα; Εάν δεν είστε σίγουροι πώς να αντιμετωπίσετε αυτά τα προβλήματα, τότε δεν πρέπει να ανησυχείτε ότι έχουμε λύσει στο Excel. Στο προηγούμενο άρθρο μας "Excel Solver" μάθαμε πώς να λύσουμε εξισώσεις στο Excel. Εάν δεν γνωρίζετε, το "SOLVER" διατίθεται και με το VBA. Σε αυτό το άρθρο, θα σας καθοδηγήσουμε στον τρόπο χρήσης του "Solver" στο VBA.

Ενεργοποίηση λύσης στο φύλλο εργασίας

Ο επιλυτής είναι ένα κρυφό εργαλείο που είναι διαθέσιμο στην καρτέλα δεδομένων στο excel (εάν είναι ήδη ενεργοποιημένο).

Για να χρησιμοποιήσουμε πρώτα το SOLVER στο excel, πρέπει να ενεργοποιήσουμε αυτήν την επιλογή. Ακολουθήστε τα παρακάτω βήματα.

Βήμα 1: Μεταβείτε στην καρτέλα FILE. Στην καρτέλα FILE επιλέξτε "Επιλογές".

Βήμα 2: Στο παράθυρο Επιλογές του Excel επιλέξτε "Πρόσθετα".

Βήμα 3: Στο κάτω μέρος επιλέγει "Πρόσθετα Excel" και κάντε κλικ στο "Μετάβαση".

Βήμα 4: Τώρα επιλέξτε το πλαίσιο "Solver Add-in" και κάντε κλικ στο, Ok.

Τώρα πρέπει να δείτε το "Solver" κάτω από την καρτέλα δεδομένων.

Ενεργοποιήστε το Solver στο VBA

Και στο VBA, το Solver είναι ένα εξωτερικό εργαλείο. πρέπει να το επιτρέψουμε για να το χρησιμοποιήσουμε. Ακολουθήστε τα παρακάτω βήματα για να το ενεργοποιήσετε.

Βήμα 1: Μεταβείτε στην ενότητα Εργαλεία >>> Αναφορά στο παράθυρο επεξεργασίας της Visual Basic.

Βήμα 2: Από τη λίστα αναφορών, επιλέξτε "Solver" και κάντε κλικ στο Ok για να το χρησιμοποιήσετε.

Τώρα μπορούμε να χρησιμοποιήσουμε το Solver και στο VBA.

Λειτουργίες επίλυσης στο VBA

Για να γράψουμε έναν κωδικό VBA πρέπει να χρησιμοποιήσουμε τρεις "Λειτουργίες Solver" στο VBA και αυτές οι λειτουργίες είναι "SolverOk, SolverAdd και SolverSolve".

SolverOk

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

SetCell: Αυτή θα είναι η αναφορά κελιού που πρέπει να αλλάξει, δηλαδή Κελί κέρδους.

MaxMinVal: Αυτή είναι μια προαιρετική παράμετρος, παρακάτω είναι αριθμοί και προσδιοριστές.

  • 1 = Μεγιστοποίηση
  • 2 = Ελαχιστοποίηση
  • 3 = Ταιριάξτε μια συγκεκριμένη τιμή

ValueOf: Αυτή η παράμετρος πρέπει να παρέχει εάν το όρισμα MaxMinVal είναι 3.

ByChange: Αλλάζοντας ποια κελιά, αυτή η εξίσωση πρέπει να λυθεί.

SolverΠροσθήκη

Τώρα ας δούμε τις παραμέτρους του SolverAdd

CellRef: Για να ορίσετε τα κριτήρια για την επίλυση του προβλήματος, ποιο είναι το κελί που πρέπει να αλλάξει.

Σχέση: Σε αυτό, εάν ικανοποιηθούν οι λογικές τιμές τότε μπορούμε να χρησιμοποιήσουμε τους παρακάτω αριθμούς.

  • 1 είναι μικρότερο από (<=)
  • 2 είναι ίσο με (=)
  • 3 είναι μεγαλύτερο από (> =)
  • 4 είναι πρέπει να έχει τελικές τιμές που είναι ακέραιοι.
  • 5 είναι πρέπει να έχει τιμές μεταξύ 0 ή 1.
  • 6 είναι πρέπει να έχει τελικές τιμές που είναι όλες διαφορετικές και ακέραιοι.

Παράδειγμα Solver στο Excel VBA

Για παράδειγμα δείτε το παρακάτω σενάριο.

Χρησιμοποιώντας αυτόν τον πίνακα, πρέπει να προσδιορίσουμε το ποσό "Κέρδος", το οποίο πρέπει να είναι τουλάχιστον 10000. Για να φτάσουμε σε αυτόν τον αριθμό έχουμε συγκεκριμένες προϋποθέσεις.

  • Οι μονάδες προς πώληση πρέπει να είναι ακέραιες τιμές.
  • Η τιμή / μονάδα πρέπει να είναι μεταξύ 7 και 15.

Με βάση αυτές τις προϋποθέσεις, πρέπει να προσδιορίσουμε πόσες μονάδες θα πουλήσουμε σε τι τιμή για να πάρουμε την τιμή κέρδους των 10000.

Εντάξει, ας λύσουμε αυτήν την εξίσωση τώρα.

Βήμα 1: Ξεκινήστε τη δευτερεύουσα διαδικασία VBA.

Κώδικας:

Sub Solver_Example () End Sub

Βήμα 2: Πρώτα πρέπει να ορίσουμε την αναφορά αντικειμενικού κελιού χρησιμοποιώντας τη λειτουργία SolverOk

Βήμα 3: Το πρώτο όρισμα αυτής της συνάρτησης είναι το "SetCell", σε αυτό το παράδειγμα πρέπει να αλλάξουμε την τιμή του κελιού κέρδους, δηλαδή του κελιού B8.

Κώδικας:

Sub Solver_Example () SolverOk SetCell: = Range ("B8") End Sub

Βήμα 4: Τώρα πρέπει να ορίσουμε αυτήν την τιμή κελιού σε 10000, οπότε για τη χρήση του MaxMinVal 3 ως τιμή ορίσματος.

Κώδικας:

Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3 End Sub

Βήμα 5: Το επόμενο όρισμα τιμή ValueOf πρέπει να είναι 10000.

Κώδικας:

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

Εντάξει, εκτελέστε τον κωδικό πατώντας το πλήκτρο F5 για να λάβετε το αποτέλεσμα.

Όταν εκτελείτε τον κωδικό, θα δείτε το ακόλουθο παράθυρο.

Πατήστε Ok και θα λάβετε το αποτέλεσμα σε ένα φύλλο excel.

Έτσι, για να κερδίσουμε κέρδος 10000, πρέπει να πουλήσουμε 5000 μονάδες σε 7 ανά τιμή όπου η τιμή κόστους είναι 5.

Πράγματα που πρέπει να θυμάστε

  • Για να εργαστείτε με το Solver στο excel & VBA, πρώτα, ενεργοποιήστε το για φύλλο εργασίας και, στη συνέχεια, ενεργοποιήστε για αναφορά VBA.
  • Μόλις ενεργοποιηθεί και στα δύο φύλλα εργασίας και στο VBA τότε μόνο θα έχουμε πρόσβαση σε όλες τις λειτουργίες του Solver.

ενδιαφέροντα άρθρα...