Aby zrobić listę rozwijaną w Excelu, zaznacz komórki, przejdź do Dane → Poprawność danych (Data Validation), w polu Zezwalaj wybierz Lista, a w polu Źródło wpisz pozycje oddzielone średnikami albo zaznacz komórki, w których się znajdują. Kliknij OK, a każda komórka dostanie strzałkę otwierającą Twoją listę.
Liczy się jeden wybór: skąd pochodzą pozycje. Lista wpisana ręcznie jest szybka, ale niewygodna w zmianie. Jeśli lista będzie rosnąć, trzymaj pozycje w tabeli Excela, aby każda lista rozwijana automatycznie uwzględniała nowe wpisy.
| Źródło pozycji | Przykład w polu Źródło | Aktualizuje się automatycznie? | Najlepsze do |
|---|---|---|---|
| Lista wpisana ręcznie | Tak;Nie;Może | Nie, edytujesz ją ręcznie | Krótkich list, które się nie zmieniają |
| Zakres komórek | =$F$2:$F$5 | Tylko jeśli edytujesz wewnątrz zakresu | Stałych list trzymanych w arkuszu |
| Tabela Excela | Zaznacz komórki kolumny tabeli | Tak, w miarę dodawania wierszy | List, które z czasem rosną |
| Zakres rozlany | =$H$2# | Tak, z formuły takiej jak UNIKATOWE | List pobieranych z istniejących danych |
| Lista zależna | =ADR.POŚR($A2) | Zależy od pierwszego wyboru | Kategoria, a potem pozycja |
Tworzenie listy rozwijanej krok po kroku
Te kroki działają w Excelu dla Microsoft 365 oraz w Excelu 2021 i 2019 w systemie Windows, a na Macu i w Excelu dla internetu są niemal identyczne (różnice poniżej).
- Wpisz pozycje listy w jednej kolumnie, bez pustych komórek, na przykład Nie rozpoczęto, W toku, Gotowe i Zablokowane w komórkach F2:F5. Dobrze sprawdza się do tego osobny arkusz o nazwie „Listy”.
- Zaznacz komórkę lub komórki, w których ma pojawić się lista rozwijana, na przykład C2:C50.
- Przejdź do Dane → Poprawność danych (w grupie Narzędzia danych). W Windows skrót klawiszowy w angielskiej wersji Excela to Alt, A, V, V.
- Na karcie Ustawienia w polu Zezwalaj wybierz Lista.
- Kliknij w polu Źródło i zaznacz pozycje w arkuszu (F2:F5, bez nagłówka). Excel wpisze
=$F$2:$F$5. - Upewnij się, że zaznaczona jest opcja Lista rozwijana w komórce. Pozostaw zaznaczone Ignoruj puste, jeśli puste komórki są dopuszczalne.
- Kliknij OK.
Zaznacz jedną z tych komórek, a obok pojawi się strzałka. Kliknij ją albo naciśnij Alt+strzałka w dół (Option+strzałka w dół na Macu), aby otworzyć listę.
Wpisywanie pozycji zamiast zaznaczania komórek
Dla krótkiej, stałej listy wpisz pozycje bezpośrednio w polu Źródło: Tak;Nie;Może. W polskim Excelu separatorem jest średnik; w angielskim Excelu (z kropką jako separatorem dziesiętnym) pozycje oddziela się przecinkami (Yes,No,Maybe). Nie dodawaj spacji po separatorach, bo staną się częścią każdej pozycji. Pole Źródło mieści do 255 znaków, więc dłuższe listy powinny znajdować się w arkuszu.
Excel dla Maca i Excel dla internetu
- Mac: Dane → Poprawność danych (w niektórych wersjach przycisk w grupie Narzędzia nazywa się Sprawdź). Okno ma te same trzy karty.
- Internet: Dane → Poprawność danych (w grupie Narzędzia danych, a przy wąskim oknie w menu rozwijanym), a potem te same kroki Zezwalaj → Lista.
- Urządzenia mobilne: aplikacje na iPhone'a, iPada i Androida pozwalają wybierać z list rozwijanych utworzonych na komputerze.
Lista, która rośnie automatycznie dzięki tabeli
Zwykły zakres, taki jak $F$2:$F$5, nie obejmie pozycji wpisanej w F6. Zamień listę w tabelę, a obejmie:
- Kliknij komórkę na liście i naciśnij
Ctrl+T(Cmd+Tna Macu). Zaznacz Moja tabela ma nagłówki i kliknij OK. - Utwórz listę rozwijaną jak wyżej, zaznaczając w polu Źródło komórki z danymi tabeli (bez nagłówka).
- Wpisz nową pozycję w wierszu bezpośrednio pod tabelą. Tabela się rozszerzy, a każda lista rozwijana oparta na niej pokaże nową pozycję.
Nie możesz wpisać odwołania do tabeli, takiego jak =Tabela1[Status], bezpośrednio w polu Źródło; Excel je odrzuci. Zamiast tego zaznacz komórki albo użyj =ADR.POŚR("Tabela1[Status]"). (Wszystkie formuły w tym artykule są zapisane w formie polskiego Excela. W angielskim Excelu: INDIRECT, UNIQUE, SORT, FILTER, SUBSTITUTE, z przecinkami zamiast średników.)
Działa też nazwany zakres: zaznacz pozycje, wpisz nazwę, na przykład Statusy, w Polu nazwy (po lewej od paska formuły), naciśnij Enter i użyj =Statusy jako źródła.
Dynamiczna lista z istniejących danych za pomocą UNIKATOWE
Jeśli opcje mają pochodzić z danych, które już masz, na przykład z nazw wszystkich klientów w kolumnie A, pozwól formule zbudować listę. W Excelu dla Microsoft 365, Excelu 2021 lub nowszym albo Excelu dla internetu:
- W pustej komórce, powiedzmy H2, wpisz
=SORTUJ(UNIKATOWE(FILTRUJ(A2:A500;A2:A500<>"")))(w angielskim Excelu=SORT(UNIQUE(FILTER(A2:A500,A2:A500<>"")))). Posortowana lista bez duplikatów rozleje się w dół od H2 (częśćFILTRUJpomija puste komórki, które inaczej pojawiłyby się jako 0). - Utwórz listę rozwijaną, ustawiając Źródło na
=$H$2#.
Znak # każe Excelowi użyć całego zakresu rozlanego, niezależnie od jego długości, więc nowe nazwy pojawiają się na liście rozwijanej automatycznie. Nie możesz wpisać samej funkcji UNIKATOWE w polu Źródło, dlatego potrzebna jest komórka pomocnicza. Więcej o funkcji UNIKATOWE znajdziesz w artykule Usuwanie duplikatów w Excelu.
Lista z innego arkusza
Gdy pole Źródło jest aktywne, kliknij kartę innego arkusza i zaznacz komórki; Excel zapisze coś w rodzaju =Listy!$A$2:$A$10. Następnie możesz ukryć arkusz Listy (kliknij prawym przyciskiem jego kartę → Ukryj), żeby nikt nie edytował go przypadkiem. Źródło w innym skoroszycie działa tylko wtedy, gdy ten skoroszyt jest otwarty, dlatego trzymaj listę w tym samym pliku.
Zależna (kaskadowa) lista rozwijana z użyciem ADR.POŚR
Zależna lista rozwijana zmienia swoje opcje w zależności od innej komórki: wybierz Owoce w kolumnie A, a kolumna B zaproponuje tylko owoce.
- W arkuszu Listy wpisz każdą kategorię w nagłówku, a pod nią jej pozycje. Na przykład Owoce w D1 z pozycjami Jabłko, Banan, Wiśnia poniżej oraz Warzywa w E1 z pozycjami Marchew, Por, Groszek.
- Zaznacz D1:E4 i przejdź do Formuły → Utwórz z zaznaczenia. Zaznacz tylko Górny wiersz i kliknij OK. Excel utworzy nazwany zakres dla każdej kolumny, nazwany tak jak jej nagłówek.
- W komórce A2 w głównym arkuszu utwórz zwykłą listę rozwijaną, której źródłem są dwie komórki z nagłówkami,
=Listy!$D$1:$E$1(nazwy kategorii). - Zaznacz B2, otwórz Dane → Poprawność danych, wybierz Lista i wpisz
=ADR.POŚR($A2)jako źródło. - Jeśli Excel ostrzeże, że źródło aktualnie zwraca błąd, kliknij Tak. To tylko dlatego, że A2 jest jeszcze puste.
Nazwy nie mogą zawierać spacji, więc Utwórz z zaznaczenia zamienia „Produkty mleczne” na Produkty_mleczne. Dla kategorii ze spacjami użyj zamiast tego =ADR.POŚR(PODSTAW($A2;" ";"_")).
Komunikat wejściowy i alert o błędzie
- Komunikat wejściowy: zaznacz Pokaż komunikat wejściowy po zaznaczeniu komórki i wpisz tytuł oraz krótką treść, na przykład „Wybierz status zadania”. Pojawia się za każdym razem, gdy ktoś zaznaczy komórkę.
- Alert o błędzie: zaznacz Pokaż alert o błędzie po wprowadzeniu nieprawidłowych danych i wybierz Styl. Zatrzymaj blokuje wszystko, czego nie ma na liście, a Ostrzeżenie i Informacje pokazują Twój komunikat, ale przepuszczają wpis.
Pamiętaj, że poprawność danych sprawdza tylko to, co ludzie wpisują. Wklejenie danych na komórkę zastępuje jej listę rozwijaną razem z zawartością.
Wyszukiwanie na liście rozwijanej przez wpisywanie
W aktualnych wersjach Excela w Microsoft 365 wpisywanie w komórce z listą rozwijaną filtruje listę do pozycji zawierających te litery, co znacznie przyspiesza pracę z długimi listami. Excel 2021 i starsze tego nie mają.
Edytowanie, kopiowanie lub usuwanie listy rozwijanej
Dodawanie lub zmiana pozycji. W przypadku tabeli wpisz nową pozycję pod nią. W przypadku zakresu edytuj komórki, a potem ponownie otwórz Dane → Poprawność danych i dostosuj Źródło, jeśli zakres się wydłużył. W przypadku listy wpisanej ręcznie edytuj tekst w polu Źródło. Zaznacz Zastosuj te zmiany do wszystkich komórek o takich samych ustawieniach, aby zaktualizować wszystkie pasujące listy rozwijane naraz.
Kopiowanie listy rozwijanej do kolejnych komórek. Przeciągnij uchwyt wypełniania w dół albo skopiuj komórkę, zaznacz miejsce docelowe i wybierz Narzędzia główne → Wklej → Wklej specjalnie → Sprawdzanie poprawności, aby skopiować samą listę rozwijaną bez wartości.
Usuwanie listy rozwijanej. Zaznacz komórki, przejdź do Dane → Poprawność danych, kliknij Wyczyść wszystko, a potem OK. Wartości już znajdujące się w komórkach pozostają. Aby znaleźć wszystkie komórki z listą rozwijaną, użyj Narzędzia główne → Znajdź i zaznacz → Sprawdzanie poprawności danych.
Dlaczego lista rozwijana nie działa
- Nie pojawia się strzałka. Otwórz Dane → Poprawność danych i zaznacz Lista rozwijana w komórce. Strzałka widoczna jest też tylko wtedy, gdy komórka jest zaznaczona. Jeśli strzałek nie ma nigdzie, sprawdź Plik → Opcje → Zaawansowane → Opcje wyświetlania dla tego skoroszytu i ustaw Dla obiektów pokaż na Wszystkie.
- Nowe pozycje się nie pojawiają. Źródłem jest stały zakres. Rozszerz go albo zamień listę w tabelę lub zakres rozlany, aby rosła sama.
- Poprawność danych jest wyszarzona. Arkusz jest chroniony (Recenzja → Cofnij ochronę arkusza), skoroszyt używa przestarzałej funkcji skoroszytu udostępnionego, kilka arkuszy jest zgrupowanych (kliknij kartę pojedynczego arkusza) albo nadal edytujesz komórkę (naciśnij Enter lub Esc).
- Lista pokazuje puste pozycje. Zakres źródłowy obejmuje puste komórki. Zmniejsz go albo użyj tabeli.
- Ktoś wpisał wartość, której nie ma na liście. Styl alertu o błędzie to Ostrzeżenie lub Informacje albo alert jest wyłączony. Przełącz go na Zatrzymaj.
Najczęściej zadawane pytania
Jak dodać listę rozwijaną do całej kolumny? Zaznacz potrzebny zakres kolumny, na przykład C2:C1000, zanim otworzysz Poprawność danych. Jeśli Twoje dane są w tabeli, lista rozwijana zastosowana do kolumny automatycznie obejmie nowe wiersze.
Czy lista rozwijana w Excelu może pozwalać na wielokrotny wybór? Nie za pomocą wbudowanej funkcji Poprawność danych. Każda komórka przechowuje jeden wybór. Wybranie kilku wartości wymaga makra VBA albo innego układu, na przykład jednej kolumny na opcję.
Ile pozycji może mieć lista rozwijana? Lista z komórek może zawierać tysiące pozycji, choć długie listy trudno przewijać. Lista wpisana ręcznie jest ograniczona 255 znakami w polu Źródło.
Jak zrobić listę rozwijaną z opcjami Tak i Nie? Ustaw Zezwalaj na Lista i wpisz Tak;Nie w polu Źródło.
Jak usunąć listę rozwijaną, zachowując wartości? Zaznacz komórki, przejdź do Dane → Poprawność danych → Wyczyść wszystko i kliknij OK. Bieżące wartości pozostaną jako zwykły tekst.
Dbaj o czystość danych na listach
Lista rozwijana działa dobrze tylko wtedy, gdy dane, na których się opiera, są spójne. CoreGPT for Excel pozwala opisać porządkowanie zwykłym językiem, na przykład „usuń zduplikowanych klientów i popraw różne zapisy nazw krajów”, i wykonuje je w Twoim skoroszycie, dzięki czemu listy, z których budujesz listy rozwijane, pozostają uporządkowane. Można zacząć za darmo, a narzędzie działa w Excelu dla internetu, Windows i Mac.
CoreGPTGotowe. Wprowadzono te zmiany:
- Usunięto 12 zduplikowanych wierszy
- Zmieniono „US”, „DE”… na pełne nazwy
- Ujednolicono numery telefonów
Wypróbuj: CoreGPT for Excel
Powiązane artykuły:
