Tworzenie list rozwijanych w programie Excel usprawnia wprowadzanie danych i minimalizuje błędy. Jednak w przypadku większych zbiorów danych przewijanie długich list staje się kłopotliwe. Czy nie byłoby łatwiej po prostu wpisać i szybko zlokalizować swój przedmiot? A "lista rozwijana z możliwością przeszukiwania” oferuje tę wygodę. Ten przewodnik przeprowadzi Cię przez cztery metody konfiguracji takiej listy w programie Excel.

• Lista rozwijana z możliwością przeszukiwania w programie Excel 365
• Utwórz listę rozwijaną z możliwością wyszukiwania (dla programu Excel 2019 i nowszych wersji)
• Łatwe tworzenie przeszukiwalnej listy rozwijanej (dla wszystkich wersji programu Excel)
• Utwórz listę rozwijaną z możliwością przeszukiwania za pomocą pola kombi i VBA (bardziej złożone)

Wideo

Lista rozwijana z możliwością przeszukiwania w programie Excel 365

W programie Excel 365 do list rozwijanych sprawdzania poprawności danych wprowadzono długo oczekiwaną funkcję: możliwość wyszukiwania na liście. Dzięki funkcji wyszukiwania użytkownicy mogą szybko lokalizować i wybierać elementy w bardziej efektywny sposób. Po wstawieniu listy rozwijanej w zwykły sposób wystarczy kliknąć komórkę z listą rozwijaną i zacząć pisać. Lista zostanie natychmiast przefiltrowana w celu dopasowania do wpisanego tekstu.

W tym przypadku piszę San w komórce, a lista rozwijana odfiltruje miasta zaczynające się od wyszukiwanego hasła San, Takie jak San Francisco i San Diego. Następnie możesz wybrać wynik za pomocą myszki lub użyć klawiszy strzałek i nacisnąć Enter.

Utwórz listę rozwijaną z możliwością wyszukiwania (dla programu Excel 2019 i nowszych wersji)

Jeśli używasz programu Excel 2019 lub nowszego, metody opisanej w tej sekcji można również użyć, aby umożliwić przeszukiwanie listy rozwijanej w programie Excel.

Zakładając, że utworzyłeś listę rozwijaną w komórce A2 Arkusza 2 (obrazek po prawej) przy użyciu danych z zakresu A2:A8 Arkusza 1 (obrazek po lewej stronie), wykonaj poniższe kroki, aby umożliwić przeszukiwanie listy.

Krok 1. Utwórz kolumnę pomocniczą zawierającą listę wyszukiwanych elementów

Tutaj potrzebujemy kolumny pomocniczej, aby wyświetlić listę elementów pasujących do danych źródłowych. W tym przypadku utworzę kolumnę pomocniczą w kolumna D of Sheet1.

1. Wybierz pierwszą komórkę D1 w kolumnie D i wprowadź nagłówek kolumny, np. „Wyniki wyszukiwania” w tym przypadku.
2. Wprowadź następującą formułę do komórki D2 i naciśnij Wchodzę.

   =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")

   

Krok 2: Skonfiguruj ponownie listę rozwijaną

1. Wybierz komórkę listy rozwijanej (w tym przypadku wybieram komórkę A2 w Arkuszu 2), a następnie przejdź do opcji Wybierz Dane > Walidacja danych > Walidacja danych.
   
2. W Walidacja danych oknie dialogowym, należy skonfigurować w następujący sposób.
   1. Pod Ustawienia Kliknij kartę przycisk w Źródło pudełko.
      
   2. Połączenia Walidacja danych okno dialogowe przekieruje do Arkusza1, wybierz komórkę (np. D2) zawierającą formułę z kroku 1, dodaj # symbol i kliknij Zamknij przycisk.
      
   3. Idź do Alert o błędzie kartę, odznacz pole Pokaż alert o błędzie po wprowadzeniu nieprawidłowych danych pole wyboru i na koniec kliknij przycisk OK przycisk, aby zapisać zmiany.
      

Wynik

Można teraz przeszukiwać listę rozwijaną w komórce A2 arkusza 2. Wpisz tekst w komórce, kliknij strzałkę rozwijaną, aby rozwinąć listę rozwijaną, a lista zostanie natychmiast przefiltrowana w celu dopasowania do wpisanego tekstu.

Łatwe tworzenie przeszukiwalnej listy rozwijanej (dla wszystkich wersji programu Excel)

Biorąc pod uwagę różne ograniczenia powyższych metod, oto bardzo skuteczne narzędzie dla Ciebie - Kutools dla programu Excel's Umożliwia przeszukiwanie listy rozwijanej, automatyczne wyskakujące okienkofunkcja. Ta funkcja jest dostępna we wszystkich wersjach programu Excel i umożliwia łatwe wyszukiwanie żądanego elementu na liście rozwijanej za pomocą prostej konfiguracji.

Po pobieranie i instalowanie Kutools dla Excela, Wybierz Kutools > Lista rozwijana > Umożliwia przeszukiwanie listy rozwijanej, automatyczne wyskakujące okienko aby włączyć tę funkcję. w Udostępnij listę rozwijaną do przeszukiwania w oknie dialogowym, musisz:

1. Wybierz zakres zawierający listy rozwijane, które należy ustawić jako listy rozwijane z możliwością przeszukiwania.
2. Kliknij OK aby zakończyć ustawienia.
   

Wynik

Po kliknięciu komórki listy rozwijanej w określonym zakresie po prawej stronie pojawia się pole listy. Wpisz tekst, aby natychmiast przefiltrować listę, a następnie wybierz element lub użyj klawiszy strzałek i naciśnij Wchodzę aby dodać go do komórki.

Utwórz listę rozwijaną z możliwością przeszukiwania za pomocą pola kombi i VBA (bardziej złożone)

Jeśli chcesz po prostu utworzyć listę rozwijaną z możliwością przeszukiwania, bez określania konkretnego typu listy rozwijanej. W tej sekcji przedstawiono alternatywne podejście: użycie pola kombi z kodem VBA do wykonania zadania.

Załóżmy, że masz listę nazw krajów w kolumnie A, jak pokazano na poniższym zrzucie ekranu, a teraz chcesz ich użyć jako danych źródłowych list rozwijanych wyszukiwania, możesz wykonać następujące czynności.

Musisz wstawić pole kombi zamiast listy rozwijanej sprawdzania poprawności danych w arkuszu.

1. Jeśli Deweloper karta nie wyświetla się na wstążce, możesz włączyć Deweloper w następujący sposób.
   1. W programie Excel 2010 lub nowszym kliknij filet > Opcje. I w Opcje programu Excel okno dialogowe, kliknij Dostosuj Wstążkę w lewym okienku. Przejdź do pola listy Dostosuj wstążkę, zaznacz Deweloper a następnie kliknij OK przycisk. Zobacz zrzut ekranu:
      
   2. W programie Excel 2007 kliknij Biurowe przycisk> Opcje programu Excel, w Opcje programu Excel okno dialogowe, kliknij Popularny w lewym okienku zaznacz plik Pokaż kartę Deweloper na Wstążce i na koniec kliknij OK przycisk.
      
2. Po pokazaniu Deweloper tab, kliknij Deweloper > wstawka > Pole kombi.
   
3. Narysuj pole kombi w arkuszu, kliknij je prawym przyciskiem myszy, a następnie wybierz Właściwości z menu po kliknięciu prawym przyciskiem myszy.
   
4. W Właściwości w oknie dialogowym, musisz:
   1. Wybierz Fałszywy Autowybór słów pole;
   2. Określ komórkę w pliku Połączona komórka pole. W tym przypadku wpisujemy A12;
   3. Wybierz 2-fmMatchEntryBrak DopasujWpis pole;
   4. Rodzaj Nieruchomości Lista rozwijana do ListaWypełnij Zakres pole;
   5. Zamknij Właściwości Okno dialogowe. Zobacz zrzut ekranu:
      
5. Teraz wyłącz tryb projektowania, klikając Deweloper > Tryb projektowania.
6. Wybierz pustą komórkę, np. C2, wprowadź poniższą formułę i naciśnij Wchodzę. Przeciągają jego uchwyt autouzupełniania do komórki C9, aby automatycznie wypełnić komórki tą samą formułą. Zobacz zrzut ekranu:

   =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))

   
   Uwagi:

   1. 12 $ A $ XNUMX to komórka, którą określiłeś jako Połączona komórka w kroku 4;
   2. Po wykonaniu powyższych kroków możesz teraz przetestować: wpisz literę C w polu kombi, a zobaczysz, że komórki formuły odwołujące się do komórek zawierających znak C są wypełnione cyfrą 1.
7. Wybierz komórkę D2, wprowadź poniższą formułę i naciśnij Wchodzę. Następnie przeciągnij jego uchwyt autouzupełniania w dół do komórki D9.

   =IF(C2=1,COUNTIF($C$2:C2,1),"")

   
8. Wybierz komórkę E2, wprowadź poniższą formułę i naciśnij Wchodzę. Następnie przeciągnij jego uchwyt autouzupełniania w dół do E9, aby zastosować tę samą formułę.

   =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")

   
9. Teraz musisz utworzyć zakres nazw. Proszę kliknąć Formuła > Zdefiniuj nazwę.
   
10. W Nowe imie okno dialogowe, wpisz Lista rozwijana  Imię wpisz poniższą formułę w polu Odnosi się do a następnie kliknij OK przycisk.

    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    

    
11. Teraz włącz tryb projektowania, klikając Deweloper > Tryb projektowania. Następnie kliknij dwukrotnie pole Combo, aby otworzyć Microsoft Visual Basic for Applications okno.
12. Skopiuj i wklej poniższy kod VBA do edytora kodu.
    
    Kod VBA: umożliwia przeszukiwanie listy rozwijanej

    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub

13. Naciśnij inny + Q klucze do zamknięcia Microsoft Visual Basic for Applications okno.

Od tej chwili, gdy znak zostanie wprowadzony w polu kombi, wykona wyszukiwanie rozmyte, a następnie wyświetli odpowiednie wartości na liście.

Podobne artykuły: