1Aug
VLOOKUP είναι μία από τις πιο χρήσιμες λειτουργίες του Excel και είναι επίσης ένα από τα λιγότερο κατανοητά.Σε αυτό το άρθρο, απομυθοποιούμε το VLOOKUP μέσω ενός πραγματικού παραδείγματος.Θα δημιουργήσουμε ένα χρησιμοποιήσιμο Πρότυπο Τιμολογίου για μια εικονική εταιρεία.Το
VLOOKUP είναι μια λειτουργία του Excel .Αυτό το άρθρο θα υποθέσει ότι ο αναγνώστης έχει ήδη μια διαρκή κατανόηση των λειτουργιών του Excel και μπορεί να χρησιμοποιήσει βασικές λειτουργίες όπως SUM, AVERAGE και TODAY.Στη συνήθη χρήση της, το VLOOKUP είναι μια βάση δεδομένων , που σημαίνει ότι λειτουργεί με πίνακες βάσεων δεδομένων - ή πιο απλά, ο κατάλογος των πραγμάτων σε ένα φύλλο εργασίας του Excel.Τι είδους πράγματα;Λοιπόν, κάθε είδους .Μπορεί να έχετε ένα φύλλο εργασίας που περιέχει μια λίστα υπαλλήλων ή προϊόντων ή πελατών ή CD στη συλλογή CD ή αστέρια στον νυχτερινό ουρανό.Δεν πειράζει πραγματικά.
Ακολουθεί ένα παράδειγμα μιας λίστας ή μιας βάσης δεδομένων.Σε αυτή την περίπτωση είναι μια λίστα προϊόντων που η φανταστική μας εταιρεία πουλάει:
Συνήθως οι κατάλογοι σαν αυτό έχουν κάποιο είδος μοναδικού αναγνωριστικού για κάθε στοιχείο της λίστας.Στην περίπτωση αυτή, ο μοναδικός προσδιοριστής βρίσκεται στη στήλη "Κωδικός στοιχείου".Σημείωση: Για να λειτουργεί η λειτουργία VLOOKUP με μια βάση δεδομένων / λίστα, αυτή η λίστα πρέπει να περιέχει μια στήλη που περιέχει το μοναδικό αναγνωριστικό( ή "κλειδί" ή "ID") και ότι η στήλη πρέπει να είναι η πρώτη στήλη στον πίνακα.Η βάση δεδομένων δείγματος παραπάνω ικανοποιεί αυτό το κριτήριο.
Το πιο δύσκολο μέρος της χρήσης του VLOOKUP είναι η κατανόηση του τι ακριβώς είναι για.Επομένως, ας δούμε αν μπορούμε να έχουμε αυτό το σαφές πρώτο:
VLOOKUP ανακτά πληροφορίες από μια βάση δεδομένων / λίστα με βάση μια παρεχόμενη παρουσία του μοναδικού αναγνωριστικού.
Στο παραπάνω παράδειγμα, θα εισάγετε τη λειτουργία VLOOKUP σε ένα άλλο υπολογιστικό φύλλο με έναν κωδικό στοιχείου και θα σας επιστρέψει είτε την περιγραφή του αντίστοιχου στοιχείου, την τιμή του, ή τη διαθεσιμότητα του( την ποσότητα "In stock") όπως περιγράφεται στοαρχική λίστα.Ποιες από αυτές τις πληροφορίες θα σας μεταφέρουν πίσω;Λοιπόν, μπορείτε να αποφασίσετε αυτό όταν δημιουργείτε τη φόρμουλα.
Αν το μόνο που χρειάζεστε είναι ένα κομμάτι πληροφοριών από τη βάση δεδομένων, θα ήταν πολύ δύσκολο να πάτε για να κατασκευάσετε μια φόρμα με μια λειτουργία VLOOKUP σε αυτήν.Συνήθως θα χρησιμοποιούσατε αυτό το είδος λειτουργικότητας σε ένα επαναχρησιμοποιούμενο υπολογιστικό φύλλο, όπως ένα πρότυπο.Κάθε φορά που κάποιος εισάγει έναν έγκυρο κωδικό είδους, το σύστημα θα ανακτήσει όλες τις απαραίτητες πληροφορίες σχετικά με το αντίστοιχο στοιχείο.
Ας δημιουργήσουμε ένα παράδειγμα αυτού: Πρότυπο τιμολογίου που μπορούμε να ξαναχρησιμοποιούμε ξανά και ξανά στην πλασματική μας εταιρεία.
Αρχικά ξεκινάμε το Excel και δημιουργούμε ένα κενό τιμολόγιο:
Έτσι θα λειτουργήσει: Το άτομο που χρησιμοποιεί το πρότυπο τιμολογίου θα συμπληρώσει μια σειρά κωδικών στοιχείων στη στήλη "A" και το σύστημα θα ανακτήσει τα στοιχεία κάθε στοιχείουπεριγραφή και τιμή από τη βάση δεδομένων του προϊόντος μας.Αυτές οι πληροφορίες θα χρησιμοποιηθούν για τον υπολογισμό του συνολικού αριθμού γραμμών για κάθε στοιχείο( υποθέτοντας ότι εισάγουμε μια έγκυρη ποσότητα).
Για λόγους διατήρησης αυτού του παραδείγματος απλού, θα εντοπίσουμε τη βάση δεδομένων προϊόντος σε ξεχωριστό φύλλο στο ίδιο βιβλίο εργασίας:
Στην πραγματικότητα, είναι πιθανότερο η βάση δεδομένων του προϊόντος να βρίσκεται σε ξεχωριστό βιβλίο εργασίας.Διαφέρει ελάχιστα στη λειτουργία VLOOKUP, η οποία δεν ενδιαφέρει αν η βάση δεδομένων βρίσκεται στο ίδιο φύλλο, σε διαφορετικό φύλλο ή σε ένα εντελώς διαφορετικό βιβλίο εργασίας.
Δημιουργήσαμε τη βάση δεδομένων του προϊόντος μας, η οποία μοιάζει με αυτή:
Για να δοκιμάσουμε τον τύπο VLOOKUP που πρόκειται να γράψουμε, εισάγουμε πρώτα έναν έγκυρο κωδικό στοιχείου στο κελί A11 του κεννού μας τιμολογίου:
Στη συνέχεια, εμείςμετακινήστε το ενεργό κελί στο κελί στο οποίο θέλετε να αποθηκευτούν οι πληροφορίες που ανακτήθηκαν από τη βάση δεδομένων από το VLOOKUP.Είναι ενδιαφέρον ότι αυτό είναι το βήμα που οι περισσότεροι άνθρωποι κάνουν λάθος.Για να εξηγήσουμε περαιτέρω: Πρόκειται να δημιουργήσουμε έναν τύπο VLOOKUP που θα ανακτήσει την περιγραφή που αντιστοιχεί στον κώδικα του στοιχείου στο κελί A11.Πού θέλουμε να περιγράψουμε αυτή την περιγραφή όταν το φτάσουμε;Στο κελί Β11, βέβαια.Γι 'αυτό γράφουμε τον τύπο VLOOKUP: στο κελί B11.Επιλέξτε το στοιχείο Β11 τώρα.
Πρέπει να εντοπίσουμε τη λίστα με όλες τις διαθέσιμες λειτουργίες που πρέπει να προσφέρει το Excel, ώστε να μπορέσουμε να επιλέξουμε το VLOOKUP και να λάβουμε βοήθεια για την συμπλήρωση του τύπου.Αυτό προκύπτει από την πρώτη κλικ στο φόρμες καρτέλα, και στη συνέχεια κάνοντας κλικ Εισαγωγή Λειτουργία :
Εμφανίζεται ένα πλαίσιο που μας επιτρέπει να επιλέξουμε οποιαδήποτε από τις διαθέσιμες λειτουργίες στο Excel.
Για να βρείτε αυτό που ψάχνουμε, θα μπορούσαμε να πληκτρολογήσουμε έναν όρο αναζήτησης όπως "αναζήτηση"( επειδή η λειτουργία που μας ενδιαφέρει είναι μια αναζήτηση λειτουργία).Το σύστημα θα μας επιστρέψει μια λίστα όλων των λειτουργιών που σχετίζονται με την αναζήτηση στο Excel. VLOOKUP Το είναι το δεύτερο στη λίστα.Επιλέξτε το με ένα κλικ OK .
Το παράθυρο Arguments Function εμφανίζεται, ζητώντας μας όλα τα επιχειρήματα ( ή παράμετροι ) που απαιτούνται για να ολοκληρωθεί η λειτουργία VLOOKUP.Μπορείτε να σκεφτείτε αυτό το πλαίσιο ως τη λειτουργία που μας ρωτάει τις ακόλουθες ερωτήσεις:
- Τι μοναδικό αναγνωριστικό αναζητάτε στη βάση δεδομένων;
- Πού είναι η βάση δεδομένων;
- Ποια πληροφορία από τη βάση δεδομένων που σχετίζεται με το μοναδικό αναγνωριστικό, θέλετε να έχετε ανακτήσει για εσάς;
Τα πρώτα τρία επιχειρήματα εμφανίζονται με έντονα , υποδεικνύοντας ότι είναι υποχρεωτικά επιχειρήματα ( η λειτουργία VLOOKUP είναι ελλιπής χωρίς αυτά και δεν θα επιστρέψει μια έγκυρη τιμή).Το τέταρτο επιχείρημα δεν είναι τολμηρό, που σημαίνει ότι είναι προαιρετικό:
Θα ολοκληρώσουμε τα επιχειρήματα με σειρά, από πάνω προς τα κάτω.
Το πρώτο επιχείρημα που πρέπει να ολοκληρώσουμε είναι το όρισμα Lookup_value .Η συνάρτηση μας χρειάζεται να την πούμε πού θα βρεθεί το μοναδικό αναγνωριστικό( ο κωδικός του στοιχείου σε αυτή την περίπτωση) ότι πρέπει να επιστρέψει την περιγραφή του.Πρέπει να επιλέξετε τον κωδικό είδους που καταχωρήσαμε προηγουμένως( στο A11).
Κάντε κλικ στο εικονίδιο επιλογέα στα δεξιά του πρώτου επιχειρήματος:
Στη συνέχεια, κάντε κλικ μία φορά στο κελί που περιέχει τον κωδικό στοιχείου( A11) και πατήστε Εισάγετε :
Η τιμή του "A11" εισάγεται στο πρώτο όρισμα.
Τώρα πρέπει να εισαγάγουμε μια τιμή για το παράθυρο Table_array .Με άλλα λόγια, πρέπει να πούμε στο VLOOKUP πού θα βρει τη βάση δεδομένων / λίστα.Κάντε κλικ στο εικονίδιο επιλογέα δίπλα στο δεύτερο επιχείρημα:
Τώρα εντοπίστε τη βάση δεδομένων / λίστα και επιλέξτε ολόκληρη τη λίστα - που δεν περιλαμβάνει τη γραμμή κεφαλίδας .Στο παράδειγμά μας, η βάση δεδομένων βρίσκεται σε ξεχωριστό φύλλο εργασίας, οπότε πρώτα κάνουμε κλικ στην καρτέλα φύλλου εργασίας:
Στη συνέχεια επιλέγουμε ολόκληρη τη βάση δεδομένων, χωρίς να συμπεριλαμβάνουμε τη γραμμή κεφαλίδας:
. .. και πατήστε Εισάγετε .Το εύρος των κυψελών που αντιπροσωπεύει τη βάση δεδομένων( στην περίπτωση αυτή "Βάση Δεδομένων Προϊόντος!! A2: D7") εισάγεται αυτόματα για εμάς στο δεύτερο επιχείρημα.
Τώρα πρέπει να εισαγάγουμε το τρίτο επιχείρημα, Col_index_num .Χρησιμοποιούμε αυτό το επιχείρημα για να καθορίσουμε στο VLOOKUP ποια πληροφορία από τη βάση δεδομένων, που σχετίζεται με τον κωδικό του στοιχείου μας στο A11, θέλουμε να μας επέστρεψαν.Σε αυτό το συγκεκριμένο παράδειγμα, θα θέλαμε να μας επιστραφεί η περιγραφή του αντικειμένου .Αν κοιτάξετε στο φύλλο εργασίας της βάσης δεδομένων, θα παρατηρήσετε ότι η στήλη "Περιγραφή" είναι η δεύτερη στήλη στη βάση δεδομένων.Αυτό σημαίνει ότι πρέπει να εισαγάγουμε μια τιμή "2" στο πλαίσιο Col_index_num :
Είναι σημαντικό να σημειώσουμε ότι δεν εισάγουμε "2" εδώ επειδή η στήλη "Περιγραφή" βρίσκεται στη στήλη B στοαυτό το φύλλο εργασίας.Αν η βάση δεδομένων αρχίσει να ξεκινάει στη στήλη K του φύλλου εργασίας, θα εισάγουμε ακόμα ένα "2" σε αυτό το πεδίο επειδή η στήλη "Περιγραφή" είναι η δεύτερη στήλη σε σύνολο κελιών που επιλέξαμε όταν καθορίζουμε το "Table_array".
Τέλος, πρέπει να αποφασίσουμε αν θα εισαγάγουμε μια τιμή στο τελικό επιχείρημα VLOOKUP, Range_lookup .Αυτό το επιχείρημα απαιτεί είτε μια τιμή ή ψευδή τιμή , είτε θα πρέπει να παραμείνει κενή.Όταν χρησιμοποιείτε το VLOOKUP με βάσεις δεδομένων( όπως ισχύει το 90% του χρόνου), ο τρόπος για να αποφασίσετε τι να θέσετε σε αυτό το επιχείρημα μπορεί να θεωρηθεί ως εξής:
Εάν η πρώτη στήλη της βάσης δεδομένων( η στήλη που περιέχει τα μοναδικά αναγνωριστικά)ταξινομείται αλφαβητικά / αριθμητικά με αύξουσα σειρά, τότε είναι δυνατό να εισαγάγετε μια τιμή του true σε αυτό το επιχείρημα ή αφήστε το κενό.
Εάν η πρώτη στήλη της βάσης δεδομένων είναι όχι ταξινομημένη ή είναι ταξινομημένη σε φθίνουσα σειρά, τότε πρέπει να εισαγάγει μια τιμή false σε αυτό το επιχείρημα
Καθώς η πρώτη στήλη της βάσης δεδομένων μας είναι όχι ταξινομημένη,εισάγουμε false σε αυτό το επιχείρημα:
Αυτό είναι!Έχουμε εισάγει όλες τις πληροφορίες που απαιτούνται για το VLOOKUP για να επιστρέψουμε την αξία που χρειαζόμαστε.Κάντε κλικ στο κουμπί OK και παρατηρήστε ότι η περιγραφή που αντιστοιχεί στον κωδικό τεμαχίου "R99245" έχει εισαχθεί σωστά στο κελί B11:
Ο τύπος που δημιουργήθηκε για εμάς μοιάζει με αυτόν:
Αν εισάγουμε έναν διαφορετικό κώδικα στοιχείου στο κελίA11, θα αρχίσουμε να βλέπουμε τη δύναμη της λειτουργίας VLOOKUP: Η κυψέλη περιγραφής αλλάζει ώστε να ταιριάζει με τον νέο κωδικό στοιχείου:
Μπορούμε να εκτελέσουμε ένα παρόμοιο σύνολο βημάτων για να πάρουμε την τιμή του στοιχείου που επιστρέφεται στο κελί E11.Σημειώστε ότι ο νέος τύπος πρέπει να δημιουργηθεί στο κελί E11.Το αποτέλεσμα θα μοιάζει με αυτό:
. .. και ο τύπος θα μοιάζει με αυτόν:
Σημειώστε ότι η μόνη διαφορά μεταξύ των δύο τύπων είναι το τρίτο όρισμα( Col_index_num ) έχει αλλάξει από "2" σε "3"θέλουμε τα δεδομένα να ανακτώνται από την 3η στήλη της βάσης δεδομένων).
Εάν αποφασίσαμε να αγοράσουμε 2 από αυτά τα στοιχεία, θα εισάγουμε ένα "2" στο κελί D11.Θα εισάγουμε τότε μια απλή φόρμουλα στο κελί F11 για να πάρουμε τη συνολική γραμμή:
= D11 * E11
. .. που μοιάζει με αυτό. ..
Συμπλήρωση του προτύπου τιμολογίου
Έχουμε μάθει πολλά για το VLOOKUP μέχρι τώρα.Στην πραγματικότητα, έχουμε μάθει όλα όσα πρόκειται να μάθουμε σε αυτό το άρθρο.Είναι σημαντικό να σημειωθεί ότι το VLOOKUP μπορεί να χρησιμοποιηθεί σε άλλες περιστάσεις εκτός από τις βάσεις δεδομένων.Αυτό είναι λιγότερο κοινό, και μπορεί να καλυφθεί στα μελλοντικά άρθρα για το πώς να Geek.
Το πρότυπο τιμολογίου δεν έχει ολοκληρωθεί ακόμα.Για να το ολοκληρώσουμε, θα κάνουμε τα εξής:
- Θα καταργούσαμε τον κώδικα του δείγματος από το κελί A11 και το "2" από το κελί D11.Αυτό θα κάνει τους νεοδημιουργημένους τύπους VLOOKUP να εμφανίζουν μηνύματα σφάλματος:
Μπορούμε να το διορθώσουμε με συνετή χρήση των λειτουργιών IF() και ISBLANK() του Excel.Αλλάζουμε τον τύπο μας από αυτό. .. = VLOOKUP( A11, 'Βάση δεδομένων προϊόντων!! A2: D7,2, FALSE) . .. σε αυτό. .. = IF( ISBLANK( A11), VLOOKUP'A2: D7,2, FALSE))
- Θα αντιγράψαμε τους τύπους στα κελιά B11, E11 και F11 προς τα κάτω στις υπόλοιπες σειρές στοιχείων του τιμολογίου.Σημειώστε ότι αν το κάνουμε αυτό, οι τύποι που προκύπτουν δεν θα αναφέρονται πλέον σωστά στον πίνακα βάσης δεδομένων.Θα μπορούσαμε να διορθώσουμε αυτό, αλλάζοντας τις αναφορές κελιών για τη βάση δεδομένων σε απόλυτες αναφορές κυττάρων .Εναλλακτικά - και ακόμα καλύτερα - θα μπορούσαμε να δημιουργήσουμε ένα όνομα περιοχής για ολόκληρη τη βάση δεδομένων του προϊόντος( όπως "Προϊόντα") και να χρησιμοποιήσετε αυτό το όνομα περιοχής αντί για τις αναφορές κελιών.Ο τύπος θα αλλάξει από αυτό. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, 'Βάση δεδομένων προϊόντος! A2: D7,2, FALSE), "", VLOOKUP( A11, Προϊόντα, 2, FALSE)) . .. και στη συνέχεια αντιγράψτε τους τύπους μέχρι τις υπόλοιπες σειρές στοιχείων τιμολογίου.
- Θα μπορούσαμε πιθανώς να «κλειδώσουμε» τα κελιά που περιέχουν τους τύπους μας( ή μάλλον να ξεκλειδώσετε το τα άλλα κελιά) και στη συνέχεια να προστατέψουμε το φύλλο εργασίας,στο τιμολόγιο.
- Θα αποθηκεύσαμε το αρχείο ως πρότυπο , έτσι ώστε να μπορεί να επαναχρησιμοποιηθεί από όλους στην εταιρεία μας
Εάν αισθανόμασταν πραγματικά έξυπνοι, δημιουργήσαμε μια βάση δεδομένων όλων των πελατών μας σε ένα άλλο φύλλο εργασίας και στη συνέχεια χρησιμοποιήσαμε τοαναγνωριστικό πελάτη που έχει εισαχθεί στο κελί F5 για να συμπληρώσει αυτόματα το όνομα και τη διεύθυνση του πελάτη στα κελιά B6, B7 και B8.
Εάν θέλετε να εξασκηθείτε με το VLOOKUP, ή απλά να δείτε το προκείμενο πρότυπο τιμολογίου, μπορείτε να το κατεβάσετε από εδώ.