Το πακέτο MS Excel επιτρέπει την κατασκευή μιας εξίσωσης γραμμικής παλινδρόμησης πλέονκάνε τη δουλειά πολύ γρήγορα. Είναι σημαντικό να κατανοήσουμε πώς να ερμηνεύσουμε τα αποτελέσματα.

Απαιτεί πρόσθετο για εργασία Πακέτο ανάλυσης, το οποίο πρέπει να είναι ενεργοποιημένο στο στοιχείο μενού Υπηρεσία\Πρόσθετα

Στο Excel 2007, για να ενεργοποιήσετε το Πακέτο ανάλυσης, κάντε κλικ στην επιλογή Μετάβαση στον αποκλεισμό Επιλογές Excelπατώντας το κουμπί στα αριστερά πάνω γωνίακαι μετά το κουμπί Επιλογές Excel» στο κάτω μέρος του παραθύρου:

Για να δημιουργήσετε ένα μοντέλο παλινδρόμησης, επιλέξτε το στοιχείο Service\Data Analysis\Regression. (Στο Excel 2007, αυτή η λειτουργία βρίσκεται στο Δεδομένα / Ανάλυση Δεδομένων / Παλινδρόμηση). Θα εμφανιστεί ένα παράθυρο διαλόγου που πρέπει να συμπληρωθεί:

1) Διάστημα εισαγωγής ΥΤο ¾ περιέχει έναν σύνδεσμο προς κελιά που περιέχουν τις τιμές του χαρακτηριστικού που προκύπτει y. Οι τιμές πρέπει να βρίσκονται σε μια στήλη.

2) Διάστημα εισαγωγής ΧΤο ¾ περιέχει έναν σύνδεσμο προς κελιά που περιέχουν τις τιμές των παραγόντων. Οι τιμές πρέπει να είναι σε στήλες.

3) Υπογράψτε Ετικέτεςορίστε εάν τα πρώτα κελιά περιέχουν επεξηγηματικό κείμενο (ετικέτες δεδομένων).

4) Επίπεδο αξιοπιστίαςΤο ¾ είναι το επίπεδο εμπιστοσύνης, το οποίο θεωρείται ότι είναι 95% από προεπιλογή. Εάν αυτή η τιμή δεν σας ταιριάζει, τότε πρέπει να ενεργοποιήσετε αυτήν τη δυνατότητα και να εισαγάγετε την απαιτούμενη τιμή.

5) Σημάδι Μηδενική σταθεράπεριλαμβάνεται εάν είναι απαραίτητο να κατασκευαστεί μια εξίσωση στην οποία η ελεύθερη μεταβλητή ;

6) Επιλογές εξόδουκαθορίσει πού θα τοποθετηθούν τα αποτελέσματα. Προεπιλεγμένη λειτουργία κατασκευής Νέο φύλλο εργασίας;

7) Μπλοκ Λείψανασας επιτρέπει να συμπεριλάβετε την έξοδο των υπολειμμάτων και την κατασκευή των γραφημάτων τους.

Το αποτέλεσμα είναι πληροφορίες που περιέχουν όλα απαραίτητες πληροφορίεςκαι ομαδοποιούνται σε τρία μπλοκ: Στατιστικά παλινδρόμησης , Ανάλυση της διακύμανσης , Ανάληψη υπολοίπου. Ας τα εξετάσουμε λεπτομερέστερα.

1. Στατιστικά παλινδρόμησης:

πολλαπλούς Rορίζεται από τον τύπο ( Συντελεστής συσχέτισης Pearson);

R (συντελεστή προσδιορισμού);

Κανονικοποιήθηκε R-Το τετράγωνο υπολογίζεται με τον τύπο (χρησιμοποιείται για πολλαπλή παλινδρόμηση);

τυπικό σφάλμα μικρόυπολογίζεται με τον τύπο ;

Παρατηρήσεις ¾ είναι ο όγκος των δεδομένων n.

2. Ανάλυση της διακύμανσης, γραμμή Οπισθοδρόμηση:

Παράμετρος dfισοδυναμεί Μ(αριθμός συνόλων παραγόντων Χ);

Παράμετρος SSκαθορίζεται από τον τύπο ;

Παράμετρος Κυρίακαθορίζεται από τον τύπο ;

Στατιστική φάκαθορίζεται από τον τύπο ;

Σημασία φά. Εάν ο αριθμός που προκύπτει υπερβαίνει το , τότε η υπόθεση γίνεται αποδεκτή (χωρίς γραμμική σχέση), διαφορετικά η υπόθεση γίνεται αποδεκτή (υπάρχει γραμμική σχέση).

3. Ανάλυση της διακύμανσης, γραμμή Υπόλοιπο:

Παράμετρος dfισούται ;

Παράμετρος SSκαθορίζεται από τον τύπο ;

Παράμετρος Κυρίακαθορίζεται από τον τύπο.

4. Ανάλυση της διακύμανσης, γραμμή Σύνολοπεριέχει το άθροισμα των δύο πρώτων στηλών.

5. Ανάλυση της διακύμανσης, γραμμή Υ-τομήπεριέχει την τιμή του συντελεστή , τυπικό σφάλμα και t-στατιστική.

Π-τιμή ¾ είναι η τιμή των επιπέδων σημαντικότητας που αντιστοιχούν στα υπολογιζόμενα t- στατιστικολόγοι. Καθορίζεται από τον ΣΠΟΥΔΑΤΗ( t-στατιστική; ). Αν ένα Π-η τιμή υπερβαίνει το , τότε η αντίστοιχη μεταβλητή είναι στατιστικά ασήμαντη και μπορεί να εξαιρεθεί από το μοντέλο.

κάτω 95%και Κορυφαίο 95%Το ¾ είναι το κατώτερο και το ανώτερο όριο του 95 τοις εκατό διαστήματα εμπιστοσύνηςγια τους συντελεστές θεωρητική εξίσωσηγραμμικής παλινδρόμησης. Εάν στην εισαγωγή δεδομένων μπλοκάρετε την τιμή επίπεδο αυτοπεποίθησηςέμεινε από προεπιλογή, τότε οι δύο τελευταίες στήλες θα αντιγράψουν τις προηγούμενες. Εάν ο χρήστης έχει εισαγάγει μια προσαρμοσμένη τιμή εμπιστοσύνης, τότε οι δύο τελευταίες στήλες περιέχουν τις τιμές κάτω και άνω ορίου για το καθορισμένο επίπεδο εμπιστοσύνης.

6. Ανάλυση της διακύμανσης, οι σειρές περιέχουν τις τιμές των συντελεστών, τυπικά σφάλματα, t-στατιστικολόγος, Π-τιμές και διαστήματα εμπιστοσύνης για τα αντίστοιχα.

7. Μπλοκ Ανάληψη υπολοίπουπεριέχει τις τιμές του προβλεπόμενου y(στη σημειογραφία μας είναι ) και τα υπόλοιπα .

28 Οκτ

Καλησπέρα, αγαπητοί αναγνώστες του ιστολογίου! Σήμερα θα μιλήσουμε για μη γραμμικές παλινδρομήσεις. Λύση γραμμικές παλινδρομήσειςμπορείτε να δείτε ΕΔΩ.

Αυτή η μέθοδοςεφαρμόζεται κυρίως σε οικονομική μοντελοποίησηκαι την πρόβλεψη. Σκοπός του είναι να παρατηρήσει και να εντοπίσει τη σχέση μεταξύ δύο δεικτών.

Κύριοι τύποι μη γραμμικές παλινδρομήσειςείναι:

• πολυώνυμο (τετραγωνικό, κυβικό).
• υπερβολικός;
• εξουσία;
• επίδειξη;
• λογαριθμική.

Μπορούν επίσης να χρησιμοποιηθούν διάφοροι συνδυασμοί. Για παράδειγμα, για ανάλυση χρονοσειρών σε τραπεζικές, ασφαλιστικές, δημογραφικές μελέτες, χρησιμοποιείται η καμπύλη Gompzer, η οποία είναι ένας τύπος λογαριθμικής παλινδρόμησης.

Στην πρόβλεψη με χρήση μη γραμμικών παλινδρομήσεων, το κύριο πράγμα είναι να βρούμε τον συντελεστή συσχέτισης, ο οποίος θα μας δείξει αν υπάρχει ΣΤΕΝΗ ΣΧΕΣΗμέλι με δύο παραμέτρους ή όχι. Κατά κανόνα, εάν ο συντελεστής συσχέτισης είναι κοντά στο 1, τότε υπάρχει σύνδεση και η πρόβλεψη θα είναι αρκετά ακριβής. ένα ακόμα σημαντικό στοιχείοΗ μη γραμμική παλινδρόμηση είναι ο μέσος όρος σχετικό σφάλμα (ΑΛΛΑ ) αν είναι στο διάστημα<8…10%, значит модель достаточно точна.

Σε αυτό, ίσως, θα ολοκληρώσουμε το θεωρητικό μπλοκ και θα προχωρήσουμε σε πρακτικούς υπολογισμούς.

Έχουμε έναν πίνακα πωλήσεων αυτοκινήτων για μια περίοδο 15 ετών (ας τον χαρακτηρίσουμε ως Χ), ο αριθμός των βημάτων μέτρησης θα είναι το όρισμα n, έχουμε επίσης έσοδα για αυτές τις περιόδους (ας το συμβολίσουμε με Y), πρέπει να προβλέψουμε ποια θα είναι τα έσοδα στο μέλλον. Ας φτιάξουμε τον παρακάτω πίνακα:

Για τη μελέτη, πρέπει να λύσουμε την εξίσωση (την εξάρτηση του Υ από το Χ): y=ax 2 +bx+c+e. Αυτή είναι μια τετραγωνική παλινδρόμηση ζεύγους. Σε αυτήν την περίπτωση, εφαρμόζουμε τη μέθοδο των ελαχίστων τετραγώνων για να βρούμε τα άγνωστα ορίσματα - a, b, c. Θα οδηγήσει σε ένα σύστημα αλγεβρικών εξισώσεων της μορφής:

Για να λύσουμε αυτό το σύστημα, χρησιμοποιούμε, για παράδειγμα, τη μέθοδο Cramer. Βλέπουμε ότι τα αθροίσματα που περιλαμβάνονται στο σύστημα είναι οι συντελεστές των αγνώστων. Για να τις υπολογίσουμε, προσθέτουμε πολλές στήλες στον πίνακα (D, E, F, G, H) και τις υπογράφουμε σύμφωνα με το νόημα των υπολογισμών - στη στήλη D τετραγωνίζουμε το x, στο E σε κύβο, στο F στο 4η δύναμη, στο G πολλαπλασιάζουμε τους δείκτες x και y, στο H τετραγωνίζουμε το x και πολλαπλασιάζουμε με το y.

Θα εμφανιστεί ένας πίνακας της φόρμας γεμάτος με αυτά που είναι απαραίτητα για την επίλυση της εξίσωσης.

Ας σχηματίσουμε μια μήτρα ΕΝΑ ένα σύστημα που αποτελείται από συντελεστές για αγνώστους στην αριστερή πλευρά των εξισώσεων. Ας το βάλουμε στο κελί A22 και ας το ονομάσουμε " Α=". Ακολουθούμε το σύστημα εξισώσεων που επιλέξαμε για να λύσουμε την παλινδρόμηση.

Δηλαδή, στο κελί Β21 πρέπει να τοποθετήσουμε το άθροισμα της στήλης όπου ανεβάσαμε τον δείκτη Χ στην τέταρτη δύναμη - F17. Ας αναφερθούμε απλώς στο κελί - "=F17". Στη συνέχεια, χρειαζόμαστε το άθροισμα της στήλης όπου το X ήταν κυβισμένο - E17, τότε πηγαίνουμε αυστηρά σύμφωνα με το σύστημα. Έτσι, θα χρειαστεί να συμπληρώσουμε ολόκληρο τον πίνακα.

Σύμφωνα με τον αλγόριθμο του Cramer, θα συλλέξουμε έναν πίνακα A1, παρόμοιο με τον A, στον οποίο αντί για τα στοιχεία της πρώτης στήλης θα πρέπει να τοποθετηθούν τα στοιχεία των δεξιών τμημάτων των εξισώσεων του συστήματος. Δηλαδή, το άθροισμα της στήλης Χ στο τετράγωνο του Υ, το άθροισμα της στήλης ΧΥ και το άθροισμα της στήλης Υ.

Θα χρειαστούμε επίσης δύο ακόμη πίνακες - ας τους ονομάσουμε Α2 και Α3 στους οποίους η δεύτερη και η τρίτη στήλη θα αποτελούνται από τους συντελεστές της δεξιάς πλευράς των εξισώσεων. Η εικόνα θα είναι έτσι.

Ακολουθώντας τον επιλεγμένο αλγόριθμο, θα χρειαστεί να υπολογίσουμε τις τιμές των καθοριστικών παραγόντων (ορίζοντες, D) των ληφθέντων πινάκων. Ας χρησιμοποιήσουμε τον τύπο MOPRED. Τα αποτελέσματα θα τοποθετηθούν στα κελιά J21:K24.

Θα υπολογίσουμε τους συντελεστές της εξίσωσης σύμφωνα με τον Cramer στα κελιά απέναντι από τις αντίστοιχες ορίζουσες σύμφωνα με τον τύπο: ένα(στο κελί M22) - "=K22/K21"; σι(στο κελί M23) — "=K23/K21"; Με(στο κελί M24) - "=K24 / K21".

Παίρνουμε την επιθυμητή εξίσωση τετραγωνικής παλινδρόμησης ζεύγους:

y=-0,074x2 +2,151x+6,523

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

Για να υπολογίσετε, προσθέστε μια επιπλέον στήλη J στον πίνακα (ας την ονομάσουμε y*). Ο υπολογισμός θα γίνει ως εξής (σύμφωνα με την εξίσωση παλινδρόμησης που λάβαμε) - "=$m$22*B2*B2+$M$23*B2+$M$24".Ας το βάλουμε στο κελί J2. Το μόνο που απομένει είναι να σύρετε τον δείκτη αυτόματης συμπλήρωσης προς τα κάτω στο κελί J16.

Για να υπολογίσετε τα αθροίσματα (μέσος όρος Υ-Υ) 2, προσθέστε τις στήλες K και L στον πίνακα με τους αντίστοιχους τύπους. Υπολογίζουμε τον μέσο όρο για τη στήλη Y χρησιμοποιώντας τη συνάρτηση AVERAGE.

Στο κελί K25 τοποθετούμε τον τύπο για τον υπολογισμό του δείκτη συσχέτισης - "=ROOT(1-(K17/L17))".

Βλέπουμε ότι η τιμή του 0,959 είναι πολύ κοντά στο 1, πράγμα που σημαίνει ότι υπάρχει μια στενή μη γραμμική σχέση μεταξύ των πωλήσεων και των ετών.

Απομένει να αξιολογηθεί η ποιότητα της προσαρμογής της εξίσωσης τετραγωνικής παλινδρόμησης (δείκτης προσδιορισμού). Υπολογίζεται από τον τύπο του τετραγώνου του δείκτη συσχέτισης. Δηλαδή, ο τύπος στο κελί K26 θα είναι πολύ απλός - "=K25*K25".

Ο συντελεστής 0,920 είναι κοντά στο 1, γεγονός που υποδηλώνει υψηλής ποιότητας εφαρμογή.

Το τελευταίο βήμα είναι ο υπολογισμός του σχετικού σφάλματος. Ας προσθέσουμε μια στήλη και εισάγουμε τον τύπο εκεί: “=ABS((C2-J2)/C2), ABS — ενότητα, απόλυτη τιμή. Ας σύρουμε τον δείκτη προς τα κάτω και στο κελί M18 θα εμφανίσουμε τη μέση τιμή (ΜΕΣΟΣ), θα εκχωρήσουμε τη μορφή ποσοστού στα κελιά. Το αποτέλεσμα που λήφθηκε - 7,79% είναι εντός των αποδεκτών τιμών σφάλματος<8…10%. Значит вычисления достаточно точны.

Εάν είναι απαραίτητο, μπορούμε να δημιουργήσουμε ένα γράφημα με βάση τις τιμές που λαμβάνονται.

Επισυνάπτεται ένα παράδειγμα αρχείου - ΣΥΝΔΕΣΜΟΣ!

Κατηγορίες:// με ημερομηνία 28 Οκτωβρίου 2017

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

Σύνδεση του πακέτου ανάλυσης

Όμως, για να χρησιμοποιήσετε τη συνάρτηση που σας επιτρέπει να πραγματοποιήσετε ανάλυση παλινδρόμησης, πρώτα απ 'όλα, πρέπει να ενεργοποιήσετε το Πακέτο Ανάλυσης. Μόνο τότε τα απαραίτητα εργαλεία για αυτήν τη διαδικασία θα εμφανιστούν στην κορδέλα του Excel.

1. Μεταβείτε στην καρτέλα "Αρχείο".
2. Μεταβείτε στην ενότητα "Ρυθμίσεις".
3. Ανοίγει το παράθυρο Επιλογές Excel. Μεταβείτε στην υποενότητα "Πρόσθετα".
4. Στο κάτω μέρος του παραθύρου που ανοίγει, αναδιατάσσουμε το διακόπτη στο μπλοκ "Διαχείριση" στη θέση "Πρόσθετα Excel", εάν βρίσκεται σε διαφορετική θέση. Κάντε κλικ στο κουμπί "Μετάβαση".
5. Ανοίγει το παράθυρο πρόσθετων του Excel. Επιλέξτε το πλαίσιο δίπλα στο "Πακέτο ανάλυσης". Κάντε κλικ στο κουμπί "OK".

Τώρα, όταν πάμε στην καρτέλα "Δεδομένα", στην κορδέλα στο μπλοκ εργαλείων "Ανάλυση", θα δούμε ένα νέο κουμπί - "Ανάλυση δεδομένων".

Τύποι ανάλυσης παλινδρόμησης

Υπάρχουν διάφοροι τύποι παλινδρόμησης:

• παραβολικός;
• εξουσία;
• λογαριθμική?
• εκθετικός;
• επίδειξη;
• υπερβολικός;
• γραμμικής παλινδρόμησης.

Θα μιλήσουμε λεπτομερέστερα για την υλοποίηση του τελευταίου τύπου ανάλυσης παλινδρόμησης στο Excel αργότερα.

Γραμμική παλινδρόμηση στο Excel

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

Η γενική εξίσωση γραμμικής παλινδρόμησης μοιάζει με αυτό: Y = a0 + a1x1 + ... + axk. Σε αυτόν τον τύπο, το Y σημαίνει τη μεταβλητή στην οποία προσπαθούμε να μελετήσουμε την επίδραση των παραγόντων. Στην περίπτωσή μας, αυτός είναι ο αριθμός των αγοραστών. Η τιμή του x είναι οι διάφοροι παράγοντες που επηρεάζουν τη μεταβλητή. Οι παράμετροι a είναι οι συντελεστές παλινδρόμησης. Δηλαδή, καθορίζουν τη σημασία ενός συγκεκριμένου παράγοντα. Ο δείκτης k υποδηλώνει τον συνολικό αριθμό αυτών των ίδιων παραγόντων.

Ανάλυση αποτελεσμάτων ανάλυσης

Τα αποτελέσματα της ανάλυσης παλινδρόμησης εμφανίζονται με τη μορφή πίνακα στη θέση που καθορίζεται στις ρυθμίσεις.

Ένας από τους κύριους δείκτες είναι το R-square. Δείχνει την ποιότητα του μοντέλου. Στην περίπτωσή μας, αυτός ο συντελεστής είναι 0,705 ή περίπου 70,5%. Αυτό είναι ένα αποδεκτό επίπεδο ποιότητας. Μια σχέση μικρότερη από 0,5 είναι κακή.

Ένας άλλος σημαντικός δείκτης βρίσκεται στο κελί στη διασταύρωση της γραμμής "Y-τομή" και της στήλης "Συντελεστές". Εδώ υποδεικνύεται ποια τιμή θα έχει το Υ και στην περίπτωσή μας αυτός είναι ο αριθμός των αγοραστών, με όλους τους άλλους παράγοντες ίσους με μηδέν. Σε αυτόν τον πίνακα, αυτή η τιμή είναι 58,04.

Η τιμή στη διασταύρωση της στήλης "Μεταβλητή X1" και "Συντελεστές" δείχνει το επίπεδο εξάρτησης του Υ από το Χ. Στην περίπτωσή μας, αυτό είναι το επίπεδο εξάρτησης του αριθμού των πελατών του καταστήματος από τη θερμοκρασία. Ο συντελεστής 1,31 θεωρείται αρκετά υψηλός δείκτης επιρροής.

Όπως μπορείτε να δείτε, είναι αρκετά εύκολο να δημιουργήσετε έναν πίνακα ανάλυσης παλινδρόμησης χρησιμοποιώντας το Microsoft Excel. Όμως, μόνο ένα εκπαιδευμένο άτομο μπορεί να εργαστεί με τα δεδομένα που λαμβάνονται στην έξοδο και να κατανοήσει την ουσία τους.

Χαιρόμαστε που μπορέσαμε να σας βοηθήσουμε να επιλύσετε το πρόβλημα.

Κάντε την ερώτησή σας στα σχόλια, περιγράφοντας λεπτομερώς την ουσία του προβλήματος. Οι ειδικοί μας θα προσπαθήσουν να απαντήσουν το συντομότερο δυνατό.

Σας βοήθησε αυτό το άρθρο;

Η μέθοδος γραμμικής παλινδρόμησης μας επιτρέπει να περιγράψουμε μια ευθεία που ταιριάζει καλύτερα σε μια σειρά από διατεταγμένα ζεύγη (x, y). Η εξίσωση για μια ευθεία γραμμή, γνωστή ως γραμμική εξίσωση, δίνεται παρακάτω:

