Note: The other languages of the website are Google-translated. Back to English
Zaloguj Się  \/ 
x
or
x
Zarejestruj się  \/ 
x

or

Jak stworzyć dynamiczną walidację danych i automatycznie rozszerzyć listę rozwijaną?

Zwykle podczas tworzenia listy rozwijanej w arkuszu, lista rozwijana nie zostanie zmieniona wraz z dynamicznym wprowadzaniem nowych danych, w takim przypadku należy zmodyfikować dane źródłowe, aby ręcznie rozszerzyć listę. W tym artykule przedstawię, jak dynamicznie sprawdzać poprawność danych i automatycznie rozszerzać listę rozwijaną po wprowadzeniu nowych danych.

Utwórz dynamiczną weryfikację danych i automatycznie rozszerz listę, tworząc tabelę

Utwórz dynamiczną walidację danych i automatycznie rozszerz listę rozwijaną o Definiowanie nazwy zakresu


strzałka niebieski prawy bąbelek Utwórz dynamiczną weryfikację danych i automatycznie rozszerz listę, tworząc tabelę

Aby utworzyć dynamiczną listę sprawdzania poprawności danych, możesz najpierw utworzyć tabelę, a następnie zastosować funkcję Walidacja danych. Wykonaj następujące czynności:

Utwórz format tabeli dla listy danych źródłowych:

1. Wybierz listę danych, której chcesz użyć jako danych źródłowych dla listy rozwijanej, a następnie kliknij wstawka > Stół, w wyskoczył Utwórz tabelę okno dialogowe, sprawdź Moja tabela ma nagłówki jeśli twoja lista danych ma nagłówki, zobacz zrzut ekranu:

dynamiczna walidacja danych doc 1

2. Następnie kliknij OK przycisk, tabela została utworzona, zobacz zrzut ekranu:

dynamiczna walidacja danych doc 2

Zdefiniuj nazwę zakresu dla tabeli:

3. Następnie wybierz utworzoną tabelę, ale ignorując nagłówek, i wpisz nazwę tej tabeli w Imię i nazwisko pudełko i naciśnij wchodzić klucz, patrz zrzut ekranu:

dynamiczna walidacja danych doc 3

Utwórz listę dynamicznej weryfikacji danych:

4. Po zdefiniowaniu nazwy tabeli, a następnie wybierz komórki, do których chcesz wstawić listę rozwijaną i kliknij Statystyki i dane > Walidacja danych > Walidacja danychzobacz zrzut ekranu:

dynamiczna walidacja danych doc 4

5. I w Walidacja danych okno dialogowe w obszarze Ustawienia kartę, wybierz Lista z dopuszczać a następnie wprowadź następującą formułę: = Lista nazw (Lista imion to nazwa utworzona dla tabeli w kroku 3) do pliku Źródło pole tekstowe, patrz zrzut ekranu:

dynamiczna walidacja danych doc 5

6. Następnie kliknij OK przycisk, lista rozwijana została wstawiona do wybranych komórek, od teraz, po wprowadzeniu nowej nazwy do listy danych źródłowych, zostanie ona również automatycznie dodana do rozwijanej listy, patrz zrzut ekranu:

dynamiczna walidacja danych doc 6


strzałka niebieski prawy bąbelek Utwórz dynamiczną walidację danych i automatycznie rozszerz listę rozwijaną o Definiowanie nazwy zakresu

Oprócz tworzenia tabeli możesz również zakończyć to zadanie, definiując nazwę zakresu za pomocą formuły, wykonaj następujące czynności:

1, Kliknij Wzory > Zdefiniuj nazwęzobacz zrzut ekranu:

dynamiczna walidacja danych doc 7

2, w Nowe imie w oknie dialogowym określ potrzebną nazwę jako Lista nazw, a następnie wpisz następującą formułę: = OFFSET (Sheet1! $ A $ 2,0,0, COUNTA (Sheet1! $ A: $ A), 1) do Odnosi się do pole tekstowe, patrz zrzut ekranu:

dynamiczna walidacja danych doc 8

Uwagi: W powyższym wzorze, Sheet1 czy nazwa arkusza zawiera listę danych, A2 to pierwsza komórka na liście danych, Odp .: A to kolumna zawierająca listę danych.

3, Kliknij OK przycisk, a następnie przejdź do Walidacja danych okno dialogowe, klikając Statystyki i dane > Walidacja danych > Walidacja danych, W Walidacja danych dialog, wybierz Lista z dopuszczać a następnie wprowadź następującą formułę: = Lista nazw (Lista imion to nazwa utworzona w kroku 2) do pliku Źródło pole tekstowe, patrz zrzut ekranu:

dynamiczna walidacja danych doc 9

4. A następnie kliknij OK, teraz, gdy wprowadzisz nową nazwę do listy danych, lista rozwijana zostanie automatycznie rozszerzona. Zobacz zrzut ekranu:

dynamiczna walidacja danych doc 10


Najlepsze narzędzia biurowe

Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%

  • Ponowne użycie: Szybko włóż złożone wzory, wykresy i wszystko, czego używałeś wcześniej; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny bez utraty danych; Podziel zawartość komórek; Połącz zduplikowane wiersze / kolumny... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Ponad 300 zaawansowanych funkcji. Obsługuje Office / Excel 2007-2019 i 365. Obsługuje wszystkie języki. Łatwe wdrażanie w przedsiębiorstwie lub organizacji. Pełne funkcje 30-dniowy bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy.
kte tab 201905

Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszą każdego dnia!
officetab dół
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Bartek · 2 years ago
    Thank you very much, it is very powerful and simple solution at the same time.
  • To post as a guest, your comment is unpublished.
    Nico · 2 years ago
    thanks for this. Really helpful
  • To post as a guest, your comment is unpublished.
    Tom · 2 years ago
    Do you know how to create a dynamic range using drop boxes to expand and contract the data range to be graphed (without deleting any data or hiding it)? I've only seen one person do this. https://youtu.be/sHfWRb2yUrM
    Unfortunately I need to do this on a mac.
  • To post as a guest, your comment is unpublished.
    Adam · 2 years ago
    Thansk for the info - just what I was looking for!!
  • To post as a guest, your comment is unpublished.
    Alex The Great · 2 years ago
    @Alex The Great Solution was to put name of the sheet, e.g. MyLists, rather than Sheet1 in Step 2
  • To post as a guest, your comment is unpublished.
    Alex The Great · 2 years ago
    I get "The source currently evaluates to an error" on Step 4 of "Create Dynamic Data Validation And Auto Extend The Drop Down By Defining Range Name"
  • To post as a guest, your comment is unpublished.
    Christopher · 3 years ago
    I LOVE YOU!
  • To post as a guest, your comment is unpublished.
    geoff · 3 years ago
    I find it is more simple to use a Table than write this whole Offset function. However since a Table cannot be used directly as a data validation source, I have to create a Name that refers to the Table[Column], and point the data validation list to the Name.