Γενική φόρμουλα
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Περίληψη
Για να συνοψίσετε τις κορυφαίες τιμές σε ένα εύρος, μπορείτε να χρησιμοποιήσετε έναν τύπο που βασίζεται στη συνάρτηση LARGE, τυλιγμένο μέσα στη συνάρτηση SUMPRODUCT. Στη γενική μορφή του τύπου (παραπάνω), το rng αντιπροσωπεύει μια περιοχή κελιών που περιέχουν αριθμητικές τιμές και το Ν αντιπροσωπεύει την ιδέα της τιμής Nth.
Στο παράδειγμα, το ενεργό κελί περιέχει αυτόν τον τύπο:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Εξήγηση
Στην απλούστερη μορφή του, το LARGE θα επιστρέψει την "Nth μεγαλύτερη" τιμή σε μια σειρά. Για παράδειγμα, ο τύπος:
=LARGE(B4:B13, 2)
θα επιστρέψει τη 2η μεγαλύτερη τιμή στην περιοχή B4: B13, η οποία, στο παραπάνω παράδειγμα, είναι ο αριθμός 9.
Ωστόσο, εάν παρέχετε μια "σταθερά πίνακα" (π.χ. μια σταθερά στη φόρμα (1,2,3)) στο LARGE ως το δεύτερο όρισμα, το LARGE θα επιστρέψει μια σειρά αποτελεσμάτων αντί για ένα μόνο αποτέλεσμα. Έτσι, ο τύπος:
=LARGE(B4:B13,(1,2,3))
θα επιστρέψει την 1η, 2η και 3η μεγαλύτερη τιμή στην περιοχή B4: B13. Στο παραπάνω παράδειγμα, όπου το B4: B13 περιέχει τους αριθμούς 1-10, το αποτέλεσμα από το LARGE θα είναι ο πίνακας (8,9,10). Στη συνέχεια, το SUMPRODUCT αθροίζει τους αριθμούς σε αυτόν τον πίνακα και επιστρέφει ένα σύνολο, που είναι 27.
SUM αντί για SUMPRODUCT
Το SUMPRODUCT είναι μια ευέλικτη λειτουργία που σας επιτρέπει να χρησιμοποιείτε αναφορές κυττάρων για k μέσα στη συνάρτηση LARGE.
Ωστόσο, εάν χρησιμοποιείτε μια απλή σταθερή συστοιχία κωδικοποιημένων συστοιχιών όπως (1,2,3), μπορείτε απλώς να χρησιμοποιήσετε τη συνάρτηση SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Σημειώστε ότι πρέπει να εισαγάγετε αυτόν τον τύπο ως τύπο πίνακα εάν χρησιμοποιείτε αναφορές κελιών και όχι σταθερά πίνακα για k εντός LARGE.
Όταν το Ν γίνεται μεγάλο
Όταν το Ν γίνει μεγάλο, είναι δύσκολο να δημιουργηθεί η σταθερά του πίνακα με το χέρι - Εάν θέλετε να αθροίσετε τις κορυφαίες 20 ή 30 τιμές σε μια μεγάλη λίστα, η πληκτρολόγηση μιας σταθεράς πίνακα με 20 ή 30 στοιχεία θα διαρκέσει πολύ. Σε αυτήν την περίπτωση, μπορείτε να χρησιμοποιήσετε μια συντόμευση για τη δημιουργία σταθεράς πίνακα που χρησιμοποιεί τις λειτουργίες ROW και INDIRECT.
Για παράδειγμα, εάν θέλετε να Αθροίσετε τις 20 κορυφαίες τιμές σε ένα εύρος που ονομάζεται "rng", μπορείτε να γράψετε έναν τύπο όπως αυτό:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Μεταβλητή Ν
Με ανεπαρκή δεδομένα, ένα σταθερό Ν μπορεί να προκαλέσει σφάλματα. Σε αυτήν την περίπτωση, μπορείτε να δοκιμάσετε έναν τύπο ως εξής:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Εδώ, χρησιμοποιούμε το MIN με COUNT για να συνοψίσουμε τις 3 πρώτες τιμές ή τον αριθμό των τιμών, εάν είναι μικρότερες από 3.
