Jak utworzyć listę rozwijaną w programie Excel (jedyny przewodnik, którego potrzebujesz)

Lista rozwijana to doskonały sposób, aby dać użytkownikowi możliwość wyboru z predefiniowanej listy.

Może być używany podczas nakłaniania użytkownika do wypełnienia formularza lub podczas tworzenia interaktywnych kokpitów Excel.

Listy rozwijane są dość powszechne na stronach internetowych/aplikacjach i są bardzo intuicyjne dla użytkownika.

Obejrzyj wideo - tworzenie listy rozwijanej w programie Excel

W tym samouczku dowiesz się, jak utworzyć listę rozwijaną w programie Excel (zajmuje to tylko kilka sekund) wraz ze wszystkimi niesamowitymi rzeczami, które możesz z nią zrobić.

Jak utworzyć listę rozwijaną w programie Excel?

W tej sekcji poznasz dokładne kroki tworzenia listy rozwijanej w programie Excel:

1. Korzystanie z danych z komórek.
2. Ręczne wprowadzanie danych.
3. Korzystanie z formuły PRZESUNIĘCIE.

#1 Używanie danych z komórek

Załóżmy, że masz listę przedmiotów, jak pokazano poniżej:

Oto kroki, aby utworzyć listę rozwijaną programu Excel:

1. Wybierz komórkę, w której chcesz utworzyć listę rozwijaną.
2. Przejdź do Dane -> Narzędzia danych -> Walidacja danych.
3. W oknie dialogowym Sprawdzanie poprawności danych, na karcie Ustawienia, wybierz Lista jako kryteria sprawdzania poprawności.
   • Po wybraniu opcji Lista pojawi się pole źródłowe.
4. W polu źródłowym wprowadź = $ A $ 2: $ A $ 6 lub po prostu kliknij w polu Źródło i wybierz komórki za pomocą myszy i kliknij OK. Spowoduje to wstawienie listy rozwijanej w komórce C2.
   • Upewnij się, że opcja rozwijana w komórce jest zaznaczona (która jest zaznaczona domyślnie). Jeśli ta opcja nie jest zaznaczona, komórka nie wyświetla listy rozwijanej, jednak możesz ręcznie wprowadzić wartości na liście.

Notatka: Jeśli chcesz tworzyć listy rozwijane w wielu komórkach za jednym razem, zaznacz wszystkie komórki, w których chcesz je utworzyć, a następnie wykonaj powyższe kroki. Upewnij się, że odwołania do komórek są bezwzględne (takie jak $A$2), a nie względne (takie jak A2 lub A$2 lub $A2).

#2 Wprowadzając dane ręcznie

W powyższym przykładzie odwołania do komórek są używane w polu Źródło. Możesz również dodawać elementy bezpośrednio, wprowadzając je ręcznie w polu źródłowym.

Załóżmy na przykład, że chcesz wyświetlić dwie opcje, Tak i Nie, na liście rozwijanej w komórce. Oto jak możesz bezpośrednio wpisać go w polu źródła walidacji danych:

• Wybierz komórkę, w której chcesz utworzyć listę rozwijaną (w tym przykładzie komórka C2).
• Przejdź do Dane -> Narzędzia danych -> Walidacja danych.
• W oknie dialogowym Sprawdzanie poprawności danych, na karcie Ustawienia, wybierz Lista jako kryteria sprawdzania poprawności.
  • Po wybraniu opcji Lista pojawi się pole źródłowe.
• W polu źródłowym wpisz Tak, Nie
  • Upewnij się, że opcja rozwijana w komórce jest zaznaczona.
• Kliknij OK.

Spowoduje to utworzenie listy rozwijanej w wybranej komórce. Wszystkie pozycje wymienione w polu źródłowym, oddzielone przecinkami, są wymienione w różnych wierszach w rozwijanym menu.

Wszystkie pozycje wprowadzone w polu źródłowym, oddzielone przecinkiem, są wyświetlane w różnych wierszach na liście rozwijanej.

Notatka: Jeśli chcesz tworzyć listy rozwijane w wielu komórkach za jednym razem, zaznacz wszystkie komórki, w których chcesz je utworzyć, a następnie wykonaj powyższe kroki.

#3 Korzystanie z formuł Excela

Oprócz wybierania komórek i ręcznego wprowadzania danych możesz również użyć formuły w polu źródłowym, aby utworzyć listę rozwijaną programu Excel.

Dowolna formuła zwracająca listę wartości może służyć do tworzenia listy rozwijanej w programie Excel.

Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej:

Oto kroki, aby utworzyć listę rozwijaną programu Excel za pomocą funkcji PRZESUNIĘCIE:

• Wybierz komórkę, w której chcesz utworzyć listę rozwijaną (w tym przykładzie komórka C2).
• Przejdź do Dane -> Narzędzia danych -> Walidacja danych.
• W oknie dialogowym Sprawdzanie poprawności danych, na karcie Ustawienia, wybierz Lista jako kryteria sprawdzania poprawności.
  • Po wybraniu opcji Lista pojawi się pole źródłowe.
• W polu Źródło wprowadź następującą formułę: =OFFSET($A$2,0,0,5)
  • Upewnij się, że opcja rozwijana w komórce jest zaznaczona.
• Kliknij OK.

Spowoduje to utworzenie listy rozwijanej zawierającej wszystkie nazwy owoców (jak pokazano poniżej).

Notatka: Jeśli chcesz utworzyć listę rozwijaną w wielu komórkach za jednym razem, zaznacz wszystkie komórki, w których chcesz ją utworzyć, a następnie wykonaj powyższe kroki. Upewnij się, że odwołania do komórek są bezwzględne (takie jak $A$2), a nie względne (takie jak A2 lub A$2 lub $A2).

Jak działa ta formuła?

W powyższym przypadku do stworzenia listy rozwijanej użyliśmy funkcji PRZESUNIĘCIE. Zwraca listę przedmiotów z ra

Zwraca listę elementów z zakresu A2:A6.

Oto składnia funkcji PRZESUNIĘCIE: = PRZESUNIĘCIE(odwołanie, wiersze, kolumny, [wysokość], [szerokość])

Przyjmuje pięć argumentów, w których podaliśmy referencję jako A2 (punkt początkowy listy). Wiersze/kolumny są określone jako 0, ponieważ nie chcemy przesunąć komórki odniesienia. Wysokość jest określona jako 5, ponieważ lista zawiera pięć elementów.

Teraz, gdy używasz tej formuły, zwraca ona tablicę zawierającą listę pięciu owoców w A2:A6. Zauważ, że jeśli wprowadzisz formułę w komórce, zaznaczysz ją i naciśniesz F9, zobaczysz, że zwraca ona tablicę nazw owoców.

Tworzenie dynamicznej listy rozwijanej w programie Excel (przy użyciu PRZESUNIĘCIA)

Powyższa technika używania formuły do ​​tworzenia listy rozwijanej może zostać rozszerzona, aby utworzyć również dynamiczną listę rozwijaną. Jeśli użyjesz funkcji PRZESUNIĘCIE, jak pokazano powyżej, nawet jeśli dodasz więcej pozycji do listy, lista rozwijana nie zaktualizuje się automatycznie. Będziesz musiał ręcznie zaktualizować go za każdym razem, gdy zmienisz listę.

Oto sposób na uczynienie go dynamicznym (i to nic innego jak drobna poprawka w formule):

• Wybierz komórkę, w której chcesz utworzyć listę rozwijaną (w tym przykładzie komórka C2).
• Przejdź do Dane -> Narzędzia danych -> Walidacja danych.
• W oknie dialogowym Sprawdzanie poprawności danych, na karcie Ustawienia, wybierz Lista jako kryteria sprawdzania poprawności. Po wybraniu opcji Lista pojawi się pole źródłowe.
• W polu źródłowym wprowadź następującą formułę: =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100""))
• Upewnij się, że opcja rozwijana w komórce jest zaznaczona.
• Kliknij OK.

W tej formule zastąpiłem argument 5 argumentem LICZ.JEŻELI($A$2:$A$100'').

Funkcja LICZ.JEŻELI zlicza niepuste komórki w zakresie A2:A100. W związku z tym funkcja PRZESUNIĘCIE dostosowuje się, aby uwzględnić wszystkie niepuste komórki.

Notatka:

• Aby to zadziałało, NIE MOŻE być żadnych pustych komórek pomiędzy wypełnionymi komórkami.
• Jeśli chcesz utworzyć listę rozwijaną w wielu komórkach za jednym razem, zaznacz wszystkie komórki, w których chcesz ją utworzyć, a następnie wykonaj powyższe kroki. Upewnij się, że odwołania do komórek są bezwzględne (takie jak $A$2), a nie względne (takie jak A2 lub A$2 lub $A2).

Kopiuj listy rozwijane wklejania w programie Excel

Możesz skopiować i wkleić komórki z walidacją danych do innych komórek, a także skopiuje walidację danych.

Na przykład, jeśli masz listę rozwijaną w komórce C2 i chcesz ją zastosować również do C3:C6, po prostu skopiuj komórkę C2 i wklej ją w C3:C6. Spowoduje to skopiowanie listy rozwijanej i udostępnienie jej w C3:C6 (wraz z listą rozwijaną skopiuje również formatowanie).

Jeśli chcesz tylko skopiować listę rozwijaną, a nie formatowanie, oto kroki:

• Skopiuj komórkę, która ma listę rozwijaną.
• Wybierz komórki, do których chcesz skopiować listę rozwijaną.
• Przejdź do Home -> Wklej -> Wklej specjalnie.
• W oknie dialogowym Wklej specjalnie wybierz Walidacja w opcjach wklejania.
• Kliknij OK.

Spowoduje to skopiowanie tylko listy rozwijanej, a nie formatowania skopiowanej komórki.

Ostrożność podczas pracy z listą rozwijaną programu Excel

Musisz zachować ostrożność podczas pracy z listami rozwijanymi w programie Excel.

Gdy kopiujesz komórkę (niezawierającą listy rozwijanej) do komórki zawierającej listę rozwijaną, lista rozwijana jest tracona.

Najgorsze jest to, że Excel nie wyświetli żadnego ostrzeżenia ani monitu, aby poinformować użytkownika, że ​​lista rozwijana zostanie nadpisana.

Jak wybrać wszystkie komórki, które mają w sobie listę rozwijaną?

Czasami trudno jest określić, które komórki zawierają listę rozwijaną.

Dlatego warto oznaczyć te komórki, nadając im wyraźną ramkę lub kolor tła.

Zamiast ręcznie sprawdzać wszystkie komórki, istnieje szybki sposób na wybranie wszystkich komórek, które mają w sobie listy rozwijane (lub dowolną regułę sprawdzania poprawności danych).

• Przejdź do strony głównej -> Znajdź i wybierz -> Przejdź do specjalnych.
• W oknie dialogowym Przejdź do specjalnego wybierz opcję Sprawdzanie danych
  • Walidacja danych ma dwie opcje: Wszystkie i To samo. Wszystkie zaznaczyłyby wszystkie komórki, do których zastosowano regułę sprawdzania poprawności danych. To samo spowoduje wybranie tylko tych komórek, które mają taką samą regułę sprawdzania poprawności danych jak komórka aktywna.
• Kliknij OK.

Spowoduje to natychmiastowe wybranie wszystkich komórek, do których zastosowano regułę sprawdzania poprawności danych (dotyczy to również list rozwijanych).

Teraz możesz po prostu sformatować komórki (nadać obramowanie lub kolor tła), aby były widoczne wizualnie i przypadkowo nie skopiowały na nią innej komórki.

Oto kolejna technika Jona Acampory, której możesz użyć, aby zawsze wyświetlać ikonę strzałki w dół. Możesz również zobaczyć kilka sposobów na zrobienie tego w tym wideo autorstwa pana Excela.

Tworzenie zależnej/warunkowej listy rozwijanej programu Excel

Oto wideo na temat tworzenia zależnej listy rozwijanej w programie Excel.

Jeśli wolisz czytać niż oglądać wideo, czytaj dalej.

Czasami możesz mieć więcej niż jedną listę rozwijaną i chcesz, aby elementy wyświetlane w drugiej liście były zależne od tego, co użytkownik wybrał w pierwszej liście rozwijanej.

Są to tak zwane listy rozwijane zależne lub warunkowe.

Poniżej znajduje się przykład rozwijanej listy warunkowej/zależnej:

W powyższym przykładzie, gdy pozycje wymienione w „Rozwijanej 2” są zależne od wyboru dokonanego w „Rozwijanej 1”.

Zobaczmy teraz, jak to stworzyć.

Oto kroki, aby utworzyć zależną / warunkową listę rozwijaną w programie Excel:

• Wybierz komórkę, w której chcesz pierwszą (główną) listę rozwijaną.
• Przejdź do Dane -> Walidacja danych. Spowoduje to otwarcie okna dialogowego sprawdzania poprawności danych.
• W oknie dialogowym sprawdzania poprawności danych, na karcie ustawień wybierz opcję Lista.
• W polu Źródło określ zakres zawierający elementy, które mają być wyświetlane na pierwszej liście rozwijanej.
• Kliknij OK. Spowoduje to utworzenie listy rozwijanej 1.
• Wybierz cały zestaw danych (w tym przykładzie A1:B6).
• Przejdź do Formuły -> Zdefiniowane nazwy -> Utwórz z zaznaczenia (lub możesz użyć skrótu klawiszowego Control + Shift + F3).
• W oknie dialogowym „Utwórz nazwany z zaznaczenia” zaznacz opcję Górny wiersz i odznacz wszystkie pozostałe. W ten sposób tworzy się 2 zakresy nazw („Owoce” i „Warzywa”). Nazwany zakres owoców odnosi się do wszystkich owoców na liście, a nazwany zakres warzyw odnosi się do wszystkich warzyw na liście.
• Kliknij OK.
• Wybierz komórkę, w której chcesz wyświetlić listę rozwijaną Zależne/warunkowe (w tym przykładzie E3).
• Przejdź do Dane -> Walidacja danych.
• W oknie dialogowym Sprawdzanie poprawności danych, na karcie ustawień, upewnij się, że wybrano opcję Lista.
• W polu Źródło wprowadź formułę =ADR.POŚR(D3). Tutaj D3 to komórka zawierająca główne menu rozwijane.
• Kliknij OK.

Teraz, gdy dokonasz wyboru na liście rozwijanej 1, opcje wymienione na liście rozwijanej 2 zostaną automatycznie zaktualizowane.

Pobierz przykładowy plik

Jak to działa? - Warunkowa lista rozwijana (w komórce E3) odwołuje się do = ADR.POŚR(D3). Oznacza to, że po wybraniu „Owoce” w komórce D3 rozwijana lista w E3 odwołuje się do nazwanego zakresu „Owoce” (poprzez funkcję ADR.POŚREDNIA), a zatem wyświetla wszystkie elementy w tej kategorii.

Ważna uwaga podczas pracy z warunkowymi listami rozwijanymi w programie Excel:

• Po dokonaniu wyboru, a następnie zmianie listy rozwijanej rodzica, lista rozwijana zależna nie zmieni się i dlatego będzie błędnym wpisem. Na przykład, jeśli jako kraj wybierzesz Stany Zjednoczone, a następnie jako stan wybierzesz Floryda, a następnie wrócisz i zmienisz kraj na Indie, stan pozostanie Floryda. Oto świetny samouczek Debry dotyczący czyszczenia zależnych (warunkowych) list rozwijanych w programie Excel po zmianie wyboru.
• Jeśli główna kategoria zawiera więcej niż jedno słowo (na przykład „Owoce sezonowe” zamiast „Owoce”), należy użyć formuły =INDIRECT(SUBSTITUTE(D3”, „”_”)), zamiast prosta funkcja ADR.POŚREDNIA pokazana powyżej. Powodem tego jest to, że Excel nie zezwala na spacje w nazwanych zakresach. Dlatego podczas tworzenia nazwanego zakresu przy użyciu więcej niż jednego słowa program Excel automatycznie wstawia podkreślenie między słowami. Tak więc nazwany zakres „Owoce sezonowe” to „Owoce sezonowe”. Użycie funkcji SUBSTITUTE w ramach funkcji ADR.POŚR zapewnia, że ​​spacje są zamienione na podkreślenia.