Το Goal Seek είναι ένα από τα εργαλεία ανάλυσης What-if του Excel που σας βοηθά να βρείτε τη σωστή τιμή εισαγωγής ενός τύπου για να λάβετε το επιθυμητό αποτέλεσμα. Δείχνει πώς μια τιμή σε έναν τύπο επηρεάζει μια άλλη. Με άλλα λόγια, εάν έχετε μια τιμή στόχο που θέλετε να επιτύχετε, μπορείτε να χρησιμοποιήσετε την αναζήτηση στόχου για να βρείτε τη σωστή τιμή εισόδου για να την αποκτήσετε.
Για παράδειγμα, ας υποθέσουμε ότι συγκεντρώσατε συνολικά 75 βαθμούς σε ένα μάθημα και χρειάζεστε τουλάχιστον 90 για να λάβετε βαθμό S σε αυτό το μάθημα. Ευτυχώς, έχετε ένα τελευταίο τεστ που μπορεί να σας βοηθήσει να αυξήσετε τη μέση βαθμολογία σας. Μπορείτε να χρησιμοποιήσετε το Goal Seek για να υπολογίσετε πόση βαθμολογία χρειάζεστε σε αυτό το τελικό τεστ για να λάβετε βαθμό S.
Το Goal Seek χρησιμοποιεί μια μέθοδο δοκιμής και λάθους για να παρακολουθεί το πρόβλημα εισάγοντας εικασίες μέχρι να φτάσει στο σωστό αποτέλεσμα. Το Goal Seek μπορεί να βρει την καλύτερη τιμή εισαγωγής για τον τύπο μέσα σε λίγα δευτερόλεπτα που θα χρειαζόταν πολύς χρόνος για να καταλάβουμε με μη αυτόματο τρόπο. Σε αυτό το άρθρο, θα σας δείξουμε πώς να χρησιμοποιήσετε το εργαλείο αναζήτησης στόχου στο Excel με μερικά παραδείγματα.
Στοιχεία της συνάρτησης αναζήτησης στόχου
Η συνάρτηση αναζήτησης στόχου αποτελείται από τρεις παραμέτρους, δηλαδή:
- Ρύθμιση κελιού – Αυτό είναι το κελί στο οποίο εισάγεται ο τύπος. Καθορίζει το κελί στο οποίο θέλετε την επιθυμητή έξοδο.
- Να εκτιμάς – Αυτός είναι ο στόχος / η επιθυμητή τιμή που θέλετε ως αποτέλεσμα της λειτουργίας αναζήτησης στόχου.
- Με την αλλαγή κελιού – Αυτό καθορίζει το κελί του οποίου η τιμή πρέπει να προσαρμοστεί για να ληφθεί η επιθυμητή έξοδος.
Πώς να χρησιμοποιήσετε το Goal Seek στο Excel: Παράδειγμα 1
Για να δείξετε πώς λειτουργεί σε ένα απλό παράδειγμα, φανταστείτε ότι έχετε έναν πάγκο με φρούτα. Στο παρακάτω στιγμιότυπο οθόνης, το κελί B11 (παρακάτω) εμφανίζει πόσο σας κοστίζει η αγορά φρούτων για τον πάγκο σας και το κελί B12 δείχνει τα συνολικά έσοδά σας από την πώληση αυτών των φρούτων. Και το κελί B13 δείχνει το ποσοστό του κέρδους σας (20%).
Εάν θέλετε να αυξήσετε το κέρδος σας στο «30%», αλλά δεν μπορείτε να αυξήσετε την επένδυσή σας, επομένως πρέπει να αυξήσετε τα έσοδά σας για να κερδίσετε κέρδος. Αλλά μέχρι πόσο; Η αναζήτηση στόχου θα σας βοηθήσει να τον βρείτε.
Χρησιμοποιείτε τον ακόλουθο τύπο στο κελί B13 για να υπολογίσετε το ποσοστό κέρδους:
=(B12-B11)/B12Τώρα, θέλετε να υπολογίσετε το περιθώριο κέρδους έτσι ώστε το ποσοστό κέρδους σας να είναι 30%. Μπορείτε να το κάνετε αυτό με το Goal Seek στο Excel.
Το πρώτο πράγμα που πρέπει να κάνετε είναι να επιλέξετε το κελί του οποίου την τιμή θέλετε να προσαρμόσετε. Κάθε φορά που χρησιμοποιείτε το Goal Seek, πρέπει να επιλέξετε ένα κελί που περιέχει έναν τύπο ή μια συνάρτηση. Στην περίπτωσή μας, θα επιλέξουμε το κελί B13 επειδή περιέχει τον τύπο για τον υπολογισμό του ποσοστού.
Στη συνέχεια, μεταβείτε στην καρτέλα "Δεδομένα", κάντε κλικ στο κουμπί "Τι θα συμβεί αν Ανάλυση" στην ομάδα Πρόβλεψη και επιλέξτε "Αναζήτηση στόχου".
Το πλαίσιο διαλόγου "Αναζήτηση στόχου" θα εμφανιστεί με 3 πεδία:
- Ρύθμιση κελιού – Εισαγάγετε την αναφορά κελιού που περιέχει τον τύπο (B13). Αυτό είναι το κελί που θα έχει την επιθυμητή έξοδο.
- Να εκτιμάς – Εισαγάγετε το επιθυμητό αποτέλεσμα που προσπαθείτε να επιτύχετε (30%).
- Με την αλλαγή κελιού – Εισαγάγετε την αναφορά κελιού για την τιμή εισόδου που θέλετε να αλλάξετε (B12) για να φτάσετε στο επιθυμητό αποτέλεσμα. Απλώς κάντε κλικ στο κελί ή εισαγάγετε χειροκίνητα την αναφορά κελιού. Όταν επιλέγετε το κελί, το Excel θα προσθέσει το σύμβολο «$» πριν από το γράμμα της στήλης και τον αριθμό της γραμμής για να το κάνει απόλυτο κελί.
Όταν τελειώσετε, κάντε κλικ στο «OK» για να το δοκιμάσετε.
Στη συνέχεια, θα εμφανιστεί ένα παράθυρο διαλόγου «Κατάσταση αναζήτησης στόχου» και θα σας ενημερώσει εάν βρήκε κάποια λύση όπως φαίνεται παρακάτω. Εάν έχει βρεθεί μια λύση, η τιμή εισόδου στο «μεταβαλλόμενο κελί (B12)» θα προσαρμοστεί σε μια νέα τιμή. Αυτό θέλουμε. Σε αυτό το παράδειγμα, η ανάλυση καθόρισε ότι, για να επιτύχετε τον στόχο κέρδους του 30%, πρέπει να επιτύχετε έσοδα 1635,5 $ (B12).
Κάντε κλικ στο «OK» και το Excel θα αλλάξει τις τιμές των κελιών ή κάντε κλικ στο «Ακύρωση» για να απορρίψετε τη λύση και να επαναφέρετε την αρχική τιμή.
Η τιμή εισόδου (1635,5) στο κελί B12 είναι αυτή που ανακαλύψαμε χρησιμοποιώντας την Αναζήτηση στόχου για να πετύχουμε τον στόχο μας (30%).
Πράγματα που πρέπει να θυμάστε όταν χρησιμοποιείτε το Goal Seek
- Το κελί συνόλου πρέπει πάντα να περιέχει έναν τύπο που εξαρτάται από το κελί «Με την αλλαγή κελιού».
- Μπορείτε να χρησιμοποιήσετε το Goal Seek μόνο σε μία τιμή εισαγωγής κελιού κάθε φορά
- Το «Με την αλλαγή κελιού» πρέπει να περιέχει μια τιμή και όχι έναν τύπο.
- Εάν το Goal Seek δεν μπορεί να βρει τη σωστή λύση, δείχνει την πλησιέστερη τιμή που μπορεί να παράγει και σας λέει ότι το μήνυμα «Η αναζήτηση στόχου μπορεί να μην έχει βρει λύση».
Τι να κάνετε όταν το Excel Goal Seek δεν λειτουργεί
Ωστόσο, εάν είστε βέβαιοι ότι υπάρχει λύση, υπάρχουν μερικά πράγματα που μπορείτε να δοκιμάσετε για να διορθώσετε αυτό το ζήτημα.
Ελέγξτε τις παραμέτρους και τις τιμές αναζήτησης στόχου
Υπάρχουν δύο πράγματα που πρέπει να ελέγξετε: πρώτα, ελέγξτε εάν η παράμετρος "Set cell" αναφέρεται στο κελί του τύπου. Δεύτερον, βεβαιωθείτε ότι το κελί τύπου (Set cell) εξαρτάται, άμεσα ή έμμεσα, από το μεταβαλλόμενο κελί.
Προσαρμογή των ρυθμίσεων επανάληψης
Στις ρυθμίσεις του Excel, μπορείτε να αλλάξετε τον αριθμό των πιθανών προσπαθειών που μπορεί να κάνει το Excel για να βρει τη σωστή λύση καθώς και την ακρίβειά της.
Για να αλλάξετε τις ρυθμίσεις υπολογισμού επανάληψης, κάντε κλικ στο «Αρχείο» από τη λίστα καρτελών. Στη συνέχεια, κάντε κλικ στο «Επιλογές» στο κάτω μέρος.
Στο παράθυρο Επιλογές του Excel, κάντε κλικ στην επιλογή «Τύποι» στο αριστερό τμήμα του παραθύρου.
Στην ενότητα "Επιλογές υπολογισμού", αλλάξτε αυτές τις ρυθμίσεις:
- Μέγιστες επαναλήψεις – Υποδεικνύει τον αριθμό των πιθανών λύσεων που θα υπολογίσει το Excel. όσο μεγαλύτερος είναι ο αριθμός τόσο περισσότερες επαναλήψεις μπορεί να εκτελέσει. Για παράδειγμα, εάν ορίσετε τη «Μέγιστες επαναλήψεις» σε 150, το Excel δοκιμάζει 150 πιθανές λύσεις.
- Μέγιστη αλλαγή – Υποδεικνύει την ακρίβεια των αποτελεσμάτων. ο μικρότερος αριθμός δίνει μεγαλύτερη ακρίβεια. Για παράδειγμα, εάν η τιμή του κελιού εισόδου ισούται με "0", αλλά η αναζήτηση στόχου σταματήσει να υπολογίζει στο "0,001", η αλλαγή αυτής της τιμής σε "0,0001" θα πρέπει να λύσει το πρόβλημα.
Αυξήστε την τιμή «Μέγιστες επαναλήψεις» εάν θέλετε το Excel να δοκιμάσει περισσότερες πιθανές λύσεις και μειώστε την τιμή «Μέγιστη αλλαγή» εάν θέλετε ένα πιο ακριβές αποτέλεσμα.
Το παρακάτω στιγμιότυπο οθόνης δείχνει την προεπιλεγμένη τιμή:
Χωρίς κυκλικές αναφορές
Όταν ένας τύπος αναφέρεται στο δικό του κελί, ονομάζεται κυκλική αναφορά. Εάν θέλετε το Excel Goal Seek να λειτουργεί σωστά, οι τύποι δεν πρέπει να χρησιμοποιούν κυκλική αναφορά.
Παράδειγμα αναζήτησης στόχου του Excel 2
Ας υποθέσουμε ότι δανείζεστε χρήματα "25.000 $" από κάποιον. Σας δανείζουν τα χρήματα με επιτόκιο 7% το μήνα για περίοδο 20 μηνών, γεγονός που κάνει την αποπληρωμή των «$1327» ανά μήνα. Αλλά μπορείτε να αντέξετε οικονομικά να πληρώνετε μόνο «1.000 $» το μήνα για 20 μήνες με επιτόκιο 7%. Το Goal Seek μπορεί να σας βοηθήσει να βρείτε το ποσό του δανείου που παράγει μηνιαία πληρωμή (EMI) «1000$».
Εισαγάγετε τις τρεις μεταβλητές εισόδου που θα χρειαστείτε για να υπολογίσετε τον υπολογισμό του PMT, δηλαδή επιτόκιο 7%, διάρκεια 20 μηνών και ποσό κεφαλαίου 25.000 $.
Εισαγάγετε τον ακόλουθο τύπο PMT στο κελί B5 που θα σας δώσει ένα ποσό EMI 1.327,97 $.
Η σύνταξη της συνάρτησης PMT:
=PMT(Επιτόκιο/12, Διάρκεια, Κεφάλαιο)Ο τύπος:
=PMT(B3/12,B4,-B2)
Επιλέξτε το κελί B5 (κελί τύπου) και μεταβείτε στο Data –> What If Analysis –> Goal Seek.
Χρησιμοποιήστε αυτές τις παραμέτρους στο παράθυρο «Αναζήτηση στόχου»:
- Ρύθμιση κελιού – B5 (το κελί που περιέχει τον τύπο που υπολογίζει το EMI)
- Να εκτιμάς – 1000 (το αποτέλεσμα/στόχος της φόρμουλας που αναζητάτε)
- Με την αλλαγή κελιού – B2 (αυτό είναι το ποσό δανείου που θέλετε να αλλάξετε για να επιτύχετε την αξία στόχου)
Στη συνέχεια, πατήστε "OK" για να διατηρήσετε τη λύση που βρέθηκε ή "Ακύρωση" για να την απορρίψετε.
Η ανάλυση σας λέει ότι το μέγιστο χρηματικό ποσό που μπορείτε να δανειστείτε είναι 18825 $ εάν θέλετε να υπερβείτε τον προϋπολογισμό σας.
Έτσι χρησιμοποιείτε το Goal Seek στο Excel.