Γενική φόρμουλα
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Περίληψη
Για να λάβετε το όνομα της ένατης μεγαλύτερης τιμής με κριτήρια, μπορείτε να χρησιμοποιήσετε το INDEX και MATCH, τη συνάρτηση LARGE και ένα φίλτρο που δημιουργήθηκε με τη συνάρτηση IF. Στο παράδειγμα που εμφανίζεται, ο τύπος στο κελί G5, που αντιγράφεται, είναι:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
όπου το όνομα (B5: B16), η ομάδα (C5: C16) και η βαθμολογία (D5: D16) ονομάζονται εύρη. Ο τύπος επιστρέφει το όνομα που σχετίζεται με την 1η, 2η και 3η υψηλότερη τιμή στην Ομάδα Α.
Σημείωση: Αυτός είναι ένας τύπος πίνακα που πρέπει να εισαχθεί με control + shift + enter, εκτός από το Excel 365.
Εξήγηση
Η συνάρτηση LARGE είναι ένας εύκολος τρόπος για να αποκτήσετε την ένατη μεγαλύτερη τιμή σε μια σειρά:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Σε αυτό το παράδειγμα, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση LARGE για να πάρουμε την υψηλότερη βαθμολογία και, στη συνέχεια, χρησιμοποιήσουμε τη βαθμολογία σαν "κλειδί" για να ανακτήσουμε το σχετικό όνομα με INDEX και MATCH. Παρατηρήστε ότι παίρνουμε τις τιμές για το n από το εύρος F5: F7, προκειμένου να λάβουμε την 1η, 2η και 3η υψηλότερη βαθμολογία.
Η στροφή όμως σε αυτήν την περίπτωση είναι ότι πρέπει να ξεχωρίσουμε τις βαθμολογίες στην ομάδα Α και την ομάδα Β. Με άλλα λόγια, πρέπει να εφαρμόσουμε κριτήρια. Αυτό το κάνουμε με τη συνάρτηση IF, η οποία χρησιμοποιείται για "φιλτράρισμα" τιμών προτού αξιολογηθούν με το ΜΕΓΑΛΟ. Ως γενικό παράδειγμα, για να λάβετε τη μεγαλύτερη τιμή (δηλ. 1η τιμή) στο εύρος2 όπου το εύρος 1 = "A", μπορείτε να χρησιμοποιήσετε έναν τύπο όπως αυτό:
LARGE(IF(range="A",range2),1)
Σημείωση: η χρήση IF με αυτόν τον τρόπο καθιστά έναν τύπο πίνακα.
Δουλεύοντας από μέσα προς τα έξω, το πρώτο βήμα είναι να αποκτήσετε τη "1η" μεγαλύτερη τιμή στα δεδομένα που σχετίζονται με την Ομάδα Α με τη συνάρτηση LARGE:
LARGE(IF(group="A",score),F5)
Σε αυτήν την περίπτωση, η τιμή στο F5 είναι 1, οπότε ζητάμε την κορυφαία βαθμολογία στην Ομάδα Α. Όταν αξιολογείται η συνάρτηση IF, ελέγχει κάθε τιμή στην ονομαζόμενη ομάδα εύρους . Η ονομαστική βαθμολογία βαθμολογίας παρέχεται για το value_if_true. Αυτό δημιουργεί έναν νέο πίνακα, ο οποίος επιστρέφεται απευθείας στη συνάρτηση LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Παρατηρήστε ότι οι μόνες βαθμολογίες που επιβιώνουν από το φίλτρο είναι από την Ομάδα Α. Η ΜΕΓΑΛΗ επιστροφή τότε η υψηλότερη βαθμολογία που απομένει, 93, απευθείας στη συνάρτηση MATCH ως τιμή αναζήτησης. Μπορούμε τώρα να απλοποιήσουμε τον τύπο σε:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Τώρα μπορούμε να δούμε ότι η συνάρτηση MATCH έχει ρυθμιστεί να χρησιμοποιεί τον ίδιο φιλτραρισμένο πίνακα που είδαμε παραπάνω. Η συνάρτηση IF φιλτράρει ξανά τις ανεπιθύμητες τιμές και το τμήμα MATCH του τύπου επιλύει:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Εφόσον το 93 εμφανίζεται στην 3η θέση, το MATCH επιστρέφει 3 απευθείας στη συνάρτηση INDEX:
=INDEX(name,3) // Hannah
Τέλος, η συνάρτηση INDEX επιστρέφει το όνομα στην 3η σειρά, "Hannah".
Με XLOOKUP
Η συνάρτηση XLOOKUP μπορεί επίσης να χρησιμοποιηθεί για την επίλυση αυτού του προβλήματος, χρησιμοποιώντας την ίδια προσέγγιση που εξηγήθηκε παραπάνω:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Όπως παραπάνω, το LARGE έχει ρυθμιστεί ώστε να λειτουργεί με έναν πίνακα που φιλτράρεται από IF και επιστρέφει ένα αποτέλεσμα 93 σε XLOOKUP ως τιμή αναζήτησης:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Ο πίνακας αναζήτησης δημιουργείται επίσης χρησιμοποιώντας το IF ως φίλτρο στις βαθμολογίες από την Ομάδα A. Με τον πίνακα επιστροφής να παρέχεται ως όνομα (B5: B16). Το XLOOKUP επιστρέφει το "Hannah" ως τελικό αποτέλεσμα.
Σημειώσεις
- Για να λάβετε το όνομα της nth τιμής με κριτήρια, (δηλαδή να περιορίσετε τα αποτελέσματα στην ομάδα Α ή Β) θα χρειαστεί να επεκτείνετε τον τύπο για να χρησιμοποιήσετε επιπλέον λογική.
- Στο Excel 365, η συνάρτηση FILTER είναι ένας καλύτερος τρόπος για να απαριθμήσετε δυναμικά τα κορυφαία ή τα κάτω αποτελέσματα. Αυτή η προσέγγιση θα χειριστεί αυτόματα τους δεσμούς.
