Βρείτε πολλαπλά πεδία δεδομένων με το Excel VLOOKUP

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

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

01 από 10

Επιστροφή πολλαπλών τιμών με το Excel VLOOKUP

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

Ο τύπος αναζήτησης απαιτεί τη λειτουργία COLUMN να είναι ένθετη μέσα στο VLOOKUP.

Η τοποθέτηση μιας συνάρτησης περιλαμβάνει την εισαγωγή της δεύτερης λειτουργίας ως ένα από τα επιχειρήματα για την πρώτη λειτουργία.

Σε αυτό το σεμινάριο, η συνάρτηση COLUMN θα εισαχθεί ως το όρισμα αριθμού ευρετηρίου στήλης για το VLOOKUP.

Το τελευταίο βήμα στο σεμινάριο περιλαμβάνει την αντιγραφή της φόρμας αναζήτησης σε πρόσθετες στήλες για την ανάκτηση πρόσθετων τιμών για το επιλεγμένο τμήμα.

Περιεχόμενα περιεχομένου

• Εισαγάγετε τα Δεδομένα Tutorial
• Δημιουργία εύρους ονομασίας για τον πίνακα δεδομένων
• Εκκίνηση της λειτουργίας VLOOKUP
• Εισαγωγή του Argument Value Value χρησιμοποιώντας Absolute Cell References
• Εισαγωγή του Argument Array πίνακα
• Εισαγωγή στη λειτουργία Nested COLUMN
• Ολοκλήρωση της λειτουργίας VLOOKUP
• Αντιγραφή της φόρμουλας αναζήτησης με τη λαβή πλήρωσης
• Ανάκτηση δεδομένων με τον τύπο αναζήτησης

02 από 10

Εισαγάγετε τα Δεδομένα Tutorial

Το πρώτο βήμα στο μάθημα είναι να εισαγάγετε τα δεδομένα σε ένα φύλλο εργασίας του Excel.

Για να ακολουθήσετε τα βήματα στο μάθημα εισάγετε τα δεδομένα που εμφανίζονται στην παραπάνω εικόνα στα ακόλουθα κελιά .

• Καταχωρίστε την κορυφαία περιοχή δεδομένων στα κελιά D1 έως G1
• Εισάγετε τη δεύτερη περιοχή στα κελιά D4 έως G10

Τα κριτήρια αναζήτησης και ο τύπος αναζήτησης που δημιουργήθηκε κατά τη διάρκεια αυτού του σεμιναρίου θα εισαχθούν στη σειρά 2 του φύλλου εργασίας.

Το σεμινάριο δεν περιλαμβάνει τη μορφοποίηση που φαίνεται στην εικόνα, αλλά αυτό δεν θα επηρεάσει τον τρόπο με τον οποίο λειτουργεί ο τύπος αναζήτησης.

Πληροφορίες σχετικά με τις επιλογές μορφοποίησης παρόμοιες με εκείνες που βλέπετε παραπάνω είναι διαθέσιμες σε αυτό το Βασικό Εκμάθηση Μορφοποίησης Excel .

Εκπαιδευτικά βήματα

1. Εισάγετε τα δεδομένα όπως φαίνεται στην παραπάνω εικόνα στα κελιά D1 έως G10

03 από 10

Δημιουργία εύρους ονομασίας για τον πίνακα δεδομένων

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

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

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

Σημείωση: Το όνομα εμβέλειας δεν περιλαμβάνει τις επικεφαλίδες ή τα ονόματα πεδίων για τα δεδομένα (γραμμή 4) αλλά μόνο τα ίδια τα δεδομένα.

Εκπαιδευτικά βήματα

1. Επισημάνετε τα κελιά D5 έως G10 στο φύλλο εργασίας για να τα επιλέξετε
2. Κάντε κλικ στο πλαίσιο ονόματος που βρίσκεται πάνω από τη στήλη Α
3. Πληκτρολογήστε "Πίνακας" (χωρίς εισαγωγικά) στο πλαίσιο Όνομα
4. Πατήστε το πλήκτρο ENTER στο πληκτρολόγιο
5. Τα κελιά D5 έως G10 έχουν τώρα το όνομα της περιοχής "Πίνακας". Θα χρησιμοποιήσουμε το όνομα για το όρισμα πίνακα πίνακα VLOOKUP αργότερα στο εκπαιδευτικό πρόγραμμα

04 από 10

Ανοίξτε το παράθυρο διαλόγου VLOOKUP

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

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

Εκπαιδευτικά βήματα

