Πώς να εμφανίσετε μια τάση σε ένα γράφημα στο excel. Εργαλεία πρόβλεψης στο Microsoft Excel

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

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

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

Γραμμική συνάρτηση: y=mx+b

όπου m είναι η εφαπτομένη της γωνίας κλίσης της ευθείας, b είναι η μετατόπιση.

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

Λογαριθμική συνάρτηση: y=c*ln⁡x+b

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

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

Πολυωνυμική συνάρτηση (μέχρι τον 6ο βαθμό συμπεριλαμβανομένου): y= b + c 1 *x + c 2 *x 2 + c 3 *x 3 + ...+ c 6* x 6

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

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

Λειτουργία ισχύος: y = cxb

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

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

Εκθετικη συναρτηση: y = cebx

όπου c και b είναι σταθερές, το e είναι η βάση του φυσικού λογάριθμου.

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

Χρήση γραμμικού φιλτραρίσματος χρησιμοποιώντας τον τύπο: F t = (A t +A (t-1) +⋯+A (t-n+1))/n

όπου n είναι ο συνολικός αριθμός των μελών της σειράς, t είναι ο δεδομένος αριθμός σημείων (2 ≤ t< n).

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

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

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

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

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

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

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

Γι' αυτό χρησιμοποιούνται ειδικές λειτουργίες.

Τώρα θα τα κατασκευάσουμε, θα τα διαμορφώσουμε και θα τα αναλύσουμε.

Έκδοση εύκολης κατασκευής

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

Ας ξεκινήσουμε με την εισαγωγή δεδομένων.

1. Δημιουργήστε έναν πίνακα στο Excel με τα δεδομένα προέλευσης.

(Εικόνα 1)

2. Επιλέξτε τα κελιά B3:B17 και μεταβείτε στην καρτέλα «Εισαγωγή» και επιλέξτε «Γράφημα».

(Σχήμα 2)

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

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

Στη συνέχεια, μεταβείτε στην καρτέλα "Σχεδίαση" και επιλέξτε "Διάταξη 1".

(Εικόνα 3)

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

(Εικόνα 4)

5. Κάντε κλικ στο κουμπί "Trend line" και επιλέξτε "linear fit" ή "exponential fit".

(Εικόνα 5)

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

Αυτό είναι το ενδιάμεσο αποτέλεσμα μας.

(Εικόνα 6)

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

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

6. Κάντε κλικ στο κουμπί «Γραμμή τάσης» και επιλέξτε «Πρόσθετες παράμετροι και γραμμές τάσης».

(Εικόνα 7)

7. Στο παράθυρο «Μορφή γραμμής τάσης», τσεκάρουμε το πλαίσιο δίπλα στο «τοποθετούμε την τιμή αξιοπιστίας προσέγγισης R^2 στο διάγραμμα και κάνουμε κλικ στο κουμπί «κλείσιμο».

Βλέπουμε στο διάγραμμα τον συντελεστή R^2= 0,6442

(Εικόνα 8)

8. Ακυρώστε τις αλλαγές. Επιλέξτε το γράφημα, κάντε κλικ στην καρτέλα «Διάταξη», κουμπί «γραμμή τάσης» και επιλέξτε «Όχι».

9. Πηγαίνετε στο παράθυρο «Μορφή γραμμής τάσης», αλλά για να επιλέξετε τη γραμμή τάσης «Πολυώνυμο», αλλάζουμε το βαθμό, επιτυγχάνοντας δείκτες συντελεστών R^2 = 0,8321

(Εικόνα 9)

Πρόβλεψη

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

(Εικόνα 10)

Με βάση την πρόβλεψη, μπορούμε να υποθέσουμε ότι στις 25 Ιανουαρίου ο αριθμός των πόντων θα ήταν μεταξύ 60 και 70.

συμπέρασμα

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

Τώρα ξέρετε πώς να ολοκληρώσετε την εργασία και να δημιουργήσετε μια γραμμή τάσεων, ακόμη και σε ένα πρόγραμμα όπως το excel 2010.

Μη διστάσετε να κάνετε ερωτήσεις.

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

Ίσως θα ξεκινήσω τη σειρά άρθρων για την πρόβλεψη με το πιο απλό πράγμα - την κατασκευή μιας συνάρτησης τάσης. Για παράδειγμα, ας πάρουμε δεδομένα πωλήσεων και ας δημιουργήσουμε ένα μοντέλο που περιγράφει την εξάρτηση των πωλήσεων από τον χρόνο.

ΒΑΣΙΚΕΣ ΕΝΝΟΙΕΣ

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

Y(t) = a0 + a1*t + E

Y είναι ο όγκος πωλήσεων, η μεταβλητή που θα εξηγήσουμε με βάση το χρόνο και από την οποία εξαρτάται, δηλαδή Y(t);

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

Το a0 είναι ο μηδενικός συντελεστής παλινδρόμησης, ο οποίος δείχνει την τιμή του Y(t), απουσία της επίδρασης του επεξηγηματικού παράγοντα (t=0).

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

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

Πρότυπο κτίριο

Έτσι, γνωρίζουμε τον όγκο πωλήσεων για τους τελευταίους 9 μήνες. Έτσι φαίνεται το ζώδιό μας:

Το επόμενο πράγμα που πρέπει να κάνουμε είναι να καθορίσουμε τους συντελεστές a0Και Α'1για την πρόβλεψη του όγκου πωλήσεων για τον 10ο μήνα.

Προσδιορισμός Συντελεστών Μοντέλου

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

Στα Φύλλα Google επιλέγουμε Επεξεργαστής γραφήματος -> Πρόσθετοςκαι βάλτε ένα τικ δίπλα Γραμμές τάσης. Στις ρυθμίσεις που επιλέγουμε Επιγραφή — Η εξίσωσηΚαι Εμφάνιση R^2.

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

Από προεπιλογή, δημιουργείται μια γραμμική συνάρτηση. Στα δεξιά, επιλέξτε "Εμφάνιση εξίσωσης στο διάγραμμα" και "Τιμή αξιοπιστίας προσέγγισης R^2".

Να τι συνέβη:

Στο γράφημα βλέπουμε την εξίσωση της συνάρτησης:

y = 4856*x + 105104

Περιγράφει τον όγκο πωλήσεων ανάλογα με τον αριθμό του μήνα για τον οποίο θέλουμε να προβλέψουμε αυτές τις πωλήσεις. Σε κοντινή απόσταση βλέπουμε τον συντελεστή προσδιορισμού R^2, ο οποίος δείχνει την ποιότητα του μοντέλου και πόσο καλά περιγράφει τις πωλήσεις μας (Y). Όσο πιο κοντά στο 1, τόσο το καλύτερο.

Έχω R^2 = 0,75. Αυτός είναι ένας μέσος δείκτης, υποδηλώνει ότι το μοντέλο δεν λαμβάνει υπόψη άλλους σημαντικούς παράγοντες εκτός από το χρόνο t, για παράδειγμα, μπορεί να είναι εποχικότητα.

Προβλέπουμε

y = 4856*10 + 105104

Έχουμε 153664 εκπτώσεις τον επόμενο μήνα. Αν προσθέσουμε ένα νέο σημείο στο γράφημα, βλέπουμε αμέσως ότι το R^2 έχει βελτιωθεί.

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

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

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

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

Μέθοδος 1: γραμμή τάσης

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

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

Μέθοδος 2: τελεστής FORECAST

Η παρέκταση για δεδομένα σε πίνακα μπορεί να γίνει χρησιμοποιώντας την τυπική συνάρτηση Excel ΠΡΟΦΗΤΕΙΑ. Αυτό το όρισμα κατηγοριοποιείται ως στατιστικό εργαλείο και έχει την ακόλουθη σύνταξη:

ΠΡΟΒΛΕΨΗ(Χ, γνωστές_τιμές_y, γνωστές_τιμές_x)

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

"Γνωστές αξίες y"— μια βάση γνωστών τιμών συνάρτησης. Στην περίπτωσή μας, το ρόλο του παίζει το ύψος του κέρδους για προηγούμενες περιόδους.

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

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

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

Ας δούμε τις αποχρώσεις της χρήσης του τελεστή ΠΡΟΦΗΤΕΙΑσε συγκεκριμένο παράδειγμα. Ας πάρουμε τον ίδιο πίνακα. Θα πρέπει να γνωρίζουμε την πρόβλεψη κερδών για το 2018.

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

Μέθοδος 3: τελεστής TREND

Για την πρόβλεψη, μπορείτε να χρησιμοποιήσετε μια ακόμη συνάρτηση - ΤΑΣΗ. Εμπίπτει επίσης στην κατηγορία των στατιστικών φορέων. Η σύνταξή του είναι από πολλές απόψεις παρόμοια με τη σύνταξη του εργαλείου ΠΡΟΦΗΤΕΙΑκαι μοιάζει με αυτό:

TREND(Γνωστές_υ τιμές; γνωστές_χ_τιμές; νέες_χ_τιμές; [συνθ])

Όπως βλέπουμε, τα επιχειρήματα "Γνωστές αξίες y"Και "Γνωστές τιμές του x"αντιστοιχούν πλήρως σε παρόμοια στοιχεία του χειριστή ΠΡΟΦΗΤΕΙΑ, και το επιχείρημα "Νέες τιμές x"ταιριάζει με το επιχείρημα "Χ"προηγούμενο εργαλείο. Επιπλέον, στο ΤΑΣΗυπάρχει ένα επιπλέον επιχείρημα "Συνεχής", αλλά δεν είναι υποχρεωτικό και χρησιμοποιείται μόνο με την παρουσία σταθερών παραγόντων.

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

Ας δούμε πώς θα λειτουργεί αυτό το εργαλείο με τον ίδιο πίνακα δεδομένων. Για να συγκρίνουμε τα αποτελέσματα που προέκυψαν, θα ορίσουμε το 2019 ως το σημείο πρόβλεψης.

Μέθοδος 4: Χειριστής GROWTH

Μια άλλη συνάρτηση που μπορεί να χρησιμοποιηθεί για την πραγματοποίηση προβλέψεων στο Excel είναι ο τελεστής GROWTH. Ανήκει επίσης στη στατιστική ομάδα εργαλείων, αλλά, σε αντίθεση με τα προηγούμενα, στον υπολογισμό δεν χρησιμοποιεί μέθοδο γραμμικής εξάρτησης, αλλά εκθετική. Η σύνταξη αυτού του εργαλείου μοιάζει με αυτό:

GROWTH(Γνωστές τιμές_y; γνωστές τιμές_x; νέες_τιμές_x; [const])

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

Μέθοδος 5: Χειριστής LINEST

Χειριστής LINESTΚατά τον υπολογισμό, χρησιμοποιεί τη μέθοδο γραμμικής προσέγγισης. Αυτό δεν πρέπει να συγχέεται με τη μέθοδο γραμμικής σχέσης που χρησιμοποιείται από το εργαλείο ΤΑΣΗ. Η σύνταξή του μοιάζει με αυτό:

LINEST(Γνωστές_τιμές; γνωστές_x_τιμές; νέες_χ_τιμές;[const];[statistics])

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

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

Μέθοδος 6: Χειριστής LGRFPRIBL

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

LGRFPRIBL (Γνωστές τιμές_y; γνωστές τιμές_x; new_values_x; [const]; [statistics])

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

Το προβλεπόμενο ποσό κέρδους το 2019, το οποίο υπολογίστηκε με τη μέθοδο της εκθετικής προσέγγισης, θα είναι 4639,2 χιλιάδες ρούβλια, το οποίο και πάλι δεν διαφέρει πολύ από τα αποτελέσματα που προέκυψαν από τον υπολογισμό χρησιμοποιώντας προηγούμενες μεθόδους.

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

Τι πρέπει να κάνω εάν δεν υπάρχουν χρονικές μετρήσεις για συγκεκριμένους όγκους/μεγέθη προϊόντων; Ή μήπως ο αριθμός των μετρήσεων είναι ανεπαρκής και δεν μπορούν να γίνουν πρόσθετες παρατηρήσεις στο εγγύς μέλλον; Ο καλύτερος τρόπος για να λύσετε αυτό το πρόβλημα είναι να δημιουργήσετε υπολογισμένες εξαρτήσεις (εξισώσεις παλινδρόμησης) χρησιμοποιώντας γραμμές τάσης στο MS Excel.

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

