Aby zrobić listę rozwijaną w Excelu, zaznacz komórki, przejdź do Dane > Poprawność danych, ustaw Dozwolone na Lista, w polu Źródło wpisz pozycje oddzielone średnikami (North;South;East;West) albo zaznacz zakres, który je zawiera, i naciśnij OK. Każda komórka pokazuje teraz strzałkę z tymi wyborami, a inne wpisy są odrzucane.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Region | Sales | |
| 2 | Ana | North | 120 | North | 360 | |
| 3 | Ben | South | 85 | |||
| 4 | Cara | North | 240 | |||
| 5 | Dan | East | 60 | |||
| 6 | Eve | South | 150 |
F2 pokazuje 360, sumę dla North. Wybierz North w B3, a F2 wzrośnie o 85 Bena. E2 też ma listę rozwijaną: wybierz tam South, a F2 pokaże sumę dla South. Lista do wprowadzania danych i formuła, która ją czyta, to najczęstsze zastosowanie listy rozwijanej. Tabela pokazuje formułę po angielsku, =SUMIF(B2:B6,E2,C2:C6), ale możesz w niej wpisywać formuły także po polsku, ze średnikami: =SUMA.JEŻELI(B2:B6;E2;C2:C6).
Jak zrobić listę rozwijaną krok po kroku
- Zaznacz komórki, które mają dostać listę, na przykład B2:B6.
- Przejdź do Dane > Poprawność danych (grupa Narzędzia danych). W angielskim Excelu dla Windows sekwencja klawiszy to Alt, A, V, V.
- Na karcie Ustawienia ustaw Dozwolone na Lista.
- W polu Źródło albo wpisz pozycje oddzielone średnikami,
North;South;East;West, albo kliknij w pole i zaznacz na arkuszu zakres z pozycjami, co wpisze=$F$2:$F$5. - Zostaw zaznaczone Rozwinięcie w komórce (bez tego nie ma strzałki, jest tylko sprawdzanie).
- Naciśnij OK.
Aby otworzyć listę z klawiatury, zaznacz komórkę i naciśnij Alt+Strzałka w dół (Windows) albo Option+Strzałka w dół (Mac). W Excelu dla Microsoft 365 wpisanie pierwszych liter w komórce zawęża listę do pasujących pozycji.
Dwie opcjonalne karty w tym samym oknie: Komunikat wejściowy pokazuje podpowiedź po zaznaczeniu komórki, a Alert o błędzie ustala, co się dzieje, gdy ktoś wpisze wartość spoza listy. Przy stylu Zatrzymaj (domyślnym) wpis jest odrzucany; przy Ostrzeżenie albo Informacje jest dozwolony po komunikacie. Odznacz Pokazuj alert o błędzie po wprowadzeniu nieprawidłowych danych, aby pozwolić wpisywać cokolwiek i nadal oferować listę.
Wpisane pozycje oddziela się separatorem listy z ustawień regionalnych komputera. W polskich ustawieniach, tak jak w innych krajach z przecinkiem dziesiętnym, jest to średnik: North;South;East;West.
Lista rozwijana z zakresu komórek
Listy wpisanej w oknie nie widać, a edytuje się ją tylko tam. Lista w komórkach jest łatwiejsza w utrzymaniu: zmień komórkę, a zmieni się każda lista rozwijana, która jej używa. Tutaj regiony są w E2:E5, a lista rozwijana w B2:B6 używa tego zakresu jako źródła.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Regions | |
| 2 | Ana | North | 120 | North | |
| 3 | Ben | South | 85 | South | |
| 4 | Cara | North | 240 | East | |
| 5 | Dan | East | 60 | West | |
| 6 | Eve | South | 150 |
Zmień E5 z West na Central, a potem otwórz dowolną strzałkę w kolumnie B: lista oferuje Central zamiast West. Wartości już wybrane w kolumnie B się nie zmieniają.
Aby użyć zakresu z innego arkusza, co jest zwykłym sposobem ukrycia list, wpisz w polu Źródło nazwę arkusza: =Lists!$A$2:$A$5. Aby lista rosła po dopisaniu pozycji na dole, najpierw zamień pozycje w tabelę (zaznacz je, Wstawianie > Tabela), a potem zaznacz kolumnę tabeli jako źródło: odwołanie rozszerza się razem z tabelą.
Dynamiczna lista rozwijana z UNIKATOWE
Gdy pozycje mają pochodzić z samych danych (każdy region, który występuje w kolumnie, każdy raz), zbuduj listę formułą i wskaż liście rozwijanej jej wynik. =SORTUJ(UNIKATOWE(B2:B8)) w G2 rozlewa różne regiony w kolejności alfabetycznej.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Pick | Sales | Regions | |
| 2 | Ana | North | 120 | South | 235 | East | |
| 3 | Ben | South | 85 | North | |||
| 4 | Cara | North | 240 | South | |||
| 5 | Dan | East | 60 | West | |||
| 6 | Eve | South | 150 | ||||
| 7 | Fay | West | 95 | ||||
| 8 | Gus | East | 110 |
G2 rozlewa East, North, South, West, a lista rozwijana w E2 oferuje te cztery. Zmień B7 na Central, a Central pojawi się i w rozlaniu, i na liście.
W Excelu ustaw Źródło listy rozwijanej na =$G$2#. # po komórce oznacza „całe rozlanie tej formuły”, więc lista zawsze ma dokładnie długość wyniku, bez pustych wierszy na końcu. Odwołanie do rozlania wymaga Excela 365 albo 2021; komórka źródłowa może być na innym arkuszu (=Lists!$A$2#). Jeśli kolumna danych ma puste komórki, UNIKATOWE zwraca dla nich 0; pomiń je formułą =SORTUJ(UNIKATOWE(FILTRUJ(B2:B100;B2:B100<>""))). Funkcję opisuje szczegółowo strona UNIKATOWE (UNIQUE).
Zależne listy rozwijane
Lista zależna zmienia się razem z wyborem w innej komórce: wybierz Fruit w A2, a B2 zaoferuje tylko owoce. W Excelu 365 i 2021 drugą listę buduje formuła FILTRUJ (FILTER): =FILTRUJ(E2:E8;D2:D8=A2) zwraca pozycje, których kategoria zgadza się z A2, a lista rozwijana w B2 używa tego rozlania jako źródła.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Category | Item | Category | Item | Items | ||
| 2 | Fruit | Pear | Fruit | Apple | Apple | ||
| 3 | Fruit | Pear | Pear | ||||
| 4 | Vegetable | Carrot | Kiwi | ||||
| 5 | Vegetable | Leek | |||||
| 6 | Bakery | Bread | |||||
| 7 | Fruit | Kiwi | |||||
| 8 | Bakery | Bagel |
Przy Fruit w A2 komórka G2 rozlewa Apple, Pear i Kiwi i to są wybory w B2. Wybierz Bakery w A2: G2 zmieni się na Bread i Bagel. B2 nadal pokazuje Pear, dopóki nie wybierzesz ponownie, bo lista rozwijana nigdy nie zmienia wartości, która już jest w komórce. W Excelu źródłem dla B2 jest =$G$2#.
W starszych wersjach Excela klasyczny sposób używa ADR.POŚR (INDIRECT) i nazwanych zakresów:
- Umieść pozycje każdej kategorii w osobnej kolumnie, z nazwą kategorii jako nagłówkiem: Fruit w jednej kolumnie, Vegetable w następnej.
- Zaznacz każdą kolumnę pozycji i nazwij ją jak jej kategorię w Polu nazwy (na lewo od paska formuły):
Fruit,Vegetable,Bakery. - Daj komórce A2 listę rozwijaną ze źródłem
Fruit;Vegetable;Bakery. - Daj komórce B2 listę rozwijaną ze źródłem
=ADR.POŚR(A2). ADR.POŚR zamienia tekst z A2 w odwołanie do zakresu o tej nazwie.
Nazwy muszą dokładnie odpowiadać tekstowi kategorii i nie mogą zawierać spacji (użyj Dairy_Products albo w źródle =ADR.POŚR(PODSTAW(A2;" ";"_"))). Więcej o tej funkcji na stronie ADR.POŚR.
Wyszukiwanie wartości wybranej pozycji
Lista rozwijana często jest wejściem formularza zamówienia albo oferty: użytkownik wybiera produkt, a wyszukiwanie wpisuje jego cenę.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Product | Price | ||
| 2 | Order | Pear | Apple | $1.20 | ||
| 3 | Pear | $1.50 | ||||
| 4 | Carrot | $0.80 | ||||
| 5 | Bread | $2.40 | ||||
| 6 | Milk | $1.10 |
Twoja kolej: Zwróć w C2 cenę produktu wybranego w B2 z tabeli w E:F.
Przy wybranym Pear odpowiedź to $1.50. Wybierz inny produkt w B2, a cena się dostosuje. =X.WYSZUKAJ(B2;E2:E6;F2:F6) też działa; argumenty opisuje strona WYSZUKAJ.PIONOWO (VLOOKUP).
Kolorowanie komórki według wybranej pozycji
Aby pokolorować komórkę według tego, co wybrano (na zielono dla Done, na czerwono dla Late), dodaj do tych samych komórek regułę formatowania warunkowego: zaznacz B2:B6, przejdź do Narzędzia główne > Formatowanie warunkowe > Reguły wyróżniania komórek > Równe, wpisz Late i wybierz format. Aby pokolorować cały wiersz, zaznacz A2:B6 i użyj Nowa reguła > Użyj formuły do określenia komórek, które należy sformatować z =$B2="Late".
| A | B | |
|---|---|---|
| 1 | Task | Status |
| 2 | Quote | Done |
| 3 | Invoice | Late |
| 4 | Order | Open |
| 5 | Report | Late |
| 6 | Survey | Done |
B3 i B5 są wyróżnione. Wybierz Late w B4, a też zostanie wyróżniona; wybierz Done w B3, a wyróżnienie zniknie. Reguły opisuje szczegółowo strona o formatowaniu warunkowym.
Dlaczego lista rozwijana nie działa
- W Dane > Poprawność danych odznaczone jest Rozwinięcie w komórce. Lista nadal ogranicza wpisy, ale nie ma strzałki.
- Strzałka pokazuje się tylko na zaznaczonej komórce. Nic w siatce nie oznacza innych komórek z listą; aby je znaleźć, użyj Narzędzia główne > Znajdź i zaznacz > Poprawność danych.
- Zakres źródłowy ma puste komórki, więc lista pokazuje puste wiersze. Zaznacz tylko wypełnione komórki albo użyj rozlanego źródła (
=$G$2#), które nie ma pustych pozycji. - Pozycje wpisane ze złym separatorem:
North;Southw Excelu, który używa przecinków, staje się jedną pozycją o nazwieNorth;South. - Lista rozwijana przechowuje jedną wartość. Wybranie drugiej pozycji zastępuje pierwszą; wybór kilku pozycji w jednej komórce wymaga makra VBA.
Aby skopiować listę rozwijaną do innych komórek bez kopiowania wartości, skopiuj komórkę, a potem użyj Narzędzia główne > Wklej > Wklej specjalnie > Sprawdzanie poprawności. Aby ją usunąć, zaznacz komórki i wybierz Dane > Poprawność danych > Wyczyść wszystko.
Najczęściej zadawane pytania
Jak zrobić listę rozwijaną w Excelu?
Zaznacz komórki, przejdź do Dane > Poprawność danych, ustaw Dozwolone na Lista, w polu Źródło wpisz pozycje oddzielone średnikami (North;South;East) albo zaznacz zakres, który je zawiera (=$F$2:$F$5), i naciśnij OK.
Jak edytować listę rozwijaną w Excelu?
Zaznacz komórkę z listą, otwórz Dane > Poprawność danych i zmień pole Źródło. Zaznacz Zastosuj te zmiany do wszystkich innych komórek z tymi samymi ustawieniami, żeby zaktualizować każdą kopię. Jeśli źródłem jest zakres, edycja komórek tego zakresu zmienia listę bez otwierania okna.
Jak usunąć listę rozwijaną w Excelu?
Zaznacz komórki, przejdź do Dane > Poprawność danych, kliknij Wyczyść wszystko, a potem OK. Wybrane już wartości zostają w komórkach; znikają tylko strzałka i ograniczenie.
Jak zrobić listę rozwijaną z innego arkusza?
Wpisz w polu Źródło odwołanie z nazwą arkusza: =Lists!$A$2:$A$6 albo przy aktywnym polu Źródło kliknij drugi arkusz i zaznacz zakres. Działa też nazwany zakres (Formuły > Definiuj nazwę): =Regions.
Jak zrobić listę rozwijaną, która aktualizuje się automatycznie?
Wskaż jej rozlaną formułę: wpisz =SORTUJ(UNIKATOWE(FILTRUJ(B2:B100;B2:B100<>""))) w komórce pomocniczej, na przykład H2, i użyj =$H$2# jako Źródła. Nowe wartości w kolumnie B pojawią się wtedy na liście od razu, a FILTRUJ nie wpuszcza na nią pustych wierszy. Wymaga to Excela 365 albo 2021.
