Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο δείχνει πώς να χρησιμοποιήσετε το Λειτουργία Excel VLOOKUP στο Excel για να αναζητήσετε μια τιμή.
Επισκόπηση λειτουργίας VLOOKUP
Η συνάρτηση VLOOKUP Vlookup σημαίνει κάθετη αναζήτηση. Αναζητά μια τιμή στην αριστερή στήλη ενός πίνακα. Στη συνέχεια, επιστρέφει μια τιμή έναν καθορισμένο αριθμό στηλών στα δεξιά από την τιμή που βρέθηκε. Είναι το ίδιο με το hlookup, εκτός από το ότι αναζητά τιμές κάθετα αντί οριζόντια.
(Παρατηρήστε πώς εμφανίζονται οι εισαγωγές τύπου)
Σύνταξη και επιχειρήματα συνάρτησης VLOOKUP:
| 1 | = VLOOKUP (τιμή_αναζήτησης, πίνακας_σειράς, col_index_num, range_lookup) |
αναζήτηση_αξίας - Η τιμή που θέλετε να αναζητήσετε.
πίνακας_συστοιχίας - Το εύρος δεδομένων που περιέχει τόσο την τιμή που θέλετε να αναζητήσετε όσο και την τιμή που θέλετε να επιστρέψει το vlookup. Η στήλη που περιέχει τις τιμές αναζήτησης πρέπει να είναι η πιο αριστερή στήλη.
col_index_num - Ο αριθμός στήλης της περιοχής δεδομένων, από την οποία θέλετε να επιστρέψετε μια τιμή.
range_lookup - Σωστό ή λάθος. FALSE αναζητά ακριβή αντιστοίχιση. TRUE αναζητά την πλησιέστερη αντιστοίχιση που είναι μικρότερη ή ίση με την τιμή αναζήτησης. Εάν επιλεγεί TRUE, η στήλη αριστερά (η στήλη αναζήτησης) πρέπει να ταξινομηθεί αύξουσα (χαμηλότερη προς υψηλότερη).
Τι είναι η λειτουργία VLOOKUP;
Ως μία από τις παλαιότερες συναρτήσεις στον κόσμο των υπολογιστικών φύλλων, χρησιμοποιείται η συνάρτηση VLOOKUP Vκάθετος Αναζητήσεις. Έχει μερικούς περιορισμούς που συχνά ξεπερνιούνται με άλλες λειτουργίες, όπως INDEX/MATCH. Ωστόσο, έχει το πλεονέκτημα ότι είναι μια ενιαία λειτουργία που μπορεί να κάνει μια αναζήτηση ακριβούς αντιστοίχισης από μόνη της. Τείνει επίσης να είναι μια από τις πρώτες λειτουργίες για τις οποίες μαθαίνουν οι άνθρωποι.
Βασικό παράδειγμα
Ας δούμε ένα δείγμα δεδομένων από ένα βιβλίο βαθμολογίας. Θα ασχοληθούμε με αρκετά παραδείγματα για την εξαγωγή πληροφοριών για συγκεκριμένους μαθητές.
Αν θέλουμε να βρούμε σε ποια τάξη βρίσκεται ο Bob, θα γράφαμε τον τύπο:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, FALSE) |
Σημαντικά πράγματα που πρέπει να θυμόμαστε είναι ότι το στοιχείο που ψάχνουμε (Bob), πρέπει να βρίσκεται στην πρώτη στήλη του εύρους αναζήτησης (A2: C5). Είπαμε στη συνάρτηση ότι θέλουμε να επιστρέψουμε μια τιμή από το 2nd στήλη, η οποία σε αυτή την περίπτωση είναι η στήλη Β. Τέλος, υποδείξαμε ότι θέλουμε να κάνουμε ένα ακριβές ταίριασμα τοποθετώντας το False ως το τελευταίο επιχείρημα. Εδώ, η απάντηση θα είναι "Ανάγνωση".
Πλευρική συμβουλή: Μπορείτε επίσης να χρησιμοποιήσετε τον αριθμό 0 αντί για False ως τελικό όρισμα, καθώς έχουν την ίδια τιμή. Μερικοί άνθρωποι το προτιμούν καθώς είναι πιο γρήγορο να γράφουν. Απλά να ξέρετε ότι και τα δύο είναι αποδεκτά.
Μετατοπισμένα δεδομένα
Για να προσθέσετε κάποια διευκρίνιση στο πρώτο μας παράδειγμα, το στοιχείο αναζήτησης δεν χρειάζεται να βρίσκεται στη στήλη Α του υπολογιστικού φύλλου σας, μόνο στην πρώτη στήλη του εύρους αναζήτησης. Ας χρησιμοποιήσουμε το ίδιο σύνολο δεδομένων:
Τώρα, ας βρούμε τον βαθμό για την τάξη των Επιστημών. Η φόρμουλα μας θα ήταν
| 1 | = VLOOKUP ("Science", B2: C5, 2, FALSE) |
Αυτός εξακολουθεί να είναι έγκυρος τύπος, καθώς η πρώτη στήλη του εύρους αναζήτησής μας είναι η στήλη Β, όπου βρίσκεται ο όρος αναζήτησης "Επιστήμη". Επιστρέφουμε μια τιμή από το 2nd στήλη του εύρους αναζήτησης, η οποία σε αυτή την περίπτωση είναι η στήλη C. Η απάντηση τότε είναι "A-".
Χρήση μπαλαντέρ
Η λειτουργία VLOOKUP υποστηρίζει τη χρήση των μπαλαντέρ «*» και «;» όταν κάνετε αναζητήσεις. Για παράδειγμα, ας πούμε ότι είχαμε ξεχάσει πώς να γράψουμε το όνομα του Φρανκ και απλώς θέλαμε να αναζητήσουμε ένα όνομα που ξεκινά με "F". Θα μπορούσαμε να γράψουμε τον τύπο
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
Αυτό θα μπορούσε να βρει το όνομα Frank στη σειρά 5 και, στη συνέχεια, να επιστρέψει την τιμή από το 2nd σχετική στήλη. Σε αυτή την περίπτωση, η απάντηση θα είναι "Επιστήμη".
Μη ακριβής αντιστοίχιση
Τις περισσότερες φορές, θα θέλετε να βεβαιωθείτε ότι το τελευταίο όρισμα στο VLOOKUP είναι False (ή 0), ώστε να έχετε μια ακριβή αντιστοίχιση. Ωστόσο, υπάρχουν μερικές φορές που μπορεί να ψάχνετε για μια μη ακριβή αντιστοίχιση. Εάν έχετε μια λίστα με ταξινομημένα δεδομένα, μπορείτε επίσης να χρησιμοποιήσετε το VLOOKUP για να επιστρέψετε το αποτέλεσμα για το στοιχείο που είναι είτε το ίδιο είτε το επόμενο μικρότερο. Αυτό χρησιμοποιείται συχνά όταν πρόκειται για αυξανόμενα εύρη αριθμών, όπως σε φορολογικό πίνακα ή επιδόματα προμήθειας.
Ας υποθέσουμε ότι θέλετε να βρείτε τον φορολογικό συντελεστή για ένα εισόδημα που εισάγεται στο κελί D2. Ο τύπος στο D4 μπορεί να είναι:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
Η διαφορά σε αυτόν τον τύπο είναι ότι το τελευταίο μας επιχείρημα είναι "True". Στο συγκεκριμένο μας παράδειγμα, μπορούμε να δούμε ότι όταν οι ατομικές μας εισροές εισοδήματος 45.000 δολαρίων θα έχουν φορολογικό συντελεστή 15%.
Σημείωση: Παρόλο που συνήθως θέλουμε μια ακριβή αντιστοίχιση με το ρήμα ως επιχείρημα, ξεχνάτε να καθορίσετε το 4ου όρισμα σε ένα VLOOKUP, η προεπιλογή είναι True. Αυτό μπορεί να σας προκαλέσει κάποια απροσδόκητα αποτελέσματα, ειδικά όταν ασχολείστε με τιμές κειμένου.
Δυναμική στήλη
Το VLOOKUP απαιτεί να δώσετε ένα επιχείρημα που λέει από ποια στήλη θέλετε να επιστρέψετε μια τιμή, αλλά η περίσταση μπορεί να προκύψει όταν δεν γνωρίζετε πού θα βρίσκεται η στήλη ή θέλετε να επιτρέψετε στο χρήστη σας να αλλάξει από ποια στήλη θα επιστρέψει. Σε αυτές τις περιπτώσεις, μπορεί να είναι χρήσιμο να χρησιμοποιήσετε τη συνάρτηση MATCH για τον προσδιορισμό του αριθμού στήλης.
Ας εξετάσουμε ξανά το παράδειγμα του βιβλίου βαθμολογίας μας, με μερικές εισόδους στα Ε2 και Ε4. Για να λάβουμε τον αριθμό στήλης, θα μπορούσαμε να γράψουμε έναν τύπο του
| 1 | = ΑΓΩΝΑΣ (Ε2, Α1: Γ1, 0) |
Αυτό θα προσπαθήσει να βρει την ακριβή θέση του "Βαθμού" εντός του εύρους A1: C1. Η απάντηση θα είναι 3. Γνωρίζοντας αυτό, μπορούμε να το συνδέσουμε σε μια συνάρτηση VLOOKUP και να γράψουμε έναν τύπο στο Ε6, έτσι:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Έτσι, η συνάρτηση MATCH θα αξιολογηθεί σε 3 και αυτό λέει στο VLOOKUP να επιστρέψει ένα αποτέλεσμα από το 3rd στήλη στην περιοχή A2: C5. Συνολικά, τότε έχουμε το επιθυμητό αποτέλεσμα "C". Ο τύπος μας είναι δυναμικός τώρα στο ότι μπορούμε να αλλάξουμε είτε τη στήλη προς εξέταση είτε το όνομα για αναζήτηση.
Περιορισμοί VLOOKUP
Όπως αναφέρθηκε στην αρχή του άρθρου, η μεγαλύτερη πτώση του VLOOKUP είναι ότι απαιτεί ο όρος αναζήτησης να βρίσκεται στην πιο αριστερή στήλη του εύρους αναζήτησης. Ενώ υπάρχουν μερικά φανταχτερά κόλπα που μπορείτε να κάνετε για να το ξεπεράσετε, η κοινή εναλλακτική λύση είναι να χρησιμοποιήσετε INDEX και MATCH. Αυτός ο συνδυασμός σας δίνει μεγαλύτερη ευελιξία και μερικές φορές μπορεί να είναι ακόμη πιο γρήγορος υπολογισμός.
VLOOKUP στα Υπολογιστικά φύλλα Google
Η συνάρτηση VLOOKUP λειτουργεί ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel:
επιπρόσθετες σημειώσεις
Χρησιμοποιήστε τη συνάρτηση VLOOKUP για να εκτελέσετε μια ΚΑΘΕΤΗ αναζήτηση. Το VLOOKUP αναζητά μια ακριβή αντιστοίχιση (range_lookup = FALSE) ή η πλησιέστερη αντιστοίχιση που είναι ίση ή μικρότερη από την τιμή αναζήτησης (range_lookup = TRUE, αριθμητικές τιμές μόνο) στην πρώτη σειρά του πίνακα_σειρά. Στη συνέχεια επιστρέφει μια αντίστοιχη τιμή, n αριθμό γραμμών κάτω από την αντιστοίχιση.
Όταν χρησιμοποιείτε ένα VLOOKUP για να βρείτε μια ακριβή αντιστοίχιση, πρώτα ορίστε μια αναγνωριστική τιμή που θέλετε να αναζητήσετε ως αναζήτηση_αξίαςΤο Αυτή η αναγνωριστική τιμή μπορεί να είναι ένα SSN, αναγνωριστικό υπαλλήλου, όνομα ή κάποιο άλλο μοναδικό αναγνωριστικό.
Στη συνέχεια, ορίζετε το εύρος (που ονομάζεται πίνακας_συστοιχίας) που περιέχει τα αναγνωριστικά στην επάνω σειρά και οποιεσδήποτε τιμές θέλετε τελικά να αναζητήσετε στις γραμμές κάτω από αυτήν. ΣΗΜΑΝΤΙΚΟ: Τα μοναδικά αναγνωριστικά πρέπει να βρίσκονται στην επάνω σειρά. Εάν δεν είναι, πρέπει είτε να μετακινήσετε τη σειρά στην κορυφή είτε να χρησιμοποιήσετε MATCH / INDEX αντί για το VLOOKUP.
Τρίτον, καθορίστε τον αριθμό σειράς (row_index) απο πίνακας_συστοιχίας που θέλεις να επιστρέψεις. Λάβετε υπόψη ότι η πρώτη σειρά, που περιέχει τα μοναδικά αναγνωριστικά είναι η σειρά 1. Η δεύτερη σειρά είναι η σειρά 2 κ.λπ.
Τέλος, πρέπει να υποδείξετε αν θα αναζητήσετε ακριβή αντιστοίχιση (FALSE) ή πλησιέστερη αντιστοίχιση (TRUE) στο range_lookupΤο Εάν είναι επιλεγμένη η επιλογή ακριβούς αντιστοίχισης και δεν βρεθεί ακριβής αντιστοίχιση, επιστρέφεται σφάλμα (#N/A). Για να εμφανιστεί ο τύπος κενός ή "δεν βρέθηκε" ή οποιαδήποτε άλλη τιμή αντί της τιμής σφάλματος (#N/A) χρησιμοποιήστε τη συνάρτηση IFERROR με το VLOOKUP.
Για να χρησιμοποιήσετε τη συνάρτηση VLOOKUP για να επιστρέψετε ένα κατά προσέγγιση σύνολο αντιστοίχισης: range_lookup = ΑΛΗΘΕΙΑ Αυτή η επιλογή είναι διαθέσιμη μόνο για αριθμητικές τιμές. Οι τιμές πρέπει να ταξινομηθούν με αύξουσα σειρά.
Για να εκτελέσετε μια οριζόντια αναζήτηση, χρησιμοποιήστε τη συνάρτηση HLOOKUP.
Παραδείγματα VLOOKUP σε VBA
Μπορείτε επίσης να χρησιμοποιήσετε τη λειτουργία VLOOKUP στο VBA. Τύπος:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Για τα ορίσματα συνάρτησης (τιμή_αναζήτησης, κ.λπ.), μπορείτε είτε να τα εισαγάγετε απευθείας στη συνάρτηση είτε να ορίσετε μεταβλητές που θα χρησιμοποιηθούν.
Επιστρέψτε στη λίστα όλων των λειτουργιών στο Excel
