1. źródło listy rozwijanej musi być utworzone osobno, przechowywane jako Tabela i jeszcze, jeśli chcemy go przechowywać w osobnym arkuszu to musi mieć nazwę zdefiniowaną;
2. dopisanie danych do głównego źródła naszej listy (czyli pierwotnej tabeli) nie powoduje, że nasze źródło listy rozwijanej samo się zaktualizuje - musimy tę daną dopisać ręcznie.

Na szczęście w Excelu on-line, 365 i 2021 możesz spróbować wykonać tę listę znacznie szybciej i bez dodatkowych nieprzyjemnych niespodzianek za pomocą Funkcji Tablicowych.

Data artykułu: 14.07.2023
Autor: Marzena Andrykowska

Jedną z przydatnych funkcji, która może znacznie ułatwić pracę z danymi, jest tworzenie dynamicznych list rozwijanych.

Dynamiczna lista rozwijana to narzędzie, które umożliwia użytkownikom wybór wartości z wstępnie zdefiniowanego zestawu danych, który jest automatycznie aktualizowany, gdy dodawane są nowe informacje. Pozwala to na tworzenie bardziej interaktywnych arkuszy kalkulacyjnych, gdzie użytkownik może wybrać wartość z listy, zamiast ręcznego wpisywania danych.

W artykule przedstawię krok po kroku, jak stworzyć dynamiczną listę rozwijaną w Excelu, wykorzystując dwie kluczowe funkcje: UNIKATOWE i SORTUJ. Funkcja UNIKATOWE pozwala na uzyskanie niezduplikowanych wartości w kolumnie, a funkcja SORTUJ sortuje te wartości w porządku alfabetycznym.​

1. Dane źródłowe

W arkuszu "dane geograficzne" mam tabelę Excela o nazwie "Dane_geograficzne". Możesz to sprawdzić na karcie "Projekt tabeli" (po jej lewej stronie widnieje nazwa).

O tym jak włączyć tabelę Excela i jakie ma ona zalety opowiadam w innym artykule: Narzędzie Tabela w Excelu - podstawa przechowywania danych.

Moja Tabela zawiera kolumnę z województwami, które się powielają. Potrzebuję wygenerować listę unikatowych województw, które następnie będę chciała umieścić w drugim arkuszu w mojej liście rozwijanej.

2. Lista unikatów - FUNKCJA UNIKATOWE()

Stworzę obok danych źródłowych unikatową listę województw za pomocą funkcji UNIKATOWE, która ma tylko jeden argument -> zakres, z którego ma stworzyć listę unikatów.

Ponieważ moje dane źródłowe przechowuję w formie Tabeli Excela, to w trakcie pisania formuły odniosę się do kolumny "Województwo" poprzez jej zwykłe zaznaczenie (wystarczy najechać na wybraną kolumnę od góry, tak aby kursor zmienił się w czarną strzałeczkę). Jeśli nie masz danych w formie tabeli to zaznacz je w standardowy sposób.

3. Wynik końcowy funkcji UIKATOWE

Funkcja UNIKATOWE zwraca wynik w postaci rozlanej tabeli wynikowej.

4. Błąd #ROZLANIE

5. W przypadku błędu...

Zaznacz komórkę z błędem, aby zobaczyć jaki obszar potrzebuje funkcja aby wyświetlić wyniki - zakres będzie zaznaczony przerywaną linią. Na poniższym zdjęciu widać, że przeszkodą jest komórka z "blabla"- usuń ją lub przenieś w inne miejsce, a problem zniknie.

6. Zawsze posortowana lista - FUNKCJA SORTUJ

Jeśli chcesz posortować listę województw, możesz dodać Funkcję SORTUJ.

Czyli uzyskaną listę unikatów "zapakuję" w funkcję SORTUJ. Dzięki temu będzie ona ciągle posortowana alfabetycznie (od A-Z) - nawet jak dodamy nowe dane do naszych danych źródłowych.

7. Dokładanie danych źródłowych

Na końcu mojego źródła danych dopiszę dodatkowe woj. POMORSKIE, uzupełnię dane o miasto GDAŃSK (moje dane są typu geograficznego, więc same pobiorą pozostałe wartości dla liczby mieszkańców i obszaru w km2). Zobacz, że lista województw automatycznie się powiększyła i umieściła woj. POMORSKIE zgodnie z alfabetem.

8. Tworzenie listy rozwijanej

Teraz mogę odwołać się do nowo powstałej listy województw jako źródła listy rozwijanej.

W arkuszu "lista rozwijana", w którym chcę umieścić listę rozwijaną z województw, zaznaczę komórki, w których ona ma występować. Następnie z karty Dane wybieram narzędzie: Poprawność Danych i na zakładce Ustawienia wybieram "Lista".

9. Źródło danych listy rozwijanej

W źródle danych zaznaczę arkusz, z którego Excel ma pobrać dane, a następnie komórkę, w której mamy formułę UNIKATOWE z SORTUJ. W moim przypadku będzie to komórka H4 w arkuszu "dane geograficzne". Uwaga!!! Zakończ dane źródłowe znakiem "#".

10. Lista rozwijana gotowa 😁😎

11. Sprawdźmy wszystkie funkcjonalności

Zmienię jedną pozycję w moim głównym źródle danych na dodatkowe województwo: opolskie, wpiszę miasto Ople, aby uzupełnić resztę danych i widzę, że automatycznie zaktualizowało się źródło mojej listy rozwijanej 😁

Na liście rozwijanej też to województwo jest od razu widoczne.

12. Przeszukiwalna lista rozwijana

Pojawiła się aktualizacja dla użytkowników Excel Insider kanał Beta, która umożliwia wpisywanie w komórkę z listą rozwijaną fragmentu tekstu i lista sama się przeszukuje pokazując możliwe podpowiedzi. Niestety dostęp do tej funkcjonalności mają tylko nieliczni użytkownicy, gdyż funkcjonalność jest ciągle w fazie testu (sprawdź teraz u siebie - gdyż może kiedy czytasz ten artykuł to ta funkcjonalność już została włączona 🤔).

13. Lista rozwijana w Excelu on-line

Jeśli otworzysz swoje pliki on-line (z OneDrive lub SharePoint) będziesz mógł skorzystać z funkcjonalności przeszukiwalnej listy rozwijanej.

Kiedy zacznę wpisywać fragment nazwy województwa, który widnieje na liście, to pojawi mi się odfiltrowana lista z województwami, które spełniają te kryteria.

14. Sięgnij po więcej

Zachęcam Cię do przeczytania artykułu o listach rozwijanych tworzonych za pomocą innych narzędzi Dynamiczna lista rozwijana - walidacja, poprawność danych.

Omawiam w nim w szerszym stopniu komunikaty narzędzia Poprawności Danych oraz pokazuję, jak wykonać funkcję SUMA.JEŻELI na bazie dynamicznej listy rozwijanej.