Μετάβαση στο κύριο περιεχόμενο

Excel INDEX MATCH: Βασικές και σύνθετες αναζητήσεις

Στο Excel, η ακριβής ανάκτηση συγκεκριμένων δεδομένων είναι συχνά μια συχνή ανάγκη. Ενώ οι συναρτήσεις INDEX και MATCH έχουν η καθεμία τα δικά της δυνατά σημεία, ο συνδυασμός τους ξεκλειδώνει ένα ισχυρό σύνολο εργαλείων για αναζήτηση δεδομένων. Μαζί, διευκολύνουν μια σειρά δυνατοτήτων αναζήτησης, από βασικές οριζόντιες και κάθετες αναζητήσεις έως πιο προηγμένες λειτουργίες, όπως αμφίδρομες αναζητήσεις, με διάκριση πεζών-κεφαλαίων και αναζήτηση πολλαπλών κριτηρίων. Προσφέροντας βελτιωμένες δυνατότητες σε σύγκριση με το VLOOKUP, ο συνδυασμός INDEX και MATCH επιτρέπει ένα ευρύτερο φάσμα επιλογών αναζήτησης δεδομένων. Σε αυτό το σεμινάριο, ας εμβαθύνουμε στο βάθος των δυνατοτήτων που μπορούν να επιτύχουν μαζί.


Πώς να χρησιμοποιήσετε το INDEX και το MATCH στο Excel

Προτού χρησιμοποιήσουμε τις συναρτήσεις INDEX και MATCH, ας βεβαιωθούμε ότι γνωρίζουμε πώς μπορούν το INDEX και το MATCH να μας βοηθήσουν στην αναζήτηση τιμών πρώτα.


Πώς να χρησιμοποιήσετε τη συνάρτηση INDEX στο Excel

Η ΔΕΊΚΤΗΣ συνάρτηση στο Excel επιστρέφει την τιμή σε μια δεδομένη τοποθεσία σε ένα συγκεκριμένο εύρος. Η σύνταξη της συνάρτησης INDEX είναι η εξής:

=INDEX(array, row_num, [column_num])
  • παράταξη (απαιτείται) αναφέρεται στο εύρος από το οποίο θέλετε να επιστρέψετε την τιμή.
  • σειρά_αριθμός (απαιτείται, εκτός αν στήλη_αριθμός υπάρχει) αναφέρεται στον αριθμό σειράς του πίνακα.
  • στήλη_αριθμός (προαιρετικό, αλλά απαιτείται εάν σειρά_αριθμός παραλείπεται) αναφέρεται στον αριθμό στήλης του πίνακα.

Για παράδειγμα, να ξέρεις το σκορ του Τζεφ, τη 6Ο μαθητής στη λίστα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση INDEX ως εξής:

=INDEX(C2:C11,6)

αντιστοιχία ευρετηρίου excel 01

√ Σημείωση: Το εύρος C2: C11 είναι όπου αναγράφονται οι βαθμολογίες, ενώ ο αριθμός 6 βρίσκει τη βαθμολογία των εξετάσεων του 6ο μαθητής.

Εδώ ας κάνουμε μια μικρή δοκιμή. Για τη φόρμουλα =INDEX(A1:C1,2), τι αξία θα επιστρέψει; --- Ναι, θα επιστρέψει Ημερομηνία γέννησης, τη 2nd τιμή στη δεδομένη σειρά.

Τώρα πρέπει να ξέρουμε ότι η συνάρτηση INDEX μπορεί να λειτουργήσει τέλεια με οριζόντια ή κάθετα εύρη. Τι γίνεται όμως αν το χρειαζόμαστε για να επιστρέψουμε μια τιμή σε μεγαλύτερο εύρος με πολλές σειρές και στήλες; Λοιπόν, σε αυτήν την περίπτωση, θα πρέπει να εφαρμόσουμε και έναν αριθμό γραμμής και έναν αριθμό στήλης. Για παράδειγμα, για να μάθετε Το σκορ του Τζεφ εντός του εύρους του πίνακα αντί για μία στήλη, μπορούμε να εντοπίσουμε τη βαθμολογία του με α αριθμός σειράς 6 και σε έναν αριθμός στήλης 3 στο κελιά μέσω Α2 έως C11 σαν αυτό:

=INDEX(A2:C11,6,3)

αντιστοιχία ευρετηρίου excel 02

Πράγματα που πρέπει να γνωρίζουμε για τη συνάρτηση INDEX στο Excel:
  • Η συνάρτηση INDEX μπορεί να λειτουργήσει με κάθετες και οριζόντιες περιοχές.
  • Εάν και τα δύο σειρά_αριθμός και στήλη_αριθμός χρησιμοποιούνται επιχειρήματα, σειρά_αριθμός πηγαίνει μπροστά από το στήλη_αριθμός, και το INDEX ανακτά την τιμή στη διασταύρωση του καθορισμένου σειρά_αριθμός και στήλη_αριθμός.

Ωστόσο, για μια πραγματικά μεγάλη βάση δεδομένων με πολλές σειρές και στήλες, σίγουρα δεν είναι βολικό για εμάς να εφαρμόσουμε τον τύπο με έναν ακριβή αριθμό σειράς και αριθμό στήλης. Και αυτό είναι όταν πρέπει να συνδυάσουμε τη χρήση της συνάρτησης MATCH.


Πώς να χρησιμοποιήσετε τη συνάρτηση MATCH στο Excel

Η συνάρτηση MATCH στο Excel επιστρέφει μια αριθμητική τιμή, τη θέση ενός συγκεκριμένου στοιχείου στο δεδομένο εύρος. Η σύνταξη της συνάρτησης MATCH έχει ως εξής:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value (απαιτείται) αναφέρεται στην τιμή που αντιστοιχεί στο συστοιχία αναζήτησης.
  • συστοιχία αναζήτησης (απαιτείται) αναφέρεται στο εύρος των κελιών όπου θέλετε να αναζητήσετε το MATCH.
  • match_type (προαιρετικός): 1, 0 or -1.
    • 1 (προεπιλογή), το MATCH θα βρει τη μεγαλύτερη τιμή που είναι μικρότερη ή ίση με το lookup_valueΤο Οι αξίες στο συστοιχία αναζήτησης πρέπει να τοποθετηθεί σε αύξουσα σειρά.
    • 0, MATCH θα βρει την πρώτη τιμή που ισούται ακριβώς με το lookup_valueΤο Οι αξίες στο συστοιχία αναζήτησης μπορεί να είναι με οποιαδήποτε σειρά. (Για τις περιπτώσεις που ο τύπος αντιστοίχισης έχει οριστεί σε 0, μπορείτε να χρησιμοποιήσετε χαρακτήρες μπαλαντέρ.)
    • -1, MATCH θα βρει τη μικρότερη τιμή που είναι μεγαλύτερη ή ίση με το lookup_valueΤο Οι αξίες στο συστοιχία αναζήτησης πρέπει να τοποθετηθεί σε φθίνουσα σειρά.

Για παράδειγμα, να ξέρεις η θέση της Βέρας στον κατάλογο των ονομάτων, μπορείτε να χρησιμοποιήσετε τον τύπο MATCH ως εξής:

=MATCH("Vera",A2:A11,0)

αντιστοιχία ευρετηρίου excel 3

√ Σημείωση: Το αποτέλεσμα "4" υποδεικνύει ότι το όνομα "Vera" βρίσκεται στην 4η θέση της λίστας.

Πράγματα που πρέπει να γνωρίζουμε για τη συνάρτηση MATCH στο Excel:
  • Η συνάρτηση MATCH επιστρέφει τη θέση της τιμής αναζήτησης στον πίνακα αναζήτησης, όχι την ίδια την τιμή.
  • Η συνάρτηση MATCH επιστρέφει την πρώτη αντιστοίχιση σε περίπτωση διπλότυπων.
  • Ακριβώς όπως η συνάρτηση INDEX, η συνάρτηση MATCH μπορεί να λειτουργήσει και με κάθετες και οριζόντιες περιοχές.
  • Το MATCH δεν κάνει διάκριση πεζών-κεφαλαίων.
  • Εάν η lookup_value του τύπου MATCH είναι σε μορφή κειμένου, περικλείστε το σε εισαγωγικά.
  • Εάν η lookup_value δεν βρίσκεται στο συστοιχία αναζήτησης, τη # N / A το σφάλμα επιστρέφεται.

Τώρα που γνωρίζουμε τις βασικές χρήσεις των συναρτήσεων INDEX και MATCH στο Excel, ας σηκώσουμε τα μανίκια και ετοιμαζόμαστε να συνδυάσουμε τις δύο συναρτήσεις.


Πώς να συνδυάσετε το INDEX και το MATCH στο Excel

Δείτε το παρακάτω παράδειγμα για να καταλάβετε πώς μπορούμε να συνδυάσουμε τις συναρτήσεις INDEX και MATCH:

Να βρω Το σκορ της Έβελιν, με τη γνώση ότι οι βαθμολογίες των εξετάσεων είναι στο 3XNUMXη στήλη, μπορούμε χρησιμοποιήστε τη λειτουργία MATCH για να προσδιορίσετε αυτόματα τη θέση της σειράς χωρίς να χρειάζεται να το μετρήσετε χειροκίνητα. Στη συνέχεια, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση INDEX για να ανακτήσουμε το τιμή στη διασταύρωση της καθορισμένης σειράς και της 3ης στήλης:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

αντιστοιχία ευρετηρίου excel 4

Λοιπόν, επειδή η φόρμουλα μπορεί να φαίνεται λίγο περίπλοκη, ας εξετάσουμε κάθε μέρος της.

αντιστοιχία ευρετηρίου excel 5

Η ΔΕΊΚΤΗΣ Ο τύπος περιέχει τρία ορίσματα:

  • σειρά_αριθμός: ΑΓΩΝΑΣ ("Έβελιν",A2:A11,0) παρέχει το INDEX με τη θέση γραμμής της τιμής "Evelyn"στην γκάμα A2: A11, Η οποία είναι 5.
  • στήλη_αριθμός: 3 καθορίζει το 3rd στήλη για INDEX για να εντοπίσετε τη βαθμολογία μέσα στον πίνακα.
  • παράταξη: Α2: Γ11 δίνει εντολή στο INDEX να επιστρέψει την τιμή που ταιριάζει στην τομή της καθορισμένης γραμμής και στήλης, εντός του εύρους που εκτείνεται από A2 έως C11. Τελικά, παίρνουμε το αποτέλεσμα 90.

Στον παραπάνω τύπο, χρησιμοποιήσαμε μια κωδικοποιημένη τιμή, "Έβελιν". Ωστόσο, στην πράξη, οι τιμές με σκληρό κώδικα δεν είναι πρακτικές, καθώς θα απαιτούσαν τροποποίηση κάθε φορά που επιδιώκουμε να αναζητήσουμε διαφορετικά δεδομένα, όπως τη βαθμολογία για έναν άλλο μαθητή. Σε τέτοια σενάρια, μπορούμε να χρησιμοποιήσουμε αναφορές κελιών για να δημιουργήσουμε δυναμικούς τύπους. Για παράδειγμα, σε αυτή την περίπτωση, θα το κάνω αλλάξτε το "Evelyn" σε F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

(AD) Απλοποίηση αναζητήσεων με Kutools: Δεν απαιτείται πληκτρολόγηση τύπου!

Kutools για Excel's Σούπερ Αναζήτηση παρέχει μια ποικιλία εργαλείων αναζήτησης προσαρμοσμένο για να καλύψει κάθε σας ανάγκη. Είτε εκτελείτε αναζητήσεις πολλαπλών κριτηρίων, είτε πραγματοποιείτε αναζήτηση σε πολλά φύλλα είτε πραγματοποιείτε αναζήτηση ένα προς πολλά, Σούπερ Αναζήτηση απλοποιεί τη διαδικασία με λίγα μόνο κλικ. Εξερευνήστε αυτές τις δυνατότητες για να δούμε πώς Σούπερ Αναζήτηση αλλάζει τον τρόπο αλληλεπίδρασης με τα δεδομένα του Excel. Πείτε αντίο στην ταλαιπωρία να θυμάστε πολύπλοκους τύπους.

Εργαλεία αναζήτησης Kutools

Kutools για Excel - Σας δίνει τη δυνατότητα με περισσότερες από 300 εύχρηστες λειτουργίες για αβίαστη παραγωγικότητα. Μη χάσετε την ευκαιρία να το δοκιμάσετε με μια δωρεάν δοκιμή 30 ημερών με πλήρεις δυνατότητες! Ξεκινήστε τώρα!


Παραδείγματα τύπου INDEX και MATCH

Σε αυτό το μέρος, θα μιλήσουμε για διαφορετικές περιστάσεις για τη χρήση των συναρτήσεων INDEX και MATCH για την κάλυψη διαφορετικών αναγκών.


INDEX και MATCH για εφαρμογή αμφίδρομης αναζήτησης

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

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

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

αντιστοιχία ευρετηρίου excel 6

