Λύση εξισώσεων, και όχι μόνο, με την “Αναζήτηση στόχου” (Goal Seek)

GoalSeek0

Τριγυρνώντας στο δίκτυο, έπεσα πάνω σε μία εφαρμογή που πολύ μου άρεσε. Λέγεται PhotoMath και λύνει μαθηματικές εξισώσεις. Η κάμερα ενός τηλεφώνου (smartphone), απλά σαρώνει την εξίσωση από ένα βιβλίο και στην οθόνη του τηλεφώνου εμφανίζει τη λύση της εξίσωσης. Με την ταχύτητα που παρουσιάζονται από την Microsoft οι νέες ανανεωμένες εκδόσεις του excel, κάθε φορά με νέα πλουμίδια, είμαι σίγουρος πως την εφαρμογή αυτή, ή κάποια παρόμοια, θα δούμε σύντομα σε μια από τις επόμενες εκδόσεις. Γιατί όχι; Το εργαλείο “Εφέ φωτογραφικής μηχανής” υπάρχει ήδη εδώ και πολλά χρόνια στο excel, καιρός να πλουτιστεί με νέες δυνατότητες.
Επειδή πολύ ζήλεψα την εφαρμογή αυτή, για να παρηγορηθώ, κάθισα και με λίγη vba, έστησα ένα φύλλο όπου τα κελιά της στήλης Α τα έκανα ευαίσθητα στις αλγεβρικές εξισώσεις , τις αναγνωρίζουν αμέσως μόλις τις γράψετε, και αμέσως δεξιά εμφανίζουν τη λύση τους. Γράψτε σε κελί της Α την εξίσωση στη φυσική της μορφή, πχ x^2-5*x+6=0 ή (3/7)*x-ln(x)=sqrt(x) και στο κελί Β θα εμφανιστεί μία λύση της εξίσωσης.

GoalSeek1
Αν θέλετε να δείτε πως δουλεύει, κατεβάστε το βιβλίο cells_sensitive_to_equations.xls όπου θα δείτε και τον λίγο κώδικα που χρειάστηκε. Η μικρή αυτή εφαρμογή στηρίζεται στο εργαλείο του excel “Αναζήτηση στόχου” (Goal Seek), είναι για να διασκεδάσετε, στα όρια του παιχνιδιού. Αν θέλετε να αναζητήσετε περισσότερες ή όλες τις λύσεις μιας εξίσωσης πρέπει να χειριστείτε το εργαλείο της αναζήτησης στόχου μόνοι σας. Ας δούμε ένα παράδειγμα. Έστω ότι θέλετε να λύσετε την εξίσωση:

x^3+200=9*x^2+30*x

Πρώτα από όλα, ξαναγράψτε την εξίσωση ώστε στο δεύτερο μέρος της (δεξιά από το ίσον) να υπάρχει ένας σταθερός αριθμός ή μηδέν. Έτσι:

x^3-9*x^2-30*x=-200
ή:
x^3+200-9*x^2-30*x=0

Στο παράδειγμα θα δουλέψετε με την πρώτη: x^3 – 9*x^2 – 30*x=-200
Σε ένα φύλλο excel θα χρειαστείτε τώρα δύο κελιά, έστω το Α1 και το Β1.
Στο κελί Β1 γράψτε σαν συνάρτηση (ξεκινώντας με ίσον) το αριστερό τμήμα της εξίσωσης, αντικαθιστώντας τον άγνωστο x με Α1, δηλαδή με την διεύθυνση του άλλου κελιού. Δηλαδή στο κελί Β1 θα εισάγετε:

=A1^3-9*A1^2-30*A1

Στο κελί Α1 γράψτε μια οποιαδήποτε αριθμητική τιμή, ας πούμε 20. Με την τιμή αυτή η συνάρτηση στο κελί Β1 επιστρέφει 3800. Δηλαδή με τη τιμή 20 το αριστερό τμήμα της εξίσωσης γίνεται 3800.

GoalSeek2
Τώρα θα καλέσετε το εργαλείο “Αναζήτηση στόχου”. Θα το βρείτε: στο excel 2003 και παλαιοτέρα στο μενού Δεδομένα, στο 2007 και νεώτερα στην καρτέλα Δεδομένα, ομάδα Εργαλεία δεδομένων, Ανάλυση πιθανοτήτων.GoalSeek9

Θα εμφανιστεί ένα μικρό πτυσσόμενο πλαίσιο το οποίο ζητά τρεις τιμές: “Ορισμός κελιού”, “Στην τιμή” και “Αλλαγή του κελιού”.

–> Ορισμός κελιού είναι το κελί Β1
–> Στην τιμή είναι η αριθμητική τιμή που αφήσαμε στο δεξί μέρος της εξίσωσης, στο       παράδειγμά μας -200
–> Αλλαγή του κελιού είναι το κελί Α1.

