✅ Tworzenie zależnej listy rozwijanej w programie Excel (samouczek krok po kroku)

Spisie treści

Tworzenie zależnej listy rozwijanej w programie Excel

Obejrzyj wideo - tworzenie zależnej listy rozwijanej w programie Excel

Lista rozwijana programu Excel to przydatna funkcja podczas tworzenia formularzy wprowadzania danych lub pulpitów nawigacyjnych programu Excel.

Pokazuje listę elementów jako listę rozwijaną w komórce, a użytkownik może dokonać wyboru z listy rozwijanej. Może to być przydatne, gdy masz listę nazw, produktów lub regionów, które często musisz wprowadzać w zestawie komórek.

Poniżej znajduje się przykład rozwijanej listy programu Excel:

W powyższym przykładzie użyłem elementów z A2:A6 do utworzenia listy rozwijanej w C3.

Czytać: Oto szczegółowy przewodnik dotyczący tworzenia listy rozwijanej programu Excel.

Czasami jednak możesz chcieć użyć więcej niż jednej listy rozwijanej w programie Excel, tak aby elementy dostępne na drugiej liście rozwijanej były zależne od wyboru dokonanego na pierwszej liście rozwijanej.

Są to tak zwane zależne listy rozwijane w programie Excel.

Poniżej znajduje się przykład tego, co rozumiem przez zależną listę rozwijaną w programie Excel:

Widać, że opcje w rozwijanym 2 zależą od wyboru dokonanego w rozwijanym 1. Jeśli wybiorę „Owoce” w rozwijanym 1, zostaną mi wyświetlone nazwy owoców, ale jeśli wybiorę warzywa w rozwijanym 1, wtedy Pokazano mi nazwy warzyw w Drop Down 2.

Nazywa się to warunkową lub zależną listą rozwijaną w programie Excel.

Tworzenie zależnej listy rozwijanej w programie Excel

Oto kroki, aby utworzyć zależną listę rozwijaną w programie Excel:

Wybierz komórkę, w której chcesz pierwszą (główną) listę rozwijaną.
Przejdź do Dane -> Walidacja danych. Spowoduje to otwarcie okna dialogowego sprawdzania poprawności danych.
W oknie dialogowym sprawdzania poprawności danych, na karcie ustawień wybierz opcję Lista.
W polu Źródło określ zakres zawierający elementy, które mają być wyświetlane na pierwszej liście rozwijanej.
Kliknij OK. Spowoduje to utworzenie listy rozwijanej 1.
Wybierz cały zestaw danych (w tym przykładzie A1:B6).
Przejdź do Formuły -> Zdefiniowane nazwy -> Utwórz z zaznaczenia (lub możesz użyć skrótu klawiszowego Control + Shift + F3).
W oknie dialogowym „Utwórz nazwany z zaznaczenia” zaznacz opcję Górny wiersz i odznacz wszystkie pozostałe. W ten sposób tworzy się 2 zakresy nazw („Owoce” i „Warzywa”). Nazwany zakres owoców odnosi się do wszystkich owoców na liście, a nazwany zakres warzyw odnosi się do wszystkich warzyw na liście.
Kliknij OK.
Wybierz komórkę, w której chcesz wyświetlić listę rozwijaną Zależne/warunkowe (w tym przykładzie E3).
Przejdź do Dane -> Walidacja danych.
W oknie dialogowym Sprawdzanie poprawności danych, na karcie ustawień, upewnij się, że wybrano opcję Lista.
W polu Źródło wprowadź formułę =ADR.POŚR(D3). Tutaj D3 to komórka zawierająca główne menu rozwijane.
Kliknij OK.

Teraz, gdy dokonasz wyboru na liście rozwijanej 1, opcje wymienione na liście rozwijanej 2 zostaną automatycznie zaktualizowane.

Pobierz przykładowy plik

Jak to działa? - Warunkowa lista rozwijana (w komórce E3) odwołuje się do = ADR.POŚR(D3). Oznacza to, że po wybraniu „Owoce” w komórce D3 rozwijana lista w E3 odwołuje się do nazwanego zakresu „Owoce” (poprzez funkcję ADR.POŚREDNIA), a zatem wyświetla wszystkie elementy w tej kategorii.

Ważna uwaga: Jeśli główna kategoria zawiera więcej niż jedno słowo (na przykład „Owoce sezonowe” zamiast „Owoce”), należy użyć formuły =INDIRECT(SUBSTITUTE(D3”, „”_”)), zamiast prosta funkcja ADR.POŚREDNIA pokazana powyżej.

Powodem tego jest to, że Excel nie zezwala na spacje w nazwanych zakresach. Dlatego podczas tworzenia nazwanego zakresu przy użyciu więcej niż jednego słowa program Excel automatycznie wstawia podkreślenie między słowami. Na przykład, gdy utworzysz nazwany zakres za pomocą „Owoców sezonowych”, zostanie on nazwany w zapleczu o nazwie Season_Fruits. Użycie funkcji SUBSTITUTE w ramach funkcji ADR.POŚR zapewnia, że spacje są zamienione na podkreślenia.

Zresetuj/Wyczyść zawartość zależnej listy rozwijanej automatycznie

Po dokonaniu wyboru, a następnie zmianie nadrzędnej listy rozwijanej, zależna lista rozwijana nie zmieni się i dlatego będzie błędnym wpisem.

Na przykład, jeśli wybierzesz „Owoce” jako kategorię, a następnie wybierzesz Apple jako element, a następnie wrócisz i zmienisz kategorię na „Warzywa”, zależne menu rozwijane będzie nadal pokazywać Apple jako element.

Możesz użyć VBA, aby upewnić się, że zawartość zależnej listy rozwijanej resetuje się po każdej zmianie głównej listy rozwijanej.

Oto kod VBA, aby wyczyścić zawartość zależnej listy rozwijanej:

Private Sub Worksheet_Change(ByVal Target As Range) W przypadku błędu Wznów Next If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset(0, 1)).ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub

Kredyt za ten kod trafia do tego samouczka Debry na temat czyszczenia zależnych list rozwijanych w programie Excel po zmianie wyboru.

Oto jak sprawić, by ten kod działał:

Skopiuj kod VBA.
W skoroszycie programu Excel, w którym znajduje się zależna lista rozwijana, przejdź do karty Deweloper, a w grupie „Kod” kliknij Visual Basic (możesz również użyć skrótu klawiaturowego - ALT + F11).
W oknie edytora VB, po lewej stronie eksploratora projektów, zobaczysz wszystkie nazwy arkuszy roboczych. Kliknij dwukrotnie ten, który ma rozwijaną listę.
Wklej kod w oknie kodu po prawej stronie.
Zamknij edytor VB.

Teraz, gdy zmienisz główną listę rozwijaną, kod VBA zostanie uruchomiony i wyczyści zawartość zależnej listy rozwijanej (jak pokazano poniżej).

Jeśli nie jesteś fanem VBA, możesz również użyć prostej sztuczki formatowania warunkowego, która podświetli komórkę, gdy wystąpi niezgodność. Pomoże to wizualnie zobaczyć i skorygować niezgodność (jak pokazano poniżej).

Oto kroki t0 podświetlania niezgodności na zależnych listach rozwijanych:

Wybierz komórkę, która ma zależne listy rozwijane.
Przejdź do Strona główna -> Formatowanie warunkowe -> Nowa reguła.
W oknie dialogowym Nowa reguła formatowania wybierz „Użyj formuły, aby określić, które komórki należy sformatować”.
W polu formuły wprowadź następującą formułę: =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0))
Ustaw format.
Kliknij OK.

Formuła używa funkcji WYSZUKAJ.PIONOWO, aby sprawdzić, czy element na zależnej liście rozwijanej należy do kategorii głównej, czy nie. Jeśli tak nie jest, formuła zwraca błąd. Jest to używane przez funkcję ISERROR do zwrócenia wartości TRUE, która nakazuje formatowaniu warunkowemu podświetlenie komórki.

Możesz również polubić następujące samouczki programu Excel: