• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Dwie zależne listy rozwijane i formuła tablicowa

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

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

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

Sprawdzanie poprawności danych

Poniżej wersja wpisu w formie wideo:

 

A tutaj znajdziesz wpis o tworzeniu jednej zależnej listy rozwijanej:

  • Zależna lista rozwijana w komórce

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o o nowościach na blogu.

Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Dodaj do zakładek Link.

11 odpowiedzi na „Dwie zależne listy rozwijane i formuła tablicowa

  1. Tomasz Banaś mówi:

    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ń?

  2. Malina mówi:

    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 😉

    • Marek mówi:

      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.

      • Malina mówi:

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

    • ES mówi:

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

      • Malina mówi:

        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… ;(

  3. Kamil mówi:

    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

    • Malina mówi:

      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…

  4. danek mówi:

    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

    • Malina mówi:

      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…

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *