Wybierz wiele elementów z listy rozwijanej programu Excel – pełny przewodnik
Listy rozwijane w programie Excel są fantastycznym narzędziem zapewniającym spójność danych i łatwość ich wprowadzania. Jednak domyślnie ograniczają Cię do wybrania tylko jednego elementu. Ale co, jeśli chcesz wybrać wiele elementów z tej samej listy rozwijanej? W tym obszernym przewodniku zostaną omówione metody umożliwiające wielokrotny wybór na listach rozwijanych programu Excel, zarządzanie duplikatami, ustawianie niestandardowych separatorów i definiowanie zakresu tych list.
- Zezwalanie na duplikaty elementów
- Usuwanie wszelkich istniejących elementów
- Ustawianie niestandardowego separatora
- Ustawianie określonego zakresu
- Wykonywanie w chronionym arkuszu
Włączanie wielu wyborów na liście rozwijanej
W tej sekcji przedstawiono dwie metody ułatwiające włączenie wielokrotnego wyboru na liście rozwijanej w programie Excel.
Korzystanie z kodu VBA
Aby umożliwić wielokrotny wybór z listy rozwijanej, możesz użyć Visual Basic dla aplikacji (VBA) w Excelu. Skrypt może modyfikować zachowanie listy rozwijanej, tak aby stała się listą wielokrotnego wyboru. Wykonaj następujące czynności.
Krok 1: Otwórz edytor arkuszy (kodu).
- Otwórz arkusz zawierający listę rozwijaną, dla której chcesz włączyć wielokrotny wybór.
- Kliknij prawym przyciskiem myszy kartę arkusza i wybierz Wyświetl kod z menu kontekstowego.
Krok 2: Użyj kodu VBA
Teraz skopiuj następujący kod VBA i wklej go do okna arkusza otwierającego (Kod).
Kod VBA: Włącz wielokrotny wybór na liście rozwijanej programu 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
Wynik
Kiedy wrócisz do arkusza, lista rozwijana umożliwi Ci wybór wielu opcji – zobacz demonstrację poniżej:
Powyższy kod VBA:
- Dotyczy wszystkich list rozwijanych sprawdzania poprawności danych w bieżącym arkuszu, zarówno istniejących, jak i tych utworzonych w przyszłości.
- Uniemożliwia wybranie tego samego elementu więcej niż raz z każdej listy rozwijanej.
- Używa przecinka jako separatora dla wybranych elementów. Aby użyć innych ograniczników, prosimy zobacz tę sekcję, aby zmienić separator.
Korzystanie z Kutools dla Excela za pomocą kilku kliknięć
Jeśli nie czujesz się komfortowo z VBA, łatwiejszą alternatywą jest Kutools dla programu Excel's Lista rozwijana wielokrotnego wyboru funkcja. To przyjazne dla użytkownika narzędzie upraszcza umożliwianie wielokrotnego wyboru na listach rozwijanych, umożliwiając dostosowanie separatora i łatwe zarządzanie duplikatami w celu spełnienia różnych potrzeb.
Po instalowanie Kutools dla Excela, idź do Kutools kartę, wybierz Lista rozwijana > Lista rozwijana wielokrotnego wyboru. Następnie musisz skonfigurować w następujący sposób.
- Określ zakres zawierający listę rozwijaną, z której należy wybrać wiele pozycji.
- Określ separator dla wybranych elementów w komórce listy rozwijanej.
- Kliknij OK aby zakończyć ustawienia.
Wynik
Teraz, gdy klikniesz komórkę z listą rozwijaną w określonym zakresie, obok niej pojawi się pole listy. Po prostu kliknij przycisk „+” obok elementów, aby dodać je do rozwijanej komórki, i kliknij przycisk „-”, aby usunąć elementy, których już nie potrzebujesz. Zobacz demo poniżej:
- Sprawdź Zawijaj tekst po wstawieniu separatora opcję, jeśli chcesz wyświetlić wybrane elementy w komórce pionowo. Jeśli wolisz listę poziomą, pozostaw tę opcję niezaznaczoną.
- Sprawdź Włącz wyszukiwanie opcję, jeśli chcesz dodać pasek wyszukiwania do listy rozwijanej.
- Aby zastosować tę funkcję, proszę pobierz i zainstaluj Kutools dla Excela pierwszy.
Więcej operacji dla listy rozwijanej wielokrotnego wyboru
W tej sekcji zebrano różne scenariusze, które mogą być wymagane w przypadku umożliwienia wielokrotnego wyboru na liście rozwijanej Walidacja danych.
Zezwalanie na duplikaty elementów na liście rozwijanej
Duplikaty mogą stanowić problem, jeśli na liście rozwijanej dozwolone jest wielokrotne zaznaczenie. Powyższy kod VBA nie pozwala na powielanie pozycji na liście rozwijanej. Jeśli chcesz zachować zduplikowane elementy, wypróbuj kod VBA w tej sekcji.
Kod VBA: Zezwalaj na duplikaty na liście rozwijanej sprawdzania poprawności danych
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 SubWynik
Teraz możesz wybrać wiele elementów z list rozwijanych w bieżącym arkuszu. Aby powtórzyć element w komórce listy rozwijanej, kontynuuj wybieranie tego elementu z listy. Zobacz zrzut ekranu:
Usuwanie istniejących elementów z listy rozwijanej
Po wybraniu wielu elementów z listy rozwijanej może czasami zaistnieć potrzeba usunięcia istniejącego elementu z komórki listy rozwijanej. W tej sekcji przedstawiono kolejny fragment kodu VBA, który pomoże w wykonaniu tego zadania.
Kod VBA: Usuń wszystkie istniejące elementy z komórki listy rozwijanej
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 SubWynik
Ten kod VBA umożliwia wybranie wielu pozycji z listy rozwijanej i łatwe usunięcie dowolnego już wybranego elementu. Jeśli po wybraniu wielu pozycji chcesz usunąć konkretny, po prostu wybierz go ponownie z listy.
Ustawianie niestandardowego separatora
W powyższych kodach VBA ogranicznik jest ustawiony jako przecinek. Możesz zmienić tę zmienną na dowolny preferowany znak, który będzie używany jako separator dla wyborów z listy rozwijanej. Oto jak możesz to zrobić:
Jak widać, wszystkie powyższe kody VBA mają następującą linię:
delimiter = ", "Wystarczy zmienić przecinek na dowolny separator, według potrzeb. Na przykład, jeśli chcesz oddzielić elementy średnikiem, zmień linię na:
delimiter = "; "delimiter = vbNewLineUstawianie określonego zakresu
Powyższe kody VBA dotyczą wszystkich list rozwijanych w bieżącym arkuszu. Jeśli chcesz, aby kody VBA miały zastosowanie tylko do określonego zakresu list rozwijanych, możesz określić zakres w powyższym kodzie VBA w następujący sposób.
Jak widać, wszystkie powyższe kody VBA mają następującą linię:
Set TargetRange = Me.UsedRangeWystarczy zmienić linię na:
Set TargetRange = Me.Range("C2:C10")Wykonywanie w chronionym arkuszu
Wyobraź sobie, że zabezpieczyłeś arkusz hasłem „123" i ustaw komórki listy rozwijanej na "odblokowany" przed aktywacją ochrony, zapewniając w ten sposób, że funkcja wielokrotnego wyboru pozostanie aktywna po zabezpieczeniu. Jednakże powyższe kody VBA nie mogą działać w tym przypadku i w tej sekcji opisano inny skrypt VBA, który został specjalnie zaprojektowany do obsługi funkcji wielokrotnego wyboru w chronionym arkuszu.
Kod VBA: Włącz wielokrotny wybór z listy rozwijanej bez duplikatów
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 SubWłączając wielokrotny wybór na listach rozwijanych programu Excel, możesz znacznie zwiększyć funkcjonalność i elastyczność arkuszy kalkulacyjnych. Niezależnie od tego, czy czujesz się komfortowo z kodowaniem VBA, czy wolisz prostsze rozwiązanie, takie jak Kutools, masz teraz możliwość przekształcenia standardowych list rozwijanych w dynamiczne narzędzia z możliwością wielokrotnego wyboru. Dzięki tym umiejętnościom możesz teraz tworzyć bardziej dynamiczne i przyjazne dla użytkownika dokumenty Excel. Dla tych, którzy chcą głębiej zagłębić się w możliwości Excela, nasza strona internetowa oferuje mnóstwo samouczków. Tutaj znajdziesz więcej porad i wskazówek dotyczących Excela.
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
Spis treści
- Włączanie wielokrotnego wyboru
- Korzystanie z kodu VBA
- Korzystanie z Kutools dla Excela za pomocą kilku kliknięć
- Więcej operacji
- Zezwalanie na duplikaty elementów
- Usuwanie wszelkich istniejących elementów
- Ustawianie niestandardowego separatora
- Ustawianie określonego zakresu
- Wykonywanie w chronionym arkuszu
- Powiązane artykuły
- Najlepsze narzędzia biurowe
- Komentarze