Dynamiczne pole wyszukiwania filtra programu Excel (wyodrębnij dane podczas pisania)

Filtr Excel to jedna z najczęściej używanych funkcji podczas pracy z danymi. W tym poście na blogu pokażę, jak utworzyć pole wyszukiwania dynamicznego filtra programu Excel, tak aby filtrować dane na podstawie tego, co wpiszesz w polu wyszukiwania.

Coś, jak pokazano poniżej:

Jest to podwójna funkcjonalność – możesz wybrać nazwę kraju z listy rozwijanej lub możesz ręcznie wprowadzić dane w polu wyszukiwania, a wyświetli ono wszystkie pasujące rekordy. Na przykład, gdy wpiszesz „I”, otrzymasz wszystkie nazwy krajów z alfabetem I w nim.

Obejrzyj wideo - tworzenie dynamicznego pola wyszukiwania filtra programu Excel

Tworzenie dynamicznego pola wyszukiwania filtra Excel

Ten dynamiczny filtr programu Excel można utworzyć w 3 krokach:

1. Uzyskanie unikalnej listy przedmiotów (w tym przypadku krajów). Będzie to wykorzystane przy tworzeniu listy rozwijanej.
2. Tworzenie pola wyszukiwania. Tutaj użyłem Combo Box (kontrolka ActiveX).
3. Ustawianie danych. Tutaj użyłbym trzech kolumn pomocniczych z formułami, aby wyodrębnić pasujące dane.

Oto jak wyglądają surowe dane:

PRZYDATNA WSKAZÓWKA: Prawie zawsze dobrym pomysłem jest przekonwertowanie danych do tabeli Excela. Możesz to zrobić, zaznaczając dowolną komórkę w zestawie danych i używając skrótu klawiaturowego Control + T.

Krok 1 - Uzyskanie unikalnej listy przedmiotów

1. Wybierz wszystkie kraje i wklej je do nowego arkusza roboczego.
2. Wybierz listę krajów -> Przejdź do danych -> Usuń duplikaty.
3. W oknie dialogowym Usuń duplikaty wybierz kolumnę, w której masz listę i kliknij OK. Spowoduje to usunięcie duplikatów i zapewni unikalną listę, jak pokazano poniżej:
4. Dodatkowym krokiem jest utworzenie nazwanego zakresu dla tej unikalnej listy. Aby to zrobić:
   • Przejdź do zakładki Formuła -> Zdefiniuj nazwę
   • W oknie dialogowym Zdefiniuj nazwę:
     • Nazwa: Lista krajów
     • Zakres: skoroszyt
     • Odnosi się do: =UniqueList!$A$2:$A$9 (Mam listę na osobnej karcie o nazwie UniqueList w A2:A9. Możesz odwoływać się do miejsca, w którym znajduje się Twoja unikatowa lista)

UWAGA: Jeśli używasz metody „Usuń duplikaty” i rozszerzysz swoje dane, aby dodać więcej rekordów i nowe kraje, będziesz musiał powtórzyć ten krok ponownie. Ewentualnie możesz również stworzyć formułę, która sprawi, że ten proces będzie dynamiczny.

Krok 2 - tworzenie pola wyszukiwania dynamicznego filtra programu Excel

Aby ta technika działała, musielibyśmy utworzyć „Pole wyszukiwania” i połączyć je z komórką.

Możemy użyć Combo Box w Excelu, aby utworzyć ten filtr pola wyszukiwania. W ten sposób za każdym razem, gdy wprowadzisz cokolwiek w polu kombi, zostanie to również odzwierciedlone w komórce w czasie rzeczywistym (jak pokazano poniżej).

Oto kroki, aby to zrobić:

1. Przejdź do zakładki Deweloper -> Kontrolki -> Wstaw -> Kontrolki ActiveX -> Pole kombi (kontrolki ActiveX).
   • Jeśli nie masz widocznej karty Deweloper, oto kroki, aby ją włączyć.
2. Kliknij w dowolnym miejscu arkusza. Wstawi Combo Box.
3. Kliknij prawym przyciskiem myszy Combo Box i wybierz Właściwości.
4. W oknie Właściwości wprowadź następujące zmiany:
   • Połączona komórka: K2 (możesz wybrać dowolną komórkę, w której chcesz, aby pokazywała wartości wejściowe. Będziemy używać tej komórki podczas ustawiania danych).
   • ListFillRange: CountryList (jest to nazwany zakres, który utworzyliśmy w kroku 1. Spowoduje to wyświetlenie wszystkich krajów z listy rozwijanej).
   • MatchEntry: 2-fmMatchEntryNone (zapewnia to, że słowo nie zostanie automatycznie uzupełnione podczas pisania)
5. Po wybraniu pola kombi przejdź do zakładki Deweloper -> Kontrolki -> kliknij Tryb projektowania (pozwala to wyjść z trybu projektowania i teraz możesz wpisać cokolwiek w polu kombi. Teraz wszystko, co wpiszesz, zostanie odzwierciedlone w komórce K2 w czasie rzeczywistym)

Krok 3 - Ustawianie danych

Na koniec łączymy wszystko kolumnami pomocniczymi. Do filtrowania danych używam tutaj trzech kolumn pomocniczych.

Kolumna pomocnika 1: Wprowadź numer seryjny dla wszystkich rekordów (w tym przypadku 20). W tym celu można użyć formuły ROWS().

Kolumna pomocnika 2: W kolumnie pomocniczej 2 sprawdzamy, czy tekst wprowadzony w polu wyszukiwania pasuje do tekstu w komórkach w kolumnie kraju.

Można to zrobić za pomocą kombinacji funkcji JEŻELI, CZY.LICZBA i SZUKAJ.

Oto wzór:

=JEŻELI(CZY.LICZBA(SZUKAJ($K$2;D4));E4;"")

Ta formuła wyszuka zawartość w polu wyszukiwania (które jest połączone z komórką K2) w komórce, która ma nazwę kraju.

Jeśli istnieje dopasowanie, ta formuła zwraca numer wiersza, w przeciwnym razie zwraca spację. Na przykład, jeśli pole kombi ma wartość „US”, wszystkie rekordy z krajem jako „US” będą miały numer wiersza, a reszta będzie pusta („”)

Kolumna pomocnika 3: W kolumnie pomocniczej 3 musimy zebrać wszystkie numery wierszy z kolumny pomocniczej 2 ułożone razem. W tym celu możemy użyć kombinacji formuł IFERROR i SMALL. Oto wzór:

=JEŻELIBŁĄD(MAŁA($F$4:$F$23;E4);"")

Ta formuła łączy razem wszystkie pasujące numery wierszy. Na przykład, jeśli Combo Box ma wartość US, wszystkie numery wierszy z „US” zostaną ułożone razem.

Teraz, gdy mamy ułożone razem numery wierszy, wystarczy wyodrębnić dane z tych numerów wierszy. Można to łatwo zrobić za pomocą formuły indeksu (wstaw tę formułę w miejscu, w którym chcesz wyodrębnić dane. Skopiuj ją w komórce w lewym górnym rogu, gdzie chcesz wyodrębnić dane, a następnie przeciągnij ją w dół i w prawo).

=JEŻELIBŁĄD(INDEKS($B$4:$D$23;$G4;KOLUMNY($I$3:I3));"")

Ta formuła składa się z 2 części:
INDEKS - To wyodrębnia dane na podstawie numeru wiersza.
JEŻELIBŁĄD - Zwraca puste, gdy nie ma danych.

Oto migawka tego, co w końcu otrzymujesz:

Combo Box to zarówno rozwijane menu, jak i pole wyszukiwania. Możesz ukryć oryginalne dane i kolumny pomocnicze, aby wyświetlić tylko przefiltrowane rekordy. Możesz także mieć surowe dane i kolumny pomocnicze w innym arkuszu i utworzyć ten dynamiczny filtr programu Excel w innym arkuszu.

Bądź kreatywny! Wypróbuj kilka odmian

Możesz spróbować dostosować go do swoich wymagań. Możesz utworzyć wiele filtrów programu Excel zamiast jednego. Na przykład możesz chcieć filtrować rekordy, w których przedstawiciel handlowy to Mike, a kraj to Japonia. Można to zrobić dokładnie według tych samych kroków, z pewną modyfikacją formuły w kolumnach pomocniczych.

Inną odmianą może być filtrowanie danych zaczynających się od znaków wprowadzonych w polu kombi. Na przykład, gdy wpiszesz „I”, możesz chcieć wyodrębnić kraje zaczynające się od I (w porównaniu z obecną konstrukcją, w której otrzymałeś również Singapur i Filipiny, ponieważ zawiera alfabet I).

Jak zawsze większość moich artykułów jest inspirowana pytaniami/odpowiedziami moich czytelników. Bardzo chciałbym poznać Twoją opinię i uczyć się od Ciebie. Zostaw swoje przemyślenia w sekcji komentarzy.

Uwaga: Jeśli korzystasz z usługi Office 365, możesz użyć funkcji FILTR, aby szybko filtrować dane podczas pisania. To łatwiejsze niż metoda pokazana w tym samouczku.