Σε αυτό το άρθρο, θα μάθουμε πώς να αναζητάμε τιμές σε 2δ πίνακα χρησιμοποιώντας μια συνάρτηση INDEX-MATCH-MATCH στο Excel.
Σενάριο:
Ας υποθέσουμε ότι πρέπει να κάνετε πολλαπλές αναζητήσεις από έναν πίνακα που έχει εκατοντάδες στήλες. Σε τέτοιες περιπτώσεις, η χρήση διαφορετικών τύπων για κάθε αναζήτηση θα πάρει πολύ χρόνο. Τι θα λέγατε για τη δημιουργία μιας δυναμικής φόρμουλας αναζήτησης την οποία μπορείτε να αναζητήσετε με την παρεχόμενη κεφαλίδα. Ναι, μπορούμε να το κάνουμε αυτό. Αυτός ο τύπος ονομάζεται INDEX MATCH MATCH τύπος ή πείτε έναν τύπο αναζήτησης 2d.
Πώς να λύσετε το πρόβλημα;
Για να κατανοήσει πρώτα ο τύπος, πρέπει να αναθεωρήσουμε λίγο τις ακόλουθες συναρτήσεις
- Λειτουργία INDEX
- Συνάρτηση MATCH
Η συνάρτηση INDEX επιστρέφει την τιμή σε ένα δεδομένο ευρετήριο σε έναν πίνακα.
Η συνάρτηση MATCH επιστρέφει τον δείκτη της πρώτης εμφάνισης της τιμής σε έναν πίνακα (πίνακας μονής διάστασης).
Τώρα θα φτιάξουμε έναν τύπο χρησιμοποιώντας τις παραπάνω συναρτήσεις. Η συνάρτηση αντιστοίχισης θα επιστρέψει το ευρετήριο της τιμής αναζήτησης1 στο πεδίο κεφαλίδας γραμμής. Και μια άλλη συνάρτηση MATCH θα επιστρέψει το ευρετήριο της τιμής αναζήτησης2 στο πεδίο κεφαλίδας στήλης. Οι αριθμοί ευρετηρίου θα εισαχθούν τώρα στη συνάρτηση INDEX για να λάβετε τις τιμές κάτω από την τιμή αναζήτησης από τα δεδομένα του πίνακα 2D.
Γενικός τύπος:
= 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. Καθώς ο τύπος θα εξάγει την κατά προσέγγιση αντιστοίχιση.
Εδώ, οι τιμές στον τύπο δίνονται ως αναφορές κελιών και κεφαλή γραμμής, δεδομένα και κεφαλή στήλης που δίνονται ως ονομαστικές περιοχές όπως αναφέρεται στο παραπάνω στιγμιότυπο.
Όπως μπορείτε να δείτε στο παραπάνω στιγμιότυπο, έχουμε την Τιμή που λαμβάνεται ανά ύψος (34) & Πλάτος (21) ως 53.10. Αποδεικνύει ότι ο τύπος λειτουργεί καλά και για αμφιβολίες δείτε τις παρακάτω σημειώσεις για περισσότερη κατανόηση.
Σημειώσεις:
- Η συνάρτηση επιστρέφει το σφάλμα #NA εάν το όρισμα πίνακα αναζήτησης στη συνάρτηση MATCH είναι πίνακας 2 D που είναι το πεδίο κεφαλίδας των δεδομένων…
- Η συνάρτηση ταιριάζει με την ακριβή τιμή καθώς το όρισμα τύπου αντιστοίχισης με τη συνάρτηση MATCH είναι 0.
- Οι τιμές αναζήτησης μπορούν να δοθούν ως αναφορά κελιού ή απευθείας χρησιμοποιώντας το σύμβολο εισαγωγής (") στον τύπο ως επιχειρήματα.
Ελπίζω να καταλάβατε πώς να χρησιμοποιήσετε τον πίνακα Lookup in 2 D χρησιμοποιώντας τη λειτουργία INDEX & MATCH στο Excel. Εξερευνήστε περισσότερα άρθρα σε αξία αναζήτησης Excel εδώ. Μη διστάσετε να δηλώσετε τα ερωτήματά σας παρακάτω στο πλαίσιο σχολίων. Σίγουρα θα σας βοηθήσουμε.
Χρησιμοποιήστε INDEX και MATCH για αναζήτηση τιμής : Λειτουργία INDEX & MATCH για αναζήτηση τιμής όπως απαιτείται.
Εύρος SUM με INDEX στο Excel : Χρησιμοποιήστε τη συνάρτηση INDEX για να βρείτε το άθροισμα των τιμών όπως απαιτείται.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUM στο Excel : Βρείτε το SUM των αριθμών χρησιμοποιώντας τη συνάρτηση SUM που εξηγείται με παράδειγμα.
Πώς να χρησιμοποιήσετε τη συνάρτηση INDEX στο Excel : Βρείτε το INDEX του πίνακα χρησιμοποιώντας τη συνάρτηση INDEX που εξηγείται με παράδειγμα.
Πώς να χρησιμοποιήσετε τη συνάρτηση MATCH στο Excel : Βρείτε το MATCH στον πίνακα χρησιμοποιώντας την τιμή INDEX μέσα στη συνάρτηση MATCH που εξηγείται με παράδειγμα.
Πώς να χρησιμοποιήσετε τη λειτουργία LOOKUP στο Excel : Βρείτε την τιμή αναζήτησης στον πίνακα χρησιμοποιώντας τη συνάρτηση LOOKUP που εξηγείται με παράδειγμα.
Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel : Βρείτε την τιμή αναζήτησης στον πίνακα χρησιμοποιώντας τη συνάρτηση VLOOKUP που εξηγείται με παράδειγμα.
Πώς να χρησιμοποιήσετε τη συνάρτηση HLOOKUP στο Excel : Βρείτε την τιμή αναζήτησης στον πίνακα χρησιμοποιώντας τη συνάρτηση HLOOKUP που εξηγείται με παράδειγμα.
Δημοφιλή άρθρα
50 Συντόμευση Excel για να αυξήσετε την παραγωγικότητά σας
Επεξεργασία αναπτυσσόμενης λίστας
Απόλυτη αναφορά στο Excel
Εάν με μορφοποίηση υπό όρους
Αν με μπαλαντέρ
Vlookup κατά ημερομηνία
Ενώστε το όνομα και το επώνυμο στο excel