Στη συνέχεια, προέκυψε η ανάγκη προσδιορισμού του χρόνου που απαιτείται για την επιλογή 0,6 και 0,9 m3 εμπορευμάτων/παραγγελίας. Λόγω της αδυναμίας διεξαγωγής πρόσθετων μελετών χρονισμού, ο χρόνος που δαπανήθηκε για την επιλογή αυτών των όγκων παραγγελιών υπολογίστηκε χρησιμοποιώντας εξισώσεις παλινδρόμησης στο MS Excel. Για να επιτευχθεί αυτό, ο Πίνακας 1 μετατράπηκε σε Πίνακας 2.

Επιλέγοντας ένα διάγραμμα διασποράς, εικ. 1

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

Προσθήκη γραμμής τάσης, εικ. 2

Στο παράθυρο που εμφανίζεται για τη ρύθμιση της μορφής γραμμής τάσης (Εικ. 3)επιλέχθηκαν διαδοχικά: γραμμικός/τύπος γραμμής ισχύος και ελέγχθηκαν τα ακόλουθα στοιχεία: "εμφάνιση της εξίσωσης στο διάγραμμα" και "τοποθέτηση της τιμής αξιοπιστίας προσέγγισης (R^2) στο διάγραμμα" (συντελεστής προσδιορισμού).

Μορφή γραμμής τάσης, εικ. 3

Ως αποτέλεσμα, ελήφθησαν τα γραφήματα που παρουσιάζονται στο Σχ. 1. 4 και 5.

Γραμμική υπολογισμένη εξάρτηση, Εικ. 4

Υπολογιζόμενη εξάρτηση βάσει νόμου ισχύος, Εικ. 5

Μια οπτική ανάλυση των γραφημάτων δείχνει ξεκάθαρα την εγγύτητα των αποκτηθέντων εξαρτήσεων. Επιπλέον, η τιμή αξιοπιστίας προσέγγισης (R^2), η οποία ονομάζεται επίσης συντελεστής προσδιορισμού, στην περίπτωση και των δύο εξαρτήσεων είναι η ίδια τιμή 0,97. Είναι γνωστό ότι όσο πιο κοντά είναι ο συντελεστής προσδιορισμού στο 1, τόσο περισσότερο η γραμμή τάσης αντιστοιχεί στην πραγματικότητα. Μπορεί επίσης να δηλωθεί ότι το 97% της αλλαγής του χρόνου που δαπανάται για την επεξεργασία της παραγγελίας εξηγείται από μια αλλαγή στην ποσότητα των αγαθών. Επομένως, σε αυτή την περίπτωση δεν είναι σημαντικό: η οποία υπολογισμένη εξάρτηση θα πρέπει να επιλεγεί ως η κύρια για τον μετέπειτα υπολογισμό του κόστους χρόνου.

Ας πάρουμε ως κύρια τη γραμμική υπολογισμένη εξάρτηση. Στη συνέχεια, οι τιμές του χρόνου που δαπανάται ανάλογα με την ποσότητα των αγαθών θα καθοριστούν από τον τύπο: y = 54,511x + 0,1489.Τα αποτελέσματα αυτών των υπολογισμών για την ποσότητα των εμπορευμάτων για τα οποία είχαν πραγματοποιηθεί προηγουμένως χρονικές παρατηρήσεις παρουσιάζονται στον Πίνακα 3 παρακάτω.

Ας προσδιορίσουμε τη μέση απόκλιση του χρόνου που δαπανάται που υπολογίζεται χρησιμοποιώντας την εξίσωση παλινδρόμησης από τον χρόνο που δαπανάται που υπολογίζεται από παρατηρήσεις διατήρησης χρόνου: (-0,05+0,10-0,05+0,01)/4=0,0019. Έτσι, η χρονική δαπάνη που υπολογίζεται χρησιμοποιώντας την εξίσωση παλινδρόμησης διαφέρει από τη δαπάνη χρόνου που υπολογίζεται από δεδομένα χρονομέτρησης μόνο κατά 0,19%. Η απόκλιση μεταξύ των δεδομένων είναι αμελητέα.

Χρησιμοποιώντας τον τύπο: y = 54,511x + 0,1489, θα καθορίσουμε το κόστος χρόνου για την ποσότητα των αγαθών για τα οποία δεν έχουν πραγματοποιηθεί προηγουμένως παρατηρήσεις χρόνου (Πίνακας 4).

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