Jak utworzyć dynamiczną listę rozwijaną w programie Excel przy użyciu 4 różnych metod?

W tym artykule dowiemy się, jak tworzyć dynamiczną listę rozwijaną w programie Microsoft Excel.

Jak wiemy funkcja sprawdzania poprawności danych poprawia efektywność wprowadzania danych w programie Excel oraz redukuje pomyłki i błędy pisarskie. Służy do ograniczenia użytkownika do rodzaju danych, które można wprowadzić w zakresie. W przypadku nieprawidłowego wpisu wyświetla komunikat i umożliwia użytkownikowi wprowadzenie danych na podstawie określonego warunku.

Ale dynamiczna lista rozwijana w programie Excel to wygodniejszy sposób wybierania danych bez wprowadzania zmian w źródle. Innymi słowy, powiedz, że zamierzasz często aktualizować listę, którą wybrałeś z listy rozwijanej. I myślisz, że jeśli wprowadzisz jakiekolwiek zmiany na liście, musisz za każdym razem modyfikować walidację danych, aby uzyskać zaktualizowaną listę rozwijaną.

Ale w tym miejscu pojawia się dynamiczne rozwijanie i jest to najlepsza opcja wyboru danych bez wprowadzania jakichkolwiek zmian w walidacji danych. Jest bardzo podobny do normalnego sprawdzania poprawności danych. Jednak po zaktualizowaniu listy dynamiczna lista rozwijana zmienia się, aby uwzględnić tę akcję, podczas gdy zwykła lista rozwijana nie.

Weźmy więc przykład i zrozummy, jak tworzymy dynamiczną listę rozwijaną:-

Mamy listę produktów w kolumnie A i będziemy mieć dynamiczną rozwijaną listę produktów w komórce D9.

Nazwa tabeli z funkcją pośrednią

Najpierw stworzymy tabelę; postępuj zgodnie z instrukcjami podanymi poniżej:-

• Wybierz zakres A8:A16
• Przejdź do zakładki Wstaw, a następnie kliknij Tabela

• Po kliknięciu opcji „Tabela” pojawia się okno tabeli
• Następnie wybierz zakres, dla którego chcemy wstawić tabelę A8:A17
• Kliknij OK

• Teraz klikamy OK
• Widać, że ten zakres został przekonwertowany na tabelę, a nagłówek tej tabeli ma również opcję rozwijania filtrów

Notatka: - Jeśli dodamy jakikolwiek produkt lub przedmiot na dole listy, tabela powiększy się automatycznie o nowe produkty lub pozycje.

Teraz tworzymy dynamiczną listę rozwijaną w komórce D9, wykonaj czynności podane poniżej:-

• Wybierz komórkę D9
• Otwórz okno dialogowe Sprawdzanie poprawności danych, naciskając klawisz ALT+D+L
• Z listy rozwijanej Zezwól wybierz Lista
• A następnie wprowadź tę funkcję =INDIRECT(„Tabela1”) w zakładce źródła

• Kliknij OK

Notatka: - Kiedy klikniemy OK w Excelu, wyskakuje okno z informacją, że coś jest nie tak z danymi wejściowymi. Dzieje się tak, ponieważ program Excel nie akceptuje żadnej samorozwijającej się tabeli bezpośrednio w walidacji danych.

Teraz dodaj nowe produkty na liście produktów.

Na powyższym obrazku widzimy, że na liście rozwijanej pojawia się nowy dodany produkt.

2^NS Przykład:-

W tym przykładzie nauczymy się, jak nadać nazwę tabeli jako nazwę zakresową

Mamy już nazwę tabeli, ale tutaj musimy zdefiniować nazwę tej tabeli, aby uzyskać dynamiczną listę rozwijaną; postępuj zgodnie z instrukcjami podanymi poniżej:-

• Wybierz komórkę D10
• Przejdź do zakresu tabeli i oprócz nagłówka wybieramy zakres od pierwszego produktu do ostatniego produktu
• Przejdź do pola nazwy i wpisz krótką nazwę „zakres stołów”, naciśnij Enter

• Po naciśnięciu enter widzimy, że nic się nie zmieniło w polu nazwy

• Kliknij opcję listy rozwijanej, aby zobaczyć wszystkie dostępne nazwane zakresy
• Na liście rozwijanej możemy zobaczyć również nazwę, którą właśnie zdefiniowaliśmy dla tej tabeli

• Teraz przechodzimy do walidacji danych i w „Źródle” wpisujemy „zakres tabel”

Uwaga: - Jeśli nie pamiętasz, jaką nazwę nadałeś temu zakresowi, możesz nacisnąć klawisz F3, a pojawi się okno z sugestią wszystkich dostępnych nazwanych zakresów.

• Teraz przechodzimy do zakładki „Wprowadź wiadomość” iw tytule wpisujemy „Wybierz produkt”, a następnie w treści wiadomości piszemy „Proszę wybrać produkt z listy”

• Teraz przejdź do zakładki „Ostrzeżenie o błędzie”, a tam w tytule piszemy „Nieprawidłowy produkt”, a w komunikacie o błędzie wpisujemy „Wprowadziłeś zły produkt

• Kliknij OK
• Komórka D10 zawierająca komunikat wejściowy wraz z listą rozwijaną

• Teraz, gdy dodamy dowolny produkt na liście, automatycznie pojawi się on na liście rozwijanej

Ale co się stanie, gdy pominiemy jedną komórkę po ostatniej komórce, a następnie dodamy nowy produkt lub przedmiot? Widać, że tym razem oferta stołów się nie poszerzyła, a tak naprawdę nowo dodany produkt jest w formacie ogólnym. Czy będzie więc wyświetlany na liście rozwijanej, czy nie? Aby to sprawdzić, kiedy przejdziemy do komórki D10 i sprawdzimy listę rozwijaną, zobaczymy tę samą starą listę rozwijaną bez nowego produktu. Dzieje się tak dlatego, że zakres tabeli nie znalazł niczego po ostatniej komórce, a zatem zakres nie został rozszerzony.

3^{r & D} Przykład:-

W kolejnych dwóch metodach dowiemy się, jak możemy uczynić naszą listę rozwijaną bardziej dynamiczną, używając funkcji PRZESUNIĘCIE i LICZBA.

Postępuj zgodnie z instrukcjami podanymi poniżej:-

• Wybierz komórkę D11 i naciśnij ALT + D + L
• Otworzy się okno dialogowe sprawdzania poprawności danych
• Teraz wybierz listę w opcji „Zezwól”
• Następnie w opcji Źródło wprowadź poniższą formułę: –

= PRZESUNIĘCIE ($A 9,0,0; ILE.LICZB ($A:$A);1)

Wyjaśnienie formuły:- Wybraliśmy A9, który jest pierwszym produktem w ofercie, a następnie wpisujemy 0 na 2^NS argument, ponieważ nie chcemy przenosić wiersza z punktu początkowego; potem znowu 0 w 3^{r & D} argument, ponieważ tutaj nie chcemy żadnych zmian w numerze kolumny, jak również od punktu początkowego. Następnie wprowadziliśmy funkcję ILE.NIEPUSTYCH i wybraliśmy całą kolumnę A. Ten argument sprawdzi wysokość w liczbie wierszy, aby zwrócić niepustą liczbę. Rozszerzy zakres po wprowadzeniu jakichkolwiek zmian w zakresie.

A ostatni argument „Szerokość” jest argumentem opcjonalnym. Jest to szerokość w liczbie kolumn. Możemy go pominąć lub na razie wpisać 1 tutaj. Jeśli pominiemy, domyślnie uwzględni szerokość zwróconego zakresu, który podaliśmy w argumencie, a następnie zamkniemy nawiasy.

• Po kliknięciu OK możemy zobaczyć rozwijaną listę w komórce D11
• Pokazuje listę zawierającą puste, a następnie produkty, które dodaliśmy

4^NS Przykład:-

W tym przykładzie użyjemy funkcji do zdefiniowania nazwy.

Aby zdefiniować nazwę zakresu, wykonaj następujące czynności:-

• Naciśnij CTRL + F3, pojawi się okno dialogowe Menedżera nazw
• Kliknij Nowy
• Zdefiniuj nazwę zakresu „ProdName” i wprowadź poniższą formułę:-

=OFFSET('Dynamiczna lista rozwijana z DV'!$A$9,0,0,COUNTA('Dynamiczna lista rozwijana z DV'!$A:$A))

• Kliknij OK
• Otwórz okno sprawdzania poprawności danych, naciskając klawisz Alt + D + L
• Wybierz Lista na liście rozwijanej Zezwól
• Wprowadź =ProdName na karcie Źródło

• Kliknij OK
• Teraz, jeśli dodamy coś na liście, to samo pojawi się na liście

W ten sposób można uzyskać dynamiczną listę dowolnego produktu lub przedmiotu za pomocą różnych metod przy użyciu walidacji danych. To wszystko na teraz. W następnym filmie z tej serii wyjaśnimy, jak utworzyć zależną listę rozwijaną za pomocą różnych metod w programie Excel.

Kliknij łącze wideo, aby szybko zapoznać się z jego użyciem. Subskrybuj nasz nowy kanał i ucz się z nami dalej!

Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku.
Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail