Πώς να δημιουργήσετε έναν τύπο αναζήτησης του Excel με πολλά κριτήρια

2024 Συγγραφέας: Abigail Brown | [email protected]. Τελευταία τροποποίηση: 2024-01-07 19:02

Τι πρέπει να γνωρίζετε

• Πρώτα, δημιουργήστε μια συνάρτηση INDEX και, στη συνέχεια, ξεκινήστε την ένθετη συνάρτηση MATCH εισάγοντας το όρισμα Lookup_value.
• Στη συνέχεια, προσθέστε το όρισμα Lookup_array ακολουθούμενο από το όρισμα Match_type και, στη συνέχεια, καθορίστε το εύρος στηλών.
• Στη συνέχεια, μετατρέψτε την ένθετη συνάρτηση σε τύπο πίνακα πατώντας Ctrl+ Shift+ Enter. Τέλος, προσθέστε τους όρους αναζήτησης στο φύλλο εργασίας.

Αυτό το άρθρο εξηγεί πώς να δημιουργήσετε έναν τύπο αναζήτησης που χρησιμοποιεί πολλαπλά κριτήρια στο 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 που έχει πολλές μορφές. Η συνάρτηση έχει μια φόρμα πίνακα και μια φόρμα αναφοράς. Η φόρμα πίνακα επιστρέφει τα δεδομένα από μια βάση δεδομένων ή πίνακα δεδομένων. Η φόρμα αναφοράς δίνει την αναφορά κελιού ή τη θέση των δεδομένων στον πίνακα.

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

Ακολουθήστε αυτά τα βήματα για να δημιουργήσετε τη συνάρτηση INDEX:

1. Επιλέξτε το κελί F3 για να το κάνετε το ενεργό κελί. Αυτό το κελί είναι όπου θα εισαχθεί η ένθετη συνάρτηση.

2. Μετάβαση στο Formulas.

   

3. Επιλέξτε Αναζήτηση & Αναφορά για να ανοίξετε την αναπτυσσόμενη λίστα συναρτήσεων.
4. Επιλέξτε INDEX για να ανοίξετε το παράθυρο διαλόγου Select Arguments.
5. Επιλέξτε array, row_nuum, column_nuum.
6. Επιλέξτε OK για να ανοίξετε το πλαίσιο διαλόγου Function Arguments. Στο Excel για Mac, ανοίγει το Formula Builder.
7. Τοποθετήστε τον κέρσορα στο πλαίσιο κειμένου Array.

8. Επισημάνετε τα κελιά D6 έως F11 στο φύλλο εργασίας για να εισαγάγετε την περιοχή στο παράθυρο διαλόγου.

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

   

Έναρξη της Nested MATCH Function

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

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

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

Το Lookup_value δέχεται μόνο ένα κριτήριο αναζήτησης ή έναν όρο. Για να αναζητήσετε πολλαπλά κριτήρια, επεκτείνετε την τιμή Lookup_value συνενώνοντας ή ενώνοντας δύο ή περισσότερες αναφορές κελιών χρησιμοποιώντας το σύμβολο συμπλεκτικής τιμής (&).

1. Στο πλαίσιο διαλόγου Function Arguments, τοποθετήστε τον κέρσορα στο πλαίσιο κειμένου Row_num.
2. Εισαγάγετε MATCH(.
3. Επιλέξτε το κελί D3 για να εισαγάγετε αυτήν την αναφορά κελιού στο πλαίσιο διαλόγου.
4. Εισαγάγετε & (το συμπλεκτικό σύμβολο) μετά την αναφορά κελιού D3 για να προσθέσετε μια δεύτερη αναφορά κελιού.
5. Επιλέξτε το κελί E3 για να εισαγάγετε τη δεύτερη αναφορά κελιού.

6. Εισαγάγετε , (ένα κόμμα) μετά την αναφορά του κελιού E3 για να ολοκληρώσετε την καταχώριση του ορίσματος Lookup_value της συνάρτησης MATCH.

   

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

Ολοκληρώστε τη συνάρτηση Ένθετου MATCH

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

Επειδή προσδιορίστηκαν δύο πεδία αναζήτησης στο όρισμα Lookup_array, το ίδιο πρέπει να γίνει και για το Lookup_array. Η συνάρτηση MATCH αναζητά μόνο έναν πίνακα για κάθε καθορισμένο όρο. Για να εισαγάγετε πολλούς πίνακες, χρησιμοποιήστε το συμπλεκτικό σύμβολο για να συνδέσετε τους πίνακες μεταξύ τους.

1. Τοποθετήστε τον κέρσορα στο τέλος των δεδομένων στο πλαίσιο κειμένου Row_num. Ο δρομέας εμφανίζεται μετά το κόμμα στο τέλος της τρέχουσας καταχώρισης.
2. Επισημάνετε τα κελιά D6 έως D11 στο φύλλο εργασίας για να εισαγάγετε το εύρος. Αυτό το εύρος είναι ο πρώτος πίνακας που αναζητά η συνάρτηση.
3. Εισαγάγετε & (ένα σύμφωνο) μετά τις αναφορές κελιών D6:D11. Αυτό το σύμβολο προκαλεί τη συνάρτηση να αναζητήσει δύο πίνακες.
4. Επισημάνετε τα κελιά E6 έως E11 στο φύλλο εργασίας για να εισαγάγετε το εύρος. Αυτό το εύρος είναι ο δεύτερος πίνακας που αναζητά η συνάρτηση.

5. Εισαγάγετε , (ένα κόμμα) μετά την αναφορά κελιού E3 για να ολοκληρώσετε την καταχώριση του ορίσματος Lookup_array της συνάρτησης MATCH.

   

6. Αφήστε ανοιχτό το πλαίσιο διαλόγου για το επόμενο βήμα στον οδηγό.

Προσθήκη του ορίσματος τύπου MATCH

Το τρίτο και τελευταίο όρισμα της συνάρτησης MATCH είναι το όρισμα Match_type. Αυτό το όρισμα λέει στο Excel πώς να αντιστοιχίσει το Lookup_value με τις τιμές στον πίνακα αναζήτησης. Οι διαθέσιμες επιλογές είναι 1, 0 ή -1.

Αυτό το όρισμα είναι προαιρετικό. Εάν παραλειφθεί, η συνάρτηση χρησιμοποιεί την προεπιλεγμένη τιμή 1.

• Εάν Match_type=1 ή παραλειφθεί, το MATCH βρίσκει τη μεγαλύτερη τιμή που είναι μικρότερη ή ίση με την τιμή_αναζήτησης. Τα δεδομένα Lookup_array πρέπει να ταξινομηθούν με αύξουσα σειρά.
• Αν Match_type=0, το MATCH βρίσκει την πρώτη τιμή που είναι ίση με την τιμή_Αναζήτησης. Τα δεδομένα του Lookup_array μπορούν να ταξινομηθούν με οποιαδήποτε σειρά.
• Αν Match_type=-1, το MATCH βρίσκει τη μικρότερη τιμή που είναι μεγαλύτερη ή ίση με την τιμή_Αναζήτησης. Τα δεδομένα του Lookup_array πρέπει να ταξινομηθούν με φθίνουσα σειρά.

Εισαγάγετε αυτά τα βήματα μετά το κόμμα που πληκτρολογήσατε στο προηγούμενο βήμα στη γραμμή Row_num στη συνάρτηση INDEX:

1. Εισαγάγετε 0 (ένα μηδέν) μετά το κόμμα στο πλαίσιο κειμένου Row_num. Αυτός ο αριθμός αναγκάζει την ένθετη συνάρτηση να επιστρέψει ακριβείς αντιστοιχίσεις με τους όρους που έχουν εισαχθεί στα κελιά D3 και E3.

2. Εισαγάγετε ) (ένα στρογγυλό στήριγμα κλεισίματος) για να ολοκληρώσετε τη συνάρτηση MATCH.

   

3. Αφήστε ανοιχτό το πλαίσιο διαλόγου για το επόμενο βήμα στον οδηγό.

Ολοκλήρωση της συνάρτησης INDEX

Η συνάρτηση MATCH ολοκληρώθηκε. Ήρθε η ώρα να μεταβείτε στο πλαίσιο κειμένου Column_num του πλαισίου διαλόγου και να εισαγάγετε το τελευταίο όρισμα για τη συνάρτηση INDEX. Αυτό το όρισμα λέει στο Excel ότι ο αριθμός στήλης βρίσκεται στην περιοχή D6 έως F11. Αυτή η περιοχή είναι όπου βρίσκει τις πληροφορίες που επιστρέφονται από τη συνάρτηση. Σε αυτήν την περίπτωση, ένας προμηθευτής για γραφικά στοιχεία τιτανίου.

1. Τοποθετήστε τον κέρσορα στο πλαίσιο κειμένου Column_num.

2. Εισαγάγετε 3 (ο αριθμός τρία). Αυτός ο αριθμός λέει στον τύπο να αναζητήσει δεδομένα στην τρίτη στήλη του εύρους D6 έως F11.

   

3. Αφήστε ανοιχτό το πλαίσιο διαλόγου για το επόμενο βήμα στον οδηγό.

Δημιουργία του τύπου Array

Πριν κλείσετε το παράθυρο διαλόγου, μετατρέψτε την ένθετη συνάρτηση σε τύπο πίνακα. Αυτός ο πίνακας επιτρέπει στη συνάρτηση να αναζητήσει πολλούς όρους στον πίνακα δεδομένων. Σε αυτό το σεμινάριο, αντιστοιχίζονται δύο όροι: Widgets από τη στήλη 1 και Titanium από τη στήλη 2.

Για να δημιουργήσετε έναν τύπο πίνακα στο Excel, πατήστε τα CTRL, SHIFT και ENTERπλήκτρα ταυτόχρονα. Αφού πατηθεί, η συνάρτηση περιβάλλεται από σγουρά στηρίγματα, υποδεικνύοντας ότι η συνάρτηση είναι πλέον πίνακας.

1. Επιλέξτε OK για να κλείσετε το παράθυρο διαλόγου. Στο Excel για Mac, επιλέξτε Τέλος.
2. Επιλέξτε το κελί F3 για να προβάλετε τον τύπο και, στη συνέχεια, τοποθετήστε τον κέρσορα στο τέλος του τύπου στη γραμμή τύπων.
3. Για να μετατρέψετε τον τύπο σε πίνακα, πατήστε CTRL+ SHIFT+ ENTER.
4. Εμφανίζεται ένα σφάλμα N/A στο κελί F3. Αυτό είναι το κελί όπου εισήχθη η συνάρτηση.

5. Το σφάλμα N/A εμφανίζεται στο κελί F3 επειδή τα κελιά D3 και E3 είναι κενά. Τα D3 και E3 είναι τα κελιά όπου αναζητά η συνάρτηση για να βρει το Lookup_value. Αφού προστεθούν δεδομένα σε αυτά τα δύο κελιά, το σφάλμα αντικαθίσταται από πληροφορίες από τη βάση δεδομένων.

   

Προσθήκη των κριτηρίων αναζήτησης

Το τελευταίο βήμα είναι να προσθέσετε τους όρους αναζήτησης στο φύλλο εργασίας. Αυτό το βήμα ταιριάζει με τους όρους Widgets από τη στήλη 1 και Titanium από τη στήλη 2.

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

1. Επιλέξτε το κελί D3.
2. Εισαγάγετε Γραφικά στοιχεία.
3. Επιλέξτε το κελί E3.
4. Πληκτρολογήστε Titanium και πατήστε Enter.
5. Το όνομα του προμηθευτή, Widgets Inc., εμφανίζεται στο κελί F3. Αυτός είναι ο μόνος προμηθευτής που πουλά Titanium Widgets.

6. Επιλέξτε το κελί F3. Η συνάρτηση εμφανίζεται στη γραμμή τύπων πάνω από το φύλλο εργασίας.

   {=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
   Σε αυτό το παράδειγμα, υπάρχει μόνο ένας προμηθευτής για γραφικά στοιχεία τιτανίου. Εάν υπήρχαν περισσότεροι από ένας προμηθευτές, ο προμηθευτής που αναφέρεται πρώτος στη βάση δεδομένων επιστρέφεται από τη συνάρτηση.