Υπολογισμός κινούμενου μέσου όρου στο Excel (απλό, εκθετικό και σταθμισμένο)
Ο κινητός μέσος όρος σημαίνει ότι υπολογίζουμε τον μέσο όρο των μέσων όρων του συνόλου δεδομένων που έχουμε, στο excel έχουμε ένα ενσωματωμένο χαρακτηριστικό για τον υπολογισμό του κινούμενου μέσου όρου που διατίθεται στην καρτέλα ανάλυσης δεδομένων στην ενότητα ανάλυσης, παίρνει ένα εύρος εισόδου και έξοδο εύρος με διαστήματα ως έξοδο, οι υπολογισμοί που βασίζονται σε απλούς τύπους στο excel για τον υπολογισμό του κινούμενου μέσου όρου είναι σκληροί, αλλά έχουμε μια ενσωματωμένη συνάρτηση στο excel για να το κάνουμε.
Τι είναι ο κινούμενος μέσος όρος στο Excel
Ο κινητός μέσος όρος είναι μια ευρέως χρησιμοποιούμενη τεχνική στην ανάλυση χρονοσειρών που χρησιμοποιείται για την πρόβλεψη του μέλλοντος. Οι κινούμενοι μέσοι όροι σε μια χρονοσειρά βασικά κατασκευάζονται λαμβάνοντας μέσους όρους διαφόρων διαδοχικών τιμών άλλων δεδομένων σειράς χρόνων.
Υπάρχουν τρεις τύποι κινητών μέσων όρων, δηλαδή ο απλός κινητός μέσος όρος, ο σταθμισμένος κινητός μέσος όρος και ο εκθετικός κινητός μέσος όρος στο excel.
# 1 - Απλός κινητός μέσος όρος στο Excel
Ένας απλός κινητός μέσος όρος βοηθά στον υπολογισμό του μέσου όρου του τελευταίου αριθμού περιόδων μιας σειράς δεδομένων. Ας υποθέσουμε ότι δίνονται τιμές της περιόδου n. Στη συνέχεια, ο απλός κινητός μέσος όρος δίνεται ως
Απλός μέσος όρος κίνησης = [P1 + P2 + …………. + Pn] / n
# 2 - Σταθμισμένος κινητός μέσος όρος στο Excel
Ο σταθμισμένος κινούμενος μέσος όρος παρέχει τον σταθμισμένο μέσο όρο των τελευταίων n περιόδων. Η στάθμιση μειώνεται με κάθε σημείο δεδομένων της προηγούμενης χρονικής περιόδου.
Σταθμισμένος κινούμενος μέσος όρος = (Τιμή * συντελεστής στάθμισης) + (Τιμή προηγούμενης περιόδου * συντελεστής στάθμισης-1)
# 3 - Εκθετικός κινητός μέσος όρος στο Excel
Είναι παρόμοιο με τον απλό κινούμενο μέσο όρο που μετρά τις τάσεις για μια χρονική περίοδο. Ενώ ο απλός κινούμενος μέσος όρος υπολογίζει έναν μέσο όρο δεδομένων δεδομένων, ο εκθετικός κινούμενος μέσος προσδίδει περισσότερο βάρος στα τρέχοντα δεδομένα.
Εκθετικός κινητός μέσος όρος = (K x (C - P)) + P
Που,
- Κ = εκθετική σταθερά εξομάλυνσης
- C = τρέχουσα τιμή
- P = εκθετικός κινούμενος μέσος όρος προηγούμενων περιόδων (απλός κινούμενος μέσος όρος που χρησιμοποιείται για τον υπολογισμό των πρώτων περιόδων)
Πώς να υπολογίσετε τον κινούμενο μέσο όρο στο Excel;
Ακολουθούν το παράδειγμα του κινούμενου μέσου όρου στο Excel.
Μπορείτε να κάνετε λήψη αυτού του προτύπου Moving Average Excel - Πρότυπο Moving Average ExcelΠαράδειγμα # 1 - Απλός κινητός μέσος όρος στο Excel
Για τον υπολογισμό του απλού κινούμενου μέσου όρου, έχουμε λάβει δεδομένα πωλήσεων μιας εταιρείας από τον Ιανουάριο έως τον Δεκέμβριο για το έτος 2018. Στόχος μας είναι να εξομαλύνουμε τα δεδομένα και να γνωρίζουμε τον αριθμό των πωλήσεων τον Ιανουάριο του 2019. Θα χρησιμοποιήσουμε τον κινούμενο μέσο όρο τριών μηνών εδώ.
Βήμα 1: Ο κινητός μέσος όρος Ιανουαρίου, Φεβρουαρίου και Μαρτίου υπολογίζεται λαμβάνοντας το άθροισμα των πωλήσεων των μηνών και στη συνέχεια διαιρείται με 3.
Βήμα 2: Η επιλογή στη γωνία του κελιού D5 και, στη συνέχεια, η μεταφορά και η πτώση θα δώσει τον κινούμενο μέσο όρο για τις υπόλοιπες περιόδους. Αυτή είναι η λειτουργία του εργαλείου πλήρωσης του excel.
Η πρόβλεψη πωλήσεων για τον Ιανουάριο του 2019 είναι 10456.66667.
Βήμα 3: Τώρα σχεδιάζουμε τον αριθμό των πωλήσεων και τον κινούμενο μέσο όρο στο γράφημα γραμμής για να κατανοήσουμε τη διαφορά στην τάση. Αυτό μπορεί να γίνει από την καρτέλα εισαγωγής. Αρχικά επιλέξαμε τη σειρά δεδομένων και στη συνέχεια από την ενότητα Χάρτες στο ένθετο, χρησιμοποιήσαμε το γράφημα γραμμής.
Μετά τη δημιουργία των γραφημάτων, φαίνεται ότι το γράφημα με τον κινούμενο μέσο όρο είναι πολύ πιο εξομαλυνμένο από την αρχική σειρά δεδομένων.
Παράδειγμα # 2 - Απλή μετακίνηση μέσου όρου μέσω της καρτέλας Ανάλυση δεδομένων στο Excel
- Στην καρτέλα Δεδομένα στην ομάδα ανάλυσης, πρέπει να κάνουμε κλικ στην Ανάλυση δεδομένων. Ακολουθεί το στιγμιότυπο οθόνης.
- Από την ανάλυση δεδομένων, ο κινητός μέσος όρος είναι προσβάσιμος.
- Αφού κάναμε κλικ στον κινούμενο μέσο όρο, επιλέξαμε τον αριθμό πωλήσεων ως το εύρος εισροών.
- Οι ετικέτες στην πρώτη σειρά κάνουν κλικ για να κάνουν το excel να κατανοήσει ότι η πρώτη σειρά έχει το όνομα της ετικέτας.
- Το διάστημα 3 επιλέγεται ως επειδή θέλουμε τρία χρόνια κινητού μέσου όρου.
- Επιλέξαμε το εύρος εξόδου με γειτνίαση με τον αριθμό πωλήσεων.
- Θέλουμε επίσης να δούμε την έξοδο γραφήματος όπου θα μπορούμε να δούμε τη διαφορά μεταξύ του πραγματικού και του προβλεπόμενου.
Αυτό το γράφημα δείχνει τη διαφορά μεταξύ πραγματικού και προβλεπόμενου κινούμενου μέσου όρου.
Παράδειγμα # 3 - Σταθμισμένος κινούμενος μέσος όρος στο Excel
Χρησιμοποιούμε τον σταθμισμένο κινούμενο μέσο όρο τριών ετών και ο τύπος δίνεται στο στιγμιότυπο οθόνης.
Αφού χρησιμοποιήσαμε τον τύπο, έχουμε τον κινούμενο μέσο όρο για μια περίοδο.
Πήραμε τον κινούμενο μέσο όρο για όλες τις άλλες περιόδους σύροντας και ρίχνοντας τιμές στα ακόλουθα κελιά.
Η πρόβλεψη για τον Ιανουάριο του 2019, δηλαδή 10718,33
Τώρα πήραμε το γράφημα γραμμής για να δούμε την εξομάλυνση των δεδομένων. Γι 'αυτό, επιλέξαμε τον μήνα μας τα προβλεπόμενα δεδομένα και στη συνέχεια εισαγάγαμε ένα γράφημα γραμμής.
Τώρα θα συγκρίνουμε τα προβλεπόμενα δεδομένα μας με τα πραγματικά δεδομένα μας. Στα παρακάτω στιγμιότυπα οθόνης μπορούμε εύκολα να δούμε τη διαφορά μεταξύ των πραγματικών δεδομένων και των προβλεπόμενων δεδομένων. Το γράφημα στην κορυφή είναι τα πραγματικά δεδομένα και το γράφημα παρακάτω είναι ο κινητός μέσος όρος και τα προβλεπόμενα δεδομένα. Μπορούμε να δούμε ότι το γράφημα μέσου όρου έχει εξομαλυνθεί σημαντικά σε σύγκριση με το γράφημα που περιέχει τα πραγματικά δεδομένα.
Παράδειγμα # 4 - Εκθετικός κινητός μέσος όρος στο Excel
Ο τύπος του εκθετικού κινούμενου μέσου όρου είναι S t = α.Y t-1 + (1- α) S t-1 …… (1)
Που,
- Y t-1 = πραγματική παρατήρηση στην περίοδο t-1
- S t-1 = απλός κινητός μέσος όρος στην περίοδο t-1
- α = συντελεστής εξομάλυνσης και κυμαίνεται μεταξύ .1 και .3. Όσο μεγαλύτερη είναι η τιμή του α πλησιέστερα είναι το γράφημα στις πραγματικές τιμές και μειώνεται η τιμή του α, τόσο πιο ομαλό θα είναι το γράφημα.
Πρώτον, υπολογίζουμε τον απλό κινούμενο μέσο όρο όπως φαίνεται νωρίτερα. Μετά από αυτό, εφαρμόζουμε τον τύπο που δίνεται στην εξίσωση (1). Για τον καθορισμό της τιμής α για όλες τις ακόλουθες τιμές έχουμε πατήσει F4.
Παίρνουμε τις τιμές με μεταφορά και απόθεση στα ακόλουθα κελιά.
Τώρα, θέλουμε να δούμε τη σύγκριση μεταξύ των πραγματικών τιμών, του απλού κινούμενου μέσου και του εκθετικού κινούμενου μέσου όρου στο excel. Αυτό το κάναμε κάνοντας ένα γράφημα γραμμών.
Από το παραπάνω στιγμιότυπο οθόνης, μπορούμε να δούμε τη διαφορά μεταξύ του πραγματικού αριθμού πωλήσεων, του απλού κινούμενου μέσου και του εκθετικού κινούμενου μέσου όρου στο excel.
Πράγματα που πρέπει να θυμάστε σχετικά με τον κινούμενο μέσο όρο στο Excel
- Ο απλός κινούμενος μέσος όρος μπορεί να υπολογιστεί χρησιμοποιώντας μια συνάρτηση AVERAGE στο excel
- Ο κινητός μέσος όρος βοηθά στην εξομάλυνση των δεδομένων
- Οι εποχιακοί μέσοι όροι συχνά ονομάζονται εποχιακοί δείκτες
- Ο εκθετικός κινούμενος μέσος όρος στο excel δίνει μεγαλύτερο βάρος στα πρόσφατα δεδομένα από τον απλό κινούμενο μέσο όρο. Επομένως, η εξομάλυνση σε περίπτωση του εκθετικού κινούμενου μέσου όρου στο excel είναι μεγαλύτερη από εκείνη του απλού κινούμενου μέσου όρου.
- Σε επιχειρήσεις όπως το χρηματιστήριο, ο κινητός μέσος όρος βοηθά τον έμπορο να εντοπίσει πιο εύκολα την τάση.
