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

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

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

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

Απαιτείται να γίνει πρόβλεψη για το τι τζίρο (Υ) θα έχει το κατάστημα αν έχει αυτόν ή τον άλλο χώρο λιανικής. Προφανώς, η συνάρτηση 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, θα πρέπει να προτιμάτε αυτή με τη μικρότερη τιμή του άθροισμα e i σε όλα τα υπό εξέταση σημεία. Ωστόσο, δεν είναι όλα τόσο απλά, αφού μαζί με τις θετικές αποκλίσεις θα υπάρχουν και αρνητικές.

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

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

Το Excel, όπως γνωρίζετε, έχει μια ενσωματωμένη λειτουργία AutoSum που σας επιτρέπει να υπολογίζετε τις τιμές όλων των τιμών που βρίσκονται στην επιλεγμένη περιοχή. Έτσι, τίποτα δεν θα μας εμποδίσει να υπολογίσουμε την τιμή της παράστασης (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 = 0.

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

Κάποια χαρακτηριστικά

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

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

Λειτουργία ΠΡΟΒΛΕΨΗΣ

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

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

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

· Χρήση της μεθόδου στο Excel

o Ενεργοποίηση του πρόσθετου "Αναζήτηση λύσεων".

o Προβληματικές συνθήκες

o Λύση

Χρησιμοποιώντας τη μέθοδο στο Excel

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

Ενεργοποίηση του πρόσθετου Εύρεση λύσης

Για να χρησιμοποιήσετε το MNC στο Excel, πρέπει να ενεργοποιήσετε το πρόσθετο «Βρίσκοντας λύση», το οποίο είναι απενεργοποιημένο από προεπιλογή.

1. Μεταβείτε στην καρτέλα "Αρχείο".

2. Κάντε κλικ στο όνομα της ενότητας "Επιλογές".

3. Στο παράθυρο που ανοίγει, επιλέξτε την υποενότητα "Πρόσθετα".

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

5. Ανοίγει ένα μικρό παράθυρο. Βάζουμε ένα τικ δίπλα στην παράμετρο «Βρίσκοντας λύση». Κάντε κλικ στο κουμπί "ΕΝΤΑΞΕΙ".

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

Μάθημα:Εύρεση λύσης στο Excel

Συνθήκες του προβλήματος

Ας περιγράψουμε τη χρήση του LSM χρησιμοποιώντας ένα συγκεκριμένο παράδειγμα. Έχουμε δύο σειρές αριθμών ΧΚαι y, η σειρά των οποίων φαίνεται στην παρακάτω εικόνα.

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

Παράλληλα, είναι γνωστό ότι όταν x=0 yεπίσης ίσο 0 . Επομένως, αυτή η εξίσωση μπορεί να περιγραφεί από την εξάρτηση y=nx.

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

Λύση

Ας προχωρήσουμε σε μια περιγραφή της άμεσης εφαρμογής της μεθόδου.

1. Στα αριστερά της πρώτης τιμής Χβάλε έναν αριθμό 1 . Αυτή θα είναι μια κατά προσέγγιση τιμή της τιμής του πρώτου συντελεστή n.

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

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

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

5. Στο ανοιγμένο "Οδηγός λειτουργιών"ψάχνει για είσοδο "SUMMKVARNA". Επιλέξτε το και πατήστε το κουμπί "ΕΝΤΑΞΕΙ".

6. Ανοίγει το παράθυρο ορισμάτων. Στο χωράφι "Array_x" y. Στο χωράφι "Array_y"εισάγετε το εύρος των κελιών στήλης nx. Για να εισαγάγετε τιμές, απλώς τοποθετήστε τον κέρσορα στο πεδίο και επιλέξτε το αντίστοιχο εύρος στο φύλλο. Αφού εισέλθετε, κάντε κλικ στο κουμπί "ΕΝΤΑΞΕΙ".

7. Μεταβείτε στην καρτέλα "Δεδομένα". Στην κορδέλα στην εργαλειοθήκη "Ανάλυση"κάντε κλικ στο κουμπί «Βρίσκοντας λύση».

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

9. Η λύση θα εμφανιστεί στο κελί του συντελεστή n. Αυτή η τιμή θα είναι το ελάχιστο τετράγωνο της συνάρτησης. Εάν το αποτέλεσμα ικανοποιεί τον χρήστη, κάντε κλικ στο κουμπί "ΕΝΤΑΞΕΙ"σε ένα επιπλέον παράθυρο.

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

http://multitest.semico.ru/mnk.htm

Γενικές προμήθειες

Όσο μικρότερος είναι ο αριθμός σε απόλυτη τιμή, τόσο καλύτερη είναι η επιλεγμένη ευθεία (2). Ως χαρακτηριστικό της ακρίβειας επιλογής μιας ευθείας γραμμής (2), μπορούμε να πάρουμε το άθροισμα των τετραγώνων

Οι ελάχιστες προϋποθέσεις για το S θα είναι

	(6)
	(7)

Οι εξισώσεις (6) και (7) μπορούν να γραφτούν ως εξής:

	(8)
	(9)

Από τις εξισώσεις (8) και (9) είναι εύκολο να βρούμε τα a και b από τις πειραματικές τιμές των xi και y i. Η ευθεία (2), που ορίζεται από τις εξισώσεις (8) και (9), ονομάζεται ευθεία που προκύπτει με τη μέθοδο των ελαχίστων τετραγώνων (αυτό το όνομα τονίζει ότι το άθροισμα των τετραγώνων S έχει ελάχιστο). Οι εξισώσεις (8) και (9), από τις οποίες καθορίζεται η ευθεία (2), ονομάζονται κανονικές εξισώσεις.

Μπορείτε να υποδείξετε έναν απλό και γενικό τρόπο σύνθεσης κανονικών εξισώσεων. Χρησιμοποιώντας τα πειραματικά σημεία (1) και την εξίσωση (2), μπορούμε να γράψουμε ένα σύστημα εξισώσεων για τα a και b

y 1 =ax 1 +b,
y 2 =ax 2 +b, ...		(10)
y n = ax n + b,

Ας πολλαπλασιάσουμε την αριστερή και τη δεξιά πλευρά καθεμιάς από αυτές τις εξισώσεις με τον συντελεστή του πρώτου αγνώστου a (δηλαδή με x 1, x 2, ..., x n) και ας προσθέσουμε τις εξισώσεις που προκύπτουν, καταλήγοντας στην πρώτη κανονική εξίσωση (8) .

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

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

υπάρχει σταθερή τιμή και πρέπει να προσδιορίζεται από πειραματικά δεδομένα (1).

Το σύστημα εξισώσεων για το k μπορεί να γραφτεί:

Βρείτε την ευθεία γραμμή (2) χρησιμοποιώντας τη μέθοδο των ελαχίστων τετραγώνων.

Λύση.Βρίσκουμε:

X i =21, y i =46,3, x i 2 =91, x i y i =179,1.

Γράφουμε τις εξισώσεις (8) και (9)91a+21b=179,1,

21a+6b=46,3, από εδώ βρίσκουμε
a=0,98 b=4,3.

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

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

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

Τις περισσότερες φορές, μια γραμμική μορφή χρησιμοποιείται για να περιγράψει τη στατιστική σχέση των χαρακτηριστικών. Η εστίαση στις γραμμικές σχέσεις εξηγείται από τη σαφή οικονομική ερμηνεία των παραμέτρων της, την περιορισμένη διακύμανση των μεταβλητών και το γεγονός ότι στις περισσότερες περιπτώσεις οι μη γραμμικές μορφές σχέσεων μετατρέπονται (με λογάριθμο ή αντικατάσταση μεταβλητών) σε γραμμική μορφή για την εκτέλεση υπολογισμών .
Στην περίπτωση μιας γραμμικής σχέσης ανά ζεύγη, η εξίσωση παλινδρόμησης θα έχει τη μορφή: y i =a+b·x i +u i . Οι παράμετροι a και b αυτής της εξίσωσης εκτιμώνται από στατιστικά δεδομένα παρατήρησης x και y. Το αποτέλεσμα μιας τέτοιας αξιολόγησης είναι η εξίσωση: , όπου , είναι εκτιμήσεις των παραμέτρων a και b , είναι η τιμή του προκύπτοντος χαρακτηριστικού (μεταβλητής) που λαμβάνεται από την εξίσωση παλινδρόμησης (υπολογιζόμενη τιμή).

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

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

Ταξινόμηση μεθόδων ελαχίστων τετραγώνων

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

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

Μαθηματική σημειογραφία για αυτό το πρόβλημα: .
Οι τιμές των y i και x i =1...n είναι γνωστές σε εμάς· πρόκειται για δεδομένα παρατήρησης. Στη συνάρτηση S αντιπροσωπεύουν σταθερές. Οι μεταβλητές σε αυτή τη συνάρτηση είναι οι απαιτούμενες εκτιμήσεις των παραμέτρων - , . Για να βρούμε το ελάχιστο μιας συνάρτησης δύο μεταβλητών, είναι απαραίτητο να υπολογίσουμε τις μερικές παραγώγους αυτής της συνάρτησης για κάθε μία από τις παραμέτρους και να τις εξισώσουμε με μηδέν, δηλ. .
Ως αποτέλεσμα, λαμβάνουμε ένα σύστημα 2 κανονικών γραμμικών εξισώσεων:
Επιλύοντας αυτό το σύστημα, βρίσκουμε τις απαιτούμενες εκτιμήσεις παραμέτρων:

Η ορθότητα του υπολογισμού των παραμέτρων της εξίσωσης παλινδρόμησης μπορεί να ελεγχθεί συγκρίνοντας τα ποσά (μπορεί να υπάρχει κάποια απόκλιση λόγω στρογγυλοποίησης των υπολογισμών).
Για να υπολογίσετε εκτιμήσεις παραμέτρων, μπορείτε να δημιουργήσετε τον Πίνακα 1.
Το πρόσημο του συντελεστή παλινδρόμησης b δείχνει την κατεύθυνση της σχέσης (αν b >0, η σχέση είναι άμεση, αν b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Τυπικά, η τιμή της παραμέτρου a είναι η μέση τιμή του y με x ίση με μηδέν. Εάν το χαρακτηριστικό-παράγοντας δεν έχει και δεν μπορεί να έχει μηδενική τιμή, τότε η παραπάνω ερμηνεία της παραμέτρου α δεν έχει νόημα.

Αξιολόγηση της εγγύτητας της σχέσης μεταξύ των χαρακτηριστικών πραγματοποιείται με χρήση του συντελεστή συσχέτισης γραμμικού ζεύγους - r x,y. Μπορεί να υπολογιστεί χρησιμοποιώντας τον τύπο: . Επιπλέον, ο συντελεστής συσχέτισης γραμμικού ζεύγους μπορεί να προσδιοριστεί μέσω του συντελεστή παλινδρόμησης b: .
Το εύρος των αποδεκτών τιμών του συντελεστή συσχέτισης γραμμικού ζεύγους είναι από –1 έως +1. Το πρόσημο του συντελεστή συσχέτισης δείχνει την κατεύθυνση της σχέσης. Εάν r x, y >0, τότε η σύνδεση είναι άμεση. αν r x, y<0, то связь обратная.
Εάν αυτός ο συντελεστής είναι κοντά στη μονάδα σε μέγεθος, τότε η σχέση μεταξύ των χαρακτηριστικών μπορεί να ερμηνευθεί ως αρκετά στενή γραμμική. Αν η ενότητα του είναι ίση με ένα ê r x, y ê =1, τότε η σχέση μεταξύ των χαρακτηριστικών είναι συναρτησιακή γραμμική. Εάν τα χαρακτηριστικά x και y είναι γραμμικά ανεξάρτητα, τότε τα r x,y είναι κοντά στο 0.
Για να υπολογίσετε τα r x,y, μπορείτε επίσης να χρησιμοποιήσετε τον Πίνακα 1.

Για να αξιολογήσετε την ποιότητα της προκύπτουσας εξίσωσης παλινδρόμησης, υπολογίστε τον θεωρητικό συντελεστή προσδιορισμού - R 2 yx:

,
όπου d 2 είναι η διακύμανση του y που εξηγείται από την εξίσωση παλινδρόμησης.
e 2 - υπολειπόμενη (ανεξήγητη από την εξίσωση παλινδρόμησης) διακύμανση του y;
s 2 y - συνολική (συνολική) διακύμανση του y.
Ο συντελεστής προσδιορισμού χαρακτηρίζει την αναλογία διακύμανσης (διασπορά) του προκύπτοντος χαρακτηριστικού y που εξηγείται με παλινδρόμηση (και, κατά συνέπεια, παράγοντα x) στη συνολική διακύμανση (διασπορά) y. Ο συντελεστής προσδιορισμού R 2 yx παίρνει τιμές από 0 έως 1. Κατά συνέπεια, η τιμή 1-R 2 yx χαρακτηρίζει το ποσοστό διακύμανσης y που προκαλείται από την επίδραση άλλων παραγόντων που δεν λαμβάνονται υπόψη στο μοντέλο και τα σφάλματα προδιαγραφών.
Με ζευγαρωμένη γραμμική παλινδρόμηση, R 2 yx =r 2 yx.

Μέθοδος ελαχίστων τετραγώνων (LSM)

Ένα σύστημα m γραμμικών εξισώσεων με n αγνώστους έχει τη μορφή:

Τρεις περιπτώσεις είναι δυνατές: m n. Η περίπτωση που m=n εξετάστηκε στις προηγούμενες παραγράφους. Όταν μ

Εάν m>n και το σύστημα είναι συνεπές, τότε ο πίνακας Α έχει τουλάχιστον m - n γραμμικά εξαρτώμενες σειρές. Εδώ η λύση μπορεί να ληφθεί επιλέγοντας n οποιεσδήποτε γραμμικά ανεξάρτητες εξισώσεις (εάν υπάρχουν) και εφαρμόζοντας τον τύπο X = A -1 CV, δηλαδή ανάγοντας το πρόβλημα σε ένα προηγουμένως λυμένο πρόβλημα. Σε αυτή την περίπτωση, η λύση που προκύπτει θα ικανοποιεί πάντα τις υπόλοιπες εξισώσεις m - n.

Ωστόσο, όταν χρησιμοποιείτε υπολογιστή, είναι πιο βολικό να χρησιμοποιείτε μια πιο γενική προσέγγιση - τη μέθοδο των ελαχίστων τετραγώνων.

Μέθοδος αλγεβρικών ελαχίστων τετραγώνων

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

ελαχιστοποιώντας τον Ευκλείδειο κανόνα

Τσεκούρι? σι? >πλη. (1.2)

Ανάλυση πειραματικών δεδομένων

Ας εξετάσουμε ένα πείραμα κατά το οποίο σε στιγμές του χρόνου

Για παράδειγμα, μετριέται η θερμοκρασία Q(t). Αφήστε τα αποτελέσματα της μέτρησης να καθορίζονται από έναν πίνακα

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

P(t) = + + + ... +,

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

Gauss algebraic exel προσέγγιση

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

Αν αντικαταστήσουμε το P(t) με την έκφρασή του, παίρνουμε

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

Εάν εισάγετε τον πίνακα m × n A = (), i = 1, 2..., m; j = 1, 2, ..., n, όπου

I = 1, 2..., m; j = 1, 2, ..., n,

τότε η γραπτή ισότητα θα πάρει τη μορφή

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

Για έναν μεταφερόμενο πίνακα, μια παρόμοια σχέση μοιάζει με αυτό

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

Σε μορφή μήτρας αυτή η ισότητα μπορεί να ξαναγραφτεί ως

A T x=A T B (1.3)

Εδώ το Α είναι ένας ορθογώνιος πίνακας m×n. Επιπλέον, σε προβλήματα προσέγγισης δεδομένων, κατά κανόνα, m > n. Η εξίσωση (1.3) ονομάζεται κανονική εξίσωση.

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

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

2Α Τ Β + 2Α Τ Αξ

και επομένως η λύση πρέπει να ικανοποιεί το σύστημα των γραμμικών εξισώσεων

(A T A)x = (A T B).

Αυτές οι εξισώσεις ονομάζονται κανονικές εξισώσεις. Εάν το A είναι m× n πίνακας, τότε το A>A - n × n είναι ένας πίνακας, δηλ. Ο πίνακας μιας κανονικής εξίσωσης είναι πάντα ένας τετραγωνικός συμμετρικός πίνακας. Επιπλέον, έχει την ιδιότητα της θετικής οριστικότητας με την έννοια ότι (A>Ax, x) = (Ax, Ax) ? 0.

Σχόλιο. Μερικές φορές η λύση μιας εξίσωσης της μορφής (1.3) ονομάζεται λύση του συστήματος Ax = B, όπου το A είναι ένας ορθογώνιος πίνακας m × n (m > n) χρησιμοποιώντας τη μέθοδο των ελαχίστων τετραγώνων.

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

MNC στο Excel

Ο παρακάτω αλγόριθμος για την υλοποίηση του OLS στο Excel προϋποθέτει ότι όλα τα αρχικά δεδομένα είναι ήδη γνωστά. Πολλαπλασιάζουμε και τις δύο πλευρές της εξίσωσης πίνακα AЧX=B του συστήματος στα αριστερά με τον μετατιθέμενο πίνακα του συστήματος А Т:

A T AX=A T B

Στη συνέχεια πολλαπλασιάζουμε και τις δύο πλευρές της εξίσωσης στα αριστερά με τον πίνακα (A T A) -1. Εάν υπάρχει αυτός ο πίνακας, τότε ορίζεται το σύστημα. Λαμβάνοντας υπ 'όψιν ότι

(A T A) -1 *(A T A)=E, παίρνουμε

X=(A T A) -1 A T B.

Η εξίσωση του πίνακα που προκύπτει είναι μια λύση σε ένα σύστημα m γραμμικών εξισώσεων με n αγνώστους για m>n.

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

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

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

Αποτελέσματα υπολογισμού:

Το απαιτούμενο διάνυσμα X βρίσκεται στην περιοχή E11:E12.

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

1. MOBR - επιστρέφει τον αντίστροφο πίνακα για τον πίνακα που είναι αποθηκευμένος στον πίνακα.

Σύνταξη: MOBR(πίνακας).

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

2. MULTIPULT - επιστρέφει το γινόμενο των πινάκων (οι πίνακες αποθηκεύονται σε πίνακες). Το αποτέλεσμα είναι ένας πίνακας με τον ίδιο αριθμό σειρών με τον πίνακα1 και τον ίδιο αριθμό στηλών με τον πίνακα2.

Σύνταξη: MULTIPLE(array1, array2).

Ο πίνακας1, ο πίνακας2 είναι πολλαπλασιάσιμοι πίνακες.

Αφού εισαγάγετε μια συνάρτηση στο επάνω αριστερό κελί μιας περιοχής πίνακα, επιλέξτε τον πίνακα, ξεκινώντας από το κελί που περιέχει τον τύπο, πατήστε F2 και, στη συνέχεια, πατήστε CTRL+SHIFT+ENTER.

3. ΜΕΤΑΦΟΡΑ - μετατρέπει ένα κατακόρυφο σύνολο κελιών σε οριζόντιο ή το αντίστροφο. Ως αποτέλεσμα της χρήσης αυτής της συνάρτησης, εμφανίζεται ένας πίνακας με τον αριθμό των σειρών ίσο με τον αριθμό των στηλών του αρχικού πίνακα και τον αριθμό των στηλών ίσο με τον αριθμό των γραμμών του αρχικού πίνακα.

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

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

LINEST(Τιμές_y; x-τιμές; Κωνστ; στατιστική),

Τιμές_y- πίνακας τιμών y,

x-τιμές- προαιρετικός πίνακας τιμών Χ, εάν πίνακας Χπαραλείπεται, υποτίθεται ότι πρόκειται για πίνακα (1;2;3;...) ίδιου μεγέθους με Τιμές_y,

Κωνστ- μια boolean τιμή που υποδεικνύει εάν απαιτείται η σταθερά σιήταν ίσο με 0. Αν Κωνστέχει το νόημα ΑΛΗΘΗΣή παραλείπεται, λοιπόν σιυπολογίζεται με τον συνήθη τρόπο. Αν το επιχείρημα Κωνστείναι ΛΑΘΟΣ, λοιπόν σιυποτίθεται ότι είναι 0 και οι τιμές έναεπιλέγονται έτσι ώστε να εκπληρώνεται η σχέση y=ax.

Στατιστικήείναι μια τιμή boolean που υποδεικνύει εάν απαιτείται να επιστραφούν πρόσθετα στατιστικά στοιχεία παλινδρόμησης. Αν το επιχείρημα Στατιστικήέχει το νόημα ΑΛΗΘΗΣ, μετά η συνάρτηση LINESTεπιστρέφει πρόσθετα στατιστικά στοιχεία παλινδρόμησης. Αν το επιχείρημα Στατιστικήέχει το νόημα ΨΕΜΑή παραλείπεται, τότε η συνάρτηση LINESTεπιστρέφει μόνο τον συντελεστή ένακαι σταθερή σι.

Πρέπει να θυμόμαστε ότι το αποτέλεσμα των συναρτήσεων LINEST()είναι ένα σύνολο τιμών - ένας πίνακας.

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

CORREL(Πίνακας 1;Πίνακας 2),

επιστρέφοντας τις τιμές του συντελεστή συσχέτισης, όπου Πίνακας 1- πίνακας τιμών y, Πίνακας 2- πίνακας τιμών Χ. Πίνακας 1Και Πίνακας 2πρέπει να έχει το ίδιο μέγεθος.

ΠΑΡΑΔΕΙΓΜΑ 1. Εθισμός y(Χ) παρουσιάζεται στον πίνακα. Χτίζω γραμμή παλινδρόμησηςκαι υπολογίστε συντελεστής συσχέτισης.

y		0.5		1.5		2.5		3.5
Χ		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

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

Για τον υπολογισμό των τιμών των συντελεστών παλινδρόμησης ΕΝΑΚαι σιεπιλέξτε τα κελιά A7:B7,Πάμε στον οδηγό συνάρτησης και στην κατηγορία Στατιστικόςεπιλέξτε μια λειτουργία LINEST. Ας συμπληρώσουμε το πλαίσιο διαλόγου που εμφανίζεται όπως φαίνεται στο Σχ. 3 και πατήστε Εντάξει.

Ως αποτέλεσμα, η υπολογισμένη τιμή θα εμφανίζεται μόνο στο κελί Α6(Εικ. 4). Για να εμφανιστεί η τιμή στο κελί Β6πρέπει να εισέλθετε στη λειτουργία επεξεργασίας (πλήκ F2)και, στη συνέχεια, πατήστε το συνδυασμό πλήκτρων CTRL+SHIFT+ENTER.

Να υπολογίσετε την τιμή του συντελεστή συσχέτισης σε ένα κελί Γ6εισήχθη ο ακόλουθος τύπος:

C7=CORREL(B3:J3;B2:J2).

Γνωρίζοντας τους συντελεστές παλινδρόμησης ΕΝΑΚαι σιας υπολογίσουμε τις τιμές των συναρτήσεων y=τσεκούρι+σιγια δεδομένο Χ. Για να γίνει αυτό, εισάγουμε τον τύπο

B5=$A$7*B2+$B$7

και αντιγράψτε το στην περιοχή C5:J5(Εικ. 5).

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

Ρύζι. 6

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

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

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

4.2. Χρησιμοποιώντας μια γραμμή τάσης.

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

ΠΑΡΑΔΕΙΓΜΑ 2. Ως αποτέλεσμα του πειράματος, προσδιορίστηκε μια ορισμένη εξάρτηση σε πίνακα.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Επιλέξτε και κατασκευάστε μια κατά προσέγγιση εξάρτηση. Κατασκευάστε γραφήματα πινάκων και επιλεγμένων αναλυτικών εξαρτήσεων.

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

Ας δούμε αναλυτικά αυτή τη διαδικασία. Ας εισαγάγουμε τα αρχικά δεδομένα στο φύλλο εργασίας και ας σχεδιάσουμε τα πειραματικά δεδομένα. Στη συνέχεια, επιλέξτε τα πειραματικά σημεία στο γράφημα, κάντε δεξί κλικ και χρησιμοποιήστε την εντολή Προσθήκημεγάλο γραμμή τάσης(Εικ. 10).

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

Η πρώτη καρτέλα (Εικ. 11) αυτού του παραθύρου υποδεικνύει τον τύπο της κατά προσέγγιση εξάρτησης.

Στο δεύτερο (Εικ. 12) προσδιορίζονται οι παράμετροι κατασκευής:

· όνομα της κατά προσέγγιση εξάρτησης.

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

αν θα φανεί το σημείο τομής μιας καμπύλης με μια ευθεία y=const;

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

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

Ας επιλέξουμε ένα πολυώνυμο δεύτερου βαθμού ως εξάρτηση κατά προσέγγιση (Εικ. 11) και ας εμφανίσουμε την εξίσωση που περιγράφει αυτό το πολυώνυμο σε ένα γράφημα (Εικ. 12). Το διάγραμμα που προκύπτει φαίνεται στο Σχ. 13.

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

γραμμικός y=a∙x+σι,

λογαριθμική y=a∙ln(Χ)+σι,

· εκθετική y=a∙e β,

· ναρκωτικό y=a∙x β,

πολυώνυμος y=a∙x 2 +b∙x+ντο, y=a∙x 3 +b∙x 2 +c∙x+dκαι ούτω καθεξής, μέχρι πολυώνυμο 6ου βαθμού συμπεριλαμβανομένου,

· γραμμικό φιλτράρισμα.

4.3. Χρησιμοποιώντας ένα μπλοκ επίλυσης

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

ΠΑΡΑΔΕΙΓΜΑ 3. Ως αποτέλεσμα του πειράματος, ελήφθη η εξάρτηση z(t), που παρουσιάζεται στον πίνακα

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Επιλέξτε συντελεστές εξάρτησης Z(t)=Στο 4 +Bt 3 +Ct 2 +Dt+Kμέθοδος ελαχίστων τετραγώνων.

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

Ας εξετάσουμε τη διαδικασία επίλυσης του προβλήματος βελτιστοποίησης (Εικ. 14).

Αφήστε τις αξίες ΕΝΑ, ΣΕ, ΜΕ, ρεΚαι ΠΡΟΣ ΤΗΝαποθηκεύονται σε κύτταρα A7:E7. Ας υπολογίσουμε τις θεωρητικές τιμές της συνάρτησης Ζ(t)=Στο 4 +Bt 3 +Ct 2 +Dt+Kγια δεδομένο t(B2:J2). Για να γίνει αυτό, στο κελί Β4εισάγετε την τιμή της συνάρτησης στο πρώτο σημείο (κελί Β2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Ας αντιγράψουμε αυτόν τον τύπο στο εύρος C4:J4και να πάρετε την αναμενόμενη τιμή της συνάρτησης στα σημεία των οποίων οι τετμημένες είναι αποθηκευμένες στα κελιά B2:J2.

Στο κελί Β5Ας εισαγάγουμε έναν τύπο που υπολογίζει το τετράγωνο της διαφοράς μεταξύ των πειραματικών και των υπολογισμένων σημείων:

B5=(B4-B3)^2,

και αντιγράψτε το στην περιοχή C5:J5. Σε ένα κελί F7θα αποθηκεύσουμε το συνολικό τετράγωνο σφάλμα (10). Για να το κάνετε αυτό, εισαγάγετε τον τύπο:

F7 = SUM(B5:J5).

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

Το αποτέλεσμα του μπλοκ απόφασης θα βγει στα κελιά A7:E7τιμές παραμέτρωνλειτουργίες Ζ(t)=Στο 4 +Bt 3 +Ct 2 +Dt+K. Σε κύτταρα B4:J4παίρνουμε αναμενόμενη τιμή συνάρτησηςστα σημεία εκκίνησης. Σε ένα κελί F7θα αποθηκευτεί συνολικό τετραγωνικό σφάλμα.

Μπορείτε να εμφανίσετε πειραματικά σημεία και μια προσαρμοσμένη γραμμή σε μια περιοχή γραφικών επιλέγοντας μια περιοχή B2:J4, κλήση Οδηγός γραφήματοςκαι μετά μορφοποίηση εμφάνισηέλαβε γραφήματα.

Ρύζι. 17 εμφανίζει το φύλλο εργασίας MS Excel μετά την εκτέλεση των υπολογισμών.

5. ΑΝΑΦΟΡΕΣ

1. Alekseev E.R., Chesnokova O.V., Επίλυση προβλημάτων υπολογιστικών μαθηματικών στα πακέτα Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596 σελ. :il. -(Φροντιστήριο)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, επίλυση μηχανικών και μαθηματικών προβλημάτων. –Μ., BINOM, 2008.–260 σελ.

3. Berezin I.S., Zhidkov N.P., Methods of calculations.– M.: Nauka, 1966. – 632 p.

4. Garnaev A.Yu., Χρήση MS EXCEL και VBA στα οικονομικά και τα χρηματοοικονομικά. – Αγία Πετρούπολη: BHV - Petersburg, 1999.–332 p.

5. Demidovich B.P., Maron I.A., Shuvalova V.Z., Numerical method of analysis.– M.: Nauka, 1967. – 368 p.

6. Korn G., Korn T., Εγχειρίδιο μαθηματικών για επιστήμονες και μηχανικούς – M., 1970, 720 p.

7. Alekseev E.R., Chesnokova O.V. Οδηγίες για την εκτέλεση εργαστηριακών εργασιών στο MS EXCEL. Για μαθητές όλων των ειδικοτήτων. Donetsk, DonNTU, 2004. 112 p.