Minuty czytania: 4

Dynamiczna lista rozwijana, czyli jak przyspieszyć pracę

Listy rozwijane możesz zrobić na kilka sposobów. Dzięki lekturze tego artykułu dowiesz się, jak dzięki nim zautomatyzować pracę. Dynamiczna lista rozwijana pozwoli Ci ją przyspieszyć.

Na koniec dowiesz się jak tworzyć hierarchiczne listy rozwijane. Hierarchiczne, to znaczy takie, gdzie lista wyboru z jednej listy zależy od wyborów z innej. Brzmi ciekawie? Zapraszam do lektury!

Wszystkie opisywane w artykule funkcjonalności znajdziesz w filmie przygotowanym przez Jacka.

Prosta lista rozwijana

Załóżmy, że masz taką sytuację – dysponujesz bazą sprzedaży i chciałbyś zliczać łączną wartość sprzedaży netto dla poszczególnych grup towarów. Przygotujemy tabelę, w której będzie możliwe wybranie poszczególnych towarów z listy oraz automatyczne podsumowanie sprzedaży, wg tego wyboru.

W kolejnych krokach pokażę Ci jak się za to zabrać.

Przygotuj wszystkie unikatowe elementy listy, które mają pokazywać się na liście rozwijanej. Następnie ustaw się na komórce, gdzie ma się znaleźć lista rozwijana i wybierz POPRAWNOŚĆ DANYCH z menu górnego DANE w SEKCJI NARZĘDZIA DANYCH.

W menu POPRAWNOŚĆ DANYCH, wybierz zakładkę KRYTERIA POPRAWNOŚCI. O co tutaj chodzi? Domyślnie do każdej komórki Excela możesz wpisać dowolną wartość. Natomiast my chcemy, aby do komórki z listą wyboru, mogły być wpisane tylko wartości zdefiniowane wcześniej przez nas.

Dlatego, wybieramy polecenie LISTA i klikając na ikonę ŹRÓDŁO wskazujemy zakres komórek, gdzie umieściliśmy nasze unikalne kategorie towarów. Zatwierdzamy i nasza lista rozwijana jest gotowa.

Prosta lista rozwijana

Dynamiczna lista rozwijana

Prosta lista rozwijana to nie koniec możliwości. Pewnie zauważyłeś, że w momencie dodania kolejnej wartości do listy unikalnych towarów, lista rozwijana nie zaktualizuje się o te dodatkowe wartości.

Stworzymy zatem listy dynamiczne. Co to oznacza? Gdy dodamy kolejną grupę towarów – to nie wykonując żadnych dodatkowych czynności – nowy wpis automatycznie pokaże się na naszej liście rozwijanej. Jak się do tego zabrać?

Pierwszą rzeczą jaką musisz zrobić to przygotować listę. Kolejnym elementem całej układanki jest przerobienie tej listy na tabelę excelową. Jednym z najprostszych sposobów jest użycie skrótu CTRL + T.

Dzięki temu, że ten obszar jest teraz tabelą, dodanie kolejnych elementów będzie w niej automatycznie uwzględniane. Jeśli podłączysz teraz sprawdzanie poprawności do tabeli, automatycznie dodane rekordy będą pojawiać się na liście.

Dynamiczna lista rozwijana

Początek drogi jest analogiczny jak poprzednio. To znaczy:

  1. Ustaw się na komórce, gdzie ma się znaleźć lista rozwijana.
  2. Wybierz POPRAWNOŚĆ DANYCH z menu górnego DANE w SEKCJI NARZĘDZIA DANYCH.
  3. W menu POPRAWNOŚĆ DANYCH, wybierz zakładkę KRYTERIA POPRAWNOŚCI.
  4. Wybierz polecenie LISTA i klikając na ikonę ŹRÓDŁO wskaż tabelę, którą utworzyłeś ale tylko elementy listy bez nagłówka. Excel rozpozna, że chodzi tylko o same wpisy, a nie o nagłówek.
  5. Zatwierdź klikając OK.

Teraz, gdy dodasz kolejny wpis na liście, Twoja lista rozwijana automatycznie uwzględni tę wartość!

Hierarchiczne listy rozwijane

Naprawdę ciekawym rozwiązaniem są hierarchiczne listy rozwijane. Co to takiego? Załóżmy że, mamy główne grupy towaru, a w każdej grupie towaru znajdują się poszczególne towary. Na przykład w grupie “AGD”, mamy: lodówka, mikrofalówka, okap. W grupie “Meble” mamy: biurko, fotel biurowy, itd.

Cały trick polega na tym, aby stworzyć listy rozwijane w taki sposób, że jeśli wybierzesz główną grupę towaru (na przykład AGD), to pokażą się tylko towary przypisane do tej grupy.

Wbrew pozorom, takie rozwiązanie nie jest trudne. Tak jak poprzednio, musisz przygotować dane, tyle że tym razem nagłówki tabel będą oznaczały grupę produktów, a poniżej będą poszczególne towary, czyli elementy listy rozwijanej.

Kolejny krok to nazwanie obszarów dla poszczególnych grup towarów. Zaznacz wszystkie zakresy łącznie z nagłówkami. Musisz to zrobić używając przycisku CTRL na klawiaturze oraz lewego przycisku myszy. Następnie przejdź na wstążkę FORMUŁY i wybierz z sekcji NAZWY ZDEFINIOWANE polecenie UTWÓRZ Z ZAZNACZENIA. Sprawdź czy jest wybrana opcja GÓRNY WIERSZ i zatwierdź wybór.

Utworzyłeś 3 nazwy grup towarów (AGD, meble i oświetlenie) Sprawdzisz to w MENEDŻERZE NAZW na wstążce FORMUŁY.

Przejdź teraz do komórki, gdzie ma powstać lista rozwijana z możliwością wyboru grupy towarów. W tym miejscu tworzysz listę w standardowy, opisany wcześniej sposób. Oczywiście jako zakres danych dla tej listy wskażesz tylko nagłówki z nazwami grup towarów.

Następnie przejdź do komórki, gdzie ma pojawić się lista rozwijana z towarami. Zawartość tej listy będzie zależna od wybranej w poprzedniej komórce grupy towarów.

Podobnie jak wcześniej, przejdź do polecenia POPRAWNOŚĆ DANYCH i wybierz LISTĘ. W źródło danych nie możesz wpisać, ani wskazać zakresu z: “AGD, meble, oświetlenie” ponieważ Excel potraktowałby to jako elementy listy.

Zamiast tego wykorzystaj dodatkową funkcję ADR.POŚR, która niejako “przetłumaczy” wcześniej nazwany zakres komórek (np. AGD) na konkretny zakres komórek, w której znajdują się towary z tej właśnie grupy. Jako argument tej funkcji musisz oczywiście wskazać komórkę, w której umieściłeś listę rozwijaną z grupami towarów. Zatwierdzasz i gotowe! Wybierając z listy rozwijanej grupa towarów “Oświetlenie”, zobaczysz tylko produkty, które należą do tej kategorii. 

Hierarchiczna lista rozwijana



Podsumowanie

Ten artykuł pokazał Ci jak wiele ciekawych funkcjonalności kryje w sobie prosta lista rozwijana. Jak w większości przypadków, połączenie jej z innymi funkcjonalnościami Excela daje najlepsze efekty.

Listy są często wykorzystywane, w tworzeniu interaktywnych raportów i dashboardów. Mam nadzieję, że uda Ci się wykorzystać opisywane tu możliwości w Twoich zestawieniach i raportach. Opisz proszę w komentarzu, do czego Ty wykorzystujesz listy rozwijane..


4 Comments

Tomek · 9 czerwca 2020 at 05:29

Bardzo fajny trick z listą hierarchiczną! Dzięki!

    Łukasz Pietrzak · 9 czerwca 2020 at 05:53

    Fajnie, że Ci się podoba 🙂

Joanna · 6 września 2021 at 17:23

Bardzo przydatny materiał, ale mam pytanie. Jeśli potrzebuję wykorzystać listę hierarchiczną np. w bazie danych, to jak przeciągnąć to do kolejnych wierszy? Niestety odwołanie jest na stałe do jednej komórki. Z góry dziękuję 🙂

    Łukasz Pietrzak · 8 września 2021 at 17:40

    Dzięki. Rozumiem, że chcesz stworzyć odwołanie dynamiczne, tzn. aby zakres do listy rozwijanej poszerzał się wraz z dodawaniem kolejnych wpisów? Jeżeli tak, to można wykorzystać funkcję PRZESUNIĘCIE oraz menedżera nazw, aby taki mechanizm zrobić.

Dodaj komentarz

Avatar placeholder

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