Παλινδρόμηση στο Excel: παραδείγματα εξίσωση. γραμμική παλινδρόμηση

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

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

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

• γραμμική?
• παραβολική?
• εξουσία?
• εκθετική?
• υπερβολική?
• εκθετική?
• λογαριθμική.

ΠΑΡΑΔΕΙΓΜΑ 1

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

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

	Ένα	Β	Γ
1	X	Αριθμός παραιτήσεις	μισθός
2		y	30000 ρούβλια
3	1	60	35000 ρούβλια
4	2	35	40000 ρούβλια
5	3	20	45000 ρούβλια
6	4	20	50.000 ρούβλια
7	5	15	55000 ρούβλια
8	6	15	60000 ρούβλια

Για το πρόβλημα του προσδιορισμού της εξάρτησης των εργατών ποσού διαχωρισμούς από το μέσο όρο μισθού για το μοντέλο παλινδρόμησης 6 επιχειρήσεις έχει τη μορφή της εξίσωσης Y = a ₀ + a ₁ x ₁ + ... + a _k χ _k, όπου x _i - μεγέθη επιρροής, a _i - συντελεστές παλινδρόμησης, ak - αριθμό παραγόντων.

Υ για μια δεδομένη εργασία - είναι ένας δείκτης στη φωτιά έναν υπάλληλο, ένας παράγοντας που συμβάλλει - ο μισθός, το οποίο υποδηλώνεται από X.

Η εκμετάλλευση της δύναμης του λογιστικού φύλλου «Excel»

θα πρέπει να προηγηθεί μια αίτηση με τα υπάρχοντα δεδομένα του πίνακα ενσωματωμένες λειτουργίες ανάλυσης παλινδρόμησης στο Excel. Ωστόσο, για τους σκοπούς αυτούς, είναι καλύτερο να χρησιμοποιήσετε ένα πολύ χρήσιμο add-in «ανάλυσης πακέτων». Για να την ενεργοποιήσετε, θα πρέπει να:

• με την καρτέλα «Αρχείο» πηγαίνετε στο «Ρυθμίσεις»?
• στο παράθυρο που ανοίγει, επιλέξτε «Πρόσθετα»?
• κάντε κλικ στο κουμπί «Go», που βρίσκεται στην κάτω δεξιά γωνία της γραμμής «διαχείριση»?
• βάλτε ένα σημάδι ελέγχου δίπλα στο «πακέτο εργαλείων ανάλυσης» και επιβεβαιώστε την ενέργειά σας πατώντας «ΟΚ».

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

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

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

• κουμπί κάνετε κλικ στο «Ανάλυση Δεδομένων»?
• κάντε κλικ στο κουμπί «οπισθοδρόμηση» στο ανοιχτό παράθυρο?
• μια καρτέλα που εμφανίζεται να εισαγάγει ένα εύρος τιμών Υ (ο αριθμός των διαχωρισμών εργαζομένων) και Χ (μισθού τους)?
• επιβεβαιώνουν τις ενέργειές τους, πατώντας το κουμπί «ΟΚ».

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

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

Τα δεδομένα του Excel που ελήφθησαν στα θεωρούνται παράδειγμα δεδομένων έχει τη μορφή:

Πρώτα απ 'όλα, θα πρέπει να δώσουμε προσοχή στην αξία του R-τετράγωνο. Αντιπροσωπεύει την συντελεστής προσδιορισμού. Σε αυτό το παράδειγμα, R-τετράγωνο = 0,755 (75,5%), m. Ε Οι υπολογισθείσες παράμετροι του μοντέλου για να εξηγήσει τη σχέση μεταξύ των παραμέτρων που θεωρούνται από 75,5%. Όσο υψηλότερη είναι η τιμή του συντελεστή προσδιορισμού, το επιλεγμένο μοντέλο θεωρείται ότι είναι πιο χρήσιμη για συγκεκριμένες εργασίες. Πιστεύεται να περιγράψει σωστά την πραγματική κατάσταση στο R-τετράγωνο τιμή πάνω από 0.8. Αν η R-τετράγωνο <0,5, τότε η ανάλυση παλινδρόμησης στο Excel δεν μπορεί να θεωρηθεί λογικό.

