✅ Δημιουργήστε μια αναπτυσσόμενη λίστα Excel με προτάσεις αναζήτησης

Πίνακας περιεχομένων

Αναπτυσσόμενη αναπτυσσόμενη λίστα στο Excel

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

Σε αυτό το σεμινάριο, θα μάθετε πώς μπορείτε να δημιουργήσετε μια αναπτυσσόμενη λίστα με δυνατότητα αναζήτησης στο Excel-δηλαδή, μια αναπτυσσόμενη λίστα που θα εμφανίζει τα στοιχεία που ταιριάζουν καθώς πληκτρολογείτε.

Παρακάτω είναι ένα βίντεο από αυτό το σεμινάριο (σε περίπτωση που προτιμάτε να παρακολουθείτε βίντεο παρά να διαβάζετε το κείμενο).

Αναπτυσσόμενη αναπτυσσόμενη λίστα στο Excel

Για τους σκοπούς αυτού του σεμιναρίου, χρησιμοποιώ τα δεδομένα των 20 κορυφαίων χωρών κατά ΑΕΠ.

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

Κάτι όπως φαίνεται παρακάτω:

Για να συνεχίσετε, κάντε λήψη του παραδείγματος αρχείου από εδώ

Η δημιουργία της αναπτυσσόμενης λίστας με δυνατότητα αναζήτησης στο Excel θα ήταν μια διαδικασία τριών μερών:

Διαμόρφωση του πλαισίου αναζήτησης.
Ρύθμιση των Δεδομένων.
Γράφοντας έναν σύντομο κώδικα VBA για να λειτουργήσει.

Βήμα 1 - Διαμόρφωση του πλαισίου αναζήτησης

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

Ακολουθούν τα βήματα για να το κάνετε αυτό:

Μεταβείτε στην καρτέλα Developer -> Insert -> ActiveX Controls -> Combo Box (ActiveX Control).
- Υπάρχει πιθανότητα να μην βρείτε την καρτέλα προγραμματιστή στην κορδέλα. Από προεπιλογή, είναι κρυφό και πρέπει να ενεργοποιηθεί. Κάντε κλικ εδώ για να μάθετε πώς μπορείτε να εμφανίσετε την καρτέλα προγραμματιστή στην κορδέλα στο Excel.
Μετακινήστε τον κέρσορα στην περιοχή του φύλλου εργασίας και κάντε κλικ οπουδήποτε. Θα εισαγάγει ένα σύνθετο πλαίσιο.
Κάντε δεξί κλικ στο σύνθετο πλαίσιο και επιλέξτε Ιδιότητες.
Στο πλαίσιο διαλόγου ιδιοτήτων, κάντε τις ακόλουθες αλλαγές:
- AutoWordSelect: Ψευδής
- LinkedCell: Β3
- ListFillRange: DropDownList (θα δημιουργήσουμε μια ονομαστική περιοχή με αυτό το όνομα στο βήμα 2)
- MatchEntry: 2 - fmMatchEntryNone

(Το κελί B3 συνδέεται με το Combo Box, που σημαίνει ότι οτιδήποτε πληκτρολογήσετε στο Combo Box εισάγεται στο B3)

Μεταβείτε στην καρτέλα Προγραμματιστής και κάντε κλικ στη Λειτουργία σχεδίασης. Αυτό θα σας επιτρέψει να εισαγάγετε κείμενο στο σύνθετο πλαίσιο. Επίσης, δεδομένου ότι το κελί Β3 είναι συνδεδεμένο με το σύνθετο πλαίσιο, κάθε κείμενο που εισάγετε στο σύνθετο πλαίσιο θα αντικατοπτρίζεται επίσης στο Β3 σε πραγματικό χρόνο.

Βήμα 2 - Ρύθμιση δεδομένων

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

Για να γίνει αυτό, θα χρησιμοποιήσουμε

Τρεις στήλες βοηθού.
Ένα δυναμικό όνομα περιοχής.

Στήλη βοηθού 1

Τοποθετήστε τον ακόλουθο τύπο στο κελί F3 και σύρετέ τον για ολόκληρη τη στήλη (F3: F22)

=-ΑΡΙΘΜΟΣ (IFERROR (ΑΝΑΖΗΤΗΣΗ ($ B $ 3, E3,1), ""))

Αυτός ο τύπος επιστρέφει 1 όταν το κείμενο στο Combo Box είναι εκεί στο όνομα της χώρας στα αριστερά. Για παράδειγμα, εάν πληκτρολογήσετε UNI, τότε μόνο οι τιμές για United Πολιτείες και UniΤο ted Kingdom είναι 1 και όλες οι υπόλοιπες τιμές είναι 0.

Στήλη βοηθού 2

Τοποθετήστε τον ακόλουθο τύπο στο κελί G3 και σύρετέ τον για ολόκληρη τη στήλη (G3: G22)

= ΑΝ (F3 = 1, COUNTIF ($ F $ 3: F3,1), "")

Αυτός ο τύπος επιστρέφει 1 για το πρώτο περιστατικό όπου το κείμενο Combo Box ταιριάζει με το όνομα χώρας, 2 για το δεύτερο, 3 για το τρίτο και ούτω καθεξής. Για παράδειγμα, εάν πληκτρολογήσετε UNI, το κελί G3 θα εμφανίσει 1 όπως ταιριάζει με τις Ηνωμένες Πολιτείες και το G9 θα εμφανίσει 2 όπως αντιστοιχεί στο Ηνωμένο Βασίλειο. Τα υπόλοιπα κελιά θα είναι κενά.

Στήλη βοηθού 3

Βάλτε τον ακόλουθο τύπο στο κελί H3 και σύρετέ τον για ολόκληρη τη στήλη (H3: H22)

= IFERROR (INDEX ($ E $ 3: $ E $ 22, MATCH (ROWS ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "")

Αυτός ο τύπος στοιβάζει όλα τα ονόματα που ταιριάζουν μαζί χωρίς κενά κελιά μεταξύ τους. Για παράδειγμα, εάν πληκτρολογήσετε UNI, αυτή η στήλη θα εμφανίζει 2 και 9 μαζί και το υπόλοιπο όλο το κελί θα είναι κενό.

Δημιουργία εύρους δυναμικής ονομασίας

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

Σημείωση: Στο βήμα 1 εισαγάγαμε το DropDownList στην επιλογή ListFillRange. Τώρα θα δημιουργήσουμε το όνομα με το ίδιο όνομα.

Ακολουθούν τα βήματα για τη δημιουργία του:

Μεταβείτε στους Τύπους -> Διαχειριστής ονομάτων.
Στο πλαίσιο διαλόγου ονόματος-διαχειριστή κάντε κλικ στο κουμπί Νέο. Θα ανοίξει ένα πλαίσιο διαλόγου Νέο όνομα.
Στο πεδίο Όνομα εισάγετε DropDownList
Στο πεδίο "Αναφορές στο πεδίο" εισάγετε τον τύπο: = $ H $ 3: INDEX ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Βήμα 3 - Θέτοντας σε λειτουργία τον κώδικα VBA

Είμαστε σχεδόν εκεί.

Το τελευταίο μέρος είναι να γράψετε έναν σύντομο κώδικα VBA. Αυτός ο κώδικας καθιστά το αναπτυσσόμενο μενού δυναμικό έτσι ώστε να εμφανίζει τα αντίστοιχα στοιχεία/ονόματα καθώς πληκτρολογείτε στο πλαίσιο αναζήτησης.

Για να προσθέσετε αυτόν τον κώδικα στο βιβλίο εργασίας σας:

Κάντε δεξί κλικ στην καρτέλα Φύλλο εργασίας και επιλέξτε Προβολή κώδικα.
Στο παράθυρο VBA, αντιγράψτε και επικολλήστε τον ακόλουθο κώδικα:
```
Private Sub ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Τέλος υπο
```

Αυτό είναι!!

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

Για καλύτερη εμφάνιση και αίσθηση, μπορείτε να καλύψετε το κελί B3 με το Combo Box και να αποκρύψετε όλες τις στήλες βοηθού. Τώρα μπορείτε να επιδείξετε λίγο με αυτό το καταπληκτικό κόλπο του Excel.

Για να ακολουθήσετε, κατεβάστε το αρχείο από εδώ

Τι νομίζετε; Θα μπορούσατε να χρησιμοποιήσετε αυτήν την αναπτυσσόμενη λίστα προτάσεων αναζήτησης στην εργασία σας; Πείτε μου τις σκέψεις σας αφήνοντας ένα σχόλιο.

Αν σας άρεσε αυτό το σεμινάριο, είμαι βέβαιος ότι θα θέλατε και τα ακόλουθα μαθήματα Excel: