INDEX και MATCH σε πολλές στήλες
Για να αναζητήσετε μια τιμή με αντιστοίχιση σε πολλές στήλες, ένας τύπος πίνακα που βασίζεται στο ΔΕΊΚΤΗΣ και MATCH λειτουργίες που ενσωματώνει ΠΟΛΛΑ, ΜΕΤΑΘΕΤΩ και ΣΤΗΛΗ θα σε κάνει μια χάρη.
Πώς να αναζητήσετε μια τιμή με αντιστοίχιση σε πολλές στήλες;
Για να συμπληρώσετε το αντίστοιχη τάξη κάθε μαθητή όπως φαίνεται στον παραπάνω πίνακα, όπου το Οι πληροφορίες παρατίθενται σε πολλές στήλες, μπορείτε πρώτα να χρησιμοποιήσετε το κόλπο της συνάρτησης MMULT, TRANSPOSE και COLUMN για να δημιουργήσετε έναν πίνακα μήτρας. Στη συνέχεια, η συνάρτηση MATCH θα σας δώσει τη θέση της τιμής αναζήτησης, η οποία θα τροφοδοτηθεί στο INDEX για να ανακτήσετε την τιμή που αναζητάτε στον πίνακα.
Γενική σύνταξη
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Σημείωση: Αυτός είναι ένας τύπος πίνακα που απαιτεί να εισαγάγετε με Ctrl + αλλαγή + εισάγετε.
- range_range: Το εύρος από το οποίο θέλετε να επιστρέψει ο τύπος τις πληροφορίες κλάσης. Εδώ αναφέρεται το εύρος της κατηγορίας.
- lookup_value: Η τιμή που χρησιμοποιούσε ο τύπος για να βρει τις αντίστοιχες πληροφορίες κλάσης. Εδώ αναφέρεται στο συγκεκριμένο όνομα.
- lookup_array: Το εύρος των κελιών όπου lookup_value παρατίθεται? Το εύρος με τις τιμές προς σύγκριση με το lookup_valueΤο Εδώ αναφέρεται στο εύρος ονομάτων.
- match_type 0: Αναγκάζει το MATCH να βρει την πρώτη τιμή που ισούται ακριβώς με το lookup_value.
Για να βρείτε το τάξη του Τζίμυ, αντιγράψτε ή εισαγάγετε τον παρακάτω τύπο στο κελί H5 και πατήστε Ctrl + αλλαγή + εισάγετε για να πάρετε το αποτέλεσμα:
= INDEX ($ B $ 5: $ B $ 7,(MATCH(1,MULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
√ Σημείωση: Τα σημάδια του δολαρίου ($) παραπάνω υποδεικνύουν απόλυτες αναφορές, πράγμα που σημαίνει ότι το όνομα και το εύρος κλάσεων στον τύπο δεν θα αλλάξουν όταν μετακινείτε ή αντιγράφετε τον τύπο σε άλλα κελιά. Σημειώστε ότι δεν πρέπει να προσθέσετε σύμβολα δολαρίου στην αναφορά κελιού που αντιπροσωπεύει την τιμή αναζήτησης, καθώς θέλετε να είναι σχετική όταν την αντιγράφετε σε άλλα κελιά. Αφού εισαγάγετε τον τύπο, σύρετε τη λαβή πλήρωσης προς τα κάτω για να εφαρμόσετε τον τύπο στα παρακάτω κελιά.
Επεξήγηση του τύπου
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- --($C$5:$E$7=G5): Αυτό το τμήμα ελέγχει κάθε τιμή στο εύρος $C$5:$E$7 αν είναι ίσες με την τιμή στο κελί G5 και δημιουργεί έναν πίνακα TRUE και FALSE όπως αυτός:
{True, False, False, False, False, False, False, False, False}.
Το διπλό αρνητικό θα μετατρέψει τα TRUE και FALSE σε 1 και 0 για να δώσει έναν πίνακα όπως αυτός:
{1,0,0; 0,0,0; 0,0,0}. - Στήλη ($ c $ 5: $ e $ 7): Η συνάρτηση COLUMN επιστρέφει τους αριθμούς στηλών για την περιοχή $C$5:$E$7 σε έναν πίνακα σαν αυτόν: 3,4,5 {}.
- ΜΕΤΑΘΕΤΩ(Στήλη ($ c $ 5: $ e $ 7)^0) = ΜΕΤΑΘΕΤΩ(3,4,5 {}^0): Μετά την αύξηση της ισχύος στο 0, όλοι οι αριθμοί στον πίνακα {3,4,5} θα μετατραπούν σε 1: {1,1,1}. Στη συνέχεια, η συνάρτηση TRANSPOSE μετατρέπει τον πίνακα στηλών σε έναν πίνακα σειρών ως εξής: {1; 1; 1}.
- MMULT(--($C$5:$E$7=G5),ΜΕΤΑΘΕΤΩ(Στήλη ($ c $ 5: $ e $ 7)^0)) = MMULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): Η συνάρτηση MMULT επιστρέφει το γινόμενο μήτρας των δύο συστοιχιών ως εξής: {1; 0; 0}.
- ΑΓΩΝΑΣ (1,MMULT(--($C$5:$E$7=G5),ΜΕΤΑΘΕΤΩ(Στήλη ($ c $ 5: $ e $ 7)^0)), 0) = ΑΓΩΝΑΣ (1,{1; 0; 0}, 0): Ο τύπος match_type 0 αναγκάζει τη συνάρτηση MATCH να επιστρέψει τη θέση του πρώτου αγώνα του 1 στον πίνακα {1; 0; 0}, Η οποία είναι 1.
- ΔΕΙΚΤΗΣ($ B $ 5: $ B $ 7,(MATCH(1,MMULT(--($C$5:$E$7=G5),ΜΕΤΑΘΕΤΩ(Στήλη ($ c $ 5: $ e $ 7)^0)), 0))) = INDEX ($ B $ 5: $ B $ 7,1): Η συνάρτηση INDEX επιστρέφει το 1τιμή st στο εύρος κλάσης $ B $ 5: $ B $ 7, Η οποία είναι A.
Για να αναζητήσετε εύκολα μια τιμή με αντιστοίχιση σε πολλές στήλες, μπορείτε επίσης να χρησιμοποιήσετε το επαγγελματικό μας πρόσθετο Excel Kutools για Excel. Δείτε τις οδηγίες εδώ για να ολοκληρώσετε την αποστολή.
Σχετικές λειτουργίες
Η συνάρτηση Excel INDEX επιστρέφει την εμφανιζόμενη τιμή με βάση μια δεδομένη θέση από ένα εύρος ή έναν πίνακα.
Η συνάρτηση Excel MATCH αναζητά μια συγκεκριμένη τιμή σε μια περιοχή κελιών και επιστρέφει τη σχετική θέση της τιμής.
Η συνάρτηση Excel MMULT επιστρέφει το γινόμενο μήτρας δύο πινάκων. Το αποτέλεσμα του πίνακα έχει τον ίδιο αριθμό σειρών με τον πίνακα1 και τον ίδιο αριθμό στηλών με τον πίνακα2.
Η συνάρτηση TRANSPOSE του Excel περιστρέφει τον προσανατολισμό μιας περιοχής ή πίνακα. Για παράδειγμα, μπορεί να περιστρέψει έναν πίνακα που είναι διατεταγμένος οριζόντια σε σειρές σε κάθετα σε στήλες ή αντίστροφα.
Η συνάρτηση COLUMN επιστρέφει τον αριθμό στήλης που εμφανίζεται ο τύπος ή επιστρέφει τον αριθμό στήλης της δεδομένης αναφοράς. Για παράδειγμα, ο τύπος =COLUMN(BD) επιστρέφει 56.
Σχετικοί τύποι
Αναζήτηση πολλαπλών κριτηρίων με INDEX και MATCH
Όταν αντιμετωπίζετε μια μεγάλη βάση δεδομένων σε ένα υπολογιστικό φύλλο Excel με πολλές στήλες και λεζάντες σειρών, είναι πάντα δύσκολο να βρείτε κάτι που να πληροί πολλά κριτήρια. Σε αυτήν την περίπτωση, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα με τις συναρτήσεις INDEX και MATCH.
Αμφίδρομη αναζήτηση με INDEX και MATCH
Για να αναζητήσουμε κάτι σε γραμμές και στήλες στο Excel ή λέμε να αναζητήσουμε μια τιμή στη διασταύρωση της συγκεκριμένης γραμμής και στήλης, μπορούμε να χρησιμοποιήσουμε τη βοήθεια των συναρτήσεων INDEX και MATCH.
Αναζήτηση πλησιέστερης τιμής αντιστοίχισης με πολλά κριτήρια
Σε ορισμένες περιπτώσεις, μπορεί να χρειαστεί να αναζητήσετε την πλησιέστερη ή κατά προσέγγιση τιμή αντιστοίχισης με βάση περισσότερα από ένα κριτήρια. Με το συνδυασμό λειτουργιών INDEX, MATCH και IF, μπορείτε να το κάνετε γρήγορα στο Excel.
Τα καλύτερα εργαλεία παραγωγικότητας του Office
Kutools για Excel - Σας βοηθά να ξεχωρίζετε από το πλήθος
Το Kutools για το Excel διαθέτει πάνω από 300 δυνατότητες, Διασφάλιση ότι αυτό που χρειάζεστε είναι μόνο ένα κλικ μακριά...
Καρτέλα Office - Ενεργοποίηση ανάγνωσης με καρτέλες και επεξεργασία στο Microsoft Office (συμπεριλάβετε το Excel)
- Ένα δευτερόλεπτο για εναλλαγή μεταξύ δεκάδων ανοιχτών εγγράφων!
- Μειώστε εκατοντάδες κλικ ποντικιού για εσάς καθημερινά, πείτε αντίο στο χέρι του ποντικιού.
- Αυξάνει την παραγωγικότητά σας κατά 50% κατά την προβολή και την επεξεργασία πολλών εγγράφων.
- Φέρνει αποτελεσματικές καρτέλες στο Office (συμπεριλαμβανομένου του Excel), όπως ακριβώς το Chrome, το Edge και το Firefox.