ŷ είναι η αναμενόμενη τιμή του y για μια δεδομένη τιμή του x,

x - ανεξάρτητη μεταβλητή,

α - τμήμα στον άξονα y για ευθεία γραμμή,

b είναι η κλίση της ευθείας.

Στο παρακάτω σχήμα, αυτή η έννοια απεικονίζεται γραφικά:

Το παραπάνω σχήμα δείχνει μια γραμμή που περιγράφεται από την εξίσωση ŷ =2+0,5x. Το τμήμα στον άξονα y είναι το σημείο τομής της ευθείας με τον άξονα y. στην περίπτωσή μας, a = 2. Η κλίση της γραμμής, b, ο λόγος της ανόδου γραμμής προς το μήκος γραμμής, έχει τιμή 0,5. Μια θετική κλίση σημαίνει ότι η γραμμή ανεβαίνει από αριστερά προς τα δεξιά. Εάν b = 0, η γραμμή είναι οριζόντια, που σημαίνει ότι δεν υπάρχει σχέση μεταξύ της εξαρτημένης και της ανεξάρτητης μεταβλητής. Με άλλα λόγια, η αλλαγή της τιμής του x δεν επηρεάζει την τιμή του y.

Τα ŷ και y συχνά συγχέονται. Το γράφημα δείχνει 6 διατεταγμένα ζεύγη σημείων και μια ευθεία, σύμφωνα με τη δεδομένη εξίσωση

Αυτό το σχήμα δείχνει το σημείο που αντιστοιχεί στο διατεταγμένο ζεύγος x = 2 και y = 4. Σημειώστε ότι η αναμενόμενη τιμή του y σύμφωνα με τη γραμμή στο Χ= 2 είναι ŷ. Μπορούμε να το επιβεβαιώσουμε με την ακόλουθη εξίσωση:

ŷ = 2 + 0,5х =2 +0,5(2) =3.

Η τιμή y είναι το πραγματικό σημείο και η τιμή ŷ είναι η αναμενόμενη τιμή y χρησιμοποιώντας μια γραμμική εξίσωση για μια δεδομένη τιμή x.

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

Χρήση του Excel για τον ορισμό της γραμμικής παλινδρόμησης

Για να χρησιμοποιήσετε το εργαλείο ανάλυσης παλινδρόμησης που είναι ενσωματωμένο στο Excel, πρέπει να ενεργοποιήσετε το πρόσθετο Πακέτο ανάλυσης. Μπορείτε να το βρείτε κάνοντας κλικ στην καρτέλα Αρχείο –> Επιλογές(2007+), στο παράθυρο διαλόγου που εμφανίζεται Επιλογέςπροέχωμεταβείτε στην καρτέλα Πρόσθετα.Στο χωράφι Ελεγχοςεπιλέγω πρόσθεταπροέχωκαι κάντε κλικ Πηγαίνω.Στο παράθυρο που εμφανίζεται, επιλέξτε το πλαίσιο δίπλα πακέτο ανάλυσης,Κάντε κλικ ΕΝΤΑΞΕΙ.

Στην καρτέλα Δεδομένασε μια ομάδα Ανάλυσηθα εμφανιστεί ένα νέο κουμπί Ανάλυση δεδομένων.

Για να δείξουμε πώς λειτουργεί το πρόσθετο, ας χρησιμοποιήσουμε τα δεδομένα από το προηγούμενο άρθρο, όπου ένας άντρας και ένα κορίτσι μοιράζονται ένα τραπέζι στο μπάνιο. Εισαγάγετε τα δεδομένα για το παράδειγμα του μπάνιου μας στις στήλες Α και Β ενός κενού φύλλου.

Μεταβείτε στην καρτέλα Δεδομένα,σε μια ομάδα ΑνάλυσηΚάντε κλικ Ανάλυση δεδομένων.Στο παράθυρο που εμφανίζεται Ανάλυση δεδομένωνεπιλέγω Οπισθοδρόμησηόπως φαίνεται στην εικόνα και κάντε κλικ στο OK.

Ορίστε τις απαιτούμενες παραμέτρους παλινδρόμησης στο παράθυρο Οπισθοδρόμηση, όπως φαίνεται στην εικόνα:

Κάντε κλικ ΕΝΤΑΞΕΙ.Το παρακάτω σχήμα δείχνει τα αποτελέσματα που προέκυψαν:

Αυτά τα αποτελέσματα είναι συνεπή με αυτά που λάβαμε από ανεξάρτητους υπολογισμούς στο προηγούμενο άρθρο.

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

Τύποι παλινδρόμησης

Η ίδια η έννοια εισήχθη στα μαθηματικά από τον Francis Galton το 1886. Η παλινδρόμηση συμβαίνει:

• γραμμικός;
• παραβολικός;
• εξουσία;
• εκθετικός;
• υπερβολικός;
• εκδηλωτικός;
• λογαριθμική.

Παράδειγμα 1

Εξετάστε το πρόβλημα του προσδιορισμού της εξάρτησης του αριθμού των συνταξιούχων μελών της ομάδας από τον μέσο μισθό σε 6 βιομηχανικές επιχειρήσεις.

Μια εργασία. Σε έξι επιχειρήσεις, αναλύσαμε τον μέσο μηνιαίο μισθό και τον αριθμό των εργαζομένων που αποχώρησαν με τη θέλησή τους. Σε μορφή πίνακα έχουμε:

Για το πρόβλημα του προσδιορισμού της εξάρτησης του αριθμού των απολυμένων εργαζομένων από τον μέσο μισθό σε 6 επιχειρήσεις, το μοντέλο παλινδρόμησης έχει τη μορφή της εξίσωσης Y = a0 + a1 × 1 + ... + akxk, όπου χi είναι οι μεταβλητές που επηρεάζουν, ai είναι οι συντελεστές παλινδρόμησης και k είναι ο αριθμός των παραγόντων.

Για αυτό το έργο, το Υ είναι ο δείκτης των υπαλλήλων που αποχώρησαν και ο παράγοντας που επηρεάζει είναι ο μισθός, τον οποίο συμβολίζουμε με Χ.

Χρήση των δυνατοτήτων του υπολογιστικού φύλλου "Excel"

Η ανάλυση παλινδρόμησης στο Excel πρέπει να προηγείται από την εφαρμογή ενσωματωμένων συναρτήσεων στα διαθέσιμα δεδομένα πίνακα. Ωστόσο, για αυτούς τους σκοπούς, είναι καλύτερο να χρησιμοποιήσετε το πολύ χρήσιμο πρόσθετο "Analysis Toolkit". Για να το ενεργοποιήσετε χρειάζεστε:

• από την καρτέλα "Αρχείο", μεταβείτε στην ενότητα "Επιλογές".
• στο παράθυρο που ανοίγει, επιλέξτε τη γραμμή "Πρόσθετα".
• κάντε κλικ στο κουμπί "Μετάβαση" που βρίσκεται στο κάτω μέρος, στα δεξιά της γραμμής "Διαχείριση".
• επιλέξτε το πλαίσιο δίπλα στο όνομα "Πακέτο ανάλυσης" και επιβεβαιώστε τις ενέργειές σας κάνοντας κλικ στο "OK".

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

Γραμμική παλινδρόμηση στο Excel

Τώρα που έχουμε στη διάθεσή μας όλα τα απαραίτητα εικονικά εργαλεία για την εκτέλεση οικονομετρικών υπολογισμών, μπορούμε να αρχίσουμε να λύνουμε το πρόβλημά μας. Για αυτό:

• κάντε κλικ στο κουμπί "Ανάλυση δεδομένων".
• στο παράθυρο που ανοίγει, κάντε κλικ στο κουμπί "Ανάδρομη".
• στην καρτέλα που εμφανίζεται, εισαγάγετε το εύρος τιμών για το Y (ο αριθμός των εργαζομένων που παραιτήθηκαν) και για το X (τους μισθούς τους).
• Επιβεβαιώνουμε τις ενέργειές μας πατώντας το κουμπί "Ok".

Ως αποτέλεσμα, το πρόγραμμα θα συμπληρώσει αυτόματα ένα νέο φύλλο του υπολογιστικού φύλλου με δεδομένα ανάλυσης παλινδρόμησης. Σημείωση! Το Excel έχει τη δυνατότητα να ορίσει με μη αυτόματο τρόπο την τοποθεσία που προτιμάτε για αυτόν τον σκοπό. Για παράδειγμα, θα μπορούσε να είναι το ίδιο φύλλο όπου βρίσκονται οι τιμές Y και X ή ακόμα και ένα νέο βιβλίο εργασίας ειδικά σχεδιασμένο για την αποθήκευση τέτοιων δεδομένων.

Ανάλυση αποτελεσμάτων παλινδρόμησης για R-square

Στο Excel, τα δεδομένα που λαμβάνονται κατά την επεξεργασία των δεδομένων του υπό εξέταση παραδείγματος μοιάζουν με αυτό:

Πρώτα απ 'όλα, θα πρέπει να δώσετε προσοχή στην τιμή του τετραγώνου R. Είναι ο συντελεστής προσδιορισμού. Σε αυτό το παράδειγμα, R-square = 0,755 (75,5%), δηλαδή οι υπολογισμένες παράμετροι του μοντέλου εξηγούν τη σχέση μεταξύ των εξεταζόμενων παραμέτρων κατά 75,5%. Όσο μεγαλύτερη είναι η τιμή του συντελεστή προσδιορισμού, τόσο πιο εφαρμόσιμο είναι το επιλεγμένο μοντέλο για μια συγκεκριμένη εργασία. Πιστεύεται ότι περιγράφει σωστά την πραγματική κατάσταση με τιμή R-τετράγωνο πάνω από 0,8. Εάν το τετράγωνο R είναι tcr, τότε απορρίπτεται η υπόθεση της ασημαντότητας του ελεύθερου όρου της γραμμικής εξίσωσης.

Στο εξεταζόμενο πρόβλημα για το ελεύθερο μέλος, χρησιμοποιώντας τα εργαλεία του Excel, προέκυψε ότι t = 169.20903 και p = 2.89E-12, δηλ. έχουμε μηδενική πιθανότητα ότι η σωστή υπόθεση σχετικά με την ασημαντότητα του ελεύθερου μέλους θα είναι απορρίφθηκε. Για τον συντελεστή σε άγνωστο t=5,79405, και p=0,001158. Με άλλα λόγια, η πιθανότητα να απορριφθεί η σωστή υπόθεση για τη μη σημασία του συντελεστή για το άγνωστο είναι 0,12%.

Έτσι, μπορεί να υποστηριχθεί ότι η προκύπτουσα εξίσωση γραμμικής παλινδρόμησης είναι επαρκής.

Το πρόβλημα της σκοπιμότητας αγοράς ενός πακέτου μετοχών

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

Η διοίκηση της NNN πρέπει να αποφασίσει για τη σκοπιμότητα αγοράς του 20% της ΜΜΜ Α.Ε. Το κόστος του πακέτου (JV) είναι 70 εκατομμύρια δολάρια ΗΠΑ. Οι ειδικοί του NNN συνέλεξαν δεδομένα για παρόμοιες συναλλαγές. Αποφασίστηκε να αξιολογηθεί η αξία του πακέτου μετοχών σύμφωνα με τέτοιες παραμέτρους, εκφρασμένες σε εκατομμύρια δολάρια ΗΠΑ, όπως:

• πληρωτέοι λογαριασμοί (VK)·
• ετήσιος κύκλος εργασιών (VO)·
• εισπρακτέοι λογαριασμοί (VD)·
• κόστος παγίων στοιχείων ενεργητικού (SOF).

Επιπλέον, χρησιμοποιείται η παράμετρος καθυστερήσεις μισθοδοσίας της επιχείρησης (V3 P) σε χιλιάδες δολάρια ΗΠΑ.

Λύση με χρήση υπολογιστικού φύλλου Excel

Πρώτα απ 'όλα, πρέπει να δημιουργήσετε έναν πίνακα αρχικών δεδομένων. Μοιάζει με αυτό:

• καλέστε το παράθυρο "Ανάλυση δεδομένων".
• επιλέξτε την ενότητα "Ανάδρομη".
• στο πλαίσιο "Διάστημα εισαγωγής Y" εισαγάγετε το εύρος τιμών των εξαρτημένων μεταβλητών από τη στήλη G.
• κάντε κλικ στο εικονίδιο με ένα κόκκινο βέλος στα δεξιά του παραθύρου "Input interval X" και επιλέξτε το εύρος όλων των τιμών​​από τις στήλες B, C, D, F στο φύλλο.

Επιλέξτε "Νέο φύλλο εργασίας" και κάντε κλικ στο "Ok".

Λάβετε την ανάλυση παλινδρόμησης για το δεδομένο πρόβλημα.

Εξέταση των αποτελεσμάτων και συμπερασμάτων

"Συλλέγουμε" από τα στρογγυλεμένα δεδομένα που παρουσιάζονται παραπάνω στο φύλλο υπολογιστικού φύλλου του Excel, την εξίσωση παλινδρόμησης:

SP \u003d 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

Σε μια πιο οικεία μαθηματική μορφή, μπορεί να γραφτεί ως:

y = 0,103*x1 + 0,541*x2 - 0,031*x3 +0,405*x4 +0,691*x5 - 265,844

Τα δεδομένα για την JSC "MMM" παρουσιάζονται στον πίνακα:

Αντικαθιστώντας τα στην εξίσωση παλινδρόμησης, παίρνουν ένα νούμερο 64,72 εκατομμυρίων δολαρίων ΗΠΑ. Αυτό σημαίνει ότι οι μετοχές της JSC MMM δεν πρέπει να αγοραστούν, καθώς η αξία τους στα 70 εκατομμύρια δολάρια είναι μάλλον υπερεκτιμημένη.

Όπως μπορείτε να δείτε, η χρήση του υπολογιστικού φύλλου Excel και της εξίσωσης παλινδρόμησης κατέστησαν δυνατή τη λήψη μιας τεκμηριωμένης απόφασης σχετικά με τη σκοπιμότητα μιας πολύ συγκεκριμένης συναλλαγής.

Τώρα ξέρετε τι είναι η παλινδρόμηση. Τα παραδείγματα στο Excel που συζητήθηκαν παραπάνω θα σας βοηθήσουν να λύσετε πρακτικά προβλήματα από τον τομέα της οικονομετρίας.

Το πακέτο MS Excel σάς επιτρέπει να κάνετε το μεγαλύτερο μέρος της εργασίας πολύ γρήγορα όταν κατασκευάζετε μια εξίσωση γραμμικής παλινδρόμησης. Είναι σημαντικό να κατανοήσουμε πώς να ερμηνεύσουμε τα αποτελέσματα. Για να δημιουργήσετε ένα μοντέλο παλινδρόμησης, επιλέξτε Εργαλεία\Ανάλυση Δεδομένων\Περιστροφή (στο Excel 2007, αυτή η λειτουργία βρίσκεται στην ενότητα Δεδομένα/Ανάλυση δεδομένων/Ανάλυση). Στη συνέχεια, αντιγράψτε τα ληφθέντα αποτελέσματα σε ένα μπλοκ για ανάλυση.

Αρχικά δεδομένα:

Αποτελέσματα ανάλυσης

Συμπεριλάβετε στην αναφορά
Υπολογισμός παραμέτρων της εξίσωσης παλινδρόμησης
Θεωρητικό υλικό
Εξίσωση παλινδρόμησης σε τυπική κλίμακα
Συντελεστής πολλαπλής συσχέτισης (Δείκτης πολλαπλής συσχέτισης)
Μερικοί συντελεστές ελαστικότητας
Συγκριτική αξιολόγηση της επίδρασης των αναλυόμενων παραγόντων στο αποτελεσματικό χαρακτηριστικό (d - συντελεστές χωριστού προσδιορισμού)

Έλεγχος της ποιότητας της κατασκευασμένης εξίσωσης παλινδρόμησης
Σημασία των συντελεστών παλινδρόμησης b i (t-statistics. Student's t-test)
Η σημασία της εξίσωσης στο σύνολό της (F-statistics. κριτήριο Fisher). Συντελεστής προσδιορισμού
Μερικά κριτήρια F

Επίπεδο σημασίας 0.005 0.01 0.025 0.05 0.1 0.25 0.4

Δείχνει την επίδραση ορισμένων τιμών (ανεξάρτητων, ανεξάρτητων) στην εξαρτημένη μεταβλητή. Για παράδειγμα, πώς ο αριθμός του οικονομικά ενεργού πληθυσμού εξαρτάται από τον αριθμό των επιχειρήσεων, τους μισθούς και άλλες παραμέτρους. Ή: πώς οι ξένες επενδύσεις, οι τιμές της ενέργειας κ.λπ. επηρεάζουν το επίπεδο του ΑΕΠ.

Το αποτέλεσμα της ανάλυσης σας επιτρέπει να θέσετε προτεραιότητες. Και με βάση τους κύριους παράγοντες, να προβλέψει, να σχεδιάσει την ανάπτυξη των τομέων προτεραιότητας, να λάβει αποφάσεις διαχείρισης.

Η παλινδρόμηση συμβαίνει:

γραμμικό (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 \u003d a 0 + a 1 x 1 + ... + a k x k.

Όπου a είναι οι συντελεστές παλινδρόμησης, x οι μεταβλητές που επηρεάζουν και k είναι ο αριθμός των παραγόντων.

Στο παράδειγμά μας, το Y είναι ο δείκτης των εργαζομένων που εγκατέλειψαν. Ο παράγοντας που επηρεάζει είναι οι μισθοί (x).

Το Excel διαθέτει ενσωματωμένες συναρτήσεις που μπορούν να χρησιμοποιηθούν για τον υπολογισμό των παραμέτρων ενός μοντέλου γραμμικής παλινδρόμησης. Αλλά το πρόσθετο Analysis ToolPak θα το κάνει πιο γρήγορα.

Ενεργοποιήστε ένα ισχυρό αναλυτικό εργαλείο:

1. Κάντε κλικ στο κουμπί "Office" και μεταβείτε στην καρτέλα "Επιλογές Excel". "Πρόσθετα".

2. Παρακάτω, κάτω από την αναπτυσσόμενη λίστα, στο πεδίο "Διαχείριση" θα υπάρχει η επιγραφή "Πρόσθετα Excel" (αν δεν υπάρχει, κάντε κλικ στο πλαίσιο ελέγχου στα δεξιά και επιλέξτε). Και ένα κουμπί Go. Κάντε κλικ.

3. Ανοίγει μια λίστα με τα διαθέσιμα πρόσθετα. Επιλέξτε "Πακέτο ανάλυσης" και κάντε κλικ στο OK.

Μόλις ενεργοποιηθεί, το πρόσθετο θα είναι διαθέσιμο στην καρτέλα Δεδομένα.

Τώρα θα ασχοληθούμε άμεσα με την ανάλυση παλινδρόμησης.

1. Ανοίξτε το μενού του εργαλείου Ανάλυση δεδομένων. Επιλέξτε "Προσβολή".

2. Θα ανοίξει ένα μενού για την επιλογή τιμών εισόδου και επιλογών εξόδου (πού θα εμφανιστεί το αποτέλεσμα). Στα πεδία για τα αρχικά δεδομένα, υποδεικνύουμε το εύρος της περιγραφόμενης παραμέτρου (Y) και τον παράγοντα που την επηρεάζει (X). Τα υπόλοιπα μπορεί να ολοκληρωθούν ή όχι.

3. Αφού κάνετε κλικ στο OK, το πρόγραμμα θα εμφανίσει τους υπολογισμούς σε ένα νέο φύλλο (μπορείτε να επιλέξετε το διάστημα που θα εμφανιστεί στο τρέχον φύλλο ή να αντιστοιχίσετε την έξοδο σε ένα νέο βιβλίο εργασίας).

Πρώτα απ 'όλα, δίνουμε προσοχή στο R-τετράγωνο και τους συντελεστές.

Το R-τετράγωνο είναι ο συντελεστής προσδιορισμού. Στο παράδειγμά μας, είναι 0,755, ή 75,5%. Αυτό σημαίνει ότι οι υπολογισμένες παράμετροι του μοντέλου εξηγούν τη σχέση μεταξύ των παραμέτρων που μελετήθηκαν κατά 75,5%. Όσο μεγαλύτερος είναι ο συντελεστής προσδιορισμού, τόσο καλύτερο είναι το μοντέλο. Καλό - πάνω από 0,8. Κακή - λιγότερο από 0,5 (μια τέτοια ανάλυση δύσκολα μπορεί να θεωρηθεί λογική). Στο παράδειγμά μας - "όχι κακό".

Ο συντελεστής 64,1428 δείχνει τι θα είναι το Y εάν όλες οι μεταβλητές στο υπό εξέταση μοντέλο είναι ίσες με 0. Δηλαδή, άλλοι παράγοντες που δεν περιγράφονται στο μοντέλο επηρεάζουν επίσης την τιμή της παραμέτρου που αναλύθηκε.

Ο συντελεστής -0,16285 δείχνει το βάρος της μεταβλητής X στο Y. Δηλαδή, ο μέσος μηνιαίος μισθός σε αυτό το μοντέλο επηρεάζει τον αριθμό των παραιτητών με βάρος -0,16285 (αυτός είναι ένας μικρός βαθμός επιρροής). Το σύμβολο "-" υποδηλώνει αρνητικό αντίκτυπο: όσο υψηλότερος είναι ο μισθός, τόσο λιγότερη παραίτηση. Που είναι δίκαιο.