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

Η μέθοδος των ελαχίστων τετραγώνων είναι μια μαθηματική διαδικασία για την επαγωγή μιας γραμμικής στοίχισης, καθώς θα ταίριαζε με την μεγαλύτερη ακρίβεια σε ένα σύνολο δύο σειρών αριθμών. Metoyu zastosuvannya tsgogo τρόπο є minіmіzatsіya zagalnoї τετραγωνική συγγνώμη. Το Excel διαθέτει εργαλεία, για τη βοήθεια των οποίων μπορείτε να παγώσετε τη μέθοδο κατά τον υπολογισμό. Ας βρούμε πώς να πολεμήσουμε.

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

Uvіmknennya nadbudovi "Λύση αναζήτησης"

Για να κερδίσετε MNC στο Excel, είναι απαραίτητο να αυξήσετε το nadbudova "Αναζήτηση λύσης", γιακ για zamovchuvannyam vimknen.

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

Πλύνετε τον διαχειριστή

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

Ο πιο ακριβής τρόπος για να περιγράψετε τη συνάρτηση που ακολουθεί είναι:

Οταν δεις x=0 y tezh ένα 0 . Επομένως, ο σκοπός μπορεί να περιγραφεί ως αγρανάπαυση y=nx .

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

Λύση

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

Όπως ο Bachimo, η μέθοδος zastosuvannya των ελαχίστων τετραγώνων έχει να κάνει με μια πτυσσόμενη μαθηματική διαδικασία. Δείξαμε το її y dії στον πιο απλό πισινό και το іsnuyet πλούσια διπλωμένο vpadki. Vtіm, іnstrumentarіy Microsoft Excel κλικ απλοποιεί στο μέγιστο τον υπολογισμό, scho viroblyayutsya.

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

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

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

Ο πιο συνηθισμένος τρόπος για να περιγράψετε μια στατιστική σύνδεση είναι να χρησιμοποιήσετε μια γραμμική φόρμα. Απαντήστε στο Liniyny Zv'yazka εξηγήστε τις παραμέτρους του Οικονομικού izonter, τις dumpy serinas і Tim, την Zdabyl Nelinіnі -παλιά μορφή στη viconnya του rosrahunhn, για να ανανεώσετε (το έντερο του Logarithmunsyi) στο Lyni).
Στην περίπτωση ενός γραμμικού ζευγαρωμένου συνδέσμου, η παλινδρόμηση θα είναι ίση στο μέλλον: y i =a+b·x i +u i . Παράμετροι αυτό ίσοΤα α και β αξιολογούνται σύμφωνα με τα στοιχεία της στατιστικής προειδοποίησης x και y. Το αποτέλεσμα μιας τέτοιας αξιολόγησης είναι ίσο: , απο-εκτιμήσεις των παραμέτρων a και b - την τιμή του ενεργού πρόσημου (αλλαγή), otrimane για την ίση παλινδρόμηση (τιμή rozrahunkov).

Ο πιο συνηθισμένος τρόπος αξιολόγησης των παραμέτρων του vicorist Μέθοδος ελαχίστων τετραγώνων (LSM).
Η μέθοδος των ελαχίστων τετραγώνων δίνει τις καλύτερες (πιθανές, αποτελεσματικές και αμερόληπτες) εκτιμήσεις των παραμέτρων της εξίσωσης παλινδρόμησης. Αλλά μόνο σε εκείνο το vipadka, λες και τα τραγούδια αλλάζουν γνώμη για το vipadical μέλος (u) ότι το ανεξάρτητο αλλάζει (x) (div. change their mind MNK).

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

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

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

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

Μαθηματική καταγραφή αυτής της εργασίας: .
Οι τιμές του y i x i =1...n είναι γνωστές σε εμάς, δίνεται μια προειδοποίηση. Η συνάρτηση S της βρώμας έχει σταθερές. Οι αλλαγές για αυτήν τη συνάρτηση είναι εκτιμήσεις παραμέτρων - , . Προκειμένου να προσδιοριστεί το ελάχιστο των συναρτήσεων δύο αντικαταστάσεων, είναι απαραίτητο να υπολογιστούν οι ιδιωτικές παρόμοιες λειτουργίες των δερματικών αρχών και να εξισωθούν με το μηδέν. .
Ως αποτέλεσμα, παίρνουμε το σύστημα από δύο κανονικά γραμμικά ποτάμια:
Σύστημα Virishyuchi tsyu, γνωρίζουμε τις εκτιμήσεις shukani των parametrіv:

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

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

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

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

4.1. Wikoristanya vbudovannyh funktsіy

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

γραμμικός(Τιμή_υ; Τιμή_x; Κωνστ; στατιστική),

Τιμή_υ- πίνακας τιμών y,

Τιμή_x- τιμή πίνακα neob'visual Χ, σαν πίνακας Χπαραλείψεις, τότε μεταφέρεται ότι ο πίνακας (1; 2; 3; ...) έχει το ίδιο μέγεθος με το i Τιμή_υ,

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

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

Είναι απαραίτητο να θυμόμαστε ότι το αποτέλεσμα των συναρτήσεων ΓΡΑΜΜΗ()є απρόσωπη τιμή πίνακα.

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

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

στρέψτε την τιμή του συντελεστή συσχέτισης, de Πίνακας 1- πίνακας τιμών y, Πίνακας 2- πίνακας τιμών Χ. Πίνακας 1і Πίνακας 2 mayut buti μόνος.

ΠΟΝΤΟΣ 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,κατεβείτε στον κύριο των λειτουργιών και στην κατηγορία Στατιστικόςεπιλέξτε μια λειτουργία γραμμικός. Αξίζει να θυμηθούμε το παράθυρο διαλόγου, το οποίο αποδείχθηκε ότι ήταν όπως φαίνεται στο σχ. 3 πιέζω Εντάξει.

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

Για rozrahunka η τιμή του συντελεστή συσχέτισης στο διαφημιστικό Γ6εισήχθη ο ακόλουθος τύπος:

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

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

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

και skopyuєmo її στην περιοχή С5:J5(Εικ. 5).

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

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

Ο τύπος της γραμμής, το χρώμα її και το tovshchina μπορούν να αλλάξουν με μια επιθετική κατάταξη. Δείτε τη γραμμή στο διάγραμμα, κάντε δεξί κλικ στο κουμπί του ποντικιού και επιλέξτε την εντολή από το μενού περιβάλλοντος Μορφή σειράς δεδομένων…Εγκατάσταση Dalі zrobiti, για παράδειγμα, oskіlki που φαίνεται στο σχ. οκτώ.

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

4.2. Γραμμή νίκης στην τάση.

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

ΠΟΝΤΟΣ 2. Ως αποτέλεσμα του πειράματος, ανατέθηκε μια κατάθεση σε πίνακα στο deak.

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).

Ο διάλογος Vіkno, εμφανίστηκε το scho, σας επιτρέπει να προκαλέσετε μια κατά προσέγγιση αγρανάπαυση.

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

Από την άλλη (Εικ. 12), ορίζονται οι ακόλουθες παράμετροι:

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

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

· Δείξτε το εγκάρσιο σημείο μιας καμπύλης πίσω από μια ευθεία γραμμή y=const;

· Εμφάνιση της κατά προσέγγιση συνάρτησης στα διαγράμματα nі (παράμετρος δείχνει ευθυγράμμιση στα διαγράμματα).

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

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

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

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

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

εκθετικός y=a∙eb,

· στατικό y=α 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).

Ενημέρωσέ με ΑΛΛΑ, Στο, W, ρεі Πριναποθηκεύστε στη μέση Α7:Ε7. Εξερευνήστε τη θεωρητική αξία της συνάρτησης Ζ(t)=At4+Bt3+Ct2+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εισάγουμε έναν τύπο που υπολογίζει το τετράγωνο της διαφοράς μεταξύ των πειραματικών και των σημείων rozrachunk:

B5=(B4-B3)^2,

και skopyuєmo її στην περιοχή С5:J5. Στη μέση F7αποθηκεύστε τη συνολική τετραγωνική συγγνώμη (10). Για το οποίο εισάγουμε τον τύπο:

F7 = SUM(B5: J5).

Βιαστείτε σαν ομάδα Service®Search Solutionότι virіshimo το έργο της βελτιστοποίησης χωρίς obmezhen. Αξίζει να θυμάστε την κατάταξη του πεδίου εισαγωγής στο παράθυρο διαλόγου που φαίνεται στην εικ. 14 και πατήστε το κουμπί Βικονάτη. Εάν βρεθεί η απόφαση, θα εμφανιστεί στο παράθυρο, που απεικονίζεται στην εικ. δεκαπέντε.

Αποτέλεσμα της δουλειάς του οραματικού μπλοκ θα είναι η εμφάνιση στους επιτρόπους Α7:Ε7τιμή παραμέτρουλειτουργίες Ζ(t)=At4+Bt3+Ct2+Dt+K. Στη μέση B4:J4λαμβάνονται βαθμολόγηση τιμής συνάρτησηςστα σημεία εξόδου. Στη μέση F7θα είσαι ασφαλής sumarna τετράγωνη συγγνώμη.

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

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

Η μέθοδος των ελαχίστων τετραγώνων (LSM) βρίσκεται στο πεδίο της ανάλυσης παλινδρόμησης. Vіn mає απρόσωπη zastosuvan, oskolki επιτρέπουν zdіysnyuvat zdіysnyuvat priblizhennya izmenenâ ї ї ї ї ї ї ії іshmi πιο απλή. Τα πολυεθνικά μπορεί να εμφανίζονται υπεργλωσσικά κοκκινωπά κατά τη διάρκεια της ανάλυσης των προειδοποιήσεων και να κερδίζουν ενεργά για να αξιολογήσουν ορισμένες τιμές για τα αποτελέσματα του vimiryuvan іnshih, προκειμένου να εκδικηθούν τη χάρη του vipadkovі. Από αυτά τα στατιστικά στοιχεία ξέρετε πώς να εφαρμόσετε τον υπολογισμό με τη μέθοδο των ελαχίστων τετραγώνων στο Excel.

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

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

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

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

Dekіlka sіv σχετικά με την ορθότητα των διακοπών, που είναι νικηφόρες για μετάδοση

Ας πούμε, μπορούμε να κάνουμε ένα τραπέζι, θα πάρω λεφτά για n μαγαζιά.

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

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

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

Προφανώς, είναι δυνατό να επιλέξετε έναν πλούσιο όρο υψηλού επιπέδου, αλλά μια τέτοια επιλογή δεν είναι μόνο σημαντική για εφαρμογή, αλλά απλά λανθασμένη, επειδή δεν αντιπροσωπεύει την κύρια τάση, αλλά είναι απαραίτητο να την αποδείξετε. Οι πιο λογικές λύσεις είναι να κοιτάξουμε κατευθείαν στο y = ax + b, που είναι η πλησιέστερη προσέγγιση των πειραματικών δεδομένων, και πιο συγκεκριμένα, οι συντελεστές είναι a και b.

Βαθμός ευστοχίας

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

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

Μπορείτε να αλλάξετε την προσφορά, vikoristovuyuchi ενότητες vіdkhilen ή їх τετράγωνα. Η υπόλοιπη μέθοδος είναι να γεμίσετε το μεγαλύτερο πλάτος. Vіn vikoristovuєtsya σε πλούσιες περιοχές, συμπεριλαμβανομένης της ανάλυσης παλινδρόμησης (στο Excel, αυτή η υλοποίηση έχει δημιουργηθεί για τη βοήθεια δύο λειτουργιών) και είναι από καιρό αποτελεσματική.

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

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

Η μαθηματική σημειογραφία μπορεί να μοιάζει με:

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

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

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

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

Virishuichi її, για παράδειγμα, με τη μέθοδο του Cramer, παίρνουμε ένα ακίνητο σημείο με ορισμένους συντελεστές a* και b*. Tse і є minіmum, tobto για μεταφορά, τι είδους προϊόντα θα υπάρχουν στο κατάστημα με ένα μεγάλο τετράγωνο, pіdіyde ευθεία y = a * x + b * , το οποίο είναι ένα μοντέλο παλινδρόμησης για τον πισινό, για το οποίο μπορεί να βρεθεί κάποιος. Προφανώς δεν είναι δυνατό να γνωρίζουμε το ακριβές αποτέλεσμα, αλλά επιπλέον υπάρχουν δηλώσεις για όσους εξοφλούν την αγορά με πίστωση σε κατάστημα συγκεκριμένης περιοχής.

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

Το "Excel" έχει μια συνάρτηση για την ανάλυση της αξίας του MNC. Ο Vaughn μπορεί να μοιάζει με αυτό: "ΤΑΣΗ" (στην περίπτωση της τιμής Y, στην περίπτωση της τιμής X, της νέας τιμής X, της συντ.). Zastosuєmo τον τύπο για τη διάδοση του MNC Excel στον πίνακά μας.

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

• εύρος τιμής v_domih για το Y (σε χρονικά δεδομένα για την κυκλοφορία του εμπορεύματος).
• εύρος x 1, … x n, δηλαδή το μέγεθος της περιοχής συναλλαγών.
• και vіdomі, і іnоvіdomі τιμή x, για την οποία είναι απαραίτητο να z'yasuvati rozmіr commodities (πληροφορίες σχετικά με το їhnє roztashuvannya για την αψίδα εργασίας. μακριά).

Επιπλέον, ο τύπος έχει μια λογική αλλαγή "Konst". Εάν θέλετε να εισαγάγετε το πεδίο 1 στο δεύτερο πεδίο, είναι σημαντικό να πραγματοποιηθεί ο ακόλουθος υπολογισμός, επιπλέον, ότι b = 0.

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

Πράξεις ιδιαιτερότητας

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

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

Λειτουργία "RETELL"

Η ανάλυση παλινδρόμησης στο Excel υλοποιείται για έναν αριθμό πρόσθετων συναρτήσεων. Ένα από αυτά ονομάζεται «Πρόβλεψη». Είναι παρόμοιο με το "TENDENCIES", οπότε μπορείτε να δείτε το αποτέλεσμα που υπολογίζεται με τη μέθοδο των ελαχίστων τετραγώνων. Ωστόσο, μόνο για ένα Χ, για κάποια άγνωστη τιμή του Y.

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

Η μέθοδος των ελαχίστων τετραγώνων (LSM) βασίζεται στην ελαχιστοποίηση του αθροίσματος των τετραγώνων της δεδομένης συνάρτησης στα δεδομένα. Αυτό το στατιστικό έχει κατά προσέγγιση δεδομένα για μια πρόσθετη γραμμική συνάρτησηy = ένα Χ + σι .

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

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

• Γραμμική: y=ax+b (άρθρο tsya)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+c
• : y=ax 2 +bx+c

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

Γραμμική αγρανάπαυση

Μας καλεί το κάλεσμα της 2ης αλλαγής Χі y. Є εισαγωγή, sho yκατάθεση σε Χσύμφωνα με τον γραμμικό νόμο y = τσεκούρι + σι. Προκειμένου να προσδιοριστούν οι παράμετροι της αλληλεπίδρασης του τελευταίου ελέγχου της προσοχής: για την τιμή του δέρματος του x i, πραγματοποιήθηκε δοκιμή του y (επισυνάπτεται αρχείο div.). Προφανώς, ας πούμε 20 ζεύγη τιμής (х i; y i).

Σημείωση: Yakshcho krok αλλαγή από Χ postiyny, μετά για αφύπνιση rozsiyuvannya διαγράμματαείναι δυνατό να φιτίλι, ακόμη και αν όχι, είναι απαραίτητο να φιτίλι τον τύπο των διαγραμμάτων Κράπκοβα .

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

Ως τέτοιο κριτήριο vikoristovuemo viraz:

de ŷ Εγώ = ένα * x i + σι ; n – αριθμός ζευγών τιμών (για χρόνους n=20)

Το Vishchezgadan viraz είναι το άθροισμα των τετραγώνων μεταξύ των φυλαγμένων τιμών y i ŷ i i που συχνά συμβολίζονται ως SSE ( άθροισμα του εις το τετραγωνο Σφάλματα (Υπολείμματα), το άθροισμα των τετραγώνων των συγχωροχάρτων (πλεονασμάτων)) .

Μέθοδος ελαχίστων τετραγώνων polagaє έχουν μια τέτοια γραμμή ŷ = τσεκούρι + σι, Για τι vyshchezgadane viraz nabuvaє ελάχιστη αξία.

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

Είναι σημαντικό το άθροισμα των τετραγώνων να είναι μικρότερο από το άθροισμα των τετραγώνων, αλλά η γραμμή είναι πιο πιθανό να προσεγγίσει τα πραγματικά δεδομένα και μπορεί να είναι δυνατό να χρησιμοποιηθεί μεγαλύτερη απόσταση για να προβλέψει την τιμή του y με τη μορφή ενός αλλαγή. Συνειδητοποίησα ότι είναι αλήθεια ότι δεν υπάρχει αμοιβαία σύνδεση μεταξύ των μεταβλητών, δεν υπάρχουν μη γραμμικές κλήσεις, τότε το MNC θα συνεχίσει να επιλέξει την καλύτερη γραμμή. Με αυτόν τον τρόπο, το MNC δεν λέει τίποτα για την παρουσία μιας πραγματικής αλληλεπίδρασης αλλαγών, η μέθοδος σας επιτρέπει απλώς να επιλέξετε τέτοιες παραμέτρους της συνάρτησης ένα і σι , Για όσους είναι vizchezgadane, το viraz είναι minimal.

Πιο περίπλοκες μαθηματικές πράξεις (αναφορά div.), μπορείτε να υπολογίσετε τις παραμέτρους ένα і σι :

Όπως μπορείτε να δείτε από τον τύπο, η παράμετρος ένα είναι μια αναφορά στη συνδιακύμανση και σε αυτή στο MS EXCEL για τον υπολογισμό της παραμέτρου ένα μπορείτε να κερδίσετε τέτοιους τύπους (διαιρ. λίμα φύλλου πισινό Lineyna):

= COVAR(B26:B45;C26:C45)/ VAR.G(B26:B45)ή

= ΣΥΜΒΑΛΛΟΝ.B(B26:B45;C26:C45)/VAR.B(B26:B45)

Επίσης για τον υπολογισμό της παραμέτρου ένα μπορείτε να στρίψετε τον τύπο = Nakhil (C26:C45; B26:B45). Για την παράμετρο σι τροποποιήστε τον τύπο = VIDRIZOK(C26:C45;B26:B45) .

Αν το βρείτε, η συνάρτηση Linear() σάς επιτρέπει να υπολογίσετε πολλαπλάσιο τον αριθμό των παραμέτρων. Για να εισάγετε τον τύπο Γραμμική (C26: C45; B26: B45)είναι απαραίτητο να δούμε στη σειρά 2 ντουλάπες και να στριμώξουμε CTRL + ΒΑΡΔΙΑ + ΕΙΣΑΓΩ(Διβ. άρθρο σχετικά). Η τιμή θα στραφεί στο αριστερό κέντρο ένα , στα δεξιά - σι .

Σημείωση: Μην φωνάζετε από τις εισαγωγές τύπους πίνακαείναι απαραίτητο να τροποποιήσετε επιπλέον τη συνάρτηση INDEX(). Τύπος = INDEX(LINEST(C26:C45,B26:B45),1)ή απλώς = Γραμμική (C26: C45; B26: B45)γυρίστε την παράμετρο, vіdpovіdalny nahil іnії, tobto. ένα . Τύπος = ΕΥΡΕΤΗΡΙΟ(ΓΡΑΜΜΙΚΟ(C26:C45,B26:B45),2)γυρίστε την παράμετρο, vіdpovіdalny για ρητίνη іnії z vіssyu Y, tobto. σι .

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

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

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

Σημείωση: Για να ρυθμιστούν κατάλληλα οι παράμετροι, ο τύπος των διαγραμμάτων θα πρέπει να είναι . Στα δεξιά στο γεγονός ότι όταν ζητηθεί από διαγράμματα ΠρόγραμμαΟι τιμές του άξονα X δεν μπορούν να δοθούν ως σταθερή τιμή (μια σταθερή τιμή μπορεί να καθοριστεί μόνο ως υπογραφή, ώστε να μην προστεθεί μια κουκκίδα στην εκτεταμένη κουκκίδα). Αντικαταστήστε την τιμή της νικηφόρας διαδοχής 1. 2; 3; ... (για την αρίθμηση των κατηγοριών). Σε αυτό, όπως θα είμαι γραμμή τάσηςσε τύπο διαγράμματος Πρόγραμμα, αντί για τις πραγματικές τιμές του X, θα αλλάξουν τις τιμές της ακολουθίας, το οποίο θα οδηγήσει σε εσφαλμένο αποτέλεσμα (άρα, προφανώς, οι πραγματικές τιμές του X δεν αλλάζουν από την ακολουθία 1, 2 3; ...).