fbpx

Zależna lista rozwijana w Google Sheets

03.02.2020 | Google Sheets, Sprawdzanie poprawności (walidacja)

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:

Zależna lista rozwijana w Google Sheets

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:

Zależna lista rozwijana Google Sheets - 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.

Zależna lista rozwijana Google Sheets Menu Data 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:

Zależna lista rozwijana Google Sheets Lista rozwijana kategorii

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ć? :)):

Zależna lista rozwijana Google Sheets Tworzenie zależnej listy rozwijanej podkategorii

Tworzenie zależnej listy rozwijanej podkategorii

Po zapisaniu (Save) jest gotowe!

Efekt mamy taki:

Zależna lista rozwijana w Google Sheets

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:

 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

13 komentarzy

  1. Też nie jestem specem od Arkuszy Google i zastanawiałam się czy w ogóle możliwe jest zrobienie takich list – dzięki za patent, przydaje się na co dzień na udostępnianych materiałach! 🙂

    Reply
    • Super, bardzo się cieszę 🙂

      Reply
  2. A co jeśli takie dane trzeba uzupełnić w wielu liniach pliku? Wersję roboczą należy rozszerzyć na tak dużą jak sam plik?
    Mamy plik na 100 wierszy i należy uzupełnić kolumnę B i C. W kolumnie B mamy określoną listę rozwijalną, natomiast lista rozwijalna w kolumnie C zależy od wyboru dokonanego w kolumnie B.
    Rozwiązanie przedstawione w artykule działa, ale przy jednym wyborze zależnym, a nie przy dużym pliku, gdzie lista zależna ma działać w wielu liniach.
    Proszę o pomoc w znalezieniu rozwiązania 🙂

    Reply
    • Hej, robisz tak samo, tylko trzeba zablokować zakresy odwołujące się do arkusza ($). A walidację nadajesz na te 100 wierszy, gdzie powinny być listy. 🙂

      Reply
      • Mogę prosić o pomoc. Niestety dla wielu wierszy jakoś mi to nie wychodzi. Dzięki

        Reply
        • Na wiele komórek faktycznie to nie zadziała. W GS chyba jest funkcja transponująca – trzeba byłoby wrzucić te formuły pomocnicze do funkcji transponującej, i takie pomocnicze dać dla każdego wiersza…

          Reply
  3. Bardzo ciekawy i pomocny artykuł. Niestety też nie znalazłem sposobu jak go rozwinąć dla wielu wierszy. Pozdrawiam

    Reply
    • Bardzo się cieszę, że się przydał :). Faktycznie – przydałoby się takie rozwiązanie dla wielu wierszy…

      Reply
  4. Czy rozwiązanie dla kilku wierszy jest już dostępne?

    Reply
    • Super!

      Reply

Leave a Reply to Piotr Anuluj pisanie odpowiedzi

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest