Δημιουργία ενός μοντέλου γραμμικής παλινδρόμησης στο Excel

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

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

Βασικές τακτικές

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

Σημαντικές εκτιμήσεις

Υπάρχουν μερικές κρίσιμες υποθέσεις σχετικά με το σύνολο δεδομένων που πρέπει να είναι αληθές για να προχωρήσετε με μια ανάλυση παλινδρόμησης:

1. Οι μεταβλητές πρέπει να είναι πραγματικά ανεξάρτητες (χρησιμοποιώντας τεστ Chi-square).
2. Τα δεδομένα δεν πρέπει να έχουν διαφορετικές διακυμάνσεις σφάλματος (αυτό ονομάζεται ετεροσκεδαστικότητα (επίσης ετερόκλητος χαρακτήρας)).
3. Οι όροι σφάλματος κάθε μεταβλητής πρέπει να είναι ανεξάρτητοι. Αν όχι, αυτό σημαίνει ότι οι μεταβλητές συσχετίζονται σειριακά.

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

Εκτέλεση μιας παλινδρόμησης στο Excel

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

Χρησιμοποιώντας το εργαλείο ανάλυσης δεδομένων, η δημιουργία μιας παλινδρόμησης είναι μερικά μόνο κλικ.

Η ανεξάρτητη μεταβλητή πηγαίνει στην περιοχή Χ.

Δεδομένων των επιστροφών του S & P 500, λέμε ότι θέλουμε να μάθουμε αν μπορούμε να υπολογίσουμε τη δύναμη και τη σχέση των αποδόσεων της Visa (V). Το απόθεμα Visa (V) επιστρέφει τη στήλη δεδομένων 1 ως εξαρτώμενη μεταβλητή. Το S & P 500 επιστρέφει τη στήλη 2 ως ανεξάρτητη μεταβλητή.

1. Επιλέξτε "Δεδομένα" από τη γραμμή εργαλείων. Εμφανίζεται το μενού "Δεδομένα".
2. Επιλέξτε "Ανάλυση δεδομένων". Εμφανίζεται το παράθυρο διαλόγου Ανάλυση δεδομένων - Εργαλεία ανάλυσης δεδομένων.
3. Από το μενού, επιλέξτε "Regression" και κάντε κλικ στο "OK".
4. Στο παράθυρο διαλόγου Regression, κάντε κλικ στο πλαίσιο "Input Y Range" και επιλέξτε τα εξαρτώμενα μεταβλητά δεδομένα (επιστροφές μετοχών Visa (V)).
5. Κάντε κλικ στο πλαίσιο "Εύρος εισαγωγής X" και επιλέξτε τα ανεξάρτητα μεταβλητά δεδομένα (επιστρέφει το S & P 500).
6. Κάντε κλικ στο "OK" για να εκτελέσετε τα αποτελέσματα.

[Σημείωση: Εάν ο πίνακας φαίνεται μικρός, κάντε δεξί κλικ στην εικόνα και ανοίξτε σε νέα καρτέλα για υψηλότερη ανάλυση.]

Ερμηνεύστε τα αποτελέσματα

Χρησιμοποιώντας αυτά τα δεδομένα (το ίδιο από το άρθρο μας σε R-τετράγωνο), έχουμε τον ακόλουθο πίνακα:

Η τιμή R2, επίσης γνωστή ως συντελεστής προσδιορισμού, μετρά την αναλογία μεταβολής της εξαρτώμενης μεταβλητής που εξηγείται από την ανεξάρτητη μεταβλητή ή πόσο καλά προσαρμόζεται το μοντέλο παλινδρόμησης στα δεδομένα. Η τιμή R ² κυμαίνεται από 0 έως 1 και μια υψηλότερη τιμή υποδηλώνει καλύτερη προσαρμογή. Η τιμή p ή η τιμή πιθανότητας κυμαίνεται επίσης από 0 έως 1 και υποδεικνύει αν η δοκιμή είναι σημαντική. Σε αντίθεση με την τιμή R ², μια μικρότερη τιμή p είναι ευνοϊκή, καθώς δείχνει μια συσχέτιση μεταξύ των εξαρτημένων και των ανεξάρτητων μεταβλητών.

Καταγραφή μιας παλινδρόμησης στο Excel

Μπορούμε να καταγράψουμε μια παλινδρόμηση στο Excel επισημαίνοντας τα δεδομένα και χαρτογραφώντας το ως διάσπαρτα. Για να προσθέσετε μια γραμμή παλινδρόμησης, επιλέξτε "Διάταξη" από το μενού "Εργαλεία γραφήματος". Στο πλαίσιο διαλόγου επιλέξτε "Trendline" και, στη συνέχεια, "Linear Trendline". Για να προσθέσετε την τιμή R ², επιλέξτε "More Trendline Options" από το μενού "Trendline". Τέλος, επιλέξτε "Display R-squared value on chart." Το οπτικό αποτέλεσμα συνοψίζει τη δύναμη της σχέσης, όπως και στον παραπάνω πίνακα.