GoalSeek3
Με πιο απλά λόγια η αναζήτηση μας λέει ότι θα αλλάζει τις τιμές στο κελί Α1 ώσπου η ποσότητα (συνάρτηση) που γράψατε στο κελί Β1 να γίνει -200. Τότε θα έχει βρει λύση της εξίσωσης. Και αυτό κάνει μόλις πατήσετε το ΟΚ

GoalSeek4
Η τιμή στο κελί Α1 έγινε 10 και στο κελί Β1 έγινε -200. Δηλαδή για την τιμή 10 το πρώτο μέλος της εξίσωσης γίνεται -200, άρα η τιμή 10 επαληθεύει την εξίσωση είναι δηλαδή λύση της. Πατήσετε ΟΚ για να δεχτείτε τη λύση, επιλέξετε το κελί Α1, κοιτάξετε στη γραμμή των τύπων, πιθανά θα δείτε μια προσέγγιση της τιμής 10 (στο δικό μου υπολογιστή βλέπω την τιμή 10,0000001289957). Αυτό οφείλεται στον προσεγγιστικό τρόπο που δουλεύει η αναζήτηση και στην πραγματικότητα την τιμή στόχο -200 δεν την βρήκε ακριβώς, αλλά την προσέγγισε (Τρέχουσα τιμή: -199,9999884).

Για να βρείτε πιθανές άλλες λύσεις της εξίσωσης θα χρειαστεί να ξανατρέξετε την αναζήτηση στόχου δοκιμάζοντας με άλλες αρχικές τιμές στο κελί Α1. Πιθανά να χρειαστεί να κάνετε πολλές δοκιμές. Στο παραπάνω παράδειγμα, με αρχική τιμή Α1=0 θα βρείτε μια δεύτερη λύση =4 και με αρχική τιμή Α1=-10 θα βρείτε και την τρίτη λύση της εξίσωσης = -5. Δοκιμάστε τιμές κοντά στις πιθανές λύσεις, αν έχετε πληροφορίες για αυτές από την άλγεβρα. Για τις αδύνατες εξισώσεις, η αναζήτηση θα σας πει με ειλικρίνεια ότι έχει αδυναμία να βρει λύση.

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

GoalSeek5
Ο χρήστης αφού γράψει το αρχικό ποσό στο κελί Β1, στη συνέχεια επιλέγει από ένα πτυσσόμενο πλαίσιο επικύρωσης στο κελί Β3 την δραστηριότητα:

GoalSeek6
Αμέσως μία συνάρτηση VLOOKUP θα μεταφέρει στο διπλανό κελί C3 το ποσοστό της επιδότησης που αντιστοιχεί στη δραστηριότητα. Παρόμοια θα επιλεγεί η περιοχή και η κατηγορία φόρου. Στη συνέχεια απλοί τύποι στα κελιά Β10 έως Β18 θα υπολογίσουν τα επί μέρους ποσά. Κατεβάστε, αν θέλετε να δείτε τις λεπτομέρειες του παραδείγματος, το excel βιβλίο: GoalSeekExample.xls. Στο παράδειγμα της εικόνας ένα αρχικό ποσό 15.000€ διαμορφώθηκε σε ένα τελικό ποσό 12.960€.
Αν ο χρήστης αναρωτηθεί πόσο πρέπει να είναι το αρχικό ποσό, ώστε μετά τις επιδοτήσεις και τους φόρους, το τελικό ποσό που φτάσει στα χέρια του να είναι ένα συγκεκριμένο που θέλει, έστω 14.000€, τότε χρειάζεται την αναζήτηση στόχου. Καλέστε λοιπόν το εργαλείο της αναζήτησης και ορίστε:
–> Ορισμός κελιού: το κελί Β18, δηλαδή το κελί που υπολογίζει το τελικό ποσό.
–> τιμή: το ποσό που θέλει ο χρήστης να είναι το τελικό, δηλαδή 14000
–> Αλλαγή του κελιού: το κελί Α1, δηλαδή το κελί που περιέχει το αρχικό ποσό

GoalSeek7
Μόλις πατήσετε το OK, η λύση που αναζητάτε θα εμφανιστεί στα κελιά. Για τελικό ποσό 14.000, χρειάζεται ένα αρχικό ποσό 16.203,70 :

GoalSeek8
Η αναζήτηση έψαξε και βρήκε την τιμή που θα κάνει το τελικό ποσό 14000 και αυτό έγραψε στο κελί Β1. Πατήστε ΟΚ για να κρατήσετε τη νέα τιμή ή Άκυρο για να επιστρέψετε στην παλιά 15000.

This entry was posted in excel, Goal Seek, Μακροεντολές, VBA and tagged , , , , , . Bookmark the permalink.

One Response to Λύση εξισώσεων, και όχι μόνο, με την “Αναζήτηση στόχου” (Goal Seek)

  1. Ο/Η .: admin :. λέει:

    Εξαιρετικό !
    Ευχαριστώ Πολύ .

Τα σχόλια είναι απενεργοποιημένα.