ανάλυση της αναλογίας

Αριθμός 64.1428 δείχνει ποια θα είναι η τιμή της Υ, αν όλες οι μεταβλητές xi στο μοντέλο μας θα μηδενιστεί. Με άλλα λόγια, μπορεί να υποστηριχθεί ότι η τιμή της παραμέτρου που αναλύθηκαν επηρεάζεται από άλλους παράγοντες από εκείνους που περιγράφονται στο συγκεκριμένο μοντέλο.

Ο επόμενος παράγοντας -0.16285 βρίσκεται στο κελί Β18, δείχνει η σημαντική επιρροή της μεταβλητής Χ με το Υ Αυτό σημαίνει ότι ο μέσος μισθός των εργαζομένων εντός του μοντέλου επηρεάζει τον αριθμό των παραιτήσεων από το βάρος του -0.16285, t. Ε Ο βαθμός της επίπτωσής του σε όλα μικρές. Το σύμβολο «-» σημαίνει ότι ο συντελεστής είναι αρνητικός. Είναι προφανές, αφού όλοι γνωρίζουμε ότι όσο περισσότερο μισθός στην επιχείρηση, τόσο λιγότερο οι άνθρωποι έχουν εκφράσει την επιθυμία να καταγγείλει τη σύμβαση εργασίας ή απορριφθεί.

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

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

y = f (x ₁ + x ₂ + ... χ _m) + ε, όπου y - είναι ένα χαρακτηριστικό γνώρισμα βαθμολογία (η εξαρτημένη μεταβλητή), και το χ _1, x _2, ... χ _m - είναι σημάδια παράγοντες (ανεξάρτητες μεταβλητές).

παράμετρος εκτίμησης

Για πολλαπλή παλινδρόμηση (MR) εκτελείται χρησιμοποιώντας μία μέθοδο των ελαχίστων τετραγώνων (LSM). Για γραμμικές εξισώσεις της μορφής Υ = α + b ₁ x _{1 + ... + b m χ m +} ε οικοδόμηση ενός συστήματος κανονικών εξισώσεων (cm. Παρακάτω)

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

Ως εκ τούτου, έχουμε:

όπου σ - είναι η διακύμανση του αντίστοιχου χαρακτηριστικό, αντανακλάται στο δείκτη.

MNC είναι εφαρμόσιμη στην εξίσωση MR να standartiziruemom κλίμακα. Σε αυτή την περίπτωση, θα έχουμε την εξίσωση:

όπου t _y, t _{x 1, ...} t _xm - standartiziruemye μεταβλητές για τις οποίες οι μέσες τιμές είναι 0? β _i - τυποποιημένα συντελεστές παλινδρόμησης και τυπική απόκλιση - 1.

Παρακαλώ σημειώστε ότι όλα β _i στην περίπτωση αυτή ορίζεται ως η κανονικοποιημένη και tsentraliziruemye, ως εκ τούτου, μια σύγκριση μεταξύ α θεωρείται έγκυρη και αποδεκτή. Επιπλέον, γίνεται αποδεκτό να πραγματοποιήσει διαλογή παραγόντων, απορρίπτοντας αυτές που έχουν τις χαμηλότερες τιμές του βi.

Το πρόβλημα με τη χρήση γραμμικής εξίσωσης παλινδρόμησης

Ας υποθέσουμε ότι έχετε έναν πίνακα της δυναμικής της τιμής ενός συγκεκριμένου προϊόντος N για τους τελευταίους 8 μήνες. Είναι απαραίτητο να αποφασίσει αν η απόκτηση του κόμματός του, στην τιμή των 1850 ρούβλια. / Τ

	Ένα	Β	Γ
