Λειτουργία VLOOKUP - Εισαγωγή
Η λειτουργία VLOOKUP είναι το σημείο αναφοράς.
Γνωρίζετε κάτι στο Excel εάν γνωρίζετε πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP.
Εάν δεν το κάνετε, καλύτερα να μην αναφέρετε το Excel ως έναν από τους ισχυρούς τομείς σας στο βιογραφικό σας.
Έχω συμμετάσχει στις συνεντεύξεις της ομάδας όπου μόλις ο υποψήφιος ανέφερε το Excel ως τομέα εμπειρογνωμοσύνης του, το πρώτο πράγμα που ρωτήθηκε ήταν - το καταλάβατε - η λειτουργία VLOOKUP.
Τώρα που γνωρίζουμε πόσο σημαντική είναι αυτή η συνάρτηση του Excel, είναι λογικό να την αποκτήσουμε εντελώς για να μπορούμε να πούμε με υπερηφάνεια - "Ξέρω ένα ή δύο πράγματα στο Excel".
Αυτό θα είναι ένα τεράστιο σεμινάριο VLOOKUP (σύμφωνα με τα πρότυπά μου).
Θα καλύψω όλα όσα πρέπει να γνωρίζετε και στη συνέχεια θα σας δείξω χρήσιμα και πρακτικά παραδείγματα VLOOKUP.
Λυγίστε λοιπόν.
It’sρθε η ώρα της απογείωσης.
Πότε να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel;
Η συνάρτηση VLOOKUP ταιριάζει καλύτερα σε καταστάσεις όταν ψάχνετε για ένα σημείο δεδομένων που ταιριάζει σε μια στήλη και όταν βρεθεί το αντίστοιχο σημείο δεδομένων, πηγαίνετε δεξιά σε αυτήν τη σειρά και παίρνετε μια τιμή από ένα κελί που είναι ένας καθορισμένος αριθμός στήλες στα δεξιά.
Ας πάρουμε εδώ ένα απλό παράδειγμα για να καταλάβουμε πότε πρέπει να χρησιμοποιούμε το Vlookup στο Excel.
Θυμηθείτε όταν η λίστα με τις βαθμολογίες των εξετάσεων βγήκε και επικολλήθηκε στον πίνακα ανακοινώσεων και όλοι τρελαινόταν βρίσκοντας τα ονόματά τους και τη βαθμολογία τους (τουλάχιστον αυτό συνέβαινε όταν ήμουν στο σχολείο).
Εδώ είναι πώς λειτούργησε:
- Ανεβαίνετε στον πίνακα ανακοινώσεων και αρχίζετε να αναζητάτε το όνομα ή τον αριθμό εγγραφής σας (τρέχοντας το δάχτυλό σας από πάνω προς τα κάτω στη λίστα).
- Μόλις εντοπίσετε το όνομά σας, μετακινείτε τα μάτια σας δεξιά από το όνομα/τον αριθμό εγγραφής για να δείτε τις βαθμολογίες σας.
Και αυτό ακριβώς κάνει για εσάς η λειτουργία Excel VLOOKUP (μη διστάσετε να χρησιμοποιήσετε αυτό το παράδειγμα στην επόμενη συνέντευξή σας).
Η συνάρτηση VLOOKUP αναζητά μια καθορισμένη τιμή σε μια στήλη (στο παραπάνω παράδειγμα, ήταν το όνομά σας) και όταν εντοπίσει την καθορισμένη αντιστοίχιση, επιστρέφει μια τιμή στην ίδια σειρά (τα σημάδια που αποκτήσατε).
Σύνταξη
= VLOOKUP (τιμή_αναζήτησης, πίνακας_σειράς, col_index_num, [range_lookup])
Ορίσματα εισόδου
- αξία_αναζήτησης - αυτή είναι η τιμή αναζήτησης που προσπαθείτε να βρείτε στην πιο αριστερή στήλη ενός πίνακα. Θα μπορούσε να είναι μια τιμή, μια αναφορά κελιού ή μια συμβολοσειρά κειμένου. Στο παράδειγμα του φύλλου βαθμολογίας, αυτό θα ήταν το όνομά σας.
- table_array - αυτός είναι ο πίνακας πίνακα στον οποίο αναζητάτε την τιμή. Αυτό θα μπορούσε να είναι μια αναφορά σε μια περιοχή κελιών ή μια ονομαστική περιοχή. Στο παράδειγμα του φύλλου βαθμολογίας, αυτός θα είναι ολόκληρος ο πίνακας που περιέχει βαθμολογία για όλους για κάθε θέμα
- col_index - αυτός είναι ο αριθμός ευρετηρίου στήλης από τον οποίο θέλετε να λάβετε την αντίστοιχη τιμή. Στο παράδειγμα του φύλλου βαθμολογίας, εάν θέλετε τις βαθμολογίες για τα Μαθηματικά (η οποία είναι η πρώτη στήλη σε έναν πίνακα που περιέχει τις βαθμολογίες), θα αναζητούσατε τη στήλη 1. Αν θέλετε τις βαθμολογίες για τη Φυσική, θα αναζητούσατε τη στήλη 2
- [range_lookup] - εδώ καθορίζετε αν θέλετε ακριβή αντιστοίχιση ή κατά προσέγγιση αντιστοίχιση. Εάν παραλειφθεί, είναι προεπιλεγμένο ως TRUE - κατά προσέγγιση αντιστοίχιση (βλ. πρόσθετες σημειώσεις παρακάτω).
Πρόσθετες σημειώσεις (βαρετές, αλλά σημαντικές για να γνωρίζετε)
- Η αντιστοίχιση θα μπορούσε να είναι ακριβής (FALSE ή 0 in range_lookup) ή κατά προσέγγιση (TRUE ή 1).
- Στην κατά προσέγγιση αναζήτηση, βεβαιωθείτε ότι η λίστα έχει ταξινομηθεί με αύξουσα σειρά (από πάνω προς τα κάτω), διαφορετικά το αποτέλεσμα θα μπορούσε να είναι ανακριβές.
- Όταν το range_lookup είναι TRUE (κατά προσέγγιση αναζήτηση) και τα δεδομένα ταξινομούνται με αύξουσα σειρά:
- Εάν η συνάρτηση VLOOKUP δεν μπορεί να βρει την τιμή, επιστρέφει τη μεγαλύτερη τιμή, η οποία είναι μικρότερη από την τιμή αναζήτησης.
- Επιστρέφει σφάλμα #N/A εάν η τιμή_αναζήτησης είναι μικρότερη από τη μικρότερη τιμή.
- Εάν το lookup_value είναι κείμενο, μπορούν να χρησιμοποιηθούν χαρακτήρες μπαλαντέρ (ανατρέξτε στο παρακάτω παράδειγμα).
Τώρα, ελπίζοντας ότι έχετε μια βασική κατανόηση του τι μπορεί να κάνει η συνάρτηση VLOOKUP, ας ξεφλουδίσουμε αυτό το κρεμμύδι και θα δούμε μερικά πρακτικά παραδείγματα της συνάρτησης VLOOKUP.
10 Παραδείγματα Excel VLOOKUP (Basic & Advanced)
Ακολουθούν 10 χρήσιμες ενδείξεις χρήσης του Excel Vlookup που θα σας δείξουν πώς να το χρησιμοποιείτε στην καθημερινή σας εργασία.
Παράδειγμα 1 - Εύρεση μαθηματικής βαθμολογίας του Μπραντ
Στο παρακάτω παράδειγμα VLOOKUP, έχω μια λίστα με ονόματα μαθητών στην πιο αριστερή στήλη και βαθμούς σε διαφορετικά θέματα στις στήλες Β έως Ε.
Τώρα ας αρχίσουμε να δουλεύουμε και να χρησιμοποιήσουμε τη λειτουργία VLOOKUP για αυτό που κάνει καλύτερα. Από τα παραπάνω δεδομένα, πρέπει να ξέρω πόσο σκόραρε ο Μπραντ στα Μαθηματικά.
Από τα παραπάνω δεδομένα, πρέπει να ξέρω πόσο σκόραρε ο Μπραντ στα Μαθηματικά.
Εδώ είναι ο τύπος VLOOKUP που θα επιστρέψει τη βαθμολογία των μαθηματικών του Brad:
= VLOOKUP ("Brad", $ A $ 3: $ E $ 10,2,0)
Ο παραπάνω τύπος έχει τέσσερα επιχειρήματα:
- "Ακέφαλο καρφί: - αυτή είναι η τιμή αναζήτησης.
- $ A $ 3: $ E $ 10 - αυτό είναι το εύρος των κυττάρων που ψάχνουμε. Θυμηθείτε ότι το Excel αναζητά την τιμή αναζήτησης στην πιο αριστερή στήλη. Σε αυτό το παράδειγμα, θα αναζητούσε το όνομα Brad στο A3: A10 (που είναι η πιο αριστερή στήλη του καθορισμένου πίνακα).
- 2 - Μόλις η συνάρτηση εντοπίσει το όνομα του Brad, θα μεταβεί στη δεύτερη στήλη του πίνακα και θα επιστρέψει την τιμή στην ίδια σειρά με αυτήν του Brad. Η τιμή 2 εδώ έδειξε ότι ψάχνουμε τη βαθμολογία από τη δεύτερη στήλη του καθορισμένου πίνακα.
- 0 - αυτό λέει στη λειτουργία VLOOKUP να αναζητά μόνο ακριβείς αντιστοιχίσεις.
Δείτε πώς λειτουργεί ο τύπος VLOOKUP στο παραπάνω παράδειγμα.
Αρχικά, αναζητά την τιμή Brad στην πιο αριστερή στήλη. Πηγαίνει από πάνω προς τα κάτω και βρίσκει την τιμή στο κελί A6.
Μόλις βρει την τιμή, πηγαίνει δεξιά στη δεύτερη στήλη και παίρνει την τιμή σε αυτήν.
Μπορείτε να χρησιμοποιήσετε την ίδια δομή τύπου για να λάβετε τα σημάδια οποιουδήποτε από τα θέματα.
Για παράδειγμα, για να βρείτε τα σήματα της Μαρίας στη Χημεία, χρησιμοποιήστε τον ακόλουθο τύπο VLOOKUP:
= VLOOKUP ("Μαρία", $ A $ 3: $ E $ 10,4,0)
Στο παραπάνω παράδειγμα, η τιμή αναζήτησης (όνομα μαθητή) εισάγεται σε διπλά εισαγωγικά. Μπορείτε επίσης να χρησιμοποιήσετε μια αναφορά κελιού που περιέχει την τιμή αναζήτησης.
Το όφελος από τη χρήση μιας αναφοράς κελιού είναι ότι καθιστά τον τύπο δυναμικό.
Για παράδειγμα, εάν έχετε ένα κελί με το όνομα ενός μαθητή και λαμβάνετε τη βαθμολογία για τα Μαθηματικά, το αποτέλεσμα θα ενημερώνεται αυτόματα όταν αλλάζετε το όνομα του μαθητή (όπως φαίνεται παρακάτω):
Εάν εισαγάγετε μια τιμή αναζήτησης που δεν βρίσκεται στην πιο αριστερή στήλη, επιστρέφει σφάλμα #N/A.
Παράδειγμα 2 - Αμφίδρομη αναζήτηση
Στο Παράδειγμα 1 παραπάνω, κωδικοποιήσαμε σκληρά την τιμή της στήλης. Ως εκ τούτου, ο τύπος θα επέστρεφε πάντα τη βαθμολογία για τα Μαθηματικά, καθώς έχουμε χρησιμοποιήσει το 2 ως αριθμό ευρετηρίου στήλης.
Αλλά τι γίνεται αν θέλετε να κάνετε δυναμική τόσο την τιμή VLOOKUP όσο και τον αριθμό ευρετηρίου στήλης. Για παράδειγμα, όπως φαίνεται παρακάτω, μπορείτε να αλλάξετε είτε το όνομα του μαθητή είτε το όνομα του θέματος και ο τύπος VLOOKUP συγκεντρώνει τη σωστή βαθμολογία. Αυτό είναι ένα παράδειγμα αμφίδρομης φόρμουλας VLOOKUP.
Αυτό είναι ένα παράδειγμα αμφίδρομης συνάρτησης VLOOKUP.
Για να κάνετε αυτόν τον τύπο αμφίδρομης αναζήτησης, πρέπει επίσης να κάνετε τη στήλη δυναμική. Έτσι, όταν ένας χρήστης αλλάζει θέμα, ο τύπος επιλέγει αυτόματα τη σωστή στήλη (2 στην περίπτωση των Μαθηματικών, 3 στην περίπτωση της Φυσικής, κ.ο.κ.).
Για να γίνει αυτό, πρέπει να χρησιμοποιήσετε τη συνάρτηση MATCH ως όρισμα στήλης.
Εδώ είναι ο τύπος VLOOKUP που θα το κάνει αυτό:
= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Ο παραπάνω τύπος χρησιμοποιεί MATCH (H3, $ A $ 2: $ E $ 2,0) ως αριθμό στήλης. Η συνάρτηση MATCH παίρνει το όνομα του θέματος ως τιμή αναζήτησης (σε H3) και επιστρέφει τη θέση του στο A2: E2. Επομένως, εάν χρησιμοποιείτε μαθηματικά, θα επέστρεφε 2 καθώς τα μαθηματικά βρίσκονται στο Β2 (το οποίο είναι το δεύτερο κελί στο καθορισμένο εύρος πίνακα).
Παράδειγμα 3 - Χρήση αναπτυσσόμενων λιστών ως τιμών αναζήτησης
Στο παραπάνω παράδειγμα, πρέπει να εισαγάγουμε χειροκίνητα τα δεδομένα. Αυτό θα μπορούσε να είναι χρονοβόρο και επιρρεπές σε σφάλματα, ειδικά αν έχετε μια τεράστια λίστα τιμών αναζήτησης.
Μια καλή ιδέα σε τέτοιες περιπτώσεις είναι να δημιουργήσετε μια αναπτυσσόμενη λίστα με τις τιμές αναζήτησης (σε αυτήν την περίπτωση, θα μπορούσαν να είναι ονόματα και θέματα μαθητών) και στη συνέχεια να επιλέξετε απλώς από τη λίστα.
Με βάση την επιλογή, ο τύπος θα ενημερώνει αυτόματα το αποτέλεσμα.
Κάτι όπως φαίνεται παρακάτω:
Αυτό καθιστά ένα καλό στοιχείο πίνακα εργαλείων, καθώς μπορείτε να έχετε ένα τεράστιο σύνολο δεδομένων με εκατοντάδες μαθητές στο πίσω άκρο, αλλά ο τελικός χρήστης (ας πούμε δάσκαλος) μπορεί να πάρει γρήγορα τις βαθμολογίες ενός μαθητή σε ένα θέμα κάνοντας απλώς τις επιλογές από το drop down.
Πώς να το φτιάξετε αυτό:
Ο τύπος VLOOKUP που χρησιμοποιείται σε αυτήν την περίπτωση είναι ο ίδιος που χρησιμοποιείται στο Παράδειγμα 2.
= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Οι τιμές αναζήτησης έχουν μετατραπεί σε αναπτυσσόμενες λίστες.
Ακολουθούν τα βήματα για τη δημιουργία της αναπτυσσόμενης λίστας:
- Επιλέξτε το κελί στο οποίο θέλετε την αναπτυσσόμενη λίστα. Σε αυτό το παράδειγμα, στο G4, θέλουμε τα ονόματα των μαθητών.
- Μεταβείτε στα Δεδομένα -> Εργαλεία δεδομένων -> Επικύρωση δεδομένων.
- Στο πλαίσιο Διάλογος επικύρωσης δεδομένων, στην καρτέλα ρυθμίσεις, επιλέξτε Λίστα από το αναπτυσσόμενο μενού Επιτρέπεται.
- Στην πηγή, επιλέξτε $ A $ 3: $ A $ 10
- Κάντε κλικ στο OK.
Τώρα θα έχετε την αναπτυσσόμενη λίστα στο κελί G4. Ομοίως, μπορείτε να δημιουργήσετε ένα στο Η3 για τα θέματα.
Παράδειγμα 4 - Τριπλή αναζήτηση
Τι είναι η αναζήτηση τριών κατευθύνσεων;
Στο Παράδειγμα 2, χρησιμοποιήσαμε έναν πίνακα αναζήτησης με βαθμολογίες για μαθητές σε διαφορετικά θέματα. Αυτό είναι ένα παράδειγμα αμφίδρομης αναζήτησης καθώς χρησιμοποιούμε δύο μεταβλητές για να λάβουμε τη βαθμολογία (όνομα μαθητή και όνομα υποκειμένου).
Τώρα, ας υποθέσουμε ότι σε έναν χρόνο, ένας μαθητής έχει τρία διαφορετικά επίπεδα εξετάσεων, τεστ μονάδας, ενδιάμεση και τελική εξέταση (αυτό είχα όταν ήμουν φοιτητής).
Μια αναζήτηση τριών κατευθύνσεων θα ήταν η δυνατότητα να λάβετε βαθμολογίες μαθητή για ένα συγκεκριμένο θέμα από το καθορισμένο επίπεδο εξετάσεων.
Κάτι όπως φαίνεται παρακάτω:
Στο παραπάνω παράδειγμα, η συνάρτηση VLOOKUP μπορεί να αναζητήσει σε τρεις διαφορετικούς πίνακες (Δοκιμή μονάδας, ενδιάμεση και τελική εξέταση) και επιστρέφει τη βαθμολογία για τον καθορισμένο μαθητή στο καθορισμένο θέμα.
Εδώ είναι ο τύπος που χρησιμοποιείται στο κελί Η4:
= VLOOKUP (G4, CHOOSE (IF (H2 = "Unit Test", 1, IF (H2 = "Midterm", 2,3)), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23), MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Αυτός ο τύπος χρησιμοποιεί τη συνάρτηση CHOOSE για να βεβαιωθείτε ότι αναφέρεται ο σωστός πίνακας. Ας αναλύσουμε το μέρος CHOOSE του τύπου:
ΕΠΙΛΟΓΗ (ΕΑΝ (H2 = "Δοκιμή μονάδας", 1, IF (H2 = "Ενδιάμεσο", 2,3)), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23 )
Το πρώτο επιχείρημα του τύπου είναι IF (H2 = "Unit Test", 1, IF (H2 = "Midterm", 2,3)), το οποίο ελέγχει το κελί H2 και βλέπει σε ποιο επίπεδο εξετάσεων αναφέρεται. Εάν πρόκειται για δοκιμή μονάδας, επιστρέφει $ 3 $: $ E $ 7, το οποίο έχει τις βαθμολογίες για τη δοκιμή μονάδας. Εάν είναι ενδιάμεσο, επιστρέφει $ 11 $: $ E $ 15, αλλιώς επιστρέφει $ 19 $: $ E $ 23.
Με αυτόν τον τρόπο, ο πίνακας πίνακα VLOOKUP είναι δυναμικός και ως εκ τούτου καθίσταται μια αναζήτηση τριών κατευθύνσεων.
Παράδειγμα 5 - Λήψη της τελευταίας τιμής από μια λίστα
Μπορείτε να δημιουργήσετε έναν τύπο VLOOKUP για να λάβετε την τελευταία αριθμητική τιμή από μια λίστα.
Ο μεγαλύτερος θετικός αριθμός που μπορείτε να χρησιμοποιήσετε στο Excel είναι 9.99999999999999E+307. Αυτό σημαίνει επίσης ότι ο μεγαλύτερος αριθμός αναζήτησης στον αριθμό VLOOKUP είναι επίσης ο ίδιος.
Δεν νομίζω ότι θα χρειαστείτε ποτέ κανέναν υπολογισμό που να περιλαμβάνει τόσο μεγάλο αριθμό. Και αυτό ακριβώς μπορούμε να χρησιμοποιήσουμε για να πάρουμε τον τελευταίο αριθμό σε μια λίστα.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων (στο Α1: Α14) όπως φαίνεται παρακάτω και θέλετε να λάβετε τον τελευταίο αριθμό στη λίστα.
Εδώ είναι ο τύπος που μπορείτε να χρησιμοποιήσετε:
= VLOOKUP (9.99999999999999E+307, $ A $ 1: $ A $ 14,ΑΛΗΘΗΣ)
Σημειώστε ότι ο παραπάνω τύπος χρησιμοποιεί κατά προσέγγιση αντιστοίχιση VLOOKUP (σημειώστε TRUE στο τέλος του τύπου, αντί FALSE ή 0). Επίσης, σημειώστε ότι η λίστα δεν χρειάζεται να ταξινομηθεί για να λειτουργήσει αυτός ο τύπος VLOOKUP.
Δείτε πώς λειτουργεί η κατά προσέγγιση λειτουργία VLOOKUP. Σαρώνει την πιο αριστερή στήλη από πάνω προς τα κάτω.
- Εάν βρει μια ακριβή αντιστοίχιση, επιστρέφει αυτήν την τιμή.
- Εάν βρει μια τιμή υψηλότερη από την τιμή αναζήτησης, επιστρέφει την τιμή στο κελί πάνω από αυτήν.
- Εάν η τιμή αναζήτησης είναι μεγαλύτερη από όλες τις τιμές στη λίστα, επιστρέφει την τελευταία τιμή.
Στο παραπάνω παράδειγμα, το τρίτο σενάριο λειτουργεί.
Από 9.99999999999999E+307 είναι ο μεγαλύτερος αριθμός που μπορεί να χρησιμοποιηθεί στο Excel, όταν χρησιμοποιείται ως τιμή αναζήτησης, επιστρέφει τον τελευταίο αριθμό από τη λίστα.
Με τον ίδιο τρόπο, μπορείτε επίσης να το χρησιμοποιήσετε για να επιστρέψετε το τελευταίο στοιχείο κειμένου από τη λίστα. Εδώ είναι ο τύπος που μπορεί να το κάνει αυτό:
= VLOOKUP ("zzz", $ A $ 1: $ A $ 8,1,ΑΛΗΘΗΣ)
Ακολουθεί η ίδια λογική. Το Excel εξετάζει όλα τα ονόματα και επειδή το zzz θεωρείται μεγαλύτερο από οποιοδήποτε όνομα/κείμενο ξεκινώντας με αλφάβητα πριν από το zzz, θα επέστρεφε το τελευταίο στοιχείο από τη λίστα.
Παράδειγμα 6 - Μερική αναζήτηση χρησιμοποιώντας χαρακτήρες μπαλαντέρ και VLOOKUP
Οι χαρακτήρες μπαλαντέρ Excel μπορούν να είναι πραγματικά χρήσιμοι σε πολλές περιπτώσεις.
Είναι αυτό το μαγικό φίλτρο που δίνει στους τύπους σας σούπερ δυνάμεις.
Απαιτείται μερική αναζήτηση όταν πρέπει να αναζητήσετε μια τιμή σε μια λίστα και δεν υπάρχει ακριβής αντιστοίχιση.
Για παράδειγμα, ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να αναζητήσετε την εταιρεία ABC σε μια λίστα, αλλά η λίστα έχει ABC Ltd αντί ABC.
Δεν μπορείτε να χρησιμοποιήσετε το ABC ως τιμή αναζήτησης καθώς δεν υπάρχει ακριβής αντιστοίχιση στη στήλη A. Η κατά προσέγγιση αντιστοίχιση οδηγεί επίσης σε εσφαλμένα αποτελέσματα και απαιτεί την ταξινόμηση της λίστας με αύξουσα σειρά.
Ωστόσο, μπορείτε να χρησιμοποιήσετε έναν χαρακτήρα μπαλαντέρ στη λειτουργία VLOOKUP για να λάβετε την αντιστοιχία.
Εισαγάγετε τον ακόλουθο τύπο στο κελί D2 και σύρετέ τον στα άλλα κελιά:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Πώς λειτουργεί αυτός ο τύπος;
Στον παραπάνω τύπο, αντί να χρησιμοποιήσετε την τιμή αναζήτησης ως έχει, πλαισιώνεται και στις δύο πλευρές με τον αστερίσκο χαρακτήρων μπαλαντέρ (*) - "*" & C2 & "*"
Ο αστερίσκος είναι ένας χαρακτήρας μπαλαντέρ στο Excel και μπορεί να αντιπροσωπεύει οποιονδήποτε αριθμό χαρακτήρων.
Η χρήση του αστερίσκου και στις δύο πλευρές της τιμής αναζήτησης λέει στο Excel ότι πρέπει να αναζητήσει οποιοδήποτε κείμενο περιέχει τη λέξη στο C2. Θα μπορούσε να έχει οποιονδήποτε αριθμό χαρακτήρων πριν ή μετά το κείμενο στο C2.
Για παράδειγμα, το κελί C2 έχει ABC, οπότε η συνάρτηση VLOOKUP εξετάζει τα ονόματα στο A2: A8 και αναζητά ABC. Βρίσκει αντιστοίχιση στο κελί A2, καθώς περιέχει ABC στο ABC Ltd. Δεν έχει σημασία αν υπάρχουν χαρακτήρες στα αριστερά ή δεξιά του ABC. Μέχρι να υπάρχει ABC σε μια συμβολοσειρά κειμένου, θα θεωρείται αντιστοίχιση.
Σημείωση: Η συνάρτηση VLOOKUP επιστρέφει πάντα την πρώτη τιμή αντιστοίχισης και σταματά να ψάχνει περαιτέρω. Αν λοιπόν έχετε ABC Ltd., και ABC Corporation σε μια λίστα, θα επιστρέψει το πρώτο και θα αγνοήσει τα υπόλοιπα.
Παράδειγμα 7 - VLOOKUP Επιστροφή σφάλματος παρά την αντιστοίχιση στην τιμή αναζήτησης
Μπορεί να σας τρελάνει όταν βλέπετε ότι υπάρχει μια αντίστοιχη τιμή αναζήτησης και η συνάρτηση VLOOKUP επιστρέφει ένα σφάλμα.
Για παράδειγμα, στην παρακάτω περίπτωση, υπάρχει αντιστοίχιση (Matt), αλλά η συνάρτηση VLOOKUP εξακολουθεί να επιστρέφει σφάλμα.
Τώρα, ενώ μπορούμε να δούμε ότι υπάρχει ένα ταίρι, αυτό που δεν μπορούμε να δούμε με γυμνό μάτι είναι ότι θα μπορούσαν να υπάρχουν κεντρικοί ή πίσω χώροι. Εάν έχετε αυτά τα επιπλέον κενά πριν, μετά ή μεταξύ των τιμών αναζήτησης, ΔΕΝ είναι ακριβής αντιστοίχιση.
Αυτό συμβαίνει συχνά όταν εισάγετε δεδομένα από μια βάση δεδομένων ή τα λαμβάνετε από κάποιον άλλο. Αυτοί οι κεντρικοί/πίσω χώροι έχουν την τάση να γλιστρήσουν.
Η λύση εδώ είναι η συνάρτηση TRIM. Αφαιρεί τυχόν κενά ή πίσω διαστήματα ή επιπλέον κενά μεταξύ των λέξεων.
Εδώ είναι ο τύπος που θα σας δώσει το σωστό αποτέλεσμα.
= VLOOKUP ("Matt", TRIM ($ A $ 2: $ A $ 9), 1,0)
Δεδομένου ότι πρόκειται για έναν τύπο πίνακα, χρησιμοποιήστε το Control + Shift + Enter αντί για το μόνο Enter.
Ένας άλλος τρόπος θα μπορούσε να είναι να επεξεργαστείτε πρώτα τον πίνακα αναζήτησης με τη συνάρτηση TRIM για να βεβαιωθείτε ότι έχουν εξαφανιστεί όλοι οι επιπλέον χώροι και, στη συνέχεια, να χρησιμοποιήσετε τη συνάρτηση VLOOKUP ως συνήθως.
Παράδειγμα 8 - Εκτέλεση αναζήτησης κατά περίπτωση
Από προεπιλογή, η τιμή αναζήτησης στη συνάρτηση VLOOKUP δεν έχει διάκριση πεζών -κεφαλαίων. Για παράδειγμα, εάν η τιμή αναζήτησης είναι MATT, matt ή Matt, είναι το ίδιο για τη συνάρτηση VLOOKUP. Θα επιστρέψει την πρώτη τιμή αντιστοίχισης ανεξάρτητα από την περίπτωση.
Αλλά αν θέλετε να κάνετε μια αναζήτηση με διάκριση πεζών-κεφαλαίων, πρέπει να χρησιμοποιήσετε τη συνάρτηση ΑΚΡΙΒΗ μαζί με τη συνάρτηση VLOOKUP.
Εδώ είναι ένα παράδειγμα:
Όπως μπορείτε να δείτε, υπάρχουν τρία κελιά με το ίδιο όνομα (στα Α2, Α4 και Α5) αλλά με διαφορετική αλφαβητική περίπτωση. Στα δεξιά, έχουμε τα τρία ονόματα (Matt, MATT και matt) μαζί με τις βαθμολογίες τους στα Μαθηματικά.
Τώρα η συνάρτηση VLOOKUP δεν είναι εξοπλισμένη για να χειρίζεται τιμές αναζήτησης με διάκριση πεζών-κεφαλαίων. Σε αυτό το παραπάνω παράδειγμα, θα επέστρεφε πάντα 38, το οποίο είναι το σκορ για τον Ματ στο Α2.
Για να το κάνουμε με διάκριση πεζών -κεφαλαίων, πρέπει να χρησιμοποιήσουμε μια στήλη βοηθού (όπως φαίνεται παρακάτω):
Για να λάβετε τις τιμές στη στήλη βοηθού, χρησιμοποιήστε τη συνάρτηση = ROW (). Θα πάρει απλώς τον αριθμό σειράς στο κελί.
Μόλις έχετε τη στήλη βοηθού, εδώ είναι ο τύπος που θα δώσει το αποτέλεσμα αναζήτησης με διάκριση πεζών-κεφαλαίων.
= VLOOKUP (MAX (EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))), $ B $ 2: $ C $ 9,2,0)
Τώρα ας σπάσουμε και να καταλάβουμε τι κάνει αυτό:
- ΑΚΡΙΒΗ (E2, $ A $ 2: $ A $ 9) - Αυτό το μέρος θα συγκρίνει την τιμή αναζήτησης στο E2 με όλες τις τιμές στο A2: A9. Επιστρέφει μια σειρά TRUE/FALSEs όπου επιστρέφει το TRUE όπου υπάρχει ακριβής αντιστοίχιση. Σε αυτήν την περίπτωση, θα επέστρεφε τον ακόλουθο πίνακα: {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
- ΑΚΡΙΒΗ (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - Αυτό το μέρος πολλαπλασιάζει τον πίνακα TRUE/FALSE με τον αριθμό σειράς. Όπου υπάρχει TRUE, δίνει τον αριθμό σειράς , αλλιώς δίνει 0. Σε αυτήν την περίπτωση, θα επέστρεφε {2; 0; 0; 0; 0; 0; 0; 0}.
- MAX (EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))) - Αυτό το τμήμα επιστρέφει τη μέγιστη τιμή από τον πίνακα αριθμών. Σε αυτή την περίπτωση, θα επέστρεφε 2 (που είναι ο αριθμός σειράς όπου υπάρχει ακριβής αντιστοίχιση).
- Τώρα χρησιμοποιούμε απλώς αυτόν τον αριθμό ως τιμή αναζήτησης και χρησιμοποιούμε τον πίνακα αναζήτησης ως B2: C9
Σημείωση: Δεδομένου ότι πρόκειται για έναν τύπο πίνακα, χρησιμοποιήστε Control + Shift + Enter αντί για απλά εισαγωγή.
Παράδειγμα 9 - Χρήση VLOOKUP με πολλαπλά κριτήρια
Η συνάρτηση Excel VLOOKUP, στη βασική της μορφή, μπορεί να αναζητήσει μία τιμή αναζήτησης και να επιστρέψει την αντίστοιχη τιμή από την καθορισμένη γραμμή.
Συχνά όμως υπάρχει ανάγκη χρήσης του VLOOKUP στο Excel με πολλαπλά κριτήρια.
Ας υποθέσουμε ότι έχετε δεδομένα με το όνομα των μαθητών, τον τύπο των εξετάσεων και τη βαθμολογία των Μαθηματικών (όπως φαίνεται παρακάτω):
Η χρήση της λειτουργίας VLOOKUP για τη λήψη της βαθμολογίας των Μαθηματικών για κάθε μαθητή για τα αντίστοιχα επίπεδα εξετάσεων θα μπορούσε να είναι μια πρόκληση.
Για παράδειγμα, αν προσπαθήσετε να χρησιμοποιήσετε το VLOOKUP με την Matt ως τιμή αναζήτησης, θα επιστρέφει πάντα 91, η οποία είναι η βαθμολογία για την πρώτη εμφάνιση του Matt στη λίστα. Για να λάβετε τη βαθμολογία για τον Matt για κάθε τύπο εξετάσεων (Test Unit, Mid term και Final), πρέπει να δημιουργήσετε μια μοναδική τιμή αναζήτησης.
Αυτό μπορεί να γίνει χρησιμοποιώντας τη στήλη βοηθού. Το πρώτο βήμα είναι να εισαγάγετε μια στήλη βοηθού στα αριστερά των αποτελεσμάτων.
Τώρα, για να δημιουργήσετε έναν μοναδικό προσδιοριστή για κάθε παρουσία του ονόματος, χρησιμοποιήστε τον ακόλουθο τύπο στο C2: = A2 & ”|” & B2
Αντιγράψτε αυτόν τον τύπο σε όλα τα κελιά στη στήλη βοηθού. Αυτό θα δημιουργήσει μοναδικές τιμές αναζήτησης για κάθε παρουσία ενός ονόματος (όπως φαίνεται παρακάτω):
Τώρα, ενώ υπήρχαν επαναλήψεις των ονομάτων, δεν υπάρχει επανάληψη όταν το όνομα συνδυάζεται με το επίπεδο εξέτασης.
Αυτό διευκολύνει καθώς τώρα μπορείτε να χρησιμοποιήσετε τις τιμές της στήλης βοηθού ως τιμές αναζήτησης.
Εδώ είναι ο τύπος που θα σας δώσει το αποτέλεσμα στο G3: I8.
= VLOOKUP ($ F3 & "|" & G $ 2, $ C $ 2: $ D $ 19,2,0)
Εδώ έχουμε συνδυάσει το όνομα του μαθητή και το επίπεδο εξέτασης για να λάβουμε την τιμή αναζήτησης και χρησιμοποιούμε αυτήν την τιμή αναζήτησης και την ελέγχουμε στη στήλη βοηθού για να λάβουμε την αντίστοιχη εγγραφή.
Σημείωση: Στο παραπάνω παράδειγμα, χρησιμοποιήσαμε | ως διαχωριστικό ενώ συνδέετε κείμενο στη στήλη βοηθού. Σε ορισμένες εξαιρετικά σπάνιες (αλλά πιθανές) συνθήκες, μπορεί να έχετε δύο κριτήρια που είναι διαφορετικά αλλά καταλήγουν να δίνουν το ίδιο αποτέλεσμα όταν συνδυάζονται. Εδώ είναι ένα παράδειγμα:
Σημειώστε ότι ενώ τα Α2 και Α3 είναι διαφορετικά και τα Β2 και Β3 διαφορετικά, οι συνδυασμοί καταλήγουν να είναι οι ίδιοι. Αλλά εάν χρησιμοποιείτε διαχωριστικό, τότε ακόμη και ο συνδυασμός θα είναι διαφορετικός (D2 και D3).
Ακολουθεί ένα σεμινάριο για τον τρόπο χρήσης του VLOOKUP με πολλαπλά κριτήρια χωρίς τη χρήση στηλών βοηθού. Μπορείτε επίσης να παρακολουθήσετε το σεμινάριο βίντεο μου εδώ.
Παράδειγμα 10 - Χειρισμός σφαλμάτων κατά τη χρήση της λειτουργίας VLOOKUP
Η συνάρτηση Excel VLOOKUP επιστρέφει σφάλμα όταν δεν μπορεί να βρει την καθορισμένη τιμή αναζήτησης. Μπορεί να μην θέλετε η άσχημη τιμή σφάλματος να διαταράσσει την αισθητική των δεδομένων σας σε περίπτωση που το VLOOKUP δεν μπορεί να βρει μια τιμή.
Μπορείτε εύκολα να καταργήσετε τις τιμές σφάλματος με οποιοδήποτε νόημα πλήρες κείμενο, όπως "Δεν είναι διαθέσιμο" ή "Δεν βρέθηκε".
Για παράδειγμα, στο παρακάτω παράδειγμα, όταν προσπαθείτε να βρείτε τη βαθμολογία του Brad στη λίστα, επιστρέφει ένα σφάλμα καθώς το όνομα του Brad δεν είναι εκεί στη λίστα.
Για να καταργήσετε αυτό το σφάλμα και να το αντικαταστήσετε με κάτι ουσιαστικό, τυλίξτε τη συνάρτηση VLOOKUP μέσα στη συνάρτηση IFERROR.
Εδώ είναι ο τύπος:
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0), "Δεν βρέθηκε")
Η συνάρτηση IFERROR ελέγχει εάν η τιμή που επιστρέφεται από το πρώτο όρισμα (που είναι η συνάρτηση VLOOKUP σε αυτήν την περίπτωση) είναι σφάλμα ή όχι. Εάν δεν πρόκειται για σφάλμα, επιστρέφει την τιμή με τη συνάρτηση VLOOKUP, αλλιώς επιστρέφει Δεν βρέθηκε.
Η λειτουργία IFERROR είναι διαθέσιμη από το Excel 2007 και μετά. Εάν χρησιμοποιείτε εκδόσεις πριν από αυτό, χρησιμοποιήστε την ακόλουθη λειτουργία:
= IF (ISERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0))), "Not Found", VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0))
Δείτε επίσης: Πώς να χειριστείτε τα σφάλματα VLOOKUP στο Excel.
Αυτό είναι σε αυτό το σεμινάριο VLOOKUP.
Προσπάθησα να καλύψω σημαντικά παραδείγματα χρήσης της συνάρτησης Vlookup στο Excel. Εάν θέλετε να δείτε περισσότερα παραδείγματα που προστίθενται σε αυτήν τη λίστα, ενημερώστε με στην ενότητα σχολίων.
Σημείωση: Προσπάθησα με κάθε τρόπο να διορθώσω αυτό το σεμινάριο, αλλά σε περίπτωση που βρείτε σφάλματα ή ορθογραφικά λάθη, ενημερώστε με 🙂
Χρησιμοποιώντας Λειτουργία VLOOKUP στο Excel - Βίντεο
- Λειτουργία Excel HLOOKUP.
- Λειτουργία Excel XLOOKUP
- Λειτουργία INDEX του Excel.
- Excel INDIRECT συνάρτηση.
- Λειτουργία MATCH του Excel.
- Λειτουργία Excel OFFSET.
