
Τι πρέπει να γνωρίζετε
- Αρχικά, δημιουργήστε μια συνάρτηση INDEX και, στη συνέχεια, ξεκινήστε τη λειτουργία ένθετου MATCH εισάγοντας το όρισμα Lookup_value.
- Στη συνέχεια, προσθέστε το όρισμα Lookup_array ακολουθούμενο από το Match_type όρισμα και, στη συνέχεια, καθορίστε το εύρος στηλών
- Στη συνέχεια, μετατρέψτε την ένθετη συνάρτηση σε τύπο πίνακα πατώντας Ctrl+Βάρδια+Εισαγω. Τέλος, προσθέστε τους όρους αναζήτησης στο φύλλο εργασίας.
Αυτό το άρθρο εξηγεί πώς να δημιουργήσετε έναν τύπο αναζήτησης που χρησιμοποιεί πολλαπλά κριτήρια στο Excel για να βρείτε πληροφορίες σε μια βάση δεδομένων ή έναν πίνακα δεδομένων χρησιμοποιώντας έναν τύπο πίνακα. Ο τύπος πίνακα περιλαμβάνει ένθεση της συνάρτησης MATCH μέσα στη συνάρτηση INDEX. Οι πληροφορίες καλύπτουν το Excel για Microsoft 365, το Excel 2019, το Excel 2016, το Excel 2013, το Excel 2010 και το Excel για Mac.
Ακολουθήστε μαζί με το εκπαιδευτικό πρόγραμμα
Για να ακολουθήσετε τα βήματα σε αυτό το σεμινάριο, εισαγάγετε τα δείγματα δεδομένων στα ακόλουθα κελιά, όπως φαίνεται στην παρακάτω εικόνα. Οι σειρές 3 και 4 παραμένουν κενές για να χωρέσουν τον τύπο πίνακα που δημιουργήθηκε κατά τη διάρκεια αυτού του σεμιναρίου. (Σημειώστε ότι αυτό το σεμινάριο δεν περιλαμβάνει τη μορφοποίηση που φαίνεται στην εικόνα.)
- Εισαγάγετε το κορυφαίο εύρος δεδομένων στα κελιά D1 έως F2.
- Εισαγάγετε τη δεύτερη περιοχή στα κελιά D5 έως F11.
Δημιουργήστε μια συνάρτηση INDEX στο Excel
Η συνάρτηση INDEX είναι μία από τις λίγες συναρτήσεις στο Excel που έχει πολλές φόρμες. Η συνάρτηση έχει μια φόρμα Array και μια φόρμα αναφοράς. Το Array Form επιστρέφει τα δεδομένα από μια βάση δεδομένων ή έναν πίνακα δεδομένων. Η φόρμα αναφοράς δίνει την αναφορά κελιού ή τη θέση των δεδομένων στον πίνακα. Σε αυτό το σεμινάριο, η φόρμα Array χρησιμοποιείται για την εύρεση του ονόματος του προμηθευτή για widget τιτανίου, αντί για την αναφορά κελιού σε αυτόν τον προμηθευτή στη βάση δεδομένων. Ακολουθήστε αυτά τα βήματα για να δημιουργήσετε τη συνάρτηση INDEX:
-
Επιλέξτε κελί ΣΤ3 για να γίνει το ενεργό κελί. Αυτό το κελί είναι όπου θα εισαχθεί η ένθετη συνάρτηση.
-
Παω σε ΜΑΘΗΜΑΤΙΚΟΙ τυποι.
-
Επιλέγω Αναζήτηση και αναφορά για να ανοίξετε την αναπτυσσόμενη λίστα της λειτουργίας.
-
Επιλέγω ΔΕΙΚΤΗΣ για να ανοίξετε το Επιλέξτε Επιχειρήματα κουτί διαλόγου.
-
Επιλέγω πίνακας, αριθμός_ορίων, στήλη_αριθ.
-
Επιλέγω Εντάξει για να ανοίξετε το Επιχειρήματα Λειτουργίας κουτί διαλόγου. Στο Excel για Mac, ανοίγει το Formula Builder.
-
Τοποθετήστε τον κέρσορα στο Πίνακας πλαίσιο κειμένου.
-
Επισημάνετε τα κελιά Δ6 διά μέσου F11 στο φύλλο εργασίας για να εισαγάγετε το εύρος στο πλαίσιο διαλόγου. Αφήστε το παράθυρο διαλόγου Function Arguments ανοιχτό. Ο τύπος δεν έχει ολοκληρωθεί. Θα συμπληρώσετε τον τύπο στις παρακάτω οδηγίες.
Ξεκινήστε τη λειτουργία Nested MATCH
Κατά την ένθεση μιας λειτουργίας μέσα σε μια άλλη, δεν είναι δυνατό να ανοίξετε τη δεύτερη, ή ένθετη, λειτουργία δημιουργίας τύπων για να εισαγάγετε τα απαραίτητα ορίσματα. Η ένθετη συνάρτηση πρέπει να εισαχθεί ως ένα από τα ορίσματα της πρώτης συνάρτησης. Όταν εισάγετε συναρτήσεις χειροκίνητα, τα ορίσματα της συνάρτησης διαχωρίζονται μεταξύ τους με κόμμα. Το πρώτο βήμα για την είσοδο της ένθεσης MATCH είναι να εισαγάγετε το όρισμα Lookup_value. Το Lookup_value είναι η τοποθεσία ή η αναφορά κελιού για τον όρο αναζήτησης που θα ταιριάζει στη βάση δεδομένων. Το Lookup_value δέχεται μόνο ένα κριτήριο αναζήτησης ή έναν όρο. Για να αναζητήσετε πολλαπλά κριτήρια, επεκτείνετε το Lookup_value συνδυάζοντας ή συνδυάζοντας δύο ή περισσότερες αναφορές κελιών χρησιμοποιώντας το σύμβολο ampersand (&)
-
Στο Επιχειρήματα Λειτουργίας πλαίσιο διαλόγου, τοποθετήστε τον κέρσορα στο Row_num πλαίσιο κειμένου.
-
Εισαγω ΑΓΩΝΑΣ(.
-
Επιλέξτε κελί Δ3 για να εισαγάγετε αυτήν την αναφορά κελιού στο παράθυρο διαλόγου.
-
Εισαγω & (το ampersand) μετά την αναφορά κελιού Δ3 για να προσθέσετε μια δεύτερη αναφορά κελιού.
-
Επιλέξτε κελί Ε3 για να εισαγάγετε τη δεύτερη αναφορά κελιού.
-
Εισαγω , (κόμμα) μετά την αναφορά E3 του κελιού για να ολοκληρωθεί η καταχώριση του ορίσματος Lookup_value της συνάρτησης MATCH.
Στο τελευταίο βήμα του σεμιναρίου, οι τιμές_αναζήτησης θα εισαχθούν στα κελιά D3 και E3 του φύλλου εργασίας.
Ολοκληρώστε τη λειτουργία Nested MATCH
Αυτό το βήμα καλύπτει την προσθήκη του ορίσματος Lookup_array για την ένθετη συνάρτηση MATCH. Το Lookup_array είναι το εύρος των κελιών που αναζητά η συνάρτηση MATCH για να βρει το όρισμα Lookup_value που έχει προστεθεί στο προηγούμενο βήμα του σεμιναρίου. Επειδή εντοπίστηκαν δύο πεδία αναζήτησης στο όρισμα Lookup_array, το ίδιο πρέπει να γίνει για το Lookup_array. Η συνάρτηση MATCH αναζητά μόνο έναν πίνακα για κάθε καθορισμένο όρο. Για να εισαγάγετε πολλές συστοιχίες, χρησιμοποιήστε το συμπλεκτικό σύμβολο για να ενώσετε τις συστοιχίες μαζί.
-
Τοποθετήστε τον κέρσορα στο τέλος των δεδομένων στο Row_num πλαίσιο κειμένου. Ο δρομέας εμφανίζεται μετά το κόμμα στο τέλος της τρέχουσας καταχώρισης.
-
Επισημάνετε τα κελιά Δ6 διά μέσου Δ11 στο φύλλο εργασίας για να εισαγάγετε το εύρος. Αυτό το εύρος είναι ο πρώτος πίνακας που αναζητά η συνάρτηση.
-
Εισαγω & (ένα ampersand) μετά τις αναφορές του κελιού D6: D11. Αυτό το σύμβολο προκαλεί τη λειτουργία της αναζήτησης σε δύο πίνακες.
-
Επισημάνετε τα κελιά Ε6 διά μέσου Ε11 στο φύλλο εργασίας για να εισαγάγετε το εύρος. Αυτό το εύρος είναι ο δεύτερος πίνακας που αναζητά η συνάρτηση.
-
Εισαγω , (κόμμα) μετά την αναφορά κελιού Ε3 για να ολοκληρώσετε την καταχώρηση του ορίσματος Lookup_array της συνάρτησης MATCH.
-
Αφήστε το παράθυρο διαλόγου ανοιχτό για το επόμενο βήμα στο σεμινάριο.
Προσθέστε το επιχείρημα τύπου MATCH
Το τρίτο και τελευταίο επιχείρημα του Η συνάρτηση MATCH είναι ο τύπος Match_type διαφωνία. Αυτό το επιχείρημα λέει στο Excel πώς να ταιριάξει το Lookup_value με τιμές στο Lookup_array. Οι διαθέσιμες επιλογές είναι 1, 0 ή -1. Αυτό το επιχείρημα είναι προαιρετικό. Εάν παραλειφθεί, η συνάρτηση χρησιμοποιεί την προεπιλεγμένη τιμή 1.
- Εάν το Match_type = 1 ή παραλειφθεί, το MATCH βρίσκει τη μεγαλύτερη τιμή που είναι μικρότερη ή ίση με το Lookup_value. Τα δεδομένα Lookup_array πρέπει να ταξινομηθούν με αύξουσα σειρά.
- Εάν Match_type = 0, MATCH βρίσκει την πρώτη τιμή που είναι ίση με το Lookup_value. Τα δεδομένα Lookup_array μπορούν να ταξινομηθούν με οποιαδήποτε σειρά.
- Εάν Match_type = -1, το MATCH βρίσκει τη μικρότερη τιμή που είναι μεγαλύτερη ή ίση με την τιμή_αναζήτησης. Τα δεδομένα Lookup_array πρέπει να ταξινομηθούν με φθίνουσα σειρά.
Εισαγάγετε αυτά τα βήματα μετά το κόμμα που καταχωρήθηκε στο προηγούμενο βήμα στη γραμμή Row_num στη συνάρτηση INDEX:
-
Εισαγω 0 (μηδέν) μετά το κόμμα στο Row_num πλαίσιο κειμένου. Αυτός ο αριθμός αναγκάζει την ένθετη συνάρτηση να επιστρέψει ακριβείς αντιστοιχίσεις στους όρους που έχουν εισαχθεί στα κελιά D3 και E3.
-
Εισαγω ) (ένα στρογγυλό βραχίονα κλεισίματος) για να ολοκληρώσετε τη λειτουργία MATCH.
-
Αφήστε το παράθυρο διαλόγου ανοιχτό για το επόμενο βήμα στο σεμινάριο.
Ολοκληρώστε τη συνάρτηση INDEX
Η λειτουργία MATCH ολοκληρώθηκε. Ήρθε η ώρα να μετακινηθείτε στο πλαίσιο κειμένου Column_num του παραθύρου διαλόγου και να εισαγάγετε το τελευταίο όρισμα για τη συνάρτηση INDEX. Αυτό το επιχείρημα λέει στο Excel ότι ο αριθμός στήλης είναι στην περιοχή D6 έως F11. Αυτό το εύρος εντοπίζει τις πληροφορίες που επιστρέφονται από τη συνάρτηση. Σε αυτήν την περίπτωση, ένας προμηθευτής για widget τιτανίου.
-
Τοποθετήστε τον κέρσορα στο Στήλη_αριθ πλαίσιο κειμένου.
-
Εισαγω 3 (ο αριθμός τρία). Αυτός ο αριθμός λέει τον τύπο για αναζήτηση δεδομένων στην τρίτη στήλη του εύρους D6 έως F11.
-
Αφήστε το παράθυρο διαλόγου ανοιχτό για το επόμενο βήμα στο σεμινάριο.
Δημιουργήστε τον τύπο Array
Πριν κλείσετε το παράθυρο διαλόγου, μετατρέψτε την ένθετη συνάρτηση σε τύπο πίνακα. Αυτός ο πίνακας επιτρέπει στη λειτουργία να αναζητήσει πολλούς όρους στον πίνακα δεδομένων. Σε αυτό το σεμινάριο, αντιστοιχίζονται δύο όροι: Widgets από τη στήλη 1 και Titanium από τη στήλη 2. Για να δημιουργήσετε έναν τύπο πίνακα στο Excel, πατήστε το CTRL, ΒΑΡΔΙΑ, και ΕΙΣΑΓΩ ταυτόχρονα πλήκτρα. Μόλις πατηθεί, η λειτουργία περιβάλλεται από σγουρά τιράντες, υποδηλώνοντας ότι η λειτουργία είναι τώρα ένας πίνακας.
-
Επιλέγω Εντάξει για να κλείσετε το παράθυρο διαλόγου. Στο Excel για Mac, επιλέξτε Ολοκληρώθηκε.
-
Επιλέξτε κελί ΣΤ3 για να δείτε τον τύπο και, στη συνέχεια, τοποθετήστε τον κέρσορα στο τέλος του τύπου στη γραμμή τύπων.
-
Για να μετατρέψετε τον τύπο σε πίνακα, πατήστε CTRL+ΒΑΡΔΙΑ+ΕΙΣΑΓΩ.
-
A # N / A εμφανίζεται σφάλμα στο κελί F3. Αυτό είναι το κελί όπου καταχωρήθηκε η συνάρτηση.
-
Το σφάλμα # N / A εμφανίζεται στο κελί F3 επειδή τα κελιά D3 και E3 είναι κενά. Τα D3 και E3 είναι τα κελιά όπου η συνάρτηση φαίνεται να βρίσκει την τιμή_αναζήτησης. Μετά την προσθήκη δεδομένων σε αυτά τα δύο κελιά, το σφάλμα αντικαθίσταται από πληροφορίες από τη βάση δεδομένων.
Προσθέστε τα κριτήρια αναζήτησης
Το τελευταίο βήμα είναι να προσθέσετε τους όρους αναζήτησης στο φύλλο εργασίας. Αυτό το βήμα ταιριάζει με τους όρους Widgets από τη στήλη 1 και Τιτάνιο από τη στήλη 2. Εάν ο τύπος εντοπίσει αντιστοίχιση και για τους δύο όρους στις κατάλληλες στήλες στη βάση δεδομένων, επιστρέφει την τιμή από την τρίτη στήλη.
-
Επιλέξτε κελί Δ3.
-
Εισαγω Widgets.
-
Επιλέξτε κελί Ε3.
-
Τύπος Τιτάνιοκαι πατήστε Εισαγω.
-
Το όνομα του προμηθευτή, Widgets Inc., εμφανίζεται στο κελί F3. Αυτός είναι ο μόνος προμηθευτής που πωλεί Titanium Widgets.
-
Επιλέξτε κελί ΣΤ3. Η συνάρτηση εμφανίζεται στη γραμμή τύπων πάνω από το φύλλο εργασίας. {= ΔΕΙΚΤΗΣ (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11,0), 3)}
Σε αυτό το παράδειγμα, υπάρχει μόνο ένας προμηθευτής για widget τιτανίου. Εάν υπήρχαν περισσότεροι από ένας προμηθευτές, ο προμηθευτής που αναφέρεται πρώτη στη βάση δεδομένων επιστρέφεται από τη συνάρτηση.