1	το μήνα	το όνομα του μήνα	Τιμή Ν
2	1	Ιανουάριος	1750 ρούβλια ανά τόνο
3	2	Φεβρουάριος	1755 ρούβλια ανά τόνο
4	3	Μάρτιος	1767 ρούβλια ανά τόνο
5	4	Απρίλιος	1760 ρούβλια ανά τόνο
6	5	Μάιος	1770 ρούβλια ανά τόνο
7	6	Ιούνιος	1790 ρούβλια ανά τόνο
8	7	Ιούλιος	1810 ρούβλια ανά τόνο
9	8	Αύγουστος	1840 ρούβλια ανά τόνο

Για να λύσει αυτό το πρόβλημα στο πίνακα επεξεργαστή «Excel» υποχρεωμένοι να χρησιμοποιούν ήδη γνωστό, για παράδειγμα εργαλείο «Ανάλυση Δεδομένων» που παρουσιάστηκε παραπάνω. Στη συνέχεια, το τμήμα «παλινδρόμησης» και ρυθμίστε τις παραμέτρους επιλέξτε. Πρέπει να θυμόμαστε ότι το «εύρος εισόδου Υ» θα πρέπει να εισαχθεί σε ένα εύρος τιμών της εξαρτημένης μεταβλητής (σε αυτή την περίπτωση την τιμή των αγαθών σε συγκεκριμένες μήνες του έτους) και στο «Input διάστημα Χ» - για μια ανεξάρτητη (το μήνα). Επιβεβαιώνουμε την ενέργεια κάνοντας κλικ στο «Εντάξει». Σε ένα νέο φύλλο (εφόσον προβλέπεται κάτι τέτοιο), παίρνουμε τα στοιχεία για την παλινδρόμηση.

Χτίζουμε πάνω τους γραμμική εξίσωση της μορφής y = ax + b, όπου ως παράμετροι α και β είναι οι συντελεστές από τον αριθμό γραμμής του μήνα και το όνομα των συντελεστών και «Υ-τομής» γραμμή του φύλλου με τα αποτελέσματα της ανάλυσης παλινδρόμησης. Έτσι, η εξίσωση γραμμικής παλινδρόμησης (EQ) 3 για το πρόβλημα μπορεί να γραφτεί ως εξής:

Η τιμή των αγαθών Ν = 11.714 * 1.727,54 μήνες αριθμό +.

ή σε αλγεβρική σημειογραφία

y = 11,714 x + 1727,54

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

Για να αποφασίσει κατά πόσον η ληφθείσα επαρκώς γραμμική εξίσωση παλινδρόμησης χρησιμοποιώντας τις πολλαπλές συντελεστές συσχέτισης (CMC) και προσδιορισμός καθώς και δοκιμών και t-δοκιμή Fisher. Στον πίνακα παλινδρόμησης «Excel» με τα αποτελέσματα που ενεργούν κάτω από τα ονόματα πολλών R, R-Square, F-t-στατιστικά στοιχεία και στατιστικά στοιχεία, αντίστοιχα.

KMC R δίνει τη δυνατότητα να εκτιμηθεί η εγγύτητα πιθανολογική σχέση μεταξύ ανεξάρτητων και εξαρτημένων μεταβλητών. υψηλή τιμή του δείχνει μια αρκετά ισχυρή σύνδεση μεταξύ της μεταβλητής «αριθμός του μήνα» και «N Τιμή προϊόντος σε ρούβλια ανά 1 τόνο.» Ωστόσο, η φύση αυτής της σχέσης είναι άγνωστη.

Το τετράγωνο του συντελεστή προσδιορισμού R ² (RI) είναι μια αριθμητική χαρακτηριστικό του ποσοστού των συνολικών scatter και δείχνει μια διασπορά των πειραματικών τμήματος δεδομένων, δηλαδή, τιμές της εξαρτημένης μεταβλητής που αντιστοιχεί σε μια γραμμική εξίσωση παλινδρόμησης. Σε αυτό το πρόβλημα, η τιμή αυτή είναι 84,8%, mp. Ε Στατιστικά με υψηλό βαθμό ακρίβειας που επιτυγχάνεται περιγράφονται SD.

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

Η τιμή του t-στατιστικό (t test Student) βοηθά αξιολογήσει τη σημασία του συντελεστή σε οποιαδήποτε ελεύθερη άγνωστη γραμμική μέλος εξάρτηση. Εάν η τιμή του t-test> t _cr, η υπόθεση μιας γραμμικής εξίσωσης ασημαντότητα του ελεύθερου όρου απορρίπτεται.

Σε αυτό το πρόβλημα για μια ελεύθερη όρος μέσω μέσων «Excel» διαπιστώθηκε ότι t = 169,20903, και ρ = 2,89E-12, t. Ε έχουν μηδενική πιθανότητα ότι οι πιστοί θα απορριφθεί την υπόθεση της ασημαντότητα του ελεύθερου όρου. Για άγνωστους συντελεστής σε t = 5,79405, και ρ = 0,001158. Με άλλα λόγια, η πιθανότητα ότι μία απορριφθεί σωστή υπόθεση θα ασημαντότητα του συντελεστή για το άγνωστο, είναι 0,12%.

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

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

Πολλαπλής παλινδρόμησης πραγματοποιήθηκε στο Excel χρησιμοποιώντας την ίδια «Ανάλυση Δεδομένων» εργαλείο. Εξετάστε την συγκεκριμένη εφαρμογή.

Οδηγός της εταιρείας «ΑΑΑ» πρέπει να αποφασίσει αν θα αγοράσει το 20% των μετοχών της JSC «ΥΕΝ». Η τιμή (SP) είναι 70 εκατομμύρια δολάρια ΗΠΑ. Οι ειδικοί του «ΑΑΑ» συνέλεξε στοιχεία για παρόμοιες συναλλαγές. Αποφασίστηκε να εκτιμήσει την αξία των μετοχών αυτών των παραμέτρων, που εκφράζονται σε εκατομμύρια δολάρια ΗΠΑ, όπως:

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

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

Τα μέσα πίνακας απόφαση επεξεργαστή Excel

Πρώτα θα πρέπει να δημιουργήσετε έναν πίνακα των δεδομένων εισόδου. Είναι ως εξής:

επόμενο:

• κουτί κλήση «ανάλυσης δεδομένων»?
• επιλεγμένη ενότητα «Παλινδρόμηση»?
• το παράθυρο «Input διαστήματος Υ» χορηγείται φάσμα εξαρτημένη μεταβλητή τιμές από τη στήλη G?
• κλικ στο εικονίδιο με ένα κόκκινο βέλος στα δεξιά του παραθύρου «Input διαστήματος Χ» και απομονώνονται σε ένα εύρος φύλλο όλων των αξιών της στήλης Β, C, D, F.

Σημειώστε το σημείο «New φύλλο εργασίας» και κάντε κλικ στο «Ok».

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

Τα αποτελέσματα της μελέτης και τα συμπεράσματα

«Collect» στρογγυλευμένο από τα δεδομένα που παρουσιάζονται παραπάνω σχετικά με την εξίσωση Excel επεξεργαστή παλινδρόμησης πίνακα φύλλο:

SD = 0,103 * SOF + 0.541 * VO - 0,031 * VK + 0,405 * VD + 0.691 * VZP - 265.844.

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

y = 0,103 * x1 + x2 0541 * - 0,031 * x3 + 0,405 * χ4 + χ5 0691 * - 265844

Τα δεδομένα για «ΥΕΝ» JSC παρουσιάζονται στον παρακάτω πίνακα:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102,5	535,5	45.2	41,5	21.55	64.72

Αντικαθιστώντας τους στην εξίσωση παλινδρόμησης, έλαβε ποσό των 64.720.000 δολαρίων ΗΠΑ. Αυτό σημαίνει ότι οι μετοχές της JSC «ΥΕΝ» δεν θα πρέπει να αγοράσουν, επειδή το κόστος τους είναι πολύ υπερτιμημένα σε 70 εκατομμύρια δολάρια.

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

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