Utwórz dynamiczną zależną listę rozwijaną w programie Excel (krok po kroku)
W tym samouczku krok po kroku przedstawimy, jak utworzyć zależną listę rozwijaną, która wyświetla wybory w zależności od wartości wybranych na pierwszej liście rozwijanej. Innymi słowy, stworzymy listę sprawdzania poprawności danych Excela na podstawie wartości innej listy.
Utwórz dynamiczną zależną listę rozwijaną
10s, aby utworzyć zależną listę rozwijaną za pomocą poręcznego narzędzia
Utwórz dynamiczną zależną listę rozwijaną w programie Excel 2021 lub Excel 365
Niektóre pytania, które możesz zadać na temat tego samouczka
Pobierz bezpłatnie przykładowy plik 
Wideo: Utwórz listę rozwijaną zależną od programu Excel
Utwórz dynamiczną zależną listę rozwijaną
Krok 1: Wpisz wpisy na listach rozwijanych
1. Najpierw wpisz wpisy, które mają się pojawić na listach rozwijanych, każdą listę w kolumnie osobno.
Ogłoszenie że pozycje w pierwszej kolumnie (Produkt) będą później nazwami Excel dla list zależnych. Na przykład tutaj Owoce i Warzywa będą osobno Nazwami dla kolumn B2:B5 i C2:C6.
Zobacz zrzut ekranu:
2. Następnie utwórz tabele dla każdej listy danych.
Wybierz zakres kolumn A1:A3, kliknij wstawka > Stół, a następnie w oknie dialogowym Utwórz tabelę zaznacz Moja tabela ma nagłówki pole wyboru. Kliknij OK.
Następnie powtórz ten krok, aby utworzyć tabele dla pozostałych dwóch list.
Możesz przeglądać wszystkie tabele i odniesienia do zakresów w Menedżerze nazw (naciśnij Ctrl + F3 aby go otworzyć).
Krok 2: Utwórz nazwy zakresów
W tym kroku musisz utworzyć nazwy dla listy głównej i każdej listy zależnej.
1. Wybierz elementy, które pojawiają się na liście głównej (A2: A3).
2. Następnie przejdź do Pole nazwy który obok Pasek formuły.
3. Wpisz w nim nazwę, tutaj nazwij ją jako Produkt.
4. naciśnij Wchodzę klucz do uzupełnienia.
Następnie powtórz powyższe kroki, aby osobno utworzyć Nazwy dla każdej listy zależnej.
Tutaj drugą kolumnę (B2:B5) nazywamy Owocami, a trzecią kolumnę (C2:C6) – Warzywa.
Możesz wyświetlić wszystkie nazwy zakresów w Menedżerze nazw (naciśnij Ctrl + F3 aby go otworzyć).
Krok 3: Dodaj główną listę rozwijaną
Następnie dodaj główną listę rozwijaną (Produkt), która jest normalną listą rozwijaną sprawdzania poprawności danych, a nie zależną listą rozwijaną.
1. Najpierw utwórz tabelę.
Wybierz komórkę (E1) i wpisz nagłówek pierwszej kolumny (Produkt) i przejść do następnej komórki kolumny (F1), wpisz nagłówek drugiej kolumny (Pozycja). Dodasz listę rozwijaną do tej tabeli.
Następnie wybierz te dwa nagłówki (E1 i F1), Kliknij wstawka Zakładka i wybierz Stół w grupie Tabele.
W oknie dialogowym Utwórz tabelę zaznacz Moja tabela ma nagłówki i kliknij OK.
2. Wybierz komórkę E2 do którego chcesz wstawić główną listę rozwijaną, kliknij Dane kartę i przejdź do Narzędzia danych grupa do kliknięcia Walidacja danych > Walidacja danych.
3. W oknie dialogowym Sprawdzanie poprawności danych
- Dodaj Lista Dopuszczać Sekcja,
- Wpisz poniżej formułę do Źródło bar, Produkt to nazwa listy głównej,
- Kliknij OK.
=Product
Możesz zobaczyć, że główna lista rozwijana została utworzona.
Krok 4: Dodaj zależną listę rozwijaną
1. Wybierz komórkę F2 do którego chcesz dodać zależną listę rozwijaną, kliknij Dane kartę i przejdź do grupy Narzędzia danych, aby kliknąć Walidacja danych > Walidacja danych.
2. W oknie dialogowym Sprawdzanie poprawności danych
- Dodaj Lista Dopuszczać Sekcja,
- Wpisz poniższą formułę do Źródło bar, E2 to komórka zawierająca główną listę rozwijaną.
- Kliknij OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
Jeśli E2 jest puste (nie wybrałeś żadnej pozycji z głównej listy rozwijanej), zobaczysz komunikat jak poniżej, kliknij Tak aby kontynuować.
Teraz utworzono zależną listę rozwijaną.
Krok 5: Przetestuj zależną listę rozwijaną.
1. wybierać Owoc na głównej liście rozwijanej (E2), a następnie przejdź do zależnej listy rozwijanej (F2), aby kliknąć ikonę strzałki, sprawdzić, czy owoce znajdują się na liście, a następnie wybrać jedną pozycję z zależnej listy rozwijanej.
2. naciśnij zakładka aby rozpocząć nowy wiersz w tabeli wprowadzania danych, wybierz Warzywoi przejdź do następnej komórki po prawej stronie, sprawdź, czy na liście znajdują się warzywa, a następnie wybierz jedną pozycję z zależnej listy rozwijanej.
- Jeśli na głównej liście rozwijanej (kolumna Produkt) nie ma zaznaczonej pozycji, lista rozwijana zależna (kolumna Pozycja) nie będzie działać.
- Jeśli chcesz zresetować lub wyczyścić zawartość rozwijanej listy zależności po wybraniu zmienionej, przejdź do tego artykułu Jak wyczyścić zależną komórkę listy rozwijanej po wybraniu zmiany w programie Excel?, wprowadza kod VBA, który może ci pomóc.
- Jeśli chcesz utworzyć 3-poziomową listę rozwijaną, zapoznaj się z tym artykułem Jak utworzyć listę rozwijaną zależną od wielu poziomów w programie Excel? pomoże Ci.
10s, aby utworzyć zależną listę rozwijaną za pomocą poręcznego narzędzia
Kutools dla programu Excel zapewnia potężne narzędzie ułatwiające i przyspieszające zależne listy rozwijane, zobaczmy:
Przed wykonaniem poniższych kroków proszę kliknij, aby pobrać Kutools dla programu Excel na 30-dniowy bezpłatny okres próbny po pierwsze.
Krok 1: Wpisz wpisy z listy rozwijanej
Najpierw uporządkuj swoje dane tak, jak pokazano na poniższym zrzucie ekranu:
Krok 2: Stosowanie narzędzia Kutools
1. Wybierz utworzone dane, kliknij Kutools kartę i kliknij Lista rozwijana aby wyświetlić podmenu, kliknij Dynamiczna lista rozwijana.
2. Na liście rozwijanej Zależne
- Sprawdź Tryb B który pasuje do twojego trybu danych,
- Wybierz zakres wyjściowy, kolumna zakresu wyjściowego musi być równa kolumnie zakresu danych,
- Kliknij Ok.
Teraz utworzono zależną listę rozwijaną.
- Tryb B obsługuje tworzenie listy rozwijanej trzeciego lub więcej poziomów:
- Jeśli Twoje dane są ułożone tak, jak pokazuje poniższy zrzut ekranu, musisz użyć trybu A, tryb A obsługuje tylko tworzenie 2-poziomowej zależnej listy rozwijanej.
- Więcej informacji na temat korzystania z Kutools do tworzenia zależnej listy rozwijanej można znaleźć na stronie ten poradnik .
Utwórz dynamiczną zależną listę rozwijaną w programie Excel 2021 lub Excel 365
Jeśli korzystasz z programu Excel 2021 lub Excel 365, istnieje inny sposób szybkiego tworzenia dynamicznej zależnej listy rozwijanej za pomocą nowych funkcji NIECODZIENNCH i FILTER.
Załóżmy, że Twoje dane źródłowe są ułożone tak, jak pokazano na zrzucie ekranu, wykonaj poniższe kroki, aby utworzyć dynamiczną listę rozwijaną.
Krok 1: Wykorzystanie formuły do pobrania elementów z głównej listy rozwijanej
Wybierz komórkę, na przykład komórkę G3, i za pomocą funkcji UNIQUE i FILTER wyodrębnij unikalne wartości z Produkt listę, która będzie źródłem głównej listy rozwijanej i naciśnij Wchodzę klawisz.
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Krok 2: Utwórz główną listę rozwijaną
1. Wybierz komórkę, w której chcesz umieścić główną listę rozwijaną, na przykład komórkę D3kliknij Dane kartę i przejdź do Narzędzia danych grupa do kliknięcia Walidacja danych > Walidacja danych.
2. W oknie dialogowym Sprawdzanie poprawności danych
- Dodaj Lista Dopuszczać Sekcja,
- Wpisz poniżej formułę do Źródło bar,
- Kliknij OK.
=$G$3#
Teraz tworzona jest główna lista rozwijana.
Krok 3: Korzystanie z formuły w celu uzyskania elementów z zależnej listy rozwijanej
Wybierz komórkę, na przykład komórkę H3, używając funkcji FILTRUJ, aby przefiltrować elementy na podstawie wartości w komórce D3 (wybrana pozycja na głównej liście rozwijanej), naciśnij Wchodzę klawisz.
=FILTER(B3:B20, A3:A20=D3)
Krok 4: Utwórz zależną listę rozwijaną
1. Wybierz komórkę, w której zostanie umieszczona zależna lista rozwijana, np. komórka E3kliknij Dane kartę i przejdź do Narzędzia danych grupa do kliknięcia Walidacja danych > Walidacja danych.
2. W oknie dialogowym Sprawdzanie poprawności danych
- Dodaj Lista Dopuszczać Sekcja,
- Wpisz poniżej formułę do Źródło bar,
- Kliknij OK.
=$H$3#
Teraz pomyślnie utworzono zależną listę rozwijaną.
Gdy dodasz nowe elementy lub dokonasz zmian w A3:A20, lista rozwijana zostanie automatycznie zaktualizowana.
Sortuj listę rozwijaną alfabetycznie
Jeśli chcesz ułożyć pozycje na rozwijanej liście alfabetycznie, możesz skorzystać z poniższego wzoru do tabeli przygotowania.Dla głównego menu rozwijanego (formuła w komórce G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))Dla zależnego menu rozwijanego (formuła w komórce H3):
=SORT(FILTER(B3:B20, A3:A20=D3))Teraz obie listy rozwijane są sortowane alfabetycznie od A do Z.
Aby posortować alfabetycznie od Z do A, skorzystaj z poniższego wzoru:
Dla głównego menu rozwijanego (formuła w komórce G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)Dla zależnego menu rozwijanego (formuła w komórce H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)Niektóre pytania, które możesz zadać:
1. Po co wstawiać tabelę dla każdej listy danych?
Wstawienie tabeli do listy danych pomoże w automatycznej aktualizacji listy rozwijanej na podstawie zmian na liście danych. Na przykład, dodając „Inne” na pierwszej liście danych, główna lista rozwijana zostanie automatycznie dodana z „Innymi”.
2. Po co używać tabeli do umieszczania list rozwijanych?
Gdy naciśniesz klawisz Tab, aby dodać nową linię do tabeli, listy rozwijane zostaną automatycznie dodane również w nowej linii.
3. Jak działa funkcja POŚREDNI?
POŚREDNI służy do konwersji ciągu tekstowego na prawidłowe odwołanie.
4. Jak działa formuła INDIRECT(SUBSTITUTE(E2&F2,"",""))?
Po pierwsze, ZASTĄPIĆ funkcja zastępuje tekst innym tekstem. Tutaj usuwano spacje z połączonych nazw (E2 i F2). Następnie POŚREDNI funkcja konwertuje ciąg tekstowy (połączoną zawartość przez E2 i F2) na prawidłowe odwołanie.
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!
