Γενική φόρμουλα
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Περίληψη
Για τον μέσο όρο των τελευταίων 5 σημείων δεδομένων, μπορείτε να χρησιμοποιήσετε τη συνάρτηση AVERAGE μαζί με τις συναρτήσεις COUNT και OFFSET. Μπορείτε να χρησιμοποιήσετε αυτήν την προσέγγιση για να μετρήσετε τα τελευταία σημεία δεδομένων Ν: τις τελευταίες 3 ημέρες, τις τελευταίες 6 μετρήσεις κ.λπ. Στο παράδειγμα που εμφανίζεται, ο τύπος στο F6 είναι:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Σημείωση: μια αρνητική τιμή για το ύψος δεν θα λειτουργεί στα φύλλα Google. Δείτε παρακάτω για περισσότερες πληροφορίες.
Εξήγηση
Η συνάρτηση OFFSET μπορεί να χρησιμοποιηθεί για την κατασκευή δυναμικών ορθογώνιων περιοχών βάσει μιας αρχικής αναφοράς και δεδομένων σειρών, στηλών, ύψους και πλάτους. Τα ορίσματα γραμμών και στηλών λειτουργούν σαν "offsets" από την αρχική αναφορά. Τα ορίσματα ύψους και πλάτους (και τα δύο προαιρετικά), καθορίζουν πόσες σειρές και στήλες περιλαμβάνει το τελικό εύρος. Για αυτό το παράδειγμα, το OFFSET έχει διαμορφωθεί ως εξής:
- αναφορά = C3
- σειρές = COUNT (A: A)
- στήλες = 0
- ύψος = -5
- πλάτος = (δεν παρέχεται)
Η αρχική αναφορά παρέχεται ως C3 το κελί πάνω από τα πραγματικά δεδομένα. Επειδή θέλουμε το OFFSET να επιστρέψει ένα εύρος που προέρχεται από την τελευταία καταχώρηση στη στήλη Γ, χρησιμοποιούμε τη συνάρτηση COUNT για να μετρήσουμε όλες τις τιμές στη στήλη Γ για να λάβουμε την απαιτούμενη μετατόπιση σειράς. COUNT μετρά μόνο αριθμητικές τιμές, επομένως η επικεφαλίδα στη σειρά 3 αγνοείται αυτόματα.
Με 8 αριθμητικές τιμές στη στήλη Γ, ο τύπος OFFSET επιλύει:
OFFSET(C3,8,0,-5)
Με αυτές τις τιμές, το OFFSET ξεκινά από το C3, αντισταθμίζει 8 σειρές στο C11, και στη συνέχεια χρησιμοποιεί το -5 για να επεκτείνει το ορθογώνιο εύρος έως και προς τα πίσω 5 σειρές για να δημιουργήσει το εύρος C7: C11.
Τέλος, το OFFSET επιστρέφει το εύρος C7: C11 στη συνάρτηση AVERAGE, η οποία υπολογίζει τον μέσο όρο τιμών σε αυτό το εύρος.
Excel εναντίον φύλλων
Ένα περίεργο παράξενο με αυτόν τον τύπο είναι ότι δεν θα λειτουργεί με τα Φύλλα Google, επειδή η συνάρτηση OFFSET στα Φύλλα δεν θα επιτρέπει αρνητική τιμή για ορίσματα ύψους ή πλάτους. Η τεκμηρίωση του Excel δηλώνει επίσης ότι το ύψος ή το πλάτος δεν μπορεί να είναι αρνητικό, αλλά φαίνεται ότι οι αρνητικές τιμές λειτουργούσαν καλά στο Excel από τη δεκαετία του 1990.
Για να αποφύγετε αρνητικές τιμές ύψους ή πλάτους, μπορείτε να χρησιμοποιήσετε έναν τύπο όπως αυτό:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Η σημείωση C4 είναι η αρχική αναφορά σε αυτήν την περίπτωση. Η γενική μορφή είναι:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
όπου το Α1 είναι το πρώτο κελί στους αριθμούς που θέλετε να μετρήσετε.
