W jednym z arkuszy posiadam tabelę o nazwie "Dane_geograficzne" zawierającą listę województw, wybrane miasta z tych województw, liczbę ich ludności oraz obszar jaki zajmują w km2. Chciałabym stworzyć listę rozwijaną, za pomocą której będę wybierała województwo. Obok wybranego województwa będę chciała uzyskać dowolne obliczenia, które będą budowane na bazie mojego wybranego województwa.

Możemy to zadanie wykonać na dwa sposoby:

1. lista rozwijana utworzona za pomocą narzędzia "poprawność danych" (wszystkie wersje Excela)
2. dynamiczna lista rozwijana utworzona za pomocą funkcji dynamicznych (Excel 365 i on-line) - zajrzyj na post: Dynamicznie rozszerzana, przeszukiwalna lista rozwijana na bazie funkcji – Excel 2021 / 365 / WEB

Dodatkowo, jeśli chcesz zobaczyć, że występuje już przeszukiwalna lista rozwijana- NOWA FUNKCJONALNOŚĆ EXCELA - to również możesz o tym przeczytać w powyższym poście.​

1. Źródło danych do listy rozwijanej

Po pierwsze, potrzebujesz mieć źródło danych, które umieścisz w liście rozwijanej.

Dane, które będą podstawą mojego źródła danych dla listy rozwijanej znajdują się w arkuszu "dane geograficzne". Zostały one przeze mnie zamienione na Tabelę Excela poprzez skrót klawiaturowy Ctrl+T. Zalety i zasady tworzenia narzędzia Tabela opisuję w poście: Narzędzie Tabela w Excelu – podstawa przechowywania danych

Na karcie Projekt tabeli, która powstaje razem z narzędziem Tabela, możesz nadać tabeli indywidualną nazwę. Ja swoją nazwałam "Dane_geograficzne".

2. Lista unikatów - Usuwanie duplikatów

W moim głównym zadaniu chciałabym mieć listę wymienionych w tabeli "Dane_geograficzne" województw, więc potrzebuję mieć listę unikatowych nazw województw (jeśli będą duplikaty, to w liście rozwijanej będą one występowały tyle razy, ile w zaznaczonym źródle, czyli w moim przypadku będzie występowało kilka razy woj. dolnośląskie, kujawsko-pomorskie i inne...)

Jeśli zaczniesz usuwać duplikaty bezpośrednio w oryginalnej tabeli to popsujesz dane. Dlatego skopiuj listę województw (bez nagłówka) i przenieś ją w wybrane miejsce.

W moim przypadku wklejam skopiowaną listę do arkusza: "lista rozwijana". W ten sposób zakładam, że źródło naszej listy rozwijanej jest w tym samym arkuszu.

3. Narzędzie usuń duplikaty

Będąc w obrębie skopiowanych danych idziemy na kartę "Dane", z której wybieramy narzędzie "Usuń duplikaty".

W nowym oknie: "Usuwanie duplikatów" Excel sam powinien zaznaczyć, że "moje dane mają nagłówki" (niezbędne, aby wykluczyć nagłówek kolumny "Lista województw"). Jeśli tego nie ma to doklikaj to. Wówczas Excel sam prawidłowo zaznaczy dane, na których będzie pracował. Powinieneś wówczas uzyskać okno jak u mnie. Następnie wybierz OK.

Pojawi się komunikat, informujący ile Excel znalazł i usunął duplikatów i ile pozostawił unikatowych rekordów. Wybierz OK.

4. Usuwanie formatowania

Zauważ, że lista jest już bardzo krótka i pozostał tylko ślad formatowania z naszej źródłowej tabeli "Dane_geograficzne". Zaznacz te dane i usuń to formatowanie (tak jak to robisz zazwyczaj, np.: poprzez zmianę koloru wypełnienia na: brak wypełnienia).

5. Narzędzie "Poprawność Danych"

Wybierz komórkę (lub od razu zakres komórek), w których chcesz umieścić listę rozwijaną. Następnie na karcie Dane wybierz opcję Poprawność Danych. W nowym oknie (Sprawdzanie poprawności danych), na karcie Ustawienia, wybierz opcję Lista.

6. Źródło listy rozwijanej

W źródle danych wskaż komórki, z których Excel ma pobrać dane. Następnie kliknij OK.

7. Lista rozwijana GOTOWA!

8. Kopiowanie listy rozwijanej do innych komórek

Jeśli wcześniej zaznaczyłeś tylko jedną komórkę, w której ma być lista rozwijana to możesz ją teraz skopiować do pozostałych komórek poprzez Ctrl+C i Ctrl+V lub przeciągając za mały kwadracik w prawym dolnym rogu komórki.

Możesz teraz wybrać inne województwa w pozostałych komórkach.

9. Komunikat wejściowy

Możesz zostawić informację dla innych użytkowników pliku, mówiącą co należy zrobić w danej komórce. Zaznacz komórkę, przy której ma się pojawić dodatkowa informacja i skorzystaj z drugiej karty w narzędziu poprawności danych "Komunikat wejściowy". Nadaj tytuł i napisz co ma się wyświetlać w komunikacie, a następnie kliknij OK.

Przy komórce, dla której dokonaliśmy tego ustawienia, pojawi się żółta karteczka, z komunikatem, który chcemy przekazać.

10. Alert o błędzie

Dodatkowo możesz zdefiniować komunikat, który ma być wyświetlany, jeśli użytkownik spróbuje wprowadzić coś, czego nie ma na liście.

W poprawności danych wybierz zakładkę "Alert o błędzie", wpisz tytuł i komunikat.

Możesz też wybrać rodzaj komunikatu:

1. ❌ Stop - użytkownik nie będzie mógł wprowadzić nic z poza listy rozwijanej
2. ⚠️ Ostrzeżenie - użytkownik będzie mógł wprowadzić inną pozycję po świadomej swojej akceptacji
3.   ℹ️    Informacja - użytkownik wpisze co chce, zostanie tylko poinformowany, że nie jest to zgodne z listą rozwijaną

Jeśli wpiszę województwo inne niż te, które jest na liście (np.: podkarpackie), to otrzymam komunikat, który ustawiłam, lub standardowy z Excela.

11. Umożliwianie wpisania czegokolwiek bez komunikatów o błędzie

Natomiast, jeśli w poprawności danych, na karcie Alert o błędzie, odhaczymy opcję: "pokazuj alerty... ", wówczas dajemy możliwość wpisania czegokolwiek w naszą komórkę, ale nie zostanie wyświetlony alert o błędzie. Oczywiście lista rozwijana nadal będzie istniała w komórce.

12. Wykrywanie danych niespełniających założonej reguły

Żeby zaznaczyć komórki, które zawierają województwo, które nie jest na mojej liście mogę skorzystać z dodatkowych opcji narzędzia poprawności danych: Zakreśl nieprawidłowe dane.

Wówczas województwa, które wykraczają poza moją listę zostaną wyróżnione czerwoną elipsą. Aby ją wyłączyć trzeba przy narzędziu poprawności danych wybrać opcję: "wyczyść zakreślenia nieprawidłowych danych".

Aby ją wyłączyć trzeba przy narzędziu poprawności danych wybrać opcję: "Wyczyść zakreślenia nieprawidłowych danych".

13. Dodawanie nowych danych do źródła danych

Załóżmy, że dokładamy dane dot. woj. pomorskiego i miejscowości Gdańsk. Dopisujemy je na końcu tabeli "Dane_Geograficzne". Tabela z automatu wchłania te dane do swojego zakresu, gdyż jest dynamicznym zakresem danych.

Niestety w naszych danych źródłowych do listy rozwijanej nie dołożyło się ono z automatu...

Nawet dopisanie go z ręki nie spowoduje, że to nowe województwo pojawi się w naszej liście rozwijanej.

Dzieje się tak, gdyż przy tworzeniu listy, w jej źródle, zaznaczaliśmy konkretny obszar (konkretny zakres komórek), z którego pobiera ona dane.

Możemy teraz:

a) zmienić zakres źródła listy rozwijanej zaznaczając większy obszar
b) zmienić nasze dane źródłowe do listy na narzędzie Tabela - i tę opcję wybiorę do naszego zadania.

14. Narzędzie Tabela jako źródło listy rozwijanej

Usunę najpierw poprzednio wpisaną daną z ręki (woj. pomorskie) -> zastosuję skrót Ctrl + Z.

Następnie stanę w obrębie źródła listy rozwijanej i zastosuję skrót Ctrl + T, który utworzy mi Tabelę z mojej listy województw.

15. Nadawanie nazwy Tabeli

Z karty Projekt tabeli możesz zmienić styl swojej tabeli (ikona po prawej stronie wstążki) a także nazwę swojej Tabeli (po lewej stronie). Ja wprowadzę nazwę: "Województwa" i zmienię styl na "żaden". Pamiętaj, aby nazwę zatwierdzić Enterem.

16. Dynamiczna lista rozwijana

Zamiana danych źródłowych na Tabelę, spowodowała pokrycie się zakresu Tabeli z obecnym zakresem źródła listy rozwijanej. W tym momencie lista rozwijana przetwarza swoje źródło na źródło Tabeli i staje się dynamiczną listą rozwijaną.

17. Dopisanie pozycji do listy rozwijanej

Dopiszę teraz woj. pomorskie na spodzie listy województw. Zobacz, że automatycznie to woj. zostało wchłonięte do Tabeli: "Województwa" i również już występuje na liście rozwijanej.

18. Mały Tip! Sortowanie źródła listy rozwijanej

19. Zewnętrzne źródło danych listy rozwijanej

Poprzednio umieszczałam źródło listy rozwijanej w tym samym arkuszu co lista rozwijana. Tym razem umieszczę je w innym arkuszu. Jest to najczęściej używana opcja, gdyż często nie chcemy wyświetlać źródła listy "na widoku". Dodatkowo uzyskam możliwość ukrycia arkusza ze źródłem danych, aby przypadkowo nie zrobić w nim niepotrzebnej zmiany.

Działanie przy tworzeniu jest takie samo jak poprzednio:

• najpierw tworzymy unikatową listę województw, ale w nowym arkuszu (mój nazywać się będzie: "zewn źródło listy"). Ja tworzę ją z pierwotnych danych, czyli bez woj. pomorskiego (nie jest to konieczne).
• następnie z listy województw tworzymy Tabelę (Ctrl + T) i nazywamy ją "Województwa2 " - pamiętaj, że nazwy Tabel nie mogą być identyczne.

• w arkuszu "lista rozwijana" wskazujemy komórkę/komórki, w których ma być lista rozwijana (tym razem będzie to komórka B25 i wybieramy narzędzie poprawności danych z karty Dane.
• w źródle listy wskażemy zakres komórek z arkusza "zewn źródło listy" (niestety nie możemy użyć nazwy Województwa2"). Zapis będzie wyglądał jak na poniższym zdjęciu:

20. Sprawdzenie poprawności działania listy rozwijanej

Lista powinna działać bez zarzutów. Ale... Spróbujmy dopisać nowe województwo do Tabeli Województwa2.

Ponieważ, tworzyłam listę z pierwotnego źródła danych, czyli bez woj. pomorskiego to znów je dopiszę (Ty możesz dopisać inne województwo, jeśli pomorskie już masz na liście).

Niestety zauważ, że tego nowego województwa, pomimo narzędzia Tabela, nie ma w naszej liście rozwijanej.

21. Nazwy zdefiniowane dla Tabeli

Musimy wykonać jeszcze jeden krok, aby lista rozwijana działała prawidłowo

22. Tworzenie nazwy zdefiniowanej

Cofnę wpisaną pozycję: pomorskie (Ctrl+ Z) aby zrobić mały porządek. Następnie zaznaczę wszystkie województwa z listy województw i z karty Formuły wybiorę opcję Definiuj nazwę. Zmienię nazwę na "LW" i w odwołaniu mogę zauważyć, że pojawiło się odwołanie do Tabeli =Województwa2[Lista województw]. Klikam Ok.

23. Menedźer nazw

W menedżerze nazw mogę podejrzeć wszystkie nazwy, które są utworzone obecnie w moim pliku. Widać, że poza nazwami Tabel pojawiła się nazwa "LW", której zawartość stanowią województwa.

24. Użycie nazwy zdefiniowanej w źródle listy rozwijanej

Zaznaczam ponownie komórki, w których chcę mieć listę rozwijaną i tym razem na Karcie Dane w Poprawności Danych dla Listy wskażę źródło jako odwołanie do nazwy " = LW".

Dopisuję województwo pomorskie. Zostaje ono wchłonięte do mojego źródła listy rozwijanej i jednocześnie jest już widoczne w mojej liście rozwijanej 😎😎

25. Sortowanie listy rozwijanej

Przypominam, jeśli chcesz, aby lista była posortowana alfabetycznie, posortuj dane źródłowe.

26. Lista rozwijana na bazie funkcji - Excel 365

Pewnie zauważyłeś mankamenty tej metody:

• ź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ą;
• dopisanie danych do głównego źródła naszej listy (czyli pierwotnej tabeli: "Dane_geograficzne") nie powoduje, że nasze źródło listy rozwijanej samo się zaktualizuje - musimy tę daną dopisać ręcznie do źródła listy rozwijanej.

W Excelu 365 możemy wyzbyć się tych niedogodności. Jeśli chcesz dowiedzieć się jak stworzyć dynamiczną listę rozwijaną na bazie funkcji zapraszam Cię do postu: Dynamicznie rozszerzana, przeszukiwalna lista rozwijana na bazie funkcji – Excel 2021 / 365 / WEB

27. SUMA.JEŻELI na bazie listy rozwijanej

Przemycę jeszcze informację jak wykonać jedno z najczęściej używanych obliczeń na bazie listy rozwijanej i bazy danych w postaci Tabeli, a mianowicie użycie funkcji SUMA.JEŻELI.

Funkcja SUMA.JEŻELI ma za zadanie zsumować pewne wartości z zaznaczonego zakresu danych spełniające określone kryteria. W moim przypadku będzie to suma mieszkańców z wybranego województwa. Oczywiście moja baza danych jest przykładowa i nie zawiera wszystkich danych, więc nie będą to dane prawdziwe ;-)

Funkcja w komórce C25 będzie wyglądała następująco:

Teraz możesz skopiować formułę do pozostałych komórek.

Zmień województwo na inne i sprawdź, czy zmieniają się obliczenia. Wszystko powinno działać bez zarzutów.