Τι είναι το Excel Solver;

2024 Συγγραφέας: Abigail Brown | [email protected]. Τελευταία τροποποίηση: 2023-12-17 06:44

Το πρόσθετο Excel Solver εκτελεί μαθηματική βελτιστοποίηση. Αυτό χρησιμοποιείται συνήθως για την προσαρμογή σύνθετων μοντέλων σε δεδομένα ή την εύρεση επαναληπτικών λύσεων σε προβλήματα. Για παράδειγμα, μπορεί να θέλετε να προσαρμόσετε μια καμπύλη σε ορισμένα σημεία δεδομένων, χρησιμοποιώντας μια εξίσωση. Ο Επίλυση μπορεί να βρει τις σταθερές στην εξίσωση που δίνουν την καλύτερη προσαρμογή στα δεδομένα. Μια άλλη εφαρμογή είναι όπου είναι δύσκολο να αναδιατάξετε ένα μοντέλο ώστε η απαιτούμενη έξοδος να γίνει αντικείμενο μιας εξίσωσης.

Πού είναι το Solver στο Excel;

Το πρόσθετο Solver περιλαμβάνεται στο Excel, αλλά δεν φορτώνεται πάντα ως μέρος μιας προεπιλεγμένης εγκατάστασης. Για να ελέγξετε αν έχει φορτωθεί, επιλέξτε την καρτέλα DATA και αναζητήστε το εικονίδιο Solver στην ενότητα Analysis.

Αν δεν μπορείτε να βρείτε το Επίλυση στην καρτέλα ΔΕΔΟΜΕΝΑ, τότε θα χρειαστεί να φορτώσετε το πρόσθετο:

1. Επιλέξτε την καρτέλα FILE και, στη συνέχεια, επιλέξτε Επιλογές.

   

2. Στο πλαίσιο διαλόγου Επιλογές επιλέξτε Add-Ins από τις καρτέλες στην αριστερή πλευρά.

   

3. Στο κάτω μέρος του παραθύρου, επιλέξτε Excel Add-ins από το αναπτυσσόμενο μενού Manage και επιλέξτε Μετάβαση…

   

4. Επιλέξτε το πλαίσιο ελέγχου δίπλα στο Solver Add-in και επιλέξτε OK.

   

5. Η εντολή Solver θα πρέπει τώρα να εμφανίζεται στην καρτέλα DATA. Είστε έτοιμοι να χρησιμοποιήσετε το Solver.

   

Χρήση του Solver στο Excel

Ας ξεκινήσουμε με ένα απλό παράδειγμα για να καταλάβουμε τι κάνει ο Επίλυση. Φανταστείτε ότι θέλουμε να μάθουμε ποια ακτίνα θα δώσει ένας κύκλος με εμβαδόν 50 τετραγωνικών μονάδων. Γνωρίζουμε την εξίσωση για το εμβαδόν ενός κύκλου (A=pi r²). Θα μπορούσαμε, φυσικά, να αναδιατάξουμε αυτήν την εξίσωση για να δώσουμε την ακτίνα που απαιτείται για μια δεδομένη περιοχή, αλλά για χάρη του παραδείγματος ας προσποιηθούμε ότι δεν ξέρουμε πώς να το κάνουμε αυτό.

Δημιουργήστε ένα υπολογιστικό φύλλο με την ακτίνα σε B1 και υπολογίστε την περιοχή σε B2 χρησιμοποιώντας την εξίσωση =pi()B1^2.

Μπορούσαμε να προσαρμόσουμε με μη αυτόματο τρόπο την τιμή στο B1 έως ότου το B2 εμφανίσει μια τιμή που είναι αρκετά κοντά στο 50. Ανάλογα με την ακρίβεια πρέπει να είναι, αυτή μπορεί να είναι μια πρακτική προσέγγιση. Ωστόσο, εάν πρέπει να είμαστε πολύ ακριβείς, θα χρειαστεί πολύς χρόνος για να γίνουν οι απαιτούμενες προσαρμογές. Στην πραγματικότητα, αυτό είναι ουσιαστικά αυτό που κάνει ο Solver. Πραγματοποιεί προσαρμογές σε τιμές σε ορισμένα κελιά και ελέγχει την τιμή σε ένα κελί προορισμού:

1. Επιλέξτε την καρτέλα DATA και Solver, για να φορτώσετε το Παράμετροι επίλυσης πλαίσιο διαλόγου

2. Ορίστε το Objective κελί ως την περιοχή, B2. Αυτή είναι η τιμή που θα ελεγχθεί, προσαρμόζοντας άλλα κελιά έως ότου αυτό φτάσει στη σωστή τιμή.

   

3. Επιλέξτε το κουμπί για Τιμή του: και ορίστε μια τιμή 50. Αυτή είναι η τιμή που πρέπει να επιτύχει το B2.

   

4. Στο πλαίσιο με τίτλο Με αλλαγή μεταβλητών κελιών: εισαγάγετε το κελί που περιέχει την ακτίνα, B1.

   

5. Αφήστε τις άλλες επιλογές ως έχουν από προεπιλογή και επιλέξτε Solve. Η βελτιστοποίηση πραγματοποιείται, η τιμή του B1 προσαρμόζεται έως ότου το B2 είναι 50 και εμφανίζεται ο διάλογος Solver Results.

   

6. Επιλέξτε OK για να διατηρήσετε τη λύση.

   

Αυτό το απλό παράδειγμα έδειξε πώς λειτουργεί ο λύτης. Σε αυτή την περίπτωση, θα μπορούσαμε να είχαμε πιο εύκολα τη λύση με άλλους τρόπους. Στη συνέχεια θα δούμε μερικά παραδείγματα όπου το Solver δίνει λύσεις που θα ήταν δύσκολο να βρεθούν με άλλο τρόπο.

Τοποθέτηση σύνθετου μοντέλου με χρήση του πρόσθετου επίλυσης Excel

Το

Το Excel έχει μια ενσωματωμένη λειτουργία για την εκτέλεση γραμμικής παλινδρόμησης, τοποθετώντας μια ευθεία γραμμή μέσω ενός συνόλου δεδομένων. Πολλές κοινές μη γραμμικές συναρτήσεις μπορούν να γραμμικοποιηθούν που σημαίνει ότι η γραμμική παλινδρόμηση μπορεί να χρησιμοποιηθεί για την προσαρμογή συναρτήσεων όπως οι εκθετικές. Για πιο σύνθετες συναρτήσεις, ο Επίλυση μπορεί να χρησιμοποιηθεί για την εκτέλεση μιας «ελαχιστοποίησης ελαχίστων τετραγώνων». Σε αυτό το παράδειγμα, θα εξετάσουμε την προσαρμογή μιας εξίσωσης της μορφής ax^b+cx^d στα δεδομένα που φαίνονται παρακάτω.

Αυτό περιλαμβάνει τα ακόλουθα βήματα:

1. Ταξινομήστε το σύνολο δεδομένων με τις τιμές x στη στήλη Α και τις τιμές y στη στήλη Β.
2. Δημιουργήστε τις 4 τιμές συντελεστών (a, b, c και d) κάπου στο υπολογιστικό φύλλο, μπορούν να δοθούν αυθαίρετες αρχικές τιμές.

3. Δημιουργήστε μια στήλη προσαρμοσμένων τιμών Y, χρησιμοποιώντας μια εξίσωση της μορφής ax^b+cx^d που αναφέρεται στους συντελεστές που δημιουργήθηκαν στο βήμα 2 και στις τιμές x στη στήλη Α. Σημειώστε ότι για να αντιγράψετε τον τύπο προς τα κάτω στη στήλη, οι αναφορές στους συντελεστές πρέπει να είναι απόλυτες ενώ οι αναφορές στις τιμές x πρέπει να είναι σχετικές.

   

4. Αν και δεν είναι απαραίτητο, μπορείτε να λάβετε μια οπτική ένδειξη για το πόσο καλή ταιριάζει η εξίσωση σχεδιάζοντας και τις δύο στήλες y έναντι των τιμών x σε ένα μεμονωμένο διάγραμμα διασποράς XY. Είναι λογικό να χρησιμοποιείτε δείκτες για τα αρχικά σημεία δεδομένων, καθώς πρόκειται για διακριτές τιμές με θόρυβο, και να χρησιμοποιείτε μια γραμμή για την προσαρμοσμένη εξίσωση.

   

5. Στη συνέχεια, χρειαζόμαστε έναν τρόπο να ποσοτικοποιήσουμε τη διαφορά μεταξύ των δεδομένων και της προσαρμοσμένης εξίσωσης. Ο τυπικός τρόπος για να γίνει αυτό είναι να υπολογίσετε το άθροισμα των τετραγωνικών διαφορών. Σε μια τρίτη στήλη, για κάθε γραμμή, η αρχική τιμή δεδομένων για το Y αφαιρείται από την προσαρμοσμένη τιμή της εξίσωσης και το αποτέλεσμα τετραγωνίζεται. Έτσι, στο D2, η τιμή δίνεται με =(C2-B2)^2 Στη συνέχεια υπολογίζεται το άθροισμα όλων αυτών των τετραγωνικών τιμών. Δεδομένου ότι οι τιμές είναι στο τετράγωνο, μπορούν να είναι μόνο θετικές.

   

6. Είστε έτοιμοι να εκτελέσετε τη βελτιστοποίηση χρησιμοποιώντας το Επίλυση. Υπάρχουν τέσσερις συντελεστές που πρέπει να προσαρμοστούν (α, β, γ και δ). Έχετε επίσης μια ενιαία αντικειμενική τιμή για ελαχιστοποίηση, το άθροισμα των τετραγωνικών διαφορών. Εκκινήστε τον λύτη, όπως παραπάνω, και ορίστε τις παραμέτρους του λύτη ώστε να αναφέρονται σε αυτές τις τιμές, όπως φαίνεται παρακάτω.

   

7. Αποεπιλέξτε την επιλογή Κάντε τις μη περιορισμένες μεταβλητές μη αρνητικές, αυτό θα αναγκάσει όλους τους συντελεστές να λάβουν θετικές τιμές.

   

8. Επιλέξτε Solve και ελέγξτε τα αποτελέσματα. Το γράφημα θα ενημερωθεί δίνοντας μια καλή ένδειξη της καλής εφαρμογής. Εάν ο λύτης δεν έχει καλή εφαρμογή στην πρώτη προσπάθεια, μπορείτε να δοκιμάσετε να το εκτελέσετε ξανά. Εάν η προσαρμογή έχει βελτιωθεί, δοκιμάστε να επιλύσετε από τις τρέχουσες τιμές. Διαφορετικά, θα μπορούσατε να δοκιμάσετε να βελτιώσετε χειροκίνητα την εφαρμογή πριν την επίλυση.

   

9. Μόλις επιτευχθεί μια καλή εφαρμογή, μπορείτε να βγείτε από τον λύτη.

Επαναληπτική επίλυση ενός μοντέλου

Μερικές φορές υπάρχει μια σχετικά απλή εξίσωση που δίνει μια έξοδο ως προς κάποια είσοδο. Ωστόσο, όταν προσπαθούμε να αντιστρέψουμε το πρόβλημα δεν είναι δυνατόν να βρούμε μια απλή λύση. Για παράδειγμα, η ισχύς που καταναλώνεται από ένα όχημα δίνεται κατά προσέγγιση με P=av + bv^3 όπου v είναι η ταχύτητα, a είναι ένας συντελεστής για την αντίσταση κύλισης και b είναι ένας συντελεστής για αεροδυναμική αντίσταση. Αν και αυτή είναι μια αρκετά απλή εξίσωση, δεν είναι εύκολο να αναδιατάξουμε μια εξίσωση της ταχύτητας που θα φτάσει το όχημα για μια δεδομένη ισχύ εισόδου. Μπορούμε, ωστόσο, να χρησιμοποιήσουμε το Solver για να βρούμε επαναληπτικά αυτήν την ταχύτητα. Για παράδειγμα, βρείτε την ταχύτητα που επιτυγχάνεται με ισχύ εισόδου 740 W.

1. Ρυθμίστε ένα απλό υπολογιστικό φύλλο με την ταχύτητα, τους συντελεστές a και b και την ισχύ που υπολογίζεται από αυτούς.

   

2. Εκκινήστε το Επίλυση και εισαγάγετε την ισχύ, B5, ως στόχο. Ορίστε μια αντικειμενική τιμή 740 και επιλέξτε την ταχύτητα, B2, ως τα μεταβλητά κελιά προς αλλαγή. Επιλέξτε solve για να ξεκινήσετε τη λύση.

   

3. Ο λύτης προσαρμόζει την τιμή της ταχύτητας έως ότου η ισχύς πλησιάσει πολύ το 740, παρέχοντας την ταχύτητα που χρειαζόμαστε.

   

4. Η επίλυση μοντέλων με αυτόν τον τρόπο μπορεί συχνά να είναι ταχύτερη και λιγότερο επιρρεπής σε σφάλματα από την αναστροφή σύνθετων μοντέλων.

Η κατανόηση των διαφορετικών επιλογών που είναι διαθέσιμες στο πρόγραμμα επίλυσης μπορεί να είναι αρκετά δύσκολη. Εάν δυσκολεύεστε να βρείτε μια λογική λύση, τότε είναι συχνά χρήσιμο να εφαρμόζετε οριακές συνθήκες στα μεταβλητά κελιά. Αυτές είναι οριακές τιμές πέραν των οποίων δεν πρέπει να προσαρμόζονται. Για παράδειγμα, στο προηγούμενο παράδειγμα, η ταχύτητα δεν πρέπει να είναι μικρότερη από το μηδέν και θα ήταν επίσης δυνατό να οριστεί ένα άνω όριο. Αυτή θα ήταν μια ταχύτητα από την οποία είστε σίγουροι ότι το όχημα δεν μπορεί να πάει πιο γρήγορα. Εάν μπορείτε να ορίσετε όρια για τα μεταβλητά κελιά με δυνατότητα αλλαγής, τότε αυτό κάνει και άλλες πιο προηγμένες επιλογές να λειτουργούν καλύτερα, όπως η πολλαπλή εκκίνηση. Αυτό θα εκτελέσει μια σειρά από διαφορετικές λύσεις, ξεκινώντας από διαφορετικές αρχικές τιμές για τις μεταβλητές.

Η επιλογή της μεθόδου επίλυσης μπορεί επίσης να είναι δύσκολη. Το Simplex LP είναι κατάλληλο μόνο για γραμμικά μοντέλα, εάν το πρόβλημα δεν είναι γραμμικό, θα αποτύχει με ένα μήνυμα ότι αυτή η προϋπόθεση δεν πληρούται. Οι άλλες δύο μέθοδοι είναι και οι δύο κατάλληλες για μη γραμμικές μεθόδους. Το GRG Nonlinear είναι το ταχύτερο, αλλά η λύση του μπορεί να εξαρτάται σε μεγάλο βαθμό από τις αρχικές συνθήκες εκκίνησης. Έχει την ευελιξία ότι δεν απαιτεί από τις μεταβλητές να έχουν οριστεί όρια. Ο Εξελικτικός λύτης είναι συχνά ο πιο αξιόπιστος, αλλά απαιτεί όλες οι μεταβλητές να έχουν και άνω και κάτω όρια, κάτι που μπορεί να είναι δύσκολο να υπολογιστεί εκ των προτέρων.

Το πρόσθετο Excel Solver είναι ένα πολύ ισχυρό εργαλείο που μπορεί να εφαρμοστεί σε πολλά πρακτικά προβλήματα. Για πλήρη πρόσβαση στη δύναμη του Excel, δοκιμάστε να συνδυάσετε το Solver με τις μακροεντολές του Excel.