Γενική φόρμουλα
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Περίληψη
Για τον μέσο όρο των τελευταίων 5 τιμών δεδομένων σε ένα εύρος στηλών, μπορείτε να χρησιμοποιήσετε τη συνάρτηση AVERAGE μαζί με τις συναρτήσεις COUNT και OFFSET. Στο παράδειγμα που εμφανίζεται, ο τύπος στο F6 είναι:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Εξήγηση
Η συνάρτηση OFFSET μπορεί να χρησιμοποιηθεί για την κατασκευή δυναμικών περιοχών χρησιμοποιώντας ένα κελί εκκίνησης, και με δεδομένες σειρές, στήλες, ύψος και πλάτος.
Τα ορίσματα γραμμών και στηλών λειτουργούν σαν "offsets" από την αρχική αναφορά. Τα ορίσματα ύψους και πλάτους, και τα δύο προαιρετικά, καθορίζουν πόσες σειρές και στήλες περιλαμβάνει το τελικό εύρος. Θέλουμε το OFFSET να επιστρέψει ένα εύρος που ξεκινά από την τελευταία καταχώρηση και επεκτείνεται "προς τα πίσω", οπότε παρέχουμε επιχειρήματα ως εξής:
αναφορά - η αρχική αναφορά είναι D5 - το κελί απευθείας στα δεξιά του τύπου και το πρώτο κελί στο εύρος τιμών με τις οποίες εργαζόμαστε.
σειρές - χρησιμοποιούμε το 0 για το όρισμα γραμμών, επειδή θέλουμε να παραμείνουμε στην ίδια σειρά.
στήλες - για το όρισμα στηλών, χρησιμοποιούμε τη συνάρτηση COUNT για να μετρήσουμε όλες τις τιμές στο εύρος και, στη συνέχεια, αφαιρέσουμε το 5. Αυτό αλλάζει την αρχή της στήλης εύρους 5 προς τα αριστερά.
ύψος - χρησιμοποιούμε 1 αφού θέλουμε μια σειρά 1 σειρών ως τελικό αποτέλεσμα.
πλάτος - χρησιμοποιούμε 5, αφού θέλουμε ένα τελικό εύρος με 5 στήλες.
Για τον τύπο στο C5, το OFFSET επιστρέφει ένα τελικό εύρος F5: J5. Αυτό πηγαίνει στη συνάρτηση AVERAGE που επιστρέφει τον μέσο όρο των 5 τιμών στο εύρος
Λιγότερες από 5 τιμές
Εάν υπάρχουν λιγότερες από 5 τιμές, ο τύπος θα επιστρέψει ένα κυκλικό σφάλμα αναφοράς, καθώς το εύρος θα επεκταθεί ξανά στο κελί που περιέχει τον τύπο. Για να αποφύγετε αυτό το σφάλμα, μπορείτε να προσαρμόσετε τον τύπο ως εξής:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Εδώ χρησιμοποιούμε τη συνάρτηση MIN για να "πιάσουμε" καταστάσεις όπου υπάρχουν λιγότερες από 5 τιμές και χρησιμοποιούμε τον πραγματικό αριθμό όταν υπάρχουν.
