Czyli jak w Excelu zrobić 2 listy zależne: kategoria, podkategoria i podpodkategoria
Jeśli słowa podpodkategoria w ogóle istnieje… Na potrzeby tego wpisu uznajmy, że tak 🙂
W każdym razie od razu na wstępie napiszę, że ten wpis jest kontynuacją wpisu Zależna lista rozwijana w komórce, w którym dokładnie opisywałam logikę i sposób na stworzenie jednej takiej listy. Polecam się z nim zapoznać, ponieważ tutaj szczegółowo opisuję tylko to, jak zrobić tę drugą zależną listę rozwijaną 😉 A to chcemy uzyskać:
Czyli mamy:
- rodzaj samochodu: Osobowy, Dostawczy i Terenowy (Kategoria)
- producenta: Fiat, Volkswagen i Suzuki (Podkategoria) i
- model: … trochę tego jest 😉 (Podpodkategoria)
Przy czym dane mamy następujące:
Dane
Ta lista musi być posortowana w kolejności:
- Rodzaj
- Producent
- Model
i może być dowolnie długa. Co jest istotne: warto dorzucić do niej jeszcze 2 mniejsze listy, potrzebne do Rodzaju i Producenta, czyli do kategorii (pierwsza lista) i podkategorii (druga lista). Te listy wyglądają tak:
Pozostałe listy
Chodzi o to, żeby te listy nie miały dubli rodzaju i producenta, występujące na liście z modelami. Można je stworzyć choćby za pomocą narzędzia usuń duplikaty (pokazuję to np. w tym filmie ok. 2 minuty). Jak już to mamy, to…
Pierwsza i druga lista: Rodzaj i Producent
Listy rozwijane definiujemy w menu Dane/ Poprawność danych i jako Dozwolone wybieramy Lista.
Dla Rodzaju, jako źródło listy po prostu wskazujemy zakres B7:B9.
Dla Producenta wpiszemy już formułę, którą szczegółowo opisałam tutaj. Wygląda ona tak:
=PRZESUNIĘCIE($C$12;PODAJ.POZYCJĘ($B$4;$B$13:$B$20;0);0;LICZ.JEŻELI($B$13:$B$20;$B$4);1)
Model – temat tego wpisu zrobimy na takiej samej zasadzie…
Trzecia lista: Model
… jednak ponieważ model jest uzależniony zarówno od Rodzaju jak i Producenta – użyjemy do tego formuły tablicowej. Załóżmy, że chcemy na niej wyświetlić modele osobowych Fiatów. Na pierwszej liście wybraliśmy Osobowy, na drugiej: Fiat.
Przesuwać będziemy komórkę H4 o tyle wierszy, jaka jest pozycja pierwszego osobowego Fiata. Czyli zarówno w kolumnie z rodzajem musimy mieć osobowy, jak i w kolumnie producenta musimy mieć Fiata. Gdybyśmy używali kolumny pośredniczącej (co byłoby świetnym rozwiązanie, ale chciałam Wam pokazać coś fajniejszego ;)), szukalibyśmy połączenia tych danych: OsobowyFiat. Takiej kolumny jednak nie mamy, ale możemy ją sobie stworzyć „w locie”, czyli właśnie za pomocą formuły tablicowej. Pisząc tę formułę, możesz sobie wyobrazić, że taka kolumna pośrednicząca faktycznie istnieje i zobaczycie, że będzie łatwiej 😉
Do ustalania pozycji OsobowegoFiata użyjemy oczywiście funkcji PODAJ.POZYCJĘ. Zobacz:
PODAJ.POZYCJĘ(B4&C4;F5:F39&G5:G39;0)
Powyższe oznacza, że chcemy poznać pozycję OsobowegoFiata (stąd łączenie B4&C4). Gdzie? W tej naszej wyobrażonej kolumnie pomocniczej, czyli: F5:F39&G5:G39. I tutaj jest największa trudność całej formuły.
Reszta jest już łatwiejsza, a najwięcej uwagi wymaga funkcja LICZ.WARUNKI, która sprawdza, ile jest OsobowychFiatów. Konkretnie sprawdza ile razy na liście występują takie wpisy, które w kolumnie F5:F39 mają wpis Osobowy, a w kolumnie G5:G39 – Fiat. Funkcja wygląda tak:
LICZ.WARUNKI(F5:F39;B4;G5:G39;C4)
A cała formuła dla listy rozwijanej to:
=PRZESUNIĘCIE(H4;PODAJ.POZYCJĘ(B4&C4;F5:F39&G5:G39;0);0;LICZ.WARUNKI(F5:F39;B4;G5:G39;C4);1)
Jeżeli planujesz używać tej formuły w kilku komórkach – pamiętaj o blokowaniu komórek! No i jeszcze MEGAISTOTNE jest to, żeby cały wpis zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter!!! Nie naciskaj przypadkiem OK. Czyli, jak już wejdziesz do Dane/ Poprawność danych i wybierzesz Lista jako dozwolone, to wklej tę formułę i zatwierdź ją Ctrl + Shift + Enter. Cała magia 🙂
Sprawdzanie poprawności danych
Poniżej wersja wpisu w formie wideo:
MalinowyExcel_Dwie listy zależne formuła tablicowa dw.xlsx
A tutaj znajdziesz wpis o tworzeniu jednej zależnej listy rozwijanej:
- Zależna lista rozwijana w komórce
W załączonym pliku, a także w moim, który powstał na jego podstawie, lista wyboru podkategorii nie działa po otwarciu pliku. Zaczyna działać po otwarciu okna poprawność danych i potwierdzeniu CTRL+SHIFT+ENTER. Po zamknięciu i ponownym otwarciu znów wymaga takiego zabiegu. Czy to kwestia ustawień?
Hej, u mnie też tak to działa! Będę szukała przyczyny i napiszę! Dzięki za uwagę 🙂
Poszperałam w necie i okazuje się, że to kwestia bug’a w Excelu.Ja mam 2013 i u mnie nie działa (nie działa też w 2007, a w 2010 już tak). Bug został już zgłoszony do Microsoft (w 2014 roku!!!!).
Obawiam się, że na to nic nie poradzimy ;( Pomyślę nad innym rozwiązaniem tego problemu 😉
Witam, Przede wszystkim jestem pod wrażeniem Twoich umiejętności, Gratuluję. Skorzystałem z rozwiązania dla list rozwijanych i wszystko działało do momentu ponownego otwarcia pliku. Pracowałem na pliku z wersji 2003 a korzystam z wersji 2016 Excel. Rozwiązanie jakie na razie znalazłem to zapisanie pliku w wersji .xlsx 2016 a nie jak to robiłem w starej wersji. I działają obie utworzone przeze mnie listy. Jeszcze nie wiem jak będzie wyglądało otwarcie pliku w innej wersji Excell ale jest postęp.
Witam,
dziękuję za miłe słowa 🙂
Tak, faktycznie ten problem występuje. Tak, jak pisałam wyżej, szukałam w internecie i jedyne, co znalazłam to: „it’s clearly a BUG”.
Hej, bardzo przydatny poradnik. Mam pytanie czy udało się rozwiązać problem tego nieszczęsnego buga, który pojawia się w niektórych wersjach excela? Dziękuję za odpowiedź.
Hej, bardzo się cieszę 🙂
Odnośnie buga – myślę, że najlepszą metodą będzie tworzenie kolumn pomocniczych, które sklejają ciągi. I nawigowanie po nich metodą, pokazaną tutaj. Obawiam się, że na tę chwilę jest to najbardziej pewne rozwiązanie. Zdaję sobie sprawę, że nieeleganckie… ;(
Witam,
Świetny poradnik 🙂 Wszystko działa tak jak należy ale mam jedno pytanie. Jak już posortuje auta np. na osobowe i vw to w kolejnej liście mam do wyboru konkretne modele i chciałbym zapisać je gdzieś. Chodzi o to żeby te modele nie były już jako lista rozwijana tylko naprzyklad tabelka albo lista w komórce. Zna Pani jakiś sposób na stworzenie takiej tabelki na podstawie listy rozwijanej?
Pozdrawiam
Kamil
Hmmm… tak bez VBA to nie przychodzi mi nic do głowy. Natomiast myślę, że można napisać formułę tablicową gdzieś obok, która by robiła to samo, co lista rozwijana. To jedyne rozwiązanie, które przychodzi mi do głowy…
Wielki szacunek za wiedzę.
Bardzo dobry poradnik ale ten bug wszystko psuje. Jak go obejść żęby przy 3 poziomie listy zamknięcie excela nie powodowało konieczności ponownego wchodzenia w Poprawność danych
Danek, dziękuję. Zgadzam się, że ten bug wszystko psuje ;(. Moim zdaniem jedynym sposobem są kolumny pomocnicze łączące poszczególne kolumny i nawigowanie po nich…
Malina podaj proszę przykład takich kolumn pomocniczych, co masz na myśli.
O rety, zgubiłam się. W którym momencie? 🙂
Hej,
Pewnie trochę późno, ale lepiej późno niż wcale 🙂
Problem mija, jak nadamy grupie, podgrupie i podpodgrupie nazwy w menadżerze nazw i później się do nich odniesiemy w listach.
A strona świetna!
Bardzo się cieszę i dziękuję za propozycję rozwiązania i miłe słowa:)
Czy mógłbym prosić o pomoc? Chciałbym zrobić listę rozwijaną, której wyniki będą zależne od wyborów z komórek, które również będą miały listy rozwijane ale nie będzie tam użyta formuła ctrl+shift+enter. Chodzi o to aby wyeliminować ten błąd, o którym była mowa powyżej tzn. żeby nie trzeba było po każdym odpaleniu pliku aktywować tej formuły. Z góry dziękuje!
Wystarczy w formule listy pola MODEL wstawić wszędzie $.
Dzień dobry,
Przede wszystkim super pomocny blog ! Gratuluję umiejętności.
Udało mi się utworzyć dwie zależne listy rozwijane, jednak problem, który się u mnie pojawia polega na tym, że jak wybiorę pozycję z kategorii, następnie podkategorii i na końcu podpodkategorii , to jak mając wybrane te trzy pozycje zmienię np. kategorię, to nie kasuje to automatycznie podkategorii i podpodkategorii tylko zostają one nie zmienione. Jak można sobie z tym poradzić ?
Pozdrawiam