Εισαγωγή στη λειτουργία VLOOKUP
Η συνάρτηση VLOOKUP στο Excel είναι χωρίς αμφιβολία, είναι η πιο χρησιμοποιούμενη και συνομιλούμενη συνάρτηση. Σε οποιαδήποτε συνέντευξη εργασίας που απαιτεί δεξιότητες Excel, αυτή είναι η πιο συνηθισμένη ερώτηση που εάν γνωρίζετε πώς να χρησιμοποιήσετε τη λειτουργία VLOOKUP.
Η συνάρτηση VLOOKUP ανήκει στην κατηγορία αναζήτησης των λειτουργιών excel. Το V στο VLOOKUP σημαίνει κάθετο. Η συνάρτηση χρησιμοποιείται για την αναζήτηση δεδομένων που είναι δομημένα κάθετα. Για την οριζόντια αναζήτηση, υπάρχει μια συνάρτηση που ονομάζεται HLOOKUP.
Η συνάρτηση Excel VLOOKUP αναζητά την πρώτη αντιστοίχιση μιας δεδομένης τιμής που ονομάζεται τιμή αναζήτησης, σε έναν πίνακα δεδομένων και επιστρέφει την τιμή από το δεδομένο ευρετήριο στηλών. Η αντιστοίχιση μπορεί να είναι κατά προσέγγιση ή ακριβής.
Παρόλο που το VLOOKUP είναι εύκολο στη χρήση, έχει τους δικούς του περιορισμούς και σκηνικά. Θα συζητήσουμε τα πάντα, συμπεριλαμβανομένων των πλεονεκτημάτων και των αδυναμιών της λειτουργίας.
Σύνταξη:
= VLOOKUP (τιμή_αναζήτησης, πίνακας_σειράς, col_index_number, [range_lookup])
Τι είναι η αξία αναζήτησης
Lookup_value: Είναι η τιμή που θέλετε να αναζητήσετε σε έναν πίνακα πίνακα.
Για παράδειγμα, αν ψάχνετε για τον αριθμό ρολού. 110 στο τραπέζι των μαθητών τότε είναι 110 είναι Τιμή αναζήτησης.
Table_array: Ο πίνακας από τον οποίο θέλετε να αναζητήσετε την τιμή αναζήτησης.
Για παράδειγμα, αν ψάχνετε 110 στον πίνακα πίνακα Β5: Ε17. Τότε B5: E17 είναι ο πίνακας πίνακα σας.
col_index_number: Ο αριθμός στήλης στον πίνακα πίνακα από τον οποίο θέλετε να λάβετε αποτελέσματα.
Για παράδειγμα, σε πίνακα πίνακα Β5: Ε17 θέλετε να λάβετε αξία από τη στήλη Δ τότε ο δείκτης στήλης σας θα είναι 3 (μετρώντας από το Β στο Δ (Β, Γ, Δ)).
[range_lookup]: ΛΑΘΟΣ αν θέλετε να αναζητήσετε ακριβή τιμή, ΑΛΗΘΙΝΗ εάν θέλετε μια αντιστοιχία κατά προσέγγιση.
Πώς να χρησιμοποιήσετε τη λειτουργία VLOOKUP
Όταν έχετε δομημένα δεδομένα και θέλετε να αναζητήσετε δεδομένα σε αυτά τα δεδομένα, η λειτουργία VLOOKUP είναι η λύση.
Λειτουργίες αντιστοίχισης VLOOKUP
Το VLOOKUP διαθέτει δύο τρόπους αντιστοίχισης, Κατά προσέγγιση αντιστοίχιση και ακριβή αντιστοίχιση. Από προεπιλογή, το VLOOKUP εκτελεί κατά προσέγγιση αντιστοίχιση. Αλλά αν θέλετε να αναγκάσετε τη λειτουργία VLOOKUP να κάνει μια ακριβή αντιστοίχιση, μπορείτε να το κάνετε επίσης. Τις περισσότερες φορές, εγώ και εγώ πιστεύω ακράδαντα ότι και άλλοι, προσπαθούμε να κάνουμε ένα ακριβές ταίριασμα χρησιμοποιώντας τη λειτουργία VLOOKUP. Δεν καταλαβαίνω γιατί οι προγραμματιστές excel πιστεύουν ότι οι χρήστες θα ήθελαν να χρησιμοποιούν ως επί το πλείστον την κατά προσέγγιση αντιστοίχιση.
1. VLOOKUP Approximate Match
= VLOOKUP (τιμή_αναζήτησης, πίνακας_σειράς, col_index_number, 1)
Σε κατά προσέγγιση αντιστοίχιση VLOOKUP, αν δεν βρεθεί μια τιμή τότε το αντιστοιχεί η τελευταία τιμή που είναι μικρότερη από την τιμή αναζήτησης και επιστρέφεται η τιμή από το δεδομένο δείκτη στήλης.
Το VLOOKUP από προεπιλογή ταιριάζει κατά προσέγγιση, αν παραλείψουμε τη μεταβλητή αναζήτησης εύρους. Μια κατά προσέγγιση αντιστοίχιση είναι χρήσιμη όταν θέλετε να κάνετε έναν κατά προσέγγιση αγώνα και όταν έχετε ταξινομήσει τον πίνακα πίνακα με αύξουσα σειρά.
Το Vlookup αναζητά την τιμή και αν δεν βρει την τιμή στον πίνακα πίνακα, τότε ταιριάζει με την τιμή που είναι μικρότερη από αυτήν την τιμή στον πίνακα πίνακα. Ας το καταλάβουμε με ένα παράδειγμα.
Παράδειγμα 1
Έχουμε μια λίστα μαθητών εδώ. Κάθε μαθητής έχει βαθμολογήσει κάποια βαθμολογία σε ένα τεστ. Τώρα θέλω να δώσω βαθμολογία ανάλογα με τις βαθμολογίες τους.
Κάθε μαθητής που σημείωσε λιγότερα από 40 θα πρέπει να φέρει τη σήμανση F, ο μαθητής που σημείωσε μεταξύ 40 και 60 θα πρέπει να σημειώσει το C και ούτω καθεξής όπως φαίνεται στην παρακάτω εικόνα.
Θα γράφαμε αυτόν τον τύπο VLOOKUP σε Ε2 και θα τον σύραμε προς τα κάτω.
= VLOOKUP (D2, $ H $ 2: $ I $ 6,2, TRUE)
Πως δουλεύει?
Στο παραπάνω παράδειγμα, οι τιμές αναζήτησης βρίσκονται στη στήλη D, ξεκινώντας από το D2. Ο πίνακας πίνακα είναι H2: I6 (σημειώστε ότι έχει ταξινομηθεί με αύξουσα σειρά και απόλυτη). Η στήλη από την οποία λαμβάνουμε δεδομένα είναι 2. Και ο τύπος αντιστοίχισης που έχουμε ορίσει κατά προσέγγιση περνώντας TRUE (τίποτα και 1 δεν σημαίνει το ίδιο).
Ας εξετάσουμε λοιπόν την τιμή αναζήτησης frist D2 που περιέχει 40.
- Το VLOOKUP αναζητά 40 στην πρώτη στήλη (Η) του πίνακα πίνακα H2: I6.
- Βρίσκει 40 στη δεύτερη θέση στο κελί Η3.
- Τώρα μετακινείται στη δεύτερη στήλη από το Η3 στο Ι3 και επιστρέφει το Δ.
Σε αυτήν την περίπτωση το vlookup βρήκε την ακριβή αντιστοίχιση. Ας δούμε την επόμενη περίπτωση.
Η δεύτερη τιμή αναζήτησης D3 που περιέχει 36.
- Το VLOOKUP αναζητά 36 στην πρώτη στήλη πίνακα πίνακα H2: I6.
- Το VLOOKUP δεν βρήκε 36 πουθενά στην πρώτη στήλη.
- Δεδομένου ότι το VLOOKUP δεν μπόρεσε να βρει 36, το ταιριάζει με την τελευταία τιμή που είναι μικρότερη από την τιμή αναζήτησης.
- Η πρώτη τιμή που είναι μικρότερη από 36 είναι 0, επομένως επιστρέφει το F που σχετίζεται με το 0.
Αυτό συμβαίνει για κάθε περίπτωση εδώ. Για 92, η τελευταία τιμή που είναι μικρότερη από 92 είναι 90. Επομένως, το A επιστρέφεται για το Approximate match.
2. VLOOKUP Ακριβής αντιστοιχία
Αυτή η πιο χρησιμοποιημένη μορφή VLOOKUP και μπορεί να είναι και πολύ χρήσιμη. Όταν θέλετε να αναζητήσετε ακριβή τιμή στην πρώτη στήλη πίνακα πίνακα, χρησιμοποιείτε ακριβή αντιστοίχιση. Για παράδειγμα, εάν πραγματοποιείτε μια αναζήτηση από ένα αναγνωριστικό από αυτό που θα θέλατε να θέλετε μια ακριβή αντιστοίχιση αντί για οποιαδήποτε τιμή που είναι μικρότερη από αυτήν την τιμή.
Για να αναγκάσουμε το VLOOKUP να ταιριάξει με το Excel, περνάμε την παράμετρο 0 ή FALSE ως range_lookup.
= VLOOKUP (τιμή_αναζήτησης, πίνακας_σειράς, col_index_number, 0)
Εάν η τιμή δεν βρίσκεται στην πρώτη στήλη του πίνακα πίνακα, ο τύπος θα επιστρέψει σφάλμα #N/A.
Ας δούμε ένα παράδειγμα.
Παράδειγμα 2
Σε αυτό το παράδειγμα, θέλω να γράψω μόνο τον αριθμό μαθητή στο Α2 και στο Β2 θέλω να φέρω το το όνομα του μαθητή και στο Γ2 του ΒαθμόςΤο Απλός. Ο πίνακας πίνακα είναι Β5: Δ17Το Γιατί;
Για να το κάνετε αυτό, γράψτε αυτούς τους δύο τύπους στο κελί στο κελί B2 και C2 αντίστοιχα.
= VLOOKUP (A2, $ B $ 5: $ E $ 17,2,0)
= VLOOKUP (A2, $ B $ 5: $ E $ 17,3,0)
Τώρα όποιο αναγνωριστικό θα γράψετε στο κελί Α2, η συνάρτηση VLOOKUP θα αναζητήσει ακριβή αντιστοίχιση στη στήλη Β και θα επιστρέψει τιμή από το δεδομένο col_index.
Πώς λειτουργεί το VLOOKUP ακριβούς αντιστοίχισης;
Το Exact Match VLOOKUP στο excel είναι απλό. Αναζητά την τιμή αναζήτησης στην πρώτη στήλη του δεδομένου πίνακα_συστοιχίας και επιστρέφει την τιμή από την τιμή από τον δεδομένο αριθμό στήλης_ευρετηρίου. Εάν η τιμή δεν βρεθεί, το VLOOKUP επιστρέφει σφάλμα #N/A.
Καλύτερες χρήσεις της λειτουργίας VLOOKUP
Το Vlookup έχει πολλές χρήσεις. Θα συζητήσω μερικές πιο χρήσιμες περιπτώσεις χρήσης.
3. Χρησιμοποιήστε το Excel VLOOKUP για Βαθμολόγηση αντί για σύνθετη ένθετη συνάρτηση IF
Χρησιμοποιήστε κατά προσέγγιση αντιστοίχιση VLOOKUP για να αντικαταστήσετε την ένθετη λειτουργία IF. Στο Παράδειγμα 1 είδαμε πώς μπορούμε να χρησιμοποιήσουμε το vlookup για να βαθμολογήσουμε τους μαθητές ανάλογα με τις βαθμολογίες τους.
Η κατά προσέγγιση αντιστοίχιση VLOOKUP είναι ταχύτερη και μετά φωλιάζει, εφόσον χρησιμοποιεί δυαδική αναζήτηση εσωτερικά.
4. Χρησιμοποιήστε το VLOOKUP για ανάκτηση δεδομένων
Αυτή η πιο κοινή εργασία που μπορεί να επιτευχθεί εύκολα χρησιμοποιώντας τη λειτουργία VLOOKUP στο Excel. Στο παράδειγμα 2 το κάναμε για να λάβουμε δεδομένα μαθητών χρησιμοποιώντας τον αριθμό τους.
5. Ελέγξτε εάν μια τιμή βρίσκεται σε μια εγγραφή χρησιμοποιώντας το Vlookup.
Το VLOOKUP μπορεί να χρησιμοποιηθεί για να ελέγξει εάν μια τιμή βρίσκεται σε κάποια λίστα ή όχι.
Στο Παράδειγμα 2 όταν γράφουμε 152, επιστρέφει σφάλμα #N/A. Εάν το VLOOKUP επιστρέψει #N/A, αυτό σημαίνει ότι δεν βρήκε τη δεδομένη τιμή οπουδήποτε στα δεδομένα δεδομένα.
Έχω ετοιμάσει ένα σύντομο άρθρο σχετικά με αυτό Μπορείτε να το διαβάσετε εδώ.
Χρησιμοποιώντας έναν τύπο VLOOKUP για να ελέγξετε εάν υπάρχει μια τιμή
Αυτόματη λήψη ευρετηρίου στήλης
Ανήκω σε αυτήν την ομάδα ανθρώπων, που δεν θέλουν να επαναλαμβάνουν την ίδια εργασία ξανά και ξανά. Οπότε η αλλαγή του αριθμού col_index με ενοχλεί ξανά και ξανά και πάντα το αποφεύγω. Πραγματικά αποφεύγω κάθε είδους επεξεργασία στους τύπους μου μόλις το γράψω, εκτός και μέχρι που δεν υπάρχει άλλη επιλογή.
Το ευρετήριο στήλης μπορεί να υπολογιστεί αυτόματα στο Excel. Υπάρχουν διάφοροι τρόποι να το κάνουμε. Ας δούμε μερικά από αυτά εδώ.
6. Χρησιμοποιήστε τη λειτουργία VLOOKUP με τη λειτουργία MATCH
Έτσι, όπως γνωρίζουμε ότι η συνάρτηση MATCH επιστρέφει το ευρετήριο που βρέθηκε με αντιστοιχισμένη τιμή.
Έτσι, αν έχουμε ακριβώς τις ίδιες επικεφαλίδες στη θέση αναζήτησης με τα δεδομένα προέλευσης, τότε μπορούμε να χρησιμοποιήσουμε για να πάρουμε το col_index. Πως? Ας δούμε…
Στα παρακάτω δεδομένα, θέλουμε απλά να γράψουμε τον αριθμό ρολού στο G2 και θέλουμε να φέρουμε το όνομα του μαθητή, τους βαθμούς και τον βαθμό στο H2, όποια επικεφαλίδα γράφουμε εκεί.
Γράψτε αυτόν τον τύπο στο κελί Η2
= VLOOKUP (G2, B2: E14, MATCH (H1, B1: E1,0), 0)
Τώρα όποτε αλλάζετε την επικεφαλίδα στο Η1, η τιμή στο Η2 θα εμφανίζεται από αυτήν τη στήλη.
Μπορείτε να χρησιμοποιήσετε ένα αναπτυσσόμενο μενού επικύρωσης δεδομένων που έχει όλους τους τίτλους από τον πίνακα, για να το κάνετε πιο φιλικό προς το χρήστη.
Εδώ το VLOOKUP κάνει ακριβώς το ταίρι. Τώρα το VLOOKUP κάνει τα συνηθισμένα πράγματα για την άντληση δεδομένων. Η μαγεία γίνεται με τη συνάρτηση MATCH στη θέση col_index.
Εδώ το VLOOKUP βλέπει οποιαδήποτε τιμή στο G2 στην πρώτη στήλη του πίνακα πίνακα B2: E14. Τώρα στη θέση του col_index έχουμε ΑΓΩΝΑΣ (Η1, Β1: Ε1,0).
Η συνάρτηση MATCH αναζητά οποιαδήποτε τιμή στο Η1 στο εύρος Β1: Ε1 και επιστρέφει το δείκτη της τιμής αντιστοίχισης.
Για παράδειγμα, όταν πληκτρολογούμε μαθητης σχολειου στο Η1 το ψάχνει σε εμβέλεια Β1: Ε1. Βρίσκεται στο C2. άρα επιστρέφει 2. Αν πληκτρολογήσουμε Βαθμός θα επιστρέψει βαθμό μαθητή.
Σημειώστε ότι όταν πληκτρολογούμε Region, επιστρέφει #N/A. Επειδή είναι εκτός πεδίου εφαρμογής του πίνακα. Θα μιλήσουμε για αυτό τελευταία στο άρθρο.
Αυτός είναι ο καλύτερος τρόπος για να κάνετε το VLOOKUP αυτόματο. Υπάρχουν άλλες μέθοδοι, αλλά δεν είναι τόσο ευέλικτες από αυτήν.
7. Χρησιμοποιήστε τη λειτουργία VLOOKUP With COLUMN Function
Η συνάρτηση στήλης επιστρέφει τον αριθμό στήλης της παρεχόμενης αναφοράς. Και τι χρειαζόμαστε για την ανάκτηση δεδομένων χρησιμοποιώντας το VLOOKUP; Col_index. Έτσι, χρησιμοποιώντας τη συνάρτηση COLUMN μπορούμε φυσικά να λάβουμε δεδομένα από οποιοδήποτε σύνολο δεδομένων.
Ας πάρουμε το παραπάνω παράδειγμα. Τώρα όμως πρέπει να ανακτήσουμε ολόκληρα δεδομένα. Όχι μόνο μια στήλη.
Γράψτε αυτόν τον τύπο σε Η2 και αντιγράψτε στο I2 και J2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, ΣΤΗΛΗ (Β1), 0)
Αυτό θα σας δώσει ένα δυναμικό αποτέλεσμα. Πως? Ας δούμε.
Το VLOOKUP λειτουργεί ως συνήθως. Στο column_index έχουμε γράψει ΣΤΗΛΗ (Β1), το οποίο θα μεταφραστεί σε 2, αφού έχουμε όνομα μαθητή σε 2 στήλες από τη στήλη Β, επιστρέφει το όνομα του μαθητή.
Σπουδαίος: Αυτό λειτουργεί επειδή έχουμε τον πίνακα μας ξεκινώντας από τη στήλη Β. Εάν ο πίνακάς σας βρίσκεται στη μέση του φύλλου, τότε θα πρέπει να αφαιρέσετε ή να προσθέσετε μερικούς αριθμούς, ώστε να ταιριάζει με τις απαιτήσεις σας.
Για παράδειγμα, εάν ο παραπάνω πίνακας ξεκινούσε από το C1 στην ίδια δομή, τότε όλοι οι παρακάτω τύποι λειτουργούσαν καλά.
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, ΣΤΗΛΗ (Β1), 0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, ΣΤΗΛΗ (C1) -1,0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, ΣΤΗΛΗ (A1) +1,0)
8. VLOOKUP για συγχώνευση δεδομένων
Στις περισσότερες βάσεις δεδομένων υπάρχει ένα ερώτημα που συγχωνεύεται σε πίνακες, το οποίο συνήθως ονομάζεται ερώτημα συμμετοχής, αλλά το MS Excel σε καμία βάση δεδομένων, επομένως δεν έχει ερώτημα σύνδεσης. Αλλά αυτό δεν σημαίνει ότι δεν μπορούμε να ενώσουμε δύο πίνακες εάν έχουμε μερικές κοινές στήλες σε δύο πίνακες.
Έτσι, όταν λαμβάνετε κάποια δεδομένα από δύο τμήματα της ίδιας εταιρείας, θα θέλατε να τα συγχωνεύσετε για να αναλύσετε αυτά τα δεδομένα.
Για παράδειγμα, όταν παίρνετε βαθμούς μαθητών από δασκάλους και στη συνέχεια καταγράφετε παρακολούθηση από το διοικητικό τμήμα, θα θέλατε να δείτε ποιος μαθητής πήρε τι βαθμό και ποιο είναι το ποσοστό συμμετοχής του, σε μία θέση.
Πρέπει να τα συγχωνεύσουμε στον παρακάτω πίνακα.
Σημάδια, πρέπει να πάρουμε από τον πίνακα σημείων και τη συμμετοχή από τα δεδομένα συμμετοχής.
Γράψτε παρακάτω Τύποι στο κελί D19 για να λάβετε Σημάδια και σύρετε προς τα κάτω.
= VLOOKUP (B19, $ B $ 2: $ D $ 15,3,0)
Γράψτε τον παρακάτω τύπο στο κελί E19
= VLOOKUP (B19, $ G $ 3: $ I $ 15,3,0)
Και έχουμε συγχωνεύσει δύο πίνακες σε έναν πίνακα. Μπορείτε φυσικά να προσθέσετε περισσότερους από δύο πίνακες. Απλώς πρέπει να λάβετε δεδομένα από όλους τους πίνακες που θέλετε σε ένα μέρος χρησιμοποιώντας το VLOOKUP.
Βέλτιστες πρακτικές του VLOOKUP
Έτσι, όλα τα παραπάνω παραδείγματα χρησιμοποιήσαμε το vlookup με ακατέργαστα δεδομένα. Σε όλα τα παραπάνω παραδείγματα, προσέξαμε τα δεδομένα που χρησιμοποιούσαμε. Χρειάστηκε να είμαστε προσεκτικοί, δίνοντας παράλληλα αναφορές πινάκων πίνακα. Υπάρχουν όμως ορισμένοι τρόποι, που μπορούν να μειώσουν σε μεγάλο βαθμό αυτήν την προσπάθεια.
9. Χρησιμοποιήστε τις Απόλυτες Αναφορές με το VLOOKUP
Mayσως έχετε παρατηρήσει ότι σε ορισμένα άρθρα έχω χρησιμοποιήσει απόλυτα εύρη (εύρος $). Λοιπόν, οι απόλυτες οργές χρησιμοποιούνται όταν δεν θέλουμε να αλλάξει το εύρος κατά την αντιγραφή του κελιού τύπου σε άλλα κελιά.
Για παράδειγμα, αν έχω = VLOOKUP (G2,Β2: Ε14, 2,0), 0) στο κελί Η2, και αν το αντιγράψω ή το σύρω προς τα κάτω στο κελί Η3 ο τύπος θα αλλάξει σε = VLOOKUP (G3,Β3: Ε15, 2,0). Όλες οι αναφορές αλλάζουν σχετικά. Εδώ μπορεί να θέλουμε να αλλάξουμε την αναφορά της τιμής αναζήτησης αλλά όχι τον πίνακα πίνακα. Επειδή το Β2 βγαίνει ή κυμαίνεται και μπορεί να θέλουμε να το έχουμε πάντα στους πίνακες πίνακα.
Έτσι, για να το αποτρέψουμε, χρησιμοποιούμε απόλυτες αναφορές. Κάνουμε αυτές τις περιοχές απόλυτες που δεν θέλουμε να αλλάξουμε, όπως το table_array στο H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). Όταν αντιγράφετε το H2 στο H3, ο τύπος θα είναι = VLOOKUP (G3,$ B $ 2: $ E $ 14, 2,0), 0). Σημειώστε ότι η τιμή αναζήτησης αλλάζει αλλά όχι πίνακας_σειράς.
10. Χρησιμοποιήστε ονόματα εύρους με το VLOOKUP
Στο παραπάνω παράδειγμα, χρησιμοποιήσαμε την απόλυτη αναφορά για τον καθορισμό της συστοιχίας table_array. Φαίνεται αρκετά ασαφές και δύσκολο να διαβαστεί. Τι θα λέγατε αν μπορείτε απλά να γράψετε 'δεδομένα' στη θέση του $ B $ 2: $ E $ 14 και θα αναφερόταν στο $ B $ 2: $ E $ 14 πάντα. Θα διευκολύνει ακόμη περισσότερο την ανάγνωση και τη σύνταξη του τύπου.
Απλώς επιλέξτε το εύρος και ονομάστε το "δεδομένα".
Για να ονομάσετε ένα εύρος, επιλέξτε το εύρος. Κάντε δεξί κλικ, κάντε κλικ στην επιλογή Ορισμός ονόματος και ονομάστε το εύρος που θέλετε. Τώρα απλά = VLOOKUP (G3,δεδομένα,2,0), 0) ο τύπος θα λειτουργεί ακριβώς όπως παλαιότερα.
Μπορείτε να διαβάσετε περισσότερα για τις ονομαζόμενες περιοχές στο Excel. Έχω εξηγήσει κάθε πτυχή του ονόματος της περιοχής εδώ.
11. Μπαλαντέρ για μερική αντιστοίχιση χρησιμοποιώντας το VLOOKUP
Το VLOOKUP επιτρέπει μερική αντιστοίχιση. Ναι, ενώ κάνετε ακριβή αντιστοίχιση του VLOOKUP, μπορείτε να χρησιμοποιήσετε τελεστές μπαλαντέρ για μερική αντιστοίχιση. Για παράδειγμα, εάν θέλετε να αναζητήσετε μια τιμή που ξεκινά με "Gil", μπορείτε να χρησιμοποιήσετε τον χειριστή μπαλαντέρ * με το "Gil" ως "Gil *". Ας δούμε ένα παράδειγμα.
Εδώ θέλω να πάρω βαθμούς μαθητή του οποίου το όνομα ξεκινά με τον Gil. Για να γίνει αυτό, θα γράψω τον παρακάτω τύπο.
= VLOOKUP ("*Gill", C2: D14,2,0)
Υπάρχουν δύο μπαλαντέρ διαθέσιμες στο excel για χρήση με τη λειτουργία VLOOKUP.
Μπαλαντέρ αστερίσκου (*)Το Αυτό χρησιμοποιείται όταν, ο χρήστης δεν γνωρίζει τον αριθμό των χαρακτήρων που υπάρχουν και γνωρίζει μόνο μερικούς χαρακτήρες αντιστοίχισης. Για παράδειγμα, εάν ο χρήστης γνωρίζει ένα όνομα που ξεκινά με "Sm", τότε θα γράψει "Sm*". Εάν ο χρήστης γνωρίζει ότι μια τιμή αναζήτησης που τελειώνει με "123" τότε θα γράψει "*123". (η κάρτα μπαλαντέρ λειτουργεί μόνο με κείμενα). Και γνωρίζοντας ότι το "se" έρχεται μέσα σε ένα κείμενο, τότε θα γράψει "*se*".
Ερωτηματικό ("?")Το Αυτό χρησιμοποιείται όταν ο χρήστης γνωρίζει τον αριθμό των χαρακτήρων που λείπουν. Για παράδειγμα, αν θέλω να αναζητήσω μια τιμή που περιέχει 4 χαρακτήρες, αλλά δεν ξέρω ποιοι είναι, θα γράψω απλά "????" στη θέση της τιμής αναζήτησης.
Το Vlookup θα επιστρέψει την αντίστοιχη τιμή της πρώτης τιμής που έχει ακριβώς τέσσερις χαρακτήρες.
Σπουδαίος: Η κάρτα μπαλαντέρ λειτουργεί μόνο με τιμές κειμένου. Μετατρέψτε αριθμούς σε κείμενο εάν θέλετε να χρησιμοποιήσετε μπαλαντέρ μαζί τους.
Οι αδυναμίες της λειτουργίας VLOOKUP
Το VLOOKUP είναι πράγματι μια ισχυρή και εύκολη λειτουργία για τη λήψη τιμών, αλλά υπάρχουν πολλοί τομείς όπου το VLOOKUP δεν είναι τόσο χρήσιμο. Εάν εργάζεστε στο excel, πρέπει να τα γνωρίζετε επίσης και πώς να κάνετε εργασίες που το VLOOKUP δεν μπορεί να κάνει.
12. Δεν είναι δυνατή η ανάκτηση τιμής από αριστερά του πίνακα_πίνακα
Το μεγαλύτερο ελάττωμα της συνάρτησης VLOOKUP είναι ότι δεν μπορεί να λάβει τιμή από τα αριστερά του πίνακα πίνακα. Το VLOOKUP αναζητά μόνο δεξιά από την τιμή αναζήτησης.
Αυτό συμβαίνει επειδή, το VLOOKUP αναζητά δεδομένο τιμή αναζήτησης στην πρώτη στήλη του δεδομένου πίνακας πίνακα. Δεν θα επιστρέψει ποτέ μια τιμή έξω από τον πίνακα. Και αν προσπαθήσετε να διατηρήσετε την αριστερή στήλη στον πίνακα πίνακα, γίνεται η πρώτη στήλη του πίνακα πίνακα, επομένως η τιμή αναζήτησης θα αναζητηθεί σε αυτό το εύρος. Που πιθανότατα θα δημιουργήσει λάθος.
Για την αναζήτηση τιμών από αριστερά μιας τιμής αναζήτησης, χρησιμοποιούμε το INDEX-MATCH. Η συνάρτηση INDEX-MATCH μπορεί να αναζητήσει τιμές από οποιαδήποτε στήλη στο φύλλο. Στην πραγματικότητα, το εύρος αναζήτησης και το εύρος από το οποίο θέλετε να αναζητήσετε τιμές είναι εντελώς ανεξάρτητες, γεγονός που καθιστά την αντιστοίχιση ευρετηρίου εξαιρετικά προσαρμόσιμη.
Το VLOOKUP επιστρέφει #N/A ακόμη και όταν υπάρχει τιμή αναζήτησης.
#N/Ένα σφάλμα επιστρέφεται από τη συνάρτηση VLOOKUP όταν δεν βρίσκεται η τιμή αναζήτησης. Μπορεί να το βρείτε ενοχλητικό όταν υπάρχει μια τιμή στο εύρος, αλλά το VLOOKUP επιστρέφει σφάλμα #N/A.
14. Όταν οι αριθμοί διαμορφώνονται ως κείμενο
Αυτό συμβαίνει κυρίως όταν οι αριθμοί διαμορφώνονται ως κείμενο. Όταν βρείτε εντολή CTRL+F, το Excel θα το βρείτε, αλλά όταν προσπαθείτε να χρησιμοποιήσετε το VLOOKUP, επιστρέφει #N/A. Αυτή η εργασία δίνεται στις περισσότερες συνεντεύξεις ως ερωτήσεις τέχνης.
Στην παραπάνω εικόνα, μπορείτε να δείτε ότι το 101 είναι εκεί, αλλά ο τύπος επιστρέφει ένα σφάλμα. Πώς, το χειρίζεσαι; Λοιπόν, υπάρχουν δύο μέθοδοι.
1. Μετατρέψτε κείμενο σε αριθμό στα δεδομένα
Μπορείτε να δείτε αυτήν την πράσινη ετικέτα στην αριστερή επάνω γωνία στη στήλη rollno. Υποδεικνύουν ότι κάτι είναι ασυνήθιστο στην τιμή του κελιού. Όταν επιλέγετε το κελί, θα δείξει ότι ο αριθμός έχει διαμορφωθεί ως κείμενο.
Όταν κάνετε κλικ στο θαυμαστικό (!), Θα εμφανιστεί η επιλογή Μετατροπή σε αριθμό. Επιλέξτε όλα τα κελιά και κάντε κλικ σε αυτήν την επιλογή. Όλες οι τιμές θα μετατραπούν σε κείμενο.
2. Μετατρέψτε τον αριθμό αναζήτησης σε κείμενο στον τύπο
Στο παραπάνω παράδειγμα, μεταλλάξαμε αρχικά δεδομένα. Μπορεί να μην θέλετε να αλλάξετε τίποτα στα αρχικά δεδομένα. Επομένως, θα θέλατε να το κάνετε αυτό στον τύπο. Για να αλλάξετε τον αριθμό σε κείμενο στον τύπο VLOOKUP γράψτε αυτό.
= VLOOKUP (ΚΕΙΜΕΝΟ (G2, "0"), B2: D14,2,0)
Εδώ ο τύπος αλλάζει δυναμικά τον αριθμό σε κείμενο και στη συνέχεια τον ταιριάζει με το δεδομένο εύρος.
15. Κείμενο που έχει κεντρικούς και πίσω χώρους
Ορισμένα δεδομένα που παρέχονται από χειριστές εισαγωγής δεδομένων και δεδομένα από το Διαδίκτυο δεν είναι καθαρά. Μπορεί να έχουν πίσω διαστήματα ή κάποιους μη εκτυπώσιμους χαρακτήρες. Κατά την αναζήτηση χρησιμοποιώντας αυτές τις τιμές, ενδέχεται να δείτε κάποιο σφάλμα #N/A. Για να το αποφύγετε, καθαρίστε πρώτα τα δεδομένα σας. Για να αφαιρέσετε τους κεντρικούς χώρους χρησιμοποιήστε τη λειτουργία TRIM. Το TRIM αφαιρεί οποιονδήποτε αριθμό κεντρικών ή κενών χώρων από το κείμενο.
Έτσι, προτείνω να προσθέσετε μια νέα στήλη και να λάβετε τα καθαρισμένα δεδομένα σας εκεί. Στη συνέχεια, επικολλήστε την αξία. Τώρα αν θέλετε, μπορείτε να απαλλαγείτε από την αρχική στήλη.
= TRIM (ΚΑΘΑΡΙΣΜΟΣ (κείμενο))
Σφάλματα χειρισμού του VLOOKUP
Όπως γνωρίζετε, το VLOOKUP δεν βρίσκει μια τιμή, επιστρέφει σφάλμα #N/A. Λοιπόν, είναι εντάξει να λάβετε σφάλμα #N/A, ίσως μερικές φορές σκοπεύετε να το κάνετε, όπως όταν θέλετε να ελέγξετε εάν η τιμή υπάρχει ήδη στη λίστα ή όχι. Εκεί #N/A σημαίνει ότι η τιμή δεν είναι εκεί.
Αλλά το #N/A φαίνεται άσχημο. Τι θα λέγατε να αποκτήσετε μια φιλική προς το χρήστη έξοδο, όπως "Δεν βρέθηκε" ή "Δεν βρέθηκε πελάτης". Μπορούμε να χρησιμοποιήσουμε το IFERROR με τη λειτουργία VLOOKUP για να αποφύγουμε το #N/A.
Χρησιμοποιήστε τον παρακάτω τύπο για να παγιδεύσετε το σφάλμα στο VLOOKUP.
= IFERROR (VLOOKUP (150, B2: E14,2,0), "Ο ρόλος δεν βρέθηκε")
16. VLOOKUP με ΠΟΛΛΑΠΛΑ Κριτήρια
Το VLOOKUP δεν μπορεί να λειτουργήσει με πολλά κριτήρια.Ναι, μπορείτε να έχετε μόνο ένα κριτήριο για την αναζήτηση τιμών χρησιμοποιώντας τον τύπο VLOOKUP.
Για παράδειγμα, εάν έχετε το όνομα και το επώνυμο σε δύο ξεχωριστές στήλες.
Και τώρα, αν θέλετε να αναζητήσετε την τιμή της οποίας το όνομα είναι John και Επώνυμο Dave, τότε δεν μπορείτε να το κάνετε κανονικά.
Υπάρχει όμως μια λύση για αυτό. Μπορείτε να δημιουργήσετε μια ξεχωριστή στήλη συνδέοντας το όνομα και το επώνυμο και στη συνέχεια να αναζητήσετε τη στήλη ονόματος και επώνυμου.
Υπάρχει ένα Εναλλακτική λύση INDEX-MATCH που μπορεί να αναζητήσει πολλαπλά κριτήρια χωρίς καμία στήλη βοήθειας.
17. Το VLOOKUP ανακτά μόνο την τιμή πρώτης εύρεσης
Φανταστείτε ότι έχετε κάποια δεδομένα στην κεντρική περιοχή. Τώρα θέλετε να λάβετε τις πρώτες 5 εγγραφές δεδομένων της κεντρικής περιοχής. Το VLOOKUP θα επιστρέψει μόνο την πρώτη κεντρική τιμή και στις πέντε εγγραφές. Αυτό είναι ένα σημαντικό σκηνικό.
Αλλά αυτό δεν σημαίνει ότι δεν μπορούμε να το πετύχουμε. Μπορούμε να χρησιμοποιήσουμε έναν σύνθετο τύπο συστοιχιών όπως παρακάτω.
= INDEX ($ C $ 2: $ C $ 14, ΜΙΚΡΟ (ΑΝ (G2 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1), ROW (1: 1)))
Γράψτε αυτόν τον τύπο και πατήστε CTRL+SHIFT+ENTER.
Και έγινε.
Το έχω εξηγήσει στο άρθρο πώς να κάνετε ΕΓΚΑΤΑΣΤΑΣΗ πολλαπλών τιμών στο Excel.
Λοιπόν, ναι, σε αυτό το άρθρο έχω καλύψει όλες τις πιθανές πτυχές της λειτουργίας VLOOKUP σύμφωνα με τις γνώσεις μου. Αν νομίζετε ότι έχω χάσει κάτι, παρακαλώ ενημερώστε με στην παρακάτω ενότητα σχολίων.
Vlookup Top 5 Values with Duplicate Values Using INDEX-MATCH στο Excel
VLOOKUP Πολλαπλές τιμές
VLOOKUP με Dynamic Col Index
Μερική αντιστοίχιση με τη λειτουργία VLOOKUP
Χρησιμοποιήστε το VLOOKUP από δύο ή περισσότερους πίνακες αναζήτησης
Vlookup κατά ημερομηνία στο Excel
Χρησιμοποιώντας έναν τύπο VLOOKUP για να ελέγξετε εάν υπάρχει μια τιμή
Πώς να κάνετε VLOOKUP από διαφορετικό φύλλο Excel
VLOOKUP με αριθμούς και κείμενο
IF, ISNA και VLOOKUP συνάρτηση
Λειτουργία ISNA και VLOOKUP
Λειτουργία IFERROR και VLOOKUP