Συνδυάζοντας τη συνάρτηση VLOOKUP του Excel με τη συνάρτηση COLUMN, μπορείτε να δημιουργήσετε έναν τύπο αναζήτησης που επιστρέφει πολλαπλές τιμές από μία γραμμή βάσης δεδομένων ή πίνακα δεδομένων. Μάθετε πώς να δημιουργείτε έναν τύπο αναζήτησης που επιστρέφει πολλαπλές τιμές από μία μόνο εγγραφή δεδομένων.
Οι οδηγίες σε αυτό το άρθρο ισχύουν για το Excel 2019, 2016, 2013, 2010. και Excel για Microsoft 365.
Κατώτατη γραμμή
Ο τύπος αναζήτησης απαιτεί η συνάρτηση COLUMN να είναι ένθετη μέσα στο VLOOKUP. Η ένθεση μιας συνάρτησης περιλαμβάνει την εισαγωγή της δεύτερης συνάρτησης ως ένα από τα ορίσματα για την πρώτη συνάρτηση.
Εισαγάγετε τα δεδομένα εκμάθησης
Σε αυτό το σεμινάριο, η συνάρτηση COLUMN εισάγεται ως το όρισμα του αριθμού ευρετηρίου στήλης για το VLOOKUP. Το τελευταίο βήμα στο σεμινάριο περιλαμβάνει την αντιγραφή του τύπου αναζήτησης σε πρόσθετες στήλες για την ανάκτηση πρόσθετων τιμών για το επιλεγμένο τμήμα.
Το πρώτο βήμα σε αυτό το σεμινάριο είναι να εισαγάγετε τα δεδομένα σε ένα φύλλο εργασίας του Excel. Για να ακολουθήσετε τα βήματα σε αυτό το σεμινάριο, εισαγάγετε τα δεδομένα που εμφανίζονται στην παρακάτω εικόνα στα ακόλουθα κελιά:
- Εισαγάγετε το κορυφαίο εύρος δεδομένων στα κελιά D1 έως G1.
- Εισαγάγετε το δεύτερο εύρος στα κελιά D4 έως G10.
Τα κριτήρια αναζήτησης και ο τύπος αναζήτησης που δημιουργήθηκαν σε αυτόν τον οδηγό εισάγονται στη σειρά 2 του φύλλου εργασίας.
Αυτός ο οδηγός δεν περιλαμβάνει τη βασική μορφοποίηση του Excel που εμφανίζεται στην εικόνα, αλλά αυτό δεν επηρεάζει τον τρόπο λειτουργίας του τύπου αναζήτησης.
Δημιουργία επώνυμης περιοχής για τον πίνακα δεδομένων
Ένα εύρος με όνομα είναι ένας εύκολος τρόπος αναφοράς σε ένα εύρος δεδομένων σε έναν τύπο. Αντί να πληκτρολογείτε τις αναφορές κελιών για δεδομένα, πληκτρολογήστε το όνομα της περιοχής.
Ένα δεύτερο πλεονέκτημα της χρήσης μιας περιοχής με όνομα είναι ότι οι αναφορές κελιών για αυτήν την περιοχή δεν αλλάζουν ποτέ ακόμα και όταν ο τύπος αντιγράφεται σε άλλα κελιά στο φύλλο εργασίας. Τα ονόματα εύρους είναι μια εναλλακτική λύση στη χρήση απόλυτων αναφορών κελιών για την αποφυγή σφαλμάτων κατά την αντιγραφή τύπων.
Το όνομα περιοχής δεν περιλαμβάνει τις επικεφαλίδες ή τα ονόματα των πεδίων για τα δεδομένα (όπως φαίνεται στη σειρά 4), μόνο τα δεδομένα.
-
Επισήμανση κελιά D5 έως G10 στο φύλλο εργασίας.
Image -
Τοποθετήστε τον κέρσορα στο πλαίσιο ονόματος που βρίσκεται πάνω από τη στήλη Α, πληκτρολογήστε Πίνακας και, στη συνέχεια, πατήστε Enter. Τα κελιά D5 έως G10 έχουν το όνομα εύρους του πίνακα.
Image - Το όνομα περιοχής για το όρισμα πίνακα πίνακα VLOOKUP χρησιμοποιείται αργότερα σε αυτόν τον οδηγό.
Άνοιγμα του πλαισίου διαλόγου VLOOKUP
Αν και είναι δυνατό να πληκτρολογήσετε τον τύπο αναζήτησης απευθείας σε ένα κελί σε ένα φύλλο εργασίας, πολλοί άνθρωποι δυσκολεύονται να διατηρήσουν τη σύνταξη ευθεία - ειδικά για έναν περίπλοκο τύπο όπως αυτός που χρησιμοποιείται σε αυτό το σεμινάριο.
Εναλλακτικά, χρησιμοποιήστε το παράθυρο διαλόγου Ορίσματα συνάρτησης VLOOKUP. Σχεδόν όλες οι συναρτήσεις του Excel έχουν ένα παράθυρο διαλόγου όπου καθένα από τα ορίσματα της συνάρτησης εισάγεται σε ξεχωριστή γραμμή.
-
Επιλέξτε κελί E2 του φύλλου εργασίας. Αυτή είναι η τοποθεσία όπου θα εμφανίζονται τα αποτελέσματα του τύπου δισδιάστατης αναζήτησης.
Image -
Στην κορδέλα, μεταβείτε στην καρτέλα Formulas και επιλέξτε Αναζήτηση & Αναφορά.
Image -
Επιλέξτε VLOOKUP για να ανοίξετε το παράθυρο διαλόγου Function Arguments.
Image - Το πλαίσιο διαλόγου Function Arguments είναι όπου εισάγονται οι παράμετροι της συνάρτησης VLOOKUP.
Εισαγάγετε το όρισμα τιμής αναζήτησης
Κανονικά, η τιμή αναζήτησης ταιριάζει με ένα πεδίο δεδομένων στην πρώτη στήλη του πίνακα δεδομένων. Σε αυτό το παράδειγμα, η τιμή αναζήτησης αναφέρεται στο όνομα του τμήματος που θέλετε να βρείτε πληροφορίες. Οι επιτρεπόμενοι τύποι δεδομένων για την τιμή αναζήτησης είναι δεδομένα κειμένου, λογικές τιμές, αριθμοί και αναφορές κελιών.
Απόλυτες αναφορές κυττάρων
Όταν οι τύποι αντιγράφονται στο Excel, οι αναφορές κελιών αλλάζουν για να αντικατοπτρίζουν τη νέα θέση. Εάν συμβεί αυτό, το D2, η αναφορά κελιού για την τιμή αναζήτησης, αλλάζει και δημιουργεί σφάλματα στα κελιά F2 και G2.
Οι απόλυτες αναφορές κελιών δεν αλλάζουν όταν αντιγράφονται οι τύποι.
Για να αποτρέψετε τα σφάλματα, μετατρέψτε την αναφορά κελιού D2 σε απόλυτη αναφορά κελιού. Για να δημιουργήσετε μια απόλυτη αναφορά κελιού, πατήστε το πλήκτρο F4. Αυτό προσθέτει σύμβολα δολαρίου γύρω από την αναφορά του κελιού, όπως $D$2.
-
Στο παράθυρο διαλόγου Επιχειρήματα συνάρτησης, τοποθετήστε τον κέρσορα στο πλαίσιο κειμένου lookup_value. Στη συνέχεια, στο φύλλο εργασίας, επιλέξτε κελί D2 για να προσθέσετε αυτήν την αναφορά κελιού στο lookup_value. Το κελί D2 είναι όπου θα εισαχθεί το όνομα του εξαρτήματος.
Image -
Χωρίς να μετακινήσετε το σημείο εισαγωγής, πατήστε το πλήκτρο F4 για να μετατρέψετε το D2 στην απόλυτη αναφορά κελιού $D$2.
Image - Αφήστε το παράθυρο διαλόγου της συνάρτησης VLOOKUP ανοιχτό για το επόμενο βήμα στον οδηγό.
Εισαγάγετε το όρισμα πίνακα πίνακα
Ένας πίνακας πίνακα είναι ο πίνακας δεδομένων που αναζητά ο τύπος αναζήτησης για να βρει τις πληροφορίες που θέλετε. Ο πίνακας πρέπει να περιέχει τουλάχιστον δύο στήλες δεδομένων.
Η πρώτη στήλη περιέχει το όρισμα τιμής αναζήτησης (το οποίο ρυθμίστηκε στην προηγούμενη ενότητα), ενώ η δεύτερη στήλη αναζητείται από τον τύπο αναζήτησης για να βρείτε τις πληροφορίες που καθορίζετε.
Το όρισμα πίνακα πίνακα πρέπει να εισαχθεί είτε ως περιοχή που περιέχει τις αναφορές κελιών για τον πίνακα δεδομένων είτε ως όνομα περιοχής.
Για να προσθέσετε τον πίνακα δεδομένων στη συνάρτηση VLOOKUP, τοποθετήστε τον κέρσορα στο πλαίσιο κειμένου table_array στο πλαίσιο διαλόγου και πληκτρολογήστε Tableγια να εισαγάγετε το όνομα περιοχής για αυτό το όρισμα.
Συνάρτηση Nest the COLUMN
Κανονικά, το VLOOKUP επιστρέφει δεδομένα μόνο από μία στήλη ενός πίνακα δεδομένων. Αυτή η στήλη ορίζεται από το όρισμα αριθμός ευρετηρίου στήλης. Σε αυτό το παράδειγμα, ωστόσο, υπάρχουν τρεις στήλες και ο αριθμός ευρετηρίου στήλης πρέπει να αλλάξει χωρίς επεξεργασία του τύπου αναζήτησης. Για να το πετύχετε αυτό, τοποθετήστε τη συνάρτηση COLUMN μέσα στη συνάρτηση VLOOKUP ως όρισμα Col_index_num.
Κατά την ένθεση συναρτήσεων, το Excel δεν ανοίγει το παράθυρο διαλόγου της δεύτερης συνάρτησης για να εισαγάγει τα ορίσματά της. Η λειτουργία COLUMN πρέπει να εισαχθεί χειροκίνητα. Η συνάρτηση COLUMN έχει μόνο ένα όρισμα, το όρισμα αναφοράς, το οποίο είναι μια αναφορά κελιού.
Η συνάρτηση COLUMN επιστρέφει τον αριθμό της στήλης που παρέχεται ως όρισμα αναφοράς. Μετατρέπει το γράμμα της στήλης σε αριθμό.
Για να βρείτε την τιμή ενός αντικειμένου, χρησιμοποιήστε τα δεδομένα στη στήλη 2 του πίνακα δεδομένων. Αυτό το παράδειγμα χρησιμοποιεί τη στήλη Β ως αναφορά για την εισαγωγή 2 στο όρισμα Col_index_num.
-
Στο πλαίσιο διαλόγου Function Arguments, τοποθετήστε τον κέρσορα στο πλαίσιο κειμένου Col_index_num και πληκτρολογήστε COLUMN(. (Βεβαιωθείτε ότι έχετε συμπεριλάβει την ανοιχτή στρογγυλή αγκύλη.)
Image -
Στο φύλλο εργασίας, επιλέξτε κελί B1 για να εισαγάγετε αυτήν την αναφορά κελιού ως όρισμα αναφοράς.
Image - Πληκτρολογήστε ένα κλεισιμο στρογγυλό στήριγμα για να ολοκληρώσετε τη συνάρτηση COLUMN.
Εισαγάγετε το όρισμα αναζήτησης εύρους VLOOKUP
Το όρισμα Range_lookup του VLOOKUP είναι μια λογική τιμή (TRUE ή FALSE) που υποδεικνύει εάν το VLOOKUP θα πρέπει να βρει μια ακριβή ή κατά προσέγγιση αντιστοίχιση με το Lookup_value.
- TRUE ή Παραλειφθέν: Το VLOOKUP επιστρέφει μια στενή αντιστοίχιση με την τιμή αναζήτησης. Εάν δεν βρεθεί ακριβής αντιστοίχιση, το VLOOKUP επιστρέφει την επόμενη μεγαλύτερη τιμή. Τα δεδομένα στην πρώτη στήλη του πίνακα_πίνακα πρέπει να ταξινομηθούν με αύξουσα σειρά.
- FALSE: Το VLOOKUP χρησιμοποιεί μια ακριβή αντιστοίχιση με το Lookup_value. Εάν υπάρχουν δύο ή περισσότερες τιμές στην πρώτη στήλη του Table_array που ταιριάζουν με την τιμή αναζήτησης, χρησιμοποιείται η πρώτη τιμή που βρέθηκε. Εάν δεν βρεθεί ακριβής αντιστοίχιση, επιστρέφεται ένα σφάλμα N/A.
Σε αυτόν τον οδηγό, θα αναζητηθούν συγκεκριμένες πληροφορίες σχετικά με ένα συγκεκριμένο στοιχείο υλικού, επομένως το Range_lookup έχει οριστεί σε FALSE.
Στο παράθυρο διαλόγου Function Arguments, τοποθετήστε τον κέρσορα στο πλαίσιο κειμένου Range_lookup και πληκτρολογήστε False για να πείτε στο VLOOKUP να επιστρέψει μια ακριβή αντιστοίχιση για τα δεδομένα.
Επιλέξτε OK για να ολοκληρώσετε τον τύπο αναζήτησης και να κλείσετε το πλαίσιο διαλόγου. Το κελί E2 θα περιέχει ένα σφάλμα N/A επειδή τα κριτήρια αναζήτησης δεν έχουν εισαχθεί στο κελί D2. Αυτό το σφάλμα είναι προσωρινό. Θα διορθωθεί όταν προστεθούν τα κριτήρια αναζήτησης στο τελευταίο βήμα αυτού του σεμιναρίου.
Αντιγράψτε τον τύπο αναζήτησης και εισαγάγετε τα κριτήρια
Ο τύπος αναζήτησης ανακτά δεδομένα από πολλές στήλες του πίνακα δεδομένων ταυτόχρονα. Για να γίνει αυτό, ο τύπος αναζήτησης πρέπει να βρίσκεται σε όλα τα πεδία από τα οποία θέλετε πληροφορίες.
Για να ανακτήσετε δεδομένα από τις στήλες 2, 3 και 4 του πίνακα δεδομένων (την τιμή, τον αριθμό του ανταλλακτικού και το όνομα του προμηθευτή), εισαγάγετε ένα μερικό όνομα ως το Lookup_value.
Δεδομένου ότι τα δεδομένα παρουσιάζονται με κανονικό μοτίβο στο φύλλο εργασίας, αντιγράψτε τον τύπο αναζήτησης στο κελί E2 σε κελιά F2 και G2 Καθώς ο τύπος αντιγράφεται, το Excel ενημερώνει τη σχετική αναφορά κελιού στη συνάρτηση COLUMN (κελί B1) για να αντικατοπτρίζει τη νέα θέση του τύπου. Το Excel δεν αλλάζει την απόλυτη αναφορά κελιού (όπως $D$2) και την ονομαζόμενη περιοχή (Πίνακας) καθώς αντιγράφεται ο τύπος.
Υπάρχουν περισσότεροι από ένας τρόποι αντιγραφής δεδομένων στο Excel, αλλά ο ευκολότερος τρόπος είναι να χρησιμοποιήσετε το Fill Handle.
-
Επιλέξτε κελί E2, όπου βρίσκεται ο τύπος αναζήτησης, για να το κάνετε το ενεργό κελί.
Image -
Σύρετε τη λαβή πλήρωσης στο κελί G2. Τα κελιά F2 και G2 εμφανίζουν το σφάλμα N/A που υπάρχει στο κελί E2.
Image -
Για να χρησιμοποιήσετε τους τύπους αναζήτησης για να ανακτήσετε πληροφορίες από τον πίνακα δεδομένων, στο φύλλο εργασίας επιλέξτε κελί D2, πληκτρολογήστε Widget και πατήστε Enter.
Image Οι ακόλουθες πληροφορίες εμφανίζονται στα κελιά E2 έως G2.
- E2: 14,76 $ - η τιμή ενός γραφικού στοιχείου
- F2: PN-98769 - ο αριθμός εξαρτήματος για ένα γραφικό στοιχείο
- G2: Widgets Inc. - το όνομα του προμηθευτή για γραφικά στοιχεία
-
Για να δοκιμάσετε τον τύπο του πίνακα VLOOKUP, πληκτρολογήστε το όνομα άλλων τμημάτων στο κελί D2 και παρατηρήστε τα αποτελέσματα στα κελιά E2 έως G2.
Image - Κάθε κελί που περιέχει τον τύπο αναζήτησης περιέχει ένα διαφορετικό τμήμα δεδομένων σχετικά με το στοιχείο υλικού που αναζητήσατε.
Η συνάρτηση VLOOKUP με ένθετες συναρτήσεις όπως η COLUMN παρέχει μια ισχυρή μέθοδο αναζήτησης δεδομένων μέσα σε έναν πίνακα, χρησιμοποιώντας άλλα δεδομένα ως αναφορά αναζήτησης.
