Napredni filter in nekaj čarovnije

Veliki večini uporabnikov Excela ob besedi »filtriranje podatkov« pride v glavo le običajni klasični filter iz zavihka Podatki – Filter (Podatki — Filter):

Takšen filter je nedvomno znan in v večini primerov bo zadostoval. Vendar pa obstajajo situacije, ko morate filtrirati po velikem številu kompleksnih pogojev v več stolpcih hkrati. Običajni filter tukaj ni zelo priročen in želim nekaj močnejšega. Takšno orodje bi lahko bilo napredni filter, predvsem z malo “dodelave s pilo” (po tradiciji).

Osnovna

Če želite začeti, vstavite nekaj praznih vrstic nad podatkovno tabelo in tja kopirajte glavo tabele – to bo obseg s pogoji (zaradi jasnosti označeni z rumeno):

Med rumenimi celicami in prvotno tabelo mora biti vsaj ena prazna vrstica.

V rumena polja morate vnesti kriterije (pogoje), po katerih se bo nato izvajalo filtriranje. Na primer, če morate izbrati banane v moskovskem "Auchan" v III četrtletju, bodo pogoji videti tako:

Če želite filtrirati, izberite katero koli celico v obsegu z izvornimi podatki, odprite zavihek datum In kliknite na Poleg tega (Podatki — Napredno). V oknu, ki se odpre, naj bi bil obseg s podatki že samodejno vnesen, določiti pa bomo morali samo obseg pogojev, to je A1:I2:

Upoštevajte, da obsega pogojev ni mogoče dodeliti »z robom«, tj. ne morete izbrati dodatnih praznih rumenih vrstic, ker prazno celico v obsegu pogojev Excel zazna kot odsotnost merila in celotno prazno celico. vrstico kot zahtevo za prikaz vseh podatkov brez razlikovanja.

Preklop Kopirajte rezultat na drugo mesto vam bo omogočilo, da filtrirate seznam ne tam na tem listu (kot pri običajnem filtru), ampak da razložite izbrane vrstice v drug obseg, ki ga boste nato morali določiti v polju Postavite rezultat v obseg. V tem primeru te funkcije ne uporabljamo, pustimo Seznam filtrov na mestu in kliknite OK. Izbrane vrstice bodo prikazane na listu:

Dodajanje makra

"No, kje je tukaj udobje?" vprašate in imeli boste prav. Ne samo, da morate z rokami vnesti pogoje v rumene celice, ampak tudi odpreti pogovorno okno, tam vnesti obsege, pritisniti OK. Žalostno, se strinjam! Toda »vse se spremeni, ko pridejo ©« – makri!

Delo z naprednim filtrom lahko zelo pospešimo in poenostavimo z enostavnim makrom, ki ob vnosu pogojev, torej spremembi katere koli rumene celice, samodejno zažene napredni filter. Z desno miškino tipko kliknite zavihek trenutnega lista in izberite ukaz Izvorno besedilo (izvorna koda). V oknu, ki se odpre, kopirajte in prilepite to kodo:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1").CurrentRegion End If End Sub  

Ta postopek se samodejno izvede, ko se spremeni katera koli celica na trenutnem delovnem listu. Če naslov spremenjene celice pade v rumeno območje (A2:I5), potem ta makro odstrani vse filtre (če obstajajo) in znova uporabi razširjeni filter za tabelo izvornih podatkov, ki se začne z A7, kar pomeni, da bo vse filtrirano takoj, takoj po vnosu naslednjega pogoja:

Torej je vse veliko boljše, kajne? 🙂

Izvajanje kompleksnih poizvedb

Zdaj, ko se vse filtrira sproti, se lahko še malo poglobimo v nianse in razstavimo mehanizme zahtevnejših poizvedb v naprednem filtru. Poleg vnosa natančnih ujemanj lahko uporabite različne nadomestne znake (* in ?) in znake matematične neenakosti v različnih pogojih za izvedbo približnega iskanja. Velika črka ni pomembna. Zaradi jasnosti sem vse možne možnosti povzel v tabeli:

Subtilne točke:

• Znak * pomeni poljubno število poljubnih znakov in ? – kateri koli znak.
• Logika obdelave besedilnih in številskih poizvedb je nekoliko drugačna. Tako na primer pogojna celica s številko 5 ne pomeni iskanja vseh števil, ki se začnejo na pet, pogojna celica s črko B pa je enaka B*, torej bo iskala poljubno besedilo, ki se začne na črko B.
• Če se besedilna poizvedba ne začne z znakom =, lahko na koncu mentalno postavite *.
• Datumi morajo biti vneseni v ameriški obliki mesec-dan-leto in z ulomkom (tudi če imate Excel in regionalne nastavitve).

Logični veznik IN-ALI

Za pogoje, zapisane v različnih celicah, vendar v isti vrstici, se šteje, da so med seboj povezani z logičnim operatorjem И (IN):

Tisti. filter banane zame v tretjem četrtletju, natančno v Moskvi in ​​hkrati iz Auchana.

Če morate pogoje povezati z logičnim operatorjem OR (OR), potem jih je treba samo vnesti v različne vrstice. Na primer, če moramo najti vsa naročila upravitelja Volina za moskovske breskve in vsa naročila za čebulo v tretjem četrtletju v Samari, potem je to mogoče določiti v nizu pogojev, kot sledi:

Če morate enemu stolpcu naložiti dva ali več pogojev, lahko preprosto podvojite glavo stolpca v obsegu meril in pod njim vnesete drugega, tretjega itd. pogoji. Tako lahko na primer izberete vse transakcije od marca do maja:

Na splošno se po "končanju z datoteko" napredni filter izkaže za povsem spodobno orodje, ponekod nič slabše od klasičnega samodejnega filtra.

• Superfilter za makre
• Kaj so makri, kam in kako vstaviti kodo makra v Visual Basic
• Pametne tabele v programu Microsoft Excel