Πώς να χρησιμοποιήσετε το VLOOKUP στο Excel

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

Το VLOOKUP είναι μια συνάρτηση Excel . Αυτό το άρθρο θα υποθέσει ότι ο αναγνώστης έχει ήδη κατανόηση των συναρτήσεων του Excel και μπορεί να χρησιμοποιήσει βασικές λειτουργίες όπως SUM, AVERAGE και TODAY. Στην πιο συνηθισμένη χρήση του, το VLOOKUP είναι μια λειτουργία βάσης δεδομένων , που σημαίνει ότι λειτουργεί με πίνακες βάσεων δεδομένων - ή πιο απλά, λίστες πραγμάτων σε ένα φύλλο εργασίας του Excel. Τι είδους πράγματα; Λοιπόν, κάθε είδους πράγμα. Μπορεί να έχετε ένα φύλλο εργασίας που περιέχει μια λίστα υπαλλήλων ή προϊόντων ή πελατών ή CD στη συλλογή CD ή αστέρια στον νυχτερινό ουρανό. Δεν έχει σημασία.

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

Συνήθως οι λίστες όπως αυτές έχουν ένα είδος μοναδικού αναγνωριστικού για κάθε στοιχείο στη λίστα. Σε αυτήν την περίπτωση, το μοναδικό αναγνωριστικό βρίσκεται στη στήλη "Item Code". Σημείωση: Για να λειτουργεί η συνάρτηση VLOOKUP με μια βάση δεδομένων / λίστα, αυτή η λίστα πρέπει να έχει μια στήλη που περιέχει το μοναδικό αναγνωριστικό (ή "κλειδί" ή "ID") και αυτή η στήλη πρέπει να είναι η πρώτη στήλη στον πίνακα . Το δείγμα της βάσης δεδομένων παραπάνω ικανοποιεί αυτό το κριτήριο.

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

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

Στο παραπάνω παράδειγμα, θα εισαγάγατε τη συνάρτηση VLOOKUP σε ένα άλλο υπολογιστικό φύλλο με έναν κωδικό είδους και θα σας επιστρέψει είτε την περιγραφή του αντίστοιχου στοιχείου, την τιμή του είτε τη διαθεσιμότητά του (την ποσότητα "Σε απόθεμα") όπως περιγράφεται στο πρωτότυπο λίστα. Ποιο από αυτά τα στοιχεία θα σας μεταφέρει; Λοιπόν, θα αποφασίσετε αυτό όταν δημιουργείτε τον τύπο.

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

Ας δημιουργήσουμε ένα παράδειγμα αυτού: Ένα πρότυπο τιμολογίου που μπορούμε να επαναχρησιμοποιήσουμε ξανά και ξανά στην πλασματική εταιρεία μας.

Αρχικά ξεκινάμε το Excel και δημιουργούμε ένα κενό τιμολόγιο:

Έτσι λειτουργεί: Το άτομο που χρησιμοποιεί το πρότυπο τιμολογίου θα συμπληρώσει μια σειρά κωδικών στοιχείων στη στήλη "Α" και το σύστημα θα ανακτήσει την περιγραφή και την τιμή κάθε στοιχείου από τη βάση δεδομένων προϊόντων μας. Αυτές οι πληροφορίες θα χρησιμοποιηθούν για τον υπολογισμό του συνόλου της γραμμής για κάθε στοιχείο (υποθέτοντας ότι εισάγουμε μια έγκυρη ποσότητα).

Για να διατηρήσουμε αυτό το παράδειγμα απλό, θα εντοπίσουμε τη βάση δεδομένων προϊόντων σε ξεχωριστό φύλλο στο ίδιο βιβλίο εργασίας:

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

Έτσι, δημιουργήσαμε τη βάση δεδομένων προϊόντων μας, η οποία μοιάζει με αυτήν:

Για να δοκιμάσουμε τον τύπο VLOOKUP που πρόκειται να γράψουμε, εισάγουμε πρώτα έναν έγκυρο κωδικό στοιχείου στο κελί A11 του κενού τιμολογίου μας:

Στη συνέχεια, μετακινούμε το ενεργό κελί στο κελί στο οποίο θέλουμε να αποθηκεύονται πληροφορίες που ανακτώνται από τη βάση δεδομένων από το VLOOKUP. Είναι ενδιαφέρον ότι αυτό είναι το βήμα που οι περισσότεροι κάνουν λάθος. Για να εξηγήσουμε περαιτέρω: Πρόκειται να δημιουργήσουμε έναν τύπο VLOOKUP που θα ανακτήσει την περιγραφή που αντιστοιχεί στον κωδικό στοιχείου στο κελί A11. Πού θέλουμε να τοποθετηθεί αυτή η περιγραφή όταν τη λάβουμε; Στο κελί Β11, φυσικά. Έτσι λοιπόν γράφουμε τον τύπο VLOOKUP: στο κελί B11. Επιλέξτε το κελί B11 τώρα.

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

Εμφανίζεται ένα πλαίσιο που μας επιτρέπει να επιλέξουμε οποιαδήποτε από τις διαθέσιμες λειτουργίες στο Excel.

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

Εμφανίζεται το πλαίσιο Function Arguments , ζητώντας μας όλα τα επιχειρήματα (ή τις παραμέτρους ) που απαιτούνται για την ολοκλήρωση της συνάρτησης VLOOKUP. Μπορείτε να σκεφτείτε αυτό το πλαίσιο ως συνάρτηση που μας θέτει τις ακόλουθες ερωτήσεις:

  1. Τι μοναδικό αναγνωριστικό αναζητάτε στη βάση δεδομένων;
  2. Πού είναι η βάση δεδομένων;
  3. Ποια πληροφορία από τη βάση δεδομένων, που σχετίζεται με το μοναδικό αναγνωριστικό, θέλετε να ανακτήσετε;

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

Θα ολοκληρώσουμε τα ορίσματα με σειρά, από πάνω προς τα κάτω.

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

Κάντε κλικ στο εικονίδιο επιλογής στα δεξιά του πρώτου ορίσματος:

Στη συνέχεια, κάντε κλικ μία φορά στο κελί που περιέχει τον κωδικό είδους (A11) και πατήστε Enter :

Η τιμή του "A11" εισάγεται στο πρώτο όρισμα.

Τώρα πρέπει να εισαγάγουμε μια τιμή για το όρισμα Table_array . Με άλλα λόγια, πρέπει να πούμε στο VLOOKUP πού να βρει τη βάση δεδομένων / λίστα. Κάντε κλικ στο εικονίδιο επιλογής δίπλα στο δεύτερο όρισμα:

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

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

… Και πατήστε Enter . Το εύρος των κελιών που αντιπροσωπεύει τη βάση δεδομένων (σε αυτήν την περίπτωση "'Βάση δεδομένων προϊόντος'! A2: D7") εισάγεται αυτόματα για εμάς στο δεύτερο όρισμα.

Τώρα πρέπει να εισαγάγουμε το τρίτο όρισμα, Col_index_num . Χρησιμοποιούμε αυτό το επιχείρημα για να καθορίσουμε στο VLOOKUP ποια στοιχεία από τη βάση δεδομένων, συσχετίζονται με τον κωδικό είδους μας στο A11, που θέλουμε να μας επιστρέψουν. Σε αυτό το συγκεκριμένο παράδειγμα, επιθυμούμε να μας επιστραφεί η περιγραφή του αντικειμένου . Εάν κοιτάξετε στο φύλλο εργασίας της βάσης δεδομένων, θα παρατηρήσετε ότι η στήλη "Περιγραφή" είναι η δεύτερη στήλη στη βάση δεδομένων. Αυτό σημαίνει ότι πρέπει να εισαγάγουμε μια τιμή "2" στο πλαίσιο Col_index_num :

Είναι σημαντικό να σημειωθεί ότι δεν εισάγουμε ένα "2" εδώ, επειδή η στήλη "Περιγραφή" βρίσκεται στη στήλη Β στο συγκεκριμένο φύλλο εργασίας. Εάν η βάση δεδομένων τυχαίνει να ξεκινά στη στήλη Κ του φύλλου εργασίας, θα εισαγάγαμε ακόμα ένα "2" σε αυτό το πεδίο επειδή η στήλη "Περιγραφή" είναι η δεύτερη στήλη σε σύνολο κελιών που επιλέξαμε κατά τον καθορισμό του "Table_array"

Τέλος, πρέπει να αποφασίσουμε εάν θα εισαγάγουμε μια τιμή στο τελικό όρισμα VLOOKUP, Range_lookup . Το επιχείρημα αυτό απαιτεί είτε μια αληθινή ή ψεύτικη αξία, ή θα πρέπει να μείνει κενό. Όταν χρησιμοποιείτε το VLOOKUP με βάσεις δεδομένων (όπως ισχύει το 90% του χρόνου), ο τρόπος για να αποφασίσετε τι να βάλετε σε αυτό το επιχείρημα μπορεί να θεωρηθεί ως εξής:

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

Εάν η πρώτη στήλη της βάσης δεδομένων δεν έχει ταξινομηθεί ή ταξινομηθεί με φθίνουσα σειρά, τότε πρέπει να εισαγάγετε τιμή false σε αυτό το όρισμα

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

Αυτό είναι! Έχουμε εισαγάγει όλες τις πληροφορίες που απαιτούνται για το VLOOKUP για να επιστρέψουμε την τιμή που χρειαζόμαστε. Κάντε κλικ στο κουμπί OK και παρατηρήστε ότι η περιγραφή που αντιστοιχεί στον κωδικό στοιχείου "R99245" έχει εισαχθεί σωστά στο κελί B11:

Ο τύπος που δημιουργήθηκε για εμάς έχει την εξής μορφή:

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

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

… Και ο τύπος θα μοιάζει με αυτό:

Σημειώστε ότι η μόνη διαφορά μεταξύ των δύο τύπων είναι το τρίτο όρισμα ( Col_index_num ) έχει αλλάξει από "2" σε "3" (επειδή θέλουμε να ανακτηθούν δεδομένα από την 3η στήλη στη βάση δεδομένων).

Εάν αποφασίσαμε να αγοράσουμε 2 από αυτά τα αντικείμενα, θα εισαγάγαμε ένα «2» στο κελί D11. Στη συνέχεια, θα εισαγάγαμε έναν απλό τύπο στο κελί F11 για να πάρουμε το σύνολο της γραμμής:

= D11 * Ε1

… Που μοιάζει με αυτό…

Ολοκλήρωση του προτύπου τιμολογίου

Έχουμε μάθει πολλά για το VLOOKUP μέχρι στιγμής. Στην πραγματικότητα, μάθαμε όλα όσα πρόκειται να μάθουμε σε αυτό το άρθρο. Είναι σημαντικό να σημειωθεί ότι το VLOOKUP μπορεί να χρησιμοποιηθεί σε άλλες περιπτώσεις εκτός από τις βάσεις δεδομένων. Αυτό είναι λιγότερο κοινό και μπορεί να καλυφθεί σε μελλοντικά άρθρα How-To Geek.

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

  1. Θα αφαιρούσαμε το δείγμα κώδικα στοιχείου από το κελί A11 και το "2" από το κελί D11. Αυτό θα κάνει τους νέους τύπους VLOOKUP να εμφανίζουν μηνύματα σφάλματος:



    Μπορούμε να το διορθώσουμε με συνετή χρήση των λειτουργιών IF () και ISBLANK () του Excel . Αλλάζουμε τον τύπο μας από αυτό…      = VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE) ... σε αυτό ... = IF (ISBLANK (A11), '', VLOOKUP (A11, 'Product Database'! A2 : D7,2, FALSE))


  2. Θα αντιγράψαμε τους τύπους στα κελιά B11, E11 και F11 μέχρι το υπόλοιπο των σειρών στοιχείων του τιμολογίου. Σημειώστε ότι εάν το κάνουμε αυτό, οι προκύπτοντες τύποι δεν θα αναφέρονται πλέον σωστά στον πίνακα βάσης δεδομένων. Θα μπορούσαμε να το διορθώσουμε αλλάζοντας τις αναφορές κελιού για τη βάση δεδομένων σε απόλυτες αναφορές κελιού. Εναλλακτικά - και ακόμη καλύτερα - θα μπορούσαμε να δημιουργήσουμε ένα όνομα εύρους για ολόκληρη τη βάση δεδομένων προϊόντων (όπως "Προϊόντα") και να χρησιμοποιήσουμε αυτό το όνομα εύρους αντί για τις αναφορές κελιών. Ο τύπος θα άλλαζε από αυτό…      = IF (ISBLANK (A11), ””, VLOOKUP (A11, “Product Database”! A2: D7,2, FALSE)) … σε αυτό…       = IF (ISBLANK (A11), ”” , VLOOKUP (A11, Προϊόντα, 2, FALSE)) … και μετά αντιγράψτε τους τύπους μέχρι τις υπόλοιπες σειρές του τιμολογίου.
  3. Πιθανότατα να «κλειδώσουμε» τα κελιά που περιέχουν τους τύπους μας (ή μάλλον να ξεκλειδώσουμε τα άλλα κελιά) και στη συνέχεια να προστατεύσουμε το φύλλο εργασίας, προκειμένου να διασφαλίσουμε ότι οι προσεκτικά κατασκευασμένοι τύποι μας δεν αντικαθίστανται κατά λάθος όταν κάποιος έρχεται να συμπληρώσει το τιμολόγιο.
  4. Θα αποθηκεύαμε το αρχείο ως πρότυπο , έτσι ώστε να μπορεί να επαναχρησιμοποιηθεί από όλους στην εταιρεία μας

Εάν αισθανόμασταν πολύ έξυπνοι, θα δημιουργούσαμε μια βάση δεδομένων όλων των πελατών μας σε ένα άλλο φύλλο εργασίας και, στη συνέχεια, χρησιμοποιήσαμε το αναγνωριστικό πελάτη που καταχωρίστηκε στο κελί F5 για να συμπληρώσετε αυτόματα το όνομα και τη διεύθυνση του πελάτη στα κελιά B6, B7 και B8.

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