Utworzenie pola wyszukiwania w programie Excel zwiększa funkcjonalność arkuszy kalkulacyjnych, ułatwiając filtrowanie i szybki dostęp do określonych danych. W tym przewodniku omówiono kilka metod implementacji pola wyszukiwania, dostosowanych do różnych wersji programu Excel. Niezależnie od tego, czy jesteś początkującym, czy zaawansowanym użytkownikiem, te kroki pomogą Ci skonfigurować dynamiczne pole wyszukiwania przy użyciu takich funkcji, jak funkcja FILTR, formatowanie warunkowe i różne formuły.

• Z łatwością utwórz pole wyszukiwania za pomocą FILTR, funkcja
  (dostępne w programie Excel 2019 i nowszych wersjach, Excel dla Microsoft 365)
• Utwórz pole wyszukiwania za pomocą Formatowanie warunkowe
  (dostępne we wszystkich wersjach Excela)
• Utwórz pole wyszukiwania za pomocą kombinacje formuł
  (dostępne we wszystkich wersjach Excela)

Z łatwością utwórz pole wyszukiwania za pomocą funkcji FILTR

• Ta funkcja automatycznie aktualizuje dane wyjściowe w przypadku zmiany danych.
• Funkcja FILTER może zwrócić dowolną liczbę wyników, od jednego wiersza do tysięcy, w zależności od tego, ile wpisów w zbiorze danych spełnia ustawione kryteria.

Tutaj pokażę Ci, jak używać funkcji FILTR, aby utworzyć pole wyszukiwania w Excelu.

Krok 1: Wstaw pole tekstowe i skonfiguruj właściwości

1. Idź do Deweloper tab, kliknij wstawka > Text Box (kontrolka ActiveX).
   Wskazówka: Jeśli Deweloper nie jest wyświetlana na wstążce, możesz ją włączyć, postępując zgodnie z instrukcjami zawartymi w tym samouczku: Jak wyświetlić / wyświetlić kartę programisty na wstążce programu Excel?
   
2. Kursor zmieni się w krzyżyk, a następnie przeciągnij kursor, aby narysować pole tekstowe w miejscu w arkuszu, w którym chcesz umieścić pole tekstowe. Po narysowaniu pola tekstowego zwolnij przycisk myszy.
   
3. Kliknij prawym przyciskiem myszy pole tekstowe i wybierz Właściwości z menu kontekstowego.
   
4. W Właściwości Połącz pole tekstowe z komórką, wprowadzając odwołanie do komórki w polu Połączona komórka pole. Na przykład wpisując „J2" gwarantuje, że wszelkie dane wprowadzone w polu tekstowym zostaną automatycznie zaktualizowane w komórce J2 i odwrotnie.
   
5. Kliknij Tryb projektowania pod Deweloper aby wyjść z trybu projektowania.
   

Pole tekstowe umożliwia teraz wprowadzanie tekstu.

Krok 2: Zastosuj funkcję FILTR

1. Przed użyciem funkcji FILTR skopiuj oryginalny wiersz nagłówka do nowego obszaru. Tutaj umieszczam wiersz nagłówka pod polem wyszukiwania.
   Wskazówka: takie podejście pozwala użytkownikom wyraźnie zobaczyć wyniki pod tymi samymi nagłówkami kolumn, co oryginalne dane.
   
2. Wybierz komórkę pod pierwszym nagłówkiem (np I5 w tym przykładzie) wprowadź do niego poniższą formułę i naciśnij klawisz Wchodzę aby uzyskać wynik.

   =FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

   
   Jak pokazano na powyższym zrzucie ekranu, ponieważ w polu tekstowym nie ma teraz żadnych danych wejściowych, formuła wyświetla wynik „Nie znaleziono danych„w I5.

Wynik: przetestuj pole wyszukiwania

Przetestujmy teraz pole wyszukiwania. W tym przykładzie, gdy wprowadzę nazwę klienta w polu wyszukiwania, odpowiednie wyniki zostaną natychmiast przefiltrowane i wyświetlone.

Utwórz pole wyszukiwania, korzystając z formatowania warunkowego

Formatowania warunkowego można użyć do wyróżnienia danych pasujących do wyszukiwanego hasła, pośrednio tworząc efekt pola wyszukiwania. Ta metoda nie filtruje danych, ale wizualnie prowadzi do odpowiednich komórek. W tej sekcji dowiesz się, jak utworzyć pole wyszukiwania przy użyciu formatowania warunkowego w programie Excel.

Krok 1: Wstaw pole tekstowe i skonfiguruj właściwości

1. Idź do Deweloper tab, kliknij wstawka > Text Box (kontrolka ActiveX).
   Wskazówka: Jeśli Deweloper nie jest wyświetlana na wstążce, możesz ją włączyć, postępując zgodnie z instrukcjami zawartymi w tym samouczku: Jak wyświetlić / wyświetlić kartę programisty na wstążce programu Excel?
   
2. Kursor zmieni się w krzyżyk, a następnie przeciągnij kursor, aby narysować pole tekstowe w miejscu w arkuszu, w którym chcesz umieścić pole tekstowe. Po narysowaniu pola tekstowego zwolnij przycisk myszy.
   
3. Kliknij prawym przyciskiem myszy pole tekstowe i wybierz Właściwości z menu kontekstowego.
   
4. W Właściwości Połącz pole tekstowe z komórką, wprowadzając odwołanie do komórki w polu Połączona komórka pole. Na przykład wpisując „J3" gwarantuje, że wszelkie dane wprowadzone w polu tekstowym zostaną automatycznie zaktualizowane w komórce J3 i odwrotnie.
   
5. Kliknij Tryb projektowania pod Deweloper aby wyjść z trybu projektowania.
   

Pole tekstowe umożliwia teraz wprowadzanie tekstu.

Krok 2: Zastosuj formatowanie warunkowe do wyszukiwania danych

1. Wybierz cały zakres danych do przeszukania. Tutaj wybieram zakres A3:G279.
2. Pod Strona główna tab, kliknij Formatowanie warunkowe > Nowa zasada.
   
3. W Nowa reguła formatowania Okno dialogowe:
   1. Wybierz Użyj formuły, aby określić, które komórki należy sformatować Wybierz typ reguły opcje.
   2. Wprowadź poniższą formułę do Formatuj wartości tam, gdzie ta formuła jest prawdziwa pudełko.

      =$B3=$J$3

      Tutaj, $ B3 reprezentuje pierwszą komórkę w kolumnie, którą chcesz dopasować do kryteriów wyszukiwania w wybranym zakresie, oraz 3 dolarów to komórka połączona z polem wyszukiwania.
   3. Kliknij utworzony aby określić kolor wypełnienia wyników wyszukiwania.
   4. Kliknij OK przycisk. Zobacz zrzut ekranu:
      

Wynik

Przetestujmy teraz pole wyszukiwania. W tym przykładzie, gdy wprowadzę nazwę klienta w polu wyszukiwania, odpowiednie wiersze zawierające tego klienta w kolumnie B zostaną natychmiast podświetlone określonym kolorem wypełnienia.

Utwórz pole wyszukiwania z kombinacjami formuł

Jeżeli nie korzystasz z najnowszej wersji Excela i wolisz nie tylko podświetlać wierszy, pomocna może okazać się metoda opisana w tej sekcji. Możesz użyć kombinacji formuł programu Excel, aby utworzyć funkcjonalne pole wyszukiwania w dowolnej wersji programu Excel. Wykonaj poniższe czynności.

Krok 1: Utwórz listę unikalnych wartości z kolumny wyszukiwania

1. W tym przypadku zaznaczam i kopiuję zakres B4: B281 do nowego arkusza.
2. Po wklejeniu zakresu do nowego arkusza, pozostaw zaznaczone wklejone dane, przejdź do Dane I wybierz opcję Usuń duplikaty.
   
3. W otwarciu Usuń duplikaty okno dialogowe, kliknij przycisk OK przycisk.
   
4. A Microsoft Excel następnie pojawi się okno z informacją o liczbie usuniętych duplikatów. Kliknij OK.
   
5. Po usunięciu duplikatów należy zaznaczyć na liście wszystkie unikalne wartości z wyłączeniem nagłówka i nadać temu zakresowi nazwę wpisując ją w polu Imię skrzynka. Tutaj nazwałem zakres jako Klient.
   

Krok 2: Wstaw pole kombi i skonfiguruj właściwości

1. Wróć do arkusza zawierającego zbiór danych, który chcesz przeszukać. Idź do Deweloper tab, kliknij wstawka > Pole kombi (formant ActiveX).
   Wskazówka: Jeśli Deweloper nie jest wyświetlana na wstążce, możesz ją włączyć, postępując zgodnie z instrukcjami zawartymi w tym samouczku: Jak wyświetlić / wyświetlić kartę programisty na wstążce programu Excel?
   
2. Kursor zmieni się w krzyżyk, a następnie przeciągnij kursor, aby narysować pole kombi w miejscu w arkuszu, w którym chcesz umieścić pole wyszukiwania. Po narysowaniu pola kombi zwolnij przycisk myszy.
   
3. Kliknij prawym przyciskiem myszy pole kombi i wybierz Właściwości z menu kontekstowego.
   
4. W Właściwości szkło:
   1. Połącz pole kombi z komórką, wprowadzając odwołanie do komórki w polu Połączona komórka pole. Ją piszę”M2".
      Wskazówka: określenie tego pola gwarantuje, że wszelkie dane wprowadzone w polu kombi zostaną automatycznie zaktualizowane w komórce M2 i odwrotnie.
   2. W ListaWypełnij Zakres w polu wprowadź nazwa zakresu określiłeś dla unikalnej listy w kroku 1.
   3. Zmienić DopasujWpis pole do 2 – fmMatchEntryNone.
   4. Zamknij Właściwości szkło.
      
5. Kliknij Tryb projektowania pod Deweloper aby wyjść z trybu projektowania.
   

Możesz teraz wybrać dowolny element z pola kombi lub wpisać tekst, który chcesz wyszukać.

Krok 3: Zastosuj formuły

1. Utwórz trzy kolumny pomocnicze sąsiadujące z oryginalnym zakresem danych. Zobacz zrzut ekranu:
   
2. W celi (H5) pod nagłówkiem pierwszej kolumny pomocniczej wpisz poniższą formułę i naciśnij Wchodzę.

   =ROWS($B$5:B5)

   Tutaj B5 to komórka zawierająca nazwę pierwszego klienta w kolumnie, która ma zostać przeszukana.
   
3. Kliknij dwukrotnie prawy dolny róg komórki z formułą, następna komórka automatycznie wypełni tę samą formułę.
   
4. W celi (I5) pod nagłówkiem drugiej kolumny pomocniczej wprowadź poniższą formułę i naciśnij Wchodzę. Następnie kliknij dwukrotnie prawy dolny róg komórki z formułą, aby automatycznie wypełnić komórki poniżej tą samą formułą.

   =IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")

   Tutaj M2 to komórka połączona z polem kombi.
   
5. W celi (J5) pod nagłówkiem trzeciej kolumny pomocniczej wprowadź poniższą formułę i naciśnij Wchodzę. Następnie kliknij dwukrotnie prawy dolny róg komórki z formułą, aby automatycznie wypełnić komórki poniżej tą samą formułą.

   =IFERROR(SMALL($I$5:$I$281,H5),"") 

   
6. Skopiuj oryginalny wiersz nagłówka do nowego obszaru. Tutaj umieszczam wiersz nagłówka pod polem wyszukiwania.
   
7. Wybierz komórkę pod pierwszym nagłówkiem (np L5 w tym przykładzie) wprowadź do niego poniższą formułę i naciśnij klawisz Enter.

   =IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

   Tutaj A5: G281 to cały zakres danych, który chcesz wyświetlić w komórce wynikowej.
   
8. Wybierz tę komórkę z formułą i przeciągnij Wypełnij uchwyt w prawo, a następnie w dół, aby zastosować formułę do odpowiednich kolumn i wierszy.
   
   Uwagi:

   • Ponieważ w polu wyszukiwania nie ma żadnych danych wejściowych, wyniki formuły będą zawierać surowe dane.
   • Ta metoda nie uwzględnia wielkości liter, co oznacza, że ​​dopasuje tekst niezależnie od tego, czy wpiszesz wielkie, czy małe litery.

Wynik

Przetestujmy teraz pole wyszukiwania. W tym przykładzie, gdy wprowadzę lub wybiorę nazwę klienta w polu kombi, odpowiednie wiersze zawierające tę nazwę klienta w kolumnie B zostaną odfiltrowane i natychmiast wyświetlone w zakresie wyników.

Utworzenie pola wyszukiwania w programie Excel może znacznie poprawić sposób interakcji z danymi, dzięki czemu arkusze kalkulacyjne będą bardziej dynamiczne i przyjazne dla użytkownika. Niezależnie od tego, czy wybierzesz prostotę funkcji FILTR, wizualną pomoc formatowania warunkowego, czy wszechstronność kombinacji formuł, każda metoda zapewnia cenne narzędzia zwiększające możliwości manipulacji danymi. Eksperymentuj z tymi technikami, aby znaleźć najlepszą dla Twoich konkretnych potrzeb i scenariuszy danych. Dla tych, którzy chcą głębiej zagłębić się w możliwości Excela, nasza strona internetowa oferuje mnóstwo samouczków. Tutaj znajdziesz więcej porad i wskazówek dotyczących Excela.

Powiązane artykuły