Γενική φόρμουλα
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Περίληψη
Για να συνοψίσετε τις κορυφαίες τιμές n σε κριτήρια αντιστοίχισης εύρους, μπορείτε να χρησιμοποιήσετε έναν τύπο βασισμένο στη συνάρτηση LARGE, τυλιγμένη μέσα στη συνάρτηση SUMPRODUCT. Στη γενική μορφή του τύπου (παραπάνω), το εύρος αντιπροσωπεύει μια περιοχή κελιών που συγκρίνονται με κριτήρια , οι τιμές αντιπροσωπεύουν αριθμητικές τιμές από τις οποίες ανακτώνται οι κορυφαίες τιμές και το Ν αντιπροσωπεύει την ιδέα της τιμής Nth.
Στο παράδειγμα, το ενεργό κελί περιέχει αυτόν τον τύπο:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Όπου το χρώμα είναι η ονομαστική περιοχή B5: B12 και η τιμή είναι η ονομαζόμενη περιοχή C5: C12.
Εξήγηση
Στην απλούστερη μορφή του, το LARGE επιστρέφει την τιμή "Nth μεγαλύτερη" σε μια σειρά με αυτήν την κατασκευή:
=LARGE (range,N)
Έτσι, για παράδειγμα:
=LARGE (C5:C12,2)
θα επιστρέψει τη 2η μεγαλύτερη τιμή στην περιοχή C5: C12, που είναι 12 στο παράδειγμα που εμφανίζεται.
Ωστόσο, εάν παρέχετε μια "σταθερά πίνακα" (π.χ. μια σταθερά στη φόρμα (1,2,3)) στο LARGE ως το δεύτερο όρισμα, το LARGE θα επιστρέψει μια σειρά αποτελεσμάτων αντί για ένα μόνο αποτέλεσμα. Έτσι, ο τύπος:
=LARGE (C5:C12, (1,2,3))
θα επιστρέψει την 1η, 2η και 3η μεγαλύτερη τιμή C5: C12 σε έναν πίνακα όπως αυτό: (12,12,10)
Έτσι, το κόλπο εδώ είναι να φιλτράρετε τις τιμές με βάση το χρώμα πριν εκτελεστεί το LARGE. Το κάνουμε αυτό με την έκφραση:
(color=E5)
Αυτό οδηγεί σε έναν πίνακα τιμών TRUE / FALSE Κατά τη λειτουργία πολλαπλασιασμού, αυτές οι τιμές εξαναγκάζονται σε αυτές και μηδενικά:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Έτσι, το τελικό αποτέλεσμα είναι ότι μόνο οι τιμές που σχετίζονται με το χρώμα "κόκκινο" επιβιώνουν από τη λειτουργία:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
και οι άλλες τιμές αναγκάζονται να μηδενιστούν.
Σημείωση: αυτός ο τύπος δεν θα χειρίζεται κείμενο στο εύρος τιμών. Δες παρακάτω.
Χειρισμός κειμένου σε τιμές
Εάν έχετε κείμενο οπουδήποτε στα εύρη τιμών, η συνάρτηση LARGE θα ρίξει ένα σφάλμα #VALUE και θα σταματήσει τη λειτουργία του τύπου
Για να χειριστείτε κείμενο στο εύρος τιμών, μπορείτε να προσθέσετε τη συνάρτηση IFERROR ως εξής:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Εδώ, εντοπίζουμε σφάλματα από LARGE που προκαλούνται από τιμές κειμένου και αντικαθιστούμε με μηδέν. Η χρήση IF εντός LARGE απαιτεί να εισαχθεί ο τύπος με τον έλεγχο + shift + enter, οπότε μεταβαίνουμε σε SUM αντί για SUMPRODUCT.
Σημείωση: Έτρεξα σε αυτόν τον τύπο που δημοσιεύτηκε από τον εκπληκτικό Barry Houdini στο stackoverflow.
