Excel Φόρμουλα αναζήτησης με πολλαπλά κριτήρια

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

Ο τύπος συστοιχίας περιλαμβάνει την εμφύτευση της συνάρτησης MATCH μέσα στη λειτουργία INDEX .

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

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

01 από 09

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

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

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

• Εισαγάγετε το ανώτερο εύρος δεδομένων στα κελιά D1 έως F2
• Εισάγετε τη δεύτερη περιοχή στα κελιά D5 έως F11

Οι σειρές 3 και 4 παραμένουν κενές για να ικανοποιήσουν τη φόρμα συστοιχιών που δημιουργήθηκε κατά τη διάρκεια αυτού του σεμιναρίου.

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

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

02 του 09

Ξεκινώντας τη λειτουργία INDEX

Η συνάρτηση INDEX είναι μία από τις ελάχιστες στο Excel που έχει πολλές φόρμες. Η λειτουργία έχει μια μορφή πίνακα και μια φόρμα αναφοράς .

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

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

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

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

1. Κάντε κλικ στο κελί F3 για να γίνει το ενεργό κελί . Εδώ μπαίνουμε στην εντοιχισμένη λειτουργία.
2. Κάντε κλικ στην καρτέλα Φόρμες του μενού κορδέλας .
3. Επιλέξτε Αναζήτηση και αναφοράς από την κορδέλα για να ανοίξετε την αναπτυσσόμενη λίστα λειτουργιών.
4. Κάντε κλικ στο INDEX στη λίστα για να εμφανιστεί το παράθυρο διαλόγου Επιλογή παραμέτρων .
5. Επιλέξτε το πεδίο array, row_num, col_num στο παράθυρο διαλόγου.
6. Κάντε κλικ στο κουμπί OK για να ανοίξετε το παράθυρο διαλόγου λειτουργίας INDEX.

03 του 09

Εισαγωγή του Argument Array της Λειτουργίας Καταστάσεων INDEX

Το πρώτο απαιτούμενο όρισμα είναι το όρισμα Array. Αυτό το όρισμα καθορίζει την περιοχή των κυττάρων προς αναζήτηση των επιθυμητών δεδομένων.

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

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

1. Στο παράθυρο διαλόγου Λειτουργία INDEX, κάντε κλικ στη γραμμή Array .
2. Επισημάνετε τα κελιά D6 έως F11 στο φύλλο εργασίας για να εισαγάγετε την περιοχή στο παράθυρο διαλόγου.

04 του 09

Ξεκινώντας τη λειτουργία του Vested MATCH

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

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

Σε αυτό το tutorial, η ένθετη συνάρτηση MATCH και τα επιχειρήματά της θα εισαχθούν στη δεύτερη γραμμή του πλαισίου διαλόγου Λειτουργία INDEX - η γραμμή Row_num .

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

Εισαγωγή του Argument Lookup_value της Λειτουργίας MATCH

Το πρώτο βήμα για την εισαγωγή της ενωμένης λειτουργίας MATCH είναι να εισαγάγετε το επιχείρημα Lookup_value .

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

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

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

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

1. Στο παράθυρο διαλόγου Λειτουργία INDEX, κάντε κλικ στη γραμμή Row_num .
2. Πληκτρολογήστε την αντιστοιχία ονόματος λειτουργίας ακολουθούμενη από ανοιχτό στρογγυλό βραχίονα " ( "
3. Κάντε κλικ στο κελί D3 για να εισάγετε εκείνη την αναφορά κυττάρων στο παράθυρο διαλόγου.
4. Πληκτρολογήστε ένα στοιχείο " & " μετά την αναφορά κυττάρων D3 για να προσθέσετε μια δεύτερη αναφορά κυψέλης.
5. Κάντε κλικ στο κελί E3 για να εισαγάγετε αυτή τη δεύτερη αναφορά κυψελών στο παράθυρο διαλόγου.
6. Πληκτρολογήστε ένα κόμμα "," μετά την αναφορά κυψέλης E3 για να ολοκληρώσετε την καταχώρηση του argument της συνάρτησης MATCH του Lookup_value .
7. Αφήστε το παράθυρο διαλόγου λειτουργίας INDEX ανοιχτό για το επόμενο βήμα στο μάθημα.

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

05 του 09

Προσθήκη του Lookup_array για τη συνάρτηση MATCH

Αυτό το βήμα καλύπτει την προσθήκη του επιχειρήματος Lookup_array για τη λειτουργία MATCH.

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

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

Για να εισαγάγουμε πολλαπλές συστοιχίες, χρησιμοποιούμε και πάλι το ampersand " & " για να συγκολλήσουμε τις συστοιχίες μαζί.

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

Αυτά τα βήματα πρέπει να εισαχθούν μετά το κόμμα που εισήχθη στο προηγούμενο βήμα στη γραμμή Row_num στο παράθυρο διαλόγου λειτουργίας INDEX.

1. Κάντε κλικ στη γραμμή Row_num μετά το κόμμα για να τοποθετήσετε το σημείο εισαγωγής στο τέλος της τρέχουσας καταχώρησης.
2. Επισημάνετε τα κελιά D6 έως D11 στο φύλλο εργασίας για να εισάγετε το εύρος. Αυτός είναι ο πρώτος πίνακας με τη λειτουργία αναζήτησης.
3. Πληκτρολογήστε ένα στοιχείο " & " μετά τις αναφορές κυττάρων D6: D11 επειδή θέλουμε η λειτουργία να αναζητήσει δύο πίνακες.
4. Επισημάνετε τα κελιά E6 έως E11 στο φύλλο εργασίας για να εισέλθετε στην περιοχή. Αυτή είναι η δεύτερη σειρά, η λειτουργία είναι η αναζήτηση.
5. Πληκτρολογήστε ένα κόμμα "" ", μετά την αναφορά κυψέλης E3, για να ολοκληρώσετε την εισαγωγή του argument Lookup_array της συνάρτησης MATCH.
6. Αφήστε το παράθυρο διαλόγου λειτουργίας INDEX ανοιχτό για το επόμενο βήμα στο μάθημα.

06 του 09

Προσθήκη του τύπου αντιστοίχισης και συμπλήρωση της λειτουργίας 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_value. Τα δεδομένα του Lookup_array πρέπει να ταξινομούνται κατά φθίνουσα σειρά.

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

Αυτά τα βήματα πρέπει να εισαχθούν μετά το κόμμα που εισήχθη στο προηγούμενο βήμα στη γραμμή Row_num στο παράθυρο διαλόγου λειτουργίας INDEX.

1. Μετά το κόμμα στη γραμμή Row_num , πληκτρολογήστε ένα μηδέν " 0 ", δεδομένου ότι θέλουμε η ενωμένη λειτουργία να επαναφέρει ακριβείς αντιστοιχίσεις στους όρους που εισάγουμε στα κελιά D3 και E3.
2. Πληκτρολογήστε ένα στρογγυλό βραχίονα κλεισίματος " ) " για να ολοκληρώσετε τη λειτουργία MATCH.
3. Αφήστε το παράθυρο διαλόγου λειτουργίας INDEX ανοιχτό για το επόμενο βήμα στο μάθημα.

07 του 09

Επιστροφή στη λειτουργία INDEX

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

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

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

1. Κάντε κλικ στη γραμμή Column_num στο παράθυρο διαλόγου.
2. Εισαγάγετε τον αριθμό τριών " 3 " (χωρίς εισαγωγικά) σε αυτή τη γραμμή αφού ψάχνετε δεδομένα στην τρίτη στήλη της περιοχής D6 έως F11.
3. Κάντε κλικ στο κουμπί OK ή κλείστε το παράθυρο διαλόγου λειτουργίας INDEX. Πρέπει να παραμείνει ανοιχτό για το επόμενο βήμα στο μάθημα - δημιουργώντας τη φόρμα πίνακα .

08 από 09

Δημιουργία της φόρμας πίνακα

Πριν από το κλείσιμο του πλαισίου διαλόγου πρέπει να μετατρέψουμε τη λειτουργία μας σε ένθετο.

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

Η δημιουργία μιας φόρμας πίνακα στο Excel γίνεται ταυτόχρονα πατώντας ταυτόχρονα τα πλήκτρα CTRL , SHIFT και ENTER στο πληκτρολόγιο.

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

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

1. Με το ολοκληρωμένο παράθυρο διαλόγου που εξακολουθεί να είναι ανοιχτό από το προηγούμενο βήμα αυτού του φροντιστηρίου, πατήστε και κρατήστε πατημένο τα πλήκτρα CTRL και SHIFT στο πληκτρολόγιο, στη συνέχεια πατήστε και αφήστε το πλήκτρο ENTER .
2. Εάν γίνει σωστά, το παράθυρο διαλόγου θα κλείσει και θα εμφανιστεί ένα σφάλμα # N / A στο κελί F3 - το κελί όπου εισήγαγε τη λειτουργία.
3. Το σφάλμα # N / A εμφανίζεται στο κελί F3 επειδή τα κελιά D3 και E3 είναι κενά. D3 και E3 είναι τα κελιά στα οποία είπαμε τη συνάρτηση να βρει τις τιμές Lookup_ στο βήμα 5 του tutorial. Μόλις προστεθούν δεδομένα σε αυτά τα δύο κελιά, το σφάλμα θα αντικατασταθεί από πληροφορίες από τη βάση δεδομένων .

09 του 09

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

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

Όπως αναφέρθηκε στο προηγούμενο βήμα, προσπαθούμε να ταιριάξουμε τους όρους Widgets από τη στήλη 1 και το Titanium από τη στήλη 2.

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

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

1. Κάντε κλικ στο κελί D3.
2. Πληκτρολογήστε Widgets και πατήστε το πλήκτρο Enter στο πληκτρολόγιο.
3. Κάντε κλικ στο κελί E3.
4. Πληκτρολογήστε Titanium και πατήστε το πλήκτρο Enter στο πληκτρολόγιο.
5. Το όνομα του προμηθευτή Widgets Inc. θα πρέπει να εμφανίζεται στο κελί F3 - η θέση της λειτουργίας, δεδομένου ότι είναι ο μόνος προμηθευτής στον κατάλογο που πωλεί τα Widgets Titanium.
6. Όταν κάνετε κλικ στο κελί F3 την πλήρη λειτουργία
   {= ΔΕΙΚΤΕΣ (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   εμφανίζεται στη γραμμή τύπων πάνω από το φύλλο εργασίας .

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