Τύπος Excel: Λάβετε τον ένατο αγώνα με INDEX / MATCH

Γενική φόρμουλα

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Περίληψη

Για να ανακτήσετε πολλές τιμές αντιστοίχισης από ένα σύνολο δεδομένων με έναν τύπο, μπορείτε να χρησιμοποιήσετε τις συναρτήσεις IF και SMALL για να υπολογίσετε τον αριθμό σειράς κάθε αντιστοίχισης και να τροφοδοτήσετε αυτήν την τιμή πίσω στο INDEX. Στο παράδειγμα που παρουσιάζεται, ο τύπος στο I7 είναι:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Όπου οι ονομαζόμενες περιοχές είναι amts (D4: D11), id (I3) και id (C4: C11).

Σημειώστε ότι αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με Control + Shift + Enter.

Εξήγηση

Στον πυρήνα, αυτός ο τύπος είναι απλά ένας τύπος INDEX που ανακτά την τιμή σε έναν πίνακα σε μια δεδομένη θέση. Η τιμή για το n παρέχεται στη στήλη H και όλη η "βαριά" δουλειά που κάνει ο τύπος είναι να υπολογίσει τη σειρά από την οποία θα ανακτήσει μια τιμή, όπου η σειρά αντιστοιχεί στην αντιστοίχιση "nth".

Η συνάρτηση IF κάνει τη δουλειά του να καταλάβει ποιες σειρές περιέχουν αντιστοιχία και η συνάρτηση SMALL επιστρέφει την nth τιμή από αυτήν τη λίστα. Μέσα του IF, η λογική δοκιμή είναι:

ids=id

που αποδίδει αυτόν τον πίνακα:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

Σημειώστε ότι ταιριάζει το αναγνωριστικό πελάτη στην 1η και την 4η θέση, οι οποίες εμφανίζονται ως ΑΛΗΘΕΣ. Το όρισμα "value if true" στο IF δημιουργεί μια λίστα σχετικών αριθμών σειράς με αυτήν την έκφραση:

ROW(ids)-ROW(INDEX(ids,1,1))+1

που παράγει αυτόν τον πίνακα:

(1;2;3;4;5;6;7)

Αυτός ο πίνακας στη συνέχεια "φιλτράρεται" από τα λογικά αποτελέσματα δοκιμών και η συνάρτηση IF επιστρέφει το ακόλουθο αποτέλεσμα πίνακα:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Σημειώστε ότι έχουμε έγκυρους αριθμούς σειράς για τη σειρά 1 και τη σειρά 2.

Στη συνέχεια, αυτός ο πίνακας επεξεργάζεται από το SMALL, το οποίο έχει διαμορφωθεί ώστε να χρησιμοποιεί τιμές στη στήλη H για να επιστρέφει τις τιμές "nth". Η συνάρτηση SMALL αγνοεί αυτόματα τις λογικές τιμές TRUE και FALSE στον πίνακα. Στο τέλος, οι τύποι μειώνονται σε:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Αντιμετώπιση σφαλμάτων

Μόλις δεν υπάρχουν περισσότερες αντιστοιχίες για ένα δεδομένο αναγνωριστικό, η συνάρτηση SMALL θα επιστρέψει ένα σφάλμα #NUM. Μπορείτε να χειριστείτε αυτό το σφάλμα με τη συνάρτηση IFERROR ή προσθέτοντας λογική για να μετρήσετε αγώνες και να ακυρώσετε την επεξεργασία όταν ο αριθμός στη στήλη H είναι μεγαλύτερος από τον αριθμό αντιστοίχισης. Το παράδειγμα εδώ δείχνει μια προσέγγιση.