• Zapisz się na newsletter i odbierz DARMOWY EBOOK: 10 najprzydatniejszych porad excelowych

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 najnowszych wpisach (i tylko powiadomienia: zero spamu).
Dodatkowo otrzymasz DARMOWY EBOOK pt.:

10 najprzydatniejszych porad excelowych


Dodaj do zakładek Link.

Dodaj komentarz

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