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

Τι πρέπει να γνωρίζετε

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

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

Ποιες είναι οι λειτουργίες INDEX και MATCH;

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

INDEX and MATCH Σύνταξη & Επιχειρήματα

Έτσι πρέπει να γραφτούν και οι δύο συναρτήσεις για να τις κατανοήσει το Excel:

= ΔΕΙΚΤΗΣ(πίνακας, αριθμός_αριθ, [column_num])

πίνακας είναι το εύρος των κελιών που θα χρησιμοποιεί ο τύπος. Μπορεί να είναι μία ή περισσότερες σειρές και στήλες, όπως A1: D5. Απαιτείται.
αριθμός_αριθ είναι η σειρά του πίνακα από την οποία θα επιστρέψει μια τιμή, όπως 2 ή 18. Απαιτείται εκτός αν στήλη_αριθ είναι παρών.
στήλη_αριθ είναι η στήλη του πίνακα από την οποία θα επιστρέψει μια τιμή, όπως 1 ή 9. Είναι προαιρετικό.

= ΑΛΛΑΓΗ(αναζήτηση_τιμής, lookup_array, [match_type])

αναζήτηση_τιμής είναι η τιμή στην οποία θέλετε να ταιριάξετε lookup_array. Μπορεί να είναι ένας αριθμός, κείμενο ή λογική τιμή που πληκτρολογείται χειροκίνητα ή αναφέρεται μέσω αναφοράς κελιού. Αυτό απαιτείται.
lookup_array είναι το εύρος των κελιών που πρέπει να κοιτάξετε. Μπορεί να είναι μία μόνο σειρά ή μία στήλη, όπως A2: D2 ή G1: G45. Αυτό απαιτείται.
τύπος αντιστοίχισης μπορεί να είναι -1, 0, ή 1. Καθορίζει πώς αναζήτηση_τιμής ταιριάζει με τιμές σε lookup_array (Δες παρακάτω). 1 είναι η προεπιλεγμένη τιμή εάν παραλείπεται αυτό το όρισμα.

Ποιος τύπος αντιστοίχισης θα χρησιμοποιηθεί
Τύπος αντιστοίχισης	Τι κάνει	Κανόνας	Παράδειγμα
1	Βρίσκει τη μεγαλύτερη τιμή που είναι μικρότερη ή ίση με αναζήτηση_τιμής.	ο lookup_array οι τιμές πρέπει να τοποθετηθούν σε αύξουσα σειρά (π.χ. -2, -1, 0, 1, 2 ή AZ, ή FALSE, TRUE.	αναζήτηση_τιμής είναι 25 αλλά λείπει από lookup_array, έτσι επιστρέφεται η θέση του επόμενου μικρότερου αριθμού, όπως το 22.
0	Βρίσκει την πρώτη τιμή που είναι ακριβώς ίση με αναζήτηση_τιμής.	ο lookup_array οι τιμές μπορούν να είναι με οποιαδήποτε σειρά.	αναζήτηση_τιμής είναι 25, οπότε επιστρέφει τη θέση του 25.
-1	Βρίσκει τη μικρότερη τιμή που είναι μεγαλύτερη ή ίση με αναζήτηση_τιμής.	ο lookup_array οι τιμές πρέπει να τοποθετηθούν σε φθίνουσα σειρά (π.χ. 2, 1, 0, -1, -2).	αναζήτηση_τιμής είναι 25 αλλά λείπει από lookup_array, έτσι επιστρέφεται η θέση του επόμενου μεγαλύτερου αριθμού, όπως το 34.

Χρήση 1 ή -1 για περιόδους που πρέπει να εκτελέσετε μια κατά προσέγγιση αναζήτηση σε μια κλίμακα, όπως όταν ασχολείστε με αριθμούς και όταν οι προσεγγίσεις είναι εντάξει. Αλλά θυμηθείτε ότι εάν δεν προσδιορίσετε τύπος αντιστοίχισης, 1 θα είναι η προεπιλογή, η οποία μπορεί να παρακάμψει τα αποτελέσματα εάν θέλετε πραγματικά έναν ακριβή αγώνα.

Παράδειγμα Τύποι INDEX και MATCH

Πριν εξετάσουμε πώς να συνδυάσουμε το INDEX και το MATCH σε έναν τύπο, πρέπει να κατανοήσουμε πώς λειτουργούν αυτές οι λειτουργίες μόνες τους.

INDEX Παραδείγματα

= ΔΕΙΚΤΗΣ (A1: B2,2,2)
= ΔΕΙΚΤΗΣ (A1: B1,1)
= ΔΕΙΚΤΗΣ (2: 2,1)
= ΔΕΙΚΤΗΣ (B1: B2,1)

Σε αυτό το πρώτο παράδειγμα, υπάρχουν τέσσερις τύποι INDEX που μπορούμε να χρησιμοποιήσουμε για να λάβουμε διαφορετικές τιμές:

= ΔΕΙΚΤΗΣ (A1: B2,2,2) κοιτάζει μέσα από το A1: B2 για να βρει την τιμή στη δεύτερη στήλη και στη δεύτερη σειρά, που είναι Στάσι.
= ΔΕΙΚΤΗΣ (A1: B1,1) κοιτάζει μέσα από το A1: B1 για να βρει την τιμή στην πρώτη στήλη, που είναι Τζον.
= ΔΕΙΚΤΗΣ (2: 2,1) εξετάζει τα πάντα στη δεύτερη σειρά για να εντοπίσει την τιμή στην πρώτη στήλη, που είναι Τιμ.
= ΔΕΙΚΤΗΣ (B1: B2,1) κοιτάζει μέσα από το B1: B2 για να εντοπίσει την τιμή στην πρώτη σειρά, που είναι Έιμι.

Παραδείγματα ΑΓΩΝΑΣ

= MATCH («Stacy», A2: D2,0)
= ΑΓΩΝΑ (14, D1: D2)
= ΑΓΩΝΑ (14, D1: D2, -1)
= MATCH (13, A1: D1,0)

Ακολουθούν τέσσερα εύκολα παραδείγματα της λειτουργίας MATCH:

= MATCH («Stacy», A2: D2,0) αναζητά Στάσι στο εύρος Α2: Δ2 και επιστρέφει 3 ως αποτέλεσμα.
= ΑΓΩΝΑ (14, D1: D2) αναζητά 14 στο εύρος Δ1: Δ2, αλλά επειδή δεν βρίσκεται στον πίνακα, το MATCH βρίσκει το επόμενο μεγαλύτερη τιμή που είναι μικρότερη ή ίση με 14, που σε αυτήν την περίπτωση είναι 13, που είναι στη θέση του 1 του lookup_array.
= ΑΓΩΝΑ (14, D1: D2, -1) είναι πανομοιότυπος με τον τύπο πάνω από αυτό, αλλά επειδή ο πίνακας δεν είναι σε φθίνουσα σειρά όπως -1 απαιτεί, έχουμε ένα σφάλμα.
= MATCH (13, A1: D1,0) ψάχνει για 13 στην πρώτη σειρά του φύλλου, η οποία επιστρέφει 4 δεδομένου ότι είναι το τέταρτο στοιχείο σε αυτόν τον πίνακα.

Παραδείγματα ΔΕΙΚΤΗΣ-ΑΓΩΝΑΣ

Ακολουθούν δύο παραδείγματα όπου μπορούμε να συνδυάσουμε INDEX και MATCH σε έναν τύπο:

Εύρεση αναφοράς κυψέλης στον πίνακα

= ΔΕΙΚΤΗΣ (B2: B5, MATCH (F1, A2: A5))

Αυτό το παράδειγμα τοποθετείται ο τύπος MATCH στον τύπο INDEX. Ο στόχος είναι να προσδιοριστεί το χρώμα του αντικειμένου χρησιμοποιώντας τον αριθμό είδους. Αν κοιτάξετε την εικόνα, μπορείτε να δείτε στις σειρές «Χωριστά» πώς θα γράφονταν οι τύποι μόνοι τους, αλλά αφού τους τοποθετούμε, αυτό συμβαίνει:

ΑΓΩΝΑ (F1, A2: A5) ψάχνει για το ΣΤ1 τιμή (8795) στο σύνολο δεδομένων Α2: Α5. Αν μετρήσουμε τη στήλη, μπορούμε να δούμε ότι είναι 2, λοιπόν, αυτό ακριβώς καταλάβαινε η λειτουργία MATCH.
Ο πίνακας INDEX είναι Β2: Β5 αφού τελικά αναζητούμε την τιμή σε αυτήν τη στήλη.
Η συνάρτηση INDEX θα μπορούσε πλέον να ξαναγραφεί από τότε 2 είναι αυτό που βρήκε το MATCH: ΔΕΙΚΤΗΣ (B2: B5, 2, [column_num]).
Από στήλη_αριθ είναι προαιρετικό, μπορούμε να το αφαιρέσουμε με αυτό: ΔΕΙΚΤΗΣ (B2: B5,2).
Τώρα, αυτό είναι σαν έναν κανονικό τύπο INDEX όπου βρίσκουμε την τιμή του δεύτερου στοιχείου Β2: Β5, το οποίο είναι το κόκκινο.

Αναζήτηση με επικεφαλίδες σειράς και στήλης

= ΔΕΙΚΤΗΣ (B2: E13, MATCH (H1, A2: A13,0), MATCH (H2, B1: E1,0))

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

ΑΓΩΝΑ (H1, A2: A13,0) είναι το πρώτο στοιχείο που επιλύεται σε αυτόν τον τύπο. Ψάχνει Η1 (η λέξη «Μάιος») σε Α2: Α13 για να πάρετε μια συγκεκριμένη τιμή. Δεν το βλέπουμε εδώ, αλλά είναι 5.
ΑΓΩΝΑ (H2, B1: E1,0) είναι ο δεύτερος τύπος MATCH, και είναι πραγματικά παρόμοιος με τον πρώτο, αλλά αντ ‘αυτού αναζητά Η2 (η λέξη «Πράσινο») στις επικεφαλίδες της στήλης στο Β1: Ε1. Αυτό επιλύει 3.
Τώρα μπορούμε να ξαναγράψουμε τον τύπο INDEX έτσι ώστε να απεικονίσουμε τι συμβαίνει: = ΔΕΙΚΤΗΣ (B2: E13,5,3). Αυτό κοιτάζει σε ολόκληρο τον πίνακα, Β2: Ε13, για την πέμπτη σειρά και την τρίτη στήλη, η οποία επιστρέφει 180 $.

Κανόνες MATCH και INDEX

Υπάρχουν πολλά πράγματα που πρέπει να θυμάστε όταν γράφετε τύπους με αυτές τις λειτουργίες:

Το MATCH δεν είναι διάκριση πεζών-κεφαλαίων, επομένως τα κεφαλαία και τα πεζά γράμματα αντιμετωπίζονται τα ίδια όταν ταιριάζουν με τις τιμές κειμένου.
Επιστρέφει το MATCH # Δ / Α για πολλούς λόγους: εάν τύπος αντιστοίχισης είναι 0 και αναζήτηση_τιμής δεν βρέθηκε εάν τύπος αντιστοίχισης είναι -1 και lookup_array δεν είναι σε φθίνουσα σειρά, εάν τύπος αντιστοίχισης είναι 1 και lookup_array δεν είναι σε αύξουσα σειρά και εάν lookup_array δεν είναι μία γραμμή ή στήλη.
Μπορείτε να χρησιμοποιήσετε έναν χαρακτήρα μπαλαντέρ στο αναζήτηση_τιμής επιχείρημα εάν τύπος αντιστοίχισης είναι 0 και αναζήτηση_τιμής είναι μια συμβολοσειρά κειμένου. Ένα ερωτηματικό ταιριάζει με οποιονδήποτε μεμονωμένο χαρακτήρα και ο αστερίσκος αντιστοιχεί σε οποιαδήποτε ακολουθία χαρακτήρων (π.χ. = MATCH («Jo *», 1: 1,0)). Για να χρησιμοποιήσετε το MATCH για να βρείτε ένα πραγματικό ερωτηματικό ή αστερίσκο, πληκτρολογήστε ~ πρώτα.
Επιστρέφει το INDEX # ΡΕΦ! αν αριθμός_αριθ και στήλη_αριθ μην οδηγείτε σε ένα κελί εντός του πίνακα.

Σχετικές συναρτήσεις του Excel

Η συνάρτηση MATCH είναι παρόμοια με το LOOKUP, αλλά το MATCH επιστρέφει το θέση του αντικειμένου αντί του ίδιου του αντικειμένου. Το VLOOKUP είναι μια άλλη συνάρτηση αναζήτησης που μπορείτε να χρησιμοποιήσετε στο Excel, αλλά σε αντίθεση με το MATCH που απαιτεί INDEX για προχωρημένες αναζητήσεις, οι τύποι VLOOKUP χρειάζονται μόνο αυτήν τη λειτουργία. Περισσότερα από το kalodiozois

Πώς να δημιουργήσετε έναν τύπο αναζήτησης Excel με πολλαπλά κριτήρια
Τρόπος χρήσης της λειτουργίας IF στο Excel
Τρόπος χρήσης της συνάρτησης Excel INDEX
Πώς να δημιουργήσετε μια βάση δεδομένων στο Excel
Πώς να χρησιμοποιήσετε τη συνάρτηση MATCH του Excel για να βρείτε μια τιμή
Πώς να χρησιμοποιήσετε τη συνάρτηση LOOKUP του Excel για να βρείτε πληροφορίες
Πώς να χρησιμοποιήσετε τη συνάρτηση LOOKUP στο Excel
Πώς να χρησιμοποιήσετε το VLOOKUP στα Φύλλα Google

Τρόπος χρήσης του ARRAYFORMULA στα Υπολογιστικά φύλλα Google
Τρόπος συνδυασμού των συναρτήσεων MIN και IF σε έναν τύπο Excel Array
Πώς να συνδυάσετε τις συναρτήσεις ROUND και SUM στο Excel
Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel
Βρείτε τον μεγαλύτερο αρνητικό ή θετικό αριθμό στο Excel
Βρείτε δεδομένα με τις λειτουργίες ROW και COLUMN του Excel
Τρόπος χρήσης της απόλυτης αναφοράς κυττάρων στο Excel
Τρόπος χρήσης Boolean Τιμών (Λογικές τιμές) στο Excel

Τα δικαιώματα απορρήτου σας

Η kalodiozois και οι συνεργάτες μας τρίτων μερών χρησιμοποιούν cookie και επεξεργάζονται προσωπικά δεδομένα, όπως μοναδικά αναγνωριστικά, βάσει της συγκατάθεσής σας για αποθήκευση ή / και πρόσβαση σε πληροφορίες σε μια συσκευή, προβολή εξατομικευμένων διαφημίσεων και για μέτρηση περιεχομένου, πληροφορίες κοινού και ανάπτυξη προϊόντων. Για να αλλάξετε ή να ανακαλέσετε τις επιλογές συγκατάθεσής σας για το kalodiozois.com, συμπεριλαμβανομένου του δικαιώματός σας να αντιταχθείτε όταν χρησιμοποιείται έννομο συμφέρον, κάντε κλικ παρακάτω. Ανά πάσα στιγμή, μπορείτε να ενημερώσετε τις ρυθμίσεις σας μέσω του συνδέσμου «Απόρρητο ΕΕ» στο κάτω μέρος οποιασδήποτε σελίδας. Αυτές οι επιλογές θα σηματοδοτηθούν παγκοσμίως στους συνεργάτες μας και δεν θα επηρεάσουν τα δεδομένα περιήγησης. Λίστα Συνεργατών (προμηθευτών)

Εμείς και οι συνεργάτες μας επεξεργαζόμαστε δεδομένα για:

Ενεργά σαρώστε τα χαρακτηριστικά της συσκευής για αναγνώριση. Χρησιμοποιήστε ακριβή δεδομένα γεωγραφικής τοποθεσίας. Αποθηκεύστε και / ή αποκτήστε πρόσβαση σε πληροφορίες σε μια συσκευή. Επιλέξτε εξατομικευμένο περιεχόμενο. Δημιουργήστε ένα εξατομικευμένο προφίλ περιεχομένου. Μετρήστε την απόδοση των διαφημίσεων. Επιλέξτε βασικές διαφημίσεις. Δημιουργήστε ένα εξατομικευμένο προφίλ διαφημίσεων. Επιλέξτε εξατομικευμένες διαφημίσεις. Εφαρμόστε έρευνα αγοράς για να δημιουργήσετε πληροφορίες κοινού. Μετρήστε την απόδοση περιεχομένου. Ανάπτυξη και βελτίωση προϊόντων. Λίστα Συνεργατών (προμηθευτών)