Υπολογισμός κινούμενου μέσου όρου στο Excel (απλό, σταθμισμένο και εκθετικό)

Όπως πολλά από τα σεμινάρια μου στο Excel, αυτό είναι επίσης εμπνευσμένο από ένα από τα ερωτήματα που έλαβα από έναν φίλο. Wantedθελε να υπολογίσει τον κινητό μέσο όρο στο Excel και της ζήτησα να το αναζητήσει στο διαδίκτυο (ή να παρακολουθήσει ένα βίντεο YouTube σχετικά).

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

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

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

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

Τι είναι κινούμενος μέσος όρος;

Είμαι βέβαιος ότι γνωρίζετε ποια είναι η μέση τιμή.

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

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

Για παράδειγμα, την Ημέρα 3, αν σας ρωτήσω τη μέση θερμοκρασία 3 ημερών, θα μου δώσετε τη μέση τιμή θερμοκρασίας της Ημέρας 1, 2 και 3. Και αν την Ημέρα 4 σας ζητήσω τη μέση θερμοκρασία 3 ημερών , θα μου δώσετε τον μέσο όρο της Ημέρας 2, 3 και 4.

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

Ο κινητός μέσος όρος χρησιμοποιείται σε μεγάλο βαθμό για τεχνική ανάλυση και πολλές τράπεζες και αναλυτές χρηματιστηρίου το χρησιμοποιούν σε καθημερινή βάση (παρακάτω είναι ένα παράδειγμα που πήρα από τον ιστότοπο Market Realist).

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

Τύποι κινούμενων μέσων όρων

Υπάρχουν τρεις τύποι κινητών μέσων όρων:

• Απλός κινητός μέσος όρος (SMA)
• Ζυγισμένος κινητός μέσος όρος (WMA)
• Εκθετικός κινούμενος μέσος όρος (EMA)

Απλός κινητός μέσος όρος (SMA)

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

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

Αυτό μπορεί να επιτευχθεί με τον μέσο όρο των σημείων δεδομένων στη δεδομένη διάρκεια. Στο Excel, μπορείτε να το κάνετε εύκολα χρησιμοποιώντας τη συνάρτηση AVERAGE (αυτό καλύπτεται αργότερα σε αυτό το σεμινάριο).

Σταθμισμένος κινητός μέσος όρος (WMA)

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

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

Έτσι, είμαστε καλύτερα αν βασιστούμε περισσότερο στην αξία της Ημέρας 10.

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

Αυτό ονομάζεται σταθμισμένος κινητός μέσος όρος.

Εκθετικός κινητός μέσος όρος (EMA)

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

Ονομάζεται επίσης εκθετικός σταθμισμένος μέσος όρος (EWMA)

Η διαφορά μεταξύ WMA και EMA είναι ότι με το WMA, μπορείτε να αντιστοιχίσετε βάρη βάσει οποιωνδήποτε κριτηρίων. Για παράδειγμα, σε έναν κινητό μέσο όρο 3 σημείων, μπορείτε να αντιστοιχίσετε μια ηλικία βάρους 60% στο τελευταίο σημείο δεδομένων, 30% στο μεσαίο σημείο δεδομένων και 10% στο παλαιότερο σημείο δεδομένων.

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

Αρκετά με τη διάλεξη στατιστικών.

Τώρα ας βουτήξουμε και θα δούμε πώς να υπολογίσουμε τους κινητούς μέσους όρους στο Excel.

Υπολογισμός απλού κινούμενου μέσου όρου (SMA) χρησιμοποιώντας εργαλειοθήκη ανάλυσης δεδομένων στο Excel

Το Microsoft Excel διαθέτει ήδη ένα ενσωματωμένο εργαλείο για τον υπολογισμό των απλών κινούμενων μέσων όρων.

Ονομάζεται το Εργαλειοθήκη ανάλυσης δεδομένων.

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

Σε περίπτωση που έχετε ήδη την επιλογή Ανάλυση δεδομένων στην καρτέλα Δεδομένα, παραλείψτε τα παρακάτω βήματα και δείτε τα βήματα για τον υπολογισμό των κινητών μέσων όρων.

Κάντε κλικ στην καρτέλα Δεδομένα και ελέγξτε αν βλέπετε την επιλογή Ανάλυση δεδομένων ή όχι. Εάν δεν το βλέπετε, ακολουθήστε τα παρακάτω βήματα για να το κάνετε διαθέσιμο στην κορδέλα.

1. Κάντε κλικ στην καρτέλα Αρχείο
2. Κάντε κλικ στην επιλογή Επιλογές
3. Στο παράθυρο διαλόγου Επιλογές Excel, κάντε κλικ στην επιλογή Πρόσθετα
4. Στο κάτω μέρος του παραθύρου διαλόγου, επιλέξτε Πρόσθετα Excel στο αναπτυσσόμενο μενού και, στη συνέχεια, κάντε κλικ στο κουμπί Μετάβαση.
5. Στο παράθυρο διαλόγου Πρόσθετα, που ανοίγει, ελέγξτε την επιλογή Ανάλυση εργαλειοθήκης
6. Κάντε κλικ στο OK.

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

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

Παρακάτω είναι τα βήματα για τη χρήση της Ανάλυσης Δεδομένων για τον υπολογισμό ενός απλού κινητού μέσου όρου:

1. Κάντε κλικ στην καρτέλα Δεδομένα
2. Κάντε κλικ στην επιλογή Ανάλυση δεδομένων
3. Στο παράθυρο διαλόγου Ανάλυση δεδομένων, κάντε κλικ στην επιλογή Κινούμενος μέσος όρος (ίσως χρειαστεί να κάνετε κύλιση για να το φτάσετε).
4. Κάντε κλικ στο OK. Αυτό θα ανοίξει το παράθυρο διαλόγου «Κινούμενος μέσος όρος».
5. Στο εύρος εισόδου, επιλέξτε τα δεδομένα για τα οποία θέλετε να υπολογίσετε τον κινητό μέσο όρο (B2: B11 σε αυτό το παράδειγμα)
6. Στην επιλογή Interval, πληκτρολογήστε 3 (καθώς υπολογίζουμε έναν κινητό μέσο όρο τριών σημείων)
7. Στο εύρος εξόδου, εισαγάγετε το κελί όπου θέλετε τα αποτελέσματα. Σε αυτό το παράδειγμα, χρησιμοποιώ το C2 ως εύρος εξόδου
8. Κάντε κλικ στο OK

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

Σημειώστε ότι τα δύο πρώτα κελιά στη στήλη C έχουν το αποτέλεσμα ως σφάλμα #N/A. Αυτό συμβαίνει επειδή είναι κινητός μέσος όρος τριών σημείων και χρειάζεται τουλάχιστον τρία σημεία δεδομένων για να δώσει το πρώτο αποτέλεσμα. Έτσι, οι πραγματικές τιμές κινητού μέσου όρου ξεκινούν μετά το τρίτο σημείο δεδομένων και μετά.

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

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

Υπολογισμός κινούμενων μέσων όρων (SMA, WMA, EMA) χρησιμοποιώντας τύπους στο Excel

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

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

Επίσης, η Ανάλυση Δεδομένων Toolpak δίνει μόνο το Simple Moving Average (SMA), αλλά αν θέλετε να υπολογίσετε WMA ή EMA, πρέπει να βασιστείτε μόνο σε τύπους.

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

Ας υποθέσουμε ότι έχετε το σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να υπολογίσετε το SMA 3 σημείων:

Στο κελί C4, πληκτρολογήστε τον ακόλουθο τύπο:

= ΜΕΣΗ (Β2: Β4)

Αντιγράψτε αυτόν τον τύπο για όλα τα κελιά και θα σας δώσει το SMA για κάθε μέρα.

Θυμηθείτε: Κατά τον υπολογισμό του SMA χρησιμοποιώντας τύπους, πρέπει να βεβαιωθείτε ότι οι αναφορές στον τύπο είναι σχετικές. Αυτό σημαίνει ότι ο τύπος μπορεί να είναι = ΜΕΣΟΣ (B2: B4) ή = ΜΕΣΟΣ ($ B2: $ B4), αλλά δεν μπορεί να είναι = ΜΕΣΟΣ ($ B $ 2: $ B $ 4) ή = AVERAGE (B $ 2: B $ 4 ). Το τμήμα σειράς της αναφοράς πρέπει να είναι χωρίς το σύμβολο του δολαρίου. Μπορείτε να διαβάσετε περισσότερα για τις απόλυτες και σχετικές αναφορές εδώ.

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

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

