Σε αυτό το άρθρο, θα μάθουμε πώς να ψάχνουμε nth match στον πίνακα χρησιμοποιώντας τη λειτουργία INDEX & MATCH.
Σενάριο:
Για παράδειγμα, πρέπει να βρούμε τον αριθμητικά ένατο αγώνα και τα αντίστοιχα αποτελέσματά του από τον πίνακα. Χρειαζόμαστε κάποια γενική φόρμουλα που βοηθά στην εύρεση της nη αντιστοίχισης ως προς την απαιτούμενη τιμή.
Πώς να λύσετε το πρόβλημα;
Για να κατανοήσει πρώτα ο τύπος, πρέπει να αναθεωρήσουμε λίγο τις ακόλουθες συναρτήσεις
- Λειτουργία INDEX
- Συνάρτηση MATCH
Τώρα θα φτιάξουμε έναν τύπο χρησιμοποιώντας τις παραπάνω συναρτήσεις. Η συνάρτηση MATCH θα επιστρέψει το ευρετήριο της χαμηλότερης αντιστοίχισης από το εύρος. Η συνάρτηση INDEX λαμβάνει το ευρετήριο σειρών ως όρισμα και επιστρέφει τα αντίστοιχα απαιτούμενα αποτελέσματα. Αυτή η συνάρτηση βρίσκει το
Γενικός τύπος:
= INDEX (δεδομένα, MATCH (ΜΙΚΡΟ (εύρος, n), εύρος, τύπος αντιστοίχισης), col_num)
δεδομένα : πίνακας τιμών στον πίνακα χωρίς κεφαλίδες
εύρος : lookup_array για τη χαμηλότερη αντιστοίχιση
ν : αριθμός, nη αντιστοιχία
match_type : 1 (ακριβές ή επόμενο μικρότερο) ή 0 (ακριβής αντιστοίχιση) ή -1 (ακριβές ή επόμενο μεγαλύτερο)
col_num: αριθμός στήλης, απαιτούμενη τιμή για ανάκτηση από τη στήλη του πίνακα.
Παράδειγμα:
Οι παραπάνω δηλώσεις μπορεί να προκαλέσουν σύγχυση στην κατανόηση. Ας το καταλάβουμε λοιπόν χρησιμοποιώντας τον τύπο σε ένα παράδειγμα
Εδώ έχουμε έναν πίνακα με λεπτομέρειες για την παγκόσμια ήπειρο. Πρέπει να βρούμε τον κωδικό χώρας από τον δεδομένο πίνακα από την τιμή αναζήτησης ως όνομα χώρας.
Ονομαστικό εύρος που χρησιμοποιείται για πίνακα πίνακα (E5: I10) & εύρος (I5: I10).
Εδώ χρησιμοποιήσαμε το ονομαζόμενο εύρος αντί για πίνακα αναφοράς κελιών, όπως είναι εύκολο να κατανοηθεί. Πρέπει να αναζητήσουμε όλες τις λεπτομέρειες όπου η τιμή τιμής είναι η ελάχιστη ή η χαμηλότερη στο εύρος.
Χρησιμοποιήστε τον παραπάνω τύπο για να λάβετε την πρώτη λεπτομέρεια που είναι κεφαλαίο
Χρησιμοποιήστε τον τύπο στο κελί G6:
= INDEX (πίνακας, ΑΓΩΝΑΣ ( ΜΙΚΡΟ (εύρος, 1) ,εύρος, 0 ) , 4 )
Ονομασμένες περιοχές
πίνακας (E5: I10)
εύρος (I5: I10)
Εξήγηση:
- Η συνάρτηση SMALL βρίσκει την πρώτη χαμηλότερη αντιστοίχιση στο εύρος και επιστρέφει την τιμή στη συνάρτηση MATCH.
- Η συνάρτηση MATCH αντιστοιχεί στην ακριβή αντιστοίχιση ελάχιστης τιμής στο εύρος τιμών και επιστρέφει το ευρετήριο σειρών της στη συνάρτηση INDEX.
- Η συνάρτηση INDEX βρίσκει την τιμή κεφαλαίου με δείκτη ROW και 4η στήλη στον πίνακα που ονομάζεται εύρος.
Ο τύπος επιστρέφει όλα τα αποτελέσματα για την πρώτη χαμηλότερη τιμή. Τώρα αντιγράψτε τον τύπο για να λάβετε το δεύτερο μικρότερο ή το τρίτο χαμηλότερο απλώς πρέπει να αλλάξετε το όρισμα n κάτω από τη συνάρτηση ΜΙΚΡΟΙ για να λάβετε τα διαφορετικά αποτελέσματα.
Τώρα απλά αλλάξτε την τιμή n και λάβετε τα αποτελέσματα όπως φαίνεται παρακάτω.
Όπως μπορείτε να δείτε από το παραπάνω στιγμιότυπο, αποκτήσαμε όλες τις λεπτομέρειες που αντιστοιχούν στην τιμή n στον πίνακα. Εξαγάγετε λεπτομέρειες από τον πίνακα χρησιμοποιώντας τον παραπάνω αναφερόμενο τύπο.
Παράδειγμα:
Μπορείτε επίσης να χρησιμοποιήσετε τη συνάρτηση LARGE για να λάβετε την nη αντιστοιχία από την κορυφή ή την ένατη υψηλότερη αντιστοίχιση από το εύρος και επιστρέφει τα αντίστοιχα αποτελέσματά της.
Γενικός τύπος:
= INDEX (δεδομένα, MATCH (LARGE (range, n), range, match_type), col_num)
δεδομένα : πίνακας τιμών στον πίνακα χωρίς κεφαλίδες
εύρος : lookup_array για την υψηλότερη αντιστοίχιση
ν : αριθμός, nη αντιστοιχία
match_type : 1 (ακριβές ή επόμενο μικρότερο) ή 0 (ακριβής αντιστοίχιση) ή -1 (ακριβές ή επόμενο μεγαλύτερο)
col_num: αριθμός στήλης, απαιτούμενη τιμή για ανάκτηση από τη στήλη του πίνακα.
Παράδειγμα:
Οι παραπάνω δηλώσεις μπορεί να προκαλέσουν σύγχυση στην κατανόηση. Ας το καταλάβουμε λοιπόν χρησιμοποιώντας τον τύπο σε ένα παράδειγμα
Εδώ έχουμε έναν πίνακα με λεπτομέρειες για την παγκόσμια ήπειρο. Πρέπει να βρούμε τον κωδικό χώρας από τον δεδομένο πίνακα από την τιμή αναζήτησης ως όνομα χώρας.
Ονομαστικό εύρος που χρησιμοποιείται για πίνακα πίνακα (E5: I10) & εύρος (I5: I10).
Εδώ χρησιμοποιήσαμε το ονομαζόμενο εύρος αντί για πίνακα αναφοράς κελιών, όπως είναι εύκολο να κατανοηθεί. Πρέπει να αναζητήσουμε όλες τις λεπτομέρειες όπου η τιμή τιμής είναι η μέγιστη ή η υψηλότερη στο εύρος.
Χρησιμοποιήστε τον παραπάνω τύπο για να λάβετε την πρώτη λεπτομέρεια που είναι κεφαλαίο
Χρησιμοποιήστε τον τύπο στο κελί G6:
= INDEX (πίνακας, ΑΓΩΝΑΣ ( ΜΕΓΑΛΟ (εύρος, 1) ,εύρος, 0 ) , 4 )
Ονομασμένες περιοχές
πίνακας (E5: I10)
εύρος (I5: I10)
Εξήγηση:
- Η συνάρτηση LARGE βρίσκει την πρώτη υψηλότερη αντιστοίχιση στο εύρος και επιστρέφει την τιμή στη συνάρτηση MATCH.
- Η συνάρτηση MATCH αντιστοιχεί στην ακριβή αντιστοίχιση τιμής αντιστοίχισης n στην περιοχή Τιμών και επιστρέφει το ευρετήριο σειρών της στη συνάρτηση INDEX.
- Η συνάρτηση INDEX βρίσκει την τιμή κεφαλαίου με δείκτη ROW και 4η στήλη στον πίνακα που ονομάζεται εύρος.
Ο τύπος επιστρέφει όλα τα αποτελέσματα για την πρώτη χαμηλότερη τιμή. Τώρα αντιγράψτε τον τύπο για να λάβετε το δεύτερο μικρότερο ή το τρίτο χαμηλότερο απλώς πρέπει να αλλάξετε το όρισμα n κάτω από τη συνάρτηση ΜΙΚΡΟΙ για να λάβετε τα διαφορετικά αποτελέσματα.
Τώρα απλά αλλάξτε την τιμή n και λάβετε τα αποτελέσματα όπως φαίνεται παρακάτω.
Όπως μπορείτε να δείτε από το παραπάνω στιγμιότυπο, αποκτήσαμε όλες τις λεπτομέρειες που αντιστοιχούν στην τιμή n στον πίνακα. Εξαγάγετε λεπτομέρειες από τον πίνακα χρησιμοποιώντας τον παραπάνω αναφερόμενο τύπο.
Ακολουθούν ορισμένοι σημειωτές παρατήρησης κατά τη χρήση του παραπάνω τύπου.
Σημειώσεις:
- Η τιμή του n δεν μπορεί να είναι μικρότερη από 1 ή μεγαλύτερη από το μήκος της περιοχής
- Η συνάρτηση επιστρέφει το σφάλμα #NA εάν το όρισμα πίνακα αναζήτησης στη συνάρτηση MATCH δεν έχει το ίδιο μήκος του πίνακα πίνακα.
- Ο τύπος επιστρέφει ένα σφάλμα εάν η τιμή_αναζήτησης δεν αντιστοιχεί στην τιμή του πίνακα lookup_array.
- Η συνάρτηση αντιστοιχεί στην ακριβή τιμή καθώς το όρισμα τύπου αντιστοίχισης με τη συνάρτηση MATCH είναι 0.
- Οι τιμές αναζήτησης μπορούν να δοθούν ως αναφορά κελιού ή απευθείας χρησιμοποιώντας το σύμβολο εισαγωγής (") στον τύπο ως επιχειρήματα.
Ελπίζω να καταλάβατε πώς να αναζητήσετε τον αγώνα στον πίνακα χρησιμοποιώντας τη λειτουργία INDEX & MATCH. Εξερευνήστε περισσότερα άρθρα σχετικά με την αξία αναζήτησης Excel εδώ. Μη διστάσετε να δηλώσετε τα ερωτήματά σας παρακάτω στο πλαίσιο σχολίων. Σίγουρα θα σας βοηθήσουμε.
Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email
Χρησιμοποιήστε INDEX και MATCH για αναζήτηση τιμής : Λειτουργία INDEX & MATCH για αναζήτηση τιμής όπως απαιτείται.
Εύρος SUM με INDEX στο Excel : Χρησιμοποιήστε τη συνάρτηση INDEX για να βρείτε το άθροισμα των τιμών όπως απαιτείται.
Πώς να χρησιμοποιήσετε τη συνάρτηση INDEX στο Excel : Βρείτε το INDEX του πίνακα χρησιμοποιώντας τη συνάρτηση INDEX που εξηγείται με παράδειγμα.
Πώς να χρησιμοποιήσετε τη συνάρτηση MATCH στο Excel : Βρείτε το MATCH στον πίνακα χρησιμοποιώντας την τιμή INDEX μέσα στη συνάρτηση MATCH που εξηγείται με παράδειγμα.
Πώς να χρησιμοποιήσετε τη λειτουργία LOOKUP στο Excel : Βρείτε την τιμή αναζήτησης στον πίνακα χρησιμοποιώντας τη συνάρτηση LOOKUP που εξηγείται με παράδειγμα.
Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel : Βρείτε την τιμή αναζήτησης στον πίνακα χρησιμοποιώντας τη συνάρτηση VLOOKUP που εξηγείται με παράδειγμα.
Δημοφιλή άρθρα
50 Συντόμευση Excel για να αυξήσετε την παραγωγικότητά σας
Επεξεργασία αναπτυσσόμενης λίστας
Απόλυτη αναφορά στο Excel
Εάν με μορφοποίηση υπό όρους
Αν με μπαλαντέρ
Vlookup κατά ημερομηνία
Ενώστε το όνομα και το επώνυμο στο excel