Динамично поле за търсене на филтър на Excel (Извличане на данни, докато пишете)

Excel Filter е една от най -използваните функции, когато работите с данни. В тази публикация в блога ще ви покажа как да създадете поле за търсене на филтър за динамичен Excel, така че да филтрира данните въз основа на това, което въвеждате в полето за търсене.

Нещо, както е показано по -долу:

В това има двойна функционалност - можете да изберете име на държава от падащия списък или можете ръчно да въведете данните в полето за търсене и то ще ви покаже всички съответстващи записи. Например, когато въведете „I“, той ви дава всички имена на държави с азбуката I в него.

Гледайте видео - Създаване на поле за търсене на динамичен филтър на Excel

Създаване на поле за търсене на динамичен филтър на Excel

Този филтър за динамичен Excel може да бъде създаден в 3 стъпки:

1. Получаване на уникален списък с артикули (в този случай държави). Това би било използвано при създаването на падащото меню.
2. Създаване на поле за търсене. Тук използвах Combo Box (ActiveX Control).
3. Настройка на данните. Тук бих използвал три помощни колони с формули, за да извлека съответстващите данни.

Ето как изглеждат необработените данни:

ПОЛЕЗЕН СЪВЕТ: Почти винаги е добра идея да конвертирате данните си в таблица на Excel. Можете да направите това, като изберете произволна клетка в набора от данни и използвате клавишната комбинация Control + T.

Стъпка 1 - Получаване на уникален списък с елементи

1. Изберете всички държави и го поставете в нов работен лист.
2. Изберете списъка с държави -> Отидете на Данни -> Премахване на дубликати.
3. В диалоговия прозорец Премахване на дубликати изберете колоната, в която имате списъка, и щракнете върху OK. Това ще премахне дубликатите и ще ви даде уникален списък, както е показано по -долу:
4. Една допълнителна стъпка е да създадете именован диапазон за този уникален списък. Да го направя:
   • Отидете в раздела Формула -> Определете име
   • В диалоговия прозорец Определяне на име:
     • Име: CountryList
     • Обхват: Работна тетрадка
     • Отнася се до: = UniqueList! $ A $ 2: $ A $ 9 (Имам списъка в отделен раздел, наречен UniqueList в A2: A9. Можете да се обърнете към мястото, където се намира вашият уникален списък)

ЗАБЕЛЕЖКА: Ако използвате метода „Премахване на дубликати“ и разширите данните си, за да добавите още записи и нови държави, ще трябва да повторите тази стъпка отново. Алтернативно, можете също така да формула, за да направите този процес динамичен.

Стъпка 2 - Създаване на полето за търсене на динамичния филтър на Excel

За да работи тази техника, ще трябва да създадем „поле за търсене“ и да я свържем с клетка.

Можем да използваме Combo Box в Excel, за да създадем този филтър на полето за търсене. По този начин, всеки път, когато въведете нещо в Combo Box, това също ще бъде отразено в клетка в реално време (както е показано по-долу).

Ето стъпките за това:

1. Отидете на Раздел за програмисти -> Контроли -> Вмъкване -> ActiveX контроли -> Комбинирано поле (ActiveX контроли).
   • Ако не виждате раздела за програмисти, ето стъпките за активирането му.
2. Щракнете където и да е на работния лист. Той ще постави Combo Box.
3. Щракнете с десния бутон върху Combo Box и изберете Properties.
4. В прозореца Свойства направете следните промени:
   • Свързана клетка: K2 (можете да изберете всяка клетка, където искате да показва входните стойности. Ще използваме тази клетка за настройка на данните).
   • ListFillRange: CountryList (това е именуваният диапазон, който създадохме в Стъпка 1. Това ще покаже всички държави в падащото меню).
   • MatchEntry: 2-fmMatchEntryNone (това гарантира, че дадена дума не се попълва автоматично, докато пишете)
5. При избран Combo Box отидете на Developer Tab -> Controls -> Щракнете върху Design Mode (това ви извежда от режим на проектиране и сега можете да напишете всичко в Combo Box. Сега, каквото и да напишете, ще бъде отразено в клетка K2 в реално време)

Стъпка 3 - Настройване на данните

Накрая свързваме всичко чрез помощни колони. Използвам три помощни колони тук, за да филтрирам данните.

Помощна колона 1: Въведете серийния номер за всички записи (в този случай 20). Можете да използвате формулата ROWS (), за да направите това.

Помощна колона 2: В помощна колона 2 проверяваме дали текстът, въведен в полето за търсене, съвпада с текста в клетките в колоната за държава.

Това може да стане с помощта на комбинация от функции IF, ISNUMBER и SEARCH.

Ето формулата:

= IF (ISNUMBER (ТЪРСЕНЕ ($ K $ 2, D4)), E4, "")

Тази формула ще търси съдържанието в полето за търсене (което е свързано с клетка K2) в клетката с името на държавата.

Ако има съвпадение, тази формула връща номера на реда, в противен случай връща празно. Например, ако комбинираното поле има стойността „САЩ“, всички записи с държава като „САЩ“ ще имат номера на реда, а останалите ще бъдат празни („“)

Помощна колона 3: В помощна колона 3 трябва да съберем всички номера на редовете от помощната колона 2. За да направим това, можем да използваме комбинация IFERROR и SMALL формули. Ето формулата:

= ГРЕШКА (МАЛКА ($ F $ 4: $ F $ 23, E4), "")

Тази формула подрежда всички съвпадащи номера на редове заедно. Например, ако комбинираното поле има стойността US, всички номера на редове с „US“ в него се подреждат заедно.

Сега, когато имаме номерата на редовете, подредени заедно, просто трябва да извлечем данните в тези номера на редове. Това може да стане лесно с помощта на формулата за индекс (поставете тази формула там, където искате да извлечете данните. Копирайте я в горната лява клетка, където искате да извлечете данните, и след това я плъзнете надолу и надясно).

= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")

Тази формула има 2 части:
ИНДЕКС - Това извлича данните въз основа на номера на реда.
ГРЕШКА - Това връща празно, когато няма данни.

Ето моментна снимка на това, което най -накрая получавате:

Комбинираното поле е падащо меню, както и поле за търсене. Можете да скриете оригиналните данни и помощните колони, за да показвате само филтрираните записи. Можете също така да имате необработени данни и помощни колони в друг лист и да създадете този динамичен филтър на Excel в друг работен лист.

Бъдете креативни! Опитайте някои варианти

Можете да опитате и да го персонализирате според вашите изисквания. Може да искате да създадете множество филтри на Excel вместо един. Например, може да искате да филтрирате записи, където търговският представител е Майк, а държавата е Япония. Това може да се направи точно след същите стъпки с известна промяна във формулата в помощни колони.

Друг вариант може да бъде филтрирането на данни, които започват с символите, които въвеждате в комбинираното поле. Например, когато въведете „I“, може да искате да извлечете държави, започващи с I (в сравнение с настоящата конструкция, където тя също би ви дала Сингапур и Филипини, тъй като съдържа азбуката I).

Както винаги, повечето от моите статии са вдъхновени от въпросите/отговорите на моите читатели. Ще се радвам да получа отзивите ви и да се уча от вас. Оставете мислите си в секцията за коментари.

Забележка: В случай, че използвате Office 365, можете да използвате функцията FILTER, за да филтрирате бързо данните, докато пишете. Това е по -лесно от метода, показан в този урок.