Πού εφαρμόζεται η μέθοδος των ελαχίστων τετραγώνων; Finger Math: Least τετράγωνα Ελάχιστα τετράγωνα για μια συνάρτηση

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

Κατασκευάζοντας ένα πολυώνυμο παρεμβολής n-βαθμών που περνά απευθείας σε όλα τα σημείαδεδομένης σειράς δεδομένων. Σε αυτή την περίπτωση, η συνάρτηση προσέγγισης αναπαρίσταται ως: ένα πολυώνυμο παρεμβολής στη μορφή Lagrange ή ένα πολυώνυμο παρεμβολής στη μορφή Newton.

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

Μέθοδος ελάχιστου τετραγώνου(στην αγγλική βιβλιογραφία Ordinary Least Squares, OLS) είναι μια μαθηματική μέθοδος που βασίζεται στον ορισμό μιας προσεγγιστικής συνάρτησης, η οποία είναι χτισμένη στην πλησιέστερη εγγύτητα σε σημεία από μια δεδομένη σειρά πειραματικών δεδομένων. Η εγγύτητα της αρχικής και της προσεγγιστικής συνάρτησης F(x) προσδιορίζεται από ένα αριθμητικό μέτρο, δηλαδή: το άθροισμα των τετραγωνικών αποκλίσεων των πειραματικών δεδομένων από την προσεγγιστική καμπύλη F(x) πρέπει να είναι το μικρότερο.

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

Χρησιμοποιείται η μέθοδος των ελαχίστων τετραγώνων:

Για την επίλυση υπερκαθορισμένων συστημάτων εξισώσεων όταν ο αριθμός των εξισώσεων υπερβαίνει τον αριθμό των αγνώστων.

Να αναζητήσει λύση στην περίπτωση συνηθισμένων (όχι υπερκαθορισμένων) μη γραμμικών συστημάτων εξισώσεων.

Για την προσέγγιση των τιμών των σημείων με κάποια κατά προσέγγιση συνάρτηση.

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

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

Καθορισμένη σειρά πειραματικών δεδομένων σε κομβικά σημεία .

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

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

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

∙ Αν ο βαθμός της προσεγγιστικής συνάρτησης είναι m=1, τότε προσεγγίζουμε τη συνάρτηση πίνακα με ευθεία γραμμή (γραμμική παλινδρόμηση).

∙ Αν ο βαθμός της προσεγγιστικής συνάρτησης είναι m=2, τότε προσεγγίζουμε τη συνάρτηση πίνακα με τετραγωνική παραβολή (τετραγωνική προσέγγιση).

∙ Αν ο βαθμός της προσεγγιστικής συνάρτησης είναι m=3, τότε προσεγγίζουμε τη συνάρτηση πίνακα με κυβική παραβολή (κυβική προσέγγιση).

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

- άγνωστοι συντελεστές του κατά προσέγγιση πολυωνύμου βαθμού m.

Ο αριθμός των καθορισμένων τιμών πίνακα.

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

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

Αυτό το σύστημα γραμμικών αλγεβρικών παραστάσεων μπορεί να ξαναγραφτεί σε μορφή πίνακα:

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

Προσέγγιση των αρχικών δεδομένων με γραμμική εξάρτηση

(γραμμικής παλινδρόμησης)

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

Συντεταγμένες κομβικών σημείων του πίνακα.

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

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

Ας μετατρέψουμε το γραμμικό σύστημα εξισώσεων που προκύπτει.

Λύνουμε το προκύπτον σύστημα γραμμικών εξισώσεων. Οι συντελεστές της προσεγγιστικής συνάρτησης στην αναλυτική μορφή προσδιορίζονται ως εξής (μέθοδος Cramer):

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

Αλγόριθμος για την εφαρμογή της μεθόδου των ελαχίστων τετραγώνων

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

Δίνεται μια σειρά πειραματικών δεδομένων με τον αριθμό των μετρήσεων N

Δίνεται ο βαθμός του προσεγγιστικού πολυωνύμου (m).

2. Αλγόριθμος υπολογισμού:

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

Συντελεστές του συστήματος εξισώσεων (αριστερή πλευρά της εξίσωσης)

- δείκτης του αριθμού στήλης του τετραγωνικού πίνακα του συστήματος εξισώσεων

Ελεύθερα μέλη του συστήματος γραμμικών εξισώσεων (δεξιά πλευρά της εξίσωσης)

- δείκτης του αριθμού σειράς του τετραγωνικού πίνακα του συστήματος εξισώσεων

2.2. Σχηματισμός συστήματος γραμμικών εξισώσεων με διάσταση .

2.3. Λύση συστήματος γραμμικών εξισώσεων για τον προσδιορισμό των άγνωστων συντελεστών του προσεγγιστικού πολυωνύμου βαθμού m.

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

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

Προσέγγιση με άλλες συναρτήσεις

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

Προσέγγιση καταγραφής

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

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

Δήλωση του προβλήματος σε συγκεκριμένο παράδειγμα

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

Έτσι, έστω X η περιοχή πώλησης ενός παντοπωλείου, μετρημένη σε τετραγωνικά μέτρα, και Y είναι ο ετήσιος κύκλος εργασιών, που ορίζεται σε εκατομμύρια ρούβλια.

Απαιτείται να γίνει πρόβλεψη για το τι τζίρο (Υ) θα έχει το κατάστημα αν έχει τον έναν ή τον άλλο χώρο λιανικής. Προφανώς, η συνάρτηση Y = f (X) αυξάνεται, αφού η υπεραγορά πουλάει περισσότερα αγαθά από το περίπτερο.

Λίγα λόγια για την ορθότητα των αρχικών δεδομένων που χρησιμοποιούνται για την πρόβλεψη

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

Σύμφωνα με τις μαθηματικές στατιστικές, τα αποτελέσματα θα είναι λίγο πολύ σωστά εάν εξεταστούν τα δεδομένα για τουλάχιστον 5-6 αντικείμενα. Επίσης, δεν μπορούν να χρησιμοποιηθούν "ανώμαλα" αποτελέσματα. Συγκεκριμένα, μια ελίτ μικρή μπουτίκ μπορεί να έχει τζίρο πολλαπλάσιο από τον τζίρο μεγάλων καταστημάτων της κατηγορίας «masmarket».

Η ουσία της μεθόδου

Τα δεδομένα του πίνακα μπορούν να εμφανιστούν στο καρτεσιανό επίπεδο ως σημεία M 1 (x 1, y 1), ... M n (x n, y n). Τώρα η λύση του προβλήματος θα περιοριστεί στην επιλογή μιας προσεγγιστικής συνάρτησης y = f (x), η οποία έχει μια γραφική παράσταση που περνά όσο το δυνατόν πιο κοντά στα σημεία M 1, M 2, .. M n .

Φυσικά, μπορείτε να χρησιμοποιήσετε ένα πολυώνυμο υψηλού βαθμού, αλλά αυτή η επιλογή δεν είναι μόνο δύσκολη στην εφαρμογή, αλλά απλά λανθασμένη, καθώς δεν θα αντικατοπτρίζει την κύρια τάση που πρέπει να εντοπιστεί. Η πιο λογική λύση είναι να αναζητήσετε μια ευθεία γραμμή y = ax + b, η οποία προσεγγίζει καλύτερα τα πειραματικά δεδομένα, και πιο συγκεκριμένα, τους συντελεστές - a και b.

Βαθμολογία ακρίβειας

Για κάθε προσέγγιση, ιδιαίτερη σημασία έχει η εκτίμηση της ακρίβειάς της. Σημειώστε με e i τη διαφορά (απόκλιση) μεταξύ των λειτουργικών και πειραματικών τιμών για το σημείο x i , δηλαδή e i = y i - f (x i).

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

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

Μέθοδος ελάχιστου τετραγώνου

Στο Excel, όπως γνωρίζετε, υπάρχει μια ενσωματωμένη λειτουργία αυτόματης άθροισης που σας επιτρέπει να υπολογίσετε τις τιμές όλων των τιμών που βρίσκονται στην επιλεγμένη περιοχή. Έτσι, τίποτα δεν θα μας εμποδίσει να υπολογίσουμε την τιμή της παράστασης (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

Στη μαθηματική σημειογραφία, αυτό μοιάζει με:

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

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

Αυτό απαιτεί την εξίσωση με μηδέν μερικών παραγώγων σε σχέση με τις νέες μεταβλητές a και b και την επίλυση ενός αρχέγονου συστήματος που αποτελείται από δύο εξισώσεις με 2 άγνωστα της μορφής:

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

Λύνοντάς το, για παράδειγμα, με τη μέθοδο του Cramer, λαμβάνουμε ένα ακίνητο σημείο με ορισμένους συντελεστές a * και b * . Αυτό είναι το ελάχιστο, δηλαδή για να προβλέψουμε τι τζίρο θα έχει το κατάστημα για μια συγκεκριμένη περιοχή, είναι κατάλληλη η ευθεία γραμμή y = a * x + b *, η οποία είναι ένα μοντέλο παλινδρόμησης για το εν λόγω παράδειγμα. Φυσικά, δεν θα σας επιτρέψει να βρείτε το ακριβές αποτέλεσμα, αλλά θα σας βοηθήσει να πάρετε μια ιδέα για το εάν η αγορά ενός καταστήματος με πίστωση για μια συγκεκριμένη περιοχή θα αποδώσει.

Πώς να εφαρμόσετε τη μέθοδο ελαχίστων τετραγώνων στο Excel

Το Excel έχει μια συνάρτηση για τον υπολογισμό της τιμής των ελαχίστων τετραγώνων. Έχει την ακόλουθη μορφή: TREND (γνωστές τιμές Y, γνωστές τιμές X, νέες τιμές X, σταθερά). Ας εφαρμόσουμε τον τύπο για τον υπολογισμό του OLS στο Excel στον πίνακά μας.

Για να το κάνετε αυτό, στο κελί στο οποίο θα πρέπει να εμφανίζεται το αποτέλεσμα του υπολογισμού με τη μέθοδο των ελαχίστων τετραγώνων στο Excel, εισαγάγετε το σύμβολο "=" και επιλέξτε τη συνάρτηση "TREND". Στο παράθυρο που ανοίγει, συμπληρώστε τα κατάλληλα πεδία, επισημαίνοντας:

  • εύρος γνωστών τιμών για το Y (σε αυτήν την περίπτωση δεδομένα για τον κύκλο εργασιών).
  • εύρος x 1 , …x n , δηλαδή το μέγεθος του χώρου λιανικής.
  • και γνωστές και άγνωστες τιμές του x, για τις οποίες πρέπει να μάθετε το ποσό του κύκλου εργασιών (για πληροφορίες σχετικά με τη θέση τους στο φύλλο εργασίας, δείτε παρακάτω).

Επιπλέον, υπάρχει μια λογική μεταβλητή "Const" στον τύπο. Εάν εισαγάγετε 1 στο πεδίο που αντιστοιχεί σε αυτό, τότε αυτό θα σημαίνει ότι πρέπει να πραγματοποιηθούν υπολογισμοί, με την προϋπόθεση ότι b \u003d 0.

Εάν πρέπει να γνωρίζετε την πρόβλεψη για περισσότερες από μία τιμές x, τότε αφού εισαγάγετε τον τύπο, δεν πρέπει να πατήσετε "Enter", αλλά πρέπει να πληκτρολογήσετε τον συνδυασμό "Shift" + "Control" + "Enter" ("Enter" ) στο πληκτρολόγιο.

Μερικά Χαρακτηριστικά

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

  • Εάν τακτοποιήσετε το εύρος των γνωστών τιμών της μεταβλητής y σε μία γραμμή ή στήλη, τότε κάθε γραμμή (στήλη) με γνωστές τιμές x θα γίνει αντιληπτή από το πρόγραμμα ως ξεχωριστή μεταβλητή.
  • Εάν η περιοχή με γνωστό x δεν καθορίζεται στο παράθυρο TREND, τότε στην περίπτωση χρήσης της συνάρτησης στο Excel, το πρόγραμμα θα τη θεωρήσει ως πίνακα που αποτελείται από ακέραιους αριθμούς, ο αριθμός των οποίων αντιστοιχεί στην περιοχή με τις δεδομένες τιμές ​της μεταβλητής y.
  • Για να εξάγετε έναν πίνακα "προβλεπόμενων" τιμών, η έκφραση τάσης πρέπει να εισαχθεί ως τύπος πίνακα.
  • Εάν δεν καθορίζονται νέες τιμές x, τότε η συνάρτηση TREND τις θεωρεί ίσες με τις γνωστές. Εάν δεν καθορίζονται, τότε ο πίνακας 1 λαμβάνεται ως όρισμα. 2; 3; 4;…, το οποίο είναι ανάλογο με το εύρος με τις ήδη δεδομένες παραμέτρους y.
  • Το εύρος που περιέχει τις νέες τιμές x πρέπει να έχει τις ίδιες ή περισσότερες σειρές ή στήλες με το εύρος με τις δεδομένες τιμές y. Με άλλα λόγια, πρέπει να είναι ανάλογη με τις ανεξάρτητες μεταβλητές.
  • Ένας πίνακας με γνωστές τιμές x μπορεί να περιέχει πολλές μεταβλητές. Ωστόσο, εάν μιλάμε μόνο για ένα, τότε απαιτείται οι περιοχές με τις δεδομένες τιμές των x και y να είναι ανάλογες. Στην περίπτωση πολλών μεταβλητών, είναι απαραίτητο το εύρος με τις δεδομένες τιμές y να ταιριάζει σε μία στήλη ή μία γραμμή.

Λειτουργία FORECAST

Υλοποιείται χρησιμοποιώντας διάφορες λειτουργίες. Ένα από αυτά ονομάζεται «ΠΡΟΒΛΕΨΗ». Είναι παρόμοιο με το TREND, δηλαδή δίνει το αποτέλεσμα των υπολογισμών με τη μέθοδο των ελαχίστων τετραγώνων. Ωστόσο, μόνο για ένα Χ, για το οποίο η τιμή του Υ είναι άγνωστη.

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

Slobodyanyuk A.I. Η μέθοδος των ελαχίστων τετραγώνων στο σχολικό φυσικό πείραμα // Fizika: προβληματική. δημοσίευση - 1995. - Τεύχος. 1. - S. 88-99.

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

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

Κατά κανόνα, όλα τα φυσικά πειράματα περιορίζονται στη μέτρηση της εξάρτησης κάποιας ποσότητας uαπό μία ή περισσότερες άλλες ποσότητες z 1 , z 2 , …, z n.

Η ανάγκη λήψης της εξάρτησης (και όχι η διεξαγωγή μέτρησης "σημείου" σε σταθερές τιμές των παραμέτρων) δικαιολογείται από τα ακόλουθα πλεονεκτήματα:

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

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

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

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

Η OLS δηλώνει ότι η «καλύτερη» καμπύλη είναι αυτή για την οποία το άθροισμα των τετραγωνικών αποκλίσεων των πειραματικών τιμών u iαπό τις τιμές συνάρτησης ελάχιστος. Έτσι, για τον προσδιορισμό των παραμέτρων ένα, σι, ντο,... είναι απαραίτητο να βρεθεί το ελάχιστο της συνάρτησης

. (1)

Σημειώστε ότι το Φ θεωρείται εδώ ως συνάρτηση των παραμέτρων ένα, σι, ντο,..., αφού οι ποσότητες u i, z iγνωστό από πειραματικά δεδομένα.

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

για τα οποία η εφαρμογή του LSM είναι η απλούστερη. Παραδείγματα μετασχηματισμών αυτού του τύπου δίνονται στον Πίνακα. 1. Κάποιοι μετασχηματισμοί θα συζητηθούν παρακάτω κατά την παρουσίαση συγκεκριμένων παραδειγμάτων.

Αντικαταστήστε την έκφραση (2) στην έκφραση (1)

(3)

και πάρτε εξισώσεις για τον προσδιορισμό των παραμέτρων αλλάΚαι σι. Για να γίνει αυτό, υπολογίζουμε τις παραγώγους της συνάρτησης Φ ως προς αλλάΚαι σικαι να τις εξισώσει με το μηδέν,

(4)

Αυτό το σύστημα είναι γραμμικό και μπορεί να λυθεί εύκολα:

(5)

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

(6)

(οι αγκύλες σημαίνουν τον αριθμητικό μέσο όρο των πειραματικών δεδομένων) και γράψτε

(7)

Από τη δεύτερη εξίσωση του συστήματος (4) εκφράζουμε .

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

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

1. Τα αποτελέσματα των μετρήσεων είναι ανεξάρτητα.

2. Τα σφάλματα μέτρησης ακολουθούν κανονική κατανομή.

3. Ποσότητες ΧΕγώ, είναι γνωστά ακριβώς.

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

Όταν πληρούνται αυτές οι προϋποθέσεις, οι παράμετροι αλλά, σιεκφράζονται γραμμικά ως προς τα αποτελέσματα των μετρήσεων στοΕγώ, (λάθη μέτρησης x iπαραμελήθηκε), επομένως το σφάλμα στον προσδιορισμό των παραμέτρων μπορεί να βρεθεί με την τυπική μέθοδο ως το σφάλμα της έμμεσης μέτρησης. Κάπως περίπλοκοι υπολογισμοί οδηγούν στους ακόλουθους τύπους για εκτιμήσεις σφαλμάτων:

(8)

όπου , η υπόλοιπη σημείωση παραμένει η ίδια:

(9)

Έτσι, οι τύποι (6) - (9) εξαντλούν πλήρως το LSM για την ανάλυση μιας γραμμικής εξάρτησης. Οι τύποι (7) - (8) δίνουν εκτιμήσεις μόνο τυχαίων σφαλμάτων μέτρησης. Η χρήση τους δικαιολογείται πλήρως εάν επικρατήσει αυτού του είδους το σφάλμα, κάτι που συμβαίνει συχνότερα στην πράξη. Απόδειξη αυτής της κυριαρχίας είναι μια αξιοσημείωτη διασπορά πόντων ( στοΕγώ, ΧΕγώ) στο γράφημα όταν αυτά τα σημεία δεν βρίσκονται ακριβώς σε ευθεία γραμμή. Σημειώστε ότι το σταθερό συστηματικό σφάλμα οργάνου δεν επηρεάζει τον προσδιορισμό της παραμέτρου αλλάκαι είναι μια πρόσθετη προσθήκη στο σφάλμα παραμέτρου σι, δηλ. εάν το σφάλμα μέτρησης οργάνων στοΕγώείναι ίσο λοιπόν .

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

2. Πρακτική εφαρμογή LSM για γραμμική εξάρτηση από μη προγραμματιζόμενη αριθμομηχανή

Όπως δείχνει η εμπειρία, είναι καλύτερο να χρησιμοποιήσετε μια προετοιμασμένη φόρμα για τον υπολογισμό των παραμέτρων μιας γραμμικής εξάρτησης και των σφαλμάτων τους (Πίνακας 2). Η στήλη 1 περιέχει τους αριθμούς των μετρήσεων που πραγματοποιήθηκαν ( Εγώ = 1, 2, ..., Ν ) στις στήλες 2, 3 - τα αποτελέσματα των μετρήσεων των ποσοτήτων z i, u i.

Το πρώτο βήμα για τη χρήση αυτής της φόρμας για την υλοποίηση του OLS είναι να συμπληρώσετε τις στήλες 4, 5. Παρουσιάζουν τα αποτελέσματα μετασχηματισμών από z, uσε ποσότητες Χ, στο, μεταξύ των οποίων αναζητείται γραμμική σχέση.

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

1) υπολογίστε - για να το κάνετε αυτό, εισάγετε διαδοχικά στη μνήμη όλες τις τιμές ΧΕγώ, καταγράφεται στη στήλη 4, και μετά το περιεχόμενο διαιρείται με τον αριθμό των ζευγών μέτρησης Ν,καταγράψτε το αποτέλεσμα στη στήλη 7.

2) υπολογίστε , πληκτρολογώντας διαδοχικά τις τιμές x i, συσσωρεύστε στη μνήμη το άθροισμα των τετραγώνων τους (πληκτρολογήστε τις τιμές - "πολλαπλασιάστε" - "ίσο με" - "στη μνήμη +") και διαιρέστε με Ν, αφαιρέστε το τετράγωνο του μέσου όρου από το αποτέλεσμα που προέκυψε, γράψτε το αποτέλεσμα στη στήλη 7.

3 – 4) υπολογίστε ομοίως και ;

5) συσσωρεύστε το άθροισμα των προϊόντων στη μνήμη, διαιρέστε με Ν, αφαιρέστε το γινόμενο του μέσου όρου και διαιρέστε με - λάβετε την τιμή της παραμέτρου αλλά.

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

3. Ένα παράδειγμα χρήσης των ελαχίστων τετραγώνων

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

Εξοπλισμός: νήμα, βάρος, τρίποδο, χάρακας, χρονόμετρο.

Λύση. Η περίοδος των μικρών ταλαντώσεων ενός μαθηματικού εκκρεμούς Τκαθορίζεται από τον τύπο. Αυτός ο τύπος μπορεί να μετατραπεί στη φόρμα.

Με άλλα λόγια, μεταξύ του μήκους του εκκρεμούς μεγάλοκαι το τετράγωνο της περιόδου υπάρχει μια γραμμική σχέση, την οποία γράφουμε με τη μορφή: , όπου (μετατροπή σε γραμμική μορφή). Εισαγωγή παραμέτρων σισε αυτή την περίπτωση δεν είναι υποχρεωτικό, αφού θεωρητικά σι= 0. Ωστόσο, η εγγραφή μιας γραμμικής εξάρτησης σε γενική μορφή καθιστά δυνατό να ληφθεί αυτόματα υπόψη το σφάλμα στον προσδιορισμό του μήκους του εκκρεμούς, επιπλέον, σε αυτήν την περίπτωση, είναι δυνατό να μετρηθεί όχι το μήκος του εκκρεμούς, αλλά μόνο η αλλαγή του. Εάν όλες οι μετρήσεις πραγματοποιηθούν σωστά, τότε το LSM θα πρέπει να οδηγήσει στο αποτέλεσμα, το οποίο θα υποδεικνύει ότι.

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

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

τελικό αποτέλεσμα Κυρία.

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

4. ΠΕΙΡΑΜΑΤΙΚΑ ΠΡΟΒΛΗΜΑΤΑ ΠΟΥ ΑΦΟΡΟΥΝ ΤΗ ΧΡΗΣΗ ΤΟΥ LSM

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

Εργασία 1. Η περίοδος ταλάντωσης ενός μαθηματικού εκκρεμούς εξαρτάται από το πλάτος j 0 (σε ακτίνια) σύμφωνα με το νόμο

(10)

Προσδιορίστε την τιμή της παραμέτρου β.

Εξοπλισμός: νήμα, φορτίο, τρίποδο, μοιρογνωμόνιο, ηλεκτρονικό χρονόμετρο.

Οδηγίες λύσης. Η εξάρτηση της περιόδου ταλάντωσης από το πλάτος είναι μάλλον ασθενής. Για την ανίχνευση του, είναι απαραίτητο να πραγματοποιηθούν μετρήσεις με υψηλή ακρίβεια (-0,01 s), η οποία απαιτεί ηλεκτρονικό χρονόμετρο.

Αντιπροσωπεύουμε την εξάρτηση (10) ως , όπου y=Τ,σι = Τ 0 . Χρησιμοποιώντας το LSM για μια γραμμική εξάρτηση, μπορεί κανείς να βρει τις τιμές των παραμέτρων αλλάΚαι σι, τότε ο επιθυμητός συντελεστής καθορίζεται από τον τύπο (σημειώστε ότι η θεωρητική τιμή είναι ).

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

Εξοπλισμός: πηγή φωτός, οθόνη, φακός, χάρακας.

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

όπου ρεείναι η απόσταση από το αντικείμενο στο φακό, φάείναι η απόσταση από το φακό στην εικόνα, φάείναι η εστιακή απόσταση του φακού.

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

Εργασία 3. Η υδρόψυξη περιγράφεται από τον τύπο , όπου Δ Τ- διαφορά θερμοκρασίας μεταξύ νερού και αέρα στο δωμάτιο, Δ Τ 0 - η ίδια διαφορά εκείνη τη στιγμή t= 0. Προσδιορίστε πόση ώρα έχει περάσει από τότε που έβρασε το νερό.

Εξοπλισμός: ζεστό νερό σε δοχείο, θερμόμετρο, ρολόι.

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

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

(12)

και δηλώνουν , Χ= t, παίρνουμε μια γραμμική εξάρτηση

Επεξεργαζόμενοι τα αποτελέσματα των μετρήσεων με τα ελάχιστα τετράγωνα, βρίσκουμε τις τιμές των παραμέτρων αλλά, σι, από το οποίο μπορείτε να υπολογίσετε την επιθυμητή τιμή του χρόνου t 0: .

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

Εξοπλισμός: χαρτάκια, χρονόμετρο.

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

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

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

(14)

Πάρτε πολλές (1, 2, 3, ..., 5) πανομοιότυπες πλάκες και μετρήστε τον χρόνο πτώσης t nβάζω μαζί nπιάτα. Συντελεστής απόστον τύπο (13) θα είναι το ίδιο (εξαρτάται μόνο από το σχήμα της πλάκας), ενώ η μάζα των σωμάτων που πέφτουν , όπου Μ 0 είναι η μάζα μιας πλάκας. Χρησιμοποιούμε (14): , σε λογαριθμική μορφή

(15)

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

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

Κατά τη διεξαγωγή ενός πειράματος, πρέπει να ληφθεί υπόψη ότι ο χρόνος για να πέσει ένα κομμάτι χαρτί από ύψος είναι περίπου 1,5 s, επομένως είναι απαραίτητο να μετρηθεί ο χρόνος πτώσης με σφάλμα της τάξης του 0,1 s. Επομένως, για κάθε τιμή του αριθμού nλάβετε πολλαπλές τιμές tn. Τονίζουμε ότι σε αυτήν την περίπτωση δεν χρειάζεται να υπολογιστούν εκ των προτέρων οι μέσες τιμές, είναι δυνατό (και απαραίτητο) να θεωρηθούν όλα τα αποτελέσματα των μετρήσεων ως ανεξάρτητα και να τα συμπεριληφθούν στη φόρμα υπολογισμού.

Ένα άλλο πρόβλημα αυτού του τύπου συζητείται αναλυτικά στο περιοδικό Focus.

5. Συμπέρασμα

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

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

1. Taylor J. Εισαγωγή στη θεωρία των σφαλμάτων. - Μ: Μιρ, 1985.

2. Bolshev L.N., Smirnov N.V. Πίνακες μαθηματικών στατιστικών. – Μ.: Nauka, 1983.

3. Timofeev A.. Ας τσεκάρουμε τον Stokes; - Συγκεντρώνω. - 1995. - Νο. 2. - Σ. 44-49.

Αναγωγή σε γραμμική εξάρτηση

Τύπος εξάρτησης

μεταμόρφωση

Παράμετροι

Έντυπο για τον υπολογισμό των παραμέτρων της γραμμικής εξάρτησης

Εγώ

z

u

Χ

y

Τύποι υπολογισμού

Αποτελέσματα

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

Δl,

Τύποι υπολογισμού

Αποτελέσματα

Επιλέγοντας τον τύπο της συνάρτησης παλινδρόμησης, π.χ. ο τύπος του εξεταζόμενου μοντέλου της εξάρτησης του Y από το X (ή το X από το Y), για παράδειγμα, ένα γραμμικό μοντέλο yx \u003d a + bx, είναι απαραίτητο να προσδιοριστούν οι συγκεκριμένες τιμές​των συντελεστών του μοντέλο.

Για διαφορετικές τιμές των a και b, είναι δυνατό να κατασκευαστεί ένας άπειρος αριθμός εξαρτήσεων της μορφής yx =a+bx, δηλαδή, υπάρχει ένας άπειρος αριθμός γραμμών στο επίπεδο συντεταγμένων, αλλά χρειαζόμαστε μια τέτοια εξάρτηση που αντιστοιχεί στις παρατηρούμενες τιμές με τον καλύτερο τρόπο. Έτσι, το πρόβλημα περιορίζεται στην επιλογή των καλύτερων συντελεστών.

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

Σημειώστε: Y i - η τιμή που υπολογίζεται από την εξίσωση Y i =a+bx i . y i - μετρούμενη τιμή, ε i =y i -Y i - διαφορά μεταξύ των μετρούμενων και των υπολογισμένων τιμών, ε i =y i -a-bx i .

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

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

(2)

Αν διαιρέσουμε και τις δύο πλευρές των κανονικών εξισώσεων με n, παίρνουμε:

Δεδομένου ότι (3)

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

Στην περίπτωση αυτή, το b ονομάζεται συντελεστής παλινδρόμησης. Το a ονομάζεται ελεύθερο μέλος της εξίσωσης παλινδρόμησης και υπολογίζεται από τον τύπο:

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

Ετσι, είναι μια εξίσωση γραμμικής παλινδρόμησης.

Η παλινδρόμηση μπορεί να είναι άμεση (b>0) και αντίστροφη (b Παράδειγμα 1. Τα αποτελέσματα της μέτρησης των τιμών X και Y δίνονται στον πίνακα:

x i -2 0 1 2 4
y i 0.5 1 1.5 2 3

Υποθέτοντας ότι υπάρχει γραμμική σχέση μεταξύ X και Y y=a+bx, προσδιορίστε τους συντελεστές a και b χρησιμοποιώντας τη μέθοδο των ελαχίστων τετραγώνων.

Λύση. Εδώ n=5
x i =-2+0+1+2+4=5;
x i 2 =4+0+1+4+16=25
x i y i =-2 0,5+0 1+1 1,5+2 2+4 3=16,5
y i =0,5+1+1,5+2+3=8

και το κανονικό σύστημα (2) έχει τη μορφή

Λύνοντας αυτό το σύστημα, παίρνουμε: b=0,425, a=1,175. Επομένως y=1,175+0,425x.

Παράδειγμα 2. Υπάρχει δείγμα 10 παρατηρήσεων οικονομικών δεικτών (Χ) και (Υ).

x i 180 172 173 169 175 170 179 170 167 174
y i 186 180 176 171 182 166 182 172 169 177

Απαιτείται να βρεθεί ένα δείγμα εξίσωσης παλινδρόμησης Y-on-X. Κατασκευάστε ένα δείγμα γραμμής παλινδρόμησης Y-on-X.

Λύση. 1. Ας ταξινομήσουμε τα δεδομένα κατά τιμές x i και y i . Παίρνουμε έναν νέο πίνακα:

x i 167 169 170 170 172 173 174 175 179 180
y i 169 171 166 172 180 176 177 182 182 186

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

x i y i x i 2 x i y i
167 169 27889 28223
169 171 28561 28899
170 166 28900 28220
170 172 28900 29240
172 180 29584 30960
173 176 29929 30448
174 177 30276 30798
175 182 30625 31850
179 182 32041 32578
180 186 32400 33480
∑x i =1729 ∑y i =1761 ∑x i 2 299105 ∑x i y i =304696
x=172,9 y=176,1 x i 2 =29910,5 xy=30469,6

Σύμφωνα με τον τύπο (4), υπολογίζουμε τον συντελεστή παλινδρόμησης

και με τον τύπο (5)

Έτσι, η εξίσωση παλινδρόμησης του δείγματος μοιάζει με y=-59,34+1,3804x.
Ας σχεδιάσουμε τα σημεία (x i ; y i) στο επίπεδο συντεταγμένων και ας σημειώσουμε τη γραμμή παλινδρόμησης.


Εικ. 4

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

x i y i Y i Y i -y i
167 169 168.055 -0.945
169 171 170.778 -0.222
170 166 172.140 6.140
170 172 172.140 0.140
172 180 174.863 -5.137
173 176 176.225 0.225
174 177 177.587 0.587
175 182 178.949 -3.051
179 182 184.395 2.395
180 186 185.757 -0.243

Οι τιμές Y i υπολογίζονται σύμφωνα με την εξίσωση παλινδρόμησης.

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

Παράδειγμα.

Πειραματικά δεδομένα για τις τιμές των μεταβλητών ΧΚαι στοδίνονται στον πίνακα.

Ως αποτέλεσμα της ευθυγράμμισής τους, η συνάρτηση

Χρησιμοποιώντας μέθοδος ελάχιστου τετραγώνου, προσεγγίστε αυτά τα δεδομένα με μια γραμμική εξάρτηση y=ax+b(βρες επιλογές αλλάΚαι σι). Μάθετε ποια από τις δύο γραμμές είναι καλύτερη (με την έννοια της μεθόδου των ελαχίστων τετραγώνων) ευθυγραμμίζει τα πειραματικά δεδομένα. Κάντε ένα σχέδιο.

Η ουσία της μεθόδου των ελαχίστων τετραγώνων (LSM).

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

Έτσι, η λύση του παραδείγματος ανάγεται στην εύρεση του άκρου μιας συνάρτησης δύο μεταβλητών.

Παραγωγή τύπων εύρεσης συντελεστών.

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

Λύνουμε το προκύπτον σύστημα εξισώσεων με οποιαδήποτε μέθοδο (π.χ μέθοδος αντικατάστασηςή Η μέθοδος του Cramer) και λάβετε τύπους για την εύρεση των συντελεστών χρησιμοποιώντας τη μέθοδο των ελαχίστων τετραγώνων (LSM).

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

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

Ήρθε η ώρα να θυμηθούμε το αρχικό παράδειγμα.

Λύση.

Στο παράδειγμά μας n=5. Συμπληρώνουμε τον πίνακα για τη διευκόλυνση του υπολογισμού των ποσών που περιλαμβάνονται στους τύπους των απαιτούμενων συντελεστών.

Οι τιμές στην τέταρτη σειρά του πίνακα λαμβάνονται πολλαπλασιάζοντας τις τιμές της 2ης σειράς με τις τιμές της 3ης σειράς για κάθε αριθμό Εγώ.

Οι τιμές στην πέμπτη σειρά του πίνακα λαμβάνονται με τον τετραγωνισμό των τιμών της 2ης σειράς για κάθε αριθμό Εγώ.

Οι τιμές της τελευταίας στήλης του πίνακα είναι τα αθροίσματα των τιμών στις σειρές.

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

Συνεπώς, y=0,165x+2,184είναι η επιθυμητή προσεγγιστική ευθεία.

Μένει να μάθουμε ποια από τις γραμμές y=0,165x+2,184ή προσεγγίζει καλύτερα τα αρχικά δεδομένα, δηλαδή να κάνει μια εκτίμηση χρησιμοποιώντας τη μέθοδο των ελαχίστων τετραγώνων.

Εκτίμηση του σφάλματος της μεθόδου των ελαχίστων τετραγώνων.

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

Από τότε η γραμμή y=0,165x+2,184προσεγγίζει καλύτερα τα αρχικά δεδομένα.

Γραφική απεικόνιση της μεθόδου των ελαχίστων τετραγώνων (LSM).

Όλα φαίνονται υπέροχα στα charts. Η κόκκινη γραμμή είναι η γραμμή που βρέθηκε y=0,165x+2,184, η μπλε γραμμή είναι , οι ροζ κουκκίδες είναι τα αρχικά δεδομένα.

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

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

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

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

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

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

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

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

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

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

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

Προσθήκη γραμμών τάσης σε γράφημα

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

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

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

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

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

y = y(x)

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

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

y=mx+b

όπου m είναι η εφαπτομένη της κλίσης της γραμμικής παλινδρόμησης στον άξονα x. β - συντεταγμένη του σημείου τομής της γραμμικής παλινδρόμησης με τον άξονα y.

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

Σε αυτήν την περίπτωση, η γραμμή τάσης χτίζεται σύμφωνα με την εξίσωση:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

όπου οι συντελεστές c0, c1, c2,... c6 είναι σταθερές των οποίων οι τιμές καθορίζονται κατά την κατασκευή.

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

y = c ln(x) + b

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

Είναι κατασκευασμένο σύμφωνα με την εξίσωση:

y = cxb

όπου οι συντελεστές b, c είναι σταθερές.

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

Είναι κατασκευασμένο σύμφωνα με την εξίσωση:

y=cebx

όπου οι συντελεστές b, c είναι σταθερές.

Κατά την επιλογή μιας γραμμής τάσης, το Excel υπολογίζει αυτόματα την τιμή του R2, η οποία χαρακτηρίζει την ακρίβεια της προσέγγισης: όσο πιο κοντά είναι η τιμή R2 στο ένα, τόσο πιο αξιόπιστα η γραμμή τάσης προσεγγίζει τη διαδικασία που μελετάται. Εάν είναι απαραίτητο, η τιμή του R2 μπορεί πάντα να εμφανίζεται στο διάγραμμα.

Καθορίζεται από τον τύπο:

Για να προσθέσετε μια γραμμή τάσης σε μια σειρά δεδομένων:

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

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

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

Μετά από αυτό χρειάζεστε:

Στην καρτέλα Τύπος, επιλέξτε τον απαιτούμενο τύπο γραμμής τάσης (Το Γραμμικό είναι επιλεγμένο από προεπιλογή). Για τον τύπο Polynomial, στο πεδίο Degree, καθορίστε τον βαθμό του επιλεγμένου πολυωνύμου.

1 . Το πεδίο Built on Series παραθέτει όλες τις σειρές δεδομένων στο εν λόγω γράφημα. Για να προσθέσετε μια γραμμή τάσης σε μια συγκεκριμένη σειρά δεδομένων, επιλέξτε το όνομά της στο πεδίο Ενσωματωμένη σειρά.

Εάν είναι απαραίτητο, μεταβαίνοντας στην καρτέλα Παράμετροι (Εικ. 2), μπορείτε να ορίσετε τις ακόλουθες παραμέτρους για τη γραμμή τάσης:

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

    ορίστε τον αριθμό των περιόδων (προς τα εμπρός ή προς τα πίσω) για την πρόβλεψη στο πεδίο Πρόβλεψη.

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

    Εμφάνιση της τιμής της αξιοπιστίας προσέγγισης R2 στην περιοχή του διαγράμματος, για την οποία θα πρέπει να ενεργοποιήσετε το πλαίσιο ελέγχου, τοποθετήστε την τιμή της αξιοπιστίας προσέγγισης (R^2) στο διάγραμμα.

    ορίστε το σημείο τομής της γραμμής τάσης με τον άξονα Υ, για το οποίο θα πρέπει να ενεργοποιήσετε το πλαίσιο ελέγχου Τομή της καμπύλης με τον άξονα Υ σε ένα σημείο.

    κάντε κλικ στο κουμπί OK για να κλείσετε το παράθυρο διαλόγου.

Υπάρχουν τρεις τρόποι για να ξεκινήσετε την επεξεργασία μιας ήδη χτισμένης γραμμής τάσης:

    χρησιμοποιήστε την εντολή Selected trend line από το μενού Format, αφού επιλέξετε τη γραμμή τάσης.

    επιλέξτε την εντολή Format Trendline από το μενού περιβάλλοντος, η οποία καλείται κάνοντας δεξί κλικ στη γραμμή τάσης.

    κάνοντας διπλό κλικ στη γραμμή τάσης.

Το πλαίσιο διαλόγου Format Trendline θα εμφανιστεί στην οθόνη (Εικ. 3), που περιέχει τρεις καρτέλες: Προβολή, Τύπος, Παράμετροι και τα περιεχόμενα των δύο τελευταίων συμπίπτουν πλήρως με τις παρόμοιες καρτέλες του πλαισίου διαλόγου Trendline (Εικ. 1-2 ). Στην καρτέλα Προβολή, μπορείτε να ορίσετε τον τύπο γραμμής, το χρώμα και το πάχος της.

Για να διαγράψετε μια ήδη κατασκευασμένη γραμμή τάσης, επιλέξτε τη γραμμή τάσης που θέλετε να διαγράψετε και πατήστε το πλήκτρο Διαγραφή.

Τα πλεονεκτήματα του εξεταζόμενου εργαλείου ανάλυσης παλινδρόμησης είναι:

    τη σχετική ευκολία σχεδίασης μιας γραμμής τάσης σε γραφήματα χωρίς τη δημιουργία πίνακα δεδομένων για αυτήν·

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

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

    τη δυνατότητα λήψης της εξίσωσης της γραμμής τάσης σε αναλυτική μορφή.

    τη δυνατότητα, εάν είναι απαραίτητο, να ληφθεί αξιολόγηση της αξιοπιστίας της προσέγγισης.

Τα μειονεκτήματα περιλαμβάνουν τα ακόλουθα σημεία:

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

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

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

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

Δεν μπορείτε να προσθέσετε γραμμές τάσης σε σειρές δεδομένων σε γραφήματα 3-D, Standard, Radar, Pie και Donut.

Χρήση ενσωματωμένων λειτουργιών του Excel

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

Το Excel έχει πολλές λειτουργίες για τη δημιουργία γραμμικής παλινδρόμησης, ιδίως:

    ΤΑΣΗ;

  • ΚΛΙΣΗ και ΚΟΨΗ.

Καθώς και πολλές λειτουργίες για την κατασκευή μιας εκθετικής γραμμής τάσης, ειδικότερα:

    LGRFPπερ.

Θα πρέπει να σημειωθεί ότι οι τεχνικές για την κατασκευή παλινδρόμησης χρησιμοποιώντας τις συναρτήσεις TREND και GROWTH είναι πρακτικά οι ίδιες. Το ίδιο μπορεί να ειπωθεί για το ζεύγος των συναρτήσεων LINEST και LGRFPRIBL. Για αυτές τις τέσσερις συναρτήσεις, κατά τη δημιουργία ενός πίνακα τιμών, χρησιμοποιούνται χαρακτηριστικά του Excel, όπως τύποι πίνακα, γεγονός που δυσχεραίνει κάπως τη διαδικασία δημιουργίας παλινδρομήσεων. Σημειώνουμε επίσης ότι η κατασκευή μιας γραμμικής παλινδρόμησης, κατά τη γνώμη μας, είναι πιο εύκολο να εφαρμοστεί χρησιμοποιώντας τις συναρτήσεις SLOPE και INTERCEPT, όπου η πρώτη από αυτές καθορίζει την κλίση της γραμμικής παλινδρόμησης και η δεύτερη καθορίζει το τμήμα που αποκόπτεται από την παλινδρόμηση στον άξονα y.

Τα πλεονεκτήματα του ενσωματωμένου εργαλείου συναρτήσεων για ανάλυση παλινδρόμησης είναι:

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

    μια τυπική τεχνική για την κατασκευή γραμμών τάσης με βάση τις παραγόμενες σειρές δεδομένων.

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

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

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

Παραδείγματα επίλυσης συγκεκριμένων προβλημάτων

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

Εργασία 1

Με πίνακα στοιχείων για τα κέρδη μιας αυτοκινητοβιομηχανίας για την περίοδο 1995-2002. πρέπει να κάνετε τα εξής.

    Κατασκευάστε ένα γράφημα.

    Προσθέστε γραμμικές και πολυωνυμικές (τετραγωνικές και κυβικές) γραμμές τάσης στο γράφημα.

    Χρησιμοποιώντας τις εξισώσεις της γραμμής τάσης, λάβετε δεδομένα σε πίνακα για τα κέρδη της επιχείρησης για κάθε γραμμή τάσης για την περίοδο 1995-2004.

    Κάντε μια πρόβλεψη κερδών για την επιχείρηση για το 2003 και το 2004.

Η λύση του προβλήματος

    Στην περιοχή των κελιών A4:C11 του φύλλου εργασίας του Excel, εισάγουμε το φύλλο εργασίας που φαίνεται στην Εικ. 4.

    Έχοντας επιλέξει το εύρος των κελιών B4:C11, κατασκευάζουμε ένα γράφημα.

    Ενεργοποιούμε το κατασκευασμένο γράφημα και, σύμφωνα με τη μέθοδο που περιγράφεται παραπάνω, αφού επιλέξουμε τον τύπο γραμμής τάσης στο πλαίσιο διαλόγου Γραμμή τάσης (βλ. Εικ. 1), προσθέτουμε εναλλάξ γραμμικές, τετραγωνικές και κυβικές γραμμές τάσης στο γράφημα. Στο ίδιο παράθυρο διαλόγου, ανοίξτε την καρτέλα Παράμετροι (βλ. Εικ. 2), στο Όνομα του πεδίου προσεγγιστικής (εξομαλυνόμενης) καμπύλης, εισαγάγετε το όνομα της τάσης που προστέθηκε και στο πεδίο Πρόβλεψη προς τα εμπρός για: περιόδους, ορίστε την τιμή 2, αφού σχεδιάζεται να γίνει πρόβλεψη κερδών για δύο χρόνια μπροστά. Για να εμφανίσετε την εξίσωση παλινδρόμησης και την τιμή αξιοπιστίας προσέγγισης R2 στην περιοχή του διαγράμματος, ενεργοποιήστε τα πλαίσια ελέγχου Εμφάνιση της εξίσωσης στην οθόνη και τοποθετήστε την τιμή αξιοπιστίας προσέγγισης (R^2) στο διάγραμμα. Για καλύτερη οπτική αντίληψη, αλλάζουμε τον τύπο, το χρώμα και το πάχος των γραμμών τάσης, για τις οποίες χρησιμοποιούμε την καρτέλα Προβολή του πλαισίου διαλόγου Μορφή γραμμής τάσης (βλ. Εικ. 3). Το διάγραμμα που προκύπτει με τις προστιθέμενες γραμμές τάσης φαίνεται στο σχ. πέντε.

    Λήψη πινάκων στοιχείων για τα κέρδη της επιχείρησης για κάθε γραμμή τάσης για την περίοδο 1995-2004. Ας χρησιμοποιήσουμε τις εξισώσεις των γραμμών τάσης που παρουσιάζονται στο σχ. 5. Για να το κάνετε αυτό, στα κελιά της περιοχής D3:F3, εισαγάγετε πληροφορίες κειμένου σχετικά με τον τύπο της επιλεγμένης γραμμής τάσης: Γραμμική τάση, Τετραγωνική τάση, Κυβική τάση. Στη συνέχεια, εισαγάγετε τον τύπο γραμμικής παλινδρόμησης στο κελί D4 και, χρησιμοποιώντας τον δείκτη πλήρωσης, αντιγράψτε αυτόν τον τύπο με σχετικές αναφορές στην περιοχή των κελιών D5:D13. Πρέπει να σημειωθεί ότι κάθε κελί με τύπο γραμμικής παλινδρόμησης από την περιοχή των κελιών D4:D13 έχει ως όρισμα ένα αντίστοιχο κελί από την περιοχή A4:A13. Ομοίως, για την τετραγωνική παλινδρόμηση, η περιοχή κελιών E4:E13 συμπληρώνεται και για την κυβική παλινδρόμηση, η περιοχή κελιών F4:F13 συμπληρώνεται. Έτσι, έγινε πρόβλεψη για τα κέρδη της επιχείρησης για το 2003 και το 2004. με τρεις τάσεις. Ο πίνακας τιμών που προκύπτει φαίνεται στο σχ. 6.

Εργασία 2

    Κατασκευάστε ένα γράφημα.

    Προσθέστε λογαριθμικές, εκθετικές και εκθετικές γραμμές τάσης στο γράφημα.

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

    Χρησιμοποιώντας τις εξισώσεις γραμμής τάσης, λάβετε πίνακες για τα κέρδη της επιχείρησης για κάθε γραμμή τάσης για την περίοδο 1995-2002.

    Κάντε μια πρόβλεψη κερδών για την επιχείρηση για το 2003 και το 2004 χρησιμοποιώντας αυτές τις γραμμές τάσης.

Η λύση του προβλήματος

Ακολουθώντας τη μεθοδολογία που δίνεται στην επίλυση του προβλήματος 1, λαμβάνουμε ένα διάγραμμα με πρόσθετες λογαριθμικές, εκθετικές και εκθετικές γραμμές τάσης (Εικ. 7). Περαιτέρω, χρησιμοποιώντας τις ληφθείσες εξισώσεις γραμμής τάσης, συμπληρώνουμε τον πίνακα τιμών για το κέρδος της επιχείρησης, συμπεριλαμβανομένων των προβλεπόμενων τιμών για το 2003 και το 2004. (Εικ. 8).

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

R2 = 0,8659

Οι υψηλότερες τιμές του R2 αντιστοιχούν σε μοντέλα με πολυωνυμική τάση: τετραγωνικό (R2 = 0,9263) και κυβικό (R2 = 0,933).

Εργασία 3

Με έναν πίνακα δεδομένων σχετικά με τα κέρδη μιας επιχείρησης μηχανοκίνητων μεταφορών για την περίοδο 1995-2002, που δίνεται στην εργασία 1, πρέπει να εκτελέσετε τα ακόλουθα βήματα.

    Λάβετε σειρές δεδομένων για γραμμικές και εκθετικές γραμμές τάσης χρησιμοποιώντας τις συναρτήσεις TREND και GROW.

    Χρησιμοποιώντας τις συναρτήσεις TREND και GROWTH, κάντε μια πρόβλεψη κερδών για την επιχείρηση για το 2003 και το 2004.

    Για τα αρχικά δεδομένα και τις λαμβανόμενες σειρές δεδομένων, κατασκευάστε ένα διάγραμμα.

Η λύση του προβλήματος

Ας χρησιμοποιήσουμε το φύλλο εργασίας της εργασίας 1 (βλ. Εικ. 4). Ας ξεκινήσουμε με τη συνάρτηση TREND:

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

    καλέστε την εντολή Function από το μενού Insert. Στο παράθυρο διαλόγου Function Wizard που εμφανίζεται, επιλέξτε τη συνάρτηση TREND από την κατηγορία Statistical και, στη συνέχεια, κάντε κλικ στο κουμπί OK. Η ίδια λειτουργία μπορεί να πραγματοποιηθεί πατώντας το κουμπί (Λειτουργία Εισαγωγής) της τυπικής γραμμής εργαλείων.

    Στο παράθυρο διαλόγου Επιχειρήματα συνάρτησης που εμφανίζεται, εισαγάγετε την περιοχή των κελιών C4:C11 στο πεδίο Known_values_y. στο πεδίο Known_values_x - το εύρος των κελιών B4:B11;

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

Ο τύπος που πληκτρολογήσαμε στη γραμμή τύπων θα μοιάζει με: =(TREND(C4:C11;B4:B11)).

Ως αποτέλεσμα, η περιοχή των κελιών D4:D11 γεμίζει με τις αντίστοιχες τιμές της συνάρτησης TREND (Εικ. 9).

Να γίνει πρόβλεψη των κερδών της εταιρείας για το 2003 και το 2004. απαραίτητη:

    επιλέξτε την περιοχή των κελιών D12:D13, όπου θα εισαχθούν οι τιμές που προβλέπονται από τη συνάρτηση TREND.

    καλέστε τη συνάρτηση TREND και στο παράθυρο διαλόγου Function Arguments που εμφανίζεται, εισαγάγετε στο πεδίο Known_values_y - το εύρος των κελιών C4:C11; στο πεδίο Known_values_x - το εύρος των κελιών B4:B11; και στο πεδίο New_values_x - το εύρος των κελιών B12:B13.

    μετατρέψτε αυτόν τον τύπο σε τύπο πίνακα χρησιμοποιώντας τη συντόμευση πληκτρολογίου Ctrl + Shift + Enter.

    Ο εισαγόμενος τύπος θα μοιάζει με: =(TREND(C4:C11;B4:B11;B12:B13)), και η περιοχή των κελιών D12:D13 θα γεμίσει με τις προβλεπόμενες τιμές της συνάρτησης TREND (βλ. 9).

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

Το σχήμα 10 δείχνει τον πίνακα σε λειτουργία εμφάνισης τύπου.

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

Εργασία 4

Με τον πίνακα στοιχείων παραλαβής αιτήσεων για υπηρεσίες από την υπηρεσία αποστολής της αυτοκινητοβιομηχανίας για την περίοδο από 1η έως 11η ημέρα του τρέχοντος μήνα, πρέπει να γίνουν οι ακόλουθες ενέργειες.

    Λήψη σειρών δεδομένων για γραμμική παλινδρόμηση: χρησιμοποιώντας τις συναρτήσεις SLOPE και INTERCEPT. χρησιμοποιώντας τη συνάρτηση LINEST.

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

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

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

Η λύση του προβλήματος

Σημειώστε ότι, σε αντίθεση με τις συναρτήσεις TREND και GROW, καμία από τις συναρτήσεις που αναφέρονται παραπάνω (SLOPE, INTERCEPTION, LINEST, LGRFPRIB) δεν είναι παλινδρόμηση. Αυτές οι συναρτήσεις παίζουν μόνο βοηθητικό ρόλο, καθορίζοντας τις απαραίτητες παραμέτρους παλινδρόμησης.

Για γραμμικές και εκθετικές παλινδρομήσεις που κατασκευάζονται χρησιμοποιώντας τις συναρτήσεις SLOPE, INTERCEPT, LINEST, LGRFINB, η εμφάνιση των εξισώσεών τους είναι πάντα γνωστή, σε αντίθεση με τις γραμμικές και εκθετικές παλινδρομήσεις που αντιστοιχούν στις συναρτήσεις TREND και GROWTH.

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

y=mx+b

χρησιμοποιώντας τις συναρτήσεις SLOPE και INTERCEPT, με την κλίση της παλινδρόμησης m να καθορίζεται από τη συνάρτηση SLOPE και τον σταθερό όρο b - από τη συνάρτηση INTERCEPT.

Για να γίνει αυτό, εκτελούμε τις ακόλουθες ενέργειες:

    εισαγάγετε τον πίνακα προέλευσης στην περιοχή των κελιών A4:B14.

    Η τιμή της παραμέτρου m θα καθοριστεί στο κελί C19. Επιλέξτε από την κατηγορία Στατιστική τη συνάρτηση Slope. εισαγάγετε την περιοχή των κελιών B4:B14 στο πεδίο γνωστές_τιμές_y και την περιοχή κελιών A4:A14 στο πεδίο γνωστές_τιμές_x. Ο τύπος θα εισαχθεί στο κελί C19: =SLOPE(B4:B14;A4:A14);

    χρησιμοποιώντας παρόμοια μέθοδο, προσδιορίζεται η τιμή της παραμέτρου b στο κελί D19. Και το περιεχόμενό του θα μοιάζει με αυτό: = INTERCEPT(B4:B14;A4:A14). Έτσι, οι τιμές των παραμέτρων m και b, που είναι απαραίτητες για την κατασκευή μιας γραμμικής παλινδρόμησης, θα αποθηκευτούν, αντίστοιχα, στα κελιά C19, D19.

    τότε εισάγουμε τον τύπο γραμμικής παλινδρόμησης στο κελί C4 με τη μορφή: = $ C * A4 + $ D. Σε αυτόν τον τύπο, τα κελιά C19 και D19 γράφονται με απόλυτες αναφορές (η διεύθυνση κελιού δεν πρέπει να αλλάζει με πιθανή αντιγραφή). Το απόλυτο σύμβολο αναφοράς $ μπορεί να πληκτρολογηθεί είτε από το πληκτρολόγιο είτε χρησιμοποιώντας το πλήκτρο F4, αφού τοποθετήσετε τον κέρσορα στη διεύθυνση του κελιού. Χρησιμοποιώντας τη λαβή πλήρωσης, αντιγράψτε αυτόν τον τύπο στην περιοχή των κελιών C4:C17. Λαμβάνουμε την επιθυμητή σειρά δεδομένων (Εικ. 12). Λόγω του γεγονότος ότι ο αριθμός των αιτημάτων είναι ακέραιος, θα πρέπει να ορίσετε τη μορφή αριθμού στην καρτέλα Αριθμός του παραθύρου Μορφή κελιού με τον αριθμό των δεκαδικών ψηφίων στο 0.

2 . Τώρα ας οικοδομήσουμε μια γραμμική παλινδρόμηση που δίνεται από την εξίσωση:

y=mx+b

χρησιμοποιώντας τη συνάρτηση LINEST.

Για αυτό:

    εισαγάγετε τη συνάρτηση LINEST ως τύπο πίνακα στην περιοχή των κελιών C20:D20: =(LINEST(B4:B14;A4:A14)). Ως αποτέλεσμα, παίρνουμε την τιμή της παραμέτρου m στο κελί C20 και την τιμή της παραμέτρου b στο κελί D20.

    εισαγάγετε τον τύπο στο κελί D4: =$C*A4+$D;

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

3 . Κατασκευάζουμε μια εκθετική παλινδρόμηση που έχει την εξίσωση:

με τη βοήθεια της συνάρτησης LGRFPRIBL, εκτελείται παρόμοια:

    στην περιοχή των κελιών C21:D21, εισαγάγετε τη συνάρτηση LGRFPRIBL ως τύπο πίνακα: =( LGRFPRIBL (B4:B14;A4:A14)). Σε αυτήν την περίπτωση, η τιμή της παραμέτρου m θα προσδιοριστεί στο κελί C21 και η τιμή της παραμέτρου b θα καθοριστεί στο κελί D21.

    ο τύπος εισάγεται στο κελί E4: =$D*$C^A4;

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

Στο σχ. Το 13 δείχνει έναν πίνακα όπου μπορούμε να δούμε τις συναρτήσεις που χρησιμοποιούμε με τις απαραίτητες περιοχές κελιών, καθώς και τύπους.

αξία R 2 που ονομάζεται συντελεστή προσδιορισμού.

Το καθήκον της κατασκευής μιας εξάρτησης παλινδρόμησης είναι να βρεθεί το διάνυσμα των συντελεστών m του μοντέλου (1) στο οποίο ο συντελεστής R παίρνει τη μέγιστη τιμή.

Για να εκτιμηθεί η σημασία του R, χρησιμοποιείται το Fisher's F-test, που υπολογίζεται με τον τύπο

όπου n- μέγεθος δείγματος (αριθμός πειραμάτων).

k είναι ο αριθμός των συντελεστών του μοντέλου.

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

Έτσι, η σημασία του R καθορίζεται όχι μόνο από την τιμή του, αλλά και από την αναλογία μεταξύ του αριθμού των πειραμάτων και του αριθμού των συντελεστών (παραμέτρων) του μοντέλου. Πράγματι, ο λόγος συσχέτισης για n=2 για ένα απλό γραμμικό μοντέλο είναι 1 (μέσω 2 σημείων στο επίπεδο, μπορείτε πάντα να σχεδιάσετε μια ευθεία γραμμή). Ωστόσο, εάν τα πειραματικά δεδομένα είναι τυχαίες μεταβλητές, μια τέτοια τιμή του R θα πρέπει να είναι αξιόπιστη με μεγάλη προσοχή. Συνήθως, για να ληφθεί μια σημαντική R και αξιόπιστη παλινδρόμηση, στοχεύει να διασφαλιστεί ότι ο αριθμός των πειραμάτων υπερβαίνει σημαντικά τον αριθμό των συντελεστών του μοντέλου (n>k).

Για να δημιουργήσετε ένα μοντέλο γραμμικής παλινδρόμησης, πρέπει:

1) ετοιμάστε μια λίστα n σειρών και m στηλών που περιέχουν τα πειραματικά δεδομένα (στήλη που περιέχει την τιμή εξόδου Υπρέπει να είναι είτε πρώτος είτε τελευταίος στη λίστα). για παράδειγμα, ας πάρουμε τα δεδομένα της προηγούμενης εργασίας, προσθέτοντας μια στήλη που ονομάζεται "αριθμός περιόδου", αριθμώντας τους αριθμούς των περιόδων από το 1 έως το 12. (αυτές θα είναι οι τιμές Χ)

2) μεταβείτε στο μενού Δεδομένα/Ανάλυση δεδομένων/Ανάλυση

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

3) στο πλαίσιο διαλόγου "Παλινδρόμηση", ορίστε:

διάστημα εισόδου Y;

διάστημα εισόδου X;

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

4) Κάντε κλικ στο "Ok" και αναλύστε τα αποτελέσματα.