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

2024 Συγγραφέας: Abigail Brown | [email protected]. Τελευταία τροποποίηση: 2023-12-17 06:44

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

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

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

Τι είναι οι συναρτήσεις INDEX και MATCH;

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

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

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

INDEX and MATCH Syntax & Arguments

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

=INDEX(πίνακας, αριθμός_γραμμών, [αριθμός_στήλων])

Το

array είναι το εύρος των κελιών που θα χρησιμοποιεί ο τύπος. Μπορεί να είναι μία ή περισσότερες σειρές και στήλες, όπως A1:D5. Απαιτείται.

row_num είναι η σειρά στον πίνακα από την οποία πρέπει να επιστραφεί μια τιμή, όπως 2 ή 18. Απαιτείται εκτός εάν υπάρχει αριθμός στήλης.

column_num είναι η στήλη στον πίνακα από την οποία επιστρέφεται μια τιμή, όπως 1 ή 9. Είναι προαιρετικό.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value είναι η τιμή που θέλετε να αντιστοιχίσετε στο lookup_array. Μπορεί να είναι αριθμός, κείμενο ή λογική τιμή που πληκτρολογείται με μη αυτόματο τρόπο ή αναφέρεται μέσω αναφοράς κελιού. Αυτό απαιτείται.

Το

lookup_array είναι το εύρος των κελιών προς εξέταση. Μπορεί να είναι μία γραμμή ή μία στήλη, όπως A2:D2 ή G1:G45. Αυτό απαιτείται.

Το

match_type μπορεί να είναι -1, 0, ή 1. Καθορίζει πώς το lookup_value αντιστοιχίζεται με τις τιμές στο lookup_array (δείτε παρακάτω). 1 είναι η προεπιλεγμένη τιμή εάν αυτό το όρισμα παραλειφθεί.

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

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

Παράδειγμα INDEX and MATCH Formulas

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

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

=INDEX(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

=INDEX(2:2, 1)=INDEX(B1:B2, 1)

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

=INDEX(A1:B2, 2, 2) ψάχνει στο A1:B2 για να βρει την τιμή στη δεύτερη στήλη και τη δεύτερη σειρά, που είναι Stacy.
=INDEX(A1:B1, 1) αναζητά μέσω του A1:B1 για να βρει την τιμή στην πρώτη στήλη, η οποία είναι Jon.
=INDEX(2:2, 1) εξετάζει τα πάντα στη δεύτερη σειρά για να εντοπίσει την τιμή στην πρώτη στήλη, η οποία είναι Tim.
=INDEX(B1:B2, 1) αναζητά μέσω B1:B2 για να εντοπίσει την τιμή στην πρώτη σειρά, η οποία είναι Amy.

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

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

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

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

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

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

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

=INDEX(B2:B5, MATCH(F1, A2:A5))

Αυτό το παράδειγμα ενσωματώνει τον τύπο MATCH στον τύπο INDEX. Ο στόχος είναι να προσδιορίσετε το χρώμα του στοιχείου χρησιμοποιώντας τον αριθμό του στοιχείου.

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

Το

MATCH(F1, A2:A5) αναζητά την τιμή F1 (8795) στο σύνολο δεδομένων A2:A5. Αν μετρήσουμε αντίστροφα τη στήλη, μπορούμε να δούμε ότι είναι 2, έτσι ακριβώς κατάλαβε η συνάρτηση MATCH.

Ο πίνακας INDEX είναι B2:B5 αφού τελικά αναζητούμε την τιμή σε αυτήν τη στήλη.

Η συνάρτηση INDEX θα μπορούσε τώρα να ξαναγραφτεί ως εξής, καθώς το 2 είναι αυτό που βρήκε το MATCH: INDEX(B2:B5, 2, [αριθμός_στήλης]).

Δεδομένου ότι το column_num είναι προαιρετικό, μπορούμε να το αφαιρέσουμε για να μείνει με αυτό: INDEX(B2:B5, 2).

Λοιπόν, τώρα, αυτό μοιάζει με έναν κανονικό τύπο INDEX όπου βρίσκουμε την τιμή του δεύτερου στοιχείου στο B2:B5, το οποίο είναι κόκκινο.

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

=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))

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

Το

MATCH(G1, A2:A13, 0) είναι το πρώτο στοιχείο που λύθηκε σε αυτόν τον τύπο. Αναζητά το G1 (η λέξη "Μάιος") στο A2:A13 για να πάρει μια συγκεκριμένη τιμή. Δεν το βλέπουμε εδώ, αλλά είναι 5.

MATCH(G2, B1:E1, 0) είναι ο δεύτερος τύπος MATCH, και είναι πραγματικά παρόμοιος με τον πρώτο, αλλά αντ' αυτού αναζητά το G2 (η λέξη "Πράσινο") στις επικεφαλίδες στηλών στο B1:E1. Αυτό επιλύει σε 3.

Μπορούμε τώρα να ξαναγράψουμε τον τύπο INDEX ως εξής για να οπτικοποιήσουμε τι συμβαίνει: =INDEX(B2:E13, 5, 3). Αυτό αναζητά σε ολόκληρο τον πίνακα, B2:E13, την πέμπτη σειρά και την τρίτη στήλη, η οποία επιστρέφει $180.

Κανόνες MATCH and INDEX

Υπάρχουν πολλά πράγματα που πρέπει να έχετε υπόψη κατά τη σύνταξη τύπων με αυτές τις συναρτήσεις:

Το MATCH δεν κάνει διάκριση πεζών-κεφαλαίων, επομένως τα κεφαλαία και τα πεζά γράμματα αντιμετωπίζονται με τον ίδιο τρόπο κατά την αντιστοίχιση τιμών κειμένου.
Το MATCH επιστρέφει N/A για πολλούς λόγους: εάν ο τύπος_ταιριάσματος είναι 0 και η τιμή_αναζήτησης δεν βρέθηκε εάν ο τύπος_ταιριάσματος είναι -1 και ο πίνακας_αναζήτησης δεν είναι σε φθίνουσα σειρά, εάν ο τύπος_ταιριάσματος είναι 1 και ο πίνακας_αναζήτησης δεν είναι σε αύξουσα σειρά και αν το lookup_array δεν είναι μία γραμμή ή στήλη.
Μπορείτε να χρησιμοποιήσετε έναν χαρακτήρα μπαλαντέρ στο όρισμα lookup_value εάν το match_type είναι 0 και το lookup_value είναι μια συμβολοσειρά κειμένου. Ένα ερωτηματικό ταιριάζει με οποιονδήποτε μεμονωμένο χαρακτήρα και ένας αστερίσκος ταιριάζει με οποιαδήποτε ακολουθία χαρακτήρων (π.ζ., =MATCH("Jo", 1:1, 0)). Για να χρησιμοποιήσετε το MATCH για να βρείτε ένα πραγματικό ερωτηματικό ή έναν αστερίσκο, πληκτρολογήστε πρώτα ~.
Το INDEX επιστρέφει REF! αν οι row_num και column_num δεν δείχνουν σε ένα κελί μέσα στον πίνακα.

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

Η συνάρτηση MATCH είναι παρόμοια με το LOOKUP, αλλά το MATCH επιστρέφει τη θέση του στοιχείου αντί για το ίδιο το στοιχείο.

VLOOKUP είναι μια άλλη συνάρτηση αναζήτησης που μπορείτε να χρησιμοποιήσετε στο Excel, αλλά σε αντίθεση με το MATCH που απαιτεί INDEX για σύνθετες αναζητήσεις, οι τύποι VLOOKUP χρειάζονται μόνο αυτή τη συνάρτηση.