Γενική φόρμουλα
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Περίληψη
Για να αποκτήσετε το όνομα της ένατης μεγαλύτερης τιμής, μπορείτε να χρησιμοποιήσετε το INDEX και MATCH με τη συνάρτηση LARGE. Στο παράδειγμα που εμφανίζεται, ο τύπος στο κελί H5 είναι:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
όπου το όνομα (B5: B16) και το σκορ (D5: D16) ονομάζονται εύρη.
Εξήγηση
Με λίγα λόγια, αυτός ο τύπος χρησιμοποιεί τη συνάρτηση LARGE για να βρει την ένατη μεγαλύτερη τιμή σε ένα σύνολο δεδομένων. Μόλις έχουμε αυτήν την τιμή, την συνδέουμε σε έναν τυποποιημένο τύπο INDEX και MATCH για να ανακτήσουμε το σχετικό όνομα. Με άλλα λόγια, χρησιμοποιούμε την ένατη μεγαλύτερη τιμή όπως ένα "κλειδί" για την ανάκτηση σχετικών πληροφοριών.
Η συνάρτηση LARGE είναι ένας απλός τρόπος για να αποκτήσετε την ένατη μεγαλύτερη τιμή σε ένα εύρος. Απλώς δώστε ένα εύρος για το πρώτο όρισμα (πίνακας) και μια τιμή για το n ως το δεύτερο όρισμα (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Δουλεύοντας από μέσα προς τα έξω, το πρώτο βήμα είναι να αποκτήσετε τη "1η" μεγαλύτερη τιμή στα δεδομένα με τη συνάρτηση LARGE:
LARGE(score,F5) // returns 93
Σε αυτήν την περίπτωση, η τιμή στο F5 είναι 1, επομένως ζητάμε το 1ο μεγαλύτερο σκορ (δηλαδή το κορυφαίο σκορ), το οποίο είναι 93. Τώρα μπορούμε να απλοποιήσουμε τον τύπο για:
=INDEX(name,MATCH(93,score,0))
Μέσα στη συνάρτηση INDEX, η συνάρτηση MATCH έχει ρυθμιστεί για να εντοπίσει τη θέση του 93 στην ονομαστική βαθμολογία εύρους (D5: D16):
MATCH(93,score,0) // returns 3
Δεδομένου ότι το 93 εμφανίζεται στην 3η σειρά, το MATCH επιστρέφει 3 απευθείας στο INDEX ως αριθμό σειράς, με όνομα ως πίνακα:
=INDEX(name,3) // Hannah
Τέλος, η συνάρτηση INDEX επιστρέφει το όνομα στην 3η σειρά, "Hannah".
Παρατηρήστε ότι παίρνουμε τις τιμές για το n από το εύρος F5: F7, προκειμένου να λάβουμε την 1η, 2η και 3η υψηλότερη βαθμολογία καθώς ο τύπος αντιγράφεται.
Ανάκτηση ομάδας
Ο ίδιος βασικός τύπος θα λειτουργήσει για την ανάκτηση τυχόν σχετικών πληροφοριών. Για να λάβετε την ομάδα για τις μεγαλύτερες τιμές, μπορείτε απλά να αλλάξετε τον πίνακα που παρέχεται στο INDEX με την ονομαστική ομάδα εύρους :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Με την τιμή 1 στο F5, το LARGE θα πάρει την υψηλότερη βαθμολογία και ο τύπος θα επιστρέψει "A".
Σημείωση: με το Excel 365, μπορείτε να χρησιμοποιήσετε τη συνάρτηση FILTER για να αναφέρετε δυναμικά τα κορυφαία ή τα κάτω αποτελέσματα
Με XLOOKUP
Η συνάρτηση XLOOKUP μπορεί επίσης να χρησιμοποιηθεί για να επιστρέψει το όνομα της ένατης μεγαλύτερης τιμής όπως αυτή:
=XLOOKUP(LARGE(score,F5),score,name)
Η LARGE επιστρέφει τη μεγαλύτερη τιμή, 93, απευθείας στο XLOOKUP ως τιμή αναζήτησης:
=XLOOKUP(93,score,name) // Hannah
Με την ονομαστική βαθμολογία εύρους (D5: D16) ως πίνακα αναζήτησης και το όνομα (B5: B16) ως πίνακα επιστροφής, το XLOOKUP επιστρέφει το "Hannah" όπως προηγουμένως.
Χειρισμός δεσμών
Οι διπλές τιμές στα αριθμητικά δεδομένα θα δημιουργήσουν "ισοπαλία". Εάν εμφανιστεί ισοπαλία στις τιμές που κατατάσσονται, για παράδειγμα, εάν η πρώτη και η δεύτερη μεγαλύτερη τιμή είναι οι ίδιες, το LARGE θα επιστρέψει την ίδια τιμή για καθεμία. Όταν αυτή η τιμή μεταβιβασθεί στη συνάρτηση MATCH, το MATCH θα επιστρέψει τη θέση του πρώτου αγώνα, οπότε θα δείτε το ίδιο (πρώτο) όνομα να επιστρέφεται.
Εάν υπάρχει η πιθανότητα δεσμών, ίσως θελήσετε να εφαρμόσετε κάποια στρατηγική ισοπαλίας. Μία προσέγγιση είναι να δημιουργήσετε μια νέα βοηθητική στήλη τιμών που έχουν προσαρμοστεί για να διακόψουν τους δεσμούς. Στη συνέχεια, χρησιμοποιήστε τις τιμές στήλης βοηθού για να ταξινομήσετε και να ανακτήσετε πληροφορίες. Αυτό καθιστά τη λογική που χρησιμοποιείται για τη διακοπή των δεσμών σαφής και σαφής.
Μια άλλη προσέγγιση είναι να σπάσετε τους δεσμούς με βάση μόνο τη θέση (δηλ. Η πρώτη ισοπαλία "κερδίζει"). Εδώ είναι ένας τύπος που ακολουθεί αυτήν την προσέγγιση:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Σημείωση: πρόκειται για έναν τύπο πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter, εκτός από το Excel 365.
Εδώ, χρησιμοποιούμε MATCH για να βρούμε τον αριθμό 1 και κατασκευάζουμε έναν πίνακα αναζήτησης χρησιμοποιώντας λογική boolean που (1) συγκρίνει όλες τις βαθμολογίες με την τιμή που επέστρεψε ο LARGE:
score=LARGE(score,F5)
και (2) χρησιμοποιεί έναν έλεγχο επέκτασης εύρους εάν το όνομα βρίσκεται ήδη στη λίστα κατάταξης:
COUNTIF(H$4:H4,name)=0
Όταν ένα όνομα είναι ήδη στη λίστα, "ακυρώνεται" από τη λογική και η επόμενη (διπλή) τιμή αντιστοιχεί. Παρατηρήστε ότι το αναπτυσσόμενο εύρος ξεκινά στην προηγούμενη σειρά, προκειμένου να αποφευχθεί μια κυκλική αναφορά.
Αυτή η προσέγγιση λειτουργεί σε αυτό το παράδειγμα επειδή δεν υπάρχουν διπλά ονόματα στη στήλη ονόματος. Ωστόσο, εάν εμφανιστούν διπλά ονόματα σε τιμές κατάταξης, η προσέγγιση πρέπει να προσαρμοστεί. Η ευκολότερη λύση είναι να βεβαιωθείτε ότι τα ονόματα είναι μοναδικά.
Σημειώσεις
- Για να λάβετε το όνομα της nth τιμής με κριτήρια, (δηλαδή να περιορίσετε τα αποτελέσματα στην ομάδα Α ή Β) θα χρειαστεί να επεκτείνετε τον τύπο για να χρησιμοποιήσετε επιπλέον λογική.
- Στο Excel 365, η συνάρτηση FILTER είναι ένας καλύτερος τρόπος για να απαριθμήσετε δυναμικά τα κορυφαία ή τα κάτω αποτελέσματα. Αυτή η προσέγγιση θα χειριστεί αυτόματα τους δεσμούς.
