Επίλυση 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
Μπορείτε να κατεβάσετε αυτό το Πρότυπο VBA Solver Excel εδώ - Πρότυπο VBA Solver ExcelΓια παράδειγμα δείτε το παρακάτω σενάριο.
Χρησιμοποιώντας αυτόν τον πίνακα πρέπει να προσδιορίσουμε το ποσό "Κέρδος" που πρέπει να είναι τουλάχιστον 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
Το επόμενο επιχείρημα είναι ByChange, δηλαδή αλλάζοντας ποια κελιά πρέπει να επιλυθεί αυτή η εξίσωση. Σε αυτήν την περίπτωση, αλλάζοντας τις μονάδες προς πώληση (B1) και την τιμή ανά μονάδα (B2), το κελί πρέπει να αλλάξει.
Κώδικας:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") End Sub
Σημείωση: τα υπόλοιπα επιχειρήματα δεν απαιτούνται εδώ.
Βήμα 6: Μόλις οριστεί το αντικειμενικό κελί, τώρα πρέπει να κατασκευάσουμε άλλα κριτήρια. Για αυτήν την ανοικτή λειτουργία "SolverAdd".
Βήμα 7: Πρώτο Ref Cell που πρέπει να αλλάξουμε είναι το Price Per Unit κελί, δηλαδή το B2 cell.
Κώδικας:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2") End Sub
Βήμα 8: Αυτό το κελί πρέπει να είναι> = 7, έτσι το όρισμα Σχέση θα είναι 3.
Κώδικας:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Σχέση: = 3 End Sub
Βήμα 9: Αυτή η τιμή κελιού πρέπει να είναι> = 7 δηλ. Τύπος κειμένου = 7 .
Κώδικας:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Σχέση: = 3, FormulaText: = 7 End Sub
Βήμα 10: Παρομοίως το ίδιο κελί πρέπει να είναι μικρότερο από 15, οπότε για αυτήν τη σχέση είναι <= δηλ. 1 ως τιμή ορίσματος.
Κώδικας:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Σχέση: = 3, FormulaText: = 7 SolverAdd CellRef: = Range ("B2"), Σχέση: = 1, FormulaText: = 15 End Sub
Βήμα 11: Πρώτο κελί, δηλαδή Μονάδες προς Πώληση, πρέπει να είναι μια τιμή ακέραιου για αυτό ορίζει επίσης τα κριτήρια όπως παρακάτω.
Κώδικας:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Σχέση: = 3, FormulaText: = 7 SolverAdd CellRef: = Range ("B2"), Relation: = 1, FormulaText: = 15 SolverAdd CellRef: = Range ("B1"), Relation: = 4, FormulaText: = "Integer" End Sub
Βήμα 12: Ένα τελευταίο βήμα πρέπει να προσθέσουμε τη λειτουργία SolverSolve.
Κώδικας:
Sub Solver_Example () SolverOk SetCell: = Range ("B8"), MaxMinVal: = 3, ValueOf: = 10000, ByChange: = Range ("B1: B2") SolverAdd CellRef: = Range ("B2"), Σχέση: = 3, FormulaText: = 7 SolverAdd CellRef: = Range ("B2"), Relation: = 1, FormulaText: = 15 SolverAdd CellRef: = Range ("B1"), Relation: = 4, FormulaText: = "Integer" SolverSolve End Υπο
Εντάξει, εκτελέστε τον κωδικό πατώντας το πλήκτρο F5 για να λάβετε το αποτέλεσμα.
Όταν εκτελείτε τον κωδικό, θα δείτε το ακόλουθο παράθυρο.
Πατήστε Ok και θα λάβετε το αποτέλεσμα σε ένα φύλλο excel.
Έτσι, για να κερδίσουμε κέρδος 10000, πρέπει να πουλήσουμε 5000 μονάδες σε 7 ανά τιμή όπου η τιμή κόστους είναι 5.
Πράγματα που πρέπει να θυμάστε
- Για να εργαστείτε με το Solver στο excel & VBA, πρώτα, ενεργοποιήστε το για φύλλο εργασίας και, στη συνέχεια, ενεργοποιήστε για αναφορά VBA.
- Μόλις ενεργοποιηθεί και στα δύο φύλλα εργασίας και στο VBA τότε μόνο θα έχουμε πρόσβαση σε όλες τις λειτουργίες του Solver.