1. Κάντε κλικ στο κελί E2 του φύλλου εργασίας - τη θέση όπου θα εμφανιστούν τα αποτελέσματα της φόρμουλας δύο διαστάσεων αναζήτησης
2. Κάντε κλικ στην καρτέλα Φόρμες της κορδέλας
3. Κάντε κλικ στην επιλογή αναζήτησης και αναφοράς στην κορδέλα για να ανοίξετε την αναπτυσσόμενη λίστα λειτουργιών
4. Κάντε κλικ στο VLOOKUP στη λίστα για να ανοίξετε το παράθυρο διαλόγου της λειτουργίας

05 από 10

Εισαγωγή του Argument Value Value χρησιμοποιώντας Absolute Cell References

Κανονικά, η τιμή αναζήτησης αντιστοιχεί σε ένα πεδίο δεδομένων στην πρώτη στήλη του πίνακα δεδομένων.

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

Οι επιτρεπόμενοι τύποι δεδομένων για την τιμή αναζήτησης είναι:

• δεδομένα κειμένου
• μια λογική τιμή (TRUE ή FALSE μόνο)
• ένας αριθμός
• μια αναφορά κυψέλης σε μια τιμή στο φύλλο εργασίας

Σε αυτό το παράδειγμα, θα εισαγάγουμε την αναφορά κυψέλης όπου θα βρίσκεται το όνομα του μέρους - το κελί D2.

Απόλυτες αναφορές κυττάρων

Σε ένα μεταγενέστερο βήμα στο tutorial, θα αντιγράψουμε τη φόρτωση αναζήτησης στο κελί E2 στα κελιά F2 και G2.

Κανονικά, όταν οι τύποι αντιγράφονται στο Excel, οι αναφορές κελιών αλλάζουν για να αντικατοπτρίζουν τη νέα τους θέση.

Εάν συμβεί αυτό, το D2 - η αναφορά κυψέλης για την τιμή αναζήτησης - θα αλλάξει καθώς αντιγράφεται ο τύπος δημιουργώντας σφάλματα στα κελιά F2 και G2.

Για να αποτρέψουμε τα σφάλματα, θα μετατρέψουμε την αναφορά κυψέλης D2 σε απόλυτη αναφορά κυψέλης .

Οι απόλυτες αναφορές κυψελών δεν αλλάζουν όταν αντιγράφονται οι τύποι.

Απόλυτες αναφορές κυττάρων δημιουργούνται πατώντας το πλήκτρο F4 στο πληκτρολόγιο. Με τον τρόπο αυτό προσθέτει σημάδια δολαρίων γύρω από την αναφορά κυττάρων, όπως $ D $ 2

Εκπαιδευτικά βήματα

1. Κάντε κλικ στη γραμμή lookup_value στο παράθυρο διαλόγου
2. Κάντε κλικ στο κελί D2 για να προσθέσετε αυτήν την αναφορά κελιού στη γραμμή lookup_value . Αυτό είναι το κελί όπου θα πληκτρολογήσουμε το όνομα του μέρους για το οποίο αναζητούμε πληροφορίες
3. Χωρίς να μετακινείτε το σημείο εισαγωγής, πατήστε το πλήκτρο F4 στο πληκτρολόγιο για να μετατρέψετε το D2 στην απόλυτη αναφορά κυττάρων $ D $ 2
4. Αφήστε το παράθυρο διαλόγου λειτουργίας VLOOKUP ανοικτό για το επόμενο βήμα του οδηγού

06 από 10

Εισαγωγή του Argument Array πίνακα

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

Η συστοιχία πίνακα πρέπει να περιέχει τουλάχιστον δύο στήλες δεδομένων .

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

Το όρισμα πίνακας συστοιχιών πρέπει να εισαχθεί είτε ως ένα εύρος που περιέχει τις αναφορές κελιών για τον πίνακα δεδομένων είτε ως όνομα εύρους .

Για αυτό το παράδειγμα, θα χρησιμοποιήσουμε το όνομα εύρους που δημιουργήθηκε στο βήμα 3 του εκπαιδευτικού προγράμματος.

Εκπαιδευτικά βήματα

1. Κάντε κλικ στη γραμμή table_array στο παράθυρο διαλόγου
2. Πληκτρολογήστε "Πίνακας" (χωρίς εισαγωγικά) για να εισαγάγετε το όνομα περιοχής για αυτό το όρισμα
3. Αφήστε το παράθυρο διαλόγου λειτουργίας VLOOKUP ανοικτό για το επόμενο βήμα του οδηγού

07 από 10

Φένοντας τη λειτουργία COLUMN

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

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

Εδώ εισάγεται η συνάρτηση COLUMN. Εισάγοντάς την ως το όρισμα αριθμού ευρετηρίου στήλης , θα αλλάξει καθώς ο τύπος αναζήτησης θα αντιγραφεί από το κελί D2 στα κελιά E2 και F2 αργότερα στο tutorial.

Λειτουργίες φθορισμού

Επομένως, η συνάρτηση COLUMN λειτουργεί ως παράμετρος του αριθμού ευρετηρίου στήλης VLOOKUP.

Αυτό επιτυγχάνεται με την εμφύτευση της συνάρτησης COLUMN μέσα στο VLOOKUP στη γραμμή Col_index_num του πλαισίου διαλόγου.

Εισαγωγή στη λειτουργία COLUMN χειροκίνητα

Όταν λειτουργίες εμφύτευσης, το Excel δεν μας επιτρέπει να ανοίξουμε το παράθυρο διαλόγου δεύτερης λειτουργίας για να εισαγάγουμε τα επιχειρήματά του.

Επομένως, η συνάρτηση COLUMN πρέπει να εισαχθεί με μη αυτόματο τρόπο στη γραμμή Col_index_num .

Η συνάρτηση COLUMN έχει μόνο ένα όρισμα - το όρισμα αναφοράς που είναι μια αναφορά κυψέλης.

Επιλογή του Argument αναφοράς της Λειτουργίας ΣΤΗΛΗ

Η εργασία της συνάρτησης COLUMN είναι να επιστρέψει τον αριθμό της στήλης που δίνεται ως το όρισμα αναφοράς .

Με άλλα λόγια, μετατρέπει το γράμμα της στήλης σε έναν αριθμό με τη στήλη Α την πρώτη στήλη, τη στήλη Β τη δεύτερη και ούτω καθεξής.

Δεδομένου ότι το πρώτο πεδίο δεδομένων που θέλουμε να επιστρέψουμε είναι η τιμή του στοιχείου - το οποίο βρίσκεται στη δεύτερη στήλη του πίνακα δεδομένων - μπορούμε να επιλέξουμε την αναφορά κυψελών για κάθε κελί στη στήλη Β ως Argument αναφοράς για να πάρουμε τον αριθμό 2 για το όρισμα Col_index_num .

Εκπαιδευτικά βήματα

1. Στο παράθυρο διαλόγου VLOOKUP, κάντε κλικ στη γραμμή Col_index_num
2. Πληκτρολογήστε τη στήλη ονομασίας λειτουργίας ακολουθούμενη από ανοιχτό στρογγυλό βραχίονα " ( "
3. Κάντε κλικ στο κελί B1 στο φύλλο εργασίας για να εισαγάγετε αυτήν την αναφορά κυττάρου ως το όρισμα αναφοράς
4. Πληκτρολογήστε ένα στρογγυλό βραχίονα κλεισίματος " ) " για να ολοκληρώσετε τη λειτουργία COLUMN
5. Αφήστε το παράθυρο διαλόγου λειτουργίας VLOOKUP ανοικτό για το επόμενο βήμα του οδηγού

08 από 10

Εισαγωγή του Argument Lookup Range VLOOKUP

Το όρισμα Range_lookup του VLOOKUP είναι μια λογική τιμή (TRUE ή FALSE μόνο) που δηλώνει εάν θέλετε το VLOOKUP να βρει ακριβή ή κατά προσέγγιση αντιστοιχία με την Lookup_value.

• Εάν το TRUE ή αν παραλειφθεί αυτό το όρισμα , το VLOOKUP επιστρέφει είτε ακριβή αντιστοίχιση με την τιμή Lookup_value, είτε, αν δεν βρεθεί ακριβής αντιστοίχιση, το VLOOKUP επιστρέφει την επόμενη μεγαλύτερη τιμή. Για να γίνει αυτό, τα δεδομένα στην πρώτη στήλη του Table_array πρέπει να ταξινομούνται με αύξουσα σειρά .
• Εάν FALSE, VLOOKUP θα χρησιμοποιήσει μόνο μια ακριβή αντιστοιχία στην τιμή Lookup_value. Εάν υπάρχουν δύο ή περισσότερες τιμές στην πρώτη στήλη του Table_array που ταιριάζουν με την τιμή αναζήτησης, χρησιμοποιείται η πρώτη τιμή που βρέθηκε. Εάν δεν βρεθεί ακριβής αντιστοίχιση, επιστρέφεται σφάλμα # N / A.

Σε αυτό το σεμινάριο, δεδομένου ότι ψάχνουμε για συγκεκριμένες πληροφορίες σχετικά με ένα συγκεκριμένο στοιχείο υλικού, θα ορίσουμε το Range_lookup ίσο με το False .

Εκπαιδευτικά βήματα

1. Κάντε κλικ στη γραμμή Range_lookup στο παράθυρο διαλόγου
2. Πληκτρολογήστε τη λέξη Ψευδές σε αυτή τη γραμμή για να υποδείξετε ότι θέλουμε το VLOOKUP να επιστρέψει μια ακριβή αντιστοιχία για τα δεδομένα που αναζητούμε
3. Κάντε κλικ στο κουμπί OK για να ολοκληρώσετε το παράθυρο διαλόγου αναζήτησης φόρτωσης και κλείσιμο
4. Δεδομένου ότι δεν έχουμε εισάγει ακόμα τα κριτήρια αναζήτησης στο κελί D2 , θα υπάρχει ένα σφάλμα # N / A στο κελί E2
5. Αυτό το σφάλμα θα διορθωθεί όταν θα προσθέσουμε τα κριτήρια αναζήτησης στο τελευταίο βήμα του σεμιναρίου

09 από 10

Αντιγραφή της φόρμουλας αναζήτησης με τη λαβή πλήρωσης

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

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

Σε αυτό το σεμινάριο θέλουμε να ανακτήσει τα δεδομένα από τις στήλες 2, 3 και 4 του πίνακα δεδομένων - δηλαδή την τιμή, τον αριθμό του τμήματος και το όνομα του προμηθευτή όταν εισάγουμε ένα όνομα μέρους ως τιμή Lookup_value.

Δεδομένου ότι τα δεδομένα παρατίθενται σε κανονικό μοτίβο στο φύλλο εργασίας , μπορούμε να αντιγράψουμε τη φόρτωση αναζήτησης στο κελί E2 στα κελιά F2 και G2.

Καθώς αντιγράφεται ο τύπος, το Excel θα ενημερώσει τη σχετική αναφορά κυψέλης στη συνάρτηση COLUMN (B1) για να αντικατοπτρίζει τη νέα θέση του τύπου.

Επίσης, το Excel δεν αλλάζει την απόλυτη αναφορά κυττάρων $ D $ 2 και την ονομαστική περιοχή πίνακα, καθώς αντιγράφεται ο τύπος.

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

Εκπαιδευτικά βήματα

1. Κάντε κλικ στο κελί E2 - όπου βρίσκεται ο τύπος αναζήτησης - για να γίνει το ενεργό κελί
2. Τοποθετήστε το δείκτη του ποντικιού πάνω από το μαύρο τετράγωνο στην κάτω δεξιά γωνία. Ο δείκτης θα αλλάξει σε ένα σύμβολο συν " + " - αυτή είναι η λαβή πλήρωσης
3. Κάντε κλικ στο αριστερό πλήκτρο του ποντικιού και σύρετε τη λαβή πλήρωσης στο κελί G2
4. Απελευθερώστε το κουμπί του ποντικιού και το κελί F3 θα πρέπει να περιέχει τον τύπο διαμόρφωσης δύο διαστάσεων
5. Αν γίνει σωστά, τα κελιά F2 και G2 πρέπει τώρα να περιέχουν και το σφάλμα # N / A που υπάρχει στο κελί E2

10 από 10

Εισαγωγή κριτηρίων αναζήτησης

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

Για να το κάνετε αυτό, πληκτρολογήστε το όνομα του στοιχείου που θέλετε να ανακτήσετε στο στοιχείο Lookup_value (D2) και πατήστε το πλήκτρο ENTER στο πληκτρολόγιο.

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

Εκπαιδευτικά βήματα

1. Κάντε κλικ στο κελί D2 στο φύλλο εργασίας
2. Πληκτρολογήστε Widget στο κελί D2 και πατήστε το πλήκτρο ENTER στο πληκτρολόγιο
3. Οι ακόλουθες πληροφορίες πρέπει να εμφανίζονται στα κελιά E2 έως G2:
   • E2 - 14,76 δολάρια - η τιμή ενός widget
   • F2 - PN-98769 - ο αριθμός μέρους για ένα γραφικό στοιχείο
   • G2 - Widgets Inc. - το όνομα του προμηθευτή για widgets
4. Δοκιμάστε περαιτέρω τον τύπο συστοιχιών VLOOKUP πληκτρολογώντας το όνομα των άλλων μερών στο κελί D2 και παρατηρώντας τα αποτελέσματα στα κελιά E2 έως G2

Εάν ένα μήνυμα σφάλματος, όπως #REF! εμφανίζεται στα κελιά E2, F2 ή G2, αυτή η λίστα με μηνύματα σφάλματος VLOOKUP μπορεί να σας βοηθήσει να προσδιορίσετε πού βρίσκεται το πρόβλημα.