Gevorderde filter en 'n bietjie magie

Vir die oorgrote meerderheid van Excel-gebruikers, wanneer die woord "datafiltrering" in hul kop opduik, is slegs die gewone klassieke filter van die oortjie Data – Filter (Data – Filter):

So 'n filter is 'n bekende ding, ongetwyfeld, en in die meeste gevalle sal dit doen. Daar is egter situasies wanneer jy deur 'n groot aantal komplekse toestande in verskeie kolomme gelyktydig moet filter. Die gewone filter hier is nie baie gerieflik nie en ek wil iets kragtiger hê. So 'n instrument kan wees gevorderde filter, veral met 'n bietjie "afronding met 'n lêer" (volgens oorlewering).

basis

Om te begin, voeg 'n paar leë reëls bo jou datatabel in en kopieer die tabelopskrif daarheen - dit sal 'n reeks met voorwaardes wees (geel uitgelig vir duidelikheid):

Daar moet ten minste een leë reël tussen die geel selle en die oorspronklike tabel wees.

Dit is in die geel selle wat jy die kriteria (voorwaardes) moet invoer waarvolgens die filter dan uitgevoer sal word. Byvoorbeeld, as jy piesangs in die Moskou "Auchan" in die III kwartaal moet kies, sal die toestande soos volg lyk:

Om te filter, kies enige sel in die reeks met die brondata, maak die oortjie oop data En klik op die Daarbenewens (Data — Gevorderd). In die venster wat oopmaak, moet 'n reeks met data reeds outomaties ingevoer word en ons sal slegs die reeks voorwaardes moet spesifiseer, dws A1:I2:

Neem asseblief kennis dat die reeks toestande nie "met 'n marge" toegeken kan word nie, dit wil sê jy kan nie ekstra leë geel lyne kies nie, want 'n leë sel in die reeks toestande word deur Excel as die afwesigheid van 'n maatstaf beskou, en 'n hele leë lyn as 'n versoek om al die data onoordeelkundig te vertoon.

Skakel Kopieer die resultaat na 'n ander plek sal jou toelaat om die lys nie net daar op hierdie blad te filtreer nie (soos met 'n gewone filter), maar om die geselekteerde rye in 'n ander reeks af te laai, wat dan in die veld gespesifiseer moet word Plaas resultaat in reeks. In hierdie geval gebruik ons ​​nie hierdie funksie nie, ons vertrek Filterlys in plek en klik op OK. Die geselekteerde rye sal op die blad vertoon word:

Voeg 'n makro by

“Wel, waar is die gerief hier?” jy vra en jy sal reg wees. Jy hoef nie net voorwaardes in die geel selle met jou hande in te voer nie, maar maak ook 'n dialoogkassie oop, voer reekse daar in, druk OK. Jammer, ek stem saam! Maar “alles verander wanneer hulle kom ©” – makros!

Werk met 'n gevorderde filter kan baie versnel en vereenvoudig word deur 'n eenvoudige makro te gebruik wat outomaties die gevorderde filter sal laat loop wanneer toestande ingevoer word, maw enige geel sel verander. Regskliek op die oortjie van die huidige blad en kies die opdrag Bronteks (Bronkode). Kopieer en plak die volgende kode in die venster wat oopmaak:

Private Sub Worksheet_Change(ByVal Target As Range) Indien Nie Sny(Target, Range("A2:I5")) Is Niks Dan Fout Hervat Volgende ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterRinPlace, Criteria :=Bereik("A1"). Huidige Streek Einde Indien Einde Sub  

Hierdie prosedure sal outomaties uitgevoer word wanneer enige sel op die huidige werkblad verander word. As die adres van die veranderde sel in die geel reeks val (A2:I5), dan verwyder hierdie makro alle filters (indien enige) en pas die uitgebreide filter weer toe op die brondatatabel wat met A7 begin, maw alles sal onmiddellik, onmiddellik gefiltreer word na die volgende voorwaarde:

So alles is baie beter, reg? 🙂

Implementering van komplekse navrae

Noudat alles dadelik gefiltreer word, kan ons 'n bietjie dieper in die nuanses gaan en die meganismes van meer komplekse navrae in die gevorderde filter uitmekaar haal. Benewens die invoer van presiese passings, kan jy verskeie jokertekens (* en ?) en wiskundige ongelykheidstekens in 'n reeks toestande gebruik om 'n benaderde soektog te implementeer. Die karaktergeval maak nie saak nie. Vir duidelikheid het ek al die moontlike opsies in 'n tabel opgesom:

Subtiele punte:

• Die * teken beteken enige getal van enige karakters, en ? - enige karakter.
• Die logika in die verwerking van teks- en numeriese navrae is effens anders. So, byvoorbeeld, beteken 'n voorwaardesel met die nommer 5 nie om te soek vir alle getalle wat met vyf begin nie, maar 'n voorwaardesel met die letter B is gelyk aan B*, dit wil sê sal soek vir enige teks wat met die letter B begin.
• As die teksnavraag nie met die =-teken begin nie, kan jy verstandelik * aan die einde sit.
• Datums moet in die Amerikaanse formaat maand-dag-jaar en deur 'n breuk ingevoer word (selfs al het jy Excel- en streekinstellings).

Logiese verbindings EN-OF

Voorwaardes wat in verskillende selle geskryf is, maar in dieselfde lyn, word beskou as onderling verbind deur 'n logiese operateur И (EN):

Dié. filter piesangs vir my in die derde kwartaal, juis in Moskou en terselfdertyd van Auchan.

As jy voorwaardes met 'n logiese operateur moet koppel OR (OR), dan moet hulle net in verskillende reëls ingevoer word. As ons byvoorbeeld alle bestellings van bestuurder Volina vir Moskou-perskes en alle bestellings vir uie in die derde kwartaal in Samara moet vind, kan dit in 'n reeks toestande soos volg gespesifiseer word:

As jy twee of meer voorwaardes op een kolom moet stel, dan kan jy eenvoudig die kolomopskrif in die kriteriareeks dupliseer en die tweede, derde, ens. daaronder invoer. terme. So, byvoorbeeld, kan jy alle transaksies van Maart tot Mei kies:

Oor die algemeen, na "afwerking met 'n lêer", blyk 'n gevorderde filter 'n redelike hulpmiddel te wees, op sommige plekke nie erger as 'n klassieke outofilter nie.

• Superfilter op makro's
• Wat is makro's, waar en hoe om makro-kode in Visual Basic in te voeg
• Slim tabelle in Microsoft Excel