Συντελεστής προσδιορισμού στο Excel (Excel). Υπολογισμός του συντελεστή προσδιορισμού στο Microsoft Excel

Για στατιστικά μοντέλα, σε πολλές περιπτώσεις είναι απαραίτητο να προσδιοριστεί η ακρίβεια της πρόβλεψης. Αυτό γίνεται χρησιμοποιώντας ειδικούς υπολογισμούς στο Microsoft Excel και θα χρησιμοποιηθεί ο συντελεστής προσδιορισμού. Δηλώνεται ως R ^ 2.

Τα στατιστικά μοντέλα μπορούν να χωριστούν σε ποιοτικά επίπεδα ανάλογα με τον συντελεστή. Από το 0,8 έως το 1 είναι μοντέλα καλής ποιότητας, τα μοντέλα επαρκούς ποιότητας έχουν ένα επίπεδο από 0,5 έως 0,8 και η κακή ποιότητα κυμαίνεται από 0 έως 0,5.

Μέθοδος για τον προσδιορισμό της ακρίβειας χρησιμοποιώντας τη λειτουργία KVPIRSON

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

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

Μετά από αυτό θα ανοίξει ένα ειδικό παράθυρο. Στην κατηγορία που θέλετε να επιλέξετε "Στατιστικά" και επιλέξτε KVPIRSON. Αυτή η λειτουργία σας επιτρέπει να προσδιορίσετε τον συντελεστή συσχέτισης σε σχέση με τη λειτουργία Pearson, αντίστοιχα, την τετραγωνική τιμή του συντελεστή συσχέτισης \u003d συντελεστή προσδιορισμού.

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

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

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

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

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

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

Μπορείτε να το βρείτε στην ενότητα "Δεδομένα", μετά από την οποία κάνουμε κλικ στην "Ανάλυση δεδομένων" στη δεξιά πλευρά της οθόνης.

Αφού το ανοίξετε, επιλέξτε "Regression" από τη λίστα και επιβεβαιώστε τη δράση.

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

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

Η μέθοδος προσδιορισμού του συντελεστή προσδιορισμού της γραμμής τάσης

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

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

Για να δείξετε τον συντελεστή προσδιορισμού, πρέπει να κάνετε δεξί κλικ στη μαύρη καμπύλη και να επιλέξετε στη λίστα τη "Μορφή τάσης τάσης".

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

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

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

Ως αποτέλεσμα, μπορείτε να δείτε την αλλαγή στη γραμμή τάσεων και τον αριθμό εμπιστοσύνης.

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

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

28 Οκτ

Καλησπέρα, αγαπητοί αναγνώστες blog! Σήμερα θα μιλήσουμε για μη γραμμικές παλινδρομήσεις. Η λύση γραμμικών παλινδρομήσεων μπορεί να βρεθεί στο LINK.

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

Οι κύριοι τύποι μη γραμμικών παλινδρομήσεων είναι:

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

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

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

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

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

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

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

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

Θα σχηματίσουμε μια μήτρα Α   σύστημα που αποτελείται από συντελεστές για άγνωστες στις αριστερές πλευρές των εξισώσεων. Τοποθετήστε το στο κελί A22 και ονομάστε το " A \u003d". Ακολουθούμε το σύστημα των εξισώσεων που επιλέξαμε να λύσουμε την παλινδρόμηση.

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

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

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

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

Ο υπολογισμός των συντελεστών της εξίσωσης σύμφωνα με τον Kramer θα πραγματοποιηθεί στα κύτταρα απέναντι από τους αντίστοιχους καθοριστικούς παράγοντες σύμφωνα με τον τύπο: α  (στο κελί M22) - "\u003d K22 / K21". β  (στο κελί Μ23) - "\u003d Κ23 / Κ21". με(στο κελί M24) - "\u003d K24 / K21".

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

y \u003d -0.074χ 2 + 2.151χ + 6.523

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

Για να υπολογίσετε, προσθέστε μια πρόσθετη στήλη J στον πίνακα (ας το ονομάσουμε y *). Ο υπολογισμός θα είναι ο ακόλουθος (σύμφωνα με την εξίσωση παλινδρόμησης) - "\u003d $ Μ $ 22 * \u200b\u200bΒ2 * Β2 + $ Μ $ 23 * Β2 + $ Μ $ 24."Τοποθετήστε το στο κελί J2. Παραμένει να σύρετε τον δείκτη αυτόματης συμπλήρωσης στο κελί J16.

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

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

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

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

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

Το τελευταίο βήμα είναι να υπολογίσουμε το σχετικό σφάλμα. Προσθέστε μια στήλη και προσθέστε τον τύπο: "\u003d ABS ((C2-J2) / C2), μονάδα ABS, απόλυτη τιμή. Τραβήξτε τον δείκτη προς τα κάτω και στο κελί M18 εμφανίζουμε τη μέση τιμή (AVERAGE), ορίστε τη μορφή ποσοστού στα κελιά. Το αποτέλεσμα - 7.79% είναι εντός των αποδεκτών τιμών σφάλματος<8…10%. Значит вычисления достаточно точны.

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

Το αρχείο με ένα παράδειγμα επισυνάπτεται - LINK!

  Κατηγορίες:   / από 10.28.2017

Ένας από τους δείκτες που περιγράφουν την ποιότητα του κατασκευαζόμενου μοντέλου στις στατιστικές είναι ο συντελεστής προσδιορισμού (R ^ 2), ο οποίος ονομάζεται επίσης και η τιμή εμπιστοσύνης προσέγγισης. Με αυτό, μπορείτε να καθορίσετε το επίπεδο ακρίβειας της πρόβλεψης. Ας μάθουμε πώς μπορείτε να υπολογίσετε αυτόν τον δείκτη χρησιμοποιώντας διάφορα εργαλεία του Excel.

Ανάλογα με το επίπεδο του συντελεστή προσδιορισμού, είναι συνηθισμένο να διαιρούνται τα μοντέλα σε τρεις ομάδες:

• 0.8 - 1 - μοντέλο καλής ποιότητας?
• 0,5 - 0,8 - μοντέλο αποδεκτής ποιότητας.
• 0 - 0,5 - μοντέλο κακής ποιότητας.

Στην τελευταία περίπτωση, η ποιότητα του μοντέλου δείχνει την αδυναμία χρήσης του για πρόβλεψη.

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

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

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

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

Αλλά η παραπάνω επιλογή για τον υπολογισμό της επιθυμητής τιμής μπορεί να εφαρμοστεί μόνο στις γραμμικές λειτουργίες. Τι πρέπει να κάνετε για να το υπολογίσετε σε μια μη γραμμική λειτουργία; Στο Excel υπάρχει μια τέτοια ευκαιρία. Μπορεί να γίνει με το εργαλείο. "Παλινδρόμηση"που αποτελεί μέρος της δέσμης "Ανάλυση δεδομένων".

1. Αλλά πριν χρησιμοποιήσετε το καθορισμένο εργαλείο, πρέπει να το ενεργοποιήσετε μόνοι σας "Πακέτο ανάλυσης", το οποίο είναι απενεργοποιημένο από προεπιλογή στο Excel. Μετακίνηση στην καρτέλα Αρχείοκαι στη συνέχεια πηγαίνετε στο "Παράμετροι".



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



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



4. Πακέτο εργαλείων "Ανάλυση δεδομένων"  στην τρέχουσα περίπτωση του Excel θα ενεργοποιηθεί. Η πρόσβαση σε αυτό βρίσκεται στην κορδέλα στην καρτέλα "Δεδομένα". Μεταβαίνουμε στην καθορισμένη καρτέλα και πατάμε το κουμπί "Ανάλυση δεδομένων"  στην ομάδα ρυθμίσεων "Ανάλυση".



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



6. Στη συνέχεια ανοίγει το παράθυρο εργαλείου "Παλινδρόμηση". Το πρώτο μπλοκ των ρυθμίσεων είναι "Δεδομένα εισόδου". Εδώ σε δύο πεδία πρέπει να καθορίσετε τις διευθύνσεις των περιοχών στις οποίες βρίσκονται οι τιμές του επιχειρήματος και της λειτουργίας. Τοποθετήστε το δρομέα στο πεδίο "Διάστημα εισόδου Y"  και επιλέξτε τα περιεχόμενα της στήλης στο φύλλο "Υ". Μετά την εμφάνιση της διεύθυνσης πίνακα στο παράθυρο "Παλινδρόμηση"βάλτε τον κέρσορα στο πεδίο "Διάστημα εισόδου Y"  και να επιλέξετε τα στοιχεία στήλης ακριβώς με τον ίδιο τρόπο "Χ".

   Περί παραμέτρων "Ετικέτα"  και "Constant zero"  μην βάζετε σημαίες. Το πλαίσιο ελέγχου μπορεί να ρυθμιστεί δίπλα στην παράμετρο. "Επίπεδο αξιοπιστίας"  και στο αντίθετο πεδίο, υποδείξτε την επιθυμητή τιμή του αντίστοιχου δείκτη (95% από προεπιλογή).

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

   • Η περιοχή στο τρέχον φύλλο.
   • Ένα άλλο φύλλο?
   • Ένα άλλο βιβλίο (νέο αρχείο).

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

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



7. Το πρόγραμμα υπολογίζεται βάσει δεδομένων που έχουν καταχωρηθεί προηγουμένως και εμφανίζει το αποτέλεσμα στο καθορισμένο εύρος. Όπως μπορείτε να δείτε, αυτό το εργαλείο εμφανίζει ένα αρκετά μεγάλο αριθμό αποτελεσμάτων σε διάφορες παραμέτρους σε ένα φύλλο εργασίας. Αλλά στο πλαίσιο του τρέχοντος μαθήματος, μας ενδιαφέρει ο δείκτης "R-τετράγωνο". Σε αυτή την περίπτωση, είναι ίση με 0,947664, η οποία χαρακτηρίζει το επιλεγμένο μοντέλο ως μοντέλο καλής ποιότητας.

Μέθοδος 3: συντελεστής προσδιορισμού της γραμμής τάσης

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

1. Έχουμε ένα γράφημα βασισμένο σε έναν πίνακα των επιχειρημάτων και τιμών λειτουργίας, που χρησιμοποιήθηκε για το προηγούμενο παράδειγμα. Θα δημιουργήσουμε μια γραμμή τάσης σε αυτό. Κάνουμε κλικ σε οποιοδήποτε σημείο της περιοχής κατασκευής στην οποία βρίσκεται το διάγραμμα, με το αριστερό πλήκτρο του ποντικιού. Ταυτόχρονα, εμφανίζεται μια πρόσθετη ομάδα καρτελών στην κορδέλα - "Εργασία με διαγράμματα". Μεταβείτε στην καρτέλα "Διάταξη". Κάντε κλικ στο κουμπί "Γραμμή τάσης"που βρίσκεται στο μπλοκ εργαλείων "Ανάλυση". Εμφανίζεται ένα μενού με επιλογή του τύπου της γραμμής τάσης. Σταματάμε την επιλογή του τύπου που αντιστοιχεί σε μια συγκεκριμένη εργασία. Ας επιλέξουμε μια επιλογή για το παράδειγμά μας Εκθετική προσέγγιση.



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



3. Τώρα το καθήκον μας είναι να δείξουμε τον ίδιο τον συντελεστή προσδιορισμού. Κάντε δεξί κλικ στη γραμμή τάσεων. Το μενού περιβάλλοντος είναι ενεργοποιημένο. Σταματάμε την επιλογή σε αυτό στο "Η μορφή της γραμμής τάσης ...".

   

   Για να εκτελέσετε τη μετάβαση στο παράθυρο μορφής γραμμής τάσης, μπορείτε να εκτελέσετε μια εναλλακτική ενέργεια. Επιλέξτε τη γραμμή τάσεων κάνοντας κλικ σε αυτό με το αριστερό πλήκτρο του ποντικιού. Μετακίνηση στην καρτέλα "Διάταξη". Κάντε κλικ στο κουμπί "Γραμμή τάσης"  σε μπλοκ "Ανάλυση". Στη λίστα που ανοίγει, κάντε κλικ στο τελευταίο στοιχείο της λίστας ενεργειών - "Πρόσθετες παράμετροι της γραμμής τάσης ...".



4. Μετά από οποιαδήποτε από τις παραπάνω δύο ενέργειες, ένα παράθυρο μορφοποίησης εκκινείται στο οποίο μπορείτε να κάνετε επιπλέον ρυθμίσεις. Συγκεκριμένα, για να ολοκληρώσουμε το έργο μας, είναι απαραίτητο να ελέγξετε το πλαίσιο δίπλα "Βάλτε την τιμή εμπιστοσύνης προσέγγισης (R ^ 2) στο διάγραμμα". Βρίσκεται στο κάτω μέρος του παραθύρου. Δηλαδή, με αυτόν τον τρόπο δίνουμε τη δυνατότητα να εμφανίζουμε τον συντελεστή προσδιορισμού στην περιοχή κατασκευής. Στη συνέχεια, μην ξεχάσετε να κάνετε κλικ στο κουμπί Κλείσιμο  στο κάτω μέρος του τρέχοντος παραθύρου.



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



6. Απόλυτα ακριβώς με αυτόν τον τρόπο μπορείτε να ρυθμίσετε την εμφάνιση του συντελεστή προσδιορισμού για οποιοδήποτε άλλο τύπο γραμμής τάσης. Μπορείτε να αλλάξετε τον τύπο της γραμμής τάσης κάνοντας μια μετάβαση από το κουμπί της κορδέλας ή του μενού περιβάλλοντος στο παράθυρο των παραμέτρων της, όπως φαίνεται παραπάνω. Στη συνέχεια, στο ίδιο το παράθυρο στην ομάδα "Οικοδόμηση μιας γραμμής τάσης"  Μπορείτε να μεταβείτε σε άλλο τύπο. Την ίδια στιγμή, μην ξεχάσετε να το ελέγξετε γύρω από το σημείο "Βάλτε την τιμή εμπιστοσύνης προσέγγισης στο διάγραμμα"  το πλαίσιο ελέγχου ελέγχθηκε. Αφού ολοκληρώσετε τα παραπάνω βήματα, κάντε κλικ στο κουμπί Κλείσιμο  στην κάτω δεξιά γωνία του παραθύρου.



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



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

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

   

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

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

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

Στο Excel 2007, για να ενεργοποιήσετε το πακέτο ανάλυσης, κάντε κλικ στο κουμπί go to block Επιλογές του Excelκάνοντας κλικ στο πλήκτρο επάνω αριστερά και στη συνέχεια στο κουμπί " Επιλογές του Excel»Στο κάτω μέρος του παραθύρου:

Για να δημιουργήσετε ένα μοντέλο παλινδρόμησης, επιλέξτε Υπηρεσία \\ Ανάλυση δεδομένων \\ Οπισθοδρόμηση. (Στο Excel 2007, αυτή η λειτουργία βρίσκεται στο μπλοκ Ανάλυση δεδομένων / δεδομένων / παλινδρόμηση) Εμφανίζεται ένα παράθυρο διαλόγου που πρέπει να συμπληρωθεί:

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

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

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

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

5) Υπογράψτε Συνεχής μηδέν  ενεργοποιείται εάν είναι απαραίτητο να δημιουργηθεί μια εξίσωση στην οποία μια ελεύθερη μεταβλητή.

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

7) Αποκλεισμός Τα απομεινάρια  σας επιτρέπει να συμπεριλάβετε την απόσυρση υπολειμμάτων και την κατασκευή των προγραμμάτων τους.

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

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

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

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

Κανονικοποιημένο R-η κλίμακα υπολογίζεται από τον τύπο   (χρησιμοποιείται για πολλαπλή παλινδρόμηση).

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

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

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

Παράμετρος df  ισούται με m  (αριθμός συνόλων παραγόντων x);

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

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

Στατιστικά στοιχεία F  καθορίζεται από τον τύπο.

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

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

Παράμετρος df  είναι ίση με?

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

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

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

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

P-value ¾ είναι η τιμή των επιπέδων σημασίας που αντιστοιχεί στο υπολογιζόμενο tστατιστικά στοιχεία. Καθορίζεται από τη λειτουργία STUDUSTER ( t-στατιστικά στοιχεία. ) Αν PΕάν η τιμή υπερβαίνει, τότε η αντίστοιχη μεταβλητή είναι στατιστικά ασήμαντη και μπορεί να αποκλειστεί από το μοντέλο.

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

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

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

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

Για να το κάνετε αυτό, πρέπει:

1. Ανοίξτε το πρόγραμμα Excel

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

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

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

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

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

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

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

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

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