Γενική φόρμουλα
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Περίληψη
Για να μετρήσετε τις 3 τελευταίες αριθμητικές τιμές σε ένα εύρος, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα που βασίζεται σε συνδυασμό συναρτήσεων για να τροφοδοτήσετε τις τελευταίες n αριθμητικές τιμές στη συνάρτηση AVERAGE. Στο παράδειγμα που εμφανίζεται, ο τύπος στο D6 είναι:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
όπου "δεδομένα" είναι η ονομαστική περιοχή B5: B13.
Σημείωση: αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter.
Εξήγηση
Η συνάρτηση AVERAGE θα υπολογίσει έναν μέσο όρο αριθμών που παρουσιάζονται σε έναν πίνακα, οπότε σχεδόν όλη η δουλειά σε αυτόν τον τύπο είναι η δημιουργία ενός πίνακα των 3 τελευταίων αριθμητικών τιμών σε ένα εύρος. Δουλεύοντας από μέσα προς τα έξω, η συνάρτηση IF χρησιμοποιείται για "φιλτράρισμα" αριθμητικών τιμών:
IF(ISNUMBER(data),ROW(data))
Η συνάρτηση ISNUMBER επιστρέφει TRUE για αριθμητικές τιμές και FALSE για άλλες τιμές (συμπεριλαμβανομένων των κενών) και η συνάρτηση ROW επιστρέφει αριθμούς σειράς, οπότε το αποτέλεσμα αυτής της λειτουργίας είναι αριθμοί σειράς πίνακα που αντιστοιχούν σε αριθμητικές καταχωρήσεις:
(5;6;FALSE;8;9;10;FALSE;12;13)
Αυτός ο πίνακας μεταβαίνει στη συνάρτηση LARGE με τη σταθερά πίνακα (1,2,3) για k. Το LARGE αγνοεί αυτόματα τις τιμές FALSE και επιστρέφει έναν πίνακα με τους μεγαλύτερους 3 αριθμούς, οι οποίοι αντιστοιχούν στις 3 τελευταίες σειρές με αριθμητικές τιμές:
(13,12,10)
Αυτός ο πίνακας πηγαίνει στη συνάρτηση LOOKUP ως τιμή αναζήτησης. Ο πίνακας αναζήτησης παρέχεται από τη συνάρτηση ROW και ο πίνακας αποτελεσμάτων είναι το ονομαζόμενο εύρος "δεδομένα":
LOOKUP((13,12,10), ROW(data), data))
Στη συνέχεια, το LOOKUP επιστρέφει έναν πίνακα που περιέχει αντίστοιχες τιμές στα "δεδομένα", ο οποίος τροφοδοτείται στο μέσο όρο:
=AVERAGE((100,92,90))
Χειρισμός λιγότερων τιμών
Εάν ο αριθμός των αριθμητικών τιμών μειωθεί κάτω από 3, αυτός ο τύπος θα επιστρέψει το σφάλμα #NUM, καθώς το LARGE δεν θα μπορεί να επιστρέψει 3 τιμές όπως ζητήθηκε. Ένας τρόπος να το χειριστείτε είναι να αντικαταστήσετε τη σταθερή κωδικοποιημένη συστοιχία (1,2,3) με μια δυναμική συστοιχία που δημιουργήθηκε χρησιμοποιώντας το INDIRECT όπως αυτό:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Εδώ, το MIN χρησιμοποιείται για να ορίσει το ανώτερο όριο του πίνακα σε 3 ή τον πραγματικό αριθμό των αριθμητικών τιμών, όποιο από τα δύο είναι μικρότερο.
Σημείωση: Συνάντησα αυτήν την έξυπνη προσέγγιση στο chandoo.org, σε απάντηση του Sajan σε μια παρόμοια ερώτηση.
