Σήμερα θα δούμε: Οι αληθινοί γνώστες της μουσικής γνωρίζουν ότι για την ποιότητα...
Η γραμμή παλινδρόμησης είναι μια γραφική αντανάκλαση της σχέσης μεταξύ των φαινομένων. Μπορείτε να δημιουργήσετε ξεκάθαρα μια γραμμή παλινδρόμησης στο Excel.
Για να το κάνετε αυτό χρειάζεστε:
1. Ανοίξτε το Excel
2.Δημιουργία στηλών δεδομένων. Στο παράδειγμά μας, θα οικοδομήσουμε μια γραμμή παλινδρόμησης, ή σχέση, μεταξύ επιθετικότητας και αυτοαμφισβήτησης στα παιδιά της πρώτης τάξης. Στο πείραμα συμμετείχαν 30 παιδιά, τα δεδομένα παρουσιάζονται στον πίνακα Excel:
1 στήλη - αριθμός θέματος
2 στήλη - επιθετικότητασε σημεία
3 στήλη - αυτοαμφιβολίασε σημεία
3. Στη συνέχεια, πρέπει να επιλέξετε και τις δύο στήλες (χωρίς το όνομα της στήλης), κάντε κλικ στην καρτέλα εισάγω , επιλέγω σημείο , και επιλέξτε την πρώτη από τις προτεινόμενες διατάξεις τελεία με μαρκαδόρους .
4. Έχουμε λοιπόν ένα πρότυπο για τη γραμμή παλινδρόμησης - το λεγόμενο - διασκορπίζω οικόπεδο. Για να μεταβείτε στη γραμμή παλινδρόμησης, πρέπει να κάνετε κλικ στο σχήμα που προκύπτει, πατήστε το tab κατασκευαστής, βρείτε στον πίνακα διατάξεις γραφημάτων και επιλέξτε Μ ΕΝΑ ket9 , λέει επίσης f(x)
5. Άρα, έχουμε μια γραμμή παλινδρόμησης. Το γράφημα δείχνει επίσης την εξίσωσή του και το τετράγωνο του συντελεστή συσχέτισης
6. Το μόνο που μένει είναι να προσθέσουμε το όνομα του γραφήματος και το όνομα των αξόνων. Επίσης, εάν θέλετε, μπορείτε να αφαιρέσετε το υπόμνημα, να μειώσετε τον αριθμό των οριζόντιων γραμμών πλέγματος (καρτέλα σχέδιο , τότε καθαρά ). Οι βασικές αλλαγές και ρυθμίσεις γίνονται στην καρτέλα Σχέδιο
Η γραμμή παλινδρόμησης κατασκευάστηκε σε MS Excel. Τώρα μπορείτε να το προσθέσετε στο κείμενο της εργασίας.
Το πακέτο MS Excel σάς επιτρέπει να κάνετε το μεγαλύτερο μέρος της εργασίας πολύ γρήγορα όταν κατασκευάζετε μια εξίσωση γραμμικής παλινδρόμησης. Είναι σημαντικό να κατανοήσουμε πώς να ερμηνεύσουμε τα αποτελέσματα που λαμβάνονται. Για να δημιουργήσετε ένα μοντέλο παλινδρόμησης, πρέπει να επιλέξετε Tools\Data Analysis\Regression (στο Excel 2007 αυτή η λειτουργία βρίσκεται στο μπλοκ Data/Data Analysis/Regression). Στη συνέχεια, αντιγράψτε τα αποτελέσματα σε ένα μπλοκ για ανάλυση.
Η παλινδρόμηση και η ανάλυση συσχέτισης είναι μέθοδοι στατιστικής έρευνας. Αυτοί είναι οι πιο συνηθισμένοι τρόποι εμφάνισης της εξάρτησης μιας παραμέτρου από μία ή περισσότερες ανεξάρτητες μεταβλητές.
Παρακάτω, χρησιμοποιώντας συγκεκριμένα πρακτικά παραδείγματα, θα εξετάσουμε αυτές τις δύο πολύ δημοφιλείς αναλύσεις μεταξύ των οικονομολόγων. Θα δώσουμε επίσης ένα παράδειγμα απόκτησης αποτελεσμάτων όταν τα συνδυάζουμε.
Ανάλυση παλινδρόμησης στο Excel
Δείχνει την επίδραση ορισμένων τιμών (ανεξάρτητων, ανεξάρτητων) στην εξαρτημένη μεταβλητή. Για παράδειγμα, πώς εξαρτάται ο αριθμός του οικονομικά ενεργού πληθυσμού από τον αριθμό των επιχειρήσεων, τους μισθούς και άλλες παραμέτρους. Ή: πώς οι ξένες επενδύσεις, οι τιμές της ενέργειας κ.λπ. επηρεάζουν το επίπεδο του ΑΕΠ.
Το αποτέλεσμα της ανάλυσης σάς επιτρέπει να επισημάνετε προτεραιότητες. Και με βάση τους κύριους παράγοντες, προβλέψτε, σχεδιάστε την ανάπτυξη των τομέων προτεραιότητας και λάβετε αποφάσεις διαχείρισης.
Η παλινδρόμηση συμβαίνει:
- γραμμικό (y = a + bx);
- παραβολική (y = a + bx + cx 2);
- εκθετική (y = a * exp(bx));
- ισχύς (y = a*x^b);
- υπερβολική (y = b/x + a);
- λογαριθμική (y = b * 1n(x) + a);
- εκθετική (y = a * b^x).
Ας δούμε ένα παράδειγμα κατασκευής ενός μοντέλου παλινδρόμησης στο Excel και ερμηνείας των αποτελεσμάτων. Ας πάρουμε τον γραμμικό τύπο παλινδρόμησης.
Εργο. Σε 6 επιχειρήσεις, αναλύθηκε ο μέσος μηνιαίος μισθός και ο αριθμός των εργαζομένων που αποχωρούν. Είναι απαραίτητο να προσδιοριστεί η εξάρτηση του αριθμού των εργαζομένων που αποχωρούν από τον μέσο μισθό.
Το μοντέλο γραμμικής παλινδρόμησης έχει την ακόλουθη μορφή:
Y = a 0 + a 1 x 1 +…+a k x k.
Όπου a είναι συντελεστές παλινδρόμησης, x είναι μεταβλητές που επηρεάζουν, k είναι ο αριθμός των παραγόντων.
Στο παράδειγμά μας, το Y είναι ο δείκτης αποχώρησης εργαζομένων. Ο παράγοντας που επηρεάζει είναι οι μισθοί (x).
Το Excel διαθέτει ενσωματωμένες λειτουργίες που μπορούν να σας βοηθήσουν να υπολογίσετε τις παραμέτρους ενός μοντέλου γραμμικής παλινδρόμησης. Αλλά το πρόσθετο "Πακέτο ανάλυσης" θα το κάνει πιο γρήγορα.
Ενεργοποιούμε ένα ισχυρό αναλυτικό εργαλείο:
Μόλις ενεργοποιηθεί, το πρόσθετο θα είναι διαθέσιμο στην καρτέλα Δεδομένα.
Τώρα ας κάνουμε την ίδια την ανάλυση παλινδρόμησης.
Πρώτα απ 'όλα, δίνουμε προσοχή στο R-τετράγωνο και τους συντελεστές.
Το R-τετράγωνο είναι ο συντελεστής προσδιορισμού. Στο παράδειγμά μας – 0,755 ή 75,5%. Αυτό σημαίνει ότι οι υπολογισμένες παράμετροι του μοντέλου εξηγούν το 75,5% της σχέσης μεταξύ των παραμέτρων που μελετήθηκαν. Όσο μεγαλύτερος είναι ο συντελεστής προσδιορισμού, τόσο καλύτερο είναι το μοντέλο. Καλό - πάνω από 0,8. Κακό – λιγότερο από 0,5 (μια τέτοια ανάλυση δύσκολα μπορεί να θεωρηθεί λογική). Στο παράδειγμά μας - "όχι κακό".
Ο συντελεστής 64,1428 δείχνει τι θα είναι το Y εάν όλες οι μεταβλητές στο υπό εξέταση μοντέλο είναι ίσες με 0. Δηλαδή, η τιμή της αναλυόμενης παραμέτρου επηρεάζεται επίσης από άλλους παράγοντες που δεν περιγράφονται στο μοντέλο.
Ο συντελεστής -0,16285 δείχνει το βάρος της μεταβλητής X στο Y. Δηλαδή, ο μέσος μηνιαίος μισθός σε αυτό το μοντέλο επηρεάζει τον αριθμό των παραιτητών με βάρος -0,16285 (αυτός είναι ένας μικρός βαθμός επιρροής). Το σύμβολο «-» υποδηλώνει αρνητικό αντίκτυπο: όσο υψηλότερος είναι ο μισθός, τόσο λιγότερα άτομα παραιτούνται. Που είναι δίκαιο.
Ανάλυση συσχέτισης στο Excel
Η ανάλυση συσχέτισης βοηθά στον προσδιορισμό του εάν υπάρχει σχέση μεταξύ των δεικτών σε ένα ή δύο δείγματα. Για παράδειγμα, μεταξύ του χρόνου λειτουργίας ενός μηχανήματος και του κόστους των επισκευών, της τιμής του εξοπλισμού και της διάρκειας λειτουργίας, του ύψους και του βάρους των παιδιών κ.λπ.
Εάν υπάρχει σύνδεση, τότε η αύξηση της μιας παραμέτρου οδηγεί σε αύξηση (θετική συσχέτιση) ή μείωση (αρνητική) της άλλης. Η ανάλυση συσχέτισης βοηθά τον αναλυτή να προσδιορίσει εάν η τιμή ενός δείκτη μπορεί να χρησιμοποιηθεί για να προβλέψει την πιθανή τιμή ενός άλλου.
Ο συντελεστής συσχέτισης συμβολίζεται με r. Διαφέρει από +1 έως -1. Η ταξινόμηση των συσχετισμών για διαφορετικές περιοχές θα είναι διαφορετική. Όταν ο συντελεστής είναι 0, δεν υπάρχει γραμμική σχέση μεταξύ των δειγμάτων.
Ας δούμε πώς να βρείτε τον συντελεστή συσχέτισης χρησιμοποιώντας το Excel.
Για την εύρεση ζευγαρωμένων συντελεστών, χρησιμοποιείται η συνάρτηση CORREL.
Στόχος: Προσδιορίστε εάν υπάρχει σχέση μεταξύ του χρόνου λειτουργίας ενός τόρνου και του κόστους συντήρησής του.
Τοποθετήστε τον κέρσορα σε οποιοδήποτε κελί και πατήστε το κουμπί fx.
- Στην κατηγορία «Στατιστικά», επιλέξτε τη συνάρτηση CORREL.
- Επιχείρημα "Array 1" - το πρώτο εύρος τιμών - χρόνος λειτουργίας μηχανής: A2:A14.
- Επιχείρημα "Array 2" - δεύτερο εύρος τιμών - κόστος επισκευής: B2:B14. Κάντε κλικ στο OK.
Για να προσδιορίσετε τον τύπο σύνδεσης, πρέπει να εξετάσετε τον απόλυτο αριθμό του συντελεστή (κάθε πεδίο δραστηριότητας έχει τη δική του κλίμακα).
Για ανάλυση συσχέτισης πολλών παραμέτρων (περισσότερων από 2), είναι πιο βολικό να χρησιμοποιήσετε το "Data Analysis" (το πρόσθετο "Analysis Package"). Πρέπει να επιλέξετε συσχέτιση από τη λίστα και να ορίσετε τον πίνακα. Ολοι.
Οι προκύπτοντες συντελεστές θα εμφανιστούν στον πίνακα συσχέτισης. Τοιουτοτροπώς:
Ανάλυση συσχέτισης και παλινδρόμησης
Στην πράξη, αυτές οι δύο τεχνικές χρησιμοποιούνται συχνά μαζί.
Παράδειγμα:
Τώρα τα δεδομένα της ανάλυσης παλινδρόμησης έχουν γίνει ορατά.
Παλινδρόμηση στο Excel
Η επεξεργασία στατιστικών δεδομένων μπορεί επίσης να πραγματοποιηθεί χρησιμοποιώντας το πρόσθετο Πακέτο Ανάλυσης στο υποστοιχείο του μενού «Υπηρεσία». Στο Excel 2003, αν ανοίξετε ΥΠΗΡΕΣΙΑ, δεν μπορούμε να βρούμε την καρτέλα ΑΝΑΛΥΣΗ ΔΕΔΟΜΕΝΩΝ, μετά κάντε κλικ στο αριστερό κουμπί του ποντικιού για να ανοίξετε την καρτέλα ΥΠΕΡΚΑΤΑΣΚΕΥΕΣκαι απέναντι από το σημείο ΠΑΚΕΤΟ ΑΝΑΛΥΣΗΣΚάντε κλικ στο αριστερό κουμπί του ποντικιού για να βάλετε ένα σημάδι επιλογής (Εικ. 17).
Ρύζι. 17. Παράθυρο ΥΠΕΡΚΑΤΑΣΚΕΥΕΣ
Μετά από αυτό στο μενού ΥΠΗΡΕΣΙΑεμφανίζεται η καρτέλα ΑΝΑΛΥΣΗ ΔΕΔΟΜΕΝΩΝ.
Στο Excel 2007 για εγκατάσταση ΠΑΚΕΤΟ ΑΝΑΛΥΣΗΣπρέπει να κάνετε κλικ στο κουμπί OFFICE στην επάνω αριστερή γωνία του φύλλου (Εικ. 18α). Στη συνέχεια, κάντε κλικ στο κουμπί ΡΥΘΜΙΣΕΙΣ EXCEL. Στο παράθυρο που εμφανίζεται ΡΥΘΜΙΣΕΙΣ EXCELκάντε αριστερό κλικ στο αντικείμενο ΥΠΕΡΚΑΤΑΣΚΕΥΕΣκαι στη δεξιά πλευρά της αναπτυσσόμενης λίστας επιλέξτε το στοιχείο ΠΑΚΕΤΟ ΑΝΑΛΥΣΗΣ.Επόμενο κλικ στο ΕΝΤΑΞΕΙ.
Ρύζι. 18. Εγκατάσταση ΠΑΚΕΤΟ ΑΝΑΛΥΣΗΣστο Excel 2007
Για να εγκαταστήσετε το Πακέτο Ανάλυσης, κάντε κλικ στο κουμπί ΠΑΩ,που βρίσκεται στο κάτω μέρος του ανοιχτού παραθύρου. Θα εμφανιστεί ένα παράθυρο όπως φαίνεται στο Σχ. 12. Βάλτε ένα τσιμπούρι μπροστά από ΠΑΚΕΤΟ ΑΝΑΛΥΣΗΣ.Στην καρτέλα ΔΕΔΟΜΕΝΑθα εμφανιστεί ένα κουμπί ΑΝΑΛΥΣΗ ΔΕΔΟΜΕΝΩΝ(Εικ. 19).
Από τα προτεινόμενα είδη, επιλέξτε το στοιχείο " ΟΠΙΣΘΟΔΡΟΜΗΣΗ" και κάντε κλικ σε αυτό με το αριστερό κουμπί του ποντικιού. Στη συνέχεια, κάντε κλικ στο OK.
Θα εμφανιστεί ένα παράθυρο όπως φαίνεται στο Σχ. 21
Εργαλείο ανάλυσης " ΟΠΙΣΘΟΔΡΟΜΗΣΗ» χρησιμοποιείται για την προσαρμογή ενός γραφήματος σε ένα σύνολο παρατηρήσεων χρησιμοποιώντας τη μέθοδο των ελαχίστων τετραγώνων. Η παλινδρόμηση χρησιμοποιείται για την ανάλυση της επίδρασης σε μια μεμονωμένη εξαρτημένη μεταβλητή των τιμών μιας ή περισσότερων ανεξάρτητων μεταβλητών. Για παράδειγμα, διάφοροι παράγοντες επηρεάζουν την αθλητική απόδοση ενός αθλητή, όπως η ηλικία, το ύψος και το βάρος. Είναι δυνατόν να υπολογιστεί ο βαθμός στον οποίο καθένας από αυτούς τους τρεις παράγοντες επηρεάζει την απόδοση ενός αθλητή και στη συνέχεια να χρησιμοποιήσει αυτά τα δεδομένα για να προβλέψει την απόδοση ενός άλλου αθλητή.
Το εργαλείο παλινδρόμησης χρησιμοποιεί τη συνάρτηση LINEST.
Πλαίσιο διαλόγου REGRESSION
Ετικέτες Επιλέξτε το πλαίσιο ελέγχου εάν η πρώτη σειρά ή η πρώτη στήλη του εύρους εισαγωγής περιέχει επικεφαλίδες. Καταργήστε αυτό το πλαίσιο ελέγχου εάν δεν υπάρχουν κεφαλίδες. Σε αυτήν την περίπτωση, οι κατάλληλες κεφαλίδες για τα δεδομένα του πίνακα εξόδου θα δημιουργηθούν αυτόματα.
Επίπεδο αξιοπιστίας Επιλέξτε το πλαίσιο ελέγχου για να συμπεριλάβετε ένα επιπλέον επίπεδο στον συνοπτικό πίνακα εξόδου. Στο κατάλληλο πεδίο, εισαγάγετε το επίπεδο εμπιστοσύνης που θέλετε να εφαρμόσετε, εκτός από το προεπιλεγμένο επίπεδο 95%.
Σταθερά - μηδέν Επιλέξτε το πλαίσιο ελέγχου για να αναγκάσετε τη γραμμή παλινδρόμησης να περάσει από την αρχή.
Εύρος εξόδου Εισαγάγετε μια αναφορά στο επάνω αριστερό κελί της περιοχής εξόδου. Παρέχετε τουλάχιστον επτά στήλες για τον συνοπτικό πίνακα εξόδου, ο οποίος θα περιλαμβάνει: αποτελέσματα ANOVA, συντελεστές, τυπικό σφάλμα του υπολογισμού Y, τυπικές αποκλίσεις, αριθμό παρατηρήσεων, τυπικά σφάλματα για συντελεστές.
Νέο φύλλο εργασίας Επιλέξτε αυτήν την επιλογή για να ανοίξετε ένα νέο φύλλο εργασίας στο βιβλίο εργασίας και να επικολλήσετε τα αποτελέσματα της ανάλυσης, ξεκινώντας από το κελί A1. Εάν είναι απαραίτητο, εισαγάγετε ένα όνομα για το νέο φύλλο στο πεδίο που βρίσκεται απέναντι από το αντίστοιχο κουμπί επιλογής.
Νέο βιβλίο εργασίας Επιλέξτε αυτήν την επιλογή για να δημιουργήσετε ένα νέο βιβλίο εργασίας με τα αποτελέσματα να προστίθενται σε ένα νέο φύλλο εργασίας.
Υπολείμματα Επιλέξτε το πλαίσιο ελέγχου για να συμπεριλάβετε υπολείμματα στον πίνακα εξόδου.
Τυποποιημένα υπολείμματα Επιλέξτε το πλαίσιο ελέγχου για να συμπεριλάβετε τυποποιημένα υπολείμματα στον πίνακα εξόδου.
Υπολειπόμενη γραφική παράσταση Επιλέξτε το πλαίσιο ελέγχου για να σχεδιάσετε τα υπολείμματα για κάθε ανεξάρτητη μεταβλητή.
Fit Plot Επιλέξτε το πλαίσιο ελέγχου για να σχεδιάσετε τις προβλεπόμενες έναντι των παρατηρούμενων τιμών.
Οικόπεδο κανονικής πιθανότηταςΕπιλέξτε το πλαίσιο ελέγχου για να σχεδιάσετε ένα κανονικό γράφημα πιθανοτήτων.
Λειτουργία LINEST
Για να πραγματοποιήσετε υπολογισμούς, επιλέξτε με τον κέρσορα το κελί στο οποίο θέλουμε να εμφανιστεί η μέση τιμή και πατήστε το πλήκτρο = στο πληκτρολόγιο. Στη συνέχεια, στο πεδίο Όνομα, υποδείξτε την επιθυμητή λειτουργία, για παράδειγμα ΜΕΣΟΣ(Εικ. 22).
Ρύζι. 22 Εύρεση συναρτήσεων στο Excel 2003
Αν στο χωράφι ΟΝΟΜΑτο όνομα της συνάρτησης δεν εμφανίζεται και, στη συνέχεια, κάντε αριστερό κλικ στο τρίγωνο δίπλα στο πεδίο και μετά θα εμφανιστεί ένα παράθυρο με μια λίστα συναρτήσεων. Εάν αυτή η λειτουργία δεν βρίσκεται στη λίστα, κάντε αριστερό κλικ στο στοιχείο της λίστας ΑΛΛΕΣ ΛΕΙΤΟΥΡΓΙΕΣ, θα εμφανιστεί ένα πλαίσιο διαλόγου FUNCTION MASTER, στο οποίο, χρησιμοποιώντας κατακόρυφη κύλιση, επιλέξτε την επιθυμητή λειτουργία, τονίστε την με τον κέρσορα και κάντε κλικ στο ΕΝΤΑΞΕΙ(Εικ. 23).
Ρύζι. 23. Οδηγός λειτουργιών
Για να αναζητήσετε μια συνάρτηση στο Excel 2007, μπορείτε να ανοίξετε οποιαδήποτε καρτέλα στο μενού, στη συνέχεια, για να πραγματοποιήσετε υπολογισμούς, επιλέξτε με τον κέρσορα το κελί στο οποίο θέλουμε να εμφανιστεί η μέση τιμή και πατήστε το πλήκτρο = στο πληκτρολόγιο. Στη συνέχεια, στο πεδίο Όνομα, καθορίστε τη συνάρτηση ΜΕΣΟΣ. Το παράθυρο για τον υπολογισμό της συνάρτησης είναι παρόμοιο με αυτό που εμφανίζεται στο Excel 2003.
Μπορείτε επίσης να επιλέξετε την καρτέλα Τύποι και να κάνετε αριστερό κλικ στο κουμπί στο μενού " ΕΙΣΑΓΩΓΗ ΣΥΝΑΡΤΗΣΗΣ"(Εικ. 24), θα εμφανιστεί ένα παράθυρο FUNCTION MASTER, η εμφάνιση του οποίου μοιάζει με το Excel 2003. Επίσης στο μενού μπορείτε να επιλέξετε αμέσως μια κατηγορία συναρτήσεων (πρόσφατα χρησιμοποιημένες, οικονομική, λογική, κείμενο, ημερομηνία και ώρα, μαθηματικές, άλλες συναρτήσεις) στις οποίες θα αναζητήσουμε τις επιθυμητές λειτουργία.
Ρύζι. 24 Επιλογή συνάρτησης στο Excel 2007
Λειτουργία LINESTυπολογίζει στατιστικά στοιχεία για μια σειρά χρησιμοποιώντας τη μέθοδο των ελαχίστων τετραγώνων για να υπολογίσει την ευθεία που προσεγγίζει καλύτερα τα διαθέσιμα δεδομένα και στη συνέχεια επιστρέφει έναν πίνακα που περιγράφει την ευθεία που προκύπτει. Μπορείτε επίσης να συνδυάσετε τη λειτουργία LINESTμε άλλες συναρτήσεις για τον υπολογισμό άλλων ειδών μοντέλων που είναι γραμμικά σε άγνωστες παραμέτρους (των οποίων οι άγνωστες παράμετροι είναι γραμμικές), συμπεριλαμβανομένων των σειρών πολυωνυμικών, λογαριθμικών, εκθετικών και ισχύος. Επειδή επιστρέφει έναν πίνακα τιμών, η συνάρτηση πρέπει να καθοριστεί ως τύπος πίνακα.
Η εξίσωση για μια ευθεία γραμμή είναι:
(σε περίπτωση πολλών περιοχών τιμών x),
όπου η εξαρτημένη τιμή y είναι συνάρτηση της ανεξάρτητης τιμής x, οι τιμές m είναι οι συντελεστές που αντιστοιχούν σε κάθε ανεξάρτητη μεταβλητή x και το b είναι μια σταθερά. Σημειώστε ότι τα y, x και m μπορούν να είναι διανύσματα. Λειτουργία LINESTεπιστρέφει έναν πίνακα 
. LINESTμπορεί επίσης να επιστρέψει πρόσθετα στατιστικά στοιχεία παλινδρόμησης.
LINEST(γνωστές_τιμές_y; γνωστές_τιμές_x; const; στατιστικά)
Known_y_values - το σύνολο των τιμών y που είναι ήδη γνωστά για τη σχέση.
Εάν ο πίνακας Known_y_values έχει μία στήλη, τότε κάθε στήλη στον πίνακα known_x_values αντιμετωπίζεται ως ξεχωριστή μεταβλητή.
Εάν ο πίνακας Known_y_values έχει μία σειρά, τότε κάθε σειρά στον πίνακα known_x_values αντιμετωπίζεται ως ξεχωριστή μεταβλητή.
Το Known_x-values είναι ένα προαιρετικό σύνολο τιμών x που είναι ήδη γνωστά για τη σχέση.
Ο πίνακας Known_x_values μπορεί να περιέχει ένα ή περισσότερα σύνολα μεταβλητών. Εάν χρησιμοποιείται μόνο μία μεταβλητή, τότε οι πίνακες Known_y_values και known_x_values μπορούν να έχουν οποιοδήποτε σχήμα - αρκεί να έχουν την ίδια διάσταση. Εάν χρησιμοποιούνται περισσότερες από μία μεταβλητές, τότε το know_y_values πρέπει να είναι διάνυσμα (δηλαδή, ένα διάστημα σε ύψος μίας γραμμής ή πλάτους μίας στήλης).
Εάν παραλειφθεί ο πίνακας_γνωστών_χ_τιμών, τότε ο πίνακας (1;2;3;...) θεωρείται ότι έχει το ίδιο μέγεθος με τον πίνακα_γνωστές_τιμές_y.
Το Const είναι μια δυαδική τιμή που καθορίζει εάν η σταθερά b απαιτείται να είναι ίση με 0.
Εάν το όρισμα "const" είναι TRUE ή παραλειφθεί, τότε η σταθερά b αξιολογείται ως συνήθως.
Εάν το όρισμα "const" είναι FALSE, τότε η τιμή του b ορίζεται στο 0 και οι τιμές του m επιλέγονται με τέτοιο τρόπο ώστε η σχέση να ικανοποιείται.
Στατιστικά - Μια δυαδική τιμή που καθορίζει εάν θα πρέπει να επιστραφούν πρόσθετα στατιστικά στοιχεία παλινδρόμησης.
Εάν τα στατιστικά στοιχεία είναι TRUE, το LINEST επιστρέφει επιπλέον στατιστικά στοιχεία παλινδρόμησης. Ο πίνακας που επιστρέφεται θα μοιάζει με αυτό: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).
Εάν τα στατιστικά στοιχεία είναι FALSE ή παραλείπονται, το LINEST επιστρέφει μόνο τους συντελεστές m και τη σταθερά b.
Πρόσθετα στατιστικά παλινδρόμησης.
Το παρακάτω σχήμα δείχνει τη σειρά με την οποία επιστρέφονται πρόσθετα στατιστικά στοιχεία παλινδρόμησης.
Σημειώσεις:
Οποιαδήποτε ευθεία μπορεί να περιγραφεί από την κλίση και την τομή της με τον άξονα y:
Κλίση (m): Για να προσδιορίσετε την κλίση μιας γραμμής, που συνήθως συμβολίζεται με m, παίρνετε δύο σημεία στη γραμμή και ; η κλίση θα είναι ίση με 
.
Y-τομή (b): Η τομή y μιας ευθείας, που συνήθως συμβολίζεται με b, είναι η τιμή y για το σημείο στο οποίο η ευθεία τέμνει τον άξονα y.
Η εξίσωση της ευθείας έχει τη μορφή . Εάν οι τιμές των m και b είναι γνωστές, τότε οποιοδήποτε σημείο στη γραμμή μπορεί να υπολογιστεί αντικαθιστώντας τις τιμές του y ή του x στην εξίσωση. Μπορείτε επίσης να χρησιμοποιήσετε τη συνάρτηση TREND.
Εάν υπάρχει μόνο μία ανεξάρτητη μεταβλητή x, μπορείτε να λάβετε την κλίση και την τομή y απευθείας χρησιμοποιώντας τους ακόλουθους τύπους:
Κλίση: INDEX(LINEST(γνωστές_y_τιμές; γνωστές_x_τιμές); 1)
Y-τομή: INDEX(LINEST(γνωστές_τιμές_y; γνωστές_χ_τιμές); 2)
Η ακρίβεια της προσέγγισης χρησιμοποιώντας την ευθεία γραμμή που υπολογίζεται από τη συνάρτηση LINEST εξαρτάται από το βαθμό διασποράς δεδομένων. Όσο πιο κοντά είναι τα δεδομένα σε μια ευθεία γραμμή, τόσο πιο ακριβές είναι το μοντέλο που χρησιμοποιείται από τη συνάρτηση LINEST. Η συνάρτηση LINEST χρησιμοποιεί ελάχιστα τετράγωνα για να καθορίσει την καλύτερη προσαρμογή στα δεδομένα. Όταν υπάρχει μόνο μία ανεξάρτητη μεταβλητή x, τα m και b υπολογίζονται χρησιμοποιώντας τους ακόλουθους τύπους:
όπου x και y είναι δείγματα μέσα, για παράδειγμα x = AVERAGE(γνωστά_x) και y = AVERAGE(γνωστά_y's).
Οι συναρτήσεις προσαρμογής LINEST και LGRFPRIBL μπορούν να υπολογίσουν την ευθεία γραμμή ή την εκθετική καμπύλη που ταιριάζει καλύτερα στα δεδομένα. Ωστόσο, δεν απαντούν στο ερώτημα ποιο από τα δύο αποτελέσματα είναι καταλληλότερο για την επίλυση του προβλήματος. Μπορείτε επίσης να αξιολογήσετε τη συνάρτηση TREND(known_y_values; known_x_values) για μια ευθεία γραμμή ή τη συνάρτηση GROWTH(known_y_values; known_x_values) για μια εκθετική καμπύλη. Αυτές οι συναρτήσεις, εκτός αν έχουν καθοριστεί new_x-values, επιστρέφουν έναν πίνακα υπολογισμένων τιμών y για τις πραγματικές x-values κατά μήκος μιας γραμμής ή καμπύλης. Στη συνέχεια, μπορείτε να συγκρίνετε τις υπολογιζόμενες τιμές με τις πραγματικές τιμές. Μπορείτε επίσης να δημιουργήσετε γραφήματα για οπτική σύγκριση.
Κατά την εκτέλεση ανάλυσης παλινδρόμησης, το Microsoft Excel υπολογίζει, για κάθε σημείο, το τετράγωνο της διαφοράς μεταξύ της προβλεπόμενης τιμής y και της πραγματικής τιμής y. Το άθροισμα αυτών των τετραγωνικών διαφορών ονομάζεται υπολειπόμενο άθροισμα τετραγώνων (ssresid). Στη συνέχεια, το Microsoft Excel υπολογίζει το συνολικό άθροισμα των τετραγώνων (sstotal). Αν const = TRUE ή η τιμή αυτού του ορίσματος δεν προσδιορίζεται, το συνολικό άθροισμα των τετραγώνων θα είναι ίσο με το άθροισμα των τετραγώνων των διαφορών μεταξύ των πραγματικών τιμών y και των μέσων τιμών y. Όταν const = FALSE, το συνολικό άθροισμα των τετραγώνων θα είναι ίσο με το άθροισμα των τετραγώνων των πραγματικών τιμών y (χωρίς να αφαιρείται η μέση τιμή y από τη μερική τιμή y). Το άθροισμα της παλινδρόμησης των τετραγώνων μπορεί στη συνέχεια να υπολογιστεί ως εξής: ssreg = sstotal - ssresid. Όσο μικρότερο είναι το υπολειπόμενο άθροισμα των τετραγώνων, τόσο μεγαλύτερη είναι η τιμή του συντελεστή προσδιορισμού r2, ο οποίος δείχνει πόσο καλά η εξίσωση που προκύπτει με την ανάλυση παλινδρόμησης εξηγεί τις σχέσεις μεταξύ των μεταβλητών. Ο συντελεστής r2 είναι ίσος με ssreg/sstotal.
Σε ορισμένες περιπτώσεις, μία ή περισσότερες στήλες X (αφήστε τις τιμές Y και X να βρίσκονται σε στήλες) δεν έχουν πρόσθετη κατηγοριματική τιμή σε άλλες στήλες X. Με άλλα λόγια, η αφαίρεση μιας ή περισσότερων στηλών X μπορεί να έχει ως αποτέλεσμα τον υπολογισμό των τιμών Y με την ίδια ακρίβεια. Σε αυτήν την περίπτωση, οι πλεονάζουσες στήλες X θα εξαιρεθούν από το μοντέλο παλινδρόμησης. Αυτό το φαινόμενο ονομάζεται «συγγραμμικότητα» επειδή οι πλεονάζουσες στήλες του X μπορούν να αναπαρασταθούν ως το άθροισμα πολλών μη περιττών στηλών. Η συνάρτηση LINEST ελέγχει για συγγραμμικότητα και αφαιρεί τυχόν περιττές στήλες X από το μοντέλο παλινδρόμησης, εάν τις εντοπίσει. Οι στήλες X που αφαιρέθηκαν μπορούν να αναγνωριστούν στην έξοδο LINEST με συντελεστή 0 και με τιμή se 0. Η κατάργηση μιας ή περισσότερων στηλών ως περιττών αλλάζει την τιμή του df επειδή εξαρτάται από τον αριθμό των στηλών X που χρησιμοποιούνται πραγματικά για σκοπούς πρόβλεψης. Για περισσότερες πληροφορίες σχετικά με τον υπολογισμό του df, δείτε το Παράδειγμα 4 παρακάτω Όταν το df αλλάζει λόγω της αφαίρεσης περιττών στηλών, αλλάζουν και οι τιμές των sey και F. Δεν συνιστάται η συχνή χρήση συγγραμμικότητας. Ωστόσο, θα πρέπει να χρησιμοποιείται εάν ορισμένες στήλες Χ περιέχουν 0 ή 1 ως δείκτη που υποδεικνύει εάν το θέμα του πειράματος ανήκει σε μια ξεχωριστή ομάδα. Εάν const = TRUE ή δεν έχει καθοριστεί μια τιμή για αυτό το όρισμα, το LINEST εισάγει μια επιπλέον στήλη X για να μοντελοποιήσει το σημείο τομής. Εάν υπάρχει μια στήλη με τιμές 1 για άνδρες και 0 για γυναίκες και υπάρχει στήλη με τιμές 1 για γυναίκες και 0 για άνδρες, τότε η τελευταία στήλη αφαιρείται επειδή μπορούν να ληφθούν οι τιμές της από τη στήλη "ανδρικός δείκτης".
Ο υπολογισμός του df για περιπτώσεις όπου οι X στήλες δεν αφαιρούνται από το μοντέλο λόγω συγγραμμικότητας γίνεται ως εξής: εάν υπάρχουν k στήλες γνωστές_x και η τιμή const = TRUE ή δεν καθορίζεται, τότε df = n – k – 1. Αν const = FALSE, τότε df = n - k. Και στις δύο περιπτώσεις, η αφαίρεση των στηλών X λόγω συγγραμμικότητας αυξάνει την τιμή df κατά 1.
Οι τύποι που επιστρέφουν πίνακες πρέπει να εισαχθούν ως τύποι πίνακα.
Όταν εισάγετε έναν πίνακα σταθερών ως όρισμα, για παράδειγμα, known_x_values, θα πρέπει να χρησιμοποιήσετε ένα ερωτηματικό για να διαχωρίσετε τις τιμές στην ίδια γραμμή και μια άνω και κάτω τελεία για να διαχωρίσετε γραμμές. Οι διαχωριστικοί χαρακτήρες ενδέχεται να διαφέρουν ανάλογα με τις ρυθμίσεις στο παράθυρο Γλώσσα και Ρυθμίσεις στον Πίνακα Ελέγχου.
Θα πρέπει να σημειωθεί ότι οι τιμές y που προβλέπονται από την εξίσωση παλινδρόμησης μπορεί να μην είναι σωστές εάν βρίσκονται εκτός του εύρους των τιμών y που χρησιμοποιήθηκαν για τον ορισμό της εξίσωσης.
Βασικός αλγόριθμος που χρησιμοποιείται στη συνάρτηση LINEST, διαφέρει από τον αλγόριθμο της κύριας συνάρτησης ΚΛΙΝΩΚαι ΤΟΜΗ. Η διαφορά μεταξύ αλγορίθμων μπορεί να οδηγήσει σε διαφορετικά αποτελέσματα με αβέβαια και συγγραμμικά δεδομένα. Για παράδειγμα, εάν τα σημεία δεδομένων ορίσματος Known_y_values είναι 0 και τα σημεία δεδομένων επιχειρήματος Known_x_values είναι 1, τότε:
Λειτουργία LINESTεπιστρέφει τιμή ίση με 0. Αλγόριθμος συνάρτησης LINESTχρησιμοποιείται για την επιστροφή κατάλληλων τιμών για συγγραμμικά δεδομένα, οπότε μπορεί να βρεθεί τουλάχιστον μία απάντηση.
Οι συναρτήσεις SLOPE και LINE επιστρέφουν το σφάλμα #DIV/0! Ο αλγόριθμος των συναρτήσεων SLOPE και INTERCEPT χρησιμοποιείται για την εύρεση μόνο μιας απάντησης, αλλά στην περίπτωση αυτή μπορεί να υπάρχουν πολλές.
Εκτός από τον υπολογισμό στατιστικών για άλλους τύπους παλινδρόμησης, το LINEST μπορεί να χρησιμοποιηθεί για τον υπολογισμό περιοχών για άλλους τύπους παλινδρόμησης, εισάγοντας συναρτήσεις των μεταβλητών x και y ως σειρές των μεταβλητών x και y για το LINEST. Για παράδειγμα, ο ακόλουθος τύπος:
LINEST(y_τιμές, x_values^COLUMN($A:$C))
λειτουργεί έχοντας μια στήλη τιμών Y και μια στήλη τιμών X για τον υπολογισμό μιας προσέγγισης κύβου (πολυώνυμο 3ου βαθμού) της ακόλουθης μορφής:
Ο τύπος μπορεί να τροποποιηθεί για τον υπολογισμό άλλων τύπων παλινδρόμησης, αλλά σε ορισμένες περιπτώσεις απαιτούνται προσαρμογές στις τιμές εξόδου και άλλα στατιστικά στοιχεία.
Η κατασκευή της γραμμικής παλινδρόμησης, η αξιολόγηση των παραμέτρων της και η σημασία τους μπορούν να πραγματοποιηθούν πολύ πιο γρήγορα κατά τη χρήση του πακέτου ανάλυσης Excel (Regression). Ας εξετάσουμε την ερμηνεία των αποτελεσμάτων που προέκυψαν στη γενική περίπτωση ( κεπεξηγηματικές μεταβλητές) σύμφωνα με το παράδειγμα 3.6.
Στον πίνακα στατιστικές παλινδρόμησηςδίνονται οι ακόλουθες τιμές:
Πολλαπλούς R – πολλαπλός συντελεστής συσχέτισης.
R- πλατεία– συντελεστής προσδιορισμού R 2 ;
Κανονικοποιήθηκε R - πλατεία– προσαρμοσμένο R 2 προσαρμοσμένο για τον αριθμό των βαθμών ελευθερίας.
Τυπικό σφάλμα– τυπικό σφάλμα παλινδρόμησης μικρό;
Παρατηρήσεις -αριθμός παρατηρήσεων n.
Στον πίνακα Ανάλυση διακύμανσηςδίνονται:
1. Στήλη df - αριθμός βαθμών ελευθερίας ίσος με
για χορδή Οπισθοδρόμηση df = κ;
για χορδή Υπόλοιποdf = n – κ – 1;
για χορδή Σύνολοdf = n– 1.
2. Στήλη SS -το άθροισμα των τετραγωνικών αποκλίσεων ίσο με
για χορδή Οπισθοδρόμηση ;
για χορδή Υπόλοιπο ;
για χορδή Σύνολο .
3. Στήλη MSαποκλίσεις που καθορίζονται από τον τύπο MS = SS/df:
για χορδή Οπισθοδρόμηση– διασπορά παραγόντων.
για χορδή Υπόλοιπο– υπολειπόμενη διακύμανση.
4. Στήλη φά – υπολογισμένη αξία φά-κριτήριο που υπολογίζεται με τον τύπο
φά = MS(οπισθοδρόμηση)/ MS(υπόλοιπο).
5. Στήλη Σημασία φά – τιμή επιπέδου σημαντικότητας που αντιστοιχεί στην υπολογιζόμενη φά-στατιστική .
Σημασία φά= FDIST( ΦΑ-στατιστική, df(οπισθοδρόμηση), df(υπόλοιπο)).
Αν σημασία φά < стандартного уровня значимости, то R 2 είναι στατιστικά σημαντικό.
| Πιθανότητα | Τυπικό σφάλμα | t-statistics | P-τιμή | Κάτω 95% | Κορυφαίο 95% | |
| Υ | 65,92 | 11,74 | 5,61 | 0,00080 | 38,16 | 93,68 |
| Χ | 0,107 | 0,014 | 7,32 | 0,00016 | 0,0728 | 0,142 |
Αυτός ο πίνακας δείχνει:
1. Πιθανότητα– τιμές συντελεστών ένα, σι.
2. Τυπικό σφάλμα– τυπικά σφάλματα των συντελεστών παλινδρόμησης S a, Sb.
3. t-στατιστική– υπολογισμένες τιμές t -κριτήρια που υπολογίζονται με τον τύπο:
t-statistic = Συντελεστές/Τυπικό σφάλμα.
4.R- αξία (σημασία t) είναι η τιμή του επιπέδου σημαντικότητας που αντιστοιχεί στην υπολογιζόμενη t-στατιστική.
R-αξία = ΣΠΟΥΔΑΤΗΣ(t-στατιστική, df(υπόλοιπο)).
Αν R-έννοια< стандартного уровня значимости, то соответствующий коэффициент статистически значим.
5. Κάτω 95% και Κορυφαίο 95%– κάτω και άνω όρια διαστημάτων εμπιστοσύνης 95% για τους συντελεστές της θεωρητικής γραμμικής εξίσωσης παλινδρόμησης.
| ΑΠΟΣΥΡΣΗ ΤΩΝ ΥΠΟΛΟΙΠΩΝ | ||
| Παρατήρηση | Προβλεπόμενο y | Υπολείμματα ε |
| 72,70 | -29,70 | |
| 82,91 | -20,91 | |
| 94,53 | -4,53 | |
| 105,72 | 5,27 | |
| 117,56 | 12,44 | |
| 129,70 | 19,29 | |
| 144,22 | 20,77 | |
| 166,49 | 24,50 | |
| 268,13 | -27,13 |
Στον πίνακα ΑΠΟΣΥΡΣΗ ΤΩΝ ΥΠΟΛΟΙΠΩΝυποδεικνύεται:
στη στήλη Παρατήρηση– αριθμός παρατήρησης·
στη στήλη Προλεχθείς y – υπολογισμένες τιμές της εξαρτημένης μεταβλητής.
στη στήλη Αποφάγια μι – τη διαφορά μεταξύ των παρατηρούμενων και των υπολογισμένων τιμών της εξαρτημένης μεταβλητής.
Παράδειγμα 3.6.Υπάρχουν στοιχεία (συμβατικές μονάδες) για το κόστος των τροφίμων yκαι κατά κεφαλήν εισόδημα xγια εννέα ομάδες οικογενειών:
| x | |||||||||
| y |
Χρησιμοποιώντας τα αποτελέσματα του πακέτου ανάλυσης Excel (Regression), θα αναλύσουμε την εξάρτηση του κόστους των τροφίμων από το κατά κεφαλήν εισόδημα.
Τα αποτελέσματα της ανάλυσης παλινδρόμησης γράφονται συνήθως με τη μορφή:
όπου τα τυπικά σφάλματα των συντελεστών παλινδρόμησης υποδεικνύονται σε παρένθεση.
Συντελεστές παλινδρόμησης ΕΝΑ = 65,92 και β= 0,107. Κατεύθυνση επικοινωνίας μεταξύ yΚαι xκαθορίζει το πρόσημο του συντελεστή παλινδρόμησης σι= 0,107, δηλ. η σύνδεση είναι άμεση και θετική. Συντελεστής σι= 0,107 δείχνει ότι με αύξηση του κατά κεφαλήν εισοδήματος κατά 1 συμβατικό. μονάδες Το κόστος των τροφίμων αυξάνεται κατά 0,107 συμβατικές μονάδες. μονάδες
Ας αξιολογήσουμε τη σημασία των συντελεστών του προκύπτοντος μοντέλου. Σημασία των συντελεστών ( α, β) ελέγχεται από t-δοκιμή:
P-τιμή ( ένα) = 0,00080 < 0,01 < 0,05
P-τιμή ( σι) = 0,00016 < 0,01 < 0,05,
επομένως, οι συντελεστές ( α, β) είναι σημαντικές σε επίπεδο 1%, και ακόμη περισσότερο στο επίπεδο σημαντικότητας 5%. Έτσι, οι συντελεστές παλινδρόμησης είναι σημαντικοί και το μοντέλο είναι επαρκές με τα αρχικά δεδομένα.
Τα αποτελέσματα της εκτίμησης παλινδρόμησης είναι συμβατά όχι μόνο με τις λαμβανόμενες τιμές των συντελεστών παλινδρόμησης, αλλά και με ένα ορισμένο σύνολο αυτών (διάστημα εμπιστοσύνης). Με πιθανότητα 95%, τα διαστήματα εμπιστοσύνης για τους συντελεστές είναι (38,16 – 93,68) για ένακαι (0,0728 – 0,142) για σι.
Η ποιότητα του μοντέλου αξιολογείται από τον συντελεστή προσδιορισμού R 2 .
Μέγεθος R 2 = 0,884 σημαίνει ότι ο παράγοντας κατά κεφαλήν εισόδημα μπορεί να εξηγήσει το 88,4% της διακύμανσης (σκέδασης) στα έξοδα διατροφής.
Σημασία R 2 ελέγχεται από ΦΑ-τεστ: σημασία φά = 0,00016 < 0,01 < 0,05, следовательно, RΤο 2 είναι σημαντικό σε επίπεδο 1%, και ακόμη περισσότερο στο επίπεδο σημαντικότητας 5%.
Στην περίπτωση γραμμικής παλινδρόμησης κατά ζεύγη, ο συντελεστής συσχέτισης μπορεί να οριστεί ως 
. Η λαμβανόμενη τιμή του συντελεστή συσχέτισης δείχνει ότι η σχέση μεταξύ των εξόδων διατροφής και του κατά κεφαλήν εισοδήματος είναι πολύ στενή.
