Το φίλτρο Excel είναι μια από τις πιο χρησιμοποιούμενες λειτουργίες όταν εργάζεστε με δεδομένα. Σε αυτήν την ανάρτηση ιστολογίου, θα σας δείξω πώς να δημιουργήσετε ένα πλαίσιο δυναμικής αναζήτησης φίλτρου Excel, έτσι ώστε να φιλτράρει τα δεδομένα με βάση αυτό που πληκτρολογείτε στο πλαίσιο αναζήτησης.
Κάτι όπως φαίνεται παρακάτω:
Υπάρχει μια διπλή λειτουργικότητα σε αυτό - μπορείτε να επιλέξετε το όνομα μιας χώρας από την αναπτυσσόμενη λίστα ή μπορείτε να εισαγάγετε μη αυτόματα τα δεδομένα στο πλαίσιο αναζήτησης και θα σας εμφανίσει όλες τις αντίστοιχες εγγραφές. Για παράδειγμα, όταν πληκτρολογείτε "I" σας δίνει όλα τα ονόματα των χωρών με το αλφάβητο I σε αυτό.
Παρακολουθήστε βίντεο - Δημιουργία πλαισίου αναζήτησης δυναμικού φίλτρου Excel
Δημιουργία πλαισίου αναζήτησης δυναμικού φίλτρου Excel
Αυτό το φίλτρο δυναμικού Excel μπορεί να δημιουργηθεί σε 3 βήματα:
- Λήψη μιας μοναδικής λίστας στοιχείων (χώρες σε αυτήν την περίπτωση). Αυτό θα χρησιμοποιηθεί για τη δημιουργία του αναπτυσσόμενου μενού.
- Δημιουργία του πλαισίου αναζήτησης. Εδώ έχω χρησιμοποιήσει ένα Combo Box (ActiveX Control).
- Ρύθμιση των Δεδομένων. Εδώ θα χρησιμοποιούσα τρεις στήλες βοηθού με τύπους για να εξαγάγω τα αντίστοιχα δεδομένα.
Δείτε πώς φαίνονται τα ακατέργαστα δεδομένα:
ΧΡΗΣΙΜΗ ΣΥΜΒΟΥΛΗ: Είναι σχεδόν πάντα καλή ιδέα να μετατρέψετε τα δεδομένα σας σε πίνακα Excel. Μπορείτε να το κάνετε αυτό επιλέγοντας οποιοδήποτε κελί στο σύνολο δεδομένων και χρησιμοποιώντας τη συντόμευση πληκτρολογίου Control + T.
Βήμα 1 - Λήψη μιας μοναδικής λίστας στοιχείων
- Επιλέξτε όλες τις χώρες και επικολλήστε τις σε ένα νέο φύλλο εργασίας.
- Επιλέξτε τη λίστα χωρών -> Μετάβαση στα δεδομένα -> Κατάργηση διπλότυπων.
- Στο παράθυρο διαλόγου Κατάργηση διπλότυπων, επιλέξτε τη στήλη στην οποία έχετε τη λίστα και κάντε κλικ στο κουμπί OK. Αυτό θα αφαιρέσει τα διπλότυπα και θα σας δώσει μια μοναδική λίστα όπως φαίνεται παρακάτω:
- Ένα επιπλέον βήμα είναι να δημιουργήσετε μια ονομαστική περιοχή για αυτήν τη μοναδική λίστα. Για να το κάνω αυτό:
- Μεταβείτε στην καρτέλα Τύπος -> Ορισμός ονόματος
- Στο πλαίσιο καθορισμού ονόματος πλαίσιο διαλόγου:
- Όνομα: CountryList
- Πεδίο εφαρμογής: Τετράδιο εργασίας
- Αναφέρεται σε: = UniqueList! $ A $ 2: $ A $ 9 (έχω τη λίστα σε ξεχωριστή καρτέλα με το όνομα UniqueList στο A2: A9. Μπορείτε να ανατρέξετε όπου βρίσκεται η μοναδική σας λίστα)
ΣΗΜΕΙΩΣΗ: Εάν χρησιμοποιείτε τη μέθοδο «Κατάργηση διπλότυπων» και επεκτείνετε τα δεδομένα σας για να προσθέσετε περισσότερες εγγραφές και νέες χώρες, θα πρέπει να επαναλάβετε αυτό το βήμα ξανά. Εναλλακτικά, μπορείτε επίσης να χρησιμοποιήσετε έναν τύπο για να κάνετε αυτήν τη διαδικασία δυναμική.
Βήμα 2 - Δημιουργία του πλαισίου αναζήτησης δυναμικού φίλτρου Excel
Για να λειτουργήσει αυτή η τεχνική, θα πρέπει να δημιουργήσουμε ένα «Πλαίσιο αναζήτησης» και να το συνδέσουμε με ένα κελί.
Μπορούμε να χρησιμοποιήσουμε το Combo Box στο Excel για να δημιουργήσουμε αυτό το φίλτρο του πλαισίου αναζήτησης. Με αυτόν τον τρόπο, κάθε φορά που εισάγετε οτιδήποτε στο Combo Box, θα αντικατοπτρίζεται επίσης σε ένα κελί σε πραγματικό χρόνο (όπως φαίνεται παρακάτω).
Ακολουθούν τα βήματα για να το κάνετε αυτό:
- Μεταβείτε στην καρτέλα Developer -> Controls -> Insert -> ActiveX Controls -> Combo Box (ActiveX Controls).
- Εάν δεν έχετε ορατή την καρτέλα προγραμματιστή, ακολουθούν τα βήματα για να την ενεργοποιήσετε.
- Εάν δεν έχετε ορατή την καρτέλα προγραμματιστή, ακολουθούν τα βήματα για να την ενεργοποιήσετε.
- Κάντε κλικ οπουδήποτε στο φύλλο εργασίας. Θα εισαγάγει το Combo Box.
- Κάντε δεξί κλικ στο Combo Box και επιλέξτε Ιδιότητες.
- Στο παράθυρο Ιδιότητες, κάντε τις ακόλουθες αλλαγές:
- Συνδεδεμένο κελί: K2 (μπορείτε να επιλέξετε οποιοδήποτε κελί όπου θέλετε να εμφανίζει τις τιμές εισόδου. Θα χρησιμοποιήσουμε αυτό το κελί για τη ρύθμιση των δεδομένων).
- ListFillRange: CountryList (αυτό είναι το εύρος με το όνομα που δημιουργήσαμε στο Βήμα 1. Αυτό θα εμφανίσει όλες τις χώρες στο αναπτυσσόμενο μενού).
- MatchEntry: 2-fmMatchEntryNone (αυτό διασφαλίζει ότι μια λέξη δεν ολοκληρώνεται αυτόματα καθώς πληκτρολογείτε)
- Με το Combo Box επιλεγμένο, μεταβείτε στην καρτέλα Developer -> Controls -> Κάντε κλικ στο Design Mode (αυτό σας βγάζει από τη λειτουργία σχεδίασης και τώρα μπορείτε να πληκτρολογήσετε οτιδήποτε στο Combo Box. Τώρα, ό, τι πληκτρολογήσετε θα αντικατοπτρίζεται στο κελί K2 σε πραγματικό χρόνο)
Βήμα 3 - Ρύθμιση δεδομένων
Τέλος, συνδέουμε τα πάντα με στήλες βοηθού. Χρησιμοποιώ τρεις στήλες βοηθού εδώ για να φιλτράρω τα δεδομένα.
Στήλη βοηθού 1: Εισαγάγετε τον σειριακό αριθμό για όλες τις εγγραφές (20 σε αυτήν την περίπτωση). Μπορείτε να χρησιμοποιήσετε τον τύπο ROWS () για να το κάνετε αυτό.
Στήλη βοηθού 2: Στη στήλη βοηθού 2, ελέγχουμε αν το κείμενο που εισάγεται στο πλαίσιο αναζήτησης ταιριάζει με το κείμενο στα κελιά της στήλης χώρας.
Αυτό μπορεί να γίνει χρησιμοποιώντας συνδυασμό συναρτήσεων IF, ISNUMBER και SEARCH.
Εδώ είναι ο τύπος:
= ΑΝ (ΑΡΙΘΜΟΣ (ΑΝΑΖΗΤΗΣΗ ($ K $ 2, D4)), Ε4, "")
Αυτός ο τύπος θα αναζητήσει το περιεχόμενο στο πλαίσιο αναζήτησης (το οποίο συνδέεται με το κελί Κ2) στο κελί που έχει το όνομα χώρας.
Εάν υπάρχει αντιστοίχιση, αυτός ο τύπος επιστρέφει τον αριθμό σειράς, αλλιώς επιστρέφει ένα κενό. Για παράδειγμα, εάν το Combo Box έχει την τιμή "US", όλες οι εγγραφές με τη χώρα ως "US" θα έχουν τον αριθμό σειράς και τα υπόλοιπα θα είναι κενά ("")
Στήλη βοηθού 3: Στη στήλη βοηθού 3, πρέπει να συγκεντρώσουμε όλους τους αριθμούς γραμμών από τη στήλη βοηθού 2. Για να γίνει αυτό, μπορούμε να χρησιμοποιήσουμε έναν συνδυασμό εάν IFERROR και ΜΙΚΡΟΙ τύποι. Εδώ είναι ο τύπος:
= IFERROR (ΜΙΚΡΟ ($ F $ 4: $ F $ 23, E4), "")
Αυτός ο τύπος στοιβάζει όλους τους αριθμούς σειρών που ταιριάζουν μαζί. Για παράδειγμα, εάν το Combo Box έχει την τιμή US, όλοι οι αριθμοί γραμμών με το "US" στοιβάζονται μαζί.
Τώρα, όταν έχουμε συγκεντρώσει τους αριθμούς γραμμών, πρέπει απλώς να εξάγουμε τα δεδομένα σε αυτόν τον αριθμό σειράς. Αυτό μπορεί να γίνει εύκολα χρησιμοποιώντας τον τύπο ευρετηρίου (εισαγάγετε αυτόν τον τύπο στο σημείο που θέλετε να εξαγάγετε τα δεδομένα. Αντιγράψτε το στο επάνω αριστερό κελί όπου θέλετε να εξαχθούν τα δεδομένα και, στη συνέχεια, σύρετέ το προς τα κάτω και προς τα δεξιά).
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, ΣΤΗΛΕΣ ($ I $ 3: I3)), "")
Αυτός ο τύπος έχει 2 μέρη:
ΔΕΙΚΤΗΣ - Αυτό εξάγει τα δεδομένα με βάση τον αριθμό γραμμής.
IFERROR - Αυτό επιστρέφει κενό όταν δεν υπάρχουν δεδομένα.
Εδώ είναι ένα στιγμιότυπο για το τι τελικά παίρνετε:
Το Combo Box είναι ένα αναπτυσσόμενο μενού καθώς και ένα πλαίσιο αναζήτησης. Μπορείτε να αποκρύψετε τα αρχικά δεδομένα και τις στήλες βοηθού για να εμφανίζονται μόνο οι φιλτραρισμένες εγγραφές. Μπορείτε επίσης να έχετε τις πρώτες στήλες δεδομένων και βοηθού σε κάποιο άλλο φύλλο και να δημιουργήσετε αυτό το δυναμικό φίλτρο excel σε άλλο φύλλο εργασίας.
Γίνετε Δημιουργικοί! Δοκιμάστε μερικές παραλλαγές
Μπορείτε να δοκιμάσετε και να το προσαρμόσετε στις απαιτήσεις σας. Μπορεί να θέλετε να δημιουργήσετε πολλά φίλτρα excel αντί για ένα. Για παράδειγμα, μπορεί να θέλετε να φιλτράρετε τις εγγραφές όπου ο αντιπρόσωπος πωλήσεων είναι ο Mike και η χώρα είναι η Ιαπωνία. Αυτό μπορεί να γίνει ακριβώς ακολουθώντας τα ίδια βήματα με κάποια τροποποίηση στον τύπο στις στήλες βοηθού.
Μια άλλη παραλλαγή θα μπορούσε να είναι το φιλτράρισμα δεδομένων που ξεκινά με τους χαρακτήρες που εισάγετε στο σύνθετο πλαίσιο. Για παράδειγμα, όταν εισάγετε το «I», μπορεί να θέλετε να εξαγάγετε χώρες που ξεκινούν με το I (σε σύγκριση με την τρέχουσα κατασκευή όπου θα σας έδινε επίσης τη Σιγκαπούρη και τις Φιλιππίνες καθώς περιέχει το αλφάβητο I).
Όπως πάντα, τα περισσότερα άρθρα μου είναι εμπνευσμένα από τις ερωτήσεις/απαντήσεις των αναγνωστών μου. Θα ήθελα πολύ να λάβω τα σχόλιά σας και να μάθω από εσάς. Αφήστε τις σκέψεις σας στην ενότητα σχολίων.
Σημείωση: Σε περίπτωση που χρησιμοποιείτε το Office 365, μπορείτε να χρησιμοποιήσετε τη λειτουργία ΦΙΛΤΡΟ για να φιλτράρετε γρήγορα τα δεδομένα καθώς πληκτρολογείτε. Είναι πιο εύκολο από τη μέθοδο που εμφανίζεται σε αυτό το σεμινάριο.
