Προέχω

Λειτουργία Excel VLOOKUP

Excel Vlookup Function

Λειτουργία Excel VLOOKUPΠερίληψη

Το VLOOKUP είναι μια συνάρτηση Excel για την αναζήτηση δεδομένων σε έναν πίνακα οργανωμένο κάθετα. Το VLOOKUP υποστηρίζει κατά προσέγγιση και ακριβή αντιστοίχιση και μπαλαντέρ (*) για μερικούς αγώνες. Οι τιμές αναζήτησης πρέπει να εμφανίζονται στο πρώτα η στήλη του πίνακα πέρασε στο VLOOKUP.





Σκοπός Αναζήτηση μιας τιμής σε έναν πίνακα με αντιστοίχιση στην πρώτη στήλη Επιστροφή τιμής Η αντιστοιχισμένη τιμή από έναν πίνακα. Σύνταξη = VLOOKUP (τιμή, πίνακας, col_index, [range_lookup]) Ορίσματα
  • αξία - Η τιμή που πρέπει να αναζητήσετε στην πρώτη στήλη ενός πίνακα.
  • τραπέζι - Ο πίνακας από τον οποίο μπορείτε να ανακτήσετε μια τιμή.
  • col_index - Η στήλη στον πίνακα από την οποία μπορείτε να ανακτήσετε μια τιμή.
  • range_lookup - [προαιρετικά] TRUE = κατά προσέγγιση αντιστοίχιση (προεπιλογή). ΛΑΘΟΣ = ακριβής αντιστοίχιση.
Εκδοχή Excel 2003 Σημειώσεις χρήσης

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

Κάθετα δεδομένα | Αριθμοί στηλών | Μόνο σωστό φαίνεται | Τρόποι αντιστοίχισης | Ακριβές ταίριασμα | Κατά προσέγγιση ταίριασμα | Πρώτος Αγώνας | Αγώνας μπαλαντέρ | Αμφίδρομη αναζήτηση | Πολλαπλά Κριτήρια | #N/A Σφάλματα | Βίντεο





Το V είναι για κάθετο

Ο σκοπός του VLOOKUP είναι να λάβει πληροφορίες από έναν πίνακα οργανωμένο ως εξής:

Το VLOOKUP είναι για κάθετα δεδομένα



Χρησιμοποιώντας τον αριθμό παραγγελίας στη στήλη Β ως τιμή αναζήτησης, το VLOOKUP μπορεί να λάβει το αναγνωριστικό πελάτη, το ποσό, το όνομα και την κατάσταση για οποιαδήποτε παραγγελία. Για παράδειγμα, για να λάβετε το όνομα πελάτη για την παραγγελία 1004, ο τύπος είναι:

 
= VLOOKUP (1004,B5:F9,4,FALSE) // returns 'Sue Martin'

Για οριζόντια δεδομένα, μπορείτε να χρησιμοποιήσετε το HLOOKUP , INDEX και MATCH , ή XLOOKUP Το

excel υπό μορφή φόρμουλας υπό όρους βασισμένο σε άλλο κελί

Το VLOOKUP βασίζεται σε αριθμούς στηλών

Όταν χρησιμοποιείτε το VLOOKUP, φανταστείτε ότι κάθε στήλη στο τραπέζι είναι αριθμημένο, ξεκινώντας από τα αριστερά. Για να λάβετε μια τιμή από μια συγκεκριμένη στήλη, δώστε τον κατάλληλο αριθμό ως 'ευρετήριο στήλης'. Για παράδειγμα, το ευρετήριο στήλης για την ανάκτηση του ονόματος παρακάτω είναι 2:

VLOOKUP παράδειγμα ακριβούς αντιστοίχισης

Το επώνυμο και το email μπορούν να ανακτηθούν με τις στήλες 3 και 4:

 
= VLOOKUP (H3,B4:E13,2,FALSE) // first name = VLOOKUP (H3,B4:E13,3,FALSE) // last name = VLOOKUP (H3,B4:E13,4,FALSE) // email address

Το VLOOKUP φαίνεται μόνο σωστά

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

Το VLOOKUP μπορεί να κοιτάξει μόνο προς τα δεξιά

Εάν πρέπει να αναζητήσετε τιμές στα αριστερά, δείτε INDEX και MATCH , ή XLOOKUP Το

Ακριβής και κατά προσέγγιση αντιστοίχιση

