Utwórz listę rozwijaną programu Excel z sugestiami wyszukiwania

Wszyscy korzystamy z Google w ramach naszej codziennej rutyny. Jedną z jego funkcji jest sugestia wyszukiwania, w której Google działa sprytnie i daje nam listę sugestii podczas pisania.

W tym samouczku dowiesz się, jak utworzyć listę rozwijaną z możliwością przeszukiwania w programie Excel - tj. listę rozwijaną, która będzie wyświetlać pasujące elementy podczas pisania.

Poniżej znajduje się wideo z tego samouczka (w przypadku, gdy wolisz oglądać wideo zamiast czytać tekst).

Przeszukiwalna lista rozwijana w programie Excel

Na potrzeby tego samouczka używam danych z 20 największych krajów według PKB.

Intencją jest utworzenie rozwijanej listy programu Excel z mechanizmem sugestii wyszukiwania, tak aby wyświetlała listę rozwijaną z pasującymi opcjami podczas pisania w pasku wyszukiwania.

Coś, jak pokazano poniżej:

Aby śledzić dalej, pobierz przykładowy plik stąd

Tworzenie listy rozwijanej z możliwością przeszukiwania w programie Excel byłoby procesem składającym się z trzech części:

1. Konfiguracja pola wyszukiwania.
2. Ustawianie danych.
3. Napisanie krótkiego kodu VBA, aby działał.

Krok 1 - Konfiguracja pola wyszukiwania

W tym pierwszym kroku użyję pola kombi i skonfiguruję go tak, aby podczas wpisywania tekst był również odzwierciedlany w komórce w czasie rzeczywistym.

Oto kroki, aby to zrobić:

1. Przejdź do zakładki Deweloper -> Wstaw -> Kontrolki ActiveX -> Pole kombi (kontrolka ActiveX).
   • Istnieje możliwość, że nie znajdziesz karty dewelopera na wstążce. Domyślnie jest ukryty i musi być włączony. Kliknij tutaj, aby dowiedzieć się, jak uzyskać kartę dewelopera na wstążce w programie Excel.
2. Przesuń kursor do obszaru arkusza i kliknij w dowolnym miejscu. Wstawi pole kombi.
3. Kliknij prawym przyciskiem myszy pole kombi i wybierz Właściwości.
4. W oknie dialogowym właściwości wprowadź następujące zmiany:
   • Autowybór słów: Fałszywe
   • Połączona komórka: B3
   • Zakres wypełniania listy: DropDownList (w kroku 2 utworzymy nazwany zakres o tej nazwie
   • Wpis meczu: 2 - fmMatchEntryBrak

(Komórka B3 jest połączona z polem kombi, co oznacza, że ​​wszystko, co wpiszesz w polu kombi, jest wprowadzane w B3)

4. Przejdź do zakładki Deweloper i kliknij Tryb projektowania. Umożliwi to wpisanie tekstu w polu kombi. Ponadto, ponieważ komórka B3 jest połączona z polem kombi, każdy tekst wprowadzony w polu kombi będzie również odzwierciedlany w B3 w czasie rzeczywistym.

Krok 2 - Ustawianie danych

Teraz, gdy pole wyszukiwania jest gotowe, musimy przygotować dane. Chodzi o to, że gdy tylko wpiszesz cokolwiek w polu wyszukiwania, pokazuje tylko te elementy, które zawierają ten tekst.

Aby to zrobić, użyjemy

• Trzy kolumny pomocnicze.
• Jeden dynamiczny nazwany zakres.

Kolumna pomocnika 1

Umieść następującą formułę w komórce F3 i przeciągnij ją dla całej kolumny (F3: F22)

=--CZY.LICZBA(JEŻELI.BŁĄD(SZUKAJ($B$3;E3;1);""))

Ta formuła zwraca 1, gdy tekst w polu kombi znajduje się w nazwie kraju po lewej stronie. Na przykład, jeśli wpiszesz UNI, to tylko wartości dla UniStany Zjednoczone i United Kingdom to 1, a wszystkie pozostałe wartości to 0.

Kolumna pomocnika 2

Umieść następującą formułę w komórce G3 i przeciągnij ją dla całej kolumny (G3:G22)

=JEŻELI(F3=1;LICZ.JEŻELI($F$3:F3;1);"") 

Ta formuła zwraca 1 dla pierwszego wystąpienia, gdy tekst pola kombi odpowiada nazwie kraju, 2 dla drugiego wystąpienia, 3 dla trzeciego i tak dalej. Na przykład, jeśli wpiszesz UNI, komórka G3 wyświetli 1, ponieważ pasuje do Stanów Zjednoczonych, a G9 wyświetli 2, ponieważ pasuje do Wielkiej Brytanii. Pozostałe komórki będą puste.

Kolumna pomocnika 3

Umieść następującą formułę w komórce H3 i przeciągnij ją dla całej kolumny (H3: H22)

=JEŻELI.BŁĄD(INDEKS($E$3:$E$22;DOPASOWANIE(WIERSZE($G$3:G3);$G$3:$G$22,0));"") 

Ta formuła układa wszystkie pasujące nazwy razem bez żadnych pustych komórek między nimi. Na przykład, jeśli wpiszesz UNI, ta kolumna pokaże 2 i 9 razem, a reszta wszystkich komórek będzie pusta.

Tworzenie dynamicznego nazwanego zakresu

Teraz, gdy kolumny pomocnicze są gotowe, musimy utworzyć dynamiczny nazwany zakres. Ten nazwany zakres będzie odnosić się tylko do tych wartości, które pasują do tekstu wprowadzonego w polu kombi. Użyjemy tego dynamicznego nazwanego zakresu, aby pokazać wartości w polu rozwijanym.

Notatka: W kroku 1 wpisaliśmy DropDownList w opcji ListFillRange. Teraz utworzymy nazwany zakres o tej samej nazwie.

Oto kroki, aby go utworzyć:

1. Przejdź do Formuły -> Menedżer nazw.
2. W oknie dialogowym menedżera nazw kliknij Nowy. Otworzy się okno dialogowe Nowa nazwa.
3. W polu Nazwa wpisz DropDownList
4. W polu Odnosi się do pola wprowadź formułę: =$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),1)

Krok 3 - Wprowadzenie kodu VBA do pracy

Jesteśmy prawie na miejscu.

Ostatnią częścią jest napisanie krótkiego kodu VBA. Ten kod sprawia, że ​​rozwijane menu jest dynamiczne, tak że pokazuje pasujące elementy/nazwy podczas wpisywania w polu wyszukiwania.

Aby dodać ten kod do skoroszytu:

1. Kliknij prawym przyciskiem myszy kartę Arkusz i wybierz Wyświetl kod.
2. W oknie VBA skopiuj i wklej następujący kod:

   Prywatne pole podrzędne ComboBox1_Change() ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Napis końcowy

Otóż ​​to!!

Wszystko masz ustawione z własnym paskiem wyszukiwania typu Google, który pokazuje pasujące elementy podczas pisania.

Aby uzyskać lepszy wygląd i styl, możesz zakryć komórkę B3 polem kombi i ukryć wszystkie kolumny pomocnicze. Teraz możesz się trochę pochwalić tą niesamowitą sztuczką Excela.

Aby kontynuować, pobierz plik stąd

Co myślisz? Czy mógłbyś użyć tej listy rozwijanej sugestii wyszukiwania w swojej pracy? Daj mi znać swoje przemyślenia, zostawiając komentarz.

Jeśli podobał Ci się ten samouczek, na pewno chciałbyś również skorzystać z następujących samouczków programu Excel:

• Filtr dynamiczny - wyodrębniaj pasujące dane podczas pisania.
• Wyodrębnij dane na podstawie wyboru z listy rozwijanej.
• Tworzenie zależnych list rozwijanych w programie Excel.
• Kompletny przewodnik po korzystaniu z funkcji WYSZUKAJ.PIONOWO programu Excel.
• Jak dokonać wielu wyborów na liście rozwijanej w programie Excel.
• Jak wstawić i użyć pola wyboru w programie Excel.