LINEST (Συνάρτηση LINEST)

Περιγραφή

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

Η εξίσωση της ευθείας είναι:

y = mx + b

–ή–

y = m1x1 + m2x2 + ... + b

εάν υπάρχουν πολλές περιοχές τιμών x, όπου οι εξαρτημένες τιμές y είναι συνάρτηση των ανεξάρτητων τιμών x. Οι τιμές m είναι συντελεστές που αντιστοιχούν σε κάθε τιμή x, ενώ η b είναι μια σταθερά. Σημειώνεται ότι τα x, y και m μπορεί να είναι ανύσματα. Ο πίνακας που επιστρέφει η συνάρτηση LINEST είναι της μορφής {mn\mn-1\...\m1\b}. Η συνάρτηση LINEST μπορεί επίσης να επιστρέψει πρόσθετα στατιστικά στοιχεία παλινδρόμησης.

Σύνταξη

LINEST(γνωστά_y; [γνωστά_x]; [σταθερά]; [στατιστικές])

Η σύνταξη της συνάρτησης LINEST περιλαμβάνει τα παρακάτω ορίσματα:

Σύνταξη

• γνωστά_y    Υποχρεωτικό. Το σύνολο των γνωστών τιμών του y, στη σχέση y = mx + b.

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

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

• γνωστά_x    Προαιρετικό. Ένα σύνολο γνωστών τιμών του x, στη σχέση y = mx + b.

  • Η περιοχή του ορίσματος γνωστά_x μπορεί να περιλαμβάνει ένα ή περισσότερα σύνολα μεταβλητών. Εάν χρησιμοποιείτε μόνο μία μεταβλητή, τα ορίσματα γνωστά_y και γνωστά_x μπορούν να είναι περιοχές οποιουδήποτε σχήματος, αρκεί να έχουν ίσες διαστάσεις. Εάν χρησιμοποιούνται περισσότερες από μία μεταβλητές, το όρισμα γνωστά_y πρέπει να είναι άνυσμα (δηλαδή, μια περιοχή με ύψος μίας μόνο γραμμής ή πλάτος μίας μόνο στήλης).

  • Εάν παραλειφθεί το όρισμα γνωστά_x, θεωρείται ίσο με έναν πίνακα {1\2\3\...} με μέγεθος ίδιο με του πίνακα γνωστά_y.

• σταθερά    Προαιρετικό. Μια λογική τιμή που καθορίζει εάν η σταθερά b θα θεωρηθεί ίση με το μηδέν.

  • Εάν το όρισμα σταθερά είναι TRUE ή παραλείπεται, η σταθερά b υπολογίζεται κανονικά.

  • Εάν το όρισμα σταθερά είναι FALSE, η σταθερά b θεωρείται ίση με το 0 και οι τιμές m αναπροσαρμόζονται, ώστε y = mx.

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

  • Εάν το όρισμα στατιστικές είναι TRUE, η συνάρτηση LINEST επιστρέφει πρόσθετα στατιστικά στοιχεία παλινδρόμησης. ως αποτέλεσμα, ο επιστρεφόμενος πίνακας είναι {mn\mn-1,...,m1\b; sen,sen-1,...,se1,seb; r^2,sey, F,df; ssreg, ssresid}.

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

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

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

Παρατηρήσεις

• Μπορείτε να περιγράψετε μια ευθεία γραμμή με την κλίση της και το σημείο τομής με τον άξονα y:

  Κλίση (m):
  Για να βρείτε την κλίση μιας γραμμής, που συχνά γράφεται ως m, πάρτε δύο σημεία στη γραμμή, (x1,y1) και (x2,y2); η κλίση ισούται με (y2 - y1)/(x2 - x1).

  Τομή Y (β):
  Το σημείο τομής y μιας γραμμής, που συχνά γράφεται ως b, είναι η τιμή του y στο σημείο όπου η γραμμή διασχίζει τον άξονα y.

  Η εξίσωση μιας ευθείας γραμμής είναι y = mx + b. Εφόσον γνωρίζετε τις τιμές των συντελεστών m και b, μπορείτε να υπολογίσετε οποιοδήποτε σημείο επάνω στην ευθεία, εισάγοντας στην εξίσωση την τιμή του y ή του x. Μπορείτε επίσης να χρησιμοποιήσετε τη συνάρτηση TREND.

• Όταν έχετε μόνο μία ανεξάρτητη μεταβλητή x, μπορείτε να υπολογίσετε απευθείας τις τιμές της κλίσης και του σημείου τομής με τον άξονα y, χρησιμοποιώντας τους εξής τύπους:

  Κλίση:
  =INDEX(LINEST(known_y;known_x);1)

  Αναχαίτιση Y:
  =INDEX(LINEST(known_y;known_x);2)

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

  

  

  όπου x και y είναι οι μέσες τιμές δείγματος, δηλαδή, x = AVERAGE(γνωστά_x) και y = AVERAGE(γνωστά_y).

• Η συνάρτηση προσαρμογής ευθείας LINEST και η συνάρτηση προσαρμογής καμπύλης LOGEST μπορούν να προσαρμόσουν τα δεδομένα σας σε ευθεία γραμμή ή εκθετική καμπύλη, αντίστοιχα. Πρέπει, ωστόσο, να αποφασίσετε ποιο από τα δύο αποτελέσματα έχει την καλύτερη προσαρμογή στα δεδομένα σας. Μπορείτε να υπολογίσετε τη συνάρτηση TREND(γνωστά_y;γνωστά_x) για ευθεία γραμμή ή τη συνάρτηση GROWTH(γνωστά_y, ;γνωστά_x) για εκθετική καμπύλη. Οι συναρτήσεις αυτές, χωρίς το όρισμα νέα_x, επιστρέφουν έναν πίνακα με τις προβλεπόμενες τιμές του y κατά μήκος της ευθείας ή της καμπύλης προσαρμογής στα σημεία των δεδομένων σας. Μπορείτε τότε να συγκρίνετε τις προβλεπόμενες τιμές με τις πραγματικές τιμές. Μπορείτε επίσης να συμπεριλάβετε τις τιμές αυτές σε ένα γράφημα, για οπτική σύγκριση.

• Στην ανάλυση παλινδρόμησης, το Excel υπολογίζει σε κάθε σημείο το τετράγωνο της διαφοράς της εκτιμώμενης τιμής του y και της πραγματικής τιμής του y στο σημείο αυτό. Το άθροισμα των τετραγώνων αυτών των διαφορών λέγεται υπολειπόμενο άθροισμα των τετραγώνων, ssresid. Το Excel υπολογίζει στη συνέχεια το συνολικό άθροισμα των τετραγώνων, sstotal. Όταν το όρισμα σταθερά = TRUE ή παραλείπεται, το συνολικό άθροισμα των τετραγώνων είναι το άθροισμα των τετραγώνων των διαφορών μεταξύ των πραγματικών τιμών του y και του μέσου όρου των τιμών του y. Όταν το όρισμα σταθερά = FALSE, το συνολικό άθροισμα των τετραγώνων είναι το άθροισμα των τετραγώνων των πραγματικών τιμών y (χωρίς την αφαίρεση του μέσου όρου των τιμών του y από κάθε μεμονωμένη τιμή του y). Στη συνέχεια, το άθροισμα των τετραγώνων παλινδρόμησης, ssreg, μπορεί να υπολογιστεί από τον τύπο: ssreg = sstotal - ssresid. Όσο μικρότερο είναι το υπολειπόμενο άθροισμα των τετραγώνων, σε σύγκριση με το συνολικό άθροισμα των τετραγώνων, τόσο μεγαλύτερη είναι η τιμή του συντελεστή προσδιορισμού, r², ο οποίος αποτελεί δείκτη του πόσο καλά η εξίσωση που προκύπτει από την ανάλυση παλινδρόμησης εξηγεί τη σχέση μεταξύ των μεταβλητών. Η τιμή του r² ισούται με ssreg/sstotal.

• Σε ορισμένες περιπτώσεις, μία ή περισσότερες από τις στήλες X (ας υποθέσουμε ότι τα Y και τα X βρίσκονται σε στήλες) ενδέχεται να μην έχουν πρόσθετη προγνωστική τιμή στην παρουσία των άλλων στηλών X. Με άλλα λόγια, η κατάργηση μίας ή περισσότερων στηλών X μπορεί να οδηγήσει σε προβλεπόμενες τιμές Y που είναι εξίσου ακριβείς. Σε αυτή την περίπτωση, αυτές οι περιττές στήλες X θα πρέπει να παραλειφθούν από το μοντέλο παλινδρόμησης. Αυτό το φαινόμενο ονομάζεται "collinearity" επειδή οποιαδήποτε περιττή στήλη X μπορεί να εκφραστεί ως άθροισμα πολλαπλών στοιχείων των μη περιττών στηλών X. Η συνάρτηση LINEST ελέγχει για συλλογικότητα και καταργεί τυχόν περιττές στήλες X από το μοντέλο παλινδρόμησης όταν τις προσδιορίζει. Οι στήλες X που καταργήθηκαν αναγνωρίζονται στο αποτέλεσμα της συνάρτησης LINEST ότι έχουν 0 συντελεστές εκτός από τις τιμές 0 se. Εάν μία ή περισσότερες στήλες καταργηθούν ως περιττές, η τιμή df επηρεάζεται επειδή η τιμή df εξαρτάται από τον αριθμό των στηλών X που χρησιμοποιούνται στην πραγματικότητα για προγνωστικούς σκοπούς. Για λεπτομέρειες σχετικά με τον υπολογισμό του df, ανατρέξτε στο Παράδειγμα 4. Εάν η τιμή df αλλάξει, επειδή καταργούνται οι περιττές στήλες X, επηρεάζονται επίσης οι τιμές sey και F. Η συλλογικότητα θα πρέπει να είναι σχετικά σπάνια στην πράξη. Ωστόσο, μία περίπτωση όπου είναι πιο πιθανό να προκύψει είναι όταν ορισμένες στήλες X περιέχουν μόνο 0 και 1 τιμές ως ενδείξεις για το αν ένα υποκείμενο σε ένα πείραμα είναι ή δεν είναι μέλος μιας συγκεκριμένης ομάδας. Εάν σταθερά = TRUE ή παραλειφθεί, η συνάρτηση LINEST εισάγει ουσιαστικά μια πρόσθετη στήλη X και των 1 τιμών για τη δημιουργία μοντέλου του σημείου τομής. Εάν έχετε μια στήλη με 1 για κάθε θέμα εάν είναι αρσενικό ή 0 εάν όχι, και έχετε επίσης μια στήλη με 1 για κάθε θέμα εάν είναι θηλυκό ή 0 εάν όχι, αυτή η δεύτερη στήλη είναι περιττή, επειδή οι καταχωρήσεις σε αυτήν μπορούν να ληφθούν από την αφαίρεση της καταχώρησης στη στήλη "αρσενικός δείκτης" από την καταχώρηση στην πρόσθετη στήλη και των 1 τιμών που προστέθηκαν από τη συνάρτηση LINEST .

• Η τιμή df υπολογίζεται με τον παρακάτω τρόπο, όταν δεν καταργηθεί καμία στήλη X από το μοντέλο εξαιτίας της ύπαρξης συγγραμμικότητας: αν υπάρχουν k στήλες του ορίσματος γνωστά_x και το όρισμα σταθερά = TRUE ή παραλείπεται, τότε df = n – k – 1. Εάν το όρισμα σταθερά = FALSE, τότε df = n - k. Και στις δύο περιπτώσεις, κάθε στήλη X που καταργήθηκε εξαιτίας της ύπαρξης συγγραμμικότητας, αυξάνει την τιμή df κατά 1.

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

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

• Ο υποκείμενος αλγόριθμος που χρησιμοποιείται στη συνάρτηση LINEST είναι διαφορετικός από τον αλγόριθμο που χρησιμοποιείται στις συναρτήσεις SLOPE και INTERCEPT. Η διαφορά μεταξύ αυτών των αλγόριθμων μπορεί να οδηγήσει σε διαφορετικά αποτελέσματα όταν τα δεδομένα είναι απροσδιόριστα και συγγραμικά. Για παράδειγμα, αν τα σημεία δεδομένων του ορίσματος γνωστά_y είναι 0 και τα σημεία δεδομένων του ορίσματος γνωστά_x είναι 1:

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

  • Οι συναρτήσεις SLOPE και INTERCEPT επιστρέφουν #ΔΙΑΙΡ./0! ως τιμή σφάλματος. Ο αλγόριθμος των συναρτήσεων SLOPE και INTERCEPT έχει σχεδιαστεί για την αναζήτηση μίας μόνο απάντησης και στην περίπτωση αυτή μπορεί να υπάρχουν περισσότερες από μία απαντήσεις.

• Εκτός από τη χρήση της συνάρτησης LOGEST για τον υπολογισμό στατιστικών στοιχείων για άλλους τύπους παλινδρόμησης, μπορείτε να χρησιμοποιήσετε τη συνάρτηση LINEST για τον υπολογισμό μιας περιοχής άλλων τύπων παλινδρόμησης μέσω της εισαγωγής συναρτήσεων των μεταβλητών x και y ως σειρές x και y για τη συνάρτηση LINEST. Για παράδειγμα, ο ακόλουθος τύπος:

  =LINEST(τιμέςy, τιμέςx^COLUMN($A:$C))

  λειτουργεί όταν έχετε μια στήλη τιμών y και μία στήλη τιμών x για τον υπολογισμό της κυβικής προσέγγισης (πολυωνυμική βαθμού 3) της μορφής:

  y = m1*x + m2*x^2 + m3*x^3 + b

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

• Η τιμή ελέγχου F που επιστρέφει η συνάρτηση LINEST διαφέρει από την τιμή ελέγχου F που επιστρέφει η συνάρτηση FTEST. Η συνάρτηση LINEST επιστρέφει τη στατιστική τιμή F, ενώ η συνάρτηση FTEST επιστρέφει την πιθανότητα.

Παραδείγματα