Το VLOOKUP διαθέτει δύο τρόπους αντιστοίχισης, ακριβή και κατά προσέγγιση. Το όνομα του ορίσματος που ελέγχει την αντιστοίχιση είναι ' range_lookup '. Αυτό είναι ένα μπερδεμένο όνομα, επειδή φαίνεται ότι έχει να κάνει με αυτό κυτταρικές περιοχές όπως το A1: A10. Στην πραγματικότητα, η λέξη 'εύρος' σε αυτήν την περίπτωση αναφέρεται σε 'εύρος τιμών' - όταν range_lookup είναι ΑΛΗΘΙΝΗ, το VLOOKUP θα ταιριάζει με ένα εύρος τιμών παρά μια ακριβή τιμή. Ένα καλό παράδειγμα αυτού είναι η χρήση VLOOKUP για τον υπολογισμό βαθμών Το

Είναι σημαντικό να το καταλάβουμε αυτό range_lookup προεπιλογή σε TRUE , που σημαίνει ότι το VLOOKUP θα χρησιμοποιήσει κατά προσέγγιση αντιστοίχιση από προεπιλογή, που μπορεί να είναι επικίνδυνο Το Σειρά range_lookup στο FALSE για να επιβληθεί η ακριβής αντιστοίχιση:

 
= VLOOKUP (value, table, col_index) // approximate match (default) = VLOOKUP (value, table, col_index, TRUE) // approximate match = VLOOKUP (value, table, col_index, FALSE) // exact match

Σημείωση: Μπορείτε επίσης να δώσετε μηδέν (0) αντί για FALSE για ακριβή αντιστοίχιση.

Ακριβές ταίριασμα

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

VLOOKUP ακριβής αντιστοίχιση με ταινίες

Ο τύπος στο H6 για εύρεση Ετος , με βάση την ακριβή αντιστοίχιση του τίτλου της ταινίας, είναι:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Κατά προσέγγιση αντιστοίχιση

Σε περιπτώσεις που θέλετε το ταιριάζει καλύτερα , όχι απαραίτητα ένα ακριβές ταίριασμα , θα θέλετε να χρησιμοποιήσετε κατά προσέγγιση λειτουργία. Για παράδειγμα, παρακάτω θέλουμε να αναζητήσουμε ένα ποσοστό προμήθειας στον πίνακα G5: H10. Οι τιμές αναζήτησης προέρχονται από τη στήλη C. Σε αυτό το παράδειγμα, πρέπει να χρησιμοποιήσουμε το VLOOKUP in κατά προσέγγιση αντιστοίχιση λειτουργία, επειδή στις περισσότερες περιπτώσεις δεν θα βρεθεί ποτέ ακριβής αντιστοίχιση. Ο τύπος VLOOKUP στο D5 έχει διαμορφωθεί για να εκτελεί μια κατά προσέγγιση αντιστοίχιση, ορίζοντας το τελευταίο όρισμα σε TRUE:

VLOOKUP κατά προσέγγιση ποσοστό προμήθειας αντιστοίχισης

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

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

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

Πρώτο ματς

Σε περίπτωση διπλών τιμών, το VLOOKUP θα βρει το πρώτο ματς όταν ο τρόπος αντιστοίχισης είναι ακριβής. Στην παρακάτω οθόνη, το VLOOKUP έχει ρυθμιστεί ώστε να βρίσκει την τιμή για το χρώμα «Πράσινο». Υπάρχουν τρεις καταχωρήσεις με το χρώμα Πράσινο και το VLOOKUP επιστρέφει την τιμή για το πρώτα είσοδος, $ 17. Ο τύπος στο κελί F5 είναι:

 
= VLOOKUP (E5,B5:C11,2,FALSE) // returns 17

Το VLOOKUP επιστρέφει τον πρώτο αγώνα

Αγώνας μπαλαντέρ

Η λειτουργία VLOOKUP υποστηρίζει μπαλαντέρ , το οποίο καθιστά δυνατή την εκτέλεση μερικής αντιστοίχισης σε μια τιμή αναζήτησης. Για παράδειγμα, μπορείτε να χρησιμοποιήσετε το VLOOKUP για να ανακτήσετε τιμές από έναν πίνακα αφού πληκτρολογήσετε μόνο μέρος μιας τιμής αναζήτησης. Για να χρησιμοποιήσετε μπαλαντέρ με VLOOKUP, πρέπει να καθορίσετε τη λειτουργία ακριβούς αντιστοίχισης παρέχοντας FALSE ή 0 για το τελευταίο όρισμα, range_lookup Το Ο τύπος στο H7 ανακτά το πρώτο όνομα, 'Michael', αφού πληκτρολογήσετε 'Aya' στο κελί H4:

 
= VLOOKUP ($H&'*',$B:$E4,2,FALSE)

Ταίριασμα μπαλαντέρ VLOOKUP

Διαβάστε περισσότερα λεπτομερή εξήγηση εδώ Το

Αμφίδρομη αναζήτηση

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

 
= VLOOKUP (H4,B5:E13, MATCH (H5,B4:E4,0),0)

VLOOKUP αμφίδρομη αναζήτηση

Για περισσότερες πληροφορίες, δείτε αυτό το παράδειγμα Το

Σημείωση: Γενικά, INDEX και MATCH είναι ένας πιο ευέλικτος τρόπος για να πραγματοποιήστε αμφίδρομες αναζητήσεις Το

Πολλαπλά κριτήρια

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

 
=C5&D5 // helper column

Το VLOOKUP έχει ρυθμιστεί να κάνει το ίδιο πράγμα για να δημιουργήσει μια τιμή αναζήτησης. Ο τύπος στο Η6 είναι:

 
= VLOOKUP (H4&H5,B5:E13,4,0)

VLOOKUP με πολλαπλά κριτήρια

Για λεπτομέρειες, δείτε αυτό το παράδειγμα Το

Σημείωση: INDEX και MATCH και XLOOKUP είναι πιο ισχυροί τρόποι χειρισμού αναζητήσεων βάσει πολλαπλών κριτηρίων.

Σφάλματα VLOOKUP και #N/A

Εάν χρησιμοποιείτε το VLOOKUP αναπόφευκτα θα αντιμετωπίσετε το σφάλμα #N/A. Το σφάλμα #N/A σημαίνει απλώς 'δεν βρέθηκε'. Για παράδειγμα, στην παρακάτω οθόνη, η τιμή αναζήτησης 'Toy Story 2' δεν υπάρχει στον πίνακα αναζήτησης και οι τρεις τύποι VLOOKUP επιστρέφουν #N/A:

VLOOKUP #N/A παράδειγμα σφάλματος

Ένας τρόπος για να «παγιδεύσετε» το σφάλμα NA είναι να χρησιμοποιήσετε το Λειτουργία IFNA σαν αυτό:

VLOOKUP #N/A παράδειγμα σφάλματος - διορθώθηκε

Ο τύπος στο Η6 είναι:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'Not found')

Το μήνυμα μπορεί να προσαρμοστεί κατά βούληση. Για να μην επιστρέψετε τίποτα (δηλ. Να εμφανίσετε ένα κενό αποτέλεσμα) όταν το VLOOKUP επιστρέψει #N/A, μπορείτε να χρησιμοποιήσετε μια κενή συμβολοσειρά όπως αυτή:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'') // no message

Το σφάλμα #N/A είναι χρήσιμο επειδή σας λέει ότι κάτι δεν πάει καλά. Στην πράξη, υπάρχουν πολλοί λόγοι για τους οποίους ενδέχεται να δείτε αυτό το σφάλμα, όπως:

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

Διαβάστε περισσότερα: VLOOKUP χωρίς σφάλματα #N/A

Περισσότερα για το VLOOKUP

Άλλες σημειώσεις

  • Range_lookup ελέγχει αν αξία πρέπει να ταιριάζει ακριβώς ή όχι. Η προεπιλογή είναι TRUE = επιτρέπει μη ακριβή αντιστοίχιση.
  • Σειρά range_lookup to FALSE to απαιτώ μια ακριβή αντιστοίχιση και ΑΛΗΘΙΝΗ σε επιτρέπουν μια μη ακριβή αντιστοίχιση Το
  • Αν range_lookup είναι TRUE (η προεπιλεγμένη ρύθμιση), μια μη ακριβής αντιστοίχιση θα προκαλέσει τη συνάρτηση VLOOKUP να ταιριάζει με την πλησιέστερη τιμή στον πίνακα που είναι ακόμα λιγότερο από αξία Το
  • Πότε range_lookup παραλείπεται, η συνάρτηση VLOOKUP θα επιτρέψει μια μη ακριβή αντιστοίχιση, αλλά θα χρησιμοποιήσει μια ακριβή αντιστοίχιση εάν υπάρχει.
  • Αν range_lookup είναι TRUE (η προεπιλεγμένη ρύθμιση) βεβαιωθείτε ότι οι τιμές αναζήτησης στην πρώτη σειρά του πίνακα έχουν ταξινομηθεί με αύξουσα σειρά. Διαφορετικά, το VLOOKUP μπορεί να επιστρέψει μια εσφαλμένη ή απροσδόκητη τιμή.
  • Αν range_lookup είναι FALSE (απαιτούν ακριβή αντιστοίχιση), τιμές στην πρώτη στήλη του τραπέζι δεν χρειάζεται να ταξινομηθούν.


^