Το Excel διαθέτει ενσωματωμένες λειτουργίες που μπορείτε να χρησιμοποιήσετε για να εμφανίσετε τα δεδομένα βαθμονόμησής σας και να υπολογίσετε μια γραμμή καλύτερης προσαρμογής. Αυτό μπορεί να είναι χρήσιμο όταν γράφετε μια έκθεση εργαστηρίου χημείας ή προγραμματίζετε έναν διορθωτικό παράγοντα σε ένα κομμάτι του εξοπλισμού.
Σε αυτό το άρθρο, θα δούμε πώς να χρησιμοποιήσετε το Excel για να δημιουργήσετε ένα γράφημα, να σχεδιάσετε μια γραμμική καμπύλη βαθμονόμησης, να εμφανίσετε τον τύπο της καμπύλης βαθμονόμησης και, στη συνέχεια, να ρυθμίσετε απλούς τύπους με τις συναρτήσεις SLOPE και INTERCEPT για να χρησιμοποιήσετε την εξίσωση βαθμονόμησης στο Excel.
Πίνακας περιεχομένων
Τι είναι μια καμπύλη βαθμονόμησης και πώς είναι χρήσιμο το Excel κατά τη δημιουργία μιας;
Για να εκτελέσετε μια βαθμονόμηση, συγκρίνετε τις ενδείξεις μιας συσκευής (όπως η θερμοκρασία που εμφανίζει ένα θερμόμετρο) με γνωστές τιμές που ονομάζονται πρότυπα (όπως τα σημεία πήξης και βρασμού του νερού). Αυτό σας επιτρέπει να δημιουργήσετε μια σειρά από ζεύγη δεδομένων που στη συνέχεια θα χρησιμοποιήσετε για να αναπτύξετε μια καμπύλη βαθμονόμησης.
Μια βαθμονόμηση δύο σημείων ενός θερμομέτρου χρησιμοποιώντας τα σημεία πήξης και βρασμού του νερού θα έχει δύο ζεύγη δεδομένων: ένα από τη στιγμή που το θερμόμετρο τοποθετείται σε παγωμένο νερό (32°F ή 0°C) και ένα σε βραστό νερό (212°F ή 100°C). Όταν σχεδιάζετε αυτά τα δύο ζεύγη δεδομένων ως σημεία και σχεδιάζετε μια γραμμή μεταξύ τους (την καμπύλη βαθμονόμησης), τότε υποθέτοντας ότι η απόκριση του θερμομέτρου είναι γραμμική, θα μπορούσατε να επιλέξετε οποιοδήποτε σημείο της γραμμής που αντιστοιχεί στην τιμή που εμφανίζει το θερμόμετρο και μπορούσε να βρει την αντίστοιχη «αληθινή» θερμοκρασία.
Έτσι, η γραμμή ουσιαστικά συμπληρώνει τις πληροφορίες μεταξύ των δύο γνωστών σημείων για εσάς, ώστε να μπορείτε να είστε αρκετά σίγουροι όταν υπολογίζετε την πραγματική θερμοκρασία όταν το θερμόμετρο δείχνει 57,2 μοίρες, αλλά όταν δεν έχετε μετρήσει ποτέ ένα “πρότυπο” που αντιστοιχεί σε αυτό το διάβασμα.
Το Excel διαθέτει λειτουργίες που σας επιτρέπουν να σχεδιάσετε τα ζεύγη δεδομένων γραφικά σε ένα γράφημα, να προσθέσετε μια γραμμή τάσης (καμπύλη βαθμονόμησης) και να εμφανίσετε την εξίσωση της καμπύλης βαθμονόμησης στο γράφημα. Αυτό είναι χρήσιμο για οπτική απεικόνιση, αλλά μπορείτε επίσης να υπολογίσετε τον τύπο της γραμμής χρησιμοποιώντας τις συναρτήσεις SLOPE και INTERCEPT του Excel. Όταν εισάγετε αυτές τις τιμές σε απλούς τύπους, θα μπορείτε να υπολογίσετε αυτόματα την «αληθινή» τιμή με βάση οποιαδήποτε μέτρηση.
Ας δούμε ένα παράδειγμα
Για αυτό το παράδειγμα, θα αναπτύξουμε μια καμπύλη βαθμονόμησης από μια σειρά δέκα ζευγών δεδομένων, το καθένα αποτελούμενο από μια τιμή Χ και μια τιμή Υ. Οι τιμές X θα είναι τα «πρότυπά» μας και θα μπορούσαν να αντιπροσωπεύουν οτιδήποτε, από τη συγκέντρωση ενός χημικού διαλύματος που μετράμε χρησιμοποιώντας ένα επιστημονικό όργανο μέχρι τη μεταβλητή εισόδου ενός προγράμματος που ελέγχει μια μηχανή εκτόξευσης μαρμάρου.
Οι τιμές Υ θα είναι οι «απαντήσεις» και θα αντιπροσωπεύουν την ένδειξη του οργάνου που παρέχεται κατά τη μέτρηση κάθε χημικού διαλύματος ή τη μετρούμενη απόσταση του πόσο μακριά από τον εκτοξευτή προσγειώθηκε το μάρμαρο χρησιμοποιώντας κάθε τιμή εισόδου.
Αφού απεικονίσουμε γραφικά την καμπύλη βαθμονόμησης, θα χρησιμοποιήσουμε τις συναρτήσεις SLOPE και INTERCEPT για να υπολογίσουμε τον τύπο της γραμμής βαθμονόμησης και να προσδιορίσουμε τη συγκέντρωση ενός «άγνωστου» χημικού διαλύματος με βάση την ένδειξη του οργάνου ή να αποφασίσουμε ποια είσοδο θα πρέπει να δώσουμε στο πρόγραμμα, ώστε η το μάρμαρο προσγειώνεται σε κάποια απόσταση από τον εκτοξευτή.
Βήμα πρώτο: Δημιουργήστε το γράφημά σας
Το απλό παράδειγμα υπολογιστικού φύλλου αποτελείται από δύο στήλες: X-Value και Y-Value.
Ας ξεκινήσουμε επιλέγοντας τα δεδομένα που θα σχεδιάσουμε στο γράφημα.
Αρχικά, επιλέξτε τα κελιά της στήλης “X-Value”.
Τώρα πατήστε το πλήκτρο Ctrl και, στη συνέχεια, κάντε κλικ στα κελιά της στήλης Y-Value.
Μεταβείτε στην καρτέλα “Εισαγωγή”.
Μεταβείτε στο μενού “Διαγράμματα” και επιλέξτε την πρώτη επιλογή στο αναπτυσσόμενο μενού “Διασπορά”.
Επιλέξτε τη σειρά κάνοντας κλικ σε ένα από τα μπλε σημεία. Μόλις επιλεγεί, το Excel περιγράφει τα σημεία που θα περιγραφούν.
Κάντε δεξί κλικ σε ένα από τα σημεία και, στη συνέχεια, επιλέξτε την επιλογή «Προσθήκη γραμμής τάσης».
Μια ευθεία γραμμή θα εμφανιστεί στο γράφημα.
Στη δεξιά πλευρά της οθόνης, θα εμφανιστεί το μενού “Format Trendline”. Επιλέξτε τα πλαίσια δίπλα στις “Εξίσωση εμφάνισης στο γράφημα” και “Εμφάνιση τιμής στο τετράγωνο R στο γράφημα”. Η τιμή R-τετράγωνο είναι μια στατιστική που σας λέει πόσο πολύ ταιριάζει η γραμμή στα δεδομένα. Η καλύτερη τιμή στο τετράγωνο R είναι 1.000, που σημαίνει ότι κάθε σημείο δεδομένων αγγίζει τη γραμμή. Καθώς οι διαφορές μεταξύ των σημείων δεδομένων και της γραμμής μεγαλώνουν, η τιμή του r-τετράγωνου πέφτει, με το 0,000 να είναι η χαμηλότερη δυνατή τιμή.
Η εξίσωση και το στατιστικό R-τετράγωνο της γραμμής τάσης θα εμφανιστούν στο γράφημα. Σημειώστε ότι η συσχέτιση των δεδομένων είναι πολύ καλή στο παράδειγμά μας, με τιμή R-τετράγωνο 0,988.
Η εξίσωση έχει τη μορφή «Y = Mx + B», όπου M είναι η κλίση και B είναι η τομή του άξονα y της ευθείας γραμμής.
Τώρα που ολοκληρώθηκε η βαθμονόμηση, ας εργαστούμε για την προσαρμογή του γραφήματος επεξεργάζοντας τον τίτλο και προσθέτοντας τίτλους αξόνων.
Για να αλλάξετε τον τίτλο του γραφήματος, κάντε κλικ σε αυτόν για να επιλέξετε το κείμενο.
Τώρα πληκτρολογήστε έναν νέο τίτλο που περιγράφει το γράφημα.
Για να προσθέσετε τίτλους στον άξονα x και στον άξονα y, πρώτα μεταβείτε στα Εργαλεία γραφήματος > Σχεδίαση.
Τώρα, μεταβείτε στους τίτλους του άξονα > Πρωτεύον οριζόντιος.
Για να μετονομάσετε τον τίτλο του άξονα, πρώτα επιλέξτε το κείμενο και μετά πληκτρολογήστε έναν νέο τίτλο.
Τώρα, κατευθυνθείτε στους τίτλους του άξονα > Πρωτεύον κάθετο.
Θα εμφανιστεί ένας τίτλος άξονα.
Μετονομάστε αυτόν τον τίτλο επιλέγοντας το κείμενο και πληκτρολογώντας έναν νέο τίτλο.
Το γράφημά σας έχει πλέον ολοκληρωθεί.
Βήμα δεύτερο: Υπολογίστε την εξίσωση γραμμής και τη στατιστική του τετραγώνου R
Τώρα ας υπολογίσουμε την εξίσωση γραμμής και το στατιστικό R-τετράγωνο χρησιμοποιώντας τις ενσωματωμένες συναρτήσεις SLOPE, INTERCEPT και CORREL του Excel.
Στο φύλλο μας (στη σειρά 14) προσθέσαμε τίτλους για αυτές τις τρεις συναρτήσεις. Θα εκτελέσουμε τους πραγματικούς υπολογισμούς στα κελιά κάτω από αυτούς τους τίτλους.
Αρχικά, θα υπολογίσουμε την ΚΛΙΣΗ. Επιλέξτε το κελί A15.
Μεταβείτε στην επιλογή Τύποι > Περισσότερες συναρτήσεις > Στατιστικά > ΚΛΙΣΗ.
Στο πεδίο “Known_xs”, επιλέξτε ή πληκτρολογήστε τα κελιά της στήλης X-Value. Η σειρά των πεδίων “Known_ys” και “Known_xs” έχει σημασία στη συνάρτηση SLOPE.
Κάντε κλικ στο “OK”. Ο τελικός τύπος στη γραμμή τύπων θα πρέπει να μοιάζει με αυτό:
=SLOPE(C3:C12,B3:B12)
Σημειώστε ότι η τιμή που επιστρέφεται από τη συνάρτηση SLOPE στο κελί A15 ταιριάζει με την τιμή που εμφανίζεται στο γράφημα.
Στη συνέχεια, επιλέξτε το κελί B15 και, στη συνέχεια, μεταβείτε στην επιλογή Τύποι > Περισσότερες συναρτήσεις > Στατιστικά > INTERCEPT.
Επιλέξτε ή πληκτρολογήστε τα κελιά της στήλης X-Value για το πεδίο “Known_xs”. Η σειρά των πεδίων “Known_ys” και “Known_xs” έχει επίσης σημασία στη συνάρτηση INTERCEPT.
Κάντε κλικ στο “OK”. Ο τελικός τύπος στη γραμμή τύπων θα πρέπει να μοιάζει με αυτό:
=INTERCEPT(C3:C12,B3:B12)
Σημειώστε ότι η τιμή που επιστρέφεται από τη συνάρτηση INTERCEPT ταιριάζει με την τομή y που εμφανίζεται στο γράφημα.
Στη συνέχεια, επιλέξτε το κελί C15 και μεταβείτε στην επιλογή Τύποι > Περισσότερες συναρτήσεις > Στατιστικά > CORREL.
Επιλέξτε ή πληκτρολογήστε την άλλη από τις δύο περιοχές κελιών για το πεδίο “Array2”.
Κάντε κλικ στο “OK”. Ο τύπος πρέπει να μοιάζει με αυτό στη γραμμή τύπων:
=CORREL(B3:B12,C3:C12)
Σημειώστε ότι η τιμή που επιστρέφεται από τη συνάρτηση CORREL δεν ταιριάζει με την τιμή “r-squared” στο γράφημα. Η συνάρτηση CORREL επιστρέφει “R”, επομένως πρέπει να την τετραγωνίσουμε για να υπολογίσουμε το “R-τετράγωνο”.
Κάντε κλικ στο εσωτερικό της γραμμής συναρτήσεων και προσθέστε το “^2” στο τέλος του τύπου για να τετραγωνίσετε την τιμή που επιστρέφεται από τη συνάρτηση CORREL. Ο ολοκληρωμένος τύπος θα πρέπει τώρα να μοιάζει με αυτό:
=CORREL(B3:B12,C3:C12)^2
Πατήστε Enter.
Μετά την αλλαγή του τύπου, η τιμή “R-squared” ταιριάζει πλέον με αυτή που εμφανίζεται στο γράφημα.
Βήμα τρίτο: Ρυθμίστε τύπους για γρήγορο υπολογισμό τιμών
Τώρα μπορούμε να χρησιμοποιήσουμε αυτές τις τιμές σε απλούς τύπους για να προσδιορίσουμε τη συγκέντρωση αυτού του «άγνωστου» διαλύματος ή ποια είσοδο θα πρέπει να εισαγάγουμε στον κωδικό ώστε το μάρμαρο να πετάξει μια ορισμένη απόσταση.
Αυτά τα βήματα θα ρυθμίσουν τους τύπους που απαιτούνται για να μπορέσετε να εισαγάγετε μια τιμή X ή μια τιμή Y και να λάβετε την αντίστοιχη τιμή με βάση την καμπύλη βαθμονόμησης.
Η εξίσωση της γραμμής βέλτιστης προσαρμογής έχει τη μορφή “Υ-τιμή = ΚΛΙΣΗ * Χ-τιμή + ΔΙΑΚΟΠΗ”, επομένως η επίλυση της “τιμής Υ” γίνεται πολλαπλασιάζοντας την τιμή Χ και ΚΛΙΣΗ και στη συνέχεια προσθέτοντας το INTERCEPT.
Για παράδειγμα, βάζουμε το μηδέν ως την τιμή X. Η τιμή Y που επιστρέφεται θα πρέπει να είναι ίση με το INTERCEPT της γραμμής καλύτερης προσαρμογής. Ταιριάζει, επομένως γνωρίζουμε ότι ο τύπος λειτουργεί σωστά.
Η επίλυση της τιμής X με βάση μια τιμή Y γίνεται αφαιρώντας το INTERCEPT από την τιμή Y και διαιρώντας το αποτέλεσμα με το SLOPE:
X-value=(Y-value-INTERCEPT)/SLOPE
Ως παράδειγμα, χρησιμοποιήσαμε το INTERCEPT ως τιμή Y. Η τιμή X που επιστρέφεται πρέπει να είναι ίση με μηδέν, αλλά η τιμή που επιστρέφεται είναι 3,14934E-06. Η τιμή που επιστράφηκε δεν είναι μηδέν επειδή περικόψαμε κατά λάθος το αποτέλεσμα INTERCEPT κατά την πληκτρολόγηση της τιμής. Ωστόσο, ο τύπος λειτουργεί σωστά, επειδή το αποτέλεσμα του τύπου είναι 0,00000314934, το οποίο είναι ουσιαστικά μηδέν.
Μπορείτε να εισαγάγετε οποιαδήποτε τιμή X θέλετε στο πρώτο κελί με παχιά περιθώρια και το Excel θα υπολογίσει αυτόματα την αντίστοιχη τιμή Y.
Η εισαγωγή οποιασδήποτε τιμής Y στο δεύτερο κελί με παχιά περιθώρια θα δώσει την αντίστοιχη τιμή X. Αυτός ο τύπος είναι αυτός που θα χρησιμοποιούσατε για να υπολογίσετε τη συγκέντρωση αυτού του διαλύματος ή ποια είσοδο χρειάζεται για να εκτοξευτεί το μάρμαρο σε μια συγκεκριμένη απόσταση.
Σε αυτήν την περίπτωση, το όργανο διαβάζει “5”, οπότε η βαθμονόμηση θα υποδηλώνει συγκέντρωση 4,94 ή θέλουμε το μάρμαρο να διανύσει πέντε μονάδες απόστασης, οπότε η βαθμονόμηση προτείνει να εισαγάγουμε το 4,94 ως μεταβλητή εισόδου για το πρόγραμμα που ελέγχει τον εκτοξευτή μαρμάρου. Μπορούμε να είμαστε αρκετά σίγουροι για αυτά τα αποτελέσματα λόγω της υψηλής τιμής R-squared σε αυτό το παράδειγμα.