Γενική φόρμουλα
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Περίληψη
Για να εξαγάγετε όλους τους αγώνες βάσει μερικής αντιστοίχισης, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα που βασίζεται στις συναρτήσεις INDEX και AGGREGATE, με υποστήριξη από ISNUMBER και SEARCH. Στο παράδειγμα που εμφανίζεται, ο τύπος στο G5 είναι:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
με τις ακόλουθες ονομασμένες περιοχές: "search" = D5, "ct" = D8, "data" = B5: B55.
Σημείωση: πρόκειται για έναν τύπο πίνακα, αλλά δεν απαιτεί έλεγχο + shift + enter, καθώς το AGGREGATE μπορεί να χειριστεί τους πίνακες εγγενώς.
Εξήγηση
Ο πυρήνας αυτού του τύπου είναι η συνάρτηση INDEX, με το AGGREGATE να χρησιμοποιείται για να καταλάβει το "nth match" για κάθε σειρά στην περιοχή εξαγωγής:
INDEX(data,nth_match_formula)
Σχεδόν όλη η εργασία είναι να βρείτε και να αναφέρετε ποιες σειρές στα "δεδομένα" ταιριάζουν με τη συμβολοσειρά αναζήτησης και να αναφέρετε τη θέση κάθε τιμής που αντιστοιχεί στο INDEX. Αυτό γίνεται με τη συνάρτηση AGGREGATE που έχει διαμορφωθεί ως εξής:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Το πρώτο επιχείρημα, 15, λέει στο AGGREGATE να συμπεριφέρεται σαν ΜΙΚΡΟ, και να επιστρέφει τις μικρότερες τιμές. Το δεύτερο επιχείρημα, 6, είναι μια επιλογή να αγνοήσουμε τα λάθη. Το τρίτο όρισμα είναι μια παράσταση που δημιουργεί μια σειρά από αποτελέσματα που ταιριάζουν (περιγράφεται παρακάτω). Το τέταρτο όρισμα, το F5, ενεργεί σαν "k" σε ΜΙΚΡΟ για να καθορίσει την "nth" τιμή.
Το AGGREGATE λειτουργεί σε συστοιχίες και η παρακάτω παράσταση δημιουργεί έναν πίνακα για το τρίτο όρισμα μέσα στο AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Εδώ, η συνάρτηση ROW χρησιμοποιείται για τη δημιουργία μιας σειράς σχετικών αριθμών σειρών, και το ISNUMBER και το SEARCH χρησιμοποιούνται μαζί για να ταιριάξουν τη συμβολοσειρά αναζήτησης με τις τιμές στα δεδομένα, η οποία δημιουργεί έναν πίνακα τιμών TRUE και FALSE.
Το έξυπνο bit είναι να διαιρέσετε τους αριθμούς σειράς με τα αποτελέσματα αναζήτησης. Σε μια μαθηματική λειτουργία όπως αυτή, το TRUE συμπεριφέρεται σαν 1 και το FALSE συμπεριφέρεται σαν μηδέν. Το αποτέλεσμα είναι οι αριθμοί σειράς που σχετίζονται με θετικό ταίριασμα διαιρούνται με το 1 και επιβιώνουν από τη λειτουργία, ενώ οι αριθμοί σειράς που σχετίζονται με τιμές που δεν ταιριάζουν καταστρέφονται και γίνονται σφάλματα # DIV / 0. Επειδή το AGGREGATE έχει ρυθμιστεί να αγνοεί τα σφάλματα, αγνοεί τα σφάλματα # DIV / 0 και επιστρέφει τον μικρότερο αριθμό "nth" στις υπόλοιπες τιμές, χρησιμοποιώντας τον αριθμό στη στήλη F για το "nth".
Διαχείριση απόδοσης
Όπως όλοι οι τύποι συστοιχιών, αυτός ο τύπος είναι "ακριβός" όσον αφορά τους πόρους με ένα μεγάλο σύνολο δεδομένων. Για να ελαχιστοποιήσετε τις επιπτώσεις στην απόδοση, ολόκληρος ο τύπος INDEX και MATCH τυλίγεται σε IF όπως αυτό:
=IF(F5>ct,"",formula)
όπου η ονομαστική περιοχή "ct" (D8) περιέχει αυτόν τον τύπο:
=COUNTIF(data,"*"&search&"*")
Αυτός ο έλεγχος εμποδίζει την εκτέλεση του τμήματος INDEX και AGGREGATE του τύπου μόλις εξαχθούν όλες οι αντίστοιχες τιμές.
Τύπος σειράς με ΜΙΚΡΟ
Εάν η έκδοση του Excel που διαθέτετε δεν διαθέτει τη συνάρτηση AGGREGATE, μπορείτε να χρησιμοποιήσετε έναν εναλλακτικό τύπο βασισμένο σε ΜΙΚΡΟ και ΕΑΝ:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Σημείωση: αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter.
