Περίληψη
Για τον υπολογισμό ενός κινούμενου ή κυλιόμενου μέσου όρου, μπορείτε να χρησιμοποιήσετε έναν απλό τύπο βασισμένο στη συνάρτηση AVERAGE με σχετικές αναφορές. Στο παράδειγμα που παρουσιάζεται, ο τύπος στο Ε7 είναι:
=AVERAGE(C5:C7)
Καθώς ο τύπος αντιγράφεται προς τα κάτω, υπολογίζει τον κινούμενο μέσο όρο 3 ημερών με βάση την αξία πωλήσεων για την τρέχουσα ημέρα και τις δύο προηγούμενες ημέρες.
Παρακάτω είναι μια πιο ευέλικτη επιλογή βάσει της λειτουργίας OFFSET που χειρίζεται μεταβλητές περιόδους.
Σχετικά με τους κινούμενους μέσους όρους
Ένας κινούμενος μέσος όρος (ονομαζόμενος επίσης κυλιόμενος μέσος όρος) είναι ένας μέσος όρος που βασίζεται σε υποσύνολα δεδομένων σε συγκεκριμένα διαστήματα. Ο υπολογισμός ενός μέσου όρου σε συγκεκριμένα διαστήματα εξομαλύνει τα δεδομένα μειώνοντας την επίδραση τυχαίων διακυμάνσεων. Αυτό διευκολύνει την προβολή γενικών τάσεων, ειδικά σε ένα γράφημα. Όσο μεγαλύτερο είναι το διάστημα που χρησιμοποιείται για τον υπολογισμό ενός κινούμενου μέσου όρου, τόσο πιο ομαλοποιείται που συμβαίνει, καθώς περισσότερα σημεία δεδομένων περιλαμβάνονται σε κάθε υπολογισμένο μέσο όρο.
Εξήγηση
Οι τύποι που εμφανίζονται στο παράδειγμα χρησιμοποιούν όλες τη συνάρτηση AVERAGE με σχετική ρύθμιση αναφοράς για κάθε συγκεκριμένο διάστημα. Ο κινητός μέσος όρος 3 ημερών στο E7 υπολογίζεται τροφοδοτώντας το AVERAGE ένα εύρος που περιλαμβάνει την τρέχουσα ημέρα και τις δύο προηγούμενες ημέρες όπως αυτό:
=AVERAGE(C5:C7) // 3-day average
Οι μέσοι όροι 5 ημερών και 7 ημερών υπολογίζονται με τον ίδιο τρόπο. Σε κάθε περίπτωση, το εύρος που παρέχεται στο AVERAGE διευρύνεται ώστε να περιλαμβάνει τον απαιτούμενο αριθμό ημερών:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Όλοι οι τύποι χρησιμοποιούν μια σχετική αναφορά για το εύρος που παρέχεται στη συνάρτηση AVERAGE. Καθώς οι τύποι αντιγράφονται στη στήλη, το εύρος αλλάζει σε κάθε σειρά για να συμπεριλάβει τις τιμές που απαιτούνται για κάθε μέσο όρο.
Όταν οι τιμές απεικονίζονται σε ένα γράφημα γραμμών, το αποτέλεσμα εξομάλυνσης είναι σαφές:
Ανεπαρκή δεδομένα
Εάν ξεκινήσετε τους τύπους στην πρώτη σειρά του πίνακα, οι πρώτοι τύποι δεν θα έχουν αρκετά δεδομένα για τον υπολογισμό ενός πλήρους μέσου όρου, επειδή το εύρος θα επεκταθεί πάνω από την πρώτη σειρά δεδομένων:
Αυτό μπορεί να είναι ή όχι ένα ζήτημα, ανάλογα με τη δομή του φύλλου εργασίας και αν είναι σημαντικό όλοι οι μέσοι όροι να βασίζονται στον ίδιο αριθμό τιμών. Η συνάρτηση AVERAGE θα αγνοήσει αυτόματα τις τιμές κειμένου και τα κενά κελιά, επομένως θα συνεχίσει να υπολογίζει έναν μέσο όρο με λιγότερες τιμές. Γι 'αυτό λειτουργεί "στα Ε5 και Ε6.
Ένας τρόπος για να δείξετε σαφώς ανεπαρκή δεδομένα είναι να ελέγξετε τον τρέχοντα αριθμό σειράς και να ακυρώσετε με #NA όταν υπάρχουν λιγότερες από τιμές n. Για παράδειγμα, για τον μέσο όρο 3 ημερών, μπορείτε να χρησιμοποιήσετε:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Το πρώτο μέρος του τύπου δημιουργεί απλά έναν "κανονικοποιημένο" αριθμό σειράς, ξεκινώντας με 1:
ROW()-ROW($C$5)+1 // relative row number
Στη σειρά 5, το αποτέλεσμα είναι 1, στη σειρά 6 το αποτέλεσμα είναι 2 και ούτω καθεξής.
Όταν ο τρέχων αριθμός σειράς είναι μικρότερος από 3, ο τύπος επιστρέφει # N / A. Διαφορετικά, ο τύπος επιστρέφει έναν κινούμενο μέσο όρο όπως πριν. Αυτό μιμείται τη συμπεριφορά της έκδοσης του εργαλείου ανάλυσης του Moving Average, η οποία εξάγει # N / A έως ότου επιτευχθεί η πρώτη πλήρης περίοδος.
Ωστόσο, καθώς ο αριθμός των περιόδων αυξάνεται, τελικά θα εξαντληθούν οι σειρές πάνω από τα δεδομένα και δεν θα μπορείτε να εισαγάγετε το απαιτούμενο εύρος στο μέσο όρο. Για παράδειγμα, δεν μπορείτε να ρυθμίσετε έναν κινούμενο μέσο όρο 7 ημερών με το φύλλο εργασίας όπως φαίνεται, καθώς δεν μπορείτε να εισαγάγετε ένα εύρος που εκτείνεται σε 6 σειρές πάνω από το C5.
Μεταβλητές περίοδοι με OFFSET
Ένας πιο ευέλικτος τρόπος υπολογισμού ενός κινούμενου μέσου όρου είναι με τη λειτουργία OFFSET. Το OFFSET μπορεί να δημιουργήσει ένα δυναμικό εύρος, που σημαίνει ότι μπορούμε να δημιουργήσουμε έναν τύπο όπου ο αριθμός των περιόδων είναι μεταβλητός. Η γενική μορφή είναι:
=AVERAGE(OFFSET(A1,0,0,-n,1))
όπου n είναι ο αριθμός περιόδων που πρέπει να περιλαμβάνονται σε κάθε μέσο όρο. Όπως παραπάνω, το OFFSET επιστρέφει ένα εύρος που περνά στη συνάρτηση AVERAGE. Παρακάτω μπορείτε να δείτε αυτόν τον τύπο σε δράση, όπου το "n" είναι το ονομαζόμενο εύρος E2. Ξεκινώντας από το κελί C5, το OFFSET δημιουργεί ένα εύρος που εκτείνεται πίσω στις προηγούμενες σειρές. Αυτό επιτυγχάνεται χρησιμοποιώντας ύψος ίσο με αρνητικό Όταν το E5 αλλάξει σε άλλο αριθμό, ο κινητός μέσος όρος υπολογίζεται εκ νέου σε όλες τις σειρές:
Ο τύπος στο E5, αντιγράφεται, είναι:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Όπως και ο αρχικός τύπος παραπάνω, η έκδοση με το OFFSET θα έχει επίσης το πρόβλημα ανεπαρκών δεδομένων στις πρώτες σειρές, ανάλογα με τον αριθμό των περιόδων που δίνονται στο E5.
Στο παράδειγμα που εμφανίζεται, οι μέσες τιμές υπολογίζονται επιτυχώς επειδή η συνάρτηση AVERAGE αγνοεί αυτόματα τις τιμές κειμένου και τα κενά κελιά και δεν υπάρχουν άλλες αριθμητικές τιμές πάνω από το C5 Έτσι, ενώ το εύρος που περνά στο μέσο όρο στο E5 είναι C1: C5, υπάρχει μόνο μία τιμή στον μέσο όρο, 100. Ωστόσο, καθώς οι περίοδοι αυξάνονται, το OFFSET θα συνεχίσει να δημιουργεί ένα εύρος που εκτείνεται πάνω από την αρχή των δεδομένων, και τελικά στην κορυφή του φύλλου εργασίας και επιστρέφει ένα σφάλμα #REF.
Μία λύση είναι να "περιορίσετε" το μέγεθος του εύρους στον αριθμό των διαθέσιμων σημείων δεδομένων. Αυτό μπορεί να γίνει χρησιμοποιώντας τη συνάρτηση MIN για να περιορίσετε τον αριθμό που χρησιμοποιείται για το ύψος όπως φαίνεται παρακάτω:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Αυτό φαίνεται αρκετά τρομακτικό, αλλά στην πραγματικότητα είναι αρκετά απλό. Περιορίζουμε το ύψος που μεταφέρεται στο OFFSET με τη συνάρτηση MIN:
MIN(ROW()-ROW($C$5)+1,n)
Μέσα στο MIN, η πρώτη τιμή είναι ένας σχετικός αριθμός σειράς, υπολογιζόμενος με:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Η δεύτερη τιμή που δίνεται στο MIN είναι ο αριθμός των περιόδων, n. Όταν ο σχετικός αριθμός σειράς είναι μικρότερος από n, το MIN επιστρέφει τον τρέχοντα αριθμό σειράς στο OFFSET για ύψος. Όταν ο αριθμός σειράς είναι μεγαλύτερος από n, το MIN επιστρέφει n. Με άλλα λόγια, το MIN επιστρέφει απλά τις μικρότερες από τις δύο τιμές.
Ένα ωραίο χαρακτηριστικό της επιλογής OFFSET είναι ότι το n μπορεί εύκολα να αλλάξει. Εάν αλλάξουμε το n στο 7 και σχεδιάσουμε τα αποτελέσματα, έχουμε ένα γράφημα ως εξής:
Σημείωση: Ένα ερώτημα με τους παραπάνω τύπους OFFSET είναι ότι δεν θα λειτουργούν στα φύλλα Google, επειδή η συνάρτηση OFFSET στα φύλλα δεν επιτρέπει αρνητική τιμή για ύψος ή πλάτος. Το συνημμένο υπολογιστικό φύλλο έχει τύπους λύσης για τα φύλλα Google.
