Μοντέλο παλινδρόμησης στο παράδειγμα excel. Ανάλυση συσχέτισης και παλινδρόμησης στο 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 διαθέτει ενσωματωμένες λειτουργίες που μπορούν να σας βοηθήσουν να υπολογίσετε τις παραμέτρους ενός μοντέλου γραμμικής παλινδρόμησης. Αλλά το πρόσθετο "Πακέτο ανάλυσης" θα το κάνει πιο γρήγορα.

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

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

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

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 (αυτός είναι ένας μικρός βαθμός επιρροής). Το σύμβολο «-» υποδηλώνει αρνητικό αντίκτυπο: όσο υψηλότερος είναι ο μισθός, τόσο λιγότερα άτομα παραιτούνται. Που είναι δίκαιο.

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

Για να το κάνετε αυτό χρειάζεστε:

1. Ανοίξτε το Excel

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

1 στήλη - αριθμός θέματος

2 στήλη - επιθετικότητασε σημεία

3 στήλη - αυτοαμφιβολίασε σημεία

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

4. Έχουμε λοιπόν ένα πρότυπο για τη γραμμή παλινδρόμησης - το λεγόμενο - διασκορπίζω οικόπεδο. Για να μεταβείτε στη γραμμή παλινδρόμησης, κάντε κλικ στο σχήμα που προκύπτει και πατήστε την καρτέλα κατασκευαστής, βρείτε στον πίνακα διατάξεις γραφημάτων και επιλέξτε Μ ΕΝΑ ket9 , λέει επίσης f(x)

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

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

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

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

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

Ανάλυση παλινδρόμησης στο 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.

1. Στην κατηγορία «Στατιστικά», επιλέξτε τη συνάρτηση CORREL.
2. Επιχείρημα "Array 1" - το πρώτο εύρος τιμών - χρόνος λειτουργίας μηχανής: A2:A14.
3. Επιχείρημα "Array 2" - δεύτερο εύρος τιμών - κόστος επισκευής: B2:B14. Κάντε κλικ στο OK.

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

Για ανάλυση συσχέτισης πολλών παραμέτρων (περισσότερων από 2), είναι πιο βολικό να χρησιμοποιήσετε το "Data Analysis" (το πρόσθετο "Analysis Package"). Πρέπει να επιλέξετε συσχέτιση από τη λίστα και να ορίσετε τον πίνακα. Ολοι.

Οι προκύπτοντες συντελεστές θα εμφανιστούν στον πίνακα συσχέτισης. Τοιουτοτροπώς:

Ανάλυση συσχέτισης και παλινδρόμησης

Στην πράξη, αυτές οι δύο τεχνικές χρησιμοποιούνται συχνά μαζί.

Παράδειγμα:

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

Ανάλυση παλινδρόμησης στο Microsoft Excel - Ο πιο ολοκληρωμένος οδηγός για τη χρήση του MS Excel για την επίλυση προβλημάτων ανάλυσης παλινδρόμησης στον τομέα των επιχειρηματικών αναλυτικών στοιχείων. Ο Konrad Carlberg εξηγεί ξεκάθαρα θεωρητικά ζητήματα, η γνώση των οποίων θα σας βοηθήσει να αποφύγετε πολλά λάθη τόσο κατά τη διεξαγωγή της ανάλυσης παλινδρόμησης μόνοι σας όσο και κατά την αξιολόγηση των αποτελεσμάτων της ανάλυσης που εκτελείται από άλλα άτομα. Όλο το υλικό, από απλές συσχετίσεις και t-test έως πολλαπλή ανάλυση συνδιακύμανσης, βασίζεται σε παραδείγματα πραγματικού κόσμου και συνοδεύεται από λεπτομερείς διαδικασίες βήμα προς βήμα.

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

Κόνραντ Κάρλμπεργκ. Ανάλυση παλινδρόμησης στο Microsoft Excel. – Μ.: Διαλεκτική, 2017. – 400 σελ.

Κατεβάστε τη σημείωση σε ή μορφή, παραδείγματα σε μορφή

Κεφάλαιο 1: Αξιολόγηση μεταβλητότητας δεδομένων

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

Το Excel προσφέρει δύο συναρτήσεις που επιστρέφουν διακύμανση: DISP.G() και DISP.V():

• Χρησιμοποιήστε τη συνάρτηση DISP.G() εάν οι προς επεξεργασία τιμές αποτελούν έναν πληθυσμό. Δηλαδή, οι τιμές που περιέχονται στο εύρος είναι οι μόνες τιμές που σας ενδιαφέρουν.
• Χρησιμοποιήστε τη συνάρτηση DISP.B() εάν οι προς επεξεργασία τιμές αποτελούν δείγμα από μεγαλύτερο πληθυσμό. Υποτίθεται ότι υπάρχουν πρόσθετες τιμές των οποίων τη διακύμανση μπορείτε επίσης να εκτιμήσετε.

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

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

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

Το μέσο άθροισμα των τετραγώνων που προσδιορίστηκε για το δείγμα παρέχει μια χαμηλότερη εκτίμηση της διακύμανσης του πληθυσμού. Η διακύμανση που υπολογίζεται με αυτόν τον τρόπο ονομάζεται εκτοπισμένοιεκτίμηση. Αποδεικνύεται ότι για να εξαλειφθεί η μεροληψία και να ληφθεί μια αμερόληπτη εκτίμηση, αρκεί να διαιρέσουμε το άθροισμα των τετραγωνικών αποκλίσεων όχι με το n, Πού n- μέγεθος δείγματος και n – 1.

Μέγεθος n – 1ονομάζεται αριθμός (αριθμός) βαθμών ελευθερίας. Υπάρχουν διάφοροι τρόποι υπολογισμού αυτής της ποσότητας, αν και όλοι περιλαμβάνουν είτε την αφαίρεση κάποιου αριθμού από το μέγεθος του δείγματος είτε την καταμέτρηση του αριθμού των κατηγοριών στις οποίες εμπίπτουν οι παρατηρήσεις.

Η ουσία της διαφοράς μεταξύ των συναρτήσεων DISP.G() και DISP.V() είναι η εξής:

• Στη συνάρτηση VAR.G(), το άθροισμα των τετραγώνων διαιρείται με τον αριθμό των παρατηρήσεων και επομένως αντιπροσωπεύει μια μεροληπτική εκτίμηση της διακύμανσης, τον αληθινό μέσο όρο.
• Στη συνάρτηση DISP.B(), το άθροισμα των τετραγώνων διαιρείται με τον αριθμό των παρατηρήσεων μείον 1, δηλ. από τον αριθμό των βαθμών ελευθερίας, που δίνει μια πιο ακριβή, αμερόληπτη εκτίμηση της διακύμανσης του πληθυσμού από τον οποίο προήλθε το δείγμα.

Τυπική απόκλιση τυπική απόκλιση, SD) – είναι η τετραγωνική ρίζα της διακύμανσης:

Ο τετραγωνισμός των αποκλίσεων μετατρέπει την κλίμακα μέτρησης σε μια άλλη μέτρηση, η οποία είναι το τετράγωνο της αρχικής: μέτρα - σε τετραγωνικά μέτρα, δολάρια - σε τετραγωνικά δολάρια κ.λπ. Η τυπική απόκλιση είναι η τετραγωνική ρίζα της διακύμανσης και επομένως μας οδηγεί πίσω στις αρχικές μονάδες μέτρησης. Όποιο είναι πιο βολικό.

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

Ας υποθέσουμε ότι συλλέξατε δεδομένα ύψους για 25 τυχαία επιλεγμένους ενήλικες άνδρες σε καθεμία από τις 50 πολιτείες. Στη συνέχεια, υπολογίζετε το μέσο ύψος των ενήλικων αρσενικών σε κάθε πολιτεία. Οι προκύπτουσες 50 μέσες τιμές, με τη σειρά τους, μπορούν να θεωρηθούν παρατηρήσεις. Από αυτό θα μπορούσατε να υπολογίσετε την τυπική τους απόκλιση, η οποία είναι τυπικό σφάλμα του μέσου όρου. Ρύζι. 1. συγκρίνει την κατανομή 1.250 ακατέργαστων μεμονωμένων τιμών (στοιχεία ύψους για 25 άνδρες σε καθεμία από τις 50 πολιτείες) με την κατανομή των μέσων όρων των 50 πολιτειών. Ο τύπος για την εκτίμηση του τυπικού σφάλματος του μέσου όρου (δηλαδή, η τυπική απόκλιση των μέσων, όχι μεμονωμένες παρατηρήσεις):

πού είναι το τυπικό σφάλμα του μέσου όρου; μικρό– τυπική απόκλιση των αρχικών παρατηρήσεων· n– αριθμός παρατηρήσεων στο δείγμα.

Ρύζι. 1. Η διακύμανση στους μέσους όρους από κράτος σε κράτος είναι σημαντικά μικρότερη από τη διακύμανση σε μεμονωμένες παρατηρήσεις.

Στη στατιστική, υπάρχει μια σύμβαση σχετικά με τη χρήση ελληνικών και λατινικών γραμμάτων για την αναπαράσταση στατιστικών μεγεθών. Συνηθίζεται να δηλώνονται οι παράμετροι του γενικού πληθυσμού με ελληνικά γράμματα και δείγματα στατιστικών με λατινικά γράμματα. Επομένως, όταν μιλάμε για την τυπική απόκλιση πληθυσμού, τη γράφουμε ως σ. αν ληφθεί υπόψη η τυπική απόκλιση του δείγματος, τότε χρησιμοποιούμε τον συμβολισμό s. Όσο για τα σύμβολα για τον προσδιορισμό των μέσων όρων, δεν συμφωνούν τόσο καλά μεταξύ τους. Η μέση τιμή του πληθυσμού συμβολίζεται με το ελληνικό γράμμα μ. Ωστόσο, το σύμβολο X̅ χρησιμοποιείται παραδοσιακά για να αναπαραστήσει τη μέση τιμή του δείγματος.

z-scoreεκφράζει τη θέση μιας παρατήρησης στην κατανομή σε μονάδες τυπικής απόκλισης. Για παράδειγμα, z = 1,5 σημαίνει ότι η παρατήρηση απέχει 1,5 τυπικές αποκλίσεις από τη μέση τιμή. Ορος z-scoreχρησιμοποιείται για μεμονωμένες αξιολογήσεις, π.χ. για διαστάσεις που αποδίδονται σε μεμονωμένα στοιχεία δείγματος. Ο όρος που χρησιμοποιείται για να αναφέρεται σε τέτοια στατιστικά στοιχεία (όπως ο μέσος όρος του κράτους) z-score:

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

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

Ας υποθέσουμε ότι εργάζεστε ως εκπαιδευτής σε ένα κλαμπ γκολφ. Καταφέρατε να μετρήσετε την απόσταση των βολών σας για μεγάλο χρονικό διάστημα και γνωρίζετε ότι ο μέσος όρος είναι 205 γιάρδες και η τυπική απόκλιση είναι 36 γιάρδες. Σου προσφέρεται ένα νέο κλαμπ, ισχυριζόμενος ότι θα αυξήσει την απόσταση χτυπήματος κατά 10 γιάρδες. Ζητάτε από κάθε έναν από τους επόμενους 81 θαμώνες του κλαμπ να κάνει μια δοκιμαστική βολή με ένα νέο κλαμπ και να καταγράψει την απόσταση αιώρησής του. Αποδείχθηκε ότι η μέση απόσταση με το νέο κλαμπ ήταν 215 γιάρδες. Ποια είναι η πιθανότητα μια διαφορά 10 γιάρδων (215 – 205) να οφείλεται αποκλειστικά σε δειγματοληπτικό σφάλμα; Ή για να το θέσω αλλιώς: Ποια είναι η πιθανότητα, σε πιο εκτεταμένες δοκιμές, ο νέος σύλλογος να μην παρουσιάσει αύξηση στην απόσταση χτυπήματος σε σχέση με τον υπάρχοντα μακροπρόθεσμο μέσο όρο των 205 γιάρδων;

Μπορούμε να το ελέγξουμε δημιουργώντας ένα z-score. Τυπικό σφάλμα του μέσου όρου:

Στη συνέχεια z-score:

Πρέπει να βρούμε την πιθανότητα ο μέσος όρος του δείγματος να απέχει 2,5 σ από τον μέσο όρο του πληθυσμού. Αν η πιθανότητα είναι μικρή, τότε οι διαφορές δεν οφείλονται στην τύχη, αλλά στην ποιότητα του νέου συλλόγου. Το Excel δεν διαθέτει έτοιμη συνάρτηση για τον προσδιορισμό της πιθανότητας βαθμολογίας z. Ωστόσο, μπορείτε να χρησιμοποιήσετε τον τύπο =1-NORM.ST.DIST(z-score,TRUE), όπου η συνάρτηση NORM.ST.DIST() επιστρέφει την περιοχή κάτω από την κανονική καμπύλη στα αριστερά της βαθμολογίας z (Εικόνα 2).

Ρύζι. 2. Η συνάρτηση NORM.ST.DIST() επιστρέφει την περιοχή κάτω από την καμπύλη στα αριστερά της τιμής z. Για να μεγεθύνετε την εικόνα, κάντε δεξί κλικ πάνω της και επιλέξτε Άνοιγμα εικόνας σε νέα καρτέλα

Το δεύτερο όρισμα της συνάρτησης NORM.ST.DIST() μπορεί να πάρει δύο τιμές: TRUE - η συνάρτηση επιστρέφει την περιοχή της περιοχής κάτω από την καμπύλη στα αριστερά του σημείου που καθορίζεται από το πρώτο όρισμα. FALSE – η συνάρτηση επιστρέφει το ύψος της καμπύλης στο σημείο που καθορίζεται από το πρώτο όρισμα.

Εάν ο μέσος όρος πληθυσμού (μ) και η τυπική απόκλιση (σ) δεν είναι γνωστοί, χρησιμοποιείται η τιμή t (βλ. λεπτομέρειες). Οι δομές z-score και t-score διαφέρουν στο ότι η τυπική απόκλιση s που προκύπτει από τα αποτελέσματα του δείγματος χρησιμοποιείται για την εύρεση του t-score και όχι της γνωστής τιμής της παραμέτρου του πληθυσμού σ. Η κανονική καμπύλη έχει ένα μόνο σχήμα και το σχήμα της κατανομής τιμής t ποικίλλει ανάλογα με τον αριθμό των βαθμών ελευθερίας df. βαθμούς ελευθερίας) του δείγματος που αντιπροσωπεύει. Ο αριθμός των βαθμών ελευθερίας του δείγματος είναι ίσος με n – 1, Πού n- μέγεθος δείγματος (Εικ. 3).

Ρύζι. 3. Το σχήμα των κατανομών t που προκύπτουν σε περιπτώσεις που η παράμετρος σ είναι άγνωστη διαφέρει από το σχήμα της κανονικής κατανομής

Το Excel έχει δύο συναρτήσεις για την κατανομή t, που ονομάζεται επίσης κατανομή Student: Η STUDENT.DIST() επιστρέφει την περιοχή κάτω από την καμπύλη στα αριστερά μιας δεδομένης τιμής t και η STUDENT.DIST.PH() επιστρέφει την περιοχή στην δικαίωμα.

Κεφάλαιο 2. Συσχέτιση

Η συσχέτιση είναι ένα μέτρο εξάρτησης μεταξύ στοιχείων ενός συνόλου διατεταγμένων ζευγών. Η συσχέτιση χαρακτηρίζεται Συντελεστές συσχέτισης Pearson–r. Ο συντελεστής μπορεί να λάβει τιμές στην περιοχή από -1,0 έως +1,0.

Οπου SxΚαι S y– τυπικές αποκλίσεις μεταβλητών ΧΚαι Υ, S xy– συνδιακύμανση:

Σε αυτόν τον τύπο, η συνδιακύμανση διαιρείται με τις τυπικές αποκλίσεις των μεταβλητών ΧΚαι Υ, αφαιρώντας έτσι τα αποτελέσματα κλιμάκωσης που σχετίζονται με τη μονάδα από τη συνδιακύμανση. Το Excel χρησιμοποιεί τη συνάρτηση CORREL(). Το όνομα αυτής της συνάρτησης δεν περιέχει τα προσδιοριστικά στοιχεία Г και В, τα οποία χρησιμοποιούνται στα ονόματα συναρτήσεων όπως STANDARDEV(), VARIANCE() ή COVARIANCE(). Παρόλο που ο συντελεστής συσχέτισης του δείγματος παρέχει μια μεροληπτική εκτίμηση, ο λόγος της μεροληψίας είναι διαφορετικός από ό,τι στην περίπτωση της διακύμανσης ή της τυπικής απόκλισης.

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

Ο τυπικός συντελεστής συσχέτισης προορίζεται για χρήση με μεταβλητές που σχετίζονται μεταξύ τους με γραμμική σχέση. Η παρουσία μη γραμμικότητας ή/και σφαλμάτων στα δεδομένα (ακραίες τιμές) οδηγεί σε λανθασμένο υπολογισμό του συντελεστή συσχέτισης. Για τη διάγνωση προβλημάτων δεδομένων, συνιστάται η δημιουργία διαγραμμάτων διασποράς. Αυτός είναι ο μόνος τύπος γραφήματος στο Excel που αντιμετωπίζει τόσο τον οριζόντιο όσο και τον κάθετο άξονα ως άξονες τιμών. Ένα γραμμικό γράφημα ορίζει μια από τις στήλες ως άξονα κατηγορίας, ο οποίος παραμορφώνει την εικόνα των δεδομένων (Εικ. 4).

Ρύζι. 4. Οι γραμμές παλινδρόμησης φαίνονται ίδιες, αλλά συγκρίνετε τις εξισώσεις τους μεταξύ τους

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

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

