Lista rozwijana w czterech krokach
Zaznacz komórkę, wejdź w kartę Dane, kliknij Poprawność danych, w polu Dozwolone wybierz Lista i wskaż źródło. Zatwierdź OK i w komórce pojawi się strzałka. W starszych wersjach narzędzie nazywa się Poprawność danych, w nowszych Sprawdzanie poprawności danych, ale znajduje się w tej samej grupie Narzędzia danych.
Trzy rzeczy, które warto ustawić od razu, bo potem zwykle nikt do nich nie wraca:
- Ignoruj puste. Odznacz to pole, jeśli komórka musi być wypełniona. Zostawione zaznaczone pozwala zostawić pustą komórkę bez komunikatu.
- Komunikat wejściowy. Krótka podpowiedź wyświetlana po kliknięciu komórki. Według dokumentacji Microsoft Support mieści do 225 znaków, więc wystarczy na zdanie wyjaśniające, co wybrać.
- Alert o błędzie. Styl Zatrzymaj blokuje wpisanie wartości spoza listy. Style Ostrzeżenie i Informacja tylko ostrzegają i przepuszczają wpis dalej.
Wybór stylu alertu to decyzja, nie formalność. Zatrzymaj stosuj tam, gdzie dane trafiają potem do tabeli przestawnej albo funkcji wyszukujących, bo każda literówka rozbija raport. Ostrzeżenie ma sens w arkuszach roboczych, gdzie lista jest podpowiedzią, a nie regułą. Jeśli zostawisz Zatrzymaj w formularzu wypełnianym przez wiele osób, prędzej czy później ktoś zadzwoni, że nie może wpisać nowego kontrahenta.
Wpisać pozycje ręcznie czy wskazać komórki z arkusza?
Ręcznie wpisuj tylko listy, które się nie zmieniają i mają kilka pozycji. Wszystko pozostałe trzymaj w komórkach. W polu Źródło możesz wpisać wartości rozdzielone separatorem listy, w polskich ustawieniach regionalnych jest to średnik, czyli na przykład Tak;Nie;Nie dotyczy. Alternatywnie zaznaczasz zakres komórek, w którym te wartości już są.
Różnica nie dotyczy wygody wpisywania, tylko tego, co dzieje się z listą w przyszłości:
| Kryterium | Pozycje wpisane w oknie | Pozycje w komórkach |
|---|---|---|
| Widoczność w arkuszu | Ukryte, widać je tylko w oknie poprawności danych | Widoczne, można je przeczytać i sprawdzić |
| Edycja | Trzeba otworzyć okno i poprawić tekst | Wystarczy zmienić zawartość komórki |
| Wiele list naraz | Każdą trzeba poprawić osobno | Jedna zmiana aktualizuje wszystkie listy z tego zakresu |
| Sensowne zastosowanie | Tak/Nie, statusy, 3 do 5 stałych wartości | Kontrahenci, produkty, kategorie, działy |
Najczęstszy błąd na tym etapie to wpisanie dwudziestu nazw produktów bezpośrednio w okno. Po pół roku nikt nie pamięta, w których komórkach ta lista w ogóle siedzi, a wyszukiwarka Excela jej nie znajdzie, bo to nie jest zawartość komórki. Drugi typowy problem dotyczy separatora. Jeśli skopiujesz listę z angielskiego poradnika i wkleisz Yes,No, w polskim Excelu dostaniesz jedną pozycję o nazwie „Yes,No” zamiast dwóch.
Dane źródłowe najlepiej trzymać na osobnym arkuszu nazwanym na przykład „Słowniki”. Zyskujesz jedno miejsce, w którym aktualizujesz wszystkie listy w skoroszycie, zamiast szukać ich po kilkunastu zakładkach.
Jak sprawić, żeby lista sama uwzględniała nowe pozycje
Zamień zakres źródłowy w tabelę Excela skrótem Ctrl+T i wskaż jej kolumnę jako źródło listy. Tabela rozszerza się automatycznie o każdy dopisany wiersz, więc lista rozwijana od razu pokazuje nową pozycję. Dokumentacja Microsoft Support wprost zaleca to rozwiązanie jako domyślne przy tworzeniu list rozwijanych.
W Excelu 365 i 2021 masz jeszcze jedną drogę, często wygodniejszą, bo pozwala uporządkować dane w locie:
=UNIKATOWE(tblTransakcje[Kategoria])zwraca każdą kategorię tylko raz, nawet jeśli w tabeli powtarza się setki razy. Przydaje się, gdy lista ma powstać z danych operacyjnych, a nie ze słownika.=SORTUJ(UNIKATOWE(tblTransakcje[Kategoria]))dodatkowo układa pozycje alfabetycznie. Na liście liczącej kilkadziesiąt wartości kolejność alfabetyczna skraca wyszukiwanie bardziej niż jakakolwiek inna zmiana.
Formułę umieszczasz w komórce pomocniczej, a w polu Źródło podajesz odwołanie do jej rozlania, czyli na przykład =$G$2#. Znak # oznacza cały wynik formuły, niezależnie od tego, ile ma pozycji. Dzięki temu na liście nigdy nie pojawią się puste wiersze na końcu, co jest stałym problemem przy wskazywaniu sztywnego zakresu w rodzaju A1:A100.
Jest jedno ograniczenie, o którym warto wiedzieć zawczasu. Formuła rozlewająca potrzebuje wolnego miejsca pod sobą. Jeśli ktoś wpisze cokolwiek w komórkę poniżej, zobaczysz błąd #ROZLANIE!, a lista przestanie działać. Dlatego komórki pomocnicze trzymam na osobnym arkuszu, a nie z boku głównej tabeli.
Zanim zbudujesz listę na formule, sprawdź w jakiej wersji Excela będzie otwierany plik u odbiorcy. UNIKATOWE, SORTUJ i FILTRUJ działają w Microsoft 365 i Excelu 2021. W Excelu 2019 i starszym plik się otworzy, ale formuły zwrócą błąd #NAZWA?, a lista rozwijana będzie pusta. W firmach z mieszanymi wersjami bezpieczniejszym wyborem jest zwykła tabela Ctrl+T.
Listy zależne, czyli najpierw kategoria, potem produkt
Lista zależna to druga lista, której zawartość zmienia się w zależności od wyboru w pierwszej komórce. Wybierasz województwo, a lista miast pokazuje tylko miasta z tego województwa. W Excelu 365 i 2021 buduje się ją funkcją FILTRUJ, w starszych wersjach funkcją ADR.POŚR i nazwanymi zakresami.
Wariant nowszy wygląda tak. Dane trzymasz w tabeli z dwiema kolumnami, na przykład Województwo i Miasto. W komórce pomocniczej wpisujesz =FILTRUJ(tblMiasta[Miasto];tblMiasta[Województwo]=B2), gdzie B2 to komórka z wyborem nadrzędnym. Źródłem drugiej listy jest rozlanie tej formuły.
Pustą komórkę nadrzędną trzeba obsłużyć osobno, bo FILTRUJ bez trafienia zwraca błąd. Wystarczy warunek: =JEŻELI(B2="";"";FILTRUJ(tblMiasta[Miasto];tblMiasta[Województwo]=B2)). Jeśli chcesz zrozumieć, dlaczego ten zapis działa i jak modyfikować podobne warunki, pomoże omówienie tego, jak działa funkcja JEŻELI w Excelu.
Wariant dla Excela 2016 i 2019 opiera się na nazwach. Każdą kolumnę z pozycjami zaznaczasz i nadajesz jej nazwę dokładnie taką, jak wartość nadrzędna, czyli na przykład „Mazowieckie”. Źródłem drugiej listy jest wtedy =ADR.POŚR(B2). Funkcja zamienia tekst z komórki B2 na odwołanie do zakresu o tej nazwie. Metoda działa dobrze, ale ma twardy warunek: nazwy w Excelu nie mogą zawierać spacji ani myślników. „Grodzisk Mazowiecki” nie przejdzie, trzeba użyć podkreślnika i podmienić znak w formule funkcją PODSTAW.
O czym prawie nikt nie uprzedza: zmiana wyboru nadrzędnego nie czyści komórki podrzędnej. Jeśli ktoś wybrał Płock, a potem zmienił województwo, w komórce nadal zostaje Płock, mimo że nie ma go już na liście. Lista rozwijana ogranicza wpisywanie, ale nigdy nie nadpisuje wartości, która już jest w komórce. W arkuszach dla wielu użytkowników warto dołożyć formatowanie warunkowe, które podświetli komórkę, gdy jej zawartość nie pasuje już do wyboru nadrzędnego.
Jak później edytować, skopiować albo usunąć listę
Edycja zależy od tego, jak powstało źródło. Listę opartą na komórkach zmieniasz, edytując te komórki. Listę wpisaną ręcznie poprawiasz w oknie Poprawność danych. Usuwasz ją w tym samym oknie przyciskiem Wyczyść wszystko.
Przy zmianie ustawień zwróć uwagę na pole „Zastosuj te zmiany we wszystkich komórkach z tymi samymi ustawieniami”. Zaznaczone aktualizuje regułę w całej kolumnie naraz. Odznaczone zmienia tylko bieżącą komórkę, przez co w jednej kolumnie powstają dwie różne reguły, a to najtrudniejszy do wykrycia błąd w całym temacie list rozwijanych.
Kopiowanie listy na kolejne wiersze ma dwie metody i obie mają konsekwencje:
- Przeciągnięcie za uchwyt wypełniania. Szybkie, ale kopiuje też formatowanie i zawartość komórki. W kolumnie z już wpisanymi danymi nadpisze to, co tam jest.
- Wklej specjalnie, opcja Poprawność danych. Kopiuje wyłącznie regułę, bez wartości i bez formatów. To właściwa metoda, gdy dokładasz listę do wypełnionej już tabeli.
Odwrotna sytuacja też się zdarza i zaskakuje użytkowników. Zwykłe wklejenie skopiowanej komórki w miejsce z listą rozwijaną kasuje regułę poprawności danych. Arkusz wygląda tak samo, strzałka znika dopiero po kliknięciu, a kontrola danych w tym miejscu po prostu przestaje istnieć. Jeśli plik wypełnia kilka osób, zabezpiecz arkusz, zamiast liczyć na to, że nikt nie użyje Ctrl+V.
Dlaczego lista rozwijana nie działa
W praktyce przyczyn jest kilka i każda ma inny objaw. Poniższa lista układa je od najczęstszej, zaczynając od sytuacji, w której w ogóle nie da się otworzyć okna Poprawność danych.
- Przycisk Poprawność danych jest wyszarzony. Arkusz jest chroniony albo skoroszyt udostępniony w starym trybie. Zdejmij ochronę w karcie Recenzja i spróbuj ponownie.
- Lista nie pokazuje nowych pozycji. Źródłem jest sztywny zakres, a dane dopisano poniżej. Rozwiązanie to tabela Ctrl+T albo formuła z rozlaniem.
- Strzałki nie widać. Strzałka pojawia się dopiero po zaznaczeniu komórki. Jeśli dalej jej nie ma, sprawdź w opcjach zaawansowanych ustawienie pokazywania strzałki listy rozwijanej dla skoroszytu.
- Lista zniknęła z części komórek. Ktoś wkleił tam dane zwykłym Ctrl+V. Przywróć regułę przez Wklej specjalnie, Poprawność danych.
- Lista nie działa po otwarciu w przeglądarce. Excel dla sieci Web nie obsługuje części źródeł, zwłaszcza odwołań do innych skoroszytów. Przenieś dane źródłowe do tego samego pliku.
- Formuła źródłowa zwraca #ROZLANIE!. Pod komórką z UNIKATOWE lub FILTRUJ stoi jakaś wartość. Zwolnij miejsce albo przenieś formułę na arkusz pomocniczy.
Jest jeszcze przypadek, który wygląda jak awaria, a nią nie jest. Lista z zakresu na innym arkuszu działa bez problemu w nowszych wersjach Excela, więc jeśli zwraca błąd, zwykle winna jest nazwa arkusza ze spacją bez apostrofów w odwołaniu. Najprościej ominąć to, definiując nazwany zakres w Menedżerze nazw i wpisując w Źródle samą nazwę, bo nazwy działają globalnie w całym skoroszycie.
Czego lista rozwijana w Excelu nie potrafi
Lista rozwijana to reguła sprawdzania wpisu, a nie kontrolka formularza. Przechowuje jedną wartość, pokazuje jedną kolumnę i nie da się jej sformatować. Każde z tych ograniczeń ma obejście, ale żadne nie jest natywne.
| Czego oczekują użytkownicy | Jak jest naprawdę | Obejście |
|---|---|---|
| Wybór kilku pozycji w jednej komórce | Nowy wybór zastępuje poprzedni | Makro VBA na zdarzenie zmiany albo kontrolka ListBox z MultiSelect |
| Dwie kolumny na liście, np. kod i nazwa | Lista pokazuje jedną kolumnę | Kolumna pomocnicza łącząca wartości formułą, potem rozdzielenie wyniku |
| Kolorowanie pozycji na rozwiniętej liście | Formatować można tylko komórkę, nie listę | Formatowanie warunkowe reagujące na wybraną wartość |
| Automatyczne podstawienie ceny po wyborze produktu | Lista zapisuje wyłącznie tekst | Funkcja wyszukująca w sąsiedniej komórce |
Ostatni punkt to najczęstsze nieporozumienie. Lista rozwijana sama z siebie niczego nie liczy i nie pobiera. Daje tylko czystą, przewidywalną wartość, na której dopiero pracują formuły. Do podstawienia ceny czy danych kontrahenta użyj funkcji wyszukującej, na przykład funkcji X.WYSZUKAJ, która znajdzie wiersz odpowiadający wybranej pozycji.
Przy długich listach dochodzi kwestia wygody. W Excelu dla Microsoft 365 wpisanie pierwszych liter zawęża listę do pasujących pozycji, funkcja jest domyślnie włączona w opcjach edycji. W starszych wersjach tego nie ma i przy kilkuset pozycjach przewijanie staje się męczarnią. Wtedy albo dzielisz listę na kaskadę dwóch poziomów, albo dokładasz pole wyszukiwania oparte na FILTRUJ. Jeżeli dane do listy pochodzą z zewnętrznego systemu, warto wciągnąć je do arkusza przez Power Query i odświeżać jednym kliknięciem, zamiast wklejać ręcznie co miesiąc.
Co sprawdzić, zanim wyślesz arkusz dalej
Lista rozwijana ma sens tylko wtedy, gdy wytrzyma kontakt z innymi użytkownikami. Zanim wyślesz plik, przejdź przez siedem punktów. Zajmuje to kilka minut i eliminuje większość zgłoszeń w stylu „u mnie nie działa”.
- Sprawdź wersję Excela u odbiorcy. Formuły dynamiczne nie zadziałają w Excelu 2019 i starszym. Tabela Ctrl+T zadziała wszędzie.
- Otwórz listę z klawiatury. Skrót Alt i strzałka w dół na Windowsie pokaże, czy reguła faktycznie siedzi w komórce.
- Przetestuj dolny wiersz. Dopisz pozycję na końcu źródła i sprawdź, czy pojawiła się na liście. To najszybszy test, czy źródło jest dynamiczne.
- Poszerz kolumnę. Lista pokazuje tyle, ile mieści komórka. Za wąska kolumna ucina dłuższe nazwy.
- Zweryfikuj styl alertu. Zatrzymaj tam, gdzie dane idą do raportu. Ostrzeżenie tam, gdzie użytkownik może potrzebować wartości spoza listy.
- Ukryj i zabezpiecz arkusz ze słownikami. Zgodnie z dokumentacją Microsoft Support arkusz źródłowy można ukryć i chronić, żeby nikt go przypadkiem nie nadpisał.
- Odblokuj komórki do wypełnienia. Przed włączeniem ochrony arkusza zdejmij blokadę z komórek z listami, inaczej odbiorca nie wpisze niczego.
Punkt ósmy, nieobowiązkowy, ale praktyczny: zapisz kopię pliku przed oddaniem go do wypełnienia. Reguły poprawności danych łatwo skasować wklejeniem i nie ma na to cofnięcia po zamknięciu pliku. Kopia wzorcowa pozwala w minutę odtworzyć strukturę zamiast budować ją od nowa.
Jeśli arkusz ma iść do kilkunastu osób i zawiera kaskadowe listy, formuły wyszukujące oraz ochronę, warto zaprojektować go raz, a porządnie. Przy takich plikach zwykle kończy się na tworzeniu aplikacji w Excelu, czyli arkuszu z gotowym formularzem, słownikami i zabezpieczeniem przed przypadkową edycją.