Για το WMA, πρέπει να γνωρίζετε τα βάρη που θα αντιστοιχούν στις τιμές.

Για παράδειγμα, ας υποθέσουμε ότι πρέπει να υπολογίσετε το WMA 3 σημείων για το παρακάτω σύνολο δεδομένων, όπου το 60% του βάρους δίνεται στην πιο πρόσφατη τιμή, το 30% του προηγούμενου και το 10% του προηγούμενου.

Για να το κάνετε αυτό, εισαγάγετε τον ακόλουθο τύπο στο κελί C4 και αντιγράψτε το για όλα τα κελιά.

= 0,6*Β4+0,3*Β3+0,1*Β2

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

Υπολογισμός εκθετικού κινούμενου μέσου όρου με χρήση τύπων

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

Παρακάτω είναι ο τύπος για τον υπολογισμό του EMA για κινητό μέσο όρο τριών σημείων:

EMA = [Τελευταία τιμή - Προηγούμενη τιμή EMA] * (2 / N + 1) + Προηγούμενο EMA

… Όπου το N θα ήταν 3 σε αυτό το παράδειγμα (καθώς υπολογίζουμε έναν EMA τριών σημείων)

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

Ας υποθέσουμε ότι έχετε το παρακάτω σύνολο δεδομένων και θέλετε να υπολογίσετε το EMA τριών περιόδων:

Στο κελί C2, εισαγάγετε την ίδια τιμή με αυτή του B2. Αυτό συμβαίνει επειδή δεν υπάρχει προηγούμενη τιμή για τον υπολογισμό του EMA.

Στο κελί C3, εισαγάγετε τον παρακάτω τύπο και αντιγράψτε για όλα τα κελιά:

= (B3-C2)*(2/4)+C2

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

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

Προσθήκη κινούμενης μέσης γραμμής τάσης σε ένα γράφημα στηλών

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

Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω:

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

1. Επιλέξτε το σύνολο δεδομένων (συμπεριλαμβανομένων των κεφαλίδων)
2. Κάντε κλικ στην καρτέλα Εισαγωγή
3. Στην ομάδα Διάγραμμα, κάντε κλικ στο εικονίδιο "Εισαγωγή στήλης ή ράβδου".
4. Κάντε κλικ στην επιλογή Διάγραμμα συμπλεγμένων στηλών. Αυτό θα εισαγάγει το γράφημα στο φύλλο εργασίας.
5. Με επιλεγμένο το γράφημα, κάντε κλικ στην καρτέλα Σχεδίαση (αυτή η καρτέλα εμφανίζεται μόνο όταν είναι επιλεγμένο το γράφημα)
6. Στην ομάδα Διάταξη γραφήματος, κάντε κλικ στην επιλογή "Προσθήκη στοιχείου γραφήματος".
7. Τοποθετήστε το δείκτη του ποντικιού στην επιλογή "Trendline" και, στη συνέχεια, κάντε κλικ στο "More Trendline Options"
8. Στο παράθυρο Μορφοποίηση γραμμής τάσης, επιλέξτε την επιλογή «Κινούμενος μέσος όρος» και ορίστε τον αριθμό των περιόδων.

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

Σε περίπτωση που θέλετε να εισαγάγετε περισσότερες από μία κινούμενες μέσες γραμμές τάσης (για παράδειγμα μία για 2 περιόδους και μία για 3 περιόδους), επαναλάβετε τα βήματα από 5 έως 8).

Μπορείτε επίσης να χρησιμοποιήσετε τα ίδια βήματα για να εισαγάγετε μια κινούμενη μέση γραμμή τάσης και σε ένα γράφημα γραμμών.

Μορφοποίηση της γραμμής τάσης κινούμενου μέσου όρου

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

Μερικά πράγματα που μπορείτε να μορφοποιήσετε στη γραμμή τάσης περιλαμβάνουν:

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

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

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