Avanceret filter og noget magi

For langt de fleste Excel-brugere, når ordet "datafiltrering" dukker op i deres hoved, er det kun det sædvanlige klassiske filter fra fanen Data – Filter (Data — Filter):

Sådan et filter er uden tvivl en velkendt ting, og i de fleste tilfælde vil det gøre det. Der er dog situationer, hvor du skal filtrere efter et stort antal komplekse forhold i flere kolonner på én gang. Det sædvanlige filter her er ikke særlig praktisk, og jeg vil have noget mere kraftfuldt. Sådan et værktøj kunne være avanceret filter, især med lidt "afslutning med en fil" (ifølge traditionen).

Basis

For at komme i gang skal du indsætte et par tomme linjer over din datatabel og kopiere tabeloverskriften dertil – dette vil være et område med betingelser (markeret med gult for tydelighedens skyld):

Der skal være mindst én tom linje mellem de gule celler og den oprindelige tabel.

Det er i de gule celler, du skal indtaste de kriterier (betingelser), som filtreringen derefter vil blive udført efter. For eksempel, hvis du har brug for at vælge bananer i Moskva "Auchan" i III kvartal, så vil betingelserne se sådan ud:

For at filtrere skal du vælge en celle i området med kildedataene, åbne fanen data Og klik på Derudover (Data — Avanceret). I vinduet, der åbnes, skulle et område med data allerede automatisk indtastes, og vi skal kun angive rækken af ​​betingelser, dvs. A1:I2:

Bemærk venligst, at rækken af ​​betingelser ikke kan tildeles "med en margen", dvs. du kan ikke vælge ekstra tomme gule linjer, fordi en tom celle i rækken af ​​betingelser af Excel opfattes som fravær af et kriterium, og en hel tom celle linje som en anmodning om at vise alle data vilkårligt.

Kontakt Kopier resultatet til et andet sted giver dig mulighed for at filtrere listen ikke lige der på dette ark (som med et almindeligt filter), men at fjerne de valgte rækker i et andet område, som derefter skal angives i feltet Sæt resultatet i rækkevidde. I dette tilfælde bruger vi ikke denne funktion, vi forlader Filterliste på plads og klik OK. De valgte rækker vil blive vist på arket:

Tilføjelse af en makro

"Nå, hvor er bekvemmeligheden her?" du spørger, og du vil have ret. Ikke kun skal du indtaste betingelser i de gule celler med dine hænder, men også åbne en dialogboks, indtaste områder der, trykke OK. Trist, jeg er enig! Men "alt ændrer sig, når de kommer ©" - makroer!

Arbejdet med et avanceret filter kan accelereres og forenkles kraftigt ved hjælp af en simpel makro, der automatisk vil køre det avancerede filter, når betingelser er indtastet, dvs. at ændre enhver gul celle. Højreklik på fanen på det aktuelle ark, og vælg kommandoen Kildetekst (Kildekode). Kopier og indsæt følgende kode i vinduet, der åbnes:

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

Denne procedure kører automatisk, når en celle på det aktuelle regneark ændres. Hvis adressen på den ændrede celle falder inden for det gule område (A2:I5), fjerner denne makro alle filtre (hvis nogen) og genanvender det udvidede filter på kildedatatabellen startende med A7, dvs. alt vil blive filtreret øjeblikkeligt, med det samme efter at have indtastet den næste betingelse:

Så alt er meget bedre, ikke? 🙂

Implementering af komplekse forespørgsler

Nu hvor alt bliver filtreret i farten, kan vi gå lidt dybere ned i nuancerne og skille mekanismerne fra mere komplekse forespørgsler ad i det avancerede filter. Ud over at indtaste eksakte matches kan du bruge forskellige jokertegn (* og ?) og matematiske ulighedstegn under en række forhold til at implementere en omtrentlig søgning. Karakterens store og små bogstaver er ligegyldige. For klarhedens skyld har jeg opsummeret alle de mulige muligheder i en tabel:

Subtile pointer:

• * tegnet betyder et vilkårligt antal af alle tegn, og ? – et hvilket som helst tegn.
• Logikken i behandlingen af ​​tekst og numeriske forespørgsler er lidt anderledes. Så f.eks. betyder en betingelsescelle med tallet 5 ikke at søge efter alle tal, der begynder med fem, men en betingelsescelle med bogstavet B er lig med B*, dvs. vil lede efter enhver tekst, der begynder med bogstavet B.
• Hvis tekstforespørgslen ikke begynder med = tegnet, så kan du mentalt sætte * til sidst.
• Datoer skal indtastes i det amerikanske format måned-dag-år og gennem en brøkdel (selvom du har Excel og regionale indstillinger).

Logiske forbindelser OG-ELLER

Betingelser skrevet i forskellige celler, men i samme linje, anses for at være forbundet med en logisk operator И (OG):

De der. filtrere bananer for mig i tredje kvartal, netop i Moskva og samtidig fra Auchan.

Hvis du skal forbinde betingelser med en logisk operator OR (OR), så skal de bare indtastes på forskellige linjer. For eksempel, hvis vi har brug for at finde alle ordrer fra manager Volina til Moskva ferskner og alle ordrer på løg i tredje kvartal i Samara, så kan dette specificeres i en række forhold som følger:

Hvis du skal pålægge to eller flere betingelser på én kolonne, så kan du blot duplikere kolonneoverskriften i kriterieområdet og indtaste den anden, tredje osv. under den. vilkår. Så for eksempel kan du vælge alle transaktioner fra marts til maj:

Generelt, efter at have "afsluttet med en fil", kommer et avanceret filter til at være et ganske anstændigt værktøj, nogle steder ikke værre end et klassisk autofilter.

• Superfilter på makroer
• Hvad er makroer, hvor og hvordan indsætter man makrokode i Visual Basic
• Smarte tabeller i Microsoft Excel