Izberite več elementov na spustnem seznamu Excel – celoten vodnik
Excelovi spustni seznami so fantastično orodje za zagotavljanje doslednosti podatkov in enostavnosti vnosa. Vendar vas privzeto omejujejo na izbiro samo enega elementa. Kaj pa, če morate izbrati več elementov z istega spustnega seznama? Ta obsežen vodnik bo raziskal metode za omogočanje več izbir na Excelovih spustnih seznamih, upravljanje dvojnikov, nastavitev ločil po meri in definiranje obsega teh seznamov.
- Dovolite podvojene elemente
- Odstranjevanje vseh obstoječih elementov
- Nastavitev ločila po meri
- Nastavitev določenega obsega
- Izvajanje v zaščitenem delovnem listu
Omogočanje več izbir na spustnem seznamu
V tem razdelku sta na voljo dve metodi, ki vam pomagata omogočiti več izbir na spustnem seznamu v Excelu.
Uporaba kode VBA
Če želite omogočiti več izbir na spustnem seznamu, lahko uporabite Visual Basic za aplikacije (VBA) v Excelu. Skript lahko spremeni vedenje spustnega seznama, da postane seznam z več možnostmi. Prosimo, naredite naslednje.
1. korak: Odprite urejevalnik listov (kode).
- Odprite delovni list, ki vsebuje spustni seznam, za katerega želite omogočiti večkratno izbiro.
- Desni klik na zavihek lista in izberite Ogled kode iz kontekstnega menija.
2. korak: Uporabite kodo VBA
Zdaj kopirajte naslednjo kodo VBA in jo prilepite v okno začetnega lista (koda).
Koda VBA: omogočite več izbir na spustnem seznamu 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
Ko se vrnete na delovni list, vam bo spustni seznam omogočil izbiro več možnosti, glejte spodnjo predstavitev:
Zgornja koda VBA:
- Velja za vse spustne sezname za preverjanje veljavnosti podatkov na trenutnem delovnem listu, tako obstoječe kot tiste, ki bodo ustvarjeni v prihodnosti.
- Preprečuje, da bi isti element izbrali več kot enkrat na vsakem spustnem seznamu.
- Uporablja vejico kot ločilo za izbrane elemente. Za uporabo drugih ločil, prosim si oglejte ta razdelek, če želite spremeniti ločilo.
Uporaba Kutools za Excel v nekaj klikih
Če niste zadovoljni z VBA, je lažja alternativa Kutools za Excel's Večkrat izberite spustni seznam funkcija. To uporabniku prijazno orodje poenostavi omogočanje več izbir na spustnih seznamih, kar vam omogoča, da prilagodite ločilo in preprosto upravljate dvojnike, da zadostite različnim potrebam.
po namestitev Kutools for Excel, pojdite na Kutools jeziček, izberite Spustni seznam > Večkrat izberite spustni seznam. Nato morate konfigurirati na naslednji način.
- Določite obseg, ki vsebuje spustni seznam, s katerega morate izbrati več elementov.
- Določite ločilo za izbrane elemente v celici spustnega seznama.
- klik OK za dokončanje nastavitev.
Rezultat
Zdaj, ko kliknete na celico s spustnim seznamom v določenem obsegu, se bo poleg nje prikazalo polje s seznamom. Preprosto kliknite gumb »+« poleg elementov, da jih dodate v spustno celico, in kliknite gumb »-«, da odstranite vse elemente, ki jih ne želite več. Oglejte si spodnjo predstavitev:
- Prijava Prelomi besedilo po vstavitvi ločila možnost, če želite izbrane elemente prikazati navpično znotraj celice. Če imate raje vodoravni seznam, pustite to možnost neoznačeno.
- Prijava Omogoči iskanje če želite na spustni seznam dodati iskalno vrstico.
- Če želite uporabiti to funkcijo, prosim prenesite in namestite Kutools za Excel najprej.
Več operacij za spustni seznam z več izbirami
V tem razdelku so zbrani različni scenariji, ki so lahko potrebni pri omogočanju več izbir na spustnem seznamu Preverjanje podatkov.
Dovolite podvojene elemente na spustnem seznamu
Dvojniki so lahko težava, če je na spustnem seznamu dovoljenih več izbir. Zgornja koda VBA ne dovoljuje podvojenih elementov na spustnem seznamu. Če morate obdržati podvojene elemente, poskusite s kodo VBA v tem razdelku.
Koda VBA: Dovoli dvojnike na spustnem seznamu za preverjanje podatkov
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
Zdaj lahko izberete več elementov s spustnih seznamov v trenutnem delovnem listu. Če želite ponoviti element v celici spustnega seznama, nadaljujte z izbiro tega elementa s seznama. Oglejte si posnetek zaslona:
Odstranjevanje vseh obstoječih elementov s spustnega seznama
Ko izberete več elementov s spustnega seznama, boste morda morali včasih odstraniti obstoječi element iz celice spustnega seznama. V tem razdelku je še en del kode VBA, ki vam bo v pomoč pri izpolnjevanju te naloge.
Koda VBA: odstranite vse obstoječe elemente iz celice spustnega seznama
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
Ta koda VBA vam omogoča, da izberete več elementov s spustnega seznama in preprosto odstranite kateri koli element, ki ste ga že izbrali. Ko izberete več elementov, če želite odstraniti določenega, ga preprosto znova izberite s seznama.
Nastavitev ločila po meri
Ločilo je v zgornjih kodah VBA nastavljeno kot vejica. To spremenljivko lahko spremenite v kateri koli želeni znak, ki ga uporabite kot ločilo za izbire spustnega seznama. Tukaj je, kako lahko storite:
Kot lahko vidite, imajo zgornje kode VBA naslednjo vrstico:
delimiter = ", "Samo spremeniti morate vejico v poljubno ločilo, kot ga potrebujete. Na primer, če želite elemente ločiti s podpičjem, spremenite vrstico v:
delimiter = "; "delimiter = vbNewLineNastavitev določenega obsega
Zgornje kode VBA veljajo za vse spustne sezname v trenutnem delovnem listu. Če želite, da se kode VBA uporabljajo samo za določen obseg spustnih seznamov, lahko obseg določite v zgornji kodi VBA, kot sledi.
Kot lahko vidite, imajo zgornje kode VBA naslednjo vrstico:
Set TargetRange = Me.UsedRangeSamo spremeniti morate vrstico v:
Set TargetRange = Me.Range("C2:C10")Izvajanje v zaščitenem delovnem listu
Predstavljajte si, da ste delovni list zaščitili z geslom "123" in nastavite celice spustnega seznama na "odklenjena" pred aktiviranjem zaščite, s čimer zagotovite, da funkcija večkratne izbire ostane aktivna tudi po zaščiti. Vendar zgoraj omenjene kode VBA v tem primeru ne morejo delovati in ta razdelek opisuje drug skript VBA, ki je posebej zasnovan za upravljanje funkcije več izbir v zaščitenem delovnem listu.
Koda VBA: Omogoči večkratno izbiro na spustnem seznamu brez dvojnikov
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Če omogočite več izbir v Excelovih spustnih seznamih, lahko močno izboljšate funkcionalnost in prilagodljivost svojih delovnih listov. Ne glede na to, ali ste zadovoljni s kodiranjem VBA ali imate raje enostavnejšo rešitev, kot je Kutools, imate zdaj možnost, da spremenite svoje standardne spustne sezname v dinamična orodja z več izbirami. S temi veščinami ste zdaj opremljeni za ustvarjanje bolj dinamičnih in uporabniku prijaznih Excelovih dokumentov. Za tiste, ki se želijo poglobiti v zmogljivosti Excela, se naše spletno mesto ponaša s številnimi vadnicami. Tukaj odkrijte več nasvetov in trikov za Excel.
Najboljša pisarniška orodja za produktivnost
Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa. Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...
Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo
- Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
- Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
- Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Kazalo
- Omogočanje več izbir
- Uporaba kode VBA
- Uporaba Kutools za Excel v nekaj klikih
- Več operacij
- Dovolite podvojene elemente
- Odstranjevanje vseh obstoječih elementov
- Nastavitev ločila po meri
- Nastavitev določenega obsega
- Izvajanje v zaščitenem delovnem listu
- Povezani članki
- Najboljša orodja za pisarniško produktivnost
- Komentarji