Tworzenie list rozwijanych

Lista rozwijana dodaje do komórki małą strzałkę, która otwiera menu dozwolonych opcji. Użytkownicy wybierają opcję zamiast ją wpisywać.

To najbardziej przyjazna forma sprawdzania poprawności danych:

• Brak literówek — opcje są wcześniej zatwierdzone.
• Jednolita pisownia w całej kolumnie.
• Szybsze wprowadzanie powtarzających się wartości, takich jak statusy lub regiony.

W tle lista rozwijana jest po prostu regułą sprawdzania poprawności typu Lista.

Najprostsza lista rozwijana korzysta z wartości wpisanych bezpośrednio w regule. W oknie dialogowym sprawdzania poprawności danych w programie Excel wybierz Zezwalaj: Lista, a w polu Źródło wpisz:

Yes,No,Maybe

Elementy oddzielaj przecinkami. W Google Sheets wybierz Lista rozwijana i wpisz każdą opcję osobno.

• To dobre rozwiązanie w przypadku małych, rzadko zmienianych list.
• Wada: aby edytować listę, trzeba ponownie otworzyć regułę.

W przypadku dłuższych lub zmieniających się list wskaż jako źródło zakres komórek. Wpisz opcje w jednej kolumnie, na przykład w zakresie F2:F6, a następnie ustaw źródło sprawdzania poprawności na:

=$F$2:$F$6

Od tej pory edytowanie komórek w kolumnie F natychmiast aktualizuje listę rozwijaną.

• Używaj odwołań bezwzględnych, aby źródło pozostało niezmienne.
• Jeśli chcesz, przechowuj komórki z opcjami na uporządkowanej karcie pomocniczej.

W tym miejscu nazwane zakresy i sprawdzanie poprawności danych doskonale się uzupełniają. Nadaj zakresowi opcji nazwę RegionList, a następnie ustaw jako źródło sprawdzania poprawności po prostu:

=RegionList

Lista rozwijana staje się teraz czytelna i łatwa w utrzymaniu. Każdy, kto sprawdza regułę, widzi znaczącą nazwę zamiast nieczytelnego adresu.

• Działa tak samo w Excel i Google Sheets.
• Nazwa i lista rozwijana pozostają zsynchronizowane.

Wyobraź sobie rejestr zadań. Na karcie pomocniczej wpisz w zakresie A1:A4 statusy: Otwarte, W toku, Zablokowane, Gotowe. Nadaj temu zakresowi nazwę StatusList.

Zaznacz kolumnę stanu, otwórz funkcję sprawdzania poprawności danych, wybierz Lista i ustaw źródło na =StatusList.

Każda komórka statusu oferuje teraz te same cztery uporządkowane opcje. Raporty i formuły COUNTIF zliczające poszczególne statusy nigdy nie pominą wpisu z błędną pisownią.

Jeśli dodasz nową opcję do listy źródłowej, stały zakres, taki jak =$F$2:$F$6, nie będzie jej zawierać. Dwa rozwiązania pozwalają rozwijać listę rozwijaną:

• Przekształć źródło w programie Excel w Tabelę i nadaj nazwę kolumnie — tabele rozszerzają się automatycznie.
• Możesz też zdefiniować dynamiczny nazwany zakres za pomocą funkcji rozlewającej, takiej jak =A2# w nowszych wersjach programu Excel.

W Google Sheets wskazanie jako źródła całej kolumny, na przykład F2:F, uwzględni przyszłe dodatki.

Zależna lista rozwijana pokazuje różne opcje w zależności od zawartości innej komórki. Po wybraniu kraju w jednej komórce lista rozwijana miast pokazuje tylko miasta z tego kraju.

Klasyczny trik w Excelu polega na nadaniu każdej podliście nazwy odpowiadającej kategorii, a następnie użyciu funkcji INDIRECT jako źródła:

=INDIRECT(A2)

Jeśli A2 zawiera France, a nazwany zakres France zawiera listę miast, lista rozwijana dostosuje się automatycznie. To rozwiązanie jest zaawansowane, ale bardzo skuteczne.

Domyślnie reguła Lista nadal pozwala użytkownikom ręcznie wpisywać wartości. Można to kontrolować:

• W programie Excel ustawienie opcji Alert o błędzie na Zatrzymaj odrzuca wszystko, czego nie ma na liście.
• Jeśli zezwolisz na Ostrzeżenie, użytkownicy mogą ominąć listę rozwijaną.
• W Google Sheets wybierz Odrzuć dane wejściowe, aby ściśle wymuszać korzystanie z listy.

W przypadku przejrzystych raportów wybierz opcję restrykcyjną, aby można było wprowadzać wyłącznie wartości znajdujące się na liście.

Mała strzałka pojawia się tylko po zaznaczeniu komórki i tylko wtedy, gdy zaznaczono opcję Lista rozwijana w komórce (Excel) lub włączono styl listy rozwijanej (Sheets).

• Jeśli nie widzisz strzałki, ponownie otwórz regułę i włącz opcję listy rozwijanej w komórce.
• Sheets pozwala wybrać między elementem ze strzałką a zwykłym sprawdzaniem poprawności danych.

To ustawienie wpływa wyłącznie na sposób prezentacji; podstawowa reguła dozwolonych wartości pozostaje taka sama w obu przypadkach.

Ponieważ lista rozwijana korzysta z formuły =RegionList, jej utrzymanie jest proste:

• Dodawaj regiony do komórek nazwanego zakresu lub je z nich usuwaj.
• Jeśli zmienił się rozmiar zakresu, zaktualizuj nazwany zakres w Menedżerze nazw (albo użyj tabeli, aby uniknąć tego kroku).
• Istniejące komórki zachowują swoje wartości, nawet jeśli później zmienisz listę.

Jedno nazwane źródło zasila każdą odwołującą się do niego listę rozwijaną — zmień je raz, a aktualizacja nastąpi wszędzie.

Listy rozwijane stają się jeszcze bardziej przydatne w połączeniu z formułami wyszukującymi. Wybierz region z listy rozwijanej w komórce A2, a następnie wyszukaj jego sprzedaż:

=XLOOKUP(A2,RegionList,SalesList)

Ponieważ lista rozwijana gwarantuje, że komórka A2 zawsze zawiera prawidłowy region, wyszukiwanie nigdy nie zakończy się niepowodzeniem z powodu literówki.

• Lista rozwijana kontroluje dane wejściowe.
• Wyszukiwanie reaguje na wybór.

To połączenie jest podstawą interaktywnych raportów sterowanych formułami.

Utworzono nazwany zakres RegionList zawierający opcje. Jak powinno wyglądać źródło reguły sprawdzania poprawności typu Lista, aby utworzyć na jego podstawie listę rozwijaną?

Nauczyli się Państwo tworzyć listy rozwijane, które pomagają utrzymać porządek w danych:

• Używaj reguły sprawdzania poprawności typu Lista z elementami wpisanymi ręcznie, zakresem komórek lub nazwanym zakresem.
• Używaj formuły =RegionList jako źródła listy rozwijanej, aby zapewnić czytelność i łatwe utrzymanie.
• Spraw, aby listy rozszerzały się za pomocą tabel lub źródeł obejmujących całe kolumny.
• Używaj alertów błędów typu Zatrzymaj, aby wymuszać korzystanie wyłącznie z wartości znajdujących się na liście.

Ukończyli Państwo lekcję Nazwane zakresy i sprawdzanie poprawności danych — potrafią Państwo teraz tworzyć czytelniejsze formuły oraz kontrolowane i niezawodne dane wejściowe.