Enamiku Exceli kasutajate jaoks tuleb sõna "andmete filtreerimine" pähe ainult tavaline klassikaline vahekaardilt pärit filter. Andmed – filter (Andmed — filter):
Selline filter on kahtlemata tuttav asi ja enamikul juhtudel sobib. Siiski on olukordi, kus peate filtreerima korraga mitmes veerus suure hulga keerukate tingimuste järgi. Tavaline filter siin pole eriti mugav ja tahaks midagi võimsamat. Selline tööriist võiks olla täiustatud filter, eriti väikese “viiliga viimistlemisega” (traditsiooni järgi).
Alus
Alustamiseks sisestage andmetabeli kohale mõned tühjad read ja kopeerige sinna tabeli päis – see on tingimustega vahemik (selguse huvides kollaselt esile tõstetud):
Kollaste lahtrite ja algse tabeli vahel peab olema vähemalt üks tühi rida.
Kollastesse lahtritesse peate sisestama kriteeriumid (tingimused), mille järgi filtreerimine toimub. Näiteks kui peate III kvartalis Moskva “Auchanis” valima banaanid, näevad tingimused välja järgmised:
Filtreerimiseks valige lähteandmetega vahemikus mis tahes lahter, avage vahekaart kuupäev Ja klõpsake nuppu Lisaks (Andmed – täpsem). Avanevas aknas peaks olema juba automaatselt sisestatud andmetega vahemik ja meil jääb vaid määrata tingimuste vahemik, st A1:I2:
Pange tähele, et tingimuste vahemikku ei saa eraldada “veerisega”, st te ei saa valida ekstra tühje kollaseid ridu, kuna tingimuste vahemikus tühja lahtrit tajub Excel kui kriteeriumi puudumist ja tervet tühja. rida nõudena kuvada kõik andmed valimatult.
Lüliti Kopeerige tulemus teise kohta võimaldab filtreerida nimekirja mitte just sellel lehel (nagu tavalise filtri puhul), vaid laadida valitud read teise vahemikku, mis tuleb siis väljale määrata Pange tulemus vahemikku. Sel juhul me seda funktsiooni ei kasuta, vaid lahkume Filtriloend on paigas ja kliki OK. Valitud read kuvatakse lehel:
Makro lisamine
"Noh, kus on siin mugavus?" küsid ja sul on õigus. Peate mitte ainult oma kätega kollastesse lahtritesse tingimusi sisestama, vaid ka avama dialoogiboksi, sisestama sinna vahemikud, vajutama OK. Kurb, nõustun! Aga “kõik muutub, kui nad tulevad ©” – makrod!
Täiustatud filtriga töötamist saab oluliselt kiirendada ja lihtsustada lihtsa makro abil, mis käivitab tingimuste sisestamisel, st mis tahes kollase lahtri muutmisel, automaatselt täiustatud filtri. Paremklõpsake aktiivse lehe vahekaarti ja valige käsk Lähtetekst (Lähtekood). Kopeerige ja kleepige avanevas aknas järgmine kood:
Private Sub Worksheet_Change(ByVal Target as Range) Kui ei lõiku(sihtmärk, vahemik("A2:I5")) ei ole midagi, siis on viga Jätka järgmise ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=RlaceFilterInria, Crite :=Range("A1").CurrentRegion End If End Sub See protseduur käivitub automaatselt, kui praegusel töölehe lahtrit muudetakse. Kui muudetud lahtri aadress langeb kollasesse vahemikku (A2:I5), siis see makro eemaldab kõik filtrid (kui neid on) ja rakendab laiendatud filtri uuesti A7-ga algava lähteandmete tabelisse ehk kõik filtreeritakse koheselt, kohe pärast järgmise tingimuse sisestamist:
Nii et kõik on palju parem, eks? 🙂
Keeruliste päringute rakendamine
Nüüd, kui kõike filtreeritakse käigu pealt, saame nüanssidesse veidi süveneda ja keerukamate päringute mehhanismid täpsemas filtris lahti võtta. Lisaks täpsete vastete sisestamisele saate ligikaudse otsingu rakendamiseks kasutada mitmesuguseid metamärke (* ja ?) ja matemaatilisi ebavõrdsuse märke. Iseloomu korral pole tähtsust. Selguse huvides olen kõik võimalikud valikud kokku võtnud tabelis:
| Kriteerium | Tulemus |
| gr* või gr | kõik lahtrid algavad tähega GrIe Grkõrv, Grapfruut, Granat ja nii edasi |
| = sibul | kõik lahtrid täpselt ja ainult sõnaga Vibust täpne vaste |
| *liv* või *liv | sisaldavad rakud Liv kuidas alla joonida, st ОLivet, Livep, VastavaltLiv ja nii edasi |
| =p*v | sõnad algavad П ja lõpetades В ie Пesimeneв, Пeeterв ja nii edasi |
| a*s | sõnad algavad А ja sisaldab veelgi СIe Аnahkaсin, Аnanaс, Asai ja nii edasi |
| =*s | sõnadega lõppevad sõnad С |
| =???? | kõik lahtrid, mille tekst on 4 tähemärki (tähed või numbrid, sh tühikud) |
| =m??????n | kõik lahtrid, mille tekst algab 8 tähemärgist М ja lõpetades НIe Мandariн, Мmureн ja nii edasi |
| =*n??a | kõik sõnad, mis lõppevad А, kus on 4. täht lõpust НIe Laiusнikа, Vastavaltнozа ja nii edasi |
| >=e | kõik sõnad algavad Э, Ю or Я |
| <>*o* | kõik sõnad, mis ei sisalda tähti О |
| <>*vich | kõik sõnad peale sõnadega lõppevad sõnad HIV (näiteks filtreerige naisi keskmise nime järgi) |
| = | kõik tühjad lahtrid |
| <> | kõik mittetühjad lahtrid |
| > = 5000 | kõik lahtrid, mille väärtus on suurem või võrdne 5000-ga |
| 5 või =5 | kõik lahtrid väärtusega 5 |
| >=3 | kõik lahtrid, mille kuupäev on hilisem kui 18. märts 2013 (kaasa arvatud) |
Peened punktid:
- Märk * tähendab mis tahes arvu märke ja ? - ükskõik milline tegelane.
- Teksti- ja numbripäringute töötlemise loogika on veidi erinev. Nii et näiteks tingimuslahter numbriga 5 ei tähenda kõigi viiega algavate numbrite otsimist, vaid tingimuslahter tähega B on võrdne B*-ga, st otsib mis tahes teksti, mis algab tähega B.
- Kui tekstipäring ei alga = märgiga, võite mõtteliselt selle lõppu panna *.
- Kuupäevad tuleb sisestada USA formaadis kuu-päev-aasta ja läbi murdosa (isegi kui teil on Exceli ja piirkondlikud sätted).
Loogilised ühendused JA-VÕI
Tingimusi, mis on kirjutatud erinevatesse lahtritesse, kuid samale reale, loetakse loogilise operaatori poolt omavahel seotuks И (JA):
Need. filterbanaanid mulle kolmandal veerandil, täpselt Moskvas ja samal ajal Auchanist.
Kui teil on vaja siduda tingimused loogilise operaatoriga OR (VÕI), siis tuleb need lihtsalt erinevatele ridadele sisestada. Näiteks kui meil on vaja leida kõik manager Volina tellimused Moskva virsikute ja kõik sibulate tellimused kolmandas kvartalis Samarast, siis saab seda määrata järgmistel tingimustel:
Kui teil on vaja ühele veerule kehtestada kaks või enam tingimust, saate lihtsalt kriteeriumivahemikus veeru päise dubleerida ja sisestada selle alla teise, kolmanda jne. tingimustele. Näiteks saate valida kõik tehingud märtsist maini:
Üldiselt tuleb täiustatud filter pärast “failiga viimistlemist” üsna korraliku tööriistana välja, kohati mitte halvem kui klassikaline automaatfilter.
- Superfilter makrodel
- Mis on makrod, kuhu ja kuidas Visual Basicus makrokoodi sisestada
- Nutikad tabelid Microsoft Excelis