Η λειτουργία VLOOKUP. Χρησιμοποιώντας τη λειτουργία VLOOKUP. Excel - VPR

Η εφαρμογή Excel είναι δημοφιλής χάρη στηντην προσβασιμότητα και την απλότητα του, δεδομένου ότι δεν απαιτεί ειδικές γνώσεις και δεξιότητες. Η τραπεζική προβολή της παροχής πληροφοριών είναι κατανοητή σε οποιονδήποτε χρήστη και ένα ευρύ φάσμα εργαλείων, συμπεριλαμβανομένου του "Master of Functions", επιτρέπει οποιονδήποτε χειρισμό και υπολογισμούς με τα παρεχόμενα δεδομένα.

Ένας από τους γνωστούς τύπους Excel είναι η κάθετη προβολή. Η χρήση της λειτουργίας VLOOK με την πρώτη ματιά φαίνεται μάλλον πολύπλοκη, αλλά μόνο στην αρχή.

Πώς λειτουργεί το Excel

Όταν εργάζεστε με τον τύπο VLR, θα πρέπει να ληφθεί υπόψη αυτόαναζητεί την τιμή αναζήτησης μόνο από στήλες, όχι από σειρές. Για να χρησιμοποιήσετε τη λειτουργία, απαιτείται ο ελάχιστος αριθμός στηλών - δύο, το μέγιστο απουσιάζει.

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

Ανάγκη χρήσης

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

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

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

Ως αποτέλεσμα της λειτουργίας VLOOKUP, δημιουργείται ένας νέος πίνακας, στον οποίο ένας συγκεκριμένος διευθυντής επιθυμεί να συγκρίνει γρήγορα το ποσό των πωλήσεών του.

Ο αλγόριθμος για την συμπλήρωση του τύπου

Ο τύπος VLOOK βρίσκεται στην καρτέλα "Οδηγός λειτουργίας" και στην ενότητα "Αναφορές και πίνακες". Ο διάλογος λειτουργίας έχει την ακόλουθη εμφάνιση:

Τα επιχειρήματα στον τύπο αναγράφονται με τη σειρά της ουράς:

• Η τιμή αναζήτησης είναι αυτή που πρέπει να βρει η λειτουργία και οι επιλογές είναι οι τιμές των κυττάρων, η διεύθυνσή της, το όνομα που της δίνεται από τον χειριστή. Στην περίπτωσή μας, αυτό είναι το επώνυμο και το όνομα του διαχειριστή.
• Πίνακας - το εύρος των σειρών και των στηλών στις οποίες γίνεται αναζήτηση στο κριτήριο.
• Ο αριθμός στήλης είναι ο αύξων αριθμός του, στον οποίο βρίσκεται το ποσό των πωλήσεων, δηλαδή το αποτέλεσμα της φόρμουλας.
• Προβολή διαστήματος. Διατηρεί την τιμή FALSE ή TRUE. Και το FALSE επιστρέφει μόνο μια ακριβή αντιστοίχιση, TRUE - σας επιτρέπει να αναζητήσετε μια κατά προσέγγιση τιμή.

Παράδειγμα χρήσης της λειτουργίας

Η λειτουργία VLOOKUP μπορεί να έχει ένα παράδειγμα χρήσηςτα ακόλουθα: κατά την άσκηση επιχειρηματικής δραστηριότητας μιας εμπορικής επιχείρησης στους πίνακες Excel της στήλης Α καταγράφεται το όνομα του προϊόντος και στη στήλη Β την αντίστοιχη τιμή. Για να γράψετε μια πρόταση στη στήλη C, πρέπει να βρείτε το κόστος για ένα συγκεκριμένο προϊόν, το οποίο θέλετε να εξάγετε στη στήλη D.

Ένα καλό παράδειγμα για την οργάνωση ενός πίνακα

Α	Στο	Γ	Δ
προϊόν 1	90	προϊόν 3	60
προϊόν 2	120	προϊόν 1	90
προϊόν 3	60	προϊόν 4	100
προϊόν 4	100	προϊόν 2	120

Ο τύπος που γράφεται στο D θα φαίνεται ως εξής: = VLP (C1, A1: B5, 2; 0), δηλαδή = VLOOK (τιμή στόχος, εύρος δεδομένων του πίνακα, κανονική στήλη, 0). Ως το τέταρτο επιχείρημα, αντί του 0, μπορείτε να χρησιμοποιήσετε το FALSE.

Για να συμπληρώσετε τον πίνακα των προτάσεων, ο τύπος που προκύπτει πρέπει να αντιγραφεί σε ολόκληρη τη στήλη D.

Για να διορθώσετε την περιοχή δεδομένων εργασίας, μπορείτεμε τη βοήθεια απόλυτων αναφορών. Για να γίνει αυτό, οι πινακίδες $ προηγούνται χειροκίνητα πριν από τις αλφαβητικές και αριθμητικές τιμές των διευθύνσεων των άκρα αριστερά και δεξιά κελιά του πίνακα. Στην περίπτωση μας, ο τύπος παίρνει τη μορφή: = ВПР (С1; $ А $ 1: $ В $ 5; 2; 0).

Σφάλματα κατά τη χρήση

Η λειτουργία VLOOKUP δεν λειτουργεί και στη συνέχεια εμφανίζεται ένα μήνυμα στη στήλη εξόδου του αποτελέσματος σφάλματος (# N / A ή # N / A). Αυτό συμβαίνει σε τέτοιες περιπτώσεις:

1. Ο τύπος εισάγεται και η στήλη των απαιτούμενων κριτηρίων δεν συμπληρώνεται (στην περίπτωση αυτή, στήλη C).
2. Η στήλη C έχει μια τιμή που λείπειστη στήλη Α (στην περιοχή αναζήτησης δεδομένων). Για να ελέγξετε τη διαθεσιμότητα της επιθυμητής τιμής, επιλέξτε τη στήλη των κριτηρίων και στο μενού καρτέλας "Επεξεργασία" - "Εύρεση" εισαγάγετε αυτήν την καταχώρηση, ξεκινήστε την αναζήτηση. Εάν το πρόγραμμα δεν το βρει, τότε λείπει.
3. Οι μορφές των κυττάρων των στηλών Α και Γ (τα απαιτούμενα κριτήρια)διαφορετικά, για παράδειγμα, ένα κείμενο, και το άλλο - αριθμητικά. Μπορείτε να αλλάξετε τη μορφή κυψέλης αν μεταβείτε στην επεξεργασία κελιών (F2). Αυτά τα προβλήματα συνήθως προκύπτουν κατά την εισαγωγή δεδομένων από άλλες εφαρμογές. Προκειμένου να αποφευχθούν τέτοια σφάλματα, οι παρακάτω λειτουργίες μπορούν να ενσωματωθούν στον τύπο VLOOK: KEY ή TEXT. Η εκτέλεση αυτών των αλγορίθμων μετατρέπει αυτόματα τη μορφή των κελιών.
4. Υπάρχουν χαρακτήρες ή χώροι που δεν μπορούν να εκτυπωθούν στον κώδικα λειτουργίας. Στη συνέχεια, θα πρέπει να ελέγξετε προσεκτικά τον τύπο για την παρουσία σφαλμάτων εισόδου.
5. Προσδιορίζεται κατά προσέγγιση η αναζήτηση, δηλαδή η τέταρτητο όρισμα της συνάρτησης VLOOK έχει τιμή 1 ή TRUE και ο πίνακας δεν ταξινομείται με αύξουσα τιμή. Στην περίπτωση αυτή, η στήλη των επιδιωκόμενων κριτηρίων πρέπει να ταξινομηθεί σε αύξουσα σειρά.

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

Χαρακτηριστικά χρήσης ως προβολή διαστήματος 1 ή TRUE

Το σφάλμα με τον αριθμό 5 είναι αρκετά κοινό και απεικονίζεται σαφώς στο παρακάτω σχήμα.

Σε αυτό το παράδειγμα, μια λίστα με ονόματα σύμφωνα με την αρίθμησηταξινομημένα όχι κατά αύξουσα σειρά, αλλά κατά φθίνουσα σειρά. Και ως άποψη διαστήματος χρησιμοποιήθηκε το TRUE κριτήριο (1), το οποίο διακόπτει αμέσως την αναζήτηση όταν βρίσκεται μια τιμή μεγαλύτερη από την αναζητούμενη τιμή, έτσι δημιουργείται ένα σφάλμα.

Όταν εφαρμόζετε 1 ή TRUE στο τέταρτο επιχείρημαΠρέπει να βεβαιωθείτε ότι η στήλη με τα απαιτούμενα κριτήρια ταξινομείται με αύξουσα σειρά. Με τη χρήση του 0 ή του FID, αυτή η ανάγκη εξαφανίζεται, αλλά δεν υπάρχει επίσης δυνατότητα σάρωσης διαστήματος.

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

Άλλες αποχρώσεις όταν εργάζεστε με τη λειτουργία VLOOKUP

Για την ευκολία εργασίας με μια τέτοια φόρμουλα, μπορείτε να επικεφαλίσετε το εύρος του πίνακα στον οποίο πραγματοποιείται η αναζήτηση (το δεύτερο επιχείρημα), όπως φαίνεται στην εικόνα.

Σε αυτή την περίπτωση, η περιοχή του πίνακα πωλήσεων έχει δικαίωμα. Για το σκοπό αυτό, επιλέγεται ένας πίνακας, με εξαίρεση τις κεφαλίδες στηλών και ονομάζεται το πεδίο ονόματος (στα αριστερά κάτω από τη γραμμή καρτελών).

Μια άλλη επιλογή - τίτλος - υποδηλώνει την επιλογή μιας σειράς δεδομένων, μετά πηγαίνετε στο μενού "Εισαγωγή" - "Όνομα" - "Αντιστοίχιση".

Για να χρησιμοποιήσετε τα δεδομένα που έχετε τοποθετήσεισε ένα άλλο φύλλο του βιβλίου εργασίας, χρησιμοποιώντας τη λειτουργία VLOOKUP, πρέπει να καθορίσετε τη θέση της περιοχής δεδομένων στο δεύτερο όρισμα του τύπου. Για παράδειγμα, = VPR (Al; List2! $ A $ 1: $ B $ 5; 2; 0), όπου το φύλλο2! - είναι ένας σύνδεσμος προς το απαιτούμενο φύλλο του βιβλίου και $ A $ 1: $ σε $ 5 - η διεύθυνση του εύρους της ανάκτησης δεδομένων.

Ένα παράδειγμα οργάνωσης της εκπαιδευτικής διαδικασίας με το VPR

Είναι πολύ βολικό για το Excel να χρησιμοποιήσει τη λειτουργία VPR όχιμόνο σε επιχειρήσεις που ασχολούνται με το εμπόριο αλλά και σε εκπαιδευτικά ιδρύματα για τη βελτιστοποίηση της διαδικασίας σύγκρισης των μαθητών (μαθητών) με τις αξιολογήσεις τους. Παραδείγματα αυτών των καθηκόντων παρουσιάζονται στα παρακάτω σχήματα.

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

Η λειτουργία VLOOK αντιμετωπίζει τέλεια τη λύση αυτούκαθήκοντα. Στη στήλη "σήματα" G τίτλο καταγράφονται αντίστοιχο τύπο: = CDF (Ε4, Β3: C13, 2, 0). Πρέπει να αντιγραφεί σε ολόκληρη τη στήλη του πίνακα.

Ως αποτέλεσμα, η συνάρτηση VLR θα παράγει εκτιμήσεις που λαμβάνονται από ορισμένους μαθητές.

Ένα παράδειγμα οργάνωσης μιας μηχανής αναζήτησης με VLOOK

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

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

</ p>