Κεφάλαιο 3: Απλή παλινδρόμηση

Εάν δύο μεταβλητές σχετίζονται μεταξύ τους, έτσι ώστε η τιμή του συντελεστή συσχέτισης να υπερβαίνει, ας πούμε, το 0,5, τότε σε αυτήν την περίπτωση είναι δυνατό να προβλεφθεί (με κάποια ακρίβεια) η άγνωστη τιμή μιας μεταβλητής από τη γνωστή τιμή της άλλης . Για να λάβετε τις προβλεπόμενες τιμές τιμής με βάση τα δεδομένα που φαίνονται στο Σχ. 5, μπορείτε να χρησιμοποιήσετε οποιαδήποτε από τις διάφορες πιθανές μεθόδους, αλλά σχεδόν σίγουρα δεν θα χρησιμοποιήσετε αυτήν που φαίνεται στο Σχ. 5. Ωστόσο, θα πρέπει να εξοικειωθείτε με αυτό, γιατί καμία άλλη μέθοδος δεν σας επιτρέπει να αποδείξετε τη σύνδεση μεταξύ συσχέτισης και πρόβλεψης τόσο ξεκάθαρα όσο αυτή. Στο Σχ. Το Σχήμα 5 στο εύρος B2:C12 δείχνει ένα τυχαίο δείγμα δέκα σπιτιών και παρέχει δεδομένα για την έκταση κάθε σπιτιού (σε τετραγωνικά πόδια) και την τιμή πώλησής του.

Ρύζι. 5. Οι προβλεπόμενες τιμές των τιμών πώλησης σχηματίζουν μια ευθεία γραμμή

Βρείτε τους μέσους όρους, τις τυπικές αποκλίσεις και τον συντελεστή συσχέτισης (εύρος A14:C18). Υπολογίστε τις βαθμολογίες z της περιοχής (E2:E12). Για παράδειγμα, το κελί E3 περιέχει τον τύπο: =(B3-$B$14)/$B$15. Υπολογίστε τις βαθμολογίες z της προβλεπόμενης τιμής (F2:F12). Για παράδειγμα, το κελί F3 περιέχει τον τύπο: =ЕЗ*$В$18. Μετατρέψτε τις βαθμολογίες z σε τιμές δολαρίου (H2:H12). Στο κελί NZ ο τύπος είναι: =F3*$C$15+$C$14.

Σημειώστε ότι η προβλεπόμενη τιμή τείνει πάντα να μετατοπίζεται προς τη μέση τιμή του 0. Όσο πιο κοντά είναι ο συντελεστής συσχέτισης στο μηδέν, τόσο πιο κοντά στο μηδέν είναι η προβλεπόμενη βαθμολογία z. Στο παράδειγμά μας, ο συντελεστής συσχέτισης μεταξύ της περιοχής και της τιμής πώλησης είναι 0,67 και η προβλεπόμενη τιμή είναι 1,0 * 0,67, δηλ. 0,67. Αυτό αντιστοιχεί σε μια υπέρβαση μιας τιμής πάνω από τη μέση τιμή ίση με τα δύο τρίτα μιας τυπικής απόκλισης. Εάν ο συντελεστής συσχέτισης ήταν ίσος με 0,5, τότε η προβλεπόμενη τιμή θα ήταν 1,0 * 0,5, δηλ. 0,5. Αυτό αντιστοιχεί σε μια υπέρβαση μιας τιμής πάνω από τη μέση τιμή ίση με μόνο μισή τυπική απόκλιση. Όποτε η τιμή του συντελεστή συσχέτισης διαφέρει από την ιδανική τιμή, δηλ. μεγαλύτερη από -1,0 και μικρότερη από 1,0, η βαθμολογία της προβλεπόμενης μεταβλητής θα πρέπει να είναι πιο κοντά στον μέσο όρο της από τη βαθμολογία της προβλεπόμενης (ανεξάρτητης) μεταβλητής προς τη δική της. Αυτό το φαινόμενο ονομάζεται παλινδρόμηση στη μέση τιμή, ή απλά παλινδρόμηση.

Το Excel έχει πολλές συναρτήσεις για τον προσδιορισμό των συντελεστών μιας εξίσωσης γραμμής παλινδρόμησης (που ονομάζεται γραμμή τάσης στο Excel) y =kx + σι. Για να προσδιορίσετε κεξυπηρετεί τη λειτουργία

=SLOPE(γνωστές_τιμές_υ, γνωστές_χ_τιμές)

Εδώ στοείναι η προβλεπόμενη μεταβλητή, και Χ– ανεξάρτητη μεταβλητή. Πρέπει να ακολουθείτε αυστηρά αυτή τη σειρά μεταβλητών. Η κλίση της γραμμής παλινδρόμησης, ο συντελεστής συσχέτισης, οι τυπικές αποκλίσεις των μεταβλητών και η συνδιακύμανση συνδέονται στενά (Εικόνα 6). Η συνάρτηση INTERMEPT() επιστρέφει την τιμή που παρεμποδίζεται από τη γραμμή παλινδρόμησης στον κατακόρυφο άξονα:

=LIMIT(γνωστές_τιμές_υ, γνωστές_χ_τιμές)

Ρύζι. 6. Η σχέση μεταξύ τυπικών αποκλίσεων μετατρέπει τη συνδιακύμανση σε συντελεστή συσχέτισης και την κλίση της γραμμής παλινδρόμησης

Σημειώστε ότι ο αριθμός των τιμών x και y που παρέχονται ως ορίσματα στις συναρτήσεις SLOPE() και INTERCEPT() πρέπει να είναι ο ίδιος.

Στην ανάλυση παλινδρόμησης, χρησιμοποιείται ένας άλλος σημαντικός δείκτης - R 2 (R-τετράγωνο) ή ο συντελεστής προσδιορισμού. Καθορίζει τη συμβολή στη συνολική μεταβλητότητα των δεδομένων από τη σχέση μεταξύ ΧΚαι στο. Στο Excel, υπάρχει μια συνάρτηση για αυτό που ονομάζεται CVPIERSON(), η οποία λαμβάνει ακριβώς τα ίδια ορίσματα με τη συνάρτηση CORREL().

Δύο μεταβλητές με μη μηδενικό συντελεστή συσχέτισης μεταξύ τους λέγεται ότι εξηγούν τη διακύμανση ή έχουν επεξηγημένη διακύμανση. Η τυπικά εξηγούμενη διακύμανση εκφράζεται ως ποσοστό. Ετσι R 2 = 0,81 σημαίνει ότι εξηγείται το 81% της διακύμανσης (σκέδασης) δύο μεταβλητών. Το υπόλοιπο 19% οφείλεται σε τυχαίες διακυμάνσεις.

Το Excel έχει μια συνάρτηση TREND που διευκολύνει τους υπολογισμούς. Συνάρτηση TREND():

• αποδέχεται τις γνωστές τιμές που παρέχετε Χκαι γνωστές αξίες στο;
• υπολογίζει την κλίση της γραμμής παλινδρόμησης και τη σταθερά (τομή).
• επιστρέφει προβλεπόμενες τιμές στο, προσδιορίζεται με την εφαρμογή μιας εξίσωσης παλινδρόμησης σε γνωστές τιμές Χ(Εικ. 7).

Η συνάρτηση TREND() είναι μια συνάρτηση πίνακα (αν δεν έχετε συναντήσει τέτοιες συναρτήσεις πριν, τη συνιστώ).

Ρύζι. 7. Η χρήση της συνάρτησης TREND() σάς επιτρέπει να επιταχύνετε και να απλοποιήσετε τους υπολογισμούς σε σύγκριση με τη χρήση ενός ζεύγους συναρτήσεων SLOPE() και INTERCEPT()

Για να εισαγάγετε τη συνάρτηση TREND() ως τύπο πίνακα στα κελιά G3:G12, επιλέξτε την περιοχή G3:G12, εισαγάγετε τον τύπο TREND(NW:C12;B3:B12), πατήστε παρατεταμένα τα πλήκτρα και μόνο μετά πατήστε το πλήκτρο . Σημειώστε ότι ο τύπος περικλείεται σε σγουρά τιράντες: ( και ). Αυτός είναι ο τρόπος με τον οποίο το Excel σάς λέει ότι αυτός ο τύπος ερμηνεύεται ως τύπος πίνακα. Μην εισάγετε μόνοι σας τις παρενθέσεις: Εάν προσπαθήσετε να τις εισαγάγετε μόνοι σας ως μέρος ενός τύπου, το Excel θα αντιμετωπίσει την εισαγωγή σας ως μια κανονική συμβολοσειρά κειμένου.

Η συνάρτηση TREND() έχει δύο ακόμη ορίσματα: new_values_xΚαι συνθ. Το πρώτο σάς επιτρέπει να κάνετε μια πρόβλεψη για το μέλλον και το δεύτερο μπορεί να αναγκάσει τη γραμμή παλινδρόμησης να περάσει από την αρχή (μια τιμή TRUE λέει στο Excel να χρησιμοποιήσει την υπολογισμένη σταθερά, μια τιμή FALSE λέει στο Excel να χρησιμοποιήσει μια σταθερά = 0 ). Το Excel σάς επιτρέπει να σχεδιάσετε μια γραμμή παλινδρόμησης σε ένα γράφημα έτσι ώστε να διέρχεται από την αρχή. Ξεκινήστε σχεδιάζοντας ένα διάγραμμα διασποράς και, στη συνέχεια, κάντε δεξί κλικ σε έναν από τους δείκτες της σειράς δεδομένων. Επιλέξτε το στοιχείο στο μενού περιβάλλοντος που ανοίγει Προσθέστε μια γραμμή τάσης; επιλέξτε μια επιλογή Γραμμικός; εάν χρειάζεται, κάντε κύλιση προς τα κάτω στον πίνακα, επιλέξτε το πλαίσιο Ρύθμιση διασταύρωσης; Βεβαιωθείτε ότι το αντίστοιχο πλαίσιο κειμένου έχει οριστεί σε 0.0.

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

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

Οπου rC.B. . W- συντελεστής συσχέτισης μεταξύ των μεταβλητών College και Books με εξαίρεση την επιρροή (σταθερή τιμή) της μεταβλητής Wealth. rC.B.- συντελεστής συσχέτισης μεταξύ των μεταβλητών College και Books. rCW- συντελεστής συσχέτισης μεταξύ των μεταβλητών College και Welfare. rB.W.- συντελεστής συσχέτισης μεταξύ των μεταβλητών Βιβλία και Πρόνοια.

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

Ρύζι. 8. Μερική συσχέτιση ως συσχέτιση υπολειμμάτων

Για να απλοποιήσετε τον υπολογισμό του πίνακα των συντελεστών συσχέτισης (B16:E19), χρησιμοποιήστε το πακέτο ανάλυσης Excel (μενού Δεδομένα –> Ανάλυση –> Ανάλυση Δεδομένων). Από προεπιλογή, αυτό το πακέτο δεν είναι ενεργό στο Excel. Για να το εγκαταστήσετε, μεταβείτε στο μενού Αρχείο –> Επιλογές –> Πρόσθετα. Στο κάτω μέρος του ανοιγμένου παραθύρου ΕπιλογέςΠροέχωβρείτε το χωράφι Ελεγχος, επιλέξτε ΠρόσθεταΠροέχω, κάντε κλικ Πάω. Επιλέξτε το πλαίσιο δίπλα στο πρόσθετο Πακέτο ανάλυσης. Κάντε κλικ στο A ανάλυση δεδομένων, επιλέξτε επιλογή Συσχέτιση. Καθορίστε $B$2:$D$13 ως το διάστημα εισαγωγής, επιλέξτε το πλαίσιο Ετικέτες στην πρώτη γραμμή, καθορίστε το $B$16:$E$19 ως το διάστημα εξόδου.

Μια άλλη δυνατότητα είναι να προσδιοριστεί η ημιμερική συσχέτιση. Για παράδειγμα, ερευνάτε τις επιπτώσεις του ύψους και της ηλικίας στο βάρος. Έτσι, έχετε δύο προγνωστικές μεταβλητές - ύψος και ηλικία, και μια μεταβλητή πρόβλεψης - βάρος. Θέλετε να εξαιρέσετε την επιρροή μιας μεταβλητής πρόβλεψης σε μια άλλη, αλλά όχι στη μεταβλητή πρόβλεψης:

όπου H – Ύψος, W – Βάρος, A – Ηλικία. Ο δείκτης συντελεστών ημιμερικής συσχέτισης χρησιμοποιεί παρενθέσεις για να δείξει ποια μεταβλητή αφαιρείται και από ποια μεταβλητή. Σε αυτήν την περίπτωση, ο συμβολισμός W(H.A) υποδεικνύει ότι η επίδραση της μεταβλητής Age αφαιρείται από τη μεταβλητή Height, αλλά όχι από τη μεταβλητή Weight.

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

Κεφάλαιο 4. Συνάρτηση LINEST().

Η συνάρτηση LINEST() επιστρέφει 10 στατιστικά στοιχεία παλινδρόμησης. Η συνάρτηση LINEST() είναι μια συνάρτηση πίνακα. Για να το εισαγάγετε, επιλέξτε μια περιοχή που περιέχει πέντε σειρές και δύο στήλες, πληκτρολογήστε τον τύπο και κάντε κλικ (Εικ. 9):

LINEST(B2:B21,A2:A21,TRUE,TRUE)

Ρύζι. 9. Συνάρτηση LINEST(): α) επιλέξτε το εύρος D2:E6, β) εισάγετε τον τύπο όπως φαίνεται στη γραμμή τύπων, γ) κάντε κλικ

Η συνάρτηση LINEST() επιστρέφει:

• συντελεστής παλινδρόμησης (ή κλίση, κελί D2).
• τμήμα (ή σταθερά, κελί E3).
• τυπικά σφάλματα συντελεστή παλινδρόμησης και σταθερά (εύρος D3:E3).
• συντελεστής προσδιορισμού R2 για παλινδρόμηση (κελί D4).
• τυπικό σφάλμα εκτίμησης (κελί E4).
• F-test για πλήρη παλινδρόμηση (κελί D5).
• αριθμός βαθμών ελευθερίας για το υπόλοιπο άθροισμα τετραγώνων (κελί E5).
• άθροισμα παλινδρόμησης τετραγώνων (κελί D6).
• υπολειπόμενο άθροισμα τετραγώνων (κελί Ε6).

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

Τυπικό σφάλμαστην περίπτωσή μας, είναι η τυπική απόκλιση που υπολογίζεται για τα δειγματοληπτικά σφάλματα. Δηλαδή, αυτή είναι μια κατάσταση όπου ο γενικός πληθυσμός έχει ένα στατιστικό στοιχείο και το δείγμα έχει άλλο. Η διαίρεση του συντελεστή παλινδρόμησης με το τυπικό σφάλμα δίνει μια τιμή 2,092/0,818 = 2,559. Με άλλα λόγια, ένας συντελεστής παλινδρόμησης 2,092 απέχει δυόμισι τυπικά σφάλματα από το μηδέν.

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

Μπορείτε να προσδιορίσετε την πιθανότητα να λάβετε έναν συντελεστή παλινδρόμησης δείγματος 2,092 εάν η πραγματική τιμή του στον πληθυσμό είναι 0,0 χρησιμοποιώντας τη συνάρτηση

STUDENT.DIST.PH (κριτήριο t = 2.559, αριθμός βαθμών ελευθερίας = 18)

Γενικά, ο αριθμός των βαθμών ελευθερίας = n – k – 1, όπου n είναι ο αριθμός των παρατηρήσεων και k ο αριθμός των προγνωστικών μεταβλητών.

Αυτός ο τύπος επιστρέφει 0,00987 ή στρογγυλοποιείται στο 1%. Μας λέει ότι εάν ο συντελεστής παλινδρόμησης για τον πληθυσμό είναι 0%, τότε η πιθανότητα να ληφθεί ένα δείγμα 20 ατόμων για τα οποία ο εκτιμώμενος συντελεστής παλινδρόμησης είναι 2,092 είναι μέτριο 1%.

Το τεστ F (κελί D5 στο Σχ. 9) εκτελεί τις ίδιες λειτουργίες σε σχέση με την πλήρη παλινδρόμηση με το τεστ t σε σχέση με τον συντελεστή απλής παλινδρόμησης κατά ζεύγη. Το τεστ F χρησιμοποιείται για να ελεγχθεί εάν ο συντελεστής προσδιορισμού R 2 για μια παλινδρόμηση είναι αρκετά μεγάλος ώστε να απορρίψει την υπόθεση ότι στον πληθυσμό έχει τιμή 0,0, που δείχνει ότι δεν υπάρχει διακύμανση που να εξηγείται από τον προβλεπόμενο και την προβλεπόμενη μεταβλητή. Όταν υπάρχει μόνο μία μεταβλητή πρόβλεψης, η δοκιμή F είναι ακριβώς ίση με το τετράγωνο του τεστ t.

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

Στατιστικά R2ποσοτικοποιεί την αναλογία διακύμανσης που εξηγείται.

Τυπικό σφάλμα εκτίμησης.Στο Σχ. Το σχήμα 4.9 παρουσιάζει τις προβλεπόμενες τιμές της μεταβλητής Weight, που λαμβάνονται με βάση τη σχέση της με τη μεταβλητή Height. Το εύρος E2:E21 περιέχει τις υπολειπόμενες τιμές για τη μεταβλητή Weight. Πιο συγκεκριμένα, αυτά τα υπολείμματα ονομάζονται σφάλματα - εξ ου και ο όρος τυπικό σφάλμα εκτίμησης.

Ρύζι. 10. Τόσο το R 2 όσο και το τυπικό σφάλμα της εκτίμησης εκφράζουν την ακρίβεια των προβλέψεων που λαμβάνονται χρησιμοποιώντας παλινδρόμηση

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

(ύψος * 2.092 – 3.591) ± 2.092 * 21.118

F-statisticείναι ο λόγος της διακύμανσης μεταξύ ομάδων προς διακύμανση εντός ομάδας. Αυτό το όνομα εισήχθη από τον στατιστικολόγο George Snedecor προς τιμήν του Sir, ο οποίος ανέπτυξε την ανάλυση διακύμανσης (ANOVA, Analysis of Variance) στις αρχές του 20ου αιώνα.

Ο συντελεστής προσδιορισμού R 2 εκφράζει την αναλογία του συνολικού αθροίσματος των τετραγώνων που σχετίζεται με την παλινδρόμηση. Η τιμή (1 – R 2) εκφράζει την αναλογία του συνολικού αθροίσματος τετραγώνων που σχετίζονται με υπολείμματα - σφάλματα πρόβλεψης. Η δοκιμή F μπορεί να ληφθεί χρησιμοποιώντας τη συνάρτηση LINEST (κελί F5 στην Εικ. 11), χρησιμοποιώντας αθροίσματα τετραγώνων (εύρος G10:J11), χρησιμοποιώντας αναλογίες διακύμανσης (εύρος G14:J15). Οι τύποι μπορούν να μελετηθούν στο συνημμένο αρχείο Excel.

Ρύζι. 11. Υπολογισμός κριτηρίου F

Όταν χρησιμοποιείτε ονομαστικές μεταβλητές, χρησιμοποιείται εικονική κωδικοποίηση (Εικόνα 12). Για την κωδικοποίηση τιμών, είναι βολικό να χρησιμοποιήσετε τις τιμές 0 και 1. Η πιθανότητα F υπολογίζεται χρησιμοποιώντας τη συνάρτηση:

F.DIST.PH(K2;I2;I3)

Εδώ, η συνάρτηση F.DIST.PH() επιστρέφει την πιθανότητα απόκτησης ενός κριτηρίου F που υπακούει στην κεντρική κατανομή F (Εικ. 13) για δύο σύνολα δεδομένων με τους αριθμούς βαθμών ελευθερίας που δίνονται στα κελιά I2 και I3 , η τιμή του οποίου συμπίπτει με την τιμή που δίνεται στο κελί K2.

Ρύζι. 12. Ανάλυση παλινδρόμησης χρησιμοποιώντας εικονικές μεταβλητές

Ρύζι. 13. Κεντρική κατανομή F στο λ = 0

Κεφάλαιο 5. Πολλαπλή παλινδρόμηση

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

LINEST(A2: A41; B2: C41;;TRUE)

Ομοίως στην περίπτωση τριών μεταβλητών:

LINEST(A2:A61,B2:D61,;TRUE)

Ας υποθέσουμε ότι θέλετε να μελετήσετε τις πιθανές επιπτώσεις της ηλικίας και της διατροφής στα επίπεδα της LDL - λιποπρωτεΐνες χαμηλής πυκνότητας, οι οποίες πιστεύεται ότι είναι υπεύθυνες για το σχηματισμό αθηρωματικών πλακών, οι οποίες προκαλούν αθηροθρόμβωση (Εικ. 14).

Ρύζι. 14. Πολλαπλή παλινδρόμηση

Το R 2 της πολλαπλής παλινδρόμησης (που αντανακλάται στο κελί F13) είναι μεγαλύτερο από το R 2 οποιασδήποτε απλής παλινδρόμησης (E4, H4). Η πολλαπλή παλινδρόμηση χρησιμοποιεί πολλαπλές μεταβλητές πρόβλεψης ταυτόχρονα. Σε αυτή την περίπτωση, το R2 σχεδόν πάντα αυξάνεται.

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

Εμφάνιση των αποτελεσμάτων που επιστρέφονται από τη συνάρτηση LINEST() για πολλαπλή παλινδρόμηση (Εικόνα 15). Οι συντελεστές παλινδρόμησης εξάγονται ως μέρος των αποτελεσμάτων που επιστρέφονται από τη συνάρτηση LINEST(). με αντίστροφη σειρά μεταβλητών(Το G–H–I αντιστοιχεί στο C–B–A).

Ρύζι. 15. Οι συντελεστές και τα τυπικά λάθη τους εμφανίζονται με αντίστροφη σειρά στο φύλλο εργασίας.

Οι αρχές και οι διαδικασίες που χρησιμοποιούνται στην ανάλυση παλινδρόμησης μιας μεταβλητής πρόβλεψης προσαρμόζονται εύκολα για να λάβουν υπόψη πολλαπλές μεταβλητές πρόβλεψης. Αποδεικνύεται ότι μεγάλο μέρος αυτής της προσαρμογής εξαρτάται από την εξάλειψη της επιρροής των μεταβλητών πρόβλεψης μεταξύ τους. Το τελευταίο συνδέεται με μερικούς και ημιμερικούς συσχετισμούς (Εικ. 16).

Ρύζι. 16. Η πολλαπλή παλινδρόμηση μπορεί να εκφραστεί μέσω αναδρομής κατά ζεύγη υπολειμμάτων (βλ. αρχείο Excel για τύπους)

Στο Excel, υπάρχουν συναρτήσεις που παρέχουν πληροφορίες σχετικά με τις κατανομές t και F. Οι συναρτήσεις των οποίων τα ονόματα περιλαμβάνουν το τμήμα DIST, όπως οι STUDENT.DIST() και F.DIST(), λαμβάνουν ένα τεστ t ή F-test ως όρισμα και επιστρέφουν την πιθανότητα παρατήρησης μιας καθορισμένης τιμής. Οι συναρτήσεις των οποίων τα ονόματα περιλαμβάνουν το τμήμα OBR, όπως οι STUDENT.INR() και F.INV(), λαμβάνουν μια τιμή πιθανότητας ως όρισμα και επιστρέφουν μια τιμή κριτηρίου που αντιστοιχεί στην καθορισμένη πιθανότητα.

Δεδομένου ότι αναζητούμε κρίσιμες τιμές της κατανομής t που κόβουν τις άκρες των περιοχών της ουράς της, περνάμε το 5% ως όρισμα σε μία από τις συναρτήσεις STUDENT.INV(), η οποία επιστρέφει την τιμή που αντιστοιχεί σε αυτήν την πιθανότητα (Εικ. 17, 18).

Ρύζι. 17. Τεστ δύο ουρών

Ρύζι. 18. Τεστ μονής ουράς

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

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

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

Ρύζι. 19. Σύγκριση δύο μοντέλων δοκιμάζοντας διαφορές στα αποτελέσματά τους

Τα αποτελέσματα της συνάρτησης LINEST() (εύρος H2:K6) σχετίζονται με αυτό που ονομάζω πλήρες μοντέλο, το οποίο παλινδρομεί τη μεταβλητή LDL στις μεταβλητές Διατροφή, Ηλικία και HDL. Το εύρος H9:J13 παρουσιάζει υπολογισμούς χωρίς να λαμβάνεται υπόψη η προγνωστική μεταβλητή Διατροφή. Αυτό το ονομάζω περιορισμένο μοντέλο. Στο πλήρες μοντέλο, το 49,2% της διακύμανσης στην εξαρτημένη μεταβλητή LDL εξηγήθηκε από τις προγνωστικές μεταβλητές. Στο περιορισμένο μοντέλο, μόνο το 30,8% της LDL εξηγείται από τις μεταβλητές Age και HDL. Η απώλεια στο R 2 λόγω εξαίρεσης της μεταβλητής Diet από το μοντέλο είναι 0,183. Στην περιοχή G15:L17, γίνονται υπολογισμοί που δείχνουν ότι υπάρχει μόνο μια πιθανότητα 0,0288 ότι η επίδραση της μεταβλητής Diet είναι τυχαία. Στο υπόλοιπο 97,1%, η διατροφή έχει επίδραση στην LDL.

Κεφάλαιο 6: Υποθέσεις και προφυλάξεις για ανάλυση παλινδρόμησης

Ο όρος "υπόθεση" δεν ορίζεται αρκετά αυστηρά και ο τρόπος που χρησιμοποιείται υποδηλώνει ότι εάν η υπόθεση δεν ικανοποιείται, τότε τα αποτελέσματα ολόκληρης της ανάλυσης είναι τουλάχιστον αμφισβητήσιμα ή πιθανώς άκυρα. Αυτό δεν συμβαίνει στην πραγματικότητα, αν και σίγουρα υπάρχουν περιπτώσεις όπου η παραβίαση μιας υπόθεσης αλλάζει ριζικά την εικόνα. Βασικές παραδοχές: α) τα υπολείμματα της μεταβλητής Υ κατανέμονται κανονικά σε οποιοδήποτε σημείο Χ κατά μήκος της γραμμής παλινδρόμησης. β) Οι τιμές Y εξαρτώνται γραμμικά από τις τιμές X. γ) η διασπορά των υπολειμμάτων είναι περίπου η ίδια σε κάθε σημείο Χ. δ) δεν υπάρχει εξάρτηση μεταξύ των υπολειμμάτων.

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

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

Ρύζι. 20. Λογιστική παλινδρόμηση

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

Στη γλώσσα των στατιστικών, η απόρριψη της μηδενικής υπόθεσης όταν είναι πραγματικά αληθής ονομάζεται σφάλμα τύπου Ι.

Στο πρόσθετο Ανάλυση Δεδομένωνπροσφέρει ένα βολικό εργαλείο για τη δημιουργία τυχαίων αριθμών, επιτρέποντας στον χρήστη να καθορίσει το επιθυμητό σχήμα της κατανομής (για παράδειγμα, Normal, Binomial ή Poisson), καθώς και τη μέση και τυπική απόκλιση.

Διαφορές μεταξύ συναρτήσεων της οικογένειας STUDENT.DIST().Ξεκινώντας με το Excel 2010, είναι διαθέσιμες τρεις διαφορετικές μορφές της συνάρτησης που επιστρέφουν την αναλογία της κατανομής στα αριστερά ή/και στα δεξιά μιας δεδομένης τιμής t-test. Η συνάρτηση STUDENT.DIST() επιστρέφει το κλάσμα της περιοχής κάτω από την καμπύλη κατανομής στα αριστερά της τιμής t-test που καθορίζετε. Ας υποθέσουμε ότι έχετε 36 παρατηρήσεις, οπότε ο αριθμός βαθμών ελευθερίας για την ανάλυση είναι 34 και η τιμή t-test = 1,69. Σε αυτή την περίπτωση ο τύπος

STUDENT.DIST(+1,69,34,TRUE)

επιστρέφει την τιμή 0,05 ή 5% (Εικόνα 21). Το τρίτο όρισμα της συνάρτησης STUDENT.DIST() μπορεί να είναι TRUE ή FALSE. Εάν οριστεί σε TRUE, η συνάρτηση επιστρέφει την αθροιστική περιοχή κάτω από την καμπύλη στα αριστερά της καθορισμένης δοκιμής t, εκφρασμένη ως αναλογία. Αν είναι FALSE, η συνάρτηση επιστρέφει το σχετικό ύψος της καμπύλης στο σημείο που αντιστοιχεί στο t-test. Άλλες εκδόσεις της συνάρτησης STUDENT.DIST() - STUDENT.DIST.PH() και STUDENT.DIST.2X() - λαμβάνουν μόνο την τιμή t-test και τον αριθμό των βαθμών ελευθερίας ως ορίσματα και δεν απαιτούν τον καθορισμό τρίτου επιχείρημα.

Ρύζι. 21. Η πιο σκούρα σκιασμένη περιοχή στην αριστερή ουρά της κατανομής αντιστοιχεί στην αναλογία της περιοχής κάτω από την καμπύλη προς τα αριστερά μιας μεγάλης θετικής τιμής t-test

Για να προσδιορίσετε την περιοχή στα δεξιά του τεστ t, χρησιμοποιήστε έναν από τους τύπους:

1 — STIODENT.DIST (1, 69;34; TRUE)

STUDENT.DIST.PH(1,69;34)

Ολόκληρη η περιοχή κάτω από την καμπύλη πρέπει να είναι 100%, οπότε αφαιρώντας από το 1 το κλάσμα της περιοχής στα αριστερά της τιμής του t-test που επιστρέφει η συνάρτηση δίνει το κλάσμα της περιοχής στα δεξιά της τιμής του t-test. Ίσως σας φαίνεται προτιμότερο να λάβετε απευθείας το κλάσμα εμβαδού που σας ενδιαφέρει χρησιμοποιώντας τη συνάρτηση STUDENT.DIST.PH(), όπου PH σημαίνει τη δεξιά ουρά της κατανομής (Εικ. 22).

Ρύζι. 22. Περιοχή άλφα 5% για δοκιμή κατεύθυνσης

Η χρήση των συναρτήσεων STUDENT.DIST() ή STUDENT.DIST.PH() σημαίνει ότι έχετε επιλέξει μια κατευθυντική υπόθεση εργασίας. Η κατευθυντική υπόθεση εργασίας σε συνδυασμό με τη ρύθμιση της τιμής άλφα στο 5% σημαίνει ότι τοποθετείτε και το 5% στη δεξιά ουρά των κατανομών. Θα πρέπει να απορρίψετε τη μηδενική υπόθεση μόνο εάν η πιθανότητα της τιμής του t-test που λαμβάνετε είναι 5% ή μικρότερη. Οι υποθέσεις κατεύθυνσης γενικά καταλήγουν σε πιο ευαίσθητες στατιστικές δοκιμές (αυτή η μεγαλύτερη ευαισθησία ονομάζεται επίσης μεγαλύτερη στατιστική ισχύς).

Σε μια μη κατευθυνόμενη δοκιμή, η τιμή άλφα παραμένει στο ίδιο επίπεδο 5%, αλλά η κατανομή θα είναι διαφορετική. Επειδή πρέπει να επιτρέψετε δύο αποτελέσματα, η πιθανότητα ενός ψευδώς θετικού πρέπει να κατανεμηθεί μεταξύ των δύο ουρών της κατανομής. Είναι γενικά αποδεκτό να κατανέμεται εξίσου αυτή η πιθανότητα (Εικ. 23).

Χρησιμοποιώντας την ίδια τιμή t-test και τον ίδιο αριθμό βαθμών ελευθερίας όπως στο προηγούμενο παράδειγμα, χρησιμοποιήστε τον τύπο

STUDENT.DIST.2Х(1,69;34)

Χωρίς ιδιαίτερο λόγο, η συνάρτηση STUDENT.DIST.2X() επιστρέφει τον κωδικό σφάλματος #NUM, εάν της δοθεί μια αρνητική τιμή t-test ως πρώτο όρισμα.

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

Κεφάλαιο 7: Χρήση παλινδρόμησης για τον έλεγχο των διαφορών μεταξύ των μέσων της ομάδας

Οι μεταβλητές που εμφανίζονταν προηγουμένως κάτω από το όνομα μεταβλητές πρόβλεψης θα ονομάζονται μεταβλητές αποτελέσματος σε αυτό το κεφάλαιο και ο όρος μεταβλητές παράγοντα θα χρησιμοποιηθεί αντί του όρου μεταβλητές πρόβλεψης.

Η απλούστερη προσέγγιση για την κωδικοποίηση μιας ονομαστικής μεταβλητής είναι εικονική κωδικοποίηση(Εικ. 24).

Ρύζι. 24. Ανάλυση παλινδρόμησης βασισμένη σε εικονική κωδικοποίηση

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

• Ο αριθμός των στηλών που προορίζονται για νέα δεδομένα πρέπει να είναι ίσος με τον αριθμό των επιπέδων παραγόντων μείον
• Κάθε διάνυσμα αντιπροσωπεύει ένα επίπεδο παράγοντα.
• Τα θέματα σε ένα από τα επίπεδα, που είναι συχνά η ομάδα ελέγχου, κωδικοποιούνται με 0 σε όλα τα διανύσματα.

Ο τύπος στα κελιά F2:H6 =LINEST(A2:A22,C2:D22,;TRUE) επιστρέφει στατιστικά στοιχεία παλινδρόμησης. Για σύγκριση, στο Σχ. Το Σχήμα 24 δείχνει τα αποτελέσματα της παραδοσιακής ANOVA που επιστρέφονται από το εργαλείο. Μονόδρομος ANOVAπρόσθετα Ανάλυση Δεδομένων.

Κωδικοποίηση εφέ.Σε έναν άλλο τύπο κωδικοποίησης που ονομάζεται κωδικοποίηση εφέ,Ο μέσος όρος κάθε ομάδας συγκρίνεται με τον μέσο όρο των μέσων της ομάδας. Αυτή η πτυχή της κωδικοποίησης εφέ οφείλεται στη χρήση του -1 αντί του 0 ως κώδικα για την ομάδα, η οποία λαμβάνει τον ίδιο κώδικα σε όλα τα διανύσματα κώδικα (Εικόνα 25).

Ρύζι. 25. Κωδικοποίηση εφέ

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

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

Y ij = μ + α j + ε ij

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

Y ij = Y̅ + a j + e ij

Η ιδέα είναι ότι κάθε παρατήρηση Y ij μπορεί να θεωρηθεί ως το άθροισμα των ακόλουθων τριών συνιστωσών: ο γενικός μέσος όρος, μ; επίδραση της θεραπείας j, και j; τιμή e ij, η οποία αντιπροσωπεύει την απόκλιση του μεμονωμένου ποσοτικού δείκτη Y ij από τη συνδυασμένη τιμή του γενικού μέσου όρου και την επίδραση της j-ης επεξεργασίας (Εικ. 26). Ο στόχος της εξίσωσης παλινδρόμησης είναι να ελαχιστοποιήσει το άθροισμα των τετραγώνων των υπολειμμάτων.

Ρύζι. 26. Παρατηρήσεις αποσυντεθειμένες σε συνιστώσες ενός γενικού γραμμικού μοντέλου

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

Ρύζι. 27. Εργαλείο Αμφίδρομη ανάλυση διακύμανσης με επαναλήψεις του Πακέτο Ανάλυσης

Το εργαλείο ANOVA που χρησιμοποιείται σε αυτό το σχήμα είναι χρήσιμο επειδή επιστρέφει τον μέσο όρο και τη διακύμανση της μεταβλητής αποτελέσματος, καθώς και την τιμή μετρητή, για κάθε ομάδα που περιλαμβάνεται στη σχεδίαση. Στον πίνακα Ανάλυση διακύμανσηςεμφανίζει δύο παραμέτρους που δεν υπάρχουν στην έξοδο της έκδοσης ενός παράγοντα του εργαλείου ANOVA. Δώστε προσοχή στις πηγές παραλλαγής ΔείγμαΚαι Στήλεςστις γραμμές 27 και 28. Πηγή παραλλαγής Στήλεςαναφέρεται στο φύλο. Πηγή παραλλαγής Δείγμααναφέρεται σε οποιαδήποτε μεταβλητή της οποίας οι τιμές καταλαμβάνουν διαφορετικές γραμμές. Στο Σχ. 27 τιμές για την ομάδα KursLech1 βρίσκονται στις γραμμές 2-6, η ομάδα KursLech2 είναι στις γραμμές 7-11 και η ομάδα KursLechZ είναι στις γραμμές 12-16.

Το κύριο σημείο είναι ότι και οι δύο παράγοντες, Φύλο (στήλες ετικέτας στο κελί E28) και θεραπεία (δείγμα ετικέτας στο κελί E27), περιλαμβάνονται στον πίνακα ANOVA ως πηγές διακύμανσης. Τα μέσα για τους άνδρες είναι διαφορετικά από τα μέσα για τις γυναίκες, και αυτό δημιουργεί μια πηγή διαφοροποίησης. Τα μέσα για τις τρεις θεραπείες διαφέρουν επίσης, παρέχοντας μια άλλη πηγή διαφοροποίησης. Υπάρχει επίσης μια τρίτη πηγή, η αλληλεπίδραση, η οποία αναφέρεται στη συνδυαστική επίδραση των μεταβλητών Φύλο και Θεραπεία.

Κεφάλαιο 8. Ανάλυση Συνδιακύμανσης

Το Analysis of Covariance, ή ANCOVA (Analysis of Covariation), μειώνει την προκατάληψη και αυξάνει τη στατιστική ισχύ. Επιτρέψτε μου να σας υπενθυμίσω ότι ένας από τους τρόπους αξιολόγησης της αξιοπιστίας μιας εξίσωσης παλινδρόμησης είναι τα F-test:

F = MS Regression/MS Residual

όπου MS (Μέσο τετράγωνο) είναι το μέσο τετράγωνο και οι δείκτες Παλινδρόμησης και Υπολειπόμενος υποδεικνύουν την παλινδρόμηση και την υπολειπόμενη συνιστώσα, αντίστοιχα. Το MS Residual υπολογίζεται χρησιμοποιώντας τον τύπο:

MS Residual = SS Residual / df Residual

όπου SS (Άθροισμα τετραγώνων) είναι το άθροισμα των τετραγώνων και df είναι ο αριθμός των βαθμών ελευθερίας. Όταν προσθέτετε συνδιακύμανση σε μια εξίσωση παλινδρόμησης, κάποιο μέρος του συνολικού αθροίσματος τετραγώνων δεν περιλαμβάνεται στο SS ResiduaI αλλά στο SS Regression. Αυτό οδηγεί σε μείωση του SS Residua l, και ως εκ τούτου του MS Residual. Όσο μικρότερο είναι το MS Residual, τόσο μεγαλύτερο είναι το F-test και τόσο πιο πιθανό είναι να απορρίψετε τη μηδενική υπόθεση ότι δεν υπάρχει διαφορά μεταξύ των μέσων. Ως αποτέλεσμα, ανακατανέμετε τη μεταβλητότητα της μεταβλητής αποτελέσματος. Στην ANOVA, όταν η συνδιακύμανση δεν λαμβάνεται υπόψη, η μεταβλητότητα γίνεται σφάλμα. Αλλά στο ANCOVA, μέρος της μεταβλητότητας που αποδόθηκε προηγουμένως στον όρο σφάλματος εκχωρείται σε μια συμμεταβλητή και γίνεται μέρος της παλινδρόμησης SS.

Εξετάστε ένα παράδειγμα στο οποίο το ίδιο σύνολο δεδομένων αναλύεται πρώτα με ANOVA και μετά με ANCOVA (Εικόνα 28).

Ρύζι. 28. Η ανάλυση ANOVA δείχνει ότι τα αποτελέσματα που προκύπτουν από την εξίσωση παλινδρόμησης είναι αναξιόπιστα

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

Το εύρος A2:C21 περιέχει τα δεδομένα πηγής που μεταβιβάστηκαν στη συνάρτηση LINEST() για την εκτέλεση ανάλυσης χρησιμοποιώντας κωδικοποίηση εφέ. Τα αποτελέσματα της συνάρτησης LINEST() δίνονται στην περιοχή E2:F6, όπου το κελί E2 εμφανίζει τον συντελεστή παλινδρόμησης που σχετίζεται με το διάνυσμα κρούσης. Το κελί E8 περιέχει t-test = 0,93 και το κελί E9 ελέγχει την αξιοπιστία αυτού του t-test. Η τιμή που περιέχεται στο κελί Ε9 υποδεικνύει ότι η πιθανότητα να συναντήσετε τη διαφορά μεταξύ των μέσων της ομάδας που παρατηρήθηκε σε αυτό το πείραμα είναι 36% εάν οι μέσοι όροι της ομάδας είναι ίσοι στον πληθυσμό. Λίγοι θεωρούν αυτό το αποτέλεσμα στατιστικά σημαντικό.

Στο Σχ. Το Σχήμα 29 δείχνει τι συμβαίνει όταν προσθέτετε μια συμμεταβλητή στην ανάλυση. Σε αυτήν την περίπτωση, πρόσθεσα την ηλικία κάθε θέματος στο σύνολο δεδομένων. Ο συντελεστής προσδιορισμού R 2 για την εξίσωση παλινδρόμησης που χρησιμοποιεί τη συμμεταβλητή είναι 0,80 (κελί F4). Η τιμή R 2 στην περιοχή F15:G19, στην οποία αντιγράφω τα αποτελέσματα ANOVA που ελήφθησαν χωρίς τη συμμεταβλητή, είναι μόνο 0,05 (κελί F17). Επομένως, μια εξίσωση παλινδρόμησης που περιλαμβάνει τη συμμεταβλητή προβλέπει τιμές για τη μεταβλητή Γνωσιακής Βαθμολογίας με πολύ μεγαλύτερη ακρίβεια από τη χρήση του διανύσματος Επιπτώσεων μόνο. Για το ANCOVA, η πιθανότητα να ληφθεί τυχαία η τιμή δοκιμής F που εμφανίζεται στο κελί F5 είναι μικρότερη από 0,01%.

Ρύζι. 29. Η ANCOVA επαναφέρει μια εντελώς διαφορετική εικόνα

Η επεξεργασία στατιστικών δεδομένων μπορεί επίσης να πραγματοποιηθεί χρησιμοποιώντας ένα πρόσθετο ΠΑΚΕΤΟ ΑΝΑΛΥΣΗΣ(Εικ. 62).

Από τα προτεινόμενα είδη, επιλέξτε το στοιχείο " ΟΠΙΣΘΟΔΡΟΜΗΣΗ" και κάντε κλικ σε αυτό με το αριστερό κουμπί του ποντικιού. Στη συνέχεια, κάντε κλικ στο OK.

Θα εμφανιστεί ένα παράθυρο όπως φαίνεται στο Σχ. 63.

Εργαλείο ανάλυσης " ΟΠΙΣΘΟΔΡΟΜΗΣΗ» χρησιμοποιείται για την προσαρμογή ενός γραφήματος σε ένα σύνολο παρατηρήσεων χρησιμοποιώντας τη μέθοδο των ελαχίστων τετραγώνων. Η παλινδρόμηση χρησιμοποιείται για την ανάλυση της επίδρασης σε μια μεμονωμένη εξαρτημένη μεταβλητή των τιμών μιας ή περισσότερων ανεξάρτητων μεταβλητών. Για παράδειγμα, διάφοροι παράγοντες επηρεάζουν την αθλητική απόδοση ενός αθλητή, όπως η ηλικία, το ύψος και το βάρος. Είναι δυνατόν να υπολογιστεί ο βαθμός στον οποίο καθένας από αυτούς τους τρεις παράγοντες επηρεάζει την απόδοση ενός αθλητή και στη συνέχεια να χρησιμοποιήσει αυτά τα δεδομένα για να προβλέψει την απόδοση ενός άλλου αθλητή.

Το εργαλείο παλινδρόμησης χρησιμοποιεί τη συνάρτηση LINEST.

Πλαίσιο διαλόγου REGRESSION

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

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

Σταθερά - μηδέν Επιλέξτε το πλαίσιο ελέγχου για να αναγκάσετε τη γραμμή παλινδρόμησης να περάσει από την αρχή.

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

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

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

Υπολείμματα Επιλέξτε το πλαίσιο ελέγχου για να συμπεριλάβετε υπολείμματα στον πίνακα εξόδου.

Τυποποιημένα υπολείμματα Επιλέξτε το πλαίσιο ελέγχου για να συμπεριλάβετε τυποποιημένα υπολείμματα στον πίνακα εξόδου.

Υπολειπόμενη γραφική παράσταση Επιλέξτε το πλαίσιο ελέγχου για να σχεδιάσετε τα υπολείμματα για κάθε ανεξάρτητη μεταβλητή.

Fit Plot Επιλέξτε το πλαίσιο ελέγχου για να σχεδιάσετε τις προβλεπόμενες έναντι των παρατηρούμενων τιμών.

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

Λειτουργία LINEST

Για να πραγματοποιήσετε υπολογισμούς, επιλέξτε με τον κέρσορα το κελί στο οποίο θέλουμε να εμφανιστεί η μέση τιμή και πατήστε το πλήκτρο = στο πληκτρολόγιο. Στη συνέχεια, στο πεδίο Όνομα, υποδείξτε την επιθυμητή λειτουργία, για παράδειγμα ΜΕΣΟΣ(Εικ. 22).

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

Η εξίσωση για μια ευθεία γραμμή είναι:

y=m 1 x 1 +m 2 x 2 +…+b (σε περίπτωση πολλών περιοχών τιμών x),

όπου η εξαρτημένη τιμή y είναι συνάρτηση της ανεξάρτητης τιμής x, οι τιμές m είναι οι συντελεστές που αντιστοιχούν σε κάθε ανεξάρτητη μεταβλητή x και το b είναι μια σταθερά. Σημειώστε ότι τα y, x και m μπορούν να είναι διανύσματα. Λειτουργία LINESTεπιστρέφει πίνακα (mn;mn-1;…;m 1 ;b). LINESTμπορεί επίσης να επιστρέψει πρόσθετα στατιστικά στοιχεία παλινδρόμησης.

LINEST(γνωστές_τιμές_y; γνωστές_τιμές_x; const; στατιστικά)

Known_y_values ​​- ένα σύνολο y-τιμών που είναι ήδη γνωστές για τη σχέση y=mx+b.

Εάν ο πίνακας Known_y_values ​​έχει μία στήλη, τότε κάθε στήλη στον πίνακα known_x_values ​​αντιμετωπίζεται ως ξεχωριστή μεταβλητή.

Εάν ο πίνακας Known_y_values ​​έχει μία σειρά, τότε κάθε σειρά στον πίνακα known_x_values ​​αντιμετωπίζεται ως ξεχωριστή μεταβλητή.

Το Known_x-values ​​είναι ένα προαιρετικό σύνολο τιμών x που είναι ήδη γνωστά για τη σχέση y=mx+b.

Ο πίνακας Known_x_values ​​μπορεί να περιέχει ένα ή περισσότερα σύνολα μεταβλητών. Εάν χρησιμοποιείται μόνο μία μεταβλητή, τότε οι πίνακες Known_y_values ​​και known_x_values ​​μπορούν να έχουν οποιοδήποτε σχήμα - αρκεί να έχουν την ίδια διάσταση. Εάν χρησιμοποιούνται περισσότερες από μία μεταβλητές, τότε το Known_y_values ​​πρέπει να είναι ένα διάνυσμα (δηλαδή, ένα διάστημα ύψους μίας γραμμής ή πλάτους μίας στήλης).

Εάν παραλειφθεί ο πίνακας_γνωστών_χ_τιμών, τότε ο πίνακας (1;2;3;...) θεωρείται ότι έχει το ίδιο μέγεθος με τον πίνακα_γνωστές_τιμές_y.

Το Const είναι μια δυαδική τιμή που καθορίζει εάν η σταθερά b απαιτείται να είναι ίση με 0.

Εάν το όρισμα "const" είναι TRUE ή παραλειφθεί, τότε η σταθερά b αξιολογείται ως συνήθως.

Εάν το όρισμα «const» είναι FALSE, τότε η τιμή του b ορίζεται στο 0 και οι τιμές του m επιλέγονται έτσι ώστε να ικανοποιείται η σχέση y=mx.

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

Εάν τα στατιστικά στοιχεία είναι TRUE, το LINEST επιστρέφει επιπλέον στατιστικά στοιχεία παλινδρόμησης. Ο πίνακας που επιστρέφεται θα μοιάζει με αυτό: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

Εάν τα στατιστικά στοιχεία είναι FALSE ή παραλείπονται, το LINEST επιστρέφει μόνο τους συντελεστές m και τη σταθερά b.

Πρόσθετα στατιστικά παλινδρόμησης (Πίνακας 17)

Μέγεθος	Περιγραφή
se1,se2,...,sen	Τυπικές τιμές σφάλματος για τους συντελεστές m1,m2,...,mn.
seb	Τυπική τιμή σφάλματος για τη σταθερά b (seb = #N/A εάν το const είναι FALSE).
r2	Συντελεστής ντετερμινισμού. Συγκρίνονται οι πραγματικές τιμές του y και οι τιμές που λαμβάνονται από την εξίσωση της γραμμής. Με βάση τα αποτελέσματα σύγκρισης, υπολογίζεται ο συντελεστής ντετερμινισμού, κανονικοποιημένος από 0 σε 1. Εάν είναι ίσος με 1, τότε υπάρχει πλήρης συσχέτιση με το μοντέλο, δηλ. δεν υπάρχει διαφορά μεταξύ των πραγματικών και των εκτιμώμενων τιμών του υ. Στην αντίθετη περίπτωση, εάν ο συντελεστής προσδιορισμού είναι 0, δεν έχει νόημα να χρησιμοποιήσουμε την εξίσωση παλινδρόμησης για να προβλέψουμε τις τιμές του y. Για περισσότερες πληροφορίες σχετικά με τον τρόπο υπολογισμού του r2, ανατρέξτε στις «Σημειώσεις» στο τέλος αυτής της ενότητας.
sey	Τυπικό σφάλμα για την εκτίμηση του y.
φά	F-statistic ή F-παρατηρούμενη τιμή. Η στατιστική F χρησιμοποιείται για να προσδιορίσει εάν μια παρατηρούμενη σχέση μεταξύ μιας εξαρτημένης και μιας ανεξάρτητης μεταβλητής οφείλεται στην τύχη.
df	Βαθμοί ελευθερίας. Οι βαθμοί ελευθερίας είναι χρήσιμοι για την εύρεση κρίσιμων τιμών F σε έναν στατιστικό πίνακα. Για να προσδιορίσετε το επίπεδο εμπιστοσύνης του μοντέλου, συγκρίνετε τις τιμές στον πίνακα με τη στατιστική F που επιστρέφεται από τη συνάρτηση LINEST. Για περισσότερες πληροφορίες σχετικά με τον υπολογισμό του df, δείτε τις «Σημειώσεις» στο τέλος αυτής της ενότητας. Το Παράδειγμα 4 παρακάτω δείχνει τη χρήση των τιμών F και df.
ssreg	Παλινδρόμηση τετραγώνων.
ssresid	Υπολειπόμενο άθροισμα τετραγώνων. Για περισσότερες πληροφορίες σχετικά με τον υπολογισμό του ssreg και του ssresid, δείτε τις «Σημειώσεις» στο τέλος αυτής της ενότητας.

Το παρακάτω σχήμα δείχνει τη σειρά με την οποία επιστρέφονται πρόσθετα στατιστικά στοιχεία παλινδρόμησης (Εικόνα 64).

Σημειώσεις:

Οποιαδήποτε ευθεία μπορεί να περιγραφεί από την κλίση και την τομή της με τον άξονα y:

Κλίση (m): Για να προσδιορίσετε την κλίση μιας ευθείας, που συνήθως συμβολίζεται με m, πρέπει να λάβετε δύο σημεία στην ευθεία (x 1 ,y 1) και (x 2 ,y 2). η κλίση θα είναι ίση με (y 2 -y 1)/(x 2 -x 1).

Y-τομή (b): Η τομή y μιας ευθείας, που συνήθως συμβολίζεται με b, είναι η τιμή y για το σημείο στο οποίο η ευθεία τέμνει τον άξονα y.

Η εξίσωση της ευθείας είναι y=mx+b. Εάν οι τιμές των 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ου βαθμού) της ακόλουθης μορφής:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

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