Χρησιμοποιώντας μια φόρμα πίνακα στο Excel μπορούμε να δημιουργήσουμε έναν τύπο αναζήτησης που χρησιμοποιεί πολλαπλά κριτήρια για να βρει πληροφορίες σε βάση δεδομένων ή πίνακα δεδομένων. Ο τύπος συστοιχίας περιλαμβάνει την εμφύτευση της συνάρτησης MATCH μέσα στη λειτουργία INDEX.
Αυτό το σεμινάριο περιλαμβάνει ένα βήμα προς βήμα παράδειγμα της δημιουργίας ενός φόρου αναζήτησης που χρησιμοποιεί πολλαπλά κριτήρια για να βρει έναν προμηθευτή τουτιτανίου Widgets σε μια βάση δεδομένων δείγματος. Ακολουθήστε μαζί για να μάθετε πόσο εύκολο είναι να δημιουργήσετε τους δικούς σας προσαρμοσμένους τύπους αναζήτησης.
Προετοιμασία του βιβλίου εργασίας του Excel με δεδομένα
Για να ακολουθήσετε τα βήματα αυτού του σεμιναρίου, θα πρέπει να ξεκινήσετε εισάγοντας τα δείγματα δεδομένων στα ακόλουθα κελιά, όπως φαίνεται στην παραπάνω εικόνα. Οι σειρές 3 και 4 παραμένουν κενές για να ικανοποιήσουν τη φόρμα συστοιχιών που δημιουργήθηκε κατά τη διάρκεια αυτού του σεμιναρίου.
- Εισαγάγετε το μέγιστο εύρος δεδομένων σε κελιά D1 προς το F2
- Εισάγετε τη δεύτερη περιοχή στα κελιά D5 προς το F11
Το σεμινάριο δεν περιλαμβάνει τη μορφοποίηση που φαίνεται στην εικόνα, αλλά αυτό δεν θα επηρεάσει τον τρόπο με τον οποίο λειτουργεί ο τύπος αναζήτησης. Πληροφορίες σχετικά με τις επιλογές μορφοποίησης παρόμοιες με αυτές που παρουσιάζονται παραπάνω είναι διαθέσιμες σε αυτό το Βασικό Εκπαιδευτικό Πρόγραμμα Διαμόρφωσης Excel.
02 από 08Δημιουργήστε μια συνάρτηση INDEX στο Excel
ο ΔΕΙΚΤΗΣ είναι ένα από τα λίγα στο Excel που έχει πολλές μορφές. Η λειτουργία έχει ένα Φύλλο πίνακα και ένα Έντυπο αναφοράς. Η φόρμα Array επιστρέφει τα πραγματικά δεδομένα από μια βάση δεδομένων ή έναν πίνακα δεδομένων, ενώ η φόρμα αναφοράς σας δίνει την αναφορά κυψελών ή τη θέση των δεδομένων στον πίνακα.
Σε αυτό το σεμινάριο, θα χρησιμοποιήσουμε τη φόρμα Array, διότι θέλουμε να γνωρίζουμε το όνομα του προμηθευτή για widgets τιτανίου αντί για την αναφορά κυψελών σε αυτόν τον προμηθευτή στη βάση δεδομένων μας. Ακολουθήστε αυτά τα βήματα για να δημιουργήσετε τη λειτουργία INDEX:
- Κάντε κλικ στο κελί F3 για να γίνει το ενεργό κελί - εδώ θα εισέλθουμε στην εντοιχισμένη λειτουργία.
- Κάνε κλικ στο ΜΑΘΗΜΑΤΙΚΟΙ τυποι καρτέλα του μενού κορδέλας.
- Επιλέγω Αναζήτηση και αναφορά από τη γραμμή κορδέλας για να ανοίξετε την αναπτυσσόμενη λειτουργία.
- Κάντε κλικ στο ΔΕΙΚΤΗΣ στη λίστα για να εμφανιστεί το Επιλέξτε Arguments κουτί διαλόγου.
- Επιλέξτε το array, row_num, col_num στο παράθυρο διαλόγου.
- Κάντε κλικ Εντάξει για να ανοίξετε το παράθυρο διαλόγου λειτουργιών INDEX. αυτό θα ανοίξει το Formula Builder στο Excel.
- Στο Κατασκευαστής φορμών, Κάνε κλικ στο Παράταξη γραμμή.
- Επισημάνετε κελιά D6 προς το F11, κάνοντας κλικ και σύροντας, στο φύλλο εργασίας για να εισαγάγετε την περιοχή στον οικοδόμο.
Σε παλαιότερες εκδόσεις του Excel Κατασκευαστής φορμών θα αντικατασταθεί από το Λειτουργικά επιχειρήματα παράθυρο. Εφαρμόστε τα ίδια βήματα με αυτά που αναφέρονται σε αυτό το σεμινάριο στο παράθυρο.
03 του 08Ξεκινήστε τη λειτουργία Vested MATCH
Όταν φτιάχνετε μια συνάρτηση μέσα στο άλλο, δεν είναι δυνατό να ανοίξετε τον δημιουργό τύπων δεύτερης ή ένθετης λειτουργίας o εισαγάγετε τα απαραίτητα επιχειρήματα. Η ένθετη λειτουργία πρέπει να πληκτρολογηθεί ως μία από τις ταξινομημένες στην πρώτη λειτουργία.
Όταν εισάγετε χειροκίνητα τις λειτουργίες, τα επιχειρήματα της λειτουργίας διαχωρίζονται το ένα από το άλλο με κόμμα.
Το πρώτο βήμα για την είσοδο στη λειτουργία IMET MATCH είναι η εισαγωγή του Lookup_value διαφωνία. Η τιμή Lookup_value θα είναι η αναφορά τοποθεσίας ή κυττάρων για τον όρο αναζήτησης που θέλουμε να αντιστοιχίσουμε στη βάση δεδομένων.
Κανονικά η τιμή Lookup_value δέχεται μόνο ένα κριτήριο ή έναν όρο αναζήτησης. Για να αναζητήσουμε πολλαπλά κριτήρια, πρέπει να επεκτείνουμε την τιμή Lookup_value. tη δική του πραγματοποιείται συνδυάζοντας ή συνδέοντας δύο ή περισσότερες αναφορές κυττάρων χρησιμοποιώντας το σύμβολο ampersand - &.
- Στο Κατασκευαστής φορμών, Κάνε κλικ στο Row_num γραμμή.
- Πληκτρολογήστε το όνομα της λειτουργίας ΑΓΩΝΑΣ ακολουθούμενο από ανοιχτό στρογγυλό βραχίονα.
- Κάντε κλικ στο κελί D3 για να εισαγάγετε αυτήν την αναφορά κυττάρων στο παράθυρο διαλόγου.
- Πληκτρολογήστε ένα μήνυμα και μετά την αναφορά κελί D3 για να προσθέσετε μια δεύτερη αναφορά κυψέλης.
- Κάντε κλικ στο κελί Ε3 για να εισαγάγετε αυτή τη δεύτερη αναφορά κυψέλης.
- Πληκτρολογήστε ένα κόμμα μετά την αναφορά κυττάρων Ε3 για να ολοκληρώσετε την καταχώρηση της λειτουργίας MATCH Lookup_value διαφωνία.
Στο τελευταίο βήμα του σεμιναρίου, το μας Τιμές_αναζήτησης θα εισαχθούν στα κελιά D3 και E3 του φύλλου εργασίας.
04 του 08Ολοκληρώστε τη λειτουργία του Nested MATCH
Αυτό το βήμα καλύπτει την προσθήκη του Lookup_array επιχειρηματολογία για τα ένθετα ΑΓΩΝΑΣ λειτουργία. Το Lookup_array είναι το εύρος των κελιών που θα αναζητήσει η συνάρτηση MATCH για να βρει το παράθυρο Lookup_value που προστέθηκε στο προηγούμενο βήμα του εκπαιδευτικού προγράμματος.
Δεδομένου ότι εντοπίσαμε δύο πεδία αναζήτησης στο παράθυρο Lookup_array, πρέπει να κάνουμε το ίδιο για το Lookup_array. Η συνάρτηση MATCH αναζητά μόνο έναν πίνακα για κάθε καθορισμένο όρο, εισάγοντας έτσι πολλαπλές συστοιχίες και πάλι χρησιμοποιούμε το ampersand για να συγκολλήσουμε τους πίνακες μαζί.
Αυτά τα βήματα πρέπει να εισαχθούν μετά το κόμμα που εισήχθη στο προηγούμενο βήμα στο Row_num γραμμή στο ΔΕΙΚΤΗΣ λειτουργία.
- Κάνε κλικ στο Row_num γραμμή μετά το κόμμα για να τοποθετήσετε το σημείο εισαγωγής στο τέλος της τρέχουσας καταχώρησης.
- Επισημάνετε κελιά D6 προς το D11 στο φύλλο εργασίας για να εισέλθετε στην περιοχή - αυτή είναι η πρώτη σειρά που αναζητά η λειτουργία.
- Πληκτρολογήστε ένα μήνυμα και μετά τις αναφορές κελιών D6: D11 επειδή θέλουμε η λειτουργία να αναζητήσει δύο συστοιχίες.
- Επισημάνετε κελιά Ε6 προς το Ε11 στο φύλλο εργασίας για να εισέλθουν στην περιοχή - αυτή είναι η δεύτερη σειρά που είναι η λειτουργία αναζήτησης.
- Πληκτρολογήστε ένα κόμμα μετά την αναφορά κυττάρων Ε3 να συμπληρώσετε την καταχώρηση του ΑΓΩΝΑΣ λειτουργία Lookup_array διαφωνία.
- ΑΣΕ το ΔΕΙΚΤΗΣ λειτουργικό παράθυρο διαλόγου για το επόμενο βήμα στο μάθημα.
Προσθέστε το Argument τύπου 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 γραμμή στο ΔΕΙΚΤΗΣ λειτουργία.
- Μετά το κόμμα στο Row_num γραμμή, πληκτρολογήστε ένα μηδέν - 0 - δεδομένου ότι θέλουμε η ενωμένη λειτουργία να επιστρέψει ακριβείς αντιστοιχίσεις στους όρους που καταχωρίσαμε στα κελιά D3 και Ε3.
- Πληκτρολογήστε ένα στρογγυλό βραχίονα - ) - να συμπληρώσετε το ΑΓΩΝΑΣ λειτουργία.
- ΑΣΕ το Κατασκευαστής φορμών ανοικτή για το επόμενο βήμα στο σεμινάριο.
Ολοκλήρωση της λειτουργίας INDEX
Τώρα που το ΑΓΩΝΑΣ λειτουργία γίνεται θα μεταβούμε στην τρίτη γραμμή του Formula Builder και θα εισάγουμε το τελευταίο επιχείρημα για το ΔΕΙΚΤΗΣ λειτουργία. Αυτό το τρίτο και τελευταίο επιχείρημα είναι το Column_num το οποίο λέει στο Excel τον αριθμό της στήλης στην περιοχή D6 προς το F11. αυτό είναι όπου θα βρείτε τις πληροφορίες που θέλουμε να επιστρέψουμε από τη λειτουργία. Στην περίπτωση αυτή, ένας προμηθευτής για τιτανίου widgets.
- Κάνε κλικ στο Column_num γραμμή.
- Πληκτρολογήστε τον αριθμό τριών - 3 - σε αυτή τη γραμμή, δεδομένου ότι ψάχνουμε για δεδομένα στην τρίτη στήλη του εύρους D6 προς το F11.
- Και πάλι, αφήστε το Κατασκευαστής φορμών ανοικτή για το επόμενο βήμα στο σεμινάριο.
Δημιουργία της φόρμας πίνακα
Πριν κλείσετε το Κατασκευαστής φορμών, πρέπει να μετατρέψουμε την ένθετη λειτουργία μας σε μια συνταγή πίνακα. αυτό είναι που επιτρέπει στη λειτουργία μας να ψάχνει για πολλαπλούς όρους στον πίνακα των δεδομένων. Σε αυτό το σεμινάριο, θέλουμε να ταιριάξουμε δύο όρους: Widgets από τη στήλη 1 και Τιτάνιο από τη στήλη 2.
Η δημιουργία μιας φόρμας πίνακα στο Excel γίνεται με το πάτημα του κουμπιού CTRL, ΒΑΡΔΙΑ, και ΕΙΣΑΓΩ πλήκτρα στο πληκτρολόγιο ταυτόχρονα. Μόλις πιεστεί, η λειτουργία θα περιβάλλεται από σγουρά, υποδηλώνοντας ότι η λειτουργία είναι τώρα μια συστοιχία.
- Κλείστε το Κατασκευαστής φορμών κάνοντας κλικ στο Εγινε κουμπί.
- Στη συνέχεια, επιλέξτε κελιά F4 και πατήστε το Εισαγω για να δείτε τον τύπο.
- Για να μετατρέψετε τον τύπο σε πίνακα, πατήστε ταυτόχρονα CTRL + SHIFT + ENTER στο πληκτρολόγιό σας.
- Εάν γίνει σωστά a # N / A θα εμφανιστεί σφάλμα στο κελί F3 - το κελί όπου εισήγαμε τη λειτουργία.
- ο # N / A Εμφανίζεται σφάλμα στο κελί F3 γιατί τα κελιά D3 και Ε3 είναι κενές. D3 και E3 είναι τα κελιά στα οποία είπαμε τη λειτουργία να βρούμε το Τιμές_αναζήτησης. Μόλις προστεθούν δεδομένα σε αυτά τα δύο κελιά, το σφάλμα θα αντικατασταθεί από πληροφορίες από τη βάση δεδομένων.
Προσθήκη των κριτηρίων αναζήτησης
Τέλος, θα προσθέσουμε τους όρους αναζήτησης στο φύλλο εργασίας μας. Όπως αναφέρθηκε στο προηγούμενο βήμα, προσπαθούμε να ταιριάξουμε τους όρους Widgets από τη στήλη 1 και Τιτάνιο από τη στήλη 2. Εάν και μόνο εάν ο τύπος μας βρίσκει μια αντιστοίχιση και για τους δύο όρους στις κατάλληλες στήλες της βάσης δεδομένων, θα επιστρέψει την τιμή από την τρίτη στήλη.
- Κάντε κλικ στο κελί D3.
- Τύπος Widgets και πατήστε το Εισαγω στο πληκτρολόγιο.
- Κάντε κλικ στο κελί Ε3.
- Τύπος Τιτάνιο και πατήστε το Εισαγω στο πληκτρολόγιο.
- Το όνομα του προμηθευτή Widgets Inc. θα πρέπει να εμφανίζεται στο κελί F3 - η θέση της λειτουργίας, δεδομένου ότι είναι ο μόνος προμηθευτής στον κατάλογο που πωλεί Widgets Titanium.
- Όταν κάνετε κλικ στο κελί F3, η πλήρης λειτουργία εμφανίζεται στη γραμμή τύπων πάνω από το φύλλο εργασίας.
{= ΔΕΙΚΤΕΣ (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
Στο παράδειγμά μας, υπήρχε μόνο ένας προμηθευτής για widgets τιτανίου. Αν υπήρχαν περισσότεροι του ενός προμηθευτές, ο προμηθευτής που αναφέρεται πρώτα στην βάση δεδομένων επιστρέφεται από τη λειτουργία.
