Επίλυση συνάρτησης στο Excel. Ενεργοποίηση, χρήση θήκης με στιγμιότυπα οθόνης

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

Τι είναι η Αναζήτηση λύσεων

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

Πώς να ενεργοποιήσετε τη λειτουργία "Αναζήτηση λύσης".

Παρά την αποτελεσματικότητα, η εν λόγω επιλογή δεν βρίσκεται σε εμφανές σημείο στη γραμμή εργαλείων ή στο μενού περιβάλλοντος. Οι περισσότεροι χρήστες που εργάζονται στο Excel δεν γνωρίζουν την παρουσία του. Από προεπιλογή, αυτή η λειτουργία είναι απενεργοποιημένη, για να την εμφανίσετε, εκτελέστε τις ακόλουθες ενέργειες:

1. Ανοίξτε το «Αρχείο» κάνοντας κλικ στο κατάλληλο όνομα.
2. Κάντε κλικ στην ενότητα "Ρυθμίσεις".
3. Στη συνέχεια, επιλέξτε την υποενότητα "Πρόσθετα". Όλα τα πρόσθετα του προγράμματος θα εμφανιστούν εδώ, η επιγραφή "Διαχείριση" θα εμφανιστεί παρακάτω. Στη δεξιά πλευρά του θα υπάρχει ένα αναδυόμενο μενού όπου θα πρέπει να επιλέξετε "Πρόσθετα Excel". Στη συνέχεια κάντε κλικ στο «Μετάβαση».
   

   

4. Ένα επιπλέον παράθυρο "Πρόσθετα" θα εμφανιστεί στην οθόνη. Επιλέξτε το πλαίσιο δίπλα στην επιθυμητή λειτουργία και κάντε κλικ στο OK.
5. Η επιθυμητή λειτουργία θα εμφανιστεί στην κορδέλα στα δεξιά της ενότητας «Δεδομένα».

Σχετικά με τα μοντέλα

Αυτές οι πληροφορίες θα είναι εξαιρετικά χρήσιμες για όσους μόλις εξοικειωθούν με την έννοια του «μοντέλου βελτιστοποίησης». Πριν χρησιμοποιήσετε την "Αναζήτηση λύσης", συνιστάται να μελετήσετε τα υλικά σχετικά με τις μεθόδους κατασκευής μοντέλων:

• η υπό εξέταση επιλογή θα επιτρέψει τον εντοπισμό της καλύτερης μεθόδου για την κατανομή κεφαλαίων για επενδύσεις, φόρτωση των εγκαταστάσεων, προμήθεια αγαθών ή άλλες ενέργειες όπου είναι απαραίτητο να βρεθεί η καλύτερη λύση.
• Η «βέλτιστη μέθοδος» σε μια τέτοια κατάσταση θα σήμαινε: αύξηση του εισοδήματος, μείωση του κόστους, βελτίωση της ποιότητας κ.λπ.

Τυπικές εργασίες βελτιστοποίησης:

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

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

Προπαρασκευαστικό στάδιο

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

Καθήκον μας θα είναι να υπολογίσουμε την έκπτωση με την οποία πολλαπλασιάζονται τα ποσά για την πώληση διαφόρων προϊόντων. Αυτά τα 2 στοιχεία συνδέονται με έναν τύπο που γράφεται ως εξής: =D13*$G$2. Όπου στο D13 γράφεται η συνολική ποσότητα για την υλοποίηση και το $G$2 είναι η διεύθυνση του επιθυμητού στοιχείου.

Χρήση της λειτουργίας και ρύθμιση της

Όταν ο τύπος είναι έτοιμος, πρέπει να χρησιμοποιήσετε απευθείας την ίδια τη συνάρτηση:

1. Πρέπει να μεταβείτε στην ενότητα "Δεδομένα" και να κάνετε κλικ στην "Αναζήτηση λύσης".

2. Θα ανοίξει το "Επιλογές", όπου έχουν οριστεί οι απαιτούμενες ρυθμίσεις. Στη γραμμή "Βελτιστοποίηση της αντικειμενικής συνάρτησης:" πρέπει να καθορίσετε το κελί όπου εμφανίζεται το άθροισμα των εκπτώσεων. Είναι δυνατό να συνταγογραφήσετε τις συντεταγμένες μόνοι σας ή να επιλέξετε από το έγγραφο.

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

4. Στη συνέχεια συμπληρώνεται το πεδίο «Αλλαγή των τιμών των μεταβλητών:». Εδώ εισάγονται τα δεδομένα του επιθυμητού κελιού, το οποίο περιέχει μια συγκεκριμένη τιμή. Οι συντεταγμένες καταχωρούνται ανεξάρτητα ή γίνεται κλικ στο αντίστοιχο κελί του εγγράφου.

5. Στη συνέχεια γίνεται επεξεργασία της καρτέλας "Σύμφωνα με περιορισμούς:", όπου ορίζονται οι περιορισμοί στα δεδομένα που εφαρμόζονται. Για παράδειγμα, εξαιρούνται δεκαδικά κλάσματα ή αρνητικοί αριθμοί.

6. Μετά από αυτό, ανοίγει ένα παράθυρο που σας επιτρέπει να προσθέσετε περιορισμούς στους υπολογισμούς. Η αρχική γραμμή περιέχει τις συντεταγμένες ενός κελιού ή μιας ολόκληρης περιοχής. Ακολουθώντας τις συνθήκες της εργασίας, υποδεικνύονται τα δεδομένα του επιθυμητού κελιού, όπου εμφανίζεται η ένδειξη έκπτωσης. Στη συνέχεια καθορίζεται το πρόσημο σύγκρισης. Έχει οριστεί σε "μεγαλύτερο από ή ίσο με" έτσι ώστε η τελική τιμή να μην είναι με πρόσημο μείον. Το "Όριο" που ορίζεται στη γραμμή 3 είναι 0 σε αυτήν την περίπτωση. Είναι επίσης δυνατό να ορίσετε ένα όριο με το "Add". Τα επόμενα βήματα είναι τα ίδια.

7. Όταν ολοκληρωθούν τα παραπάνω βήματα, το καθορισμένο όριο εμφανίζεται στη μεγαλύτερη γραμμή. Η λίστα μπορεί να είναι μεγάλη και θα εξαρτηθεί από την πολυπλοκότητα των υπολογισμών, ωστόσο, σε μια συγκεκριμένη περίπτωση, αρκεί 1 συνθήκη.

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

9. Στις ρυθμίσεις, μπορείτε να ορίσετε την "Ακρίβεια περιορισμού" και "Όρια λύσης". Στην περίπτωσή μας, δεν χρειάζεται να χρησιμοποιήσετε αυτές τις επιλογές.

10. Όταν ολοκληρωθούν οι ρυθμίσεις, ξεκινά η ίδια η λειτουργία – κάντε κλικ στο «Εύρεση λύσης».

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

12. Υπάρχει πιθανότητα οι υπολογισμοί να αποδειχθούν λανθασμένοι ή να υπάρχει ανάγκη αλλαγής των αρχικών δεδομένων για να ληφθούν άλλοι δείκτες. Σε μια τέτοια περίπτωση, πρέπει να ανοίξετε ξανά το παράθυρο ρυθμίσεων και να ελέγξετε ξανά τις πληροφορίες.
13. Όταν τα δεδομένα είναι ακριβή, μπορεί να χρησιμοποιηθεί μια εναλλακτική μέθοδος. Για τους σκοπούς αυτούς, πρέπει να κάνετε κλικ στην τρέχουσα επιλογή και να επιλέξετε την καταλληλότερη μέθοδο από τη λίστα που εμφανίζεται:

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

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

14. Όταν ληφθεί η επιθυμητή έκπτωση, μένει να την εφαρμόσετε για να υπολογίσετε το ποσό των εκπτώσεων για κάθε είδος. Για το σκοπό αυτό, επισημαίνεται το αρχικό στοιχείο της στήλης "Ποσό έκπτωσης", γράφεται ο τύπος «=D2*$G$2» και πατήστε «Enter». Τα σημάδια του δολαρίου τοποθετούνται έτσι ώστε όταν ο τύπος τεντώνεται σε παρακείμενες γραμμές, το G2 δεν αλλάζει.

15. Το ποσό της έκπτωσης για το αρχικό είδος θα ληφθεί τώρα. Στη συνέχεια, θα πρέπει να μετακινήσετε τον κέρσορα στη γωνία του κελιού, όταν γίνει "συν", το LMB πιέζεται και ο τύπος τεντώνεται στις απαιτούμενες γραμμές.
16. Μετά από αυτό, το τραπέζι θα είναι τελικά έτοιμο.

Φόρτωση/Αποθήκευση επιλογών αναζήτησης

Αυτή η επιλογή είναι χρήσιμη κατά την εφαρμογή διαφόρων επιλογών περιορισμών.

1. Στο μενού Επιλογές εύρεσης λύσεων, κάντε κλικ στην επιλογή Φόρτωση/Αποθήκευση.
2. Εισαγάγετε το εύρος για την περιοχή του μοντέλου και κάντε κλικ στην επιλογή Αποθήκευση ή Φόρτωση.

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

Σημαντικό! Για να αποθηκεύσετε τις τελευταίες ρυθμίσεις στο μενού Επιλογές λύσης, αποθηκεύεται ένα βιβλίο εργασίας. Κάθε φύλλο σε αυτό έχει τις δικές του πρόσθετες επιλογές Επίλυσης. Επιπλέον, μπορείτε να ορίσετε περισσότερες από 1 εργασίες για ένα φύλλο κάνοντας κλικ στο κουμπί «Φόρτωση ή Αποθήκευση» για να αποθηκεύσετε μεμονωμένες εργασίες.

Ένα απλό παράδειγμα χρήσης του Solver

Είναι απαραίτητο να φορτώσετε το δοχείο με δοχεία έτσι ώστε η μάζα του να είναι η μέγιστη. Η δεξαμενή έχει όγκο 32 κυβικά μέτρα. Μ. Ένα γεμάτο κουτί έχει βάρος 20 κιλά, ο όγκος του είναι 0,15 κυβικά μέτρα. Μ. Κουτί – 80 κιλά και 0,5 κυβ. Μ. Απαιτείται ο συνολικός αριθμός των δοχείων να είναι τουλάχιστον 110 τεμ. Τα δεδομένα είναι οργανωμένα ως εξής:

Οι μεταβλητές του μοντέλου σημειώνονται με πράσινο χρώμα. Η αντικειμενική συνάρτηση επισημαίνεται με κόκκινο χρώμα. Περιορισμοί: κατά τον μικρότερο αριθμό δοχείων (μεγαλύτερο ή ίσο με 110) και κατά βάρος (=SUMPRODUCT(B8:C8,B6:C6) – συνολικό απόβαρο στο δοχείο.

Κατ' αναλογία, θεωρούμε τον συνολικό όγκο: =SUMPRODUCT(B7:C7,B8:C8). Ένας τέτοιος τύπος είναι απαραίτητος για να τεθεί ένα όριο στον συνολικό όγκο των δοχείων. Στη συνέχεια, μέσω της «Αναζήτησης λύσης», εισάγονται σύνδεσμοι σε στοιχεία με μεταβλητές, τύπους και τους ίδιους τους δείκτες (ή συνδέσμους σε συγκεκριμένα κελιά). Φυσικά, ο αριθμός των κοντέινερ είναι ακέραιος (είναι και περιορισμός). Πατάμε «Εύρεση λύσης», με αποτέλεσμα να βρίσκουμε τέτοιο αριθμό δοχείων όταν η συνολική μάζα είναι μέγιστη και λαμβάνονται υπόψη όλοι οι περιορισμοί.

Η αναζήτηση λύσης απέτυχε να βρει λύσεις

Μια τέτοια ειδοποίηση εμφανίζεται όταν η εν λόγω συνάρτηση δεν έχει βρει συνδυασμούς βαθμολογιών μεταβλητών που να ικανοποιούν κάθε περιορισμό. Όταν χρησιμοποιείτε τη μέθοδο Simplex, είναι πολύ πιθανό να μην υπάρχει λύση.

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

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

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

Στο παραπάνω παράδειγμα, η ένδειξη μέγιστης έντασης είναι 16 κυβικά μέτρα. m αντί για 32, γιατί ένας τέτοιος περιορισμός έρχεται σε αντίθεση με τους δείκτες για τον ελάχιστο αριθμό θέσεων, αφού θα αντιστοιχεί στον αριθμό των 16,5 κυβικών μέτρων. Μ.

Συμπέρασμα

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