Σε αυτό το άρθρο, θα μάθουμε πώς να χρησιμοποιούμε τη συνάρτηση INDEX και MATCH για την αναζήτηση τιμής στο Excel.
Λειτουργία INDEX & MATCH για αντικατάσταση της λειτουργίας vlookup
Συνήθως χρησιμοποιούμε τη συνάρτηση VLOOKUP για να βρούμε μια τιμή γνωρίζοντας το ευρετήριο σειράς και τον αριθμό στήλης. Για τη συνάρτηση VLOOKUP πρέπει να έχουμε τον αριθμό στήλης και τα κριτήρια αντιστοίχισης γραμμών και το πιο σημαντικό πράγμα που η τιμή πρέπει να διαπιστώσουμε ότι βρίσκεται στα δεξιά της τιμής αντιστοίχισης. Αλλά τα δεδομένα δεν το υποστηρίζουν πάντα. Πολλές φορές χρειάζεται απλά να αναζητήσουμε τιμές χωρίς να γνωρίζουμε το ευρετήριο γραμμών ή στηλών, απλώς να έχουμε τις τιμές που ταιριάζουν. Για παράδειγμα: εύρεση μισθού υπαλλήλου που έχει ταυτότητα υπαλλήλου. Σε αυτό θα αναζητήσουμε το Αναγνωριστικό υπαλλήλου για το ευρετήριο σειρών και το Μισθός για το ευρετήριο στηλών. Ας μάθουμε πώς μπορούμε να κάνουμε αυτήν την αναζήτηση χρησιμοποιώντας INDEX & MATCH.
Πώς να λύσετε το πρόβλημα;
Για να κατανοήσει πρώτα ο τύπος, πρέπει να αναθεωρήσουμε λίγο τις ακόλουθες συναρτήσεις
- Λειτουργία INDEX
- Συνάρτηση MATCH
Τώρα θα φτιάξουμε έναν τύπο χρησιμοποιώντας τις παραπάνω συναρτήσεις. Η συνάρτηση αντιστοίχισης θα επιστρέψει το ευρετήριο της τιμής αναζήτησης1 στο πεδίο κεφαλίδας γραμμής. Και μια άλλη συνάρτηση MATCH θα επιστρέψει το ευρετήριο της τιμής αναζήτησης 2 από το πεδίο κεφαλίδας στήλης. Οι αριθμοί ευρετηρίου θα εισαχθούν τώρα στη συνάρτηση INDEX για να λάβετε τις τιμές κάτω από την τιμή αναζήτησης από τα δεδομένα του πίνακα.
Γενικός τύπος:
| = INDEX (δεδομένα, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0))) |
δεδομένα : πίνακας τιμών μέσα στον πίνακα χωρίς κεφαλίδες
αναζήτηση_τιμής1 : τιμή προς αναζήτηση στο row_header.
row_headers : Σειρά ευρετηρίου ευρετηρίου για αναζήτηση.
αναζήτηση_τιμής1 : τιμή προς αναζήτηση στο column_header.
head_headers : στήλη Ευρετήριο πίνακα για αναζήτηση.
Παράδειγμα:
Οι παραπάνω προτάσεις μπορεί να είναι περίπλοκες στην κατανόηση. Ας το καταλάβουμε λοιπόν χρησιμοποιώντας τον τύπο σε ένα παράδειγμα
Εδώ έχουμε μια λίστα με τις βαθμολογίες που έλαβαν οι μαθητές με τη λίστα θεμάτων τους. Πρέπει να βρούμε τη Βαθμολογία για συγκεκριμένο Φοιτητή (Γκάρι) & Θέμα (Κοινωνικές Σπουδές) όπως φαίνεται στο παρακάτω στιγμιότυπο.
Η τιμή Student1 πρέπει να ταιριάζει με τον πίνακα Row_header και η τιμή Subject2 πρέπει να ταιριάζει με τον πίνακα Column_header.
Χρησιμοποιήστε τον τύπο στο κελί J6:
| = INDEX (πίνακας, MATCH (J5, σειρά, 0, MATCH (J4, στήλη, 0))) |
Εξήγηση:
- Η συνάρτηση MATCH ταιριάζει με την τιμή Student στο κελί J4 με τον πίνακα κεφαλίδας γραμμής και επιστρέφει τη θέση της 3 ως αριθμός.
- Η συνάρτηση MATCH αντιστοιχεί στην τιμή Subject στο κελί J5 με τον πίνακα κεφαλίδας στήλης και επιστρέφει τη θέση της 4 ως αριθμός.
- Η συνάρτηση INDEX παίρνει τον αριθμό ευρετηρίου γραμμών και στηλών και αναζητά τα δεδομένα του πίνακα και επιστρέφει την αντιστοιχισμένη τιμή.
- Το όρισμα τύπου MATCH είναι σταθερό στο 0. Καθώς ο τύπος θα εξαγάγει την ακριβή αντιστοίχιση.
Εδώ οι τιμές στον τύπο δίνονται ως αναφορές κελιών και οι γραμμές_καθοριστών, πίνακας και κεφαλές στήλης δίνονται ως ονομαζόμενες περιοχές.
Όπως μπορείτε να δείτε στο παραπάνω στιγμιότυπο, πήραμε τη Βαθμολογία που έλαβε ο μαθητής Γκάρι στο Θέμα Κοινωνικές σπουδές ως 36. Και αποδεικνύει ότι ο τύπος λειτουργεί καλά και για αμφιβολίες δείτε τις παρακάτω σημειώσεις για κατανόηση.
Τώρα θα χρησιμοποιήσουμε την κατά προσέγγιση αντιστοίχιση με κεφαλίδες γραμμών και κεφαλίδες στηλών ως αριθμούς. Η αντιστοίχιση κατά προσέγγιση λαμβάνει μόνο τις τιμές αριθμών, καθώς δεν υπάρχει περίπτωση να εφαρμοστεί σε τιμές κειμένου
Εδώ έχουμε μια τιμή αξιών σύμφωνα με το ightψος & Πλάτος του προϊόντος. Πρέπει να βρούμε την Τιμή για ένα συγκεκριμένο ightψος (34) & Πλάτος (21) όπως φαίνεται στο παρακάτω στιγμιότυπο.
Η τιμή Height1 πρέπει να ταιριάζει με τον πίνακα Row_header και η τιμή Width2 πρέπει να ταιριάζει με τον πίνακα Column_header.
Χρησιμοποιήστε τον τύπο στο κελί K6:
| = INDEX (δεδομένα, ΤΑΙΡΙΑ (Κ4, ightψος, 1, ΑΓΩΝΑΣ (Κ5, Πλάτος, 1))) |
Εξήγηση:
- Η συνάρτηση MATCH ταιριάζει με την τιμή ightψος στο κελί Κ4 με τον πίνακα κεφαλίδας γραμμής και επιστρέφει τη θέση της 3 ως αριθμός.
- Η συνάρτηση MATCH αντιστοιχεί στην τιμή Πλάτος στο κελί Κ5 με τον πίνακα κεφαλίδας στήλης και επιστρέφει τη θέση της 2 ως αριθμός.
- Η συνάρτηση INDEX παίρνει τον αριθμό ευρετηρίου γραμμών και στηλών και αναζητά τα δεδομένα του πίνακα και επιστρέφει την αντιστοιχισμένη τιμή.
- Το όρισμα τύπου MATCH είναι σταθερό στο 1. Καθώς ο τύπος θα εξάγει την κατά προσέγγιση αντιστοίχιση.
Εδώ οι τιμές στον τύπο δίνονται ως αναφορές κελιών και κεφαλή γραμμής, δεδομένα και κεφαλή στήλης που δίνονται ως ονομαζόμενες περιοχές όπως αναφέρεται στο στιγμιότυπο παραπάνω.
Μπορείτε επίσης να εκτελέσετε ακριβείς αντιστοιχίσεις αναζήτησης χρησιμοποιώντας τη λειτουργία INDEX και MATCH στο Excel. Μάθετε περισσότερα σχετικά με τον τρόπο πραγματοποίησης αναζήτησης ευαίσθητων περιπτώσεων χρησιμοποιώντας τη λειτουργία INDEX & MATCH στο Excel. Μπορείτε επίσης να αναζητήσετε μερικούς αγώνες χρησιμοποιώντας τα μπαλαντέρ στο Excel.
Όπως μπορείτε να δείτε στο παραπάνω στιγμιότυπο, πήραμε την Τιμή που λαμβάνεται ανά ύψος (34) & Πλάτος (21) ως 53.10. Και αποδεικνύει ότι ο τύπος λειτουργεί καλά και για αμφιβολίες δείτε τις παρακάτω σημειώσεις για περισσότερη κατανόηση.
Σημειώσεις:
- Η συνάρτηση επιστρέφει το σφάλμα #NA εάν το όρισμα πίνακα αναζήτησης στη συνάρτηση MATCH είναι πίνακας 2 D που είναι το πεδίο κεφαλίδας των δεδομένων…
- Η συνάρτηση ταιριάζει με την ακριβή τιμή καθώς το όρισμα τύπου αντιστοίχισης με τη συνάρτηση MATCH είναι 0.
- Οι τιμές αναζήτησης μπορούν να δοθούν ως αναφορά κελιού ή απευθείας χρησιμοποιώντας το σύμβολο εισαγωγής (") στον τύπο ως επιχειρήματα.
Ελπίζουμε ότι αυτό το άρθρο σχετικά με τον τρόπο χρήσης της λειτουργίας INDEX και MATCH για αναζήτηση τιμής στο Excel είναι επεξηγηματικό. Βρείτε περισσότερα άρθρα σχετικά με τον υπολογισμό των τιμών και τους σχετικούς τύπους Excel εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email.
Χρησιμοποιήστε INDEX και MATCH για αναζήτηση τιμής : Ο τύπος INDEX-MATCH χρησιμοποιείται για να αναζητήσει δυναμικά και με ακρίβεια μια τιμή σε έναν δεδομένο πίνακα. Αυτή είναι μια εναλλακτική λύση στη λειτουργία VLOOKUP και ξεπερνά τις αδυναμίες της συνάρτησης VLOOKUP.
Χρησιμοποιήστε το VLOOKUP από δύο ή περισσότερους πίνακες αναζήτησης : Για αναζήτηση από πολλούς πίνακες μπορούμε να ακολουθήσουμε μια προσέγγιση IFERROR. Για αναζήτηση από πολλούς πίνακες, παίρνει το σφάλμα ως διακόπτη για τον επόμενο πίνακα. Μια άλλη μέθοδος μπορεί να είναι η προσέγγιση If.
Πώς να κάνετε Case Sensitive Lookup στο Excel : Η συνάρτηση VLOOKUP του excel δεν έχει διάκριση πεζών -κεφαλαίων και θα επιστρέψει την πρώτη αντιστοιχισμένη τιμή από τη λίστα. Το INDEX-MATCH δεν αποτελεί εξαίρεση, αλλά μπορεί να τροποποιηθεί ώστε να είναι κεφαλαίο. Ας δούμε πώς…
Αναζήτηση που εμφανίζεται συχνά κείμενο με κριτήρια στο Excel : Η αναζήτηση εμφανίζεται συχνότερα σε κείμενο σε μια περιοχή που χρησιμοποιούμε τη λειτουργία INDEX-MATCH με τη λειτουργία MODE. Εδώ είναι η μέθοδος.
Δημοφιλή άρθρα:
Πώς να χρησιμοποιήσετε τη συνάρτηση IF στο Excel : Η δήλωση IF στο Excel ελέγχει την κατάσταση και επιστρέφει μια συγκεκριμένη τιμή εάν η συνθήκη είναι TRUE ή επιστρέφει μια άλλη συγκεκριμένη τιμή εάν είναι FALSE.
Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel : Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel : Αυτή είναι μια άλλη βασική λειτουργία του πίνακα ελέγχου. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.