Do tej pory w tej serii sprawdzania poprawności danych nauczyliśmy się tworzyć normalną listę rozwijaną i dynamiczną listę rozwijaną przy użyciu różnych technik z walidacją danych w programie Excel.
A dzisiaj, w tym rozdziale, pokażemy, jak utworzyć listę rozwijaną Dependent w programie Microsoft Excel, używając różnych metod.
Zależna lista rozwijana jest również znana jako kaskadowa walidacja danych i ogranicza wybór z listy rozwijanej, w zależności od wartości wybranej w innej komórce zawierającej walidację danych. Innymi słowy, zależy to od wartości wybranej na pierwszej liście rozwijanej, która określa wartości, które mają być wyświetlane na drugiej liście rozwijanej.
To bardzo częsty scenariusz pracy z dużymi danymi lub niektórymi raportami dynamicznymi, gdzie chcesz mieć 2NS komórka wyświetla listę, która jest zależna od elementu listy wybranego w pierwszym menu rozwijanym.
Jak wiemy, w Excelu sposobów na wykonanie konkretnego zadania jest wiele i podobnie, istnieje wiele sposobów tworzenia zależnej walidacji danych w Excelu. A dzisiaj zademonstrujemy 5 różnych technik tworzenia listy walidacji danych zależnych.
Surowe dane mogą być w dowolnej kolejności lub formacie i za każdym razem nie możesz zmienić danych ani formatu, aby uzyskać to, czego szukasz.
Tak więc wzięliśmy jeden zestaw danych, ale w 3 różnych formatach, aby uzyskać zależną listę rozwijaną. I, jak widać, nasze dane znajdują się po lewej stronie, czyli z kolumny A do kolumny E, a nasze oczekiwane dane wyjściowe będziemy mieli po prawej stronie, czyli w kolumnie J i K. Kolumna J będzie miała podstawową walidację lista, natomiast kolumna K będzie zależna i wyświetli wartości w zależności od wartości wybranej w kolumnie J.
1NS Przykład:-
2NS Przykład:-
3r & D Przykład:-
1NS Przykład:-
Mamy listę produktów dla każdego kodu produktu od kolumny A8 do E13. I chcemy wybrać kod produktu w J10, a następnie w zależności od wybranego kodu produktu nazwę produktu w komórce K10.
Pierwsza metoda:-
Pierwsza metoda jest bardzo prosta i krótka i wymaga tylko 3 kroków, aby uzyskać listę rozwijaną zależności. Działa jednak pomyślnie tylko do momentu, gdy nie wprowadzisz żadnych zmian w swoim zasięgu. Po zmodyfikowaniu danych należy najpierw zmodyfikować nazwany zakres, aby uzyskać zaktualizowaną walidację danych kaskadowych.
Postępuj zgodnie z instrukcjami podanymi poniżej:-
- Wybierz całą tabelę od A8 do E13
- Następnie przejdź do zakładki „Formuły”, a następnie w kategorii „Nazwy zdefiniowane” kliknij „Utwórz z zaznaczenia”
- Możesz także użyć skrótu klawiaturowego CTRL+ SHIFT + F3
- Pojawi się okno dialogowe Utwórz nazwy z selekcji
- Prosi o potwierdzenie, które wiersze i kolumny mają być użyte do utworzenia nazw dla innych wierszy i kolumn. Potwierdzamy użycie „górnego wiersza” do tworzenia nazw i odznaczamy 2NS opcję, a następnie klikamy OK
Notatka: - Spacje i inne znaki specjalne z wyjątkiem podkreślenia i kropki nie są dozwolone jako nazwy. Domyślnie zostanie zamieniony na podkreślenie. Użyj więc podkreślenia i kropki, aby oddzielić słowa. Ponadto pierwsza litera nie może być liczbą; musi to być litera, podkreślenie lub ukośnik odwrotny.
- Teraz, aby potwierdzić, że każdy zakres ma nazwę, przechodzimy do „Menedżera nazw” (naciśnij CTRL + F3)
- Tam możemy zobaczyć wszystkie 5 nazwanych zakresów dostępnych
- A także widzimy, że każda nazwa zakresu ma podkreślenie zamiast pustego w środku ciągu
Teraz utworzymy listę rozwijaną:-
- Wybierz komórkę J10 i naciśnij ALT++D+L, aby otworzyć okno dialogowe Sprawdzanie danych
- Wybierz Lista > następnie wprowadź zakres A8:E8 w zakładce Źródło
- Kliknij OK
- Teraz utworzymy zależną listę w komórce K10
- Otwórz okno dialogowe Sprawdzanie poprawności danych, naciskając klawisz ALT+D+L
- Wybierz Listę, w źródle wprowadź tę funkcję :- =INDIRECT(SUBSTITUTE($J$10,"","_"))
W walidacji danych, aby utworzyć listę zależną, użyliśmy funkcji ADR.POŚR, aby zwrócić wartość na podstawie pierwotnej listy walidacji danych. Aby zastąpić podkreślenie spacją, użyjemy funkcji SUBSTITUTE wewnątrz funkcji INDIRECT.
- Kliknij OK
Gdy wybierzemy dowolny kod produktu w komórce J10, lista produktów wybranego kodu produktu pojawi się w komórce K10. Na przykład: - Wybraliśmy ETV 501, teraz w komórce K10 widać listę zależnych produktów
Notatka: - Za każdym razem, gdy dodasz nazwę produktu i kod produktu, które nie pojawią się na liście.
Na przykład: - Dodaliśmy Produkt 26 pod kodem produktu ETV 505, ale po wybraniu produktu ETV 505 dodany produkt nie pojawia się na liście rozwijanej.
W ten sposób możesz utworzyć zależną listę rozwijaną za pomocą prostej techniki w zaledwie 3 prostych krokach.
2NS Przykład:-
W tym przykładzie zobaczymy, jak uzyskać zależną listę rozwijaną, gdy masz dane, jak pokazano w tej tabeli pionowej.
Użyjemy dwóch różnych metod tworzenia zależnej listy rozwijanej. Obie są prawie podobnymi technikami. Jednak jeden nie ma nazwanego zakresu, a drugi będzie miał nazwany zakres.
1NS Metoda:-
Aby zrobić to samo, użyjemy razem funkcji PRZESUNIĘCIE, DOPASUJ i LICZ.JEŻELI.
Ponieważ wiemy, że funkcja OFFSET służy do tworzenia zakresu dynamicznego, dlatego do utworzenia listy „Weryfikacja danych dynamicznych” używamy funkcji OFFSET, aby zwrócić zakres dynamiczny.
PODAJ.POZYCJĘ służy do zwracania względnej pozycji elementu na liście w programie Excel. I tutaj pomoże nam dopasować kategorię wybraną na podstawowej liście rozwijanej w naszym zakresie na arkuszu i zwróci liczbę.
I LICZ.JEŻELI służy do uzyskania liczby komórek spełniających kryteria. I tutaj użyjemy tego do zliczenia liczby wierszy do wyświetlenia za pomocą funkcji LICZ.JEŻELI.
Postępuj zgodnie z instrukcjami podanymi poniżej:-
- Wybierz komórkę J21, w której utworzymy naszą podstawową listę walidacji danych
- Naciśnij klawisz ALT+D+L, aby otworzyć okno dialogowe Sprawdzanie danych
- Wybierz listę z dozwolonej kategorii
- Kliknij zakładkę Źródło i wybierz zakres od B20:B24
- I kliknij OK
- Przejdź do komórki K21 i ponownie otwórz okno dialogowe sprawdzania poprawności danych
- Następnie wybieramy List i w źródle wpisujemy poniższą funkcję:
- = PRZESUNIĘCIE ($ E $ 19, DOPASUJ ($ J $ 21, $ D $ 20: $ D $ 32, 0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Kliknij OK
- W komórce K21 możemy zobaczyć wszystkie odpowiadające wartości wybranego kodu produktu:-
W ten sposób można uzyskać listę zależną, biorąc odwołania do komórek w funkcji.
2NS metoda:-
W następnej metodzie użyjemy nazwanego zakresu w tej samej funkcji, aby uzyskać kaskadową walidację danych. Najpierw musimy stworzyć dynamiczną listę dla kodu produktu. W przypadku dodania nowego produktu do danych należy zaktualizować listę rozwijaną, aby wyświetlała to samo.
Aby zrobić to samo, wykonaj czynności podane poniżej:-
- Wybierz B19, a następnie naciśnij CTRL + F3, aby otworzyć okno „Menedżer nazw”
- Teraz klikamy „Nowy” i pojawia się okno dialogowe „Definiuj nazwę”
- Widzimy, że nazwa już pojawia się w polu nazwy - to dlatego, że wybraliśmy B9 przed otwarciem okna "Menedżer nazw". A ponieważ B19 ma w sobie tekst, jeśli chcemy, możemy go zmienić na inną nazwę.
- Wpisz poniżej formułę wzmianki:-
=OFFSET('Zależna lista opuszczanych'!$B20,0,0,COUNTA('Zależna lista opuszczanych'!$B$20:$B$32))
- Kliknij OK
Ponieważ stworzyliśmy dynamiczną listę dla unikalnych produktów, teraz utworzymy dynamiczny zakres dla zakresu kodu produktu, który znajduje się w kolumnie D.
Postępuj zgodnie z tymi samymi krokami, które zastosowaliśmy dla wyjątkowego produktu:-
- Wybierz komórkę D19, otwórz okno dialogowe Definiuj nazwę
- Przekonasz się, że nazwa już tam jest
- W odsyłaczach wprowadź poniższą formułę:-
=OFFSET('Zależna lista rozwijana'!$D$20,0,0,COUNTA('Zależna rozwijana lista'!$D$20:$D$35))
- Kliknij OK
- Teraz oba zakresy dynamiki są gotowe. Idziemy więc do J22 i wciskamy „ALT + D + L” i wybieramy „Lista”
- W źródle będziemy mieć nazwany zakres, który zdefiniowaliśmy jako „Unikalny kod produktu”, więc wciskamy F3, aby zobaczyć wszystkie dostępne nazwane zakresy
- Widzimy nazwany zakres „Unique Product Code”, więc klikamy na niego, a następnie klikamy OK i wciskamy enter
- W momencie, gdy wciśniemy Enter, w komórce J22 pojawia się strzałka w dół, która zawiera listę unikalnych kodów produktów
- Wybierz komórkę K22 i otwórz okno dialogowe „Weryfikacja danych”
- Użyjemy tej samej funkcji, której używaliśmy w poprzedniej metodzie, ale z nazwanym zakresem
- Wybierz listę, a następnie w źródle wprowadź poniższą formułę:-
= PRZESUNIĘCIE($E$19;MATCH($J$22;Kod_produktu;0);0;LICZ.JEŻELI(Kod_produktu;J22))
- Kliknij OK
- Teraz mamy podstawową listę rozwijaną oraz podrzędną listę produktów
- Wybierz produkt „ETV-101” z J22, a w K22 możemy zobaczyć tylko nazwy, które należą do tego produktu „ETV-101”. A kiedy zmieniamy dowolny produkt („ETV-103) w J22, K22 wyświetla odpowiednie wartości dla tego kodu
Teraz zobaczymy, co się stanie, gdy dodamy nowy kod produktu do listy? Czy te listy rozwijane zostaną zaktualizowane?
Dodajmy nowy produkt do listy; Postępuj zgodnie z instrukcjami podanymi poniżej:-
- Dodaj kod produktu na liście Unique_Prod_Code
- Dodaj również kod_produktu i nazwę_produktu w danych:-
- Teraz sprawdź listę rozwijaną - pojawia się kod produktu i nazwa
3r & D Przykład:-
Mamy dynamiczne nagłówki bezpośrednio z tabeli i dodamy nowe produkty do asortymentu. Tabela jest w tym samym formacie, którego użyliśmy do 1NS metoda.
4NS metoda:-
Postępuj zgodnie z instrukcjami podanymi poniżej:-
- Wybierz nagłówek A40:E40
- Najpierw utwórz zakres dynamiczny dla nagłówków, otwórz okno dialogowe „Definiuj nazwę”
- W miejscu nazwy wpisz „Nagłówek”, a następnie w „odnosi się do” wpisz poniższą formułę:-
- Wprowadź poniższą funkcję:-
- =OFFSET('Zależna lista rozwijana'!$A$40,,,,COUNTA('Zależna lista rozwijana'!$40:$40))
- Kliknij OK
- Dynamiczny zakres „kursu” jest już gotowy
A teraz utworzymy nazwany zakres dla każdego nagłówka, wykonaj następujące czynności:-
- Wybierz tabelę od A40 do E50
- Skrót klawiaturowy CTRL + SHIFT + F3
- Odznaczamy 2NS opcja
- I zanim klikniemy OK, upewnij się, że 1NS wybrana jest opcja „Górny rząd”
- Teraz jesteśmy gotowi z obydwoma zakresami
Teraz przygotujemy nadrzędną listę rozwijaną
- Wybierz komórkę J42
- Otwórz okno dialogowe Sprawdzanie danych
- Następnie po wybraniu „Lista” naciskamy F3 w źródle, aby uzyskać nazwany zakres nagłówków. Klikamy „Nagłówek”, a następnie klikamy OK i wciskamy enter. Mamy teraz listę rodziców w J42
- Aby utworzyć listę szczegółów pozycji, wybierz komórkęK42
- Otwórz okno dialogowe Sprawdzanie poprawności danych, naciskając klawisz ALT+D+L
- Wybierz Lista, a następnie wprowadź poniższą funkcję w zakładce Źródło: –
- =PRZESUNIĘCIE(POŚREDNIA(PODSTAWA($J$42;"","_"));,.LICZBA (POŚREDNIA(PODSTAWA($J$42;"","_"))))
- Kliknij OK
Teraz wybierz jakiś element w J42, powiedzmy, że wybieramy "Pozycja 01" i spójrz na rozwijaną listę K42. I podobnie jak poprzednie 3 metody, tutaj również mamy listę zależną.
Więc co nowego? W pierwszym przykładzie nie można było dodać żadnego produktu do listy, ale tutaj można dodać dowolny nowy produkt. Powiedzmy, że dodaliśmy nowy produkt do tego przedmiotu. Idziemy do A45, wpisujemy „ETV-501 Prod 05”, a potem wracamy do K42 i proszę. Widać, że nowy produkt został dodany.
- Teraz dodaj kilka produktów pod nową pozycją
Po wybraniu „Pozycji 06” przechodzimy do K42 i klikamy na rozwijaną listę. Co zaskakujące, nic się nie dzieje, gdy klikamy strzałkę w dół. To dlatego, że stworzyliśmy wszystko dynamiczne i zapomnieliśmy stworzyć zakres dynamiczny dla tabeli, dlatego produkty nie wyświetlają się na liście podrzędnej.
Aby to zrobić, musimy użyć różnych technik. Są na to dwie metody. Możesz utworzyć tabelę lub po prostu użyć tylko funkcji PRZESUNIĘCIE. A w następnej metodzie użyjemy funkcji OFFSET i zobaczymy sztuczkę, aby również rozszerzyć zakres tabeli.
- Więc najpierw przechodzimy do J43 i naciskamy „ALT + D + L”
- Wybieramy „List”, a następnie w źródle wciskamy F3 i wybieramy „Nagłówek” i klikamy OK, a następnie wciskamy enter
- Teraz przechodzimy do K43 i po wybraniu „Lista”, przechodzimy do „Źródła” i wchodzimy w poniższą funkcję
= PRZESUNIĘCIE ($ A 40, 1, DOPASOWANIE ($ J 43 $, 40 $: 40, 0 $)-1, COUNTA (PRZESUNIĘCIE ($ A $ 40, 1, DOPASOWANIE ($ J $ 43, $ 40: 40, 0 $) -1,1000) ,1)))
- Kliknij OK
Teraz wracamy i wybieramy „Pozycja 06 w komórce J43 i wracamy do K43 i klikamy strzałkę w dół. Ale tym razem lista pokazuje produkty, które dodaliśmy do nowej pozycji. I wybieramy pierwszy produkt „ETV-506 Prod 01”.
W ten sposób można utworzyć zależną listę rozwijaną przy użyciu różnych metod w dowolnym formacie danych.
Wideo: Jak utworzyć zależną (kaskadową) listę rozwijaną w programie Excel przy użyciu 5 różnych technik w programie Microsoft Excel
Kliknij łącze wideo, aby szybko zapoznać się z jego użyciem. Subskrybuj nasz nowy kanał i ucz się z nami dalej!
Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku.
Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail