01 από 03
Βρείτε προσεγγιστικές αντιστοιχίες στα δεδομένα με το VLOOKUP του Excel
Πώς λειτουργεί η λειτουργία VLOOKUP
Η λειτουργία VLOOKUP του Excel, που σημαίνει κάθετη αναζήτηση , μπορεί να χρησιμοποιηθεί για την αναζήτηση συγκεκριμένων πληροφοριών που βρίσκονται σε έναν πίνακα δεδομένων ή βάσης δεδομένων.
Το VLOOKUP επιστρέφει κανονικά ένα μόνο πεδίο δεδομένων ως έξοδο του. Πώς γίνεται αυτό είναι:
- Μπορείτε να δώσετε ένα όνομα ή lookup_value που λέει VLOOKUP σε ποια γραμμή ή καταγραφή του πίνακα δεδομένων για να αναζητήσετε τα επιθυμητά δεδομένα
- Παρέχετε τον αριθμό στήλης - γνωστού ως col_index_num - των δεδομένων που αναζητάτε
- Η λειτουργία αναζητά το lookup_value στην πρώτη στήλη του πίνακα δεδομένων
- Στη συνέχεια, το VLOOKUP εντοπίζει και επιστρέφει τις πληροφορίες που αναζητάτε από ένα άλλο πεδίο της ίδιας εγγραφής χρησιμοποιώντας τον αριθμό της στήλης που παρέχεται
Ταξινόμηση των δεδομένων πρώτα
Παρόλο που δεν απαιτείται πάντα, είναι συνήθως καλύτερο να ταξινομείτε πρώτα την περιοχή δεδομένων που αναζητά το VLOOKUP με αύξουσα σειρά χρησιμοποιώντας την πρώτη στήλη του εύρους για το κλειδί ταξινόμησης.
Εάν τα δεδομένα δεν ταξινομηθούν, το VLOOKUP ενδέχεται να επιστρέψει ένα εσφαλμένο αποτέλεσμα.
Η σύνταξη και τα επιχειρήματα της συνάρτησης VLOOKUP
Η σύνταξη μιας συνάρτησης αναφέρεται στη διάταξη της λειτουργίας και περιλαμβάνει το όνομα, τις αγκύλες και τα επιχειρήματα της συνάρτησης.
Η σύνταξη για τη λειτουργία VLOOKUP είναι:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (απαιτείται) η τιμή για αναζήτηση - όπως η ποσότητα που πωλείται στην παραπάνω εικόνα
table_array - (απαιτείται) Αυτός είναι ο πίνακας δεδομένων που αναζητά το VLOOKUP για να βρει τις πληροφορίες που ακολουθείτε.
- Το table_array πρέπει να περιέχει τουλάχιστον δύο στήλες δεδομένων
- Η πρώτη στήλη περιέχει συνήθως το lookup_value
col_index_num - (απαιτείται) ο αριθμός στήλης της τιμής που θέλετε.
- Η αρίθμηση ξεκινά με τη στήλη search_key ως στήλη 1
- Αν το col_index_num έχει οριστεί σε αριθμό μεγαλύτερο από τον αριθμό των στηλών που έχουν επιλεγεί στο όρισμα table_array a #REF! σφάλμα επιστρέφεται από τη λειτουργία
range_lookup - (προαιρετικά) υποδεικνύει εάν η περιοχή ταξινομείται με αύξουσα σειρά.
- Τα δεδομένα στην πρώτη στήλη χρησιμοποιούνται ως κλειδί ταξινόμησης
- Μια τιμή Boolean - TRUE ή FALSE είναι οι μόνες αποδεκτές τιμές
- Αν παραλειφθεί, η τιμή έχει οριστεί στην τιμή TRUE από προεπιλογή
- Αν οριστεί σε TRUE ή παραλειφθεί και η πρώτη στήλη του εύρους δεν ταξινομηθεί με αύξουσα σειρά, μπορεί να προκύψει εσφαλμένο αποτέλεσμα
- Εάν έχει οριστεί στην τιμή TRUE ή παραλειφθεί και δεν υπάρχει ακριβής αντιστοίχιση για την τιμή αναζήτησης , η πλησιέστερη αντιστοίχιση που είναι μικρότερη σε μέγεθος ή τιμή χρησιμοποιείται ως κλειδί αναζήτησης
- Αν είναι ρυθμισμένο σε FALSE, το VLOOKUP δέχεται μόνο μια ακριβή αντιστοίχιση για την _value αναζήτησης . Εάν υπάρχουν πολλές τιμές αντιστοίχισης, επιστρέφεται η πρώτη τιμή αντιστοίχισης
- Εάν έχει οριστεί σε FALSE και δεν υπάρχει τιμή που να ταιριάζει με το κλειδί αναζήτησης , επιστρέφεται ένα σφάλμα # N / A από τη λειτουργία
Παράδειγμα: Βρείτε το επιτόκιο έκπτωσης για την ποσότητα που αγοράσατε
Το παράδειγμα στην παραπάνω εικόνα χρησιμοποιεί τη λειτουργία VLOOKUP για να βρει το προεξοφλητικό επιτόκιο που ποικίλλει ανάλογα με την ποσότητα των αντικειμένων που αγοράσατε.
Το παράδειγμα δείχνει ότι η έκπτωση για την αγορά 19 στοιχείων είναι 2%. Αυτό συμβαίνει επειδή η στήλη Ποσότητα περιέχει εύρη τιμών. Ως αποτέλεσμα, το VLOOKUP δεν μπορεί να βρει ακριβή αντιστοίχιση. Αντ 'αυτού, πρέπει να βρεθεί κατά προσέγγιση αντιστοιχία για να επιστρέψει το σωστό προεξοφλητικό επιτόκιο.
Για να βρείτε προσεγγιστικές αντιστοιχίσεις:
- ταξινομήστε τα δεδομένα στο table_array με αύξουσα σειρά.
- ορίστε το argument_lookup στο TRUE
Στο παράδειγμα, ο ακόλουθος τύπος που περιέχει τη λειτουργία VLOOKUP χρησιμοποιείται για να βρει την έκπτωση για τις ποσότητες αγαθών που αγοράστηκαν.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Ακόμα κι αν αυτός ο τύπος μπορεί απλώς να πληκτρολογηθεί σε ένα φύλλο εργασίας φύλλου, μια άλλη επιλογή, όπως χρησιμοποιείται με τα βήματα που αναφέρονται παρακάτω, είναι να χρησιμοποιήσετε το παράθυρο διαλόγου της λειτουργίας για να εισαγάγετε τα επιχειρήματά του.
- Η χρήση του πλαισίου διαλόγου συχνά διευκολύνει την σωστή εισαγωγή των παραμέτρων μιας λειτουργίας.
Ανοίξτε το παράθυρο διαλόγου VLOOKUP
Τα βήματα που χρησιμοποιούνται για να εισαγάγετε τη λειτουργία VLOOKUP που φαίνεται στην παραπάνω εικόνα στο κελί B2 είναι:
- Κάντε κλικ στο κελί B2 για να γίνει το ενεργό κελί - η θέση όπου εμφανίζονται τα αποτελέσματα της λειτουργίας VLOOKUP
- Κάντε κλικ στην καρτέλα Φόρμες .
- Επιλέξτε Lookup & Reference από την κορδέλα για να ανοίξετε την αναπτυσσόμενη λίστα λειτουργιών
- Κάντε κλικ στο VLOOKUP στη λίστα για να εμφανιστεί το παράθυρο διαλόγου της λειτουργίας
02 του 03
Εισαγωγή των επιχειρημάτων του Excel VLOOKUP
Υποδείξεις στις αναφορές κυττάρων
Τα επιχειρήματα για τη λειτουργία VLOOKUP εισάγονται σε ξεχωριστές γραμμές του πλαισίου διαλόγου όπως φαίνεται στην παραπάνω εικόνα.
Οι αναφορές κελιών που χρησιμοποιούνται ως επιχειρήματα μπορούν να δακτυλογραφηθούν στη σωστή γραμμή ή, όπως γίνεται στα παρακάτω βήματα, που υποδεικνύουν, τα οποία επισημαίνουν την επιθυμητή περιοχή κυττάρων με τον δείκτη του ποντικιού, μπορούν να χρησιμοποιηθούν για την εισαγωγή τους στο παράθυρο διαλόγου .
Τα πλεονεκτήματα της χρήσης του pointing περιλαμβάνουν:
- Είναι ταχύτερη από την πληκτρολόγηση.
- Λιγότερα λάθη γίνονται εισάγοντας τις σωστές αναφορές κυττάρων.
Χρησιμοποιώντας σχετικές και απόλυτες αναφορές κυττάρων με επιχειρήματα
Δεν είναι ασυνήθιστο να χρησιμοποιείτε πολλαπλά αντίγραφα του VLOOKUP για να επιστρέψετε διαφορετικές πληροφορίες από τον ίδιο πίνακα δεδομένων. Για να γίνει ευκολότερο να γίνει αυτό, συχνά το VLOOKUP μπορεί να αντιγραφεί από το ένα κελί στο άλλο. Όταν οι λειτουργίες αντιγράφονται σε άλλα κελιά, πρέπει να ληφθεί μέριμνα ώστε οι προκύπτουσες αναφορές κυττάρων να είναι σωστές δεδομένης της νέας θέσης της λειτουργίας.
Στην παραπάνω εικόνα, τα σύμβολα του δολαρίου ( $ ) περιβάλλουν τις αναφορές κυττάρων για το όρισμα table_array υποδεικνύοντας ότι είναι απόλυτες αναφορές κυττάρων , πράγμα που σημαίνει ότι δεν θα αλλάξουν αν αντιγραφεί η λειτουργία σε άλλο κελί. Αυτό είναι επιθυμητό καθώς τα πολλαπλά αντίγραφα του VLOOKUP θα αναφέρονται όλοι στον ίδιο πίνακα δεδομένων ως πηγή πληροφοριών.
Η αναφορά κυψελών που χρησιμοποιείται για το lookup_value, από την άλλη πλευρά , δεν περιβάλλεται από σημάδια δολαρίου, γεγονός που την καθιστά μια σχετική αναφορά κυττάρων. Οι σχετικές αναφορές κυττάρων αλλάζουν όταν αντιγράφονται ώστε να αντικατοπτρίζουν τη νέα τους θέση σε σχέση με τη θέση των δεδομένων στα οποία αναφέρονται.
Εισαγωγή των επιχειρημάτων των λειτουργιών
- Κάντε κλικ στη γραμμή Lookup _value στο παράθυρο διαλόγου VLOOKUP
- Κάντε κλικ στο κελί C2 στο φύλλο εργασίας για να εισαγάγετε αυτήν την αναφορά κυττάρου ως το όρισμα search_key
- Κάντε κλικ στη γραμμή Table_array του πλαισίου διαλόγου
- Επισημάνετε τα κελιά C5 έως D8 στο φύλλο εργασίας για να εισαγάγετε αυτήν την περιοχή ως το όρισμα Table_array - οι επικεφαλίδες των πινάκων δεν περιλαμβάνονται
- Πατήστε το πλήκτρο F4 στο πληκτρολόγιο για να αλλάξετε την εμβέλεια σε απόλυτες αναφορές κελιών
- Κάντε κλικ στη γραμμή Col_index_num του παραθύρου διαλόγου
- Πληκτρολογήστε ένα 2 στη γραμμή αυτή ως το επιχείρημα Col_index_num , επειδή οι συντελεστές προεξόφλησης βρίσκονται στη στήλη 2 του argument Table_array
- Κάντε κλικ στη γραμμή Range_lookup του πλαισίου διαλόγου
- Πληκτρολογήστε την αληθινή λέξη ως το όρισμα Range_lookup
- Πατήστε το πλήκτρο Enter στο πληκτρολόγιο για να κλείσετε το παράθυρο διαλόγου και να επιστρέψετε στο φύλλο εργασίας
- Η απάντηση 2% (το προεξοφλητικό επιτόκιο για την αγορασθείσα ποσότητα) θα πρέπει να εμφανίζεται στο κελί D2 του φύλλου εργασίας
- Όταν κάνετε κλικ στο κελί D2, η πλήρης λειτουργία = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) εμφανίζεται στη γραμμή τύπων πάνω από το φύλλο εργασίας
Γιατί η VLOOKUP επέστρεψε το 2% ως αποτέλεσμα
- Στο παράδειγμα, η στήλη Ποσότητα δεν περιέχει ακριβή αντιστοίχιση για την τιμή search_key των 19.
- Εφόσον το όρισμα is_sorted έχει οριστεί σε TRUE, το VLOOKUP θα βρει μια προσέγγιση κατά προσέγγιση με την τιμή search_key .
- Η πλησιέστερη τιμή σε μέγεθος που είναι ακόμα μικρότερη από την τιμή search_key του 19 είναι 11.
- Επομένως, η VLOOKUP αναζητά το ποσοστό έκπτωσης στη σειρά που περιέχει 11 και ως εκ τούτου επιστρέφει προεξοφλητικό επιτόκιο 2%.
03 του 03
Το Excel VLOOKUP δεν λειτουργεί: # N / A και #REF Λάθη
Μηνύματα σφάλματος VLOOKUP
Τα ακόλουθα μηνύματα σφάλματος σχετίζονται με το VLOOKUP.
A # N / A ("τιμή δεν είναι διαθέσιμη") Εμφανίζεται σφάλμα Εάν:
- Η τιμή_αναζήτησης δεν βρίσκεται στην πρώτη στήλη του επιχειρήματος εύρους τιμών
- Το όρισμα Table_array είναι ανακριβές. Για παράδειγμα, το επιχείρημα μπορεί να περιλαμβάνει κενές στήλες στην αριστερή πλευρά του εύρους
- Το όρισμα Range_lookup έχει οριστεί σε FALSE και μια ακριβής αντιστοίχιση για το όρισμα search_key δεν μπορεί να βρεθεί στην πρώτη στήλη του εύρους
- Το όρισμα Range_lookup έχει οριστεί σε TRUE και όλες οι τιμές στην πρώτη στήλη του εύρους είναι μεγαλύτερες από το κλειδί αναζήτησης
Ένα #REF! ("αναφορά εκτός εμβέλειας") Εμφανίζεται σφάλμα Εάν:
- Το όρισμα Col_index_num είναι μεγαλύτερο από τον αριθμό των στηλών στο Table_array