Ένας από τους συναδέλφους μου με ρώτησε αν είναι δυνατόν να γίνουν πολλαπλές επιλογές σε μια αναπτυσσόμενη λίστα στο Excel.
Όταν δημιουργείτε μια αναπτυσσόμενη λίστα, μπορείτε να κάνετε μόνο μία επιλογή. Εάν επιλέξετε άλλο στοιχείο, το πρώτο αντικαθίσταται με τη νέα επιλογή.
Wantedθελε να κάνει πολλαπλές επιλογές από το ίδιο αναπτυσσόμενο μενού με τέτοιο τρόπο ώστε οι επιλογές να προστεθούν στην ήδη υπάρχουσα τιμή στο κελί.
Κάτι όπως φαίνεται παρακάτω στην εικόνα:
Δεν υπάρχει τρόπος να το κάνετε αυτό με ενσωματωμένες λειτουργίες του Excel.
Ο μόνος τρόπος είναι να χρησιμοποιήσετε έναν κωδικό VBA, ο οποίος εκτελείται κάθε φορά που κάνετε μια επιλογή και προσθέτει την επιλεγμένη τιμή στην υπάρχουσα τιμή.
Παρακολουθήστε βίντεο - Πώς να επιλέξετε πολλά στοιχεία από μια αναπτυσσόμενη λίστα Excel
Πώς να κάνετε πολλαπλές επιλογές σε μια αναπτυσσόμενη λίστα
Σε αυτό το σεμινάριο, θα σας δείξω πώς να κάνετε πολλαπλές επιλογές σε μια αναπτυσσόμενη λίστα Excel (με επανάληψη και χωρίς επανάληψη).
Αυτό ήταν ένα από τα πιο δημοφιλή μαθήματα Excel σε αυτόν τον ιστότοπο. Δεδομένου ότι έχω πολλές παρόμοιες ερωτήσεις, αποφάσισα να δημιουργήσω μια ενότητα Συχνές ερωτήσεις στο τέλος αυτού του σεμιναρίου. Έτσι, εάν έχετε οποιεσδήποτε ερωτήσεις μετά την ανάγνωση αυτού, ανατρέξτε πρώτα στην ενότητα Συχνές ερωτήσεις.Υπάρχουν δύο μέρη για τη δημιουργία μιας αναπτυσσόμενης λίστας που επιτρέπει πολλαπλές επιλογές:
- Δημιουργία της αναπτυσσόμενης λίστας.
- Προσθήκη του κώδικα VBA στο back-end.
Δημιουργία της αναπτυσσόμενης λίστας στο Excel
Ακολουθούν τα βήματα για τη δημιουργία μιας αναπτυσσόμενης λίστας στο Excel:
- Επιλέξτε το κελί ή την περιοχή κελιών όπου θέλετε να εμφανίζεται η αναπτυσσόμενη λίστα (C2 σε αυτό το παράδειγμα).
- Μεταβείτε στα Δεδομένα -> Εργαλεία δεδομένων -> Επικύρωση δεδομένων.
- Στο πλαίσιο διαλόγου Επικύρωση δεδομένων, στην καρτέλα ρυθμίσεις, επιλέξτε «Λίστα» ως κριτήρια επικύρωσης.
- Στο πεδίο Πηγή, επιλέξτε τα κελιά που έχουν τα στοιχεία που θέλετε στο αναπτυσσόμενο μενού.
- Κάντε κλικ στο OK.
Τώρα, το κελί C2 έχει μια αναπτυσσόμενη λίστα που εμφανίζει τα ονόματα των στοιχείων στο A2: A6.
Προς το παρόν, έχουμε μια αναπτυσσόμενη λίστα όπου μπορείτε να επιλέξετε ένα στοιχείο κάθε φορά (όπως φαίνεται παρακάτω).
Για να ενεργοποιήσουμε αυτό το αναπτυσσόμενο μενού για να μπορέσουμε να κάνουμε πολλαπλές επιλογές, πρέπει να προσθέσουμε τον κωδικό VBA στο πίσω άκρο.
Οι επόμενες δύο ενότητες αυτού του σεμιναρίου θα σας δώσουν τον κωδικό VBA για να επιτρέψετε πολλαπλές επιλογές στην αναπτυσσόμενη λίστα (με και χωρίς επανάληψη).
Κωδικός VBA που επιτρέπει πολλαπλές επιλογές σε μια αναπτυσσόμενη λίστα (με επανάληψη)
Παρακάτω είναι ο κώδικας VBA του Excel που θα μας επιτρέψει να επιλέξουμε περισσότερα από ένα στοιχεία από την αναπτυσσόμενη λίστα (επιτρέποντας επαναλήψεις στην επιλογή):
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' Για να κάνετε πολλαπλές επιλογές σε μια αναπτυσσόμενη λίστα στο Excel Old Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$ C $ 2" Στη συνέχεια, εάν το Target.SpecialCells (xlCellTypeAllValidation) δεν είναι τίποτα τότε GoTo Exitsub Else: If Target.Value = "" Τότε GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else Target.Value = Oldvalue & "," & Newvalue End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Τώρα πρέπει να τοποθετήσετε αυτόν τον κώδικα σε μια ενότητα στο VB Editor (όπως φαίνεται παρακάτω στην ενότητα "Πού να τοποθετήσετε τον κωδικό VBA").
Όταν τοποθετήσετε αυτόν τον κωδικό στο backend (που καλύπτεται αργότερα σε αυτό το σεμινάριο), θα σας επιτρέψει να κάνετε πολλές επιλογές στο αναπτυσσόμενο μενού (όπως φαίνεται παρακάτω).
Σημειώστε ότι εάν επιλέξετε ένα στοιχείο περισσότερες από μία φορές, θα εισαχθεί ξανά (επιτρέπεται η επανάληψη).
Δοκιμάστε το μόνοι σας … Κατεβάστε το Παράδειγμα αρχείου
Κωδικός VBA που επιτρέπει πολλαπλές επιλογές σε μια αναπτυσσόμενη λίστα (χωρίς επανάληψη)
Πολλοί άνθρωποι ρωτούν σχετικά με τον κώδικα για να επιλέξουν πολλά στοιχεία από μια αναπτυσσόμενη λίστα χωρίς επανάληψη.
Ακολουθεί ο κώδικας που θα διασφαλίσει ότι ένα στοιχείο μπορεί να επιλεγεί μόνο μία φορά, ώστε να μην υπάρχουν επαναλήψεις:
Private Sub Worksheet_Change (ByVal Target As Range) 'Code by Sumit Bansal from https://trumpexcel.com' Για να επιτρέψετε πολλαπλές επιλογές σε μια αναπτυσσόμενη λίστα στο Excel (χωρίς επανάληψη) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub If Target.Address = "$ C $ 2" Then If Target.SpecialCells (xlCellTypeAllValidation) Is Something then GoTo Exitsub Else: If Target.Value = "" Τότε GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target Application Value.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else If InStr (1, Oldvalue, Newvalue) = 0 Then Target.Value = Oldvalue & "," & Newvalue Else: Target.Value = Oldvalue End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub
Τώρα πρέπει να τοποθετήσετε αυτόν τον κώδικα σε μια ενότητα στο VB Editor (όπως φαίνεται στην επόμενη ενότητα αυτού του σεμιναρίου).
Αυτός ο κωδικός θα σας επιτρέψει να επιλέξετε πολλά στοιχεία από την αναπτυσσόμενη λίστα. Ωστόσο, θα μπορείτε να επιλέξετε ένα στοιχείο μόνο μία φορά. Εάν προσπαθήσετε να το επιλέξετε ξανά, τίποτα δεν θα συμβεί (όπως φαίνεται παρακάτω).
Δοκιμάστε το μόνοι σας … Κατεβάστε το Παράδειγμα αρχείου
Πού να τοποθετήσετε τον κωδικό VBA
Πριν ξεκινήσετε να χρησιμοποιείτε αυτόν τον κώδικα στο excel, πρέπει να τον τοποθετήσετε στο back-end, έτσι ώστε να ενεργοποιείται κάθε φορά που υπάρχει οποιαδήποτε αλλαγή στην αναπτυσσόμενη επιλογή.
Ακολουθήστε τα παρακάτω βήματα για να βάλετε τον κωδικό VBA στο backend του Excel:
- Μεταβείτε στην καρτέλα προγραμματιστή και κάντε κλικ στο Visual Basic (μπορείτε επίσης να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου - Alt + F11). Αυτό θα ανοίξει το Visual Basic Editor.
- Θα πρέπει να υπάρχει ένα παράθυρο Project Explorer στα αριστερά (αν δεν είναι εκεί, χρησιμοποιήστε το Control + R για να το κάνετε ορατό).
- Κάντε διπλό κλικ στο Όνομα φύλλου εργασίας (στο αριστερό παράθυρο) όπου βρίσκεται η αναπτυσσόμενη λίστα. Αυτό ανοίγει το παράθυρο κώδικα για αυτό το φύλλο εργασίας.
- Στο παράθυρο κώδικα, αντιγράψτε και επικολλήστε τον παραπάνω κώδικα.
- Κλείστε το πρόγραμμα επεξεργασίας VB.
Τώρα, όταν επιστρέψετε στο αναπτυσσόμενο μενού και κάνετε επιλογές, θα σας επιτρέψει να κάνετε πολλαπλές επιλογές (όπως φαίνεται παρακάτω):
Δοκιμάστε το μόνοι σας … Κατεβάστε το Παράδειγμα αρχείου
Σημείωση: Δεδομένου ότι χρησιμοποιούμε έναν κωδικό VBA για να το κάνουμε αυτό, πρέπει να αποθηκεύσετε το βιβλίο εργασίας με επέκταση .xls ή .xlsm.
Συχνές Ερωτήσεις (Συχνές Ερωτήσεις)
Δημιούργησα αυτήν την ενότητα για να απαντήσω σε μερικές από τις πιο συχνές ερωτήσεις σχετικά με αυτό το σεμινάριο και τον κώδικα VBA. Εάν έχετε οποιεσδήποτε ερωτήσεις, σας ζητώ να περάσετε πρώτα από αυτήν τη λίστα ερωτημάτων.
Ε: Στον κώδικα VBA, η λειτουργικότητα είναι μόνο για το κελί C2. Πώς μπορώ να το πάρω για άλλα κύτταρα; Απάντηση: Για να λάβετε αυτό το αναπτυσσόμενο μενού πολλαπλής επιλογής σε άλλα κελιά, πρέπει να τροποποιήσετε τον κώδικα VBA στο backend. Ας υποθέσουμε ότι θέλετε να το λάβετε για C2, C3 και C4, πρέπει να αντικαταστήσετε την ακόλουθη γραμμή στον κώδικα: Εάν Target.Address = "$ C $ 2" Στη συνέχεια με αυτήν τη γραμμή: Εάν Target.Address = "$ C $ 2" Or Target.Address = "$ C $ 3" Or Target.Address = "$ C $ 4" Στη συνέχεια
Ε: Πρέπει να δημιουργήσω πολλά αναπτυσσόμενα μενού σε ολόκληρη τη στήλη "C". Πώς μπορώ να το λάβω για όλα τα κελιά στις στήλες με λειτουργίες πολλαπλής επιλογής; Απάντηση: Για να ενεργοποιήσετε πολλαπλές επιλογές σε αναπτυσσόμενα μερίδια σε μια ολόκληρη στήλη, αντικαταστήστε την ακόλουθη γραμμή στον κώδικα: Εάν Target.Address = "$ C $ 2" Στη συνέχεια με αυτήν τη γραμμή: Εάν Target.Column = 3 Στη συνέχεια Σε παρόμοιες γραμμές, αν θέλετε αυτήν τη λειτουργικότητα στη στήλη C και D, χρησιμοποιήστε την παρακάτω γραμμή: Εάν Target.Column = 3 ή Target.Column = 4 Στη συνέχεια
Ε: Πρέπει να δημιουργήσω πολλά αναπτυσσόμενα μενού στη σειρά. Πως μπορώ να το κάνω? Απάντηση: Εάν πρέπει να δημιουργήσετε αναπτυσσόμενες λίστες με πολλές επιλογές στη σειρά (ας πούμε τη δεύτερη σειρά), πρέπει να αντικαταστήσετε την παρακάτω γραμμή κώδικα: Εάν Target.Address = "$ C $ 2" Στη συνέχεια, με αυτήν τη γραμμή: Εάν Target.Row = 2 Στη συνέχεια, ομοίως, εάν θέλετε να λειτουργεί για πολλές σειρές (ας πούμε δεύτερη και τρίτη σειρά), χρησιμοποιήστε την παρακάτω γραμμή κώδικα αντ 'αυτού: Εάν Target.Row = 2 ή Target.Row = 3 Τότε
Ε: Από τώρα, οι πολλαπλές επιλογές χωρίζονται με κόμμα. Πώς μπορώ να το αλλάξω για να τα διαχωρίσω με χώρο (ή οποιοδήποτε άλλο διαχωριστικό). Απάντηση: Για να τα διαχωρίσετε με διαχωριστικό διαφορετικό από κόμμα, πρέπει να αντικαταστήσετε την ακόλουθη γραμμή κώδικα VBA: Target.Value = Oldvalue & "," & Newvalue με αυτήν τη γραμμή κώδικα VBA: Target.Value = Oldvalue & "" & Newvalue Ομοίως, αν θέλετε να αλλάξετε κόμμα με άλλο χαρακτήρα, όπως |, μπορείτε να χρησιμοποιήσετε την ακόλουθη γραμμή κώδικα: Target.Value = Oldvalue & "|" & Newvalue
Ε: Μπορώ να λάβω κάθε επιλογή σε ξεχωριστή γραμμή στο ίδιο κελί; Απάντηση: Ναι μπορείτε. Για να το αποκτήσετε, πρέπει να αντικαταστήσετε την παρακάτω γραμμή κώδικα VBA: Target.Value = Oldvalue & "," & Newvalue με αυτήν τη γραμμή κώδικα: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine εισάγει μια νέα γραμμή στο ίδιο κελί . Έτσι, κάθε φορά που κάνετε μια επιλογή από το αναπτυσσόμενο μενού, θα εισαχθεί σε μια νέα γραμμή.
Ε: Μπορώ να κάνω τη λειτουργία πολλαπλής επιλογής να λειτουργεί σε προστατευμένο φύλλο; Απάντηση: Ναι μπορείτε. Για να το κάνετε αυτό, πρέπει να κάνετε δύο πράγματα: Προσθέστε την ακόλουθη γραμμή στον κώδικα (αμέσως μετά τη δήλωση DIM): Me.Protect UserInterfaceOnly: = True Δεύτερον, πρέπει να βεβαιωθείτε ότι τα κελιά - που έχουν το αναπτυσσόμενο με πολλές λειτουργίες επιλογής - δεν είναι κλειδωμένα όταν προστατεύετε ολόκληρο το φύλλο. Εδώ είναι ένα σεμινάριο για το πώς να το κάνετε αυτό: Κλείδωμα κελιών στο Excel
