Przejdź do głównej zawartości

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.

Wskazówka: Przed zastosowaniem poniższych metod upewnij się, że wcześniej utworzyłeś listy rozwijane w swoich arkuszach. Jeśli chcesz wiedzieć, jak tworzyć listy rozwijane sprawdzania poprawności danych, postępuj zgodnie z instrukcjami zawartymi w tym artykule: Jak utworzyć listy rozwijane sprawdzania poprawności danych w programie Excel.

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).
  1. Otwórz arkusz zawierający listę rozwijaną, dla której chcesz włączyć wielokrotny wybór.
  2. 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:

Uwagi:
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.

  1. Określ zakres zawierający listę rozwijaną, z której należy wybrać wiele pozycji.
  2. Określ separator dla wybranych elementów w komórce listy rozwijanej.
  3. 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:

Uwagi:
  • 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 Sub
Wynik

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 Sub
Wynik

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 = "; "
Uwaga: Aby zmienić ogranicznik na znak nowego wiersza w tych kodach VBA, zmień tę linię na:
delimiter = vbNewLine

Ustawianie 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.UsedRange

Wystarczy zmienić linię na:

Set TargetRange = Me.Range("C2:C10")
Note: Tutaj C2: C10 to zakres zawierający listę rozwijaną, którą chcesz ustawić jako wielokrotny wybór.

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 Sub
Note: W kodzie pamiętaj o zastąpieniu „Twoje hasło” w wierszu pswd = "twoje hasło" z rzeczywistym hasłem, którego używasz do ochrony arkusza. Na przykład, jeśli Twoje hasło to „abc123", wtedy linia powinna być pswd = "abc123".

Włą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

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty   |  Usuń puste wiersze   |  Łącz kolumny lub komórki bez utraty danych   |   Okrągły bez wzoru ...
Super wyszukiwanie: Wiele kryteriów VLookup    Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  Zależna lista rozwijana   |  Lista rozwijana wielokrotnego wyboru ....
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj zakresy i kolumny ...
Polecane funkcje: Fokus siatki   |  Widok projektu   |   Duży pasek formuły    Menedżer skoroszytów i arkuszy   |  Biblioteka zasobów (Automatyczny tekst)   |  Selektor dat   |  Połącz arkusze   |  Szyfruj/odszyfruj komórki    Wysyłaj e-maile według listy   |  Super filtr   |   Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi12 Tekst Tools (Dodaj tekst, Usuń znaki, ...)   |   50 + Wykres rodzaje (Wykres Gantta, ...)   |   40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin, ...)   |   19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki, ...)   |   12 Konwersja Tools (Liczby na słowa, Przeliczanie walut, ...)   |   7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podział komórki, ...)   |   ... i więcej

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...

Opis


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!