10 Παραδείγματα VLOOKUP για αρχάριους και προχωρημένους χρήστες

Η συνάρτηση VLOOKUP είναι το σημείο αναφοράς.

Εάν γνωρίζετε πώς να χρησιμοποιείτε τη συνάρτηση VLOOKUP, γνωρίζετε κάτι για το Excel.

Εάν δεν το κάνετε, καλύτερα να μην αναφέρετε το Excel ως ένα από τα δυνατά σας σημεία στο βιογραφικό σας.

Έκανα μια ομαδική συνέντευξη και μόλις ένας υποψήφιος ανέφερε το Excel ως το πεδίο ειδικότητάς του, το πρώτο πράγμα που ρωτήθηκε ήταν - καταλαβαίνετε - η δυνατότητα VLOOKUP.

Τώρα που γνωρίζουμε τη σημασία αυτής της συνάρτησης του Excel, είναι λογικό να την κατανοήσουμε πλήρως, ώστε να μπορούμε να πούμε περήφανα - "Ξέρω ένα ή δύο πράγματα στο Excel".

Αυτό θα είναι ένα τεράστιο σεμινάριο VLOOKUP (με τα δικά μου πρότυπα).

Θα καλύψω τα πάντα και στη συνέχεια θα σας δείξω ένα χρήσιμο και πρακτικό παράδειγμα του VLOOKUP.

Πότε να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel;

Η συνάρτηση VLOOKUP λειτουργεί καλύτερα όταν ψάχνετε για αντίστοιχα σημεία δεδομένων σε μια στήλη και όταν βρεθεί ένα αντίστοιχο σημείο δεδομένων, παίρνετε τη στήλη στα δεξιά της σειράς και λαμβάνετε την τιμή από τον καθορισμένο αριθμό κελιών.

Ας πάρουμε ένα απλό παράδειγμα εδώ για να καταλάβουμε πότε να χρησιμοποιήσετε το Vlookup στο Excel.

Θυμηθείτε, όλοι συνήθιζαν να αναζητούν μανιωδώς τα ονόματά τους και τις βαθμολογίες τους όταν οι βαθμολογίες των τεστ έβγαιναν και κολλούσαν στους πίνακες ανακοινώσεων (τουλάχιστον αυτό συνέβαινε πολύ όταν ήμουν στο σχολείο).

Ετσι δουλευει:

• Πηγαίνετε στον πίνακα ανακοινώσεων και αρχίζετε να αναζητάτε το όνομά σας ή τον αριθμό εγγραφής σας (κινήστε το δάχτυλό σας πάνω-κάτω στη λίστα).
• Μόλις βρείτε το όνομά σας, θα μετακινήσετε τα μάτια σας στα δεξιά του ονόματος/του αριθμού εγγραφής για να δείτε τη βαθμολογία σας.

Αυτό ακριβώς κάνει για εσάς η δυνατότητα Excel VLOOKUP (μη διστάσετε να χρησιμοποιήσετε αυτό το παράδειγμα στην επόμενη συνέντευξή σας).

Η συνάρτηση VLOOKUP αναζητά μια καθορισμένη τιμή σε μια στήλη (στο παραπάνω παράδειγμα, είναι το όνομά σας) και όταν βρει μια καθορισμένη αντιστοίχιση, επιστρέφει μια τιμή (το διακριτικό που λαμβάνετε) στην ίδια σειρά.

σύνταξη

=VLOOKUP(τιμή_αναζήτησης, πίνακας_πίνακας, αριθμός_ευρετηρίου, [αναζήτηση_περιοχής])

Παράμετροι εισαγωγής

• αναζήτηση_τιμής –Αυτή είναι η τιμή αναζήτησης που προσπαθείτε να βρείτε στην πιο αριστερή στήλη του πίνακα.Μπορεί να είναι μια τιμή, μια αναφορά κελιού ή μια συμβολοσειρά κειμένου.Στο παράδειγμα του φύλλου αποτελεσμάτων, αυτό θα ήταν το όνομά σας.
• πίνακα_συστοιχίας -Αυτός είναι ο πίνακας των πινάκων στους οποίους αναζητάτε τιμές.Αυτό μπορεί να είναι μια αναφορά σε μια περιοχή κελιών ή μια περιοχή με όνομα.Στο παράδειγμα του πίνακα βαθμολογίας, αυτός θα ήταν ολόκληρος ο πίνακας που θα περιέχει τις βαθμολογίες όλων για κάθε θέμα
• col_index -Αυτός είναι ο αριθμός ευρετηρίου στήλης από τον οποίο θέλετε να λάβετε τιμές που ταιριάζουν.Στο παράδειγμα του πίνακα κλασμάτων, εάν θέλετε το κλάσμα των μαθηματικών (που είναι η πρώτη στήλη στον πίνακα που περιέχει το κλάσμα), μπορείτε να δείτε τη στήλη 1.Εάν θέλετε μια φυσική βαθμολογία, μπορείτε να δείτε τις στήλες 1 και 2.
• [range_lookup] -Εδώ μπορείτε να καθορίσετε εάν θέλετε μια ακριβή αντιστοίχιση ή μια κατά προσέγγιση αντιστοίχιση.Εάν παραλειφθεί, η προεπιλογή είναι TRUE - κατά προσέγγιση αντιστοίχιση(βλ. πρόσθετες σημειώσεις παρακάτω).

Πρόσθετες σημειώσεις (βαρετό, αλλά σημαντικό να γνωρίζετε)

• Οι αντιστοιχίσεις μπορεί να είναι ακριβείς (FALSE ή 0 στο range_lookup) ή κατά προσέγγιση (TRUE ή 1).
• Σε μια κατά προσέγγιση αναζήτηση, βεβαιωθείτε ότι η λίστα είναι ταξινομημένη με αύξουσα σειρά (από πάνω προς τα κάτω), διαφορετικά τα αποτελέσματα μπορεί να είναι ανακριβή.
• Όταν το range_lookup είναι TRUE (κατά προσέγγιση αναζήτηση) και τα δεδομένα ταξινομούνται με αύξουσα σειρά:
  • Εάν η συνάρτηση VLOOKUP δεν μπορεί να βρει την τιμή, επιστρέφει τη μεγαλύτερη τιμή μικρότερη από την τιμή lookup_value.
  • Επιστρέφει σφάλμα #N/A εάν η τιμή_αναζήτησης είναι μικρότερη από την ελάχιστη τιμή.
  • Εάν το lookup_value είναι κείμενο, μπορούν να χρησιμοποιηθούν χαρακτήρες μπαλαντέρ (βλ. παράδειγμα παρακάτω).

Τώρα, ελπίζουμε ότι έχετε μια βασική κατανόηση του τι μπορεί να κάνει η συνάρτηση VLOOKUP, ας ξεφλουδίσουμε το κρεμμύδι και ας δούμε μερικά πρακτικά παραδείγματα της συνάρτησης VLOOKUP.

10 Παραδείγματα VLOOKUP του Excel (Βασικό και Προχωρημένο)

Ακολουθούν 10 χρήσιμα παραδείγματα χρήσης του Excel Vlookup για να σας δείξουν πώς να το χρησιμοποιείτε στην καθημερινή σας εργασία.

Παράδειγμα 1 - Βρείτε τη βαθμολογία μαθηματικών του Brad

Στο παρακάτω παράδειγμα VLOOKUP, έχω μια λίστα με ονόματα μαθητών στην αριστερή στήλη και βαθμούς για διαφορετικά θέματα στις στήλες Β έως Ε.

Τώρα ας πιάσουμε τη δουλειά και ας χρησιμοποιήσουμε τη συνάρτηση VLOOKUP για να κάνουμε αυτό που κάνει καλύτερα.Από τα παραπάνω δεδομένα, πρέπει να μάθω τι σημείωσε ο Μπραντ στα μαθηματικά.

Από τα παραπάνω δεδομένα, πρέπει να μάθω τι σημείωσε ο Μπραντ στα μαθηματικά.

Ακολουθεί ο τύπος VLOOKUP που επιστρέφει τη βαθμολογία μαθηματικών του Μπραντ:

=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("Maria",$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.Έτσι, εάν χρησιμοποιείτε Math, θα επιστρέψει 2 επειδή τα Math βρίσκονται στο B2 (που είναι το δεύτερο κελί στην καθορισμένη περιοχή πίνακα).

Παράδειγμα 3 - Χρήση αναπτυσσόμενου μενού ως τιμή αναζήτησης

Στο παραπάνω παράδειγμα, πρέπει να εισάγουμε τα δεδομένα χειροκίνητα.Αυτό μπορεί να είναι χρονοβόρο και επιρρεπές σε σφάλματα, ειδικά εάν έχετε πολλές τιμές αναζήτησης.

Μια καλή ιδέα σε αυτήν την περίπτωση είναι να δημιουργήσετε μια αναπτυσσόμενη λίστα τιμών αναζήτησης (σε αυτήν την περίπτωση θα μπορούσε να είναι το όνομα και το θέμα του μαθητή) και απλώς να επιλέξετε από τη λίστα.

Ανάλογα με την επιλογή, ο τύπος θα ενημερώσει αυτόματα το αποτέλεσμα.

Οπως φαίνεται παρακάτω:

Αυτό είναι ένα εξαιρετικό στοιχείο του πίνακα εργαλείων, επειδή μπορείτε να έχετε ένα τεράστιο σύνολο δεδομένων με εκατοντάδες μαθητές στο backend, αλλά οι τελικοί χρήστες (ας πούμε οι δάσκαλοι) μπορούν απλώς να πέσει κάτω από το .

Πώς το κάνω αυτό:

Ο τύπος VLOOKUP που χρησιμοποιείται σε αυτό το παράδειγμα είναι ο ίδιος με αυτόν που χρησιμοποιείται στο Παράδειγμα 2.

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Οι τιμές αναζήτησης έχουν μετατραπεί σε αναπτυσσόμενες λίστες.

Ακολουθούν τα βήματα για τη δημιουργία μιας αναπτυσσόμενης λίστας:

• Επιλέξτε το κελί που χρειάζεται μια αναπτυσσόμενη λίστα.Σε αυτό το παράδειγμα, στο G4, χρειαζόμαστε το όνομα μαθητή.
• Μεταβείτε στα Δεδομένα -> Εργαλεία δεδομένων -> Επικύρωση δεδομένων.
• Στην καρτέλα Ρυθμίσεις του διαλόγου Επικύρωση δεδομένων, επιλέξτε Λίστα από την αναπτυσσόμενη λίστα Να επιτρέπεται.
• Στην Πηγή, επιλέξτε $A$3:$A$10
• Κάντε κλικ στο OK.

Θα δείτε τώρα την αναπτυσσόμενη λίστα στο κελί G4.Ομοίως, μπορείτε να δημιουργήσετε ένα στο H3 για ένα θέμα.

Παράδειγμα 4 - Τριμερής αναζήτηση

Τι είναι η τριπλή αναζήτηση;

Στο Παράδειγμα 2, χρησιμοποιήσαμε έναν πίνακα αναζήτησης που περιέχει βαθμολογίες για μαθητές σε διαφορετικά θέματα.Αυτό είναι ένα παράδειγμα αμφίδρομης αναζήτησης επειδή χρησιμοποιούμε δύο μεταβλητές για να λάβουμε τις βαθμολογίες (όνομα μαθητή και όνομα θέματος).

Τώρα, ας πούμε σε ένα χρόνο, ένας μαθητής πρόκειται να δώσει τρία διαφορετικά επίπεδα εξετάσεων, εξετάσεις μονάδας, ενδιάμεσες εξετάσεις και τελικές εξετάσεις (αυτή είναι η εξέταση από τα φοιτητικά μου χρόνια).

Μια τριπλή αναζήτηση θα μπορεί να λάβει τη βαθμολογία ενός μαθητή για ένα συγκεκριμένο θέμα από ένα συγκεκριμένο επίπεδο εξέτασης.

Οπως φαίνεται παρακάτω:

Στο παραπάνω παράδειγμα, η συνάρτηση VLOOKUP μπορεί να αναζητήσει και να επιστρέψει τους βαθμούς των καθορισμένων μαθητών στα καθορισμένα θέματα σε τρεις διαφορετικούς πίνακες (Τεστ Ενοτήτων, Ενδιάμεσοι όροι και Τελικοί).

Εδώ είναι ο τύπος που χρησιμοποιείται στο κελί H4:

=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 του τύπου:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,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 και βλέπει το αναφερόμενο επίπεδο δοκιμής.Εάν πρόκειται για δοκιμή μονάδας, επιστρέφεται το $A$3:$E$7, το οποίο περιέχει τη βαθμολογία δοκιμής μονάδας.Επιστρέφει $A$11:$E$15 εάν πρόκειται για ενδιάμεση εξέταση, διαφορετικά επιστρέφει $A$19:$E$23.

Κάνοντας αυτό, ο πίνακας πίνακα VLOOKUP είναι δυναμικός, καθιστώντας τον αναζήτηση τριών κατευθύνσεων.

Παράδειγμα 5 - Λάβετε την τελευταία τιμή από μια λίστα

Μπορείτε να δημιουργήσετε έναν τύπο VLOOKUP για να λάβετε την τελευταία τιμή σε μια λίστα.

Ο μεγαλύτερος θετικός αριθμός που μπορείτε να χρησιμοποιήσετε στο Excel είναι 9.99999999999999 307 + . Αυτό σημαίνει επίσης ότι ο μεγαλύτερος αριθμός αναζήτησης στον αριθμό VLOOKUP είναι ο ίδιος.

Δεν νομίζω ότι θα χρειαστείτε ποτέ υπολογισμούς που να αφορούν τόσο μεγάλους αριθμούς.Αυτό ακριβώς μπορούμε να χρησιμοποιήσουμε για να πάρουμε τον τελευταίο αριθμό στη λίστα.

Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως το παρακάτω(σε A1:A14), και θέλετε να λάβετε τον τελευταίο αριθμό στη λίστα.

Εδώ είναι ο τύπος που μπορείτε να χρησιμοποιήσετε:

=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE)

Σημειώστε ότι ο παραπάνω τύπος χρησιμοποιεί ένα κατά προσέγγιση αντίστοιχο VLOOKUP  (Σημειώστε το TRUE στο τέλος του τύπου, όχι FALSE ή 0).Επίσης, σημειώστε ότι αυτός ο τύπος VLOOKUP λειτουργεί χωρίς ταξινόμηση της λίστας.

Δείτε πώς λειτουργεί η κατά προσέγγιση συνάρτηση VLOOKUP.Σαρώνει την αριστερή στήλη από πάνω προς τα κάτω.

• Αυτή η τιμή επιστρέφεται εάν βρεθεί ακριβής αντιστοίχιση.
• Εάν βρει μια τιμή υψηλότερη από την τιμή αναζήτησης, επιστρέφει την τιμή στο κελί πάνω από αυτήν.
• Εάν η τιμή αναζήτησης είναι μεγαλύτερη από όλες τις τιμές στη λίστα, επιστρέφεται η τελευταία τιμή.

Στο παραπάνω παράδειγμα, η τρίτη περίπτωση βρίσκεται σε εξέλιξη.

εξαιτίας9.99999999999999 307 +είναι ο μεγαλύτερος αριθμός που μπορεί να χρησιμοποιηθεί στο Excel, επομένως όταν χρησιμοποιείται ως τιμή αναζήτησης, επιστρέφει τον τελευταίο αριθμό στη λίστα.

Ομοίως, μπορείτε επίσης να το χρησιμοποιήσετε για να επιστρέψετε το τελευταίο στοιχείο κειμένου στη λίστα.Εδώ είναι ο τύπος που μπορεί να το κάνει:

=VLOOKUP("zzz",$A$1:$A$8,1, ΑΛΗΘΙΝΗ )

Ακολουθήστε την ίδια λογική.Το Excel εξετάζει όλα τα ονόματα και δεδομένου ότι το zzz θεωρείται μεγαλύτερο από οποιοδήποτε όνομα/κείμενο που ξεκινά με ένα γράμμα πριν το zzz, θα επιστρέψει το τελευταίο στοιχείο στη λίστα.

Παράδειγμα 6 - Μερική αναζήτηση με χρήση χαρακτήρων μπαλαντέρ και VLOOKUP

Οι χαρακτήρες μπαλαντέρ του Excel είναι χρήσιμοι σε πολλές περιπτώσεις.

Είναι αυτό το μαγικό φίλτρο που δίνει στη συνταγή σου υπερδυνάμεις.

Απαιτείται μερική αναζήτηση όταν πρέπει να αναζητήσετε μια τιμή σε μια λίστα και δεν υπάρχει ακριβής αντιστοίχιση.

Για παράδειγμα, ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως το παρακάτω και θέλετε να βρείτε την εταιρεία ABC σε μια λίστα, αλλά η λίστα έχει ABC Ltd αντί για ABC.

Δεν μπορείτε να χρησιμοποιήσετε το ABC ως τιμή αναζήτησης επειδή δεν υπάρχει ακριβής αντιστοίχιση στη στήλη Α.Οι κατά προσέγγιση αντιστοιχίσεις μπορεί επίσης να οδηγήσουν σε λανθασμένα αποτελέσματα και η λίστα πρέπει να ταξινομηθεί με αύξουσα σειρά.

Ωστόσο, μπορείτε να χρησιμοποιήσετε χαρακτήρες μπαλαντέρ στη συνάρτηση 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 στη λίστα Ε.Π.Ε.και ABC Corporation, θα επιστρέψει το πρώτο και θα αγνοήσει τα υπόλοιπα.

Παράδειγμα 7 - Το VLOOKUP επιστρέφει σφάλμα παρά την αντιστοίχιση τιμών αναζήτησης

Θα σας τρελάνει όταν δείτε ότι υπάρχει αντίστοιχη τιμή αναζήτησης και η συνάρτηση VLOOKUP επιστρέφει σφάλμα.

Για παράδειγμα, στην παρακάτω περίπτωση, υπάρχει μια αντιστοίχιση (Ματ), αλλά η συνάρτηση VLOOKUP εξακολουθεί να επιστρέφει ένα σφάλμα.

Τώρα, ενώ μπορούμε να δούμε ότι υπάρχει ένα ταίριασμα, αυτό που δεν μπορούμε να δούμε με γυμνό μάτι είναι ότι μπορεί να υπάρχουν χώροι που οδηγούν ή ακολουθούν.Εάν έχετε αυτά τα επιπλέον κενά πριν, μετά ή μεταξύ της τιμής αναζήτησης, δεν είναι ακριβής αντιστοίχιση.

Αυτό συμβαίνει συνήθως όταν εισάγετε δεδομένα από μια βάση δεδομένων ή λαμβάνετε δεδομένα από κάποιον άλλο.Αυτοί οι προπορευόμενοι χώροι έχουν την τάση να εισχωρούν.

Η λύση εδώ είναι η λειτουργία TRIM.Αφαιρεί τυχόν προπορευόμενα ή υστερούντα κενά ή επιπλέον κενά μεταξύ των λέξεων.

Εδώ είναι η φόρμουλα που θα σας δώσει το σωστό αποτέλεσμα.

=VLOOKUP("Ματ", TRIM($A$2:$A$9),1,0)

Επειδή πρόκειται για τύπο πίνακα, χρησιμοποιήστε Control+Shift+Enter αντί για Enter.

Μια άλλη προσέγγιση μπορεί να είναι να επεξεργαστείτε πρώτα τον πίνακα αναζήτησης με τη συνάρτηση TRIM για να βεβαιωθείτε ότι έχουν φύγει όλα τα επιπλέον κενά και, στη συνέχεια, χρησιμοποιήστε τη συνάρτηση VLOOKUP ως συνήθως.

Παράδειγμα 8 - Εκτέλεση αναζήτησης με διάκριση πεζών-κεφαλαίων

Από προεπιλογή, οι τιμές αναζήτησης στη συνάρτηση VLOOKUP δεν κάνουν διάκριση πεζών-κεφαλαίων.Για παράδειγμα, εάν η τιμή αναζήτησης είναι MATT, matt ή Matt, ισχύει για τη συνάρτηση VLOOKUP.Επιστρέφει την πρώτη τιμή που ταιριάζει ανεξάρτητα από την περίπτωση.

Αλλά εάν θέλετε μια αναζήτηση με διάκριση πεζών-κεφαλαίων, πρέπει να χρησιμοποιήσετε τη συνάρτηση EXACT και τη συνάρτηση VLOOKUP.

Αυτό είναι ένα παράδειγμα:

Όπως μπορείτε να δείτε, τα τρία κελιά έχουν το ίδιο όνομα (σε A2, A4 και A5) αλλά με διαφορετική χρήση κεφαλαίων.Στα δεξιά, έχουμε τρία ονόματα (Ματ, ΜΑΤ και ματ) και τις βαθμολογίες τους στα μαθηματικά.

Η λειτουργία VLOOKUP δεν είναι προς το παρόν εξοπλισμένη για να χειρίζεται τιμές αναζήτησης με διάκριση πεζών-κεφαλαίων.Στο παραπάνω παράδειγμα, επιστρέφει πάντα 38, που είναι η βαθμολογία του Ματ στο Α2.

Για να γίνει διάκριση πεζών-κεφαλαίων, πρέπει να χρησιμοποιήσουμε μια βοηθητική στήλη (όπως φαίνεται παρακάτω):

Για να λάβετε την τιμή στη στήλη βοήθειας, χρησιμοποιήστε τη συνάρτηση =ROW().Θα λάβει μόνο τον αριθμό σειράς στο κελί.

Αφού έχετε τη βοηθητική στήλη, ακολουθεί ο τύπος που δίνει ένα αποτέλεσμα αναζήτησης με διάκριση πεζών-κεφαλαίων.

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

Τώρα ας το αναλύσουμε και ας καταλάβουμε τι κάνει:

• EXACT(E2,$A$2:$A$9) – Αυτό το τμήμα συγκρίνει την τιμή αναζήτησης στο E2 με όλες τις τιμές στο A2:A9.Επιστρέφει έναν πίνακα TRUE/FALSE, όπου το TRUE επιστρέφεται σε περίπτωση ακριβούς αντιστοίχισης.Σε αυτήν την περίπτωση θα επιστρέψει τον ακόλουθο πίνακα: {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• EXACT(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 για κάθε τύπο εξέτασης (μονάδα, ενδιάμεσο και τελικό), πρέπει να δημιουργήσετε μια μοναδική τιμή αναζήτησης.

Αυτό μπορεί να γίνει χρησιμοποιώντας βοηθητικές στήλες.Το πρώτο βήμα είναι να εισαγάγετε μια βοηθητική στήλη στα αριστερά του κλάσματος.

Τώρα, για να δημιουργήσετε ένα μοναδικό προσδιοριστικό για κάθε παρουσία ενός ονόματος, χρησιμοποιήστε τον ακόλουθο τύπο στο 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 δεν μπορεί να βρει μια τιμή, πιθανότατα δεν θέλετε οι άσχημες τιμές σφαλμάτων να παρεμβαίνουν στην αισθητική των δεδομένων σας.

Μπορείτε εύκολα να αφαιρέσετε τις τιμές σφάλματος με πλήρες κείμενο οποιασδήποτε σημασίας, όπως "Δεν είναι διαθέσιμο" ή "Δεν βρέθηκε".

Για παράδειγμα, στο παρακάτω παράδειγμα, όταν προσπαθείτε να βρείτε τη βαθμολογία του Μπραντ στη λίστα, εμφανίζεται ένα σφάλμα επειδή το όνομα του Μπραντ δεν υπάρχει στη λίστα.

Για να αφαιρέσετε αυτό το σφάλμα και να το αντικαταστήσετε με κάτι σημαντικό, τυλίξτε τη συνάρτηση VLOOKUP σε μια συνάρτηση IFERROR.

Εδώ είναι ο τύπος:

=IFERROR(VLOOKUP(D2,$A$2:$B$7,2,0),"Δεν βρέθηκε")

Η συνάρτηση IFERROR ελέγχει εάν η τιμή που επιστρέφεται από το πρώτο όρισμα (σε αυτήν την περίπτωση, η συνάρτηση VLOOKUP) είναι σφάλμα.Εάν δεν είναι σφάλμα, επιστρέψτε την τιμή μέσω της συνάρτησης VLOOKUP, διαφορετικά επιστρέψτε το Not Found.

Η συνάρτηση IFERROR είναι διαθέσιμη από το Excel 2007.Εάν χρησιμοποιείτε προηγούμενη έκδοση, χρησιμοποιήστε τις ακόλουθες λειτουργίες:

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

Αυτό είναι όλο για αυτό το σεμινάριο VLOOKUP.

Προσπαθώ να παρουσιάσω το κύριο παράδειγμα χρήσης της συνάρτησης Vlookup στο Excel.Ενημερώστε με στην ενότητα σχολίων εάν θέλετε να δείτε περισσότερα παραδείγματα να προστίθενται σε αυτήν τη λίστα.

Σχετικές λειτουργίες του Excel:

• Λειτουργία Excel HLOOKUP.
• Λειτουργία Excel XLOOKUP
• Συνάρτηση ευρετηρίου Excel.
• Έμμεση συνάρτηση Excel.
• Συνάρτηση αντιστοίχισης Excel.
• Λειτουργία offset Excel.