Συνδυάζοντας το ExcelVLOOKUP λειτουργία με τοΣΤΗΛΗ μπορείτε να δημιουργήσετε έναν τύπο αναζήτησης που σας επιτρέπει να επιστρέψετε πολλές τιμές από μια μόνο σειρά μιας βάσης δεδομένων ή πίνακα δεδομένων.
01 από 10Επιστροφή πολλαπλών τιμών με το Excel VLOOKUP
Ακολουθώντας τα παρακάτω βήματα, δημιουργείται ο τύπος αναζήτησης που φαίνεται στην παραπάνω εικόνα, ο οποίος θα επιστρέψει πολλές τιμές από ένα μόνο αρχείο δεδομένων.
Ο τύπος αναζήτησης απαιτεί ότι το ΣΤΗΛΗ Η λειτουργία είναι ενσωματωμένη στο εσωτερικό του VLOOKUP. Η τοποθέτηση μιας συνάρτησης περιλαμβάνει την εισαγωγή της δεύτερης λειτουργίας ως ένα από τα επιχειρήματα για την πρώτη λειτουργία.
Σε αυτό το σεμινάριο, το ΣΤΗΛΗ η λειτουργία θα εισαχθεί ως δείκτη στήλης αριθμός επιχείρημα για VLOOKUP. Το τελευταίο βήμα στο σεμινάριο περιλαμβάνει την αντιγραφή της φόρμας αναζήτησης σε πρόσθετες στήλες για την ανάκτηση πρόσθετων τιμών για το επιλεγμένο τμήμα.
Εισαγάγετε τα Δεδομένα Tutorial
Το πρώτο βήμα στο μάθημα είναι να εισαγάγετε τα δεδομένα σε ένα φύλλο εργασίας του Excel. Για να ακολουθήσετε τα βήματα στο μάθημα εισάγετε τα δεδομένα που εμφανίζονται στην παραπάνω εικόνα στα ακόλουθα κελιά:
- Εισαγάγετε το κορυφαίο εύρος δεδομένων σε κύτταρα D1 προς το G1.
- Εισαγάγετε τη δεύτερη περιοχή σε κύτταρα D4 προς το G10.
Θα εισαχθούν τα κριτήρια αναζήτησης και ο τύπος αναζήτησης που δημιουργήθηκε κατά τη διάρκεια αυτού του σεμιναρίου σειρά 2 του φύλλου εργασίας.
Το σεμινάριο δεν περιλαμβάνει τη μορφοποίηση που φαίνεται στην εικόνα, αλλά αυτό δεν θα επηρεάσει τον τρόπο με τον οποίο λειτουργεί ο τύπος αναζήτησης. Πληροφορίες σχετικά με τις επιλογές μορφοποίησης παρόμοιες με εκείνες που βλέπετε παραπάνω είναι διαθέσιμες σε αυτό το Βασικό Εκμάθηση Μορφοποίησης Excel.
03 από 10Δημιουργία εύρους ονομασίας για τον πίνακα δεδομένων
Μια ονομαστική περιοχή είναι ένας εύκολος τρόπος να αναφερθείτε σε μια σειρά δεδομένων σε μια φόρμουλα. Αντί να πληκτρολογείτε τις αναφορές κελιών για τα δεδομένα, μπορείτε απλά να πληκτρολογήσετε το όνομα του εύρους.
Ένα δεύτερο πλεονέκτημα για τη χρήση μιας ονομαστικής περιοχής είναι ότι οι αναφορές κελιών για αυτό το εύρος δεν αλλάζουν ποτέ ακόμη και όταν ο τύπος αντιγράφεται σε άλλα κελιά στο φύλλο εργασίας. Επομένως, τα ονόματα εύρους είναι μια εναλλακτική λύση στη χρήση απόλυτων αναφορών κελιών για την αποφυγή σφαλμάτων κατά την αντιγραφή των τύπων.
Το όνομα εμβέλειας δεν περιλαμβάνει τις επικεφαλίδες ή τα ονόματα πεδίων για τα δεδομένα (σειρά 4) αλλά μόνο τα ίδια τα δεδομένα.
- Αποκορύφωμα κύτταρα D5 προς το G10 στο φύλλο εργασίας για να τις επιλέξετε.
- Κάνε κλικ στο Όνομα κουτί που βρίσκεται παραπάνω στήλη Α.
- Τύπος Τραπέζι μέσα στο Όνομα κουτί.
- Πάτα το Εισαγω στο πληκτρολόγιο.
- Κύτταρα D5 προς το G10 τώρα έχετε το όνομα της περιοχής Τραπέζι. Θα χρησιμοποιήσουμε το όνομα για το VLOOKUP πίνακα πίνακα το επιχείρημα αργότερα στο σεμινάριο.
Ανοίξτε το παράθυρο διαλόγου VLOOKUP
Αν και είναι δυνατό να πληκτρολογήσετε απλά τη φόρμουλα αναζήτησης απευθείας σε ένα κελί σε ένα φύλλο εργασίας, πολλοί άνθρωποι δυσκολεύονται να διατηρήσουν τη σύνταξη ευθύς - ειδικά για έναν πολύπλοκο τύπο όπως αυτός που χρησιμοποιούμε σε αυτό το σεμινάριο.
Μια εναλλακτική λύση, στην περίπτωση αυτή, είναι η χρήση του VLOOKUP κουτί διαλόγου. Σχεδόν όλες οι λειτουργίες του Excel έχουν ένα παράθυρο διαλόγου που σας επιτρέπει να εισάγετε τα επιχειρήματα της κάθε λειτουργίας σε ξεχωριστή γραμμή.
Εκπαιδευτικά βήματα
- Κάντε κλικ στο κυττάρου Ε2 του φύλλου εργασίας - η θέση όπου θα εμφανιστούν τα αποτελέσματα του τύπου δισδιάστατου ερωτηματολογίου.
- Κάνε κλικ στο Φάκελος τύπων απο ταινία.
- Κάνε κλικ στο Αναζήτηση & Αναφορά επιλογή στο ταινία για να ανοίξετε το αναπτυσσόμενο μενού λειτουργίας.
- Κάντε κλικ στο VLOOKUP στη λίστα για να ανοίξετε το παράθυρο διαλόγου της λειτουργίας.
Εισαγωγή του Argument Value Value Lookup
Κανονικά, το ψάχνω αξία αντιστοιχεί σε ένα πεδίο δεδομένων στην πρώτη στήλη του πίνακα δεδομένων. Στο παράδειγμα μας, το ψάχνω αξία αναφέρεται στο όνομα του τμήματος υλικού για το οποίο θέλουμε να βρούμε πληροφορίες. Οι επιτρεπόμενοι τύποι δεδομένων για το ψάχνω αξία είναι δεδομένα κειμένου, λογικές τιμές, αριθμοί και αναφορές κυττάρων.
Απόλυτες αναφορές κυττάρων
Κανονικά, όταν οι τύποι αντιγράφονται στο Excel, οι αναφορές κελιών αλλάζουν για να αντικατοπτρίζουν τη νέα τους θέση. Εάν συμβεί αυτό, D2, η αναφορά κυττάρων για το ψάχνω αξία, θα αλλάξει καθώς αντιγράφεται ο τύπος δημιουργώντας σφάλματα στο κύτταρα F2 και G2.
Οι απόλυτες αναφορές κυψελών δεν αλλάζουν όταν αντιγράφονται οι τύποι.
Για να αποτρέψουμε τα σφάλματα, θα μετατρέψουμε την αναφορά κυττάρων D2 σε απόλυτη αναφορά κυψέλης. Απόλυτες αναφορές κυττάρων δημιουργούνται πατώντας το F4 στο πληκτρολόγιο. Κάνοντας έτσι προσθέτει σημάδια δολάριο γύρω από την αναφορά κελί, όπως $ D $ 2.
- Κάνε κλικ στο lookup_value γραμμή στο παράθυρο διαλόγου.
- Κάντε κλικ στο κύτταρο D2 για να προσθέσετε αυτήν την αναφορά κελιού στο lookup_value γραμμή - αυτό είναι το κελί όπου θα πληκτρολογήσουμε το όνομα του μέρους για το οποίο αναζητούμε πληροφορίες
- Χωρίς να μετακινείτε το σημείο εισαγωγής, πατήστε το F4 κλειδί στο πληκτρολόγιο για μετατροπή D2 στην απόλυτη αναφορά κυψελών $ D $ 2.
- ΑΣΕ το VLOOKUP λειτουργικό παράθυρο διαλόγου για το επόμενο βήμα στο μάθημα.
Εισαγωγή του Argument Array πίνακα
Ο πίνακα πίνακας είναι ο πίνακας δεδομένων που αναζητά ο τύπος αναζήτησης για να βρει τις πληροφορίες που θέλουμε.Η συστοιχία πίνακα πρέπει να περιέχει τουλάχιστον δύο στήλες δεδομένων.
Η πρώτη στήλη περιέχει το όρισμα της τιμής αναζήτησης (το προηγούμενο βήμα στο μάθημα), ενώ η δεύτερη στήλη θα αναζητηθεί από τον τύπο αναζήτησης για να βρει τις πληροφορίες που καθορίζουμε.
Το όρισμα πίνακας συστοιχιών πρέπει να εισαχθεί είτε ως ένα εύρος που περιέχει τις αναφορές κελιών για τον πίνακα δεδομένων είτε ως όνομα εύρους.
- Κάνε κλικ στο table_array γραμμή στο παράθυρο διαλόγου.
- Τύπος Τραπέζι για να εισαγάγετε το όνομα περιοχής για αυτό το επιχείρημα.
- ΑΣΕ το VLOOKUP λειτουργικό παράθυρο διαλόγου για το επόμενο βήμα στο μάθημα.
Φένοντας τη λειτουργία COLUMN
Κανονικά VLOOKUP επιστρέφει μόνο δεδομένα από μια στήλη ενός πίνακα δεδομένων και αυτή η στήλη έχει οριστεί από το αριθμός ευρετηρίου στήλης διαφωνία.
Σε αυτό το παράδειγμα, ωστόσο, έχουμε τρεις στήλες που θέλουμε να επιστρέψουμε δεδομένα, οπότε χρειαζόμαστε έναν τρόπο εύκολης αλλαγής αριθμός ευρετηρίου στήλης χωρίς να επεξεργαστούμε τον τύπο μας αναζήτησης - εδώ είναι το ΣΤΗΛΗ λειτουργία εισέρχεται.
Εισάγοντάς το ως αριθμός ευρετηρίου στήλης argument, θα αλλάξει καθώς αντιγράφεται ο τύπος αναζήτησης κυττάρου D2 προς το κυττάρων Ε2 και F2 αργότερα στο σεμινάριο.
Λειτουργίες φθορισμού
ο ΣΤΗΛΗ ως εκ τούτου, ενεργεί ως VLOOKUP'μικρό αριθμός ευρετηρίου στήλης διαφωνία; αυτό επιτυγχάνεται με τη φωλιά του ΣΤΗΛΗ λειτουργία στο εσωτερικό του VLOOKUP στο Col_index_num γραμμή του πλαισίου διαλόγου.
Εισαγωγή στη λειτουργία COLUMN χειροκίνητα
Όταν λειτουργίες εμφύτευσης, το Excel δεν μας επιτρέπει να ανοίξουμε το παράθυρο διαλόγου δεύτερης λειτουργίας για να εισαγάγουμε τα επιχειρήματά του. ο ΣΤΗΛΗ λειτουργία, επομένως, πρέπει να εισαχθεί χειροκίνητα στο Col_index_num γραμμή. ο ΣΤΗΛΗ έχει μόνο ένα επιχείρημα - το Αναφορά το οποίο είναι μια αναφορά κυψέλης.
Επιλογή του Argument αναφοράς της Λειτουργίας ΣΤΗΛΗ
ο ΣΤΗΛΗ η δουλειά της συνάρτησης είναι να επιστρέψει τον αριθμό της στήλης που δίνεται ως Αναφορά διαφωνία. Με άλλα λόγια, μετατρέπει το γράμμα της στήλης σε έναν αριθμό με στήλη Α είναι η πρώτη στήλη, στήλη Β το δεύτερο και ούτω καθεξής.
Από το πρώτο πεδίο δεδομένων, αυτό που θέλουμε να επιστρέψουμε είναι η τιμή του στοιχείου, το οποίο βρίσκεται στη δεύτερη στήλη του πίνακα δεδομένων. Μπορούμε να επιλέξουμε την αναφορά κυψελών για κάθε κελί στο στήλη Β ως Αναφορά Argument για να πάρει τον αριθμό 2 για το Col_index_num διαφωνία.
- Στο VLOOKUP , κάντε κλικ στο Col_index_num γραμμή.
- Πληκτρολογήστε το όνομα της λειτουργίας στήλη ακολουθούμενο από ένα ανοικτή στρογγυλή βάση.
- Κάντε κλικ στο κύτταρο Β1 στο φύλλο εργασίας για να εισαγάγετε εκείνη την αναφορά κυττάρου ως Αναφορά διαφωνία.
- Τύπος Α κλειστός βραχίονας για να ολοκληρώσετε το ΣΤΗΛΗ λειτουργία
- ΑΣΕ το VLOOKUP λειτουργικό παράθυρο διαλόγου για το επόμενο βήμα στο μάθημα.
Εισαγωγή του Argument Lookup Range VLOOKUP
VLOOKUP'μικρό Range_lookup το επιχείρημα είναι μια λογική τιμή (ΑΛΗΘΗΣ ή ΨΕΥΔΗΣ) που δείχνει αν θέλετε VLOOKUP για να βρείτε ακριβή ή κατά προσέγγιση αντιστοιχία με το Lookup_value.
- Αν ΑΛΗΘΗΣ ή αν παραλείπεται αυτό το επιχείρημα: VLOOKUP επιστρέφει είτε ακριβή αντιστοίχιση με το Lookup_valueΉ, αν δεν βρεθεί ακριβής αντιστοίχιση, VLOOKUP επιστρέφει την επόμενη μεγαλύτερη τιμή. Για να γίνει αυτό ο τύπος, τα δεδομένα στην πρώτη στήλη του Table_array πρέπει να ταξινομηθούν σε αύξουσα σειρά.
- Αν ΨΕΥΔΗΣ: VLOOKUP θα χρησιμοποιήσει μόνο μια ακριβή αντιστοιχία με το Lookup_value. Εάν υπάρχουν δύο ή περισσότερες τιμές στην πρώτη στήλη του Table_array που ταιριάζουν με την τιμή αναζήτησης, χρησιμοποιείται η πρώτη τιμή που βρέθηκε. Αν δεν βρεθεί ακριβής αντιστοίχιση, α # N / A σφάλμα επιστρέφεται.
Σε αυτό το σεμινάριο, δεδομένου ότι αναζητούμε συγκεκριμένες πληροφορίες σχετικά με ένα συγκεκριμένο στοιχείο υλικού, θα το θέσουμε Range_lookup ίσο με Ψευδής.
- Κάνε κλικ στο Range_lookup γραμμή στο παράθυρο διαλόγου.
- Πληκτρολογήστε τη λέξη Ψευδής σε αυτή τη γραμμή για να υποδείξουμε ότι θέλουμε VLOOKUP για να επιστρέψουμε μια ακριβή αντιστοιχία για τα δεδομένα που αναζητούμε.
- Κάντε κλικ Εντάξει για να ολοκληρώσετε τη φόρμα αναζήτησης και το παράθυρο διαλόγου κλείσιμο.
- Δεδομένου ότι δεν έχουμε εισέλθει ακόμα στα κριτήρια αναζήτησης κύτταρο D2 ένα # Σφάλμα N / A θα είναι παρόντες στο κυττάρου Ε2.
- Αυτό το σφάλμα θα διορθωθεί όταν θα προσθέσουμε τα κριτήρια αναζήτησης στο τελευταίο βήμα του σεμιναρίου.
Αντιγραφή της φόρμουλας αναζήτησης
Ο τύπος αναζήτησης προορίζεται για την ανάκτηση δεδομένων από πολλαπλές στήλες του πίνακα δεδομένων ταυτόχρονα. Για να γίνει αυτό, ο τύπος αναζήτησης πρέπει να βρίσκεται σε όλα τα πεδία από τα οποία θέλουμε πληροφορίες.
Σε αυτό το σεμινάριο θέλουμε να ανακτήσει δεδομένα από στήλες 2, 3, και 4 του πίνακα δεδομένων - δηλαδή η τιμή, ο αριθμός του μέρους και το όνομα του προμηθευτή όταν εισάγουμε ένα μερικό όνομα ως Lookup_value.
Δεδομένου ότι τα δεδομένα παρατίθενται σε κανονικό πρότυπο στο φύλλο εργασίας, μπορούμε να αντιγράψουμε τη φόρμουλα αναζήτησης κυττάρου Ε2 προς το κύτταρα F2 και G2.
Καθώς αντιγράφεται ο τύπος, το Excel θα ενημερώσει τη σχετική αναφορά κυττάρων στο ΣΤΗΛΗ λειτουργία (κύτταρο Β1) για να αντικατοπτρίζει τη νέα θέση της φόρμας.
Επίσης, το Excel δεν αλλάζει την απόλυτη αναφορά κυττάρων $ D $ 2 και το όνομα φάσμα Τραπέζι καθώς αντιγράφεται ο τύπος.
Υπάρχουν περισσότεροι από ένας τρόποι για την αντιγραφή δεδομένων στο Excel, αλλά ίσως ο ευκολότερος τρόπος είναι η χρήση του Γεμίστε τη λαβή.
- Κάντε κλικ στο κυττάρου Ε2, όπου βρίσκεται ο τύπος αναζήτησης, για να γίνει το ενεργό κελί.
- Τοποθετήστε το δείκτη ποντικιού πάνω από το μαύρο τετράγωνο στην κάτω δεξιά γωνία - ο δείκτης θα αλλάξει σε ένα σύμβολο συν για να δηλώσει τη λαβή πλήρωσης.
- Κάντε κλικ στο αριστερό πλήκτρο του ποντικιού και στο σύρετε τη λαβή πλήρωσης σε όλη την κυττάρου G2.
- Ελευθέρωση το κουμπί του ποντικιού και το κυττάρου F3 θα πρέπει να περιέχει τη διδιάστατη φόρμουλα αναζήτησης.
- Εάν γίνει σωστά, κύτταρα F2 και G2 θα πρέπει τώρα να περιέχει και το # N / A σφάλμα που υπάρχει στο κυττάρου Ε2.
Εισαγωγή κριτηρίων αναζήτησης
Μόλις αντιγραφεί ο τύπος αναζήτησης στα απαιτούμενα κελιά, μπορεί να χρησιμοποιηθεί για την ανάκτηση πληροφοριών από τον πίνακα δεδομένων. Για να το κάνετε αυτό, πληκτρολογήστε το όνομα του στοιχείου που θέλετε να ανακτήσετε στο Lookup_value κυττάρων (D2) και πατήστε το Εισαγω στο πληκτρολόγιο.
Μόλις ολοκληρωθεί, κάθε κελί που περιέχει τη φόρμα αναζήτησης πρέπει να περιέχει διαφορετικό κομμάτι δεδομένων σχετικά με το στοιχείο υλικού που αναζητάτε.
- Κάντε κλικ στο κυττάρου D2 στο φύλλο εργασίας.
- Τύπος Widget σε κυττάρου D2 και πατήστε το Εισαγω στο πληκτρολόγιο.
- Οι ακόλουθες πληροφορίες θα πρέπει να εμφανίζονται στο κυττάρων Ε2 προς το G2.
- Ε2: $ 14.76 - η τιμή ενός widget
- F2: PN-98769 - ο αριθμός μέρους για ένα γραφικό στοιχείο
- G2: Widgets Inc. - το όνομα του προμηθευτή για widgets
- Δοκιμάστε το VLOOKUP πληκτρολογήστε περαιτέρω τον τύπο πληκτρολογώντας το όνομα των άλλων μερών κυττάρου D2 και παρατηρώντας τα αποτελέσματα κυττάρων Ε2 προς το G2
