Ο τύπος Vlookup λειτουργεί μόνο όταν ο πίνακας πίνακα στον τύπο δεν αλλάζει, αλλά εάν υπάρχει μια νέα στήλη που έχει εισαχθεί στον πίνακα ή μια στήλη διαγραφεί, ο τύπος δίνει εσφαλμένο αποτέλεσμα ή αντανακλά ένα σφάλμα, για να κάνει τον τύπο χωρίς σφάλματα Τέτοιες δυναμικές καταστάσεις χρησιμοποιούμε τη λειτουργία αντιστοίχισης για να ταιριάσουμε πραγματικά με το ευρετήριο των δεδομένων και να επιστρέψουμε το πραγματικό αποτέλεσμα.
Συνδυάστε το VLOOKUP με το Match
Ο τύπος vlookup είναι η πιο συχνά χρησιμοποιούμενη συνάρτηση που χρησιμοποιείται για την αναζήτηση και την επιστροφή είτε της ίδιας τιμής στο καθορισμένο ευρετήριο στηλών είτε της τιμής από διαφορετικό ευρετήριο στηλών με αναφορά στην αντίστοιχη τιμή από την πρώτη στήλη. Η κύρια πρόκληση που αντιμετωπίζετε κατά τη χρήση του vlookup είναι ότι ο δείκτης στηλών που θα καθοριστεί είναι στατικός και δεν έχει δυναμική λειτουργικότητα. Ειδικά όταν εργάζεστε σε πολλά κριτήρια που απαιτούν να αλλάξετε χειροκίνητα το ευρετήριο στηλών αναφοράς. Με αυτόν τον τρόπο, αυτή η ανάγκη εκπληρώνεται χρησιμοποιώντας τον τύπο "MATCH" για να έχετε καλύτερη πρόσφυση ή έλεγχο στο ευρέως μεταβαλλόμενο ευρετήριο στηλών στον τύπο VLOOKUP.
VLookup και Match Formula
# 1 - Φόρμουλα VLOOKUP
Ο τύπος της συνάρτησης VLOOKUP στο Excel
Εδώ όλα τα επιχειρήματα που πρέπει να εισαχθούν είναι υποχρεωτικά.
- Lookup_value - Εδώ, θα πρέπει να εισαχθεί κελί αναφοράς ή κείμενο με διπλά εισαγωγικά για να προσδιοριστεί στο εύρος στηλών.
- Πίνακας πίνακα - Αυτό το όρισμα απαιτεί την καταχώριση του εύρους πίνακα όπου θα πρέπει να αναζητηθεί η τιμή_αναζήτησης και τα δεδομένα που θα ανακτηθούν βρίσκονται στο συγκεκριμένο εύρος στηλών.
- Col_index_num - Σε αυτό το όρισμα, πρέπει να εισαχθεί ο αριθμός ευρετηρίου στηλών ή ο αριθμός της στήλης από την πρώτη στήλη αναφοράς από την οποία πρέπει να τραβηχτεί η αντίστοιχη τιμή από την ίδια θέση με την τιμή που αναζητήθηκε στην πρώτη στήλη.
- (Range_lookup) - Αυτό το επιχείρημα θα δώσει δύο επιλογές.
- TRUE - Κατά προσέγγιση αντιστοίχιση: - Το όρισμα μπορεί είτε να εισαχθεί ως TRUE είτε ως αριθμητικό "1", το οποίο επιστρέφει την κατά προσέγγιση αντιστοίχιση που αντιστοιχεί στη στήλη αναφοράς ή στην πρώτη στήλη. Επιπλέον, οι τιμές στην πρώτη στήλη του πίνακα πίνακα πρέπει να ταξινομηθούν με αύξουσα σειρά.
- FALSE - Ακριβής αντιστοίχιση: - Εδώ, το όρισμα που πρέπει να εισαχθεί μπορεί να είναι FALSE ή αριθμητικό "0". Αυτή η επιλογή θα επιστρέψει την ακριβή αντιστοίχιση της τιμής που αντιστοιχεί στην αναγνώριση από τη θέση στο εύρος της πρώτης στήλης. Η αποτυχία αναζήτησης της τιμής από την πρώτη στήλη θα επιστρέψει ένα μήνυμα σφάλματος "# N / A".
# 2 - Φόρμουλα αγώνα
Η συνάρτηση αντιστοίχισης επιστρέφει τη θέση κελιού της τιμής που έχει εισαχθεί για τον δεδομένο πίνακα πίνακα.
Όλα τα επιχειρήματα εντός της σύνταξης είναι υποχρεωτικά.
- Lookup_value - Εδώ, το όρισμα που εισάγεται μπορεί να είναι είτε η αναφορά κελιού της τιμής είτε μια συμβολοσειρά κειμένου με διπλά εισαγωγικά των οποίων η θέση κελιού απαιτείται να τραβηχτεί.
- Lookup_array - Το εύρος συστοιχιών για τον πίνακα πρέπει να εισαχθεί του οποίου η τιμή ή το περιεχόμενο κελιού είναι επιθυμητό να προσδιοριστεί.
- (τύπος αντιστοίχισης) - Αυτό το επιχείρημα παρέχει τρεις επιλογές, όπως εξηγείται παρακάτω.
- "1-Λιγότερο από" - Εδώ, το όρισμα που πρέπει να εισαχθεί είναι το αριθμητικό "1", το οποίο θα επιστρέψει την τιμή που είναι μικρότερη ή ίση με την τιμή αναζήτησης. Και επίσης, ο πίνακας αναζήτησης πρέπει να ταξινομηθεί με αύξουσα σειρά.
- "0-Exact match" - Εδώ, το όρισμα που πρέπει να εισαχθεί πρέπει να είναι αριθμητικό "0". Αυτή η επιλογή θα επιστρέψει την ακριβή θέση της αντιστοιχισμένης τιμής αναζήτησης. Ωστόσο, ο πίνακας αναζήτησης μπορεί να είναι με οποιαδήποτε σειρά.
- "-1-Μεγαλύτερο από" - Το όρισμα που πρέπει να εισαχθεί πρέπει να είναι αριθμητικό "-1". Η τρίτη επιλογή βρίσκει τη μικρότερη τιμή που είναι μεγαλύτερη ή ίση με την τιμή αναζήτησης. Εδώ η σειρά για τον πίνακα αναζήτησης πρέπει να τοποθετηθεί σε φθίνουσα σειρά.
# 3 - VLOOKUP με τύπο MATCH
= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, (match_type)), (εύρος αναζήτησης))
Πώς να χρησιμοποιήσετε το VLOOKUP με τον τύπο αντιστοίχισης στο Excel;
Το παρακάτω παράδειγμα θα βοηθήσει στην κατανόηση της λειτουργίας του τύπου προοπτικής και αντιστοίχισης κατά τη συγκέντρωση.
Εξετάστε τον παρακάτω πίνακα δεδομένων, ο οποίος περιγράφει τις προδιαγραφές του συγκεκριμένου οχήματος που πρόκειται να αγοραστεί.
Για να λάβουμε τη σαφήνεια της συνδυασμένης συνάρτησης για τη λειτουργία vlookup και match, ας καταλάβουμε πώς λειτουργεί η μεμονωμένη φόρμουλα και, στη συνέχεια, φτάνουμε στα αποτελέσματα αγώνα vlookup όταν συγκεντρώνονται.
Βήμα 1 - Ας εφαρμόσουμε τον τύπο vlookup σε ατομικό επίπεδο για να φτάσουμε στο αποτέλεσμα.
Η έξοδος φαίνεται παρακάτω:
Εδώ η τιμή αναζήτησης αναφέρεται σε $ B9 που είναι το μοντέλο "E" και ο πίνακας αναζήτησης δίνεται ως το εύρος του πίνακα δεδομένων με απόλυτη τιμή "$", ο δείκτης στήλης αναφέρεται στη στήλη "4" που είναι ο αριθμός στη στήλη "Τύπος" και η αναζήτηση εύρους έχει μια ακριβή αντιστοίχιση.
Έτσι, εφαρμόζεται ο ακόλουθος τύπος για την επιστροφή της τιμής για τη στήλη "Καύσιμο".
Η έξοδος φαίνεται παρακάτω:
Εδώ η τιμή αναζήτησης με απόλυτη συμβολοσειρά "$" εφαρμόζεται για την τιμή αναζήτησης και το lookup_array βοηθά στη διόρθωση του κελιού αναφοράς, ακόμη και αν ο τύπος αντιγράφεται σε διαφορετικό κελί. Στη στήλη "Καύσιμο", πρέπει να αλλάξουμε το ευρετήριο στηλών σε "5" καθώς η τιμή από την οποία απαιτείται η ανάκτηση των δεδομένων αλλάζει.
Βήμα 2 - Τώρα, ας εφαρμόσουμε τον τύπο αντιστοίχισης για να ανακτήσουμε τη θέση για τη δεδομένη τιμή αναζήτησης.
Η έξοδος φαίνεται παρακάτω:
Όπως φαίνεται στο παραπάνω στιγμιότυπο οθόνης, εδώ προσπαθούμε να ανακτήσουμε τη θέση της στήλης από τον πίνακα πίνακα. Σε αυτήν την περίπτωση, ο αριθμός στήλης που θα τραβηχτεί αναφέρεται ως κελί C8 που είναι στήλη "Τύπος" και το εύρος αναζήτησης που θα αναζητηθεί δίνεται ως το εύρος των κεφαλίδων στηλών και στον τύπο αντιστοίχισης δίνεται μια ακριβής αντιστοίχιση ως "0".
Έτσι, ο παρακάτω πίνακας θα δώσει το επιθυμητό αποτέλεσμα για τις θέσεις της στήλης «Καύσιμο».
Τώρα εδώ, η στήλη προς αναζήτηση δίνεται να είναι το κελί D8 και ο επιθυμητός δείκτης στήλης επιστρέφεται σε "5".
Βήμα # 3 - Τώρα, ο τύπος Match θα χρησιμοποιηθεί στη συνάρτηση vlookup για να πάρει την τιμή από την προσδιορισμένη θέση στήλης.
Η έξοδος φαίνεται παρακάτω:
Στον παραπάνω τύπο, η συνάρτηση αντιστοίχισης τίθεται στη θέση της παραμέτρου ευρετηρίου στηλών της συνάρτησης vlookup. Εδώ η συνάρτηση αντιστοίχισης θα προσδιορίσει το κελί αναφοράς τιμής αναζήτησης "C8" και θα επιστρέψει τον αριθμό στήλης μέσω του δεδομένου πίνακα πίνακα. Αυτή η θέση στήλης θα εξυπηρετήσει τον σκοπό ως είσοδο στο όρισμα ευρετηρίου στηλών στη συνάρτηση vlookup. Ποιο, με τη σειρά του, θα βοηθήσει το vlookup να εντοπίσει την τιμή που θα επιστραφεί από τον αριθμό ευρετηρίου στήλης που προκύπτει;
Ομοίως, εφαρμόσαμε το vlookup με τον τύπο αντιστοίχισης για τη στήλη "Fuel".
Η έξοδος φαίνεται παρακάτω:
Μπορούμε έτσι να εφαρμόσουμε αυτήν τη συνδυαστική λειτουργία και για άλλες στήλες «Τύπος» και «Καύσιμο» επίσης.
Πράγματα που πρέπει να θυμάστε
- Το VLOOKUP μπορεί να εφαρμοστεί σε τιμές αναζήτησης μόνο στην κύρια αριστερή πλευρά του. Τυχόν τιμές που υπάρχουν για αναζήτηση στη δεξιά πλευρά του πίνακα δεδομένων θα επιστρέψουν την τιμή σφάλματος "# N / A".
- Το εύρος του table_array που εισάγεται στο δεύτερο όρισμα θα πρέπει να είναι απόλυτη αναφορά κελιού "$", αυτό θα διατηρήσει το σταθερό εύρος πίνακα πίνακα κατά την εφαρμογή του τύπου αναζήτησης σε άλλα κελιά, αλλιώς τα κελιά αναφοράς για το εύρος πίνακα πίνακα θα μετακινηθούν στο επόμενο κελί αναφορά.
- Η τιμή που έχει εισαχθεί στην τιμή αναζήτησης δεν πρέπει να είναι μικρότερη από τη μικρότερη τιμή στην πρώτη στήλη του πίνακα πίνακα, διαφορετικά η συνάρτηση θα επιστρέψει την τιμή σφάλματος "# N / A".
- Πριν εφαρμόσετε μια κατά προσέγγιση αντιστοίχιση "TRUE" ή "1" στο τελευταίο όρισμα, θυμηθείτε πάντα να ταξινομήσετε τον πίνακα πίνακα σε αύξουσα σειρά.
- Η συνάρτηση αντιστοίχισης επιστρέφει μόνο τη θέση της τιμής στον πίνακα πίνακα vlookup και δεν επιστρέφει την τιμή.
- Σε περίπτωση που η λειτουργία Match δεν είναι σε θέση να προσδιορίσει τη θέση της τιμής αναζήτησης στον πίνακα πίνακα, τότε ο τύπος επιστρέφει "# N / A" στην τιμή σφάλματος.
- Οι συναρτήσεις Vlookup και Match δεν είναι ευαίσθητες στην περίπτωση όταν ταιριάζουν με την τιμή αναζήτησης με την αντίστοιχη τιμή κειμένου στον πίνακα πίνακα.
