Czyli prosty sposób na zależną listę rozwijaną

Do napisania tego artykułu zainspirował mnie komentarz Armena pod wpisem o zależnej liście rozwijanej w Excelu, który napisałam kilka lat temu. Armen zapytał jak coś takiego zrobić w arkuszach Google. Specem od nich nie jestem, ale pokombinować lubię. Poza tym – są bardzo podobne do Excela, więc, czemu nie? No i wymyśliłam bardzo proste rozwiązanie, używające ciekawych funkcji… Efekt ma być taki:

Do dzieła!

Zależna lista rozwijana – formatka w arkuszach Google

W odróżnieniu od metody, którą pokazywałam w tym wpisie, w arkuszu Google posłużymy się tylko jedną tabelą. Będzie ona zawierała listę kategorii i podkategorii. I co ważne – nie trzeba będzie jej sortować! Oto formatka:

Formatka

Dane obok tabeli, w zakresie D4:E5 to miejsca na stworzenie list rozwijanych, o czym za sekundę.

Tworzenie listy rozwijanej kategorii

Na wstępie tylko zaznaczę, że w arkuszach Google nie ma wstążki, jest zwykłe menu. Takie, jak kiedyś było w Excelu. Jak pamiętasz tamte czasy, to będzie Ci łatwiej, ponieważ narzędzia znajdują się w bardzo podobnych miejscach. Np. sprawdzanie poprawności (inaczej: walidacja) będzie w menu Data.

Ok. Aby utworzyć listę rozwijaną kategorii w komórce E4, należy ją zaznaczyć.

Następnie wejść do menu Data i wybrać Data validation.

Menu Data/Data validation

W okienku, które następnie się pojawi, wybieramy typ walidacji (List from a range) oraz zaznaczamy zakres, który ma być źródłem dla listy rozwijanej. Zakresem w naszym wypadku będzie kolumna z kategoriami w danych źródłowych (A5:A17).  Nie szkodzi, że występują w niej duplikaty – arkusze Google są tak genialne, że same usuną te duplikaty na liście! Ah, jakbym chciała, żeby to samo działo się w Excelu… ;).

Dodatkowo, ważne jest, aby zaznaczyć checkbox Show dropdown list in a cell:

Po zapisaniu (Save) otrzymamy następujący efekt:

Lista rozwijana kategorii

Prawda, że cudowne? 🙂

Zależna lista rozwijana podkategorii w arkuszach Google

To będzie niestety bardziej skomplikowane. Ale tylko troszkę bardziej :). Będzie bowiem wymagało utworzenia kolumny pomocniczej z formułą, której wynik będzie źródłem listy rozwijanej.

Źródło zależnej listy rozwijanej (podkategorii)

Formułę umieścimy w komórce E15, zaś jej wynik rozleje się na tyle komórek, ile będzie potrzeba (tyle, ile będzie podkategorii danej kategorii). Wynik ten będzie naszym źródłem do listy rozwijanej.

A formuła jest taka:

=FILTER(B5:B17,A5:A17=H3)

Funkcja FILTER filtruje zakres B5:B17 (czyli nasze podkategorie) tak, że gdy w kolumnie A5:A17 (kategorie) znajduje się kategoria wybrana przez użytkownika (w komórce H3) – funkcja wyświetla odpowiadające jej podkategorie. Dzięki tej funkcji tabela źródłowa nie musi być sortowana według kategorii (tak, jak była w tej sytuacji). To powoduje jednak, że na zależnej liście rozwijanej podkategorie są wyświetlane w kolejności wystąpienia. Jeśli tak jest dla Ciebie OK – możesz pominąć kolejny krok.

 Gdybyś jednak chciała, aby podkategorie wyświetlane były w kolejności alfabetycznej (może tak się bezie łatwiej szukało?), trzeba wrzucić wcześniejszą funkcję w funkcję sortującą. O tak:

=SORT(FILTER(B5:B17,A5:A17=H3))

Ponieważ nie sprecyzowałam sposobu, w jaki mają być sortowane dane (moja jedna jedyna kolumna) – zostaną one posortowane domyślnie, czyli rosnąco (od A do Z dla tekstu).

Tworzenie zależnej listy rozwijanej w arkuszach Google

Tutaj postąpimy analogicznie jak przy kategorii. Czyli:

1. Menu Data/Data validation.

2. W okienku wybieramy List from a range i jako zakres podajemy np. E15:E21. Chodzi o to, by zakres ten był co najmniej tak wysoki, jak największa liczba podkategorii na naszej liście (albo i dłuższy, jeśli spodziewasz się zmian przypisań). Robimy tak, ponieważ ich genialność – listy rozwijane w arkuszach Google – wyświetlają tylko zapełnione komórki, a nie wszystkie, które dostaną w źródle (BTW: brakuje mi w nich tylko definiowania listy za pomocą formuły – wtedy te kolumny pomocnicze nie byłyby potrzebne.  A może ja po prostu nie wiem jak to zrobić? :)):

Tworzenie zależnej listy rozwijanej podkategorii

Po zapisaniu (Save) jest gotowe!

Efekt mamy taki:

Powiązane produkty

• Nazwy w Excelu – wstęp – pokazuję tutaj jak stworzyć nazwy komórek, zakresów i – co jest arcyciekawe – formuł! To bardzo proste i przydatne, zobaczysz 🙂
• Listy rozwijane – pokazuję jak zrobić listę rozwijaną w Excelu zarówno za pomocą sprawdzania poprawności, jak i formant formularza, oraz jak je wykorzystać np. na wykresach.
• Walidacja danych – triki – opowiadam o ciekawych zastosowaniach sprawdzania poprawności, którego lista rozwijana w komórce (użyta w tym wpisie) jest opcją. Ale narzędzie to potrafi dużo, duuuużo więcej!
• Zagnieżdżanie funkcji – podczas tego webinaru pokazuję jak w prosty sposób zagnieżdżać funkcje jedna w drugiej i trzeciej…

Tutaj gotowy arkusz Google, na którym pracowałam (tylko wyświetlanie).

A tutaj wersja wideo wpisu: