Πώς να χρησιμοποιήσετε το στόχο του Excel και να επιλύσετε λύσεις για άγνωστες μεταβλητές

Διαφήμιση

Το Excel είναι πολύ ικανό όταν έχετε όλα τα δεδομένα που χρειάζεστε για τους υπολογισμούς σας.

Αλλά δεν θα ήταν ωραίο αν μπορούσε να λυθεί για άγνωστες μεταβλητές ;

Με το στόχο Goal και το add-in Solver, μπορεί. Και θα σας δείξουμε πώς. Διαβάστε παρακάτω για έναν πλήρη οδηγό σχετικά με τον τρόπο επίλυσης για ένα μόνο κελί με την αναζήτηση στόχου ή μια πιο περίπλοκη εξίσωση με το Solver.

Πώς να χρησιμοποιήσετε το στόχο στο Excel

Η αναζήτηση στόχου είναι ήδη ενσωματωμένη στο Excel. Είναι κάτω από την καρτέλα Δεδομένα, στο μενού Ανάλυση ανίχνευσης:

Για αυτό το παράδειγμα, θα χρησιμοποιήσουμε ένα πολύ απλό σύνολο αριθμών. Έχουμε αριθμούς πωλήσεων αξίας τριών τετάρτων και ετήσιο στόχο. Μπορούμε να χρησιμοποιήσουμε το Goal Seek για να καταλάβουμε τι πρέπει να έχουν οι αριθμοί στο Q4 για να επιτύχουν το στόχο.

Όπως μπορείτε να δείτε, το τρέχον σύνολο πωλήσεων είναι 114.706 μονάδες. Εάν θέλουμε να πουλήσουμε 250.000 μέχρι το τέλος του έτους, πόσοι πρέπει να πουλήσουμε στο Q4; Ο στόχος του Excel θα επιδιώξει να μας πει.

Ακολουθεί ο τρόπος χρήσης της αναζήτησης στόχων, βήμα προς βήμα:

1. Κάντε κλικ στην επιλογή Δεδομένα> Ανάλυση ανάλυσης> Ανίχνευση στόχου . Θα δείτε αυτό το παράθυρο:
   
2. Τοποθετήστε το τμήμα "equals" της εξίσωσης σας στο πεδίο Set Cell . Αυτός είναι ο αριθμός που θα προσπαθήσει να βελτιστοποιήσει το Excel. Στην περίπτωσή μας, είναι το τρέχον σύνολο των αριθμών πωλήσεών μας στο κελί B5.
3. Πληκτρολογήστε την τιμή στόχου σας στο πεδίο Προς τιμή . Αναζητούμε συνολικά 250.000 πωλήσεις, έτσι θα βάλουμε "250.000" σε αυτόν τον τομέα.
4. Ενημερώστε το Excel σε ποια μεταβλητή πρέπει να επιλύσετε το πεδίο Αλλαγή κυτταρικού πεδίου. Θέλουμε να δούμε τι πρέπει να είναι οι πωλήσεις μας στο Q4. Έτσι, θα πούμε στο Excel να λύσει το κελί D2. Θα μοιάζει με αυτό όταν είναι έτοιμο να πάει:
   
5. Πατήστε OK για να λύσετε το στόχο σας. Όταν φαίνεται καλό, απλά πατήστε OK . Το Excel θα σας ενημερώσει σχετικά με το πότε έχει βρει λύση.
   
6. Κάντε ξανά κλικ στο κουμπί OK και θα δείτε την τιμή που λύνει την εξίσωση σας στο κελί που επιλέξατε Για την αλλαγή της κυψέλης .
   

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

Σημειώστε ότι το Excel αντικαθιστά τα προηγούμενα δεδομένα μας. Είναι μια καλή ιδέα να εκτελέσετε το Go Seek σε ένα αντίγραφο των δεδομένων σας . Είναι επίσης καλή ιδέα να σημειώσετε τα αντιγραμμένα δεδομένα που δημιουργήσατε με τη χρήση του στόχου αναζήτησης. Δεν θέλετε να το συγχέετε με τρέχοντα ακριβή δεδομένα.

Έτσι το Go Seek είναι ένα χρήσιμο χαρακτηριστικό Excel 16 Φόρμες Excel που θα σας βοηθήσουν να λύσετε προβλήματα πραγματικής ζωής 16 Φόρμες Excel που θα σας βοηθήσουν να λύσετε προβλήματα πραγματικής ζωής Το σωστό εργαλείο είναι το μισό έργο. Το Excel μπορεί να λύσει υπολογισμούς και να επεξεργάζεται δεδομένα ταχύτερα από ότι μπορείτε να βρείτε την αριθμομηχανή σας. Σας παρουσιάζουμε βασικούς τύπους Excel και επιδεικνύετε πώς να τις χρησιμοποιήσετε. Διαβάστε περισσότερα, αλλά δεν είναι όλα αυτά εντυπωσιακά. Ας ρίξουμε μια ματιά σε ένα εργαλείο που είναι πολύ πιο ενδιαφέρον: το add-in Solver.

Τι κάνει ο Επίλυση του Excel;

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

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

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

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

Ας ρίξουμε μια ματιά στη φόρτωση του πρόσθετου Solver, και στη συνέχεια να μεταβείτε στον τρόπο χρήσης του Solver στο Excel 2016.

Τρόπος φόρτωσης του πρόσθετου Solver

Το Excel δεν έχει προεπιλεγμένη λύση. Είναι ένα add-in έτσι, όπως και άλλες ισχυρές δυνατότητες του Excel Power Up Excel με 10 πρόσθετα για τη διεκπεραίωση, την ανάλυση και την οπτικοποίηση δεδομένων όπως ένα Pro Power Up με 10 πρόσθετα για να επεξεργαστείτε, να αναλύσετε και να απεικονίσετε δεδομένα όπως ένα Vanilla Excel Pro είναι εκπληκτικό, αλλά μπορείτε να το κάνετε ακόμα πιο ισχυρό με πρόσθετα. Όποια και αν είναι τα δεδομένα που χρειάζεται να επεξεργαστείτε, οι πιθανότητες είναι ότι κάποιος έχει δημιουργήσει μια εφαρμογή του Excel για αυτό. Εδώ είναι μια επιλογή. Διαβάστε περισσότερα, θα πρέπει να το φορτώσετε πρώτα. Ευτυχώς, είναι ήδη στον υπολογιστή σας.

Μεταβείτε στο Αρχείο> Επιλογές> Πρόσθετα . Στη συνέχεια, κάντε κλικ στο κουμπί Μετάβαση δίπλα στην επιλογή Διαχείριση: Πρόσθετα του Excel .

Εάν αυτό το αναπτυσσόμενο μενού δηλώνει κάτι διαφορετικό από το "Πρόσθετα του Excel", θα πρέπει να το αλλάξετε:

Στο παράθυρο που προκύπτει, θα δείτε μερικές επιλογές. Βεβαιωθείτε ότι το πλαίσιο δίπλα στο πρόσθετο Solver Add-In είναι επιλεγμένο και πατήστε OK .

Θα δείτε τώρα το κουμπί Επίλυση στην ομάδα Ανάλυσης της καρτέλας Δεδομένα :

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

Τώρα που έχετε φορτώσει το πρόσθετο, ας ρίξουμε μια ματιά στο πώς να το χρησιμοποιήσετε.

Τρόπος χρήσης του Solver στο Excel

Υπάρχουν τρία μέρη σε οποιαδήποτε ενέργεια Solver: ο αντικειμενικός στόχος, τα μεταβλητά κελιά και οι περιορισμοί. Θα περπατήσουμε σε κάθε ένα από τα βήματα.

1. Κάντε κλικ στην επιλογή Δεδομένα> Επίλυση . Θα δείτε το παράθυρο Παράμετροι Solver παρακάτω. (Εάν δεν βλέπετε το κουμπί Solver, ανατρέξτε στην προηγούμενη ενότητα σχετικά με τον τρόπο φόρτωσης του πρόσθετου Solver.)
   
2. Ορίστε τον κυτταρικό στόχο και ενημερώστε το Excel για το στόχο σας. Ο στόχος βρίσκεται στην κορυφή του παραθύρου Solver και έχει δύο μέρη: το κυψέλη αντικειμένων και μια επιλογή μεγιστοποίησης, ελαχιστοποίησης ή συγκεκριμένης τιμής.
   
   Αν επιλέξετε το Max, το Excel θα προσαρμόσει τις μεταβλητές σας για να πάρει τον μεγαλύτερο δυνατό αριθμό στο αντικειμενικό σας κελί. Το ελάχιστο είναι το αντίθετο: ο Επίλυση θα ελαχιστοποιήσει τον αντικειμενικό αριθμό. Το Value Of σάς επιτρέπει να ορίσετε έναν συγκεκριμένο αριθμό για τον Solver για να αναζητήσετε.
3. Επιλέξτε τα μεταβλητά κελιά που μπορεί να αλλάξει το Excel. Τα μεταβλητά κελιά ορίζονται με το πεδίο By Changing Variable Cells . Κάντε κλικ στο βέλος δίπλα στο πεδίο και, στη συνέχεια, κάντε κλικ και σύρετε για να επιλέξετε τα κελιά που θα πρέπει να εργαστούν με το Solver. Σημειώστε ότι αυτά είναι όλα τα κελιά που μπορούν να διαφέρουν. Εάν δεν θέλετε να αλλάξει ένα κελί, μην το επιλέγετε.
   
4. Ορίστε περιορισμούς σε πολλαπλές ή μεμονωμένες μεταβλητές. Τέλος, φτάνουμε στους περιορισμούς. Αυτό είναι όπου Solver είναι πραγματικά ισχυρό. Αντί να αλλάξετε οποιοδήποτε από τα μεταβλητά κελιά σε οποιοδήποτε αριθμό θέλει, μπορείτε να καθορίσετε τους περιορισμούς που πρέπει να πληρούνται. Για λεπτομέρειες, ανατρέξτε στην ενότητα σχετικά με τον τρόπο ρύθμισης των περιορισμών παρακάτω.
5. Αφού ολοκληρωθούν όλες αυτές οι πληροφορίες, πατήστε Επίλυση για να πάρετε την απάντησή σας. Το Excel θα ενημερώσει τα δεδομένα σας για να συμπεριλάβει τις νέες μεταβλητές (γι 'αυτό συνιστούμε να δημιουργήσετε πρώτα ένα αντίγραφο των δεδομένων σας).

Μπορείτε επίσης να δημιουργήσετε αναφορές, τις οποίες θα εξετάσουμε εν συντομία στο παράδειγμα solver μας παρακάτω.

Πώς να ορίσετε τους περιορισμούς στην επίλυση

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

Για να προσθέσετε έναν περιορισμό, κάντε κλικ στο κουμπί Προσθήκη δίπλα στη λίστα περιορισμών. Θα πάρετε ένα νέο παράθυρο. Επιλέξτε το στοιχείο (ή κελιά) που πρέπει να περιοριστεί στο πεδίο Αναφορά κυψέλης και, στη συνέχεια, επιλέξτε έναν χειριστή.

Εδώ είναι οι διαθέσιμοι φορείς:

• <= (μικρότερη ή ίση με)
• = (ίσο με)
• => (μεγαλύτερη ή ίση με)
• int (πρέπει να είναι ένας ακέραιος αριθμός)
• bin (πρέπει να είναι είτε 1 είτε 0)
• Όλοι Διαφορετικοί

Όλα είναι διαφορετικά. Καθορίζει ότι κάθε κελί της περιοχής που επιλέγετε για Αναφορά κελιού πρέπει να είναι διαφορετικός αριθμός. Αλλά καθορίζει επίσης ότι πρέπει να είναι μεταξύ 1 και του αριθμού των κυττάρων. Έτσι εάν έχετε τρία κελιά, θα καταλήξετε με τους αριθμούς 1, 2 και 3 (αλλά όχι απαραίτητα με αυτή τη σειρά)

Τέλος, προσθέστε την τιμή για τον περιορισμό.

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

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

Παράδειγμα λύσης

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

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

Εδώ είναι οι περιορισμοί που θα χρησιμοποιήσουμε:

• Καμία εργασία δεν μπορεί να πέσει κάτω από τέσσερις ώρες.
• Η εργασία 2 πρέπει να είναι μεγαλύτερη από οκτώ ώρες .
• Η εργασία 5 πρέπει να είναι μικρότερη από έντεκα ώρες .
• Οι συνολικές ώρες εργασίας πρέπει να είναι ίσες με 40 .

Μπορεί να είναι χρήσιμο να γράψετε τους περιορισμούς σας όπως πριν πριν χρησιμοποιήσετε το Solver.

Ακολουθεί ο τρόπος με τον οποίο ρυθμίσαμε το Solver:

Πρώτον, σημειώστε ότι έχω δημιουργήσει ένα αντίγραφο του πίνακα ώστε να μην αντικαταστήσουμε το πρωτότυπο, το οποίο περιέχει τις τρέχουσες ώρες εργασίας μας.

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

Ας χτυπήσουμε Επίλυση και να δούμε τι συμβαίνει.

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

Για να διατηρήσετε τις νέες τιμές, βεβαιωθείτε ότι η επιλογή Διατήρηση λύσης λύσης είναι επιλεγμένη και πατήστε OK .

Αν θέλετε περισσότερες πληροφορίες, μπορείτε να επιλέξετε μια αναφορά από τη δεξιά πλευρά του παραθύρου. Επιλέξτε όλες τις αναφορές που θέλετε, ενημερώστε το Excel εάν θέλετε να περιγράφονται (σας συνιστώ) και πατήστε OK .

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

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

Προχωρημένες επιλογές Solver

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

Η πιο προφανής είναι η μέθοδος επίλυσης:

Μπορείτε να επιλέξετε ανάμεσα σε GRG Nonlinear, Simplex LP και Evolutionary. Το Excel παρέχει μια απλή εξήγηση σχετικά με το πότε πρέπει να χρησιμοποιήσετε το καθένα. Μια καλύτερη εξήγηση απαιτεί κάποια γνώση των στατιστικών Μάθε Στατιστικά για Δωρεάν με αυτές τις 6 Πόρων Μάθε Στατιστικά για Δωρεάν με αυτές τις 6 Πόρων Στατιστικά έχει τη φήμη ενός θέματος που είναι δύσκολο να καταλάβει. Αλλά η μάθηση από τον σωστό πόρο θα σας βοηθήσει να καταλάβετε τα αποτελέσματα των ερευνών, τις εκλογικές εκθέσεις και τις ταξινομήσεις των τάξεων σας σε χρόνο μηδέν. Διαβάστε περισσότερα και παλινδρόμηση.

Για να προσαρμόσετε πρόσθετες ρυθμίσεις, απλώς πατήστε το κουμπί Επιλογές . Μπορείτε να πείτε στο Excel την αρίθμηση ακέραιων αριθμών, να ορίσετε περιορισμούς χρόνου υπολογισμού (χρήσιμοι για μαζικά σύνολα δεδομένων) και να ρυθμίσετε τον τρόπο με τον οποίο οι μέθοδοι επίλυσης GRG και Evolutionary πηγαίνουν για να κάνουν τους υπολογισμούς τους.

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

Αναζήτηση και Επίλυση στόχων: Αποδοχή Excel στο επόμενο επίπεδο

Τώρα που είστε ικανοποιημένοι με τα βασικά της επίλυσης για άγνωστες μεταβλητές στο Excel, ένας εξ ολοκλήρου νέος υπολογισμός υπολογιστικών φύλλων είναι ανοικτός σε εσάς.

Το Go Seek μπορεί να σας βοηθήσει να εξοικονομήσετε χρόνο κάνοντας πιο γρήγορους υπολογισμούς και ο Solver προσθέτει μια τεράστια δύναμη στις ικανότητες υπολογισμού του Excel. Πώς να υπολογίσετε τις βασικές στατιστικές στο Excel: Οδηγός για αρχάριους Πώς να υπολογίσετε τις βασικές στατιστικές στο Excel: Οδηγός για αρχάριους Το Microsoft Excel μπορεί κάντε στατιστικά! Μπορείτε να υπολογίσετε τα ποσοστά, τους μέσους όρους, την τυπική απόκλιση, το τυπικό σφάλμα και τις δοκιμές Τ του φοιτητή. Διαβάστε περισσότερα .

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

Χρησιμοποιείτε στόχευση ή επίλυση στα υπολογιστικά φύλλα σας; Τι άλλες συμβουλές μπορείτε να παράσχετε για να λάβετε τις καλύτερες απαντήσεις από αυτές; Μοιραστείτε τις σκέψεις σας στα παρακάτω σχόλια!