Τύπος Excel: Εξαγωγή όλων των αντιστοιχιών με βοηθητική στήλη -

Πίνακας περιεχομένων

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

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Περίληψη

Ένας τρόπος για να εξαγάγετε πολλές αντιστοιχίσεις στο Excel είναι να χρησιμοποιήσετε το INDEX και το MATCH με μια βοηθητική στήλη που επισημαίνει ταιριαστά δεδομένα. Αυτό αποφεύγει την πολυπλοκότητα ενός πιο προηγμένου τύπου πίνακα. Στο παράδειγμα που παρουσιάζεται, ο τύπος στο H6 είναι:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

όπου ct (G3), δεδομένα (B3: E52) και βοηθός (E3: E52) ονομάζονται εύρη.

Εξήγηση

Η πρόκληση με τύπους αναζήτησης που ανακτούν περισσότερους από έναν αγώνες είναι η διαχείριση διπλότυπων (δηλ. Πολλαπλών αγώνων). Οι τύποι αναζήτησης όπως το VLOOKUP και το INDEX + MATCH μπορούν εύκολα να βρουν τον πρώτο αγώνα, αλλά είναι πολύ πιο δύσκολο να αναζητήσετε "όλους τους αγώνες" όταν τα κριτήρια βρίσκουν περισσότερους από έναν αγώνες.

Αυτός ο τύπος αντιμετωπίζει αυτήν την πρόκληση χρησιμοποιώντας μια βοηθητική στήλη που επιστρέφει μια αριθμητική τιμή που μπορεί να χρησιμοποιηθεί για εύκολη εξαγωγή πολλαπλών αντιστοιχιών. Ο τύπος στη στήλη βοηθού μοιάζει με τον εξής:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Η βοηθητική στήλη ελέγχει κάθε σειρά στα δεδομένα για να δει αν το Τμήμα στη στήλη Γ ταιριάζει με την τιμή στο I3 και το κτίριο στη στήλη D ταιριάζει με την τιμή στο J3 Και οι δύο λογικές δοκιμές πρέπει να επιστρέψουν TRUE για να AND επιστρέψει TRUE.

Για κάθε σειρά, το αποτέλεσμα από τη συνάρτηση AND προστίθεται στην "παραπάνω τιμή" στη βοηθητική στήλη για να δημιουργήσει μια μέτρηση. Το πρακτικό αποτέλεσμα αυτού του τύπου είναι ένας αυξανόμενος μετρητής που αλλάζει μόνο όταν βρεθεί ένας (νέος) αγώνας. Στη συνέχεια, η τιμή παραμένει η ίδια μέχρι να βρεθεί ο επόμενος αγώνας. Αυτό λειτουργεί επειδή τα αποτελέσματα TRUE / FALSE επιστρέφονται από AND εξαναγκάζονται σε τιμές 1/0 ως μέρος της λειτουργίας αθροίσματος. Τα αποτελέσματα FALSE δεν προσθέτουν τίποτα και τα Αληθινά αποτελέσματα προσθέτουν 1.

Πίσω στην περιοχή εξαγωγής, ο τύπος αναζήτησης για το Όνομα στη στήλη H μοιάζει με αυτό:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Δουλεύοντας από μέσα προς τα έξω, το μέρος INDEX + MATCH του τύπου αναζητά το όνομα του πρώτου αγώνα που βρέθηκε, χρησιμοποιώντας τον αριθμό σειράς στη στήλη G ως την τιμή αντιστοίχισης:

INDEX(data,MATCH($G6,helper,0),1)

Το INDEX λαμβάνει και τις 3 στήλες δεδομένων ως πίνακα (ονομαζόμενο εύρος "δεδομένα") και το MATCH έχει διαμορφωθεί έτσι ώστε να ταιριάζει με τον αριθμό σειράς στη στήλη βοηθού (το όνομα εύρους "βοηθός") σε λειτουργία ακριβούς αντιστοίχισης (το τρίτο όρισμα ρυθμίζεται στο μηδέν) .

Εκεί γίνεται εμφανής η ευφυΐα του τύπου. Η στήλη βοηθού προφανώς περιέχει διπλότυπα, αλλά δεν έχει σημασία, γιατί το MATCH θα ταιριάζει μόνο με την πρώτη τιμή. Σχετικά με το σχεδιασμό, κάθε "πρώτη τιμή" αντιστοιχεί στη σωστή σειρά στον πίνακα δεδομένων.

Οι τύποι στις στήλες I και J είναι οι ίδιοι με το H, εκτός από τον αριθμό στήλης, ο οποίος αυξάνεται σε κάθε περίπτωση κατά μία.

Η δήλωση IF που τυλίγει τον τύπο INDEX / MATCH εκτελεί μια απλή λειτουργία - ελέγχει κάθε αριθμό σειράς στην περιοχή εξαγωγής για να δει εάν ο αριθμός σειράς είναι μικρότερος ή ίσος με την τιμή στο G3 (ονομάζεται εύρος "ct"), που είναι ο συνολικός αριθμός όλων των αντίστοιχων εγγραφών. Εάν ναι, εκτελείται η λογική INDEX / MATCH. Εάν όχι, το IF εξάγει μια κενή συμβολοσειρά ("").

Ο τύπος στο G3 (ονομάζεται εύρος "ct") είναι απλός:

=MAX(helper)

Δεδομένου ότι η μέγιστη τιμή στη στήλη βοηθού είναι η ίδια με τον συνολικό αριθμό αντιστοίχισης, η συνάρτηση MAX είναι το μόνο που χρειαζόμαστε.

Σημείωση: η περιοχή εξαγωγής πρέπει να διαμορφωθεί χειροκίνητα ώστε να χειρίζεται όσο το δυνατόν περισσότερα δεδομένα (π.χ. 5 σειρές, 10 σειρές, 20 σειρές κ.λπ.). Σε αυτό το παράδειγμα, περιορίζεται σε 5 σειρές μόνο για να διατηρείται το φύλλο εργασίας συμπαγές.

Έμαθα αυτήν την τεχνική στο βιβλίο του Mike Girvin Control + Shift + Enter.