Avansert filter og litt magi

For de aller fleste Excel-brukere, når ordet "datafiltrering" kommer opp i hodet, er det bare det vanlige klassiske filteret fra fanen Data – Filter (Data – Filter):

Et slikt filter er en kjent ting, uten tvil, og i de fleste tilfeller vil det gjøre det. Det er imidlertid situasjoner når du trenger å filtrere etter et stort antall komplekse forhold i flere kolonner samtidig. Det vanlige filteret her er ikke veldig praktisk, og jeg vil ha noe kraftigere. Et slikt verktøy kan være avansert filter, spesielt med litt «avslutning med fil» (i følge tradisjonen).

Base

For å komme i gang, sett inn noen tomme linjer over datatabellen og kopier tabelloverskriften dit – dette vil være et område med betingelser (uthevet i gult for klarhetens skyld):

Det må være minst én tom linje mellom de gule cellene og den opprinnelige tabellen.

Det er i de gule cellene du må legge inn kriteriene (betingelsene), som filtreringen deretter skal utføres etter. For eksempel, hvis du trenger å velge bananer i Moskva "Auchan" i III-kvartalet, vil forholdene se slik ut:

For å filtrere, velg en celle i området med kildedataene, åpne fanen Data Og klikk på I tillegg (Data – Avansert). I vinduet som åpnes skal et område med data allerede legges inn automatisk, og vi trenger bare å spesifisere vilkårene, dvs. A1:I2:

Vær oppmerksom på at vilkårsområdet ikke kan tildeles "med en margin", dvs. du kan ikke velge ekstra tomme gule linjer, fordi en tom celle i vilkårsområdet oppfattes av Excel som fravær av et kriterium, og en hel tom celle. linje som en forespørsel om å vise alle dataene tilfeldig.

Bytte om Kopier resultatet til et annet sted vil tillate deg å filtrere listen ikke akkurat der på dette arket (som med et vanlig filter), men å laste ut de valgte radene inn i et annet område, som da må spesifiseres i feltet Sett resultatet i rekkevidde. I dette tilfellet bruker vi ikke denne funksjonen, vi lar være Filterliste på plass og klikk OK. De valgte radene vises på arket:

Legge til en makro

"Vel, hvor er bekvemmeligheten her?" spør du og du vil ha rett. Ikke bare må du legge inn betingelser i de gule cellene med hendene, men også åpne en dialogboks, angi områder der, trykk OK. Trist, jeg er enig! Men "alt endres når de kommer ©" – makroer!

Arbeid med et avansert filter kan akselereres og forenkles betraktelig ved å bruke en enkel makro som automatisk kjører det avanserte filteret når betingelser legges inn, dvs. endre enhver gul celle. Høyreklikk på fanen i gjeldende ark og velg kommandoen Kildetekst (Kildekode). I vinduet som åpnes, kopier og lim inn følgende kode:

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

Denne prosedyren kjøres automatisk når en celle i det gjeldende regnearket endres. Hvis adressen til den endrede cellen faller inn i det gule området (A2:I5), fjerner denne makroen alle filtre (hvis noen) og bruker det utvidede filteret på nytt på kildedatatabellen som starter med A7, dvs. alt vil bli filtrert umiddelbart, umiddelbart etter å ha angitt neste betingelse:

Så alt er mye bedre, ikke sant? 🙂

Implementering av komplekse spørsmål

Nå som alt blir filtrert i farten, kan vi gå litt dypere inn i nyansene og demontere mekanismene til mer komplekse søk i det avanserte filteret. I tillegg til å angi eksakte treff, kan du bruke ulike jokertegn (* og ?) og matematiske ulikhetstegn i en rekke forhold for å implementere et omtrentlig søk. Karaktersaken spiller ingen rolle. For klarhets skyld har jeg oppsummert alle mulige alternativer i en tabell:

Subtile poeng:

• *-tegnet betyr et hvilket som helst antall tegn, og ? – hvilken som helst karakter.
• Logikken i å behandle tekst og numeriske spørringer er litt annerledes. Så for eksempel betyr ikke en betingelsescelle med tallet 5 å søke etter alle tall som begynner med fem, men en betingelsescelle med bokstaven B er lik B*, dvs. vil se etter hvilken som helst tekst som begynner med bokstaven B.
• Hvis tekstspørringen ikke begynner med =-tegnet, kan du mentalt sette * på slutten.
• Datoer må legges inn i amerikansk format måned-dag-år og gjennom en brøkdel (selv om du har Excel og regionale innstillinger).

Logiske koblinger OG-ELLER

Betingelser skrevet i forskjellige celler, men på samme linje, anses å være sammenkoblet av en logisk operatør И (OG):

De. filtrere bananer for meg i tredje kvartal, nettopp i Moskva og samtidig fra Auchan.

Hvis du trenger å koble betingelser med en logisk operatør OR (ELLER), så må de bare legges inn på forskjellige linjer. For eksempel, hvis vi trenger å finne alle bestillinger fra manager Volina for fersken fra Moskva og alle bestillinger på løk i tredje kvartal i Samara, kan dette spesifiseres i en rekke forhold som følger:

Hvis du trenger å pålegge to eller flere betingelser for én kolonne, kan du ganske enkelt duplisere kolonneoverskriften i kriterieområdet og skrive inn den andre, tredje osv. under den. vilkår. Så, for eksempel, kan du velge alle transaksjoner fra mars til mai:

Generelt, etter å ha "fullført med en fil", kommer et avansert filter ut som et ganske anstendig verktøy, noen steder ikke verre enn et klassisk autofilter.

• Superfilter på makroer
• Hva er makroer, hvor og hvordan du setter inn makrokode i Visual Basic
• Smarte tabeller i Microsoft Excel