Seleziona più elementi nella lista a discesa di Excel – guida completa
Le liste a discesa di Excel sono uno strumento fantastico per garantire la coerenza dei dati e facilitare l'inserimento. Tuttavia, di default, ti limitano alla selezione di un solo elemento. Ma cosa succede se hai bisogno di selezionare più elementi dalla stessa lista a discesa? Questa guida completa esplorerà metodi per abilitare selezioni multiple nelle liste a discesa di Excel, gestire i duplicati, impostare separatori personalizzati e definire l'ambito di queste liste.
- Consentire elementi duplicati
- Rimuovere eventuali elementi esistenti
- Impostare un separatore personalizzato
- Impostare un intervallo specifico
- Esecuzione in un foglio di lavoro protetto
Abilitazione di selezioni multiple nella lista a discesa
Questa sezione fornisce due metodi per aiutarti ad abilitare selezioni multiple nella lista a discesa in Excel.
Utilizzo del codice VBA
Per consentire selezioni multiple in una lista a discesa, puoi utilizzare "Visual Basic for Applications" (VBA) in Excel. Lo script può modificare il comportamento di una lista a discesa per renderla una lista a scelta multipla. Procedi come segue.
Passo 1: Apri l'editor del Foglio (Codice)
- Apri il foglio di lavoro che contiene la lista a discesa per la quale desideri abilitare la selezione multipla.
- Fai clic destro sulla scheda del foglio e seleziona "Visualizza Codice" dal menu contestuale.
Passo 2: Usa il codice VBA
Ora copia il seguente codice VBA e incollalo nella finestra del Foglio (Codice) aperta.
Codice VBA: Abilita selezioni multiple nella lista a discesa di Excel.
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Set TargetRange = Me.UsedRange ' Users can change target range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
If Not (xValue1 = xValue2 Or _
InStr(1, xValue1, delimiter & xValue2) > 0 Or _
InStr(1, xValue1, xValue2 & delimiter) > 0) Then
Target.Value = xValue1 & delimiter & xValue2
Else
Target.Value = xValue1
End If
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
Risultato
Quando torni al foglio di lavoro, la lista a discesa ti permetterà di scegliere più opzioni, guarda la demo qui sotto:
Il codice VBA sopra riportato:
- Si applica a tutte le liste a discesa con convalida dei dati nel foglio di lavoro corrente, sia quelle esistenti che quelle create in futuro.
- Ti impedisce di selezionare lo stesso elemento più di una volta in ciascuna lista a discesa.
- Usa la virgola come separatore per gli elementi selezionati. Per usare altri delimitatori, consulta questa sezione per cambiare il separatore.
Utilizzo di Kutools per Excel in pochi clic
Se non sei a tuo agio con VBA, un'alternativa più semplice è la funzione "Lista a discesa multi-selezione" di "Kutools per Excel". Questo strumento facile da usare semplifica l'abilitazione di selezioni multiple nelle liste a discesa, consentendoti di personalizzare il separatore e gestire i duplicati senza sforzo per soddisfare le tue diverse esigenze.
Dopo aver installato Kutools per Excel, vai alla scheda "Kutools", seleziona "Lista a discesa" > "Lista a discesa multi-selezione". Quindi devi configurare come segue.
- Specifica l'intervallo contenente la lista a discesa da cui devi selezionare più elementi.
- Specifica il separatore per gli elementi selezionati nella cella della lista a discesa.
- Clicca su "OK" per completare le impostazioni.
Risultato
Ora, quando fai clic su una cella con una lista a discesa nell'intervallo specificato, apparirà una casella di riepilogo accanto ad essa. Basta fare clic sul pulsante "+" accanto agli elementi per aggiungerli alla cella della lista a discesa e fare clic sul pulsante "-" per rimuovere eventuali elementi che non desideri più. Guarda la demo qui sotto:
- Seleziona l'opzione "Inserisci un separatore e vai a capo" se desideri visualizzare gli elementi selezionati verticalmente all'interno della cella. Se preferisci un elenco orizzontale, lascia questa opzione deselezionata.
- Seleziona l'opzione "Abilita funzione di ricerca" se desideri aggiungere una barra di ricerca alla tua lista a discesa.
- Per applicare questa funzione, scarica e installa prima Kutools per Excel.
Ulteriori operazioni per la lista a discesa multi-selezione
Questa sezione raccoglie i diversi scenari che potrebbero essere richiesti quando si abilitano selezioni multiple nella lista a discesa di Convalida dei Dati.
Consentire elementi duplicati nella lista a discesa
I duplicati possono essere un problema quando vengono consentite selezioni multiple in una lista a discesa. Il codice VBA sopra non consente elementi duplicati nella lista a discesa. Se hai bisogno di mantenere elementi duplicati, prova il codice VBA in questa sezione.
Codice VBA: Consenti duplicati nella lista a discesa di convalida dei dati
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Set TargetRange = Me.UsedRange ' Users can change target range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
Target.Value = xValue1 & delimiter & xValue2
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
Risultato
Ora puoi selezionare più elementi dalle liste a discesa nel foglio di lavoro corrente. Per ripetere un elemento in una cella della lista a discesa, continua a selezionare quell'elemento dall'elenco. Guarda la schermata:
Rimuovere eventuali elementi esistenti dalla lista a discesa
Dopo aver selezionato più elementi da una lista a discesa, a volte potresti aver bisogno di rimuovere un elemento esistente dalla cella della lista a discesa. Questa sezione fornisce un altro pezzo di codice VBA per aiutarti a completare questa attività.
Codice VBA: Rimuovi eventuali elementi esistenti dalla cella della lista a discesa
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRngDV As Range
Dim TargetRange As Range
Dim oldValue As String
Dim newValue As String
Dim delimiter As String
Dim allValues As Variant
Dim valueExists As Boolean
Dim i As Long
Dim cleanedValue As String
Set TargetRange = Me.UsedRange ' Set your specific range here
delimiter = ", " ' Set your desired delimiter here
If Target.CountLarge > 1 Then Exit Sub
' Check if the change is within the specific range
If Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRngDV = Target.SpecialCells(xlCellTypeAllValidation)
If xRngDV Is Nothing Or Target.Value = "" Then
' Skip if there's no data validation or if the cell is cleared
Application.EnableEvents = True
Exit Sub
End If
On Error GoTo 0
If Not Intersect(Target, xRngDV) Is Nothing Then
Application.EnableEvents = False
newValue = Target.Value
Application.Undo
oldValue = Target.Value
Target.Value = newValue
' Split the old value by delimiter and check if new value already exists
allValues = Split(oldValue, delimiter)
valueExists = False
For i = LBound(allValues) To UBound(allValues)
If Trim(allValues(i)) = newValue Then
valueExists = True
Exit For
End If
Next i
' Add or remove value based on its existence
If valueExists Then
' Remove the value
cleanedValue = ""
For i = LBound(allValues) To UBound(allValues)
If Trim(allValues(i)) <> newValue Then
If cleanedValue <> "" Then cleanedValue = cleanedValue & delimiter
cleanedValue = cleanedValue & Trim(allValues(i))
End If
Next i
Target.Value = cleanedValue
Else
' Add the value
If oldValue <> "" Then
Target.Value = oldValue & delimiter & newValue
Else
Target.Value = newValue
End If
End If
Application.EnableEvents = True
End If
End Sub
Risultato
Questo codice VBA ti consente di selezionare più elementi da una lista a discesa e rimuovere facilmente qualsiasi elemento che hai già scelto. Dopo aver selezionato più elementi, se vuoi rimuoverne uno specifico, basta selezionarlo nuovamente dall'elenco.
Impostare un separatore personalizzato
Il delimitatore è impostato come virgola nei codici VBA sopra. Puoi modificare questa variabile con qualsiasi carattere preferito da utilizzare come separatore per le selezioni della lista a discesa. Ecco come puoi farlo:
Come puoi vedere, i codici VBA sopra hanno tutti la seguente riga:
delimiter = ", "
Devi solo cambiare la virgola con qualsiasi separatore di cui hai bisogno. Ad esempio, se vuoi separare gli elementi con un punto e virgola, cambia la riga in:
delimiter = "; "
delimiter = vbNewLine
Impostare un intervallo specifico
I codici VBA sopra si applicano a tutte le liste a discesa nel foglio di lavoro corrente. Se vuoi che i codici VBA si applichino solo a un certo intervallo di liste a discesa, puoi specificare l'intervallo nel codice VBA sopra come segue.
Come puoi vedere, i codici VBA sopra hanno tutti la seguente riga:
Set TargetRange = Me.UsedRange
Devi solo cambiare la riga in:
Set TargetRange = Me.Range("C2:C10")
Esecuzione in un foglio di lavoro protetto
Immagina di aver protetto un foglio di lavoro con la password "123" e impostato le celle della lista a discesa su "Sbloccate" prima di attivare la protezione, garantendo così che la funzione di selezione multipla rimanga attiva dopo la protezione. Tuttavia, i codici VBA menzionati sopra non possono funzionare in questo caso, e questa sezione descrive un altro script VBA appositamente progettato per gestire la funzionalità di selezione multipla in un foglio di lavoro protetto.
Codice VBA: Abilita selezione multipla nella lista a discesa senza duplicati
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Dim isProtected As Boolean
Dim pswd As Variant
Set TargetRange = Me.UsedRange ' Set your specific range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
' Check if sheet is protected
isProtected = Me.ProtectContents
If isProtected Then
' If protected, temporarily unprotect. Adjust or remove the password as needed.
pswd = "yourPassword" ' Change or remove this as needed
Me.Unprotect Password:=pswd
End If
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then
If isProtected Then Me.Protect Password:=pswd
Exit Sub
End If
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
If Not (xValue1 = xValue2 Or _
InStr(1, xValue1, delimiter & xValue2) > 0 Or _
InStr(1, xValue1, xValue2 & delimiter) > 0) Then
Target.Value = xValue1 & delimiter & xValue2
Else
Target.Value = xValue1
End If
End If
Application.EnableEvents = True
On Error GoTo 0
' Re-protect the sheet if it was protected
If isProtected Then
Me.Protect Password:=pswd
End If
End Sub
Abilitando selezioni multiple nelle liste a discesa di Excel, puoi migliorare notevolmente la funzionalità e la flessibilità dei tuoi fogli di lavoro. Che tu sia a tuo agio con la codifica VBA o preferisca una soluzione più semplice come Kutools, ora hai la capacità di trasformare le tue liste a discesa standard in strumenti dinamici e multi-selezione. Con queste competenze, sei ora pronto a creare documenti Excel più dinamici e user-friendly. Per coloro che sono desiderosi di approfondire ulteriormente le funzionalità di Excel, il nostro sito web offre una ricchezza di tutorial. Scopri altri suggerimenti e trucchi per Excel qui.
Articoli correlati
Completamento automatico durante la digitazione nella lista a discesa di Excel
Se hai una lista a discesa di convalida dei dati con valori numerosi, devi scorrere verso il basso nell'elenco solo per trovare quello giusto, oppure digitare l'intera parola direttamente nella casella dell'elenco. Se ci fosse un metodo per consentire il completamento automatico digitando la prima lettera nella lista a discesa, tutto diventerebbe più facile. Questo tutorial fornisce il metodo per risolvere il problema.
Crea una lista a discesa da un altro foglio di lavoro in Excel
È piuttosto facile creare una lista a discesa di convalida dei dati tra fogli di lavoro all'interno di una cartella di lavoro. Ma se i dati dell'elenco di cui hai bisogno per la convalida si trovano in un'altra cartella di lavoro, cosa faresti? In questo tutorial, imparerai come creare una lista a discesa da un'altra cartella di lavoro in Excel nei dettagli.
Crea una lista a discesa ricercabile in Excel
Per una lista a discesa con numerosi valori, trovare quello giusto non è un compito facile. In precedenza abbiamo introdotto un metodo per completare automaticamente la lista a discesa quando si inserisce la prima lettera nella casella della lista a discesa. Oltre alla funzione di completamento automatico, puoi anche rendere la lista a discesa ricercabile per migliorare l'efficienza lavorativa nel trovare i valori appropriati nella lista a discesa. Per rendere la lista a discesa ricercabile, prova il metodo in questo tutorial.
Compila automaticamente altre celle quando si selezionano valori nella lista a discesa di Excel
Supponiamo di aver creato una lista a discesa basata sui valori nell'intervallo di celle B8:B14. Quando selezioni un valore qualsiasi nella lista a discesa, vuoi che i valori corrispondenti nell'intervallo di celle C8:C14 vengano automaticamente compilati in una cella selezionata. Per risolvere il problema, i metodi in questo tutorial ti saranno d'aiuto.
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra, invece che in nuove finestre.
- Aumenta la tua produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Indice
- Abilitazione di selezioni multiple
- Utilizzo del codice VBA
- Utilizzo di Kutools per Excel in pochi clic
- Ulteriori operazioni
- Consentire elementi duplicati
- Rimuovere eventuali elementi esistenti
- Impostare un separatore personalizzato
- Impostare un intervallo specifico
- Esecuzione in un foglio di lavoro protetto
- Articoli correlati
- I migliori strumenti di produttività per Office
- Commenti