Πώς λειτουργεί αυτός ο τύπος:
  • Ο πρώτος τύπος MATCH βρίσκει τη θέση της Έβελιν στη λίστα A2:A11, παρέχοντας 5 ως αριθμός σειράς στο INDEX.
  • Ο δεύτερος τύπος MATCH καθορίζει τη στήλη για τις βαθμολογίες και αποδίδει 3 ως αριθμός στήλης στο INDEX.
  • Ο τύπος απλοποιεί να =INDEX(A2:C11,5,3)και επιστρέφει το INDEX 90.

INDEX και MATCH για εφαρμογή αριστερής αναζήτησης

Τώρα, ας εξετάσουμε ένα σενάριο όπου πρέπει να προσδιορίσετε την τάξη της Έβελυν. Ίσως έχετε παρατηρήσει ότι η στήλη κλάσης βρίσκεται στα αριστερά της στήλης ονόματος, μια κατάσταση που υπερβαίνει τις δυνατότητες μιας άλλης ισχυρής συνάρτησης αναζήτησης του Excel, του VLOOKUP.

Στην πραγματικότητα, η δυνατότητα εκτέλεσης αναζητήσεων στην αριστερή πλευρά είναι μία από τις πτυχές όπου ο συνδυασμός INDEX και MATCH υπερτερεί του VLOOKUP.

Να βρω Η τάξη της Έβελιν, χρησιμοποιήστε τον ακόλουθο τύπο για να αναζητήστε την Evelyn στο B2:B11 και ανακτήστε την αντίστοιχη τιμή από το A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

αντιστοιχία ευρετηρίου excel 7

Σημείωση: Μπορείτε εύκολα να εκτελέσετε μια αριστερή αναζήτηση για συγκεκριμένες τιμές χρησιμοποιώντας το ΑΝΑΖΗΤΗΣΗ από δεξιά προς τα αριστερά χαρακτηριστικό του Kutools για Excel με λίγα μόνο κλικ. Για να εφαρμόσετε τη δυνατότητα, μεταβείτε στο Kutools καρτέλα στο Excel και κάντε κλικ Σούπερ Αναζήτηση > ΑΝΑΖΗΤΗΣΗ από δεξιά προς τα αριστερά στο Τύπος ομάδα.

ΑΝΑΖΗΤΗΣΗ από δεξιά προς τα αριστερά

Εάν δεν έχετε εγκαταστήσει το Kutools, κάντε κλικ εδώ για να κατεβάστε και αποκτήστε μια δωρεάν δοκιμή 30 ημερών με πλήρεις δυνατότητες!


INDEX και MATCH για εφαρμογή αναζήτησης με διάκριση πεζών-κεφαλαίων

Οι λειτουργίες MATCH είναι εγγενώς χωρίς διάκριση πεζών-κεφαλαίων. Ωστόσο, όταν απαιτείται ο τύπος σας να διαφοροποιεί κεφαλαίους και πεζούς χαρακτήρες, μπορείτε να τον βελτιώσετε ενσωματώνοντας το ΑΚΡΙΒΗΣ λειτουργία. Συνδυάζοντας τη συνάρτηση MATCH με το EXACT σε έναν τύπο INDEX, μπορείτε στη συνέχεια να εκτελέσετε αποτελεσματικά μια αναζήτηση με διάκριση πεζών-κεφαλαίων, όπως φαίνεται παρακάτω:

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))
  • παράταξη αναφέρεται στην περιοχή από την οποία θέλετε να επιστρέψετε την τιμή.
  • lookup_value αναφέρεται στην τιμή που αντιστοιχεί, λαμβάνοντας υπόψη την περίπτωση των χαρακτήρων, στο συστοιχία αναζήτησης.
  • συστοιχία αναζήτησης αναφέρεται στο εύρος των κελιών με τα οποία θέλετε να συγκρίνετε το MATCH lookup_value.

Για παράδειγμα, να ξέρεις Η βαθμολογία των εξετάσεων του JIMMY, χρησιμοποιήστε τον ακόλουθο τύπο:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Σημείωση: Αυτός είναι ένας τύπος πίνακα που απαιτεί να εισαγάγετε με Ctrl + αλλαγή + εισάγετε, εκτός από το Excel 365 και το Excel 2021.

αντιστοιχία ευρετηρίου excel 8

Πώς λειτουργεί αυτός ο τύπος:
  • Η συνάρτηση EXACT συγκρίνει "JIMMY" με τις τιμές στη λίστα A2: A11, λαμβάνοντας υπόψη την περίπτωση των χαρακτήρων: Εάν οι δύο συμβολοσειρές ταιριάζουν ακριβώς, λαμβάνοντας υπόψη τους κεφαλαίους και πεζούς χαρακτήρες, το EXACT επιστρέφει ΑΛΗΘΙΝΗ; διαφορετικά, επιστρέφει ΨΕΥΔΗΣ. Ως αποτέλεσμα, παίρνουμε έναν πίνακα που περιέχει τιμές TRUE και FALSE.
  • Στη συνέχεια, η συνάρτηση MATCH ανακτά το θέση της πρώτης TRUE τιμής στον πίνακα, που θα έπρεπε να είναι 10.
  • Τέλος, το INDEX ανακτά την τιμή στο 10η θέση που παρέχεται από το MATCH στον πίνακα.

:

  • Θυμηθείτε να εισάγετε σωστά τον τύπο πατώντας Ctrl + Shift + Εισαγωγή, εκτός αν χρησιμοποιείτε Excel 365 or Excel 2021, σε αυτήν την περίπτωση, απλώς πατήστε εισάγετε.
  • Ο παραπάνω τύπος πραγματοποιεί αναζήτηση σε μια ενιαία λίστα C2: C11. Εάν θέλετε να κάνετε αναζήτηση σε ένα εύρος με πολλές στήλες και σειρές, ας πούμε Α2: Γ11, θα πρέπει να προσφέρετε αριθμούς στηλών και σειρών στο INDEX:
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • Σε αυτόν τον αναθεωρημένο τύπο, χρησιμοποιούμε τη συνάρτηση MATCH για να αναζητήσουμε "JIMMY", λαμβάνοντας υπόψη την περίπτωση των χαρακτήρων, στην περιοχή A2: A11, και μόλις βρούμε ένα ταίριασμα, ανακτούμε την αντίστοιχη τιμή από το 3η στήλη του εύρους Α2: Γ11.

INDEX και MATCH για να βρείτε την πλησιέστερη αντιστοίχιση

Στο Excel, ενδέχεται να αντιμετωπίσετε καταστάσεις όπου πρέπει να βρείτε την πλησιέστερη ή την πλησιέστερη αντιστοίχιση σε μια συγκεκριμένη τιμή μέσα σε ένα σύνολο δεδομένων. Σε τέτοια σενάρια, η χρήση ενός συνδυασμού λειτουργιών INDEX και MATCH, μαζί με τις λειτουργίες ABS και MIN, μπορεί να είναι απίστευτα χρήσιμη.

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • παράταξη αναφέρεται στην περιοχή από την οποία θέλετε να επιστρέψετε την τιμή.
  • συστοιχία αναζήτησης αναφέρεται στο εύρος τιμών όπου θέλετε να βρείτε την πλησιέστερη αντιστοίχιση lookup_value.
  • lookup_value αναφέρεται στην τιμή για την εύρεση της πλησιέστερης αντιστοιχίας.

Για παράδειγμα, για να μάθετε του οποίου η βαθμολογία είναι πλησιέστερη στο 85, χρησιμοποιήστε τον ακόλουθο τύπο για να αναζητήστε την πλησιέστερη βαθμολογία στο 85 στο C2:C11 και ανακτήστε την αντίστοιχη τιμή από το A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Σημείωση: Αυτός είναι ένας τύπος πίνακα που απαιτεί να εισαγάγετε με Ctrl + αλλαγή + εισάγετε, εκτός από το Excel 365 και το Excel 2021.

Πώς λειτουργεί αυτός ο τύπος:
  • ABS(C2:C11-85) υπολογίζει την απόλυτη διαφορά μεταξύ κάθε τιμής στο εύρος C2: C11 και 85, με αποτέλεσμα μια σειρά από απόλυτες διαφορές.
  • MIN(ABS(C2:C11-85)) βρίσκει την ελάχιστη τιμή στον πίνακα των απόλυτων διαφορών, που αντιπροσωπεύει την πλησιέστερη διαφορά στο 85.
  • Η συνάρτηση MATCH MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) τότε βρίσκει τη θέση της ελάχιστης απόλυτης διαφοράς στον πίνακα των απόλυτων διαφορών, η οποία θα έπρεπε να είναι 10.
  • Τέλος, το INDEX ανακτά την τιμή στη θέση στη λίστα A2: A11 που αντιστοιχεί στην πλησιέστερη βαθμολογία 85 στο εύρος C2: C11.

:

  • Θυμηθείτε να εισάγετε σωστά τον τύπο πατώντας Ctrl + Shift + Εισαγωγή, εκτός αν χρησιμοποιείτε Excel 365 or Excel 2021, σε αυτήν την περίπτωση, απλώς πατήστε εισάγετε.
  • Σε περίπτωση ισοπαλίας, αυτή η φόρμουλα θα επιστρέψει τον πρώτο αγώνα.
  • Να βρω η πλησιέστερη αντιστοιχία στον μέσο όρο βαθμολογίας, αντικαταστήστε 85 στον τύπο με AVERAGE(C2:C11).

INDEX και MATCH για να εφαρμόσετε μια αναζήτηση με πολλά κριτήρια

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

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…), 0))

√ Σημείωση: Αυτός είναι ένας τύπος πίνακα που απαιτεί να εισαγάγετε με Ctrl + αλλαγή + εισάγετε. Στη συνέχεια, στη γραμμή τύπων θα εμφανιστεί ένα ζευγάρι σγουρά αγκύλες.

  • παράταξη αναφέρεται στην περιοχή από την οποία θέλετε να επιστρέψετε την τιμή.
  • (lookup_value=lookup_array) αντιπροσωπεύει μια ενιαία συνθήκη. Αυτή η συνθήκη ελέγχει εάν ένα συγκεκριμένο lookup_value ταιριάζει με τις τιμές στο συστοιχία αναζήτησης.

Για παράδειγμα, για να βρείτε το βαθμολογία του Coco της κατηγορίας Α, του οποίου η ημερομηνία γέννησης είναι 7/2/2008, μπορείτε να χρησιμοποιήσετε τον ακόλουθο τύπο:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

αντιστοιχία ευρετηρίου excel 9

:

  • Σε αυτόν τον τύπο, αποφεύγουμε τις τιμές σκληρού κωδικοποίησης, καθιστώντας εύκολη τη λήψη βαθμολογίας με διαφορετικές πληροφορίες τροποποιώντας τις τιμές στα κελιά G2, G3, να G4.
  • Θα πρέπει να εισάγετε τον τύπο πατώντας Ctrl + Shift + Εισαγωγή εκτός από μέσα Excel 365 or Excel 2021, όπου μπορείτε απλά να πατήσετε εισάγετε.
    Εάν ξεχνάτε συνεχώς να χρησιμοποιείτε Ctrl + Shift + Εισαγωγή για να συμπληρώσετε τη φόρμουλα και να λάβετε λανθασμένα αποτελέσματα, χρησιμοποιήστε την παρακάτω ελαφρώς πιο περίπλοκη φόρμουλα, με την οποία μπορείτε να ολοκληρώσετε με μια απλή εισάγετε κλειδί:
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • Οι τύποι μπορεί να είναι περίπλοκοι και δύσκολο να θυμόμαστε. Για να απλοποιήσετε τις αναζητήσεις πολλαπλών κριτηρίων χωρίς την ανάγκη μη αυτόματης εισαγωγής τύπων, σκεφτείτε να το χρησιμοποιήσετε Kutools για Excel'S Αναζήτηση πολλαπλών συνθηκών χαρακτηριστικό. Αφού εγκαταστήσετε το Kutools, μεταβείτε στο Kutools καρτέλα στο Excel και κάντε κλικ Σούπερ Αναζήτηση > Αναζήτηση πολλαπλών συνθηκών στο Τύπος ομάδα.

    Αναζήτηση πολλαπλών συνθηκών

    Εάν δεν έχετε εγκαταστήσει το Kutools, κάντε κλικ εδώ για να κατεβάστε και αποκτήστε μια δωρεάν δοκιμή 30 ημερών με πλήρεις δυνατότητες!


INDEX και MATCH για εφαρμογή αναζήτησης σε πολλές στήλες

Φανταστείτε ένα σενάριο όπου έχετε να κάνετε με πολλές στήλες δεδομένων. Η πρώτη στήλη λειτουργεί ως κλειδί για την ταξινόμηση των δεδομένων στις άλλες στήλες. Για να προσδιορίσετε την κατηγορία ή την ταξινόμηση για μια συγκεκριμένη καταχώρηση, θα πρέπει να πραγματοποιήσετε αναζήτηση στις στήλες δεδομένων και να τη συσχετίσετε με το σχετικό κλειδί στη στήλη αναφοράς.

Για παράδειγμα, στον παρακάτω πίνακα, πώς μπορούμε να αντιστοιχίσουμε τον μαθητή Shawn με την αντίστοιχη τάξη του χρησιμοποιώντας το INDEX και το MATCH; Λοιπόν, μπορείτε να το πετύχετε με μια φόρμουλα, αλλά η φόρμουλα είναι αρκετά εκτενής και μπορεί να είναι δύσκολη η κατανόηση, πόσο μάλλον να θυμάστε και να πληκτρολογήσετε.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Εδώ είναι Kutools για Excel's Ευρετήριο και αντιστοίχιση σε πολλαπλές στήλες η δυνατότητα είναι χρήσιμη. Απλοποιεί τη διαδικασία, καθιστώντας γρήγορη και εύκολη την αντιστοίχιση συγκεκριμένων καταχωρήσεων με τις αντίστοιχες κατηγορίες τους. Για να ξεκλειδώσετε αυτό το ισχυρό εργαλείο και να ταιριάξετε αβίαστα τον Shawn με την τάξη του, απλά κατεβάστε και εγκαταστήστε το πρόσθετο Kutools για Excel, και μετά κάντε τα εξής:

  1. Επιλέξτε το κελί προορισμού όπου θέλετε να εμφανιστεί η αντίστοιχη κλάση.
  2. Στις Kutools κάντε κλικ στην καρτέλα Βοηθός τύπου > Αναζήτηση και αναφορά > Ευρετήριο και αντιστοίχιση σε πολλαπλές στήλες.
  3. αντιστοιχία ευρετηρίου excel 11
  4. Στο αναδυόμενο παράθυρο διαλόγου, κάντε τα εξής:
    1. Κάντε κλικ στο 1ο εικονίδιο αντιστοίχισης ευρετηρίου excel κουμπί δίπλα στο Αναζήτηση_col για να επιλέξετε τη στήλη που περιέχει τις βασικές πληροφορίες που θέλετε να επιστρέψετε, δηλαδή τα ονόματα των κλάσεων. (Μπορείτε να επιλέξετε μόνο μία στήλη εδώ.)
    2. Κάντε κλικ στο 2ο εικονίδιο αντιστοίχισης ευρετηρίου excel κουμπί δίπλα στο Table_rng για να επιλέξετε τα κελιά που ταιριάζουν με τις τιμές στα επιλεγμένα Αναζήτηση_col, δηλαδή τα ονόματα των μαθητών.
    3. Κάντε κλικ στο 3ο εικονίδιο αντιστοίχισης ευρετηρίου excel κουμπί δίπλα στο Αναζήτηση_τιμής για να επιλέξετε το κελί που περιέχει το όνομα του μαθητή που θέλετε να αντιστοιχίσετε με την τάξη του, σε αυτήν την περίπτωση, Shawn.
    4. Πατήστε OK.
    5. αντιστοιχία ευρετηρίου excel 12

Αποτέλεσμα

Το Kutools έχει δημιουργήσει αυτόματα τον τύπο και θα δείτε αμέσως το όνομα της τάξης του Shawn να εμφανίζεται στο κελί προορισμού.

Σημείωση: Για να δοκιμάσετε το Ευρετήριο και αντιστοίχιση σε πολλαπλές στήλες χαρακτηριστικό, θα χρειαστείτε εγκατεστημένο το Kutools για Excel στον υπολογιστή σας. Εάν δεν το έχετε εγκαταστήσει ακόμα, μην περιμένετε --- Κάντε λήψη και εγκαταστήστε το τώρα για μια δωρεάν δοκιμή 30 ημερών χωρίς περιορισμούς. Κάντε το Excel να λειτουργεί πιο έξυπνα σήμερα!


INDEX και MATCH για αναζήτηση για πρώτη μη κενή τιμή

Για να ανακτήσετε την πρώτη μη κενή τιμή, αγνοώντας τα σφάλματα, από μια στήλη ή μια γραμμή, μπορείτε να χρησιμοποιήσετε έναν τύπο που βασίζεται στις συναρτήσεις INDEX και MATCH. Ωστόσο, εάν δεν θέλετε να αγνοήσετε τα σφάλματα από το εύρος σας, προσθέστε τη συνάρτηση ISBLANK.

  • Λάβετε την πρώτη μη κενή τιμή σε μια στήλη ή μια σειρά αγνοώντας τα σφάλματα:
  • =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))
  • Λάβετε την πρώτη μη κενή τιμή σε μια στήλη ή μια σειρά συμπεριλαμβανομένων των σφαλμάτων:
  • =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

:


INDEX και MATCH για αναζήτηση της πρώτης αριθμητικής τιμής

Για να ανακτήσετε την πρώτη αριθμητική τιμή από μια στήλη ή μια γραμμή, χρησιμοποιήστε τον τύπο που βασίζεται στις συναρτήσεις INDEX, MATCH και ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

:


INDEX και MATCH για αναζήτηση για MAX ή MIN συσχετίσεις

Εάν χρειάζεται να ανακτήσετε μια τιμή που σχετίζεται με τη μέγιστη ή την ελάχιστη τιμή εντός ενός εύρους, μπορείτε να χρησιμοποιήσετε τη συνάρτηση MAX ή MIN μαζί με τις συναρτήσεις INDEX και MATCH.

  • INDEX και MATCH για να ανακτήσετε μια τιμή που σχετίζεται με τη μέγιστη τιμή:
  • =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
  • INDEX και MATCH για να ανακτήσετε μια τιμή που σχετίζεται με την ελάχιστη τιμή:
  • =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
  • Υπάρχουν δύο ορίσματα στους παραπάνω τύπους:
    • παράταξη αναφέρεται στην περιοχή από την οποία θέλετε να επιστρέψετε τις σχετικές πληροφορίες.
    • συστοιχία αναζήτησης αντιπροσωπεύει το σύνολο των τιμών που πρέπει να εξεταστούν ή να αναζητηθούν για συγκεκριμένα κριτήρια, π.χ. μέγιστες ή ελάχιστες τιμές.

Για παράδειγμα, αν θέλετε να προσδιορίσετε που έχει την υψηλότερη βαθμολογία, χρησιμοποιήστε τον ακόλουθο τύπο:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Πώς λειτουργεί αυτός ο τύπος:
  • MAX(C2:C11) αναζητά την υψηλότερη τιμή στο εύρος C2: C11, Η οποία είναι 96.
  • Στη συνέχεια, η συνάρτηση MATCH βρίσκει τη θέση της υψηλότερης τιμής στον πίνακα C2: C11, που πρέπει να είναι 1.
  • Τέλος, το INDEX ανακτά το 1τιμή st στη λίστα A2: A11.

:

  • Σε περίπτωση περισσότερων από μία μέγιστες ή ελάχιστες τιμές, όπως φαίνεται στο παραπάνω παράδειγμα όπου δύο μαθητές πέτυχαν την ίδια υψηλότερη βαθμολογία, αυτός ο τύπος θα επιστρέψει την πρώτη αντιστοίχιση.
  • Για να προσδιορίσετε ποιος έχει τη χαμηλότερη βαθμολογία, χρησιμοποιήστε τον ακόλουθο τύπο:
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))

Συμβουλή: Προσαρμόστε τα δικά σας μηνύματα σφάλματος #N/A

Όταν εργάζεστε με τις συναρτήσεις INDEX και MATCH του Excel, ενδέχεται να αντιμετωπίσετε το σφάλμα #N/A όταν δεν υπάρχει αντίστοιχο αποτέλεσμα. Για παράδειγμα, στον παρακάτω πίνακα, όταν προσπαθείτε να βρείτε τη βαθμολογία μιας μαθήτριας που ονομάζεται Samantha, εμφανίζεται ένα σφάλμα #N/A καθώς δεν υπάρχει στο σύνολο δεδομένων.

αντιστοιχία ευρετηρίου excel 15

Για να κάνετε τα υπολογιστικά φύλλα σας πιο φιλικά προς το χρήστη, μπορείτε να προσαρμόσετε αυτό το μήνυμα σφάλματος τυλίγοντας τον τύπο INDEX MATCH στη συνάρτηση IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

αντιστοιχία ευρετηρίου excel 16

:

  • Μπορείτε να προσαρμόσετε τα μηνύματα σφάλματος αντικαθιστώντας "Δεν βρέθηκε" με οποιοδήποτε κείμενο της επιλογής σας.
  • Εάν θέλετε να χειριστείτε όλα τα σφάλματα, όχι μόνο το #N/A, σκεφτείτε να χρησιμοποιήσετε το ΑΦΑΝΙΣΤΗΡΙΟ λειτουργία αντί για IFNA:
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

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

Παραπάνω είναι όλο το σχετικό περιεχόμενο που σχετίζεται με τις συναρτήσεις INDEX και MATCH στο Excel. Ελπίζω να βρείτε το σεμινάριο χρήσιμο. Αν θέλετε να εξερευνήσετε περισσότερες συμβουλές και κόλπα για το Excel, κάντε κλικ εδώ για να αποκτήσετε πρόσβαση στην εκτενή συλλογή μας με πάνω από χιλιάδες μαθήματα.

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations