Η λειτουργία VLOOKUP του Excel χρησιμοποιείται για την εύρεση και επιστροφή πληροφοριών από έναν πίνακα δεδομένων με βάση την τιμή αναζήτησης που επιλέγετε.
Τυπικά, το VLOOKUP απαιτεί την τιμή αναζήτησης να βρίσκεται στην αριστερή στήλη του πίνακα δεδομένων και η συνάρτηση επιστρέφει ένα άλλο πεδίο δεδομένων που βρίσκεται στην ίδια γραμμή στα δεξιά αυτής της τιμής.
Ωστόσο, συνδυάζοντας το VLOOKUP με τη λειτουργία CHOOSE, μπορεί να δημιουργηθεί ένας τύπος αριστεράς αναζήτησης που:
- Επιτρέπει την τιμή αναζήτησης να είναι από οποιαδήποτε στήλη στον πίνακα δεδομένων
- Επιστρέφει πληροφορίες που βρίσκονται σε οποιαδήποτε στήλη στα αριστερά της τιμής αναζήτησης
Σημείωση: Αυτές οι οδηγίες ισχύουν για τις εκδόσεις Excel 2019, 2016, 2013, 2010 και Excel για το Office 365.
01 από 05Εκμάθηση: Χρησιμοποιώντας τις λειτουργίες VLOOKUP και CHOOSE σε έναν τύπο αριστερής αναζήτησης
Για να δημιουργήσετε τον τύπο αριστερής αναζήτησης που εμφανίζεται στην εικόνα του παραδείγματος, χρησιμοποιήστε τον τύπο:
= VLOOKUP ($ D $ 2, ΕΠΙΛΕΞΤΕ ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
Σε αυτό το παράδειγμα, ο τύπος καθιστά δυνατή την εύρεση του τμήματος που παρέχεται από τις διάφορες εταιρείες που αναφέρονται στη στήλη 3 του πίνακα δεδομένων.
Η δουλειά της συνάρτησης CHOOSE στον τύπο είναι να εξαπατήσει το VLOOKUP να πιστέψει ότι η στήλη 3 είναι στήλη 1. Ως αποτέλεσμα, το όνομα της εταιρείας μπορεί να χρησιμοποιηθεί ως τιμή αναζήτησης για να βρει το όνομα του μέρους που παρέχεται από κάθε εταιρεία.
Εισαγάγετε τα Δεδομένα Tutorial
- Καταχωρίστε την επικεφαλίδα Προμηθευτής στο κελί D1.
- Καταχωρίστε την επικεφαλίδα Μέρος στο κελί Ε1.
- Καταχωρίστε τον πίνακα των δεδομένων που βλέπετε στην παραπάνω εικόνα στα κελιά D4 έως F9
- Γραμμές 2 και 3 παραμένουν κενά για να ικανοποιήσουν τα κριτήρια αναζήτησης και τον τύπο αριστερού συμβολαίου που δημιουργήθηκε κατά τη διάρκεια αυτού του σεμιναρίου
Ανοίξτε το παράθυρο διαλόγου VLOOKUP
Αν και είναι δυνατό να πληκτρολογήσετε τον τύπο απευθείας στο κελί F1 στο φύλλο εργασίας, πολλοί άνθρωποι δυσκολεύονται με τη σύνταξη του τύπου.
Σε αυτήν την περίπτωση, είναι ασφαλέστερο να χρησιμοποιήσετε το παράθυρο διαλόγου VLOOKUP. Σχεδόν όλες οι λειτουργίες του Excel έχουν ένα παράθυρο διαλόγου που σας επιτρέπει να εισάγετε τα επιχειρήματα της κάθε λειτουργίας σε ξεχωριστή γραμμή.
- Κάντε κλικ στο κελί Ε2 του φύλλου εργασίας. Το E2 είναι η θέση όπου θα εμφανιστούν τα αποτελέσματα του αριστερού τύπου φόρμουλα αναζήτησης.
- Κάνε κλικ στο ΜΑΘΗΜΑΤΙΚΟΙ τυποι καρτέλα της κορδέλας.
- Κάνε κλικ στο Αναζήτηση & Αναφορά στην κορδέλα για να ανοίξετε την αναπτυσσόμενη λίστα λειτουργιών.
- Κάντε κλικ στο VLOOKUP στη λίστα για να εμφανιστεί το παράθυρο διαλόγου της λειτουργίας.
Εισαγωγή επιχειρημάτων στο παράθυρο διαλόγου VLOOKUP
Τα επιχειρήματα μιας συνάρτησης είναι οι τιμές που χρησιμοποιούνται από τη συνάρτηση για τον υπολογισμό ενός αποτελέσματος.
Στο παράθυρο διαλόγου μιας λειτουργίας, το όνομα κάθε επιχειρήματος βρίσκεται σε ξεχωριστή γραμμή ακολουθούμενη από ένα πεδίο στο οποίο εισάγεται μια τιμή.
Καταχωρίστε τις ακόλουθες τιμές για κάθε ένα από τα επιχειρήματα του VLOOKUP στη σωστή γραμμή του πλαισίου διαλόγου όπως φαίνεται στην εικόνα που συνοδεύει.
Η τιμή αναζήτησης
Η τιμή αναζήτησης είναι το πεδίο πληροφοριών που χρησιμοποιείται για την αναζήτηση στη συστοιχία πίνακα. Το VLOOKUP επιστρέφει ένα άλλο πεδίο δεδομένων από την ίδια σειρά με την τιμή αναζήτησης.
Αυτό το παράδειγμα χρησιμοποιεί αναφορά κυψέλης στη θέση όπου θα εισαχθεί το όνομα της εταιρείας στο φύλλο εργασίας. Το πλεονέκτημα αυτού του γεγονότος είναι ότι καθιστά εύκολη την αλλαγή του ονόματος της εταιρείας χωρίς επεξεργασία της φόρμουλας.
- Κάνε κλικ στο lookup_value γραμμή στο παράθυρο διαλόγου.
- Κάντε κλικ στο κελί D2 για να προσθέσετε αυτήν την αναφορά κελιού στο lookup_value γραμμή.
- Πάτα το F4 κλειδί στο πληκτρολόγιο για να γίνει απόλυτη η αναφορά των κυττάρων - $ D $ 2.
Απόλυτες αναφορές κυττάρων
Οι απόλυτες αναφορές κυψελών χρησιμοποιούνται για την τιμή αναζήτησης και τα όρια πίνακα πινάκων για την αποφυγή σφαλμάτων, αν ο τύπος αναζήτησης είναι αντιγραμμένος σε άλλα κελιά του φύλλου εργασίας.
Εισαγωγή της Λειτουργίας CHOOSE
Το όρισμα πίνακα πίνακα είναι το μπλοκ συνεχόμενων δεδομένων από τα οποία ανακτώνται συγκεκριμένες πληροφορίες.
Συνήθως, το VLOOKUP βλέπει προς τα δεξιά το όρισμα της τιμής αναζήτησης για να βρει δεδομένα στον πίνακα πίνακα. Για να το δούμε αριστερά, το VLOOKUP πρέπει να εξαπατηθεί αναδιατάσσοντας τις στήλες στη συστοιχία πίνακα χρησιμοποιώντας τη λειτουργία CHOOSE.
Στον τύπο αυτό, η λειτουργία CHOOSE ολοκληρώνει δύο εργασίες:
- Δημιουργεί πίνακα πίνακα που έχει πλάτος μόνο δύο στηλών (στήλες D και F).
- Αλλάζει τη σειρά από τα δεξιά προς τα αριστερά των στηλών στη συστοιχία πίνακα έτσι ώστε η στήλη F έρχεται πρώτη και η στήλη D δεύτερη.
Εισαγωγή λειτουργιών
Όταν εισάγετε χειροκίνητα τις λειτουργίες, κάθε ένα από τα επιχειρήματα της λειτουργίας πρέπει να διαχωρίζεται με κόμμα.
- Στο παράθυρο διαλόγου VLOOKUP, κάντε κλικ στο Table_array γραμμή.
- Εισάγετε τα ακόλουθα ΕΠΙΛΕΓΩ λειτουργία:ΕΠΙΛΕΞΤΕ ({1,2}, $ F: $ F, $ D: $ D)
Αριθμός ευρετηρίου στήλης
Κανονικά, ο αριθμός ευρετηρίου στήλης υποδεικνύει ποια στήλη της συστοιχίας πίνακα περιέχει τα δεδομένα που ακολουθείτε. Ωστόσο, σε αυτόν τον τύπο, αναφέρεται στη σειρά των στηλών που ορίζονται από τη λειτουργία CHOOSE.
Η συνάρτηση CHOOSE δημιουργεί μια πίνακα πίνακα που είναι πλάτος δύο στηλών με τη στήλη F να ακολουθείται πρώτα από τη στήλη D. Δεδομένου ότι οι ζητούμενες πληροφορίες - το όνομα μέρους - είναι στη στήλη D, η τιμή του δείκτη του δείκτη της στήλης πρέπει να οριστεί στο 2.
- Κάνε κλικ στο Col_index_num γραμμή στο παράθυρο διαλόγου.
- Τύπος Α 2 σε αυτή τη γραμμή.
Εύρος αναζήτησης
Το όρισμα Range_lookup του VLOOKUP είναι μια λογική τιμή (TRUE ή FALSE μόνο) που υποδεικνύει εάν θέλετε το VLOOKUP να βρει ακριβή ή κατά προσέγγιση αντιστοιχία με την τιμή αναζήτησης.
- Εάν το TRUE ή αν παραληφθεί αυτό το όρισμα, το VLOOKUP επιστρέφει μια ακριβή αντιστοίχιση με την τιμή Lookup_value ή αν δεν βρεθεί ακριβής αντιστοίχιση, το VLOOKUP επιστρέφει την επόμενη μεγαλύτερη τιμή. Για να γίνει αυτό, τα δεδομένα στην πρώτη στήλη του Table_array πρέπει να ταξινομούνται με αύξουσα σειρά.
- Εάν FALSE, VLOOKUP χρησιμοποιεί μόνο μια ακριβή αντιστοιχία στην τιμή Lookup_value. Εάν υπάρχουν δύο ή περισσότερες τιμές στην πρώτη στήλη του Table_array που ταιριάζουν με την τιμή αναζήτησης, χρησιμοποιείται η πρώτη τιμή που βρέθηκε. Εάν δεν βρεθεί ακριβής αντιστοίχιση, επιστρέφεται σφάλμα # N / A.
Σε αυτό το σεμινάριο, δεδομένου ότι αναζητούμε ένα συγκεκριμένο όνομα, το Range_lookup είναι ρυθμισμένο σε Ψευδής έτσι ώστε να επιστραφούν μόνο ακριβείς αντιστοιχίσεις από τον τύπο.
- Κάνε κλικ στο Range_lookup γραμμή στο παράθυρο διαλόγου.
- Πληκτρολογήστε τη λέξη Ψευδής σε αυτή τη γραμμή για να υποδείξουμε ότι θέλουμε το VLOOKUP να επιστρέψει μια ακριβή αντιστοιχία για τα δεδομένα που αναζητούμε.
- Κάντε κλικ Εντάξει για να ολοκληρώσετε το παράθυρο διαλόγου για το αριστερό πλαίσιο αναζήτησης και το κλείσιμο.
- Επειδή δεν έχουμε εισάγει ακόμη το όνομα της εταιρείας στο κελί D2, εμφανίζεται ένα σφάλμα # N / A στο κελί E2.
Επιστροφή δεδομένων με τον τύπο αριστερής αναζήτησης
Για να βρείτε ποιες εταιρείες προμηθεύουν τα εξαρτήματα, πληκτρολογήστε το όνομα μιας εταιρείας σε κελί D2 και πατήστε το ΕΙΣΑΓΩ στο πληκτρολόγιο.
Το όνομα του μέρους εμφανίζεται στο κελί E2.
- Κάντε κλικ στο κελί D2 στο φύλλο εργασίας σας.
- Τύπος Gadgets Plus στο κελί D2 και πατήστε το ΕΙΣΑΓΩ στο πληκτρολόγιο.
- Το κείμενο "Gadgets" - το τμήμα που παρέχεται από την εταιρεία Gadgets Plus - πρέπει να εμφανίζεται στο κελί E2.
Δοκιμάστε περαιτέρω τον τύπο αναζήτησης πληκτρολογώντας άλλα ονόματα εταιρειών στο κελί D2 και το αντίστοιχο όνομα μέρους πρέπει να εμφανίζεται στο κελί E2.
Εάν εμφανιστεί μήνυμα σφάλματος όπως το # N / A στο κελί E2, ελέγξτε για ορθογραφικά λάθη στο κελί D2.
04 του 05Δημιουργία πίνακα πίνακα με δύο στήλες
Η σύνταξη για τη λειτουργία CHOOSE είναι:
= CHOOSE (Αριθμός_καταλόγου, Τιμή1, Τιμή2, … Τιμή254)
Η λειτουργία CHOOSE τυπικά επιστρέφει μία τιμή από τη λίστα τιμών (Value1 σε Value254) με βάση τον αριθμό ευρετηρίου που καταχωρίσατε.
Εάν ο αριθμός ευρετηρίου είναι 1, η συνάρτηση επιστρέφει Value1 από τη λίστα. εάν ο αριθμός ευρετηρίου είναι 2, η συνάρτηση επιστρέφει Value2 από τη λίστα και ούτω καθεξής.
Όταν εισάγονται πολλαπλοί αριθμοί ευρετηρίου, η συνάρτηση επιστρέφει πολλαπλές τιμές σε οποιαδήποτε επιθυμητή σειρά. Η λήψη του CHOOSE για την επιστροφή πολλών τιμών γίνεται με τη δημιουργία ενός πίνακα.
Για να εισαγάγετε έναν πίνακα, περιβάλλετε τους αριθμούς που εισάγονται με σγουρά ή τιράντες. Εισάγονται δύο αριθμοί για τον αριθμό ευρετηρίου:{ 1,2 }.
Θα πρέπει να σημειωθεί ότι το CHOOSE δεν περιορίζεται στη δημιουργία πίνακα δύο στηλών. Με τη συμπερίληψη ενός πρόσθετου αριθμού στη συστοιχία - όπως {1,2,3} - και ενός επιπλέον εύρους στο όρισμα αξίας, το CHOOSE δημιουργεί έναν πίνακα τριών στηλών.
Πρόσθετες στήλες σάς επιτρέπουν να επιστρέψετε διαφορετικές πληροφορίες με τον τύπο του αριστερού στοιχείου αναζήτησης αλλάζοντας το όρισμα του αριθμού ευρετηρίου στήλης του VLOOKUP στον αριθμό της στήλης που περιέχει τις επιθυμητές πληροφορίες.
05 του 05Αλλαγή της σειράς των στηλών με τη λειτουργία CHOOSE
Στη λειτουργία CHOOSE που χρησιμοποιείται σε αυτόν τον τύπο:
ΕΠΙΛΕΞΤΕ ({1,2}, $ F: $ F, $ D: $ D)
το εύρος για τη στήλη F παρατίθεται πριν από τη στήλη D.
Δεδομένου ότι η λειτουργία CHOOSE ορίζει τον πίνακα πίνακα VLOOKUP (την πηγή δεδομένων για τη λειτουργία αυτή) αλλάζοντας τη σειρά των στηλών στη λειτουργία CHOOSE μεταφέρεται στο VLOOKUP.
Όσον αφορά το VLOOKUP, ο πίνακας πίνακας είναι μόνο δύο στήλες πλάτος με τη στήλη F στα αριστερά και τη στήλη D στα δεξιά. Δεδομένου ότι η στήλη F περιέχει το όνομα της εταιρείας που θέλουμε να ψάξουμε και δεδομένου ότι η στήλη D περιέχει τα ονόματα των τμημάτων, το VLOOKUP μπορεί να εκτελέσει τα καθήκοντά του τακτικής αναζήτησης για την εύρεση δεδομένων που βρίσκεται στα αριστερά της τιμής αναζήτησης.
Ως αποτέλεσμα, η VLOOKUP μπορεί να χρησιμοποιήσει το όνομα της εταιρείας για να βρει το τμήμα που παρέχει.
