Dinamiskā Excel filtra meklēšanas lodziņš (iegūstiet datus, rakstot)

Excel filtrs ir viena no visbiežāk izmantotajām funkcijām, strādājot ar datiem. Šajā emuāra ziņā es parādīšu, kā izveidot dinamisko Excel filtru meklēšanas lodziņu, lai tas filtrētu datus, pamatojoties uz to, ko ierakstāt meklēšanas lodziņā.

Kaut kas, kā parādīts zemāk:

Tam ir divējāda funkcionalitāte - nolaižamajā sarakstā varat izvēlēties valsts nosaukumu vai arī manuāli ievadīt datus meklēšanas lodziņā, un tas parādīs visus atbilstošos ierakstus. Piemēram, ierakstot “I”, tiek parādīti visi valstu nosaukumi ar alfabētu I.

Skatieties video - dinamiska Excel filtra meklēšanas lodziņa izveide

Dinamiska Excel filtra meklēšanas lodziņa izveide

Šo dinamiskās Excel filtru var izveidot 3 soļos:

1. Unikāla vienumu saraksta iegūšana (šajā gadījumā valstis). Tas tiks izmantots nolaižamās izvēlnes izveidē.
2. Meklēšanas lodziņa izveide. Šeit esmu izmantojis kombinēto lodziņu (ActiveX Control).
3. Datu iestatīšana. Šeit es izmantotu trīs palīgu kolonnas ar formulām, lai iegūtu atbilstošos datus.

Lūk, kā izskatās neapstrādāti dati:

NODERĪGS PADOMS: Gandrīz vienmēr ir laba ideja pārvērst savus datus par Excel tabulu. To var izdarīt, atlasot jebkuru datu kopas šūnu un izmantojot īsinājumtaustiņu Control + T.

1. darbība. Unikāla vienumu saraksta iegūšana

1. Atlasiet visas valstis un ielīmējiet to jaunā darblapā.
2. Atlasiet valstu sarakstu -> Atvērt Dati -> Noņemt dublikātus.
3. Dialoglodziņā Noņemt dublikātus atlasiet kolonnu, kurā ir saraksts, un noklikšķiniet uz Labi. Tādējādi tiks noņemti dublikāti un parādīts unikāls saraksts, kā parādīts zemāk:
4. Vēl viens papildu solis ir izveidot nosaukto diapazonu šim unikālajam sarakstam. Lai to izdarītu:
   • Dodieties uz cilni Formula -> Definēt vārdu
   • Dialoga lodziņā Definēt vārdu:
     • Nosaukums: CountryList
     • Darbības joma: darba grāmata
     • Attiecas uz: = UniqueList! $ A $ 2: $ A $ 9 (saraksts man ir atsevišķā cilnē ar nosaukumu UniqueList A2: A9. Jūs varat atsaukties uz to, kur atrodas jūsu unikālais saraksts)

PIEZĪME. Ja izmantojat metodi “Noņemt dublikātus” un paplašināt savus datus, lai pievienotu vairāk ierakstu un jaunu valstu, šī darbība būs jāatkārto vēlreiz. Varat arī izveidot formulu, lai padarītu šo procesu dinamisku.

2. darbība - dinamiskā Excel filtra meklēšanas lodziņa izveide

Lai šī tehnika darbotos, mums ir jāizveido “meklēšanas lodziņš” un jāsaista tas ar šūnu.

Mēs varam izmantot Excel kombinēto lodziņu, lai izveidotu šo meklēšanas lodziņa filtru. Tādējādi ikreiz, kad kaut ko ievadāt kombinētajā lodziņā, tas tiks atspoguļots arī šūnā reālā laikā (kā parādīts zemāk).

Lai to izdarītu, veiciet tālāk norādītās darbības.

1. Atveriet cilni Izstrādātājs -> Vadīklas -> Ievietot -> ActiveX vadīklas -> Kombinētais lodziņš (ActiveX vadīklas).
   • Ja cilne Izstrādātājs nav redzama, veiciet tālāk norādītās darbības, lai to iespējotu.
2. Noklikšķiniet jebkurā darblapas vietā. Tajā tiks ievietots kombinētais lodziņš.
3. Ar peles labo pogu noklikšķiniet uz kombinētā lodziņa un atlasiet Rekvizīti.
4. Rekvizītu logā veiciet šādas izmaiņas:
   • Saistītā šūna: K2 (varat izvēlēties jebkuru šūnu, kurā vēlaties parādīt ievades vērtības. Mēs izmantosim šo šūnu, iestatot datus).
   • ListFillRange: CountryList (tas ir nosauktais diapazons, kuru mēs izveidojām 1. darbībā. Tas parādītu visas valstis nolaižamajā izvēlnē).
   • MatchEntry: 2-fmMatchEntryNone (tas nodrošina, ka rakstīšanas laikā vārds netiek automātiski aizpildīts)
5. Kad ir atlasīts kombinētais lodziņš, dodieties uz cilni Izstrādātājs -> Vadīklas -> Noklikšķiniet uz Dizaina režīms (tas izkļūst no projektēšanas režīma, un tagad kombinētajā lodziņā varat ierakstīt jebko. Tagad viss, ko ierakstāt, tiks atspoguļots šūnā K2 reālā laikā)

3. solis - datu iestatīšana

Visbeidzot, mēs visu saistām ar palīgu kolonnām. Šeit es izmantoju trīs palīgu kolonnas, lai filtrētu datus.

Palīga 1. sleja: Ievadiet visu ierakstu sērijas numuru (šajā gadījumā - 20). Lai to izdarītu, varat izmantot ROWS () formulu.

Palīga 2. sleja: Palīga 2. slejā mēs pārbaudām, vai meklēšanas lodziņā ievadītais teksts atbilst tekstam valsts slejas šūnās.

To var izdarīt, izmantojot funkciju IF, ISNUMBER un SEARCH kombināciju.

Šeit ir formula:

= IF (ISNUMBER (SEARCH ($ K $ 2, D4)), E4, "")

Šī formula meklēs saturu meklēšanas lodziņā (kas ir saistīts ar šūnu K2) šūnā ar valsts nosaukumu.

Ja ir atbilstība, šī formula atgriež rindas numuru, pretējā gadījumā atgriež tukšu. Piemēram, ja kombinētā lodziņa vērtība ir “US”, visiem ierakstiem, kuru valsts ir “US”, būtu rindas numurs, bet pārējie visi būtu tukši (“”)

Palīga 3. sleja: 3. palīga kolonnā mums ir jāapkopo visi rindu numuri no 2. palīga kolonnas. Lai to izdarītu, mēs varam izmantot kombināciju, ja formulas IFERROR un SMALL. Šeit ir formula:

= IFERROR (MAZS ($ F $ 4: $ F $ 23, E4), "")

Šī formula saliek visus atbilstošos rindu numurus kopā. Piemēram, ja kombinētā lodziņa vērtība ir ASV, visi rindu numuri ar “US” tiek sakrauti kopā.

Tagad, kad rindu numuri ir sakrauti kopā, mums vienkārši jāizņem dati no šīs rindas numura. To var viegli izdarīt, izmantojot indeksa formulu (ievietojiet šo formulu vietā, kur vēlaties iegūt datus. Kopējiet to augšējā kreisajā šūnā, kur vēlaties iegūt datus, un pēc tam velciet to uz leju un pa labi).

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

Šai formulai ir 2 daļas:
INDEKSS - Tādējādi dati tiek iegūti, pamatojoties uz rindas numuru.
IFERROR - Ja datu nav, tas atgriež tukšu.

Šeit ir momentuzņēmums par to, ko jūs beidzot iegūstat:

Kombinētais lodziņš ir nolaižamā izvēlne, kā arī meklēšanas lodziņš. Varat paslēpt sākotnējos datus un palīgu slejas, lai tiktu parādīti tikai filtrētie ieraksti. Neapstrādāto datu un palīgu kolonnas var būt arī citā lapā un izveidot šo dinamisko Excel filtru citā darblapā.

Esiet radošs! Izmēģiniet dažas variācijas

Jūs varat mēģināt to pielāgot savām prasībām. Iespējams, vēlēsities izveidot vairākus Excel filtrus, nevis vienu. Piemēram, iespējams, vēlēsities filtrēt ierakstus, kur pārdošanas pārstāvis ir Maiks, bet valsts - Japāna. To var izdarīt, precīzi izpildot tās pašas darbības, veicot dažas izmaiņas palīga sleju formulā.

Vēl viens variants varētu būt datu filtrēšana, kas sākas ar rakstzīmēm, kuras ievadāt kombinētajā lodziņā. Piemēram, ievadot “es”, iespējams, vēlēsities iegūt valstis, kas sākas ar I (salīdzinājumā ar pašreizējo konstrukciju, kur tā sniegtu arī Singapūru un Filipīnas, jo tajā ir I alfabēts).

Kā vienmēr, lielāko daļu manu rakstu iedvesmojuši manu lasītāju jautājumi/atbildes. Es labprāt saņemtu jūsu atsauksmes un mācītos no jums. Atstājiet savas domas komentāru sadaļā.

Piezīme .: Ja izmantojat Office 365, varat izmantot funkciju FILTER, lai ātri filtrētu datus, rakstot. Tas ir vieglāk nekā šajā apmācībā parādītā metode.