Selectați mai multe elemente din lista derulantă Excel - ghid complet
Listele derulante Excel sunt un instrument fantastic pentru a asigura coerența datelor și ușurința introducerii. Cu toate acestea, în mod implicit, vă limitează la selectarea unui singur articol. Dar ce se întâmplă dacă trebuie să selectați mai multe articole din aceeași listă derulantă? Acest ghid cuprinzător va explora metode de activare a selecțiilor multiple în listele derulante Excel, de a gestiona duplicatele, de a seta separatoare personalizate și de a defini domeniul de aplicare al acestor liste.
- Permite articole duplicate
- Eliminarea oricăror elemente existente
- Setarea unui separator personalizat
- Setarea unui interval specificat
- Executarea într-o foaie de lucru protejată
Activarea selecțiilor multiple în lista drop-down
Această secțiune oferă două metode pentru a vă ajuta să activați selecțiile multiple în lista derulantă în Excel.
Folosind codul VBA
Pentru a permite selecții multiple în lista derulantă, puteți utiliza Visual Basic pentru aplicații (VBA) în Excel. Scriptul poate modifica comportamentul unei liste derulante pentru a face din aceasta o listă cu alegeri multiple. Vă rugăm să faceți după cum urmează.
Pasul 1: Deschideți editorul Sheet (Code).
- Deschideți foaia de lucru care conține lista derulantă pentru care doriți să activați selecția multiplă.
- Faceți clic dreapta pe fila foii și selectați Afișați codul din meniul contextual.
Pasul 2: Folosiți codul VBA
Acum copiați următorul cod VBA și inserați-l în fereastra de deschidere a foii (Cod).
Cod VBA: Activați selecțiile multiple în lista derulantă 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
Rezultat
Când reveniți la foaia de lucru, lista derulantă vă va permite să alegeți mai multe opțiuni, vedeți demonstrația de mai jos:
Codul VBA de mai sus:
- Se aplică tuturor listelor derulante de validare a datelor din foaia de lucru curentă, atât cele existente, cât și celor create în viitor.
- Vă împiedică să alegeți același articol de mai multe ori în fiecare listă derulantă.
- Utilizează virgula ca separator pentru elementele selectate. Pentru a utiliza alți delimitatori, vă rugăm vizualizați această secțiune pentru a schimba separatorul.
Folosind Kutools pentru Excel în câteva clicuri
Dacă nu vă simțiți confortabil cu VBA, o alternativă mai ușoară este Kutools pentru Excel's Listă derulantă cu selectare multiplă caracteristică. Acest instrument ușor de utilizat simplifică activarea selecțiilor multiple în listele derulante, permițându-vă să personalizați separatorul și să gestionați duplicatele fără efort pentru a vă satisface diferitele nevoi.
După instalarea Kutools pentru Excel, mergeți la Kutools , selectați Lista verticală > Listă derulantă cu selectare multiplă. Apoi, trebuie să configurați după cum urmează.
- Specificați intervalul care conține lista derulantă din care trebuie să selectați mai multe elemente.
- Specificați separatorul pentru elementele selectate în celula listei derulante.
- Clic OK pentru a finaliza setările.
Rezultat
Acum, când faceți clic pe o celulă cu o listă derulantă în intervalul specificat, lângă ea va apărea o casetă de listă. Pur și simplu faceți clic pe butonul „+” de lângă elemente pentru a le adăuga în celula derulantă și faceți clic pe butonul „-” pentru a elimina elementele pe care nu le mai doriți. Vezi demo-ul de mai jos:
- Verifică Încheierea textului după inserarea unui separator opțiunea dacă doriți să afișați elementele selectate vertical în interiorul celulei. Dacă preferați o listă orizontală, lăsați această opțiune nebifată.
- Verifică Activați căutarea opțiune dacă doriți să adăugați o bară de căutare la lista dvs. derulantă.
- Pentru a aplica această caracteristică, vă rugăm descărcați și instalați Kutools pentru Excel mai întâi.
Mai multe operațiuni pentru lista derulantă cu selecție multiplă
Această secțiune colectează diferitele scenarii care pot fi necesare la activarea selecțiilor multiple din lista derulantă Validare date.
Permiterea elementelor duplicate în lista derulantă
Duplicatele pot fi o problemă atunci când sunt permise selecții multiple într-o listă derulantă. Codul VBA de mai sus nu permite elemente duplicate în lista derulantă. Dacă trebuie să păstrați articole duplicate, încercați codul VBA din această secțiune.
Cod VBA: Permite duplicate în lista derulantă de validare a datelor
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 SubRezultat
Acum puteți selecta mai multe articole din listele derulante din foaia de lucru curentă. Pentru a repeta un element dintr-o celulă din listă derulantă, continuați să selectați acel element din listă. Vedeți captura de ecran:
Eliminarea oricăror elemente existente din lista derulantă
După ce selectați mai multe articole dintr-o listă derulantă, uneori poate fi necesar să eliminați un element existent din celula listei derulante. Această secțiune oferă o altă bucată de cod VBA pentru a vă ajuta să îndepliniți această sarcină.
Cod VBA: eliminați toate elementele existente din celula listei derulante
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 SubRezultat
Acest cod VBA vă permite să selectați mai multe articole dintr-o listă derulantă și să eliminați cu ușurință orice element pe care l-ați ales deja. După ce ați selectat mai multe elemente, dacă doriți să eliminați unul anume, pur și simplu selectați-l din nou din listă.
Setarea unui separator personalizat
Delimitatorul este setat ca virgulă în codurile VBA de mai sus. Puteți modifica această variabilă la orice caracter preferat pentru a fi folosit ca separator pentru selecțiile listei derulante. Iată cum poți face:
După cum puteți vedea, toate codurile VBA de mai sus au următoarea linie:
delimiter = ", "Trebuie doar să schimbați virgula în orice separator după cum aveți nevoie. De exemplu, doriți să separați elementele prin punct și virgulă, schimbați linia în:
delimiter = "; "delimiter = vbNewLineSetarea unui interval specificat
Codurile VBA de mai sus se aplică tuturor listelor derulante din foaia de lucru curentă. Dacă doriți ca codurile VBA să se aplice doar unui anumit interval de liste derulante, puteți specifica intervalul în codul VBA de mai sus, după cum urmează.
După cum puteți vedea, toate codurile VBA de mai sus au următoarea linie:
Set TargetRange = Me.UsedRangeTrebuie doar să schimbați linia în:
Set TargetRange = Me.Range("C2:C10")Executarea într-o foaie de lucru protejată
Imaginează-ți că ai protejat o foaie de lucru cu parola "123" și setați celulele listei derulante la "descuiat" înainte de a activa protecția, asigurându-se astfel că funcția de selecție multiplă rămâne activă după protecție. Cu toate acestea, codurile VBA menționate mai sus nu pot funcționa în acest caz, iar această secțiune descrie un alt script VBA care este special conceput pentru a gestiona funcționalitatea de selecție multiplă într-o foaie de lucru protejată.
Cod VBA: Activați selecția multiplă în lista derulantă fără duplicate
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 SubPrin activarea selecțiilor multiple în listele derulante Excel, puteți îmbunătăți foarte mult funcționalitatea și flexibilitatea foilor de lucru. Indiferent dacă vă simțiți confortabil cu codificarea VBA sau preferați o soluție mai simplă, cum ar fi Kutools, acum aveți capacitatea de a vă transforma listele derulante standard în instrumente dinamice, cu selecție multiplă. Cu aceste abilități, sunteți acum echipat pentru a crea documente Excel mai dinamice și mai ușor de utilizat. Pentru cei dornici să aprofundeze capacitățile Excel, site-ul nostru web se mândrește cu o mulțime de tutoriale. Descoperiți mai multe sfaturi și trucuri Excel aici.
Cele mai bune instrumente de productivitate de birou
Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp. Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...
Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară
- Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
- Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ferestrele noi.
- Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
Cuprins
- Activarea selecțiilor multiple
- Folosind codul VBA
- Folosind Kutools pentru Excel în câteva clicuri
- Mai multe operațiuni
- Permite articole duplicate
- Eliminarea oricăror elemente existente
- Setarea unui separator personalizat
- Setarea unui interval specificat
- Executarea într-o foaie de lucru protejată
- Articole pe aceeaşi temă
- Cele mai bune instrumente de productivitate Office
- Comentarii