Excel Advanced Filter - En komplet guide med eksempler

Se video - Excel Advanced Filter

Excel Advanced Filter er en af ​​de mest undervurderede og underudnyttede funktioner, jeg er stødt på.

Hvis du arbejder med Excel, er jeg sikker på, at du har brugt (eller i det mindste hørt om det almindelige excel -filter). Det filtrerer hurtigt et datasæt baseret på markering, specificeret tekst, nummer eller andre sådanne kriterier.

I denne guide vil jeg vise dig nogle fede ting, du kan gøre ved hjælp af det avancerede Excel -filter.

Men først … Hvad er Excel Advanced Filter?

Excel Advanced Filter - som navnet antyder - er den avancerede version af det almindelige filter. Du kan bruge dette, når du skal bruge mere komplekse kriterier til at filtrere dit datasæt.

Her er nogle forskelle mellem det almindelige filter og det avancerede filter:

• Selvom det normale datafilter filtrerer det eksisterende datasæt, kan du også bruge avanceret Excel -filter til at udtrække datasættet til et andet sted.
• Excel Advanced Filter giver dig mulighed for at bruge komplekse kriterier. Hvis du f.eks. Har salgsdata, kan du filtrere data efter et kriterium, hvor salgsrepræsentanten er Bob, og regionen er enten nord eller syd (vi ser, hvordan du gør dette i eksempler). Office -support har en god forklaring på dette.
• Du kan bruge Excel Advanced Filter til at udtrække unikke poster fra dine data (mere om dette på et sekund).

EXCEL AVANCERET FILTER (Eksempler)

Lad os nu se på et eksempel på brug af det avancerede filter i Excel.

Eksempel 1 - Udtrækning af en unik liste

Du kan bruge Excel Advanced Filter til hurtigt at udtrække unikke poster fra et datasæt (eller med andre ord fjerne dubletter).

I Excel 2007 og senere versioner er der mulighed for at fjerne dubletter fra et datasæt. Men det ændrer dit eksisterende datasæt. For at bevare de originale data intakte skal du oprette en kopi af dataene og derefter bruge indstillingen Fjern dubletter. Excel Advanced filter giver dig mulighed for at vælge en placering for at få en unik liste.

Lad os se, hvordan du bruger en avanceret filtre til at få en unik liste.

Antag, at du har et datasæt som vist herunder:

Som du kan se, er der dublerede poster i dette datasæt (fremhævet med orange). Disse kan skyldes en fejl i dataindtastning eller et resultat af datakompilering.

I et sådant tilfælde kan du bruge Excel Advanced Filter -værktøj til hurtigt at få en liste over alle de unikke poster på et andet sted (så dine originale data forbliver intakte).

Her er trinene til at få alle de unikke poster:

• Vælg hele datasættet (inklusive overskrifterne).
• Gå til fanen Data -> Sorter og filtrer -> Avanceret. (Du kan også bruge tastaturgenvejen - Alt + A + Q). Dette åbner dialogboksen Avanceret filter.
• Brug følgende oplysninger i dialogboksen Avanceret filter:
  • Handling: Vælg indstillingen 'Kopier til en anden placering'. Dette giver dig mulighed for at angive det sted, hvor du kan få listen over unikke poster.
  • Listeområde: Sørg for, at det refererer til det datasæt, hvorfra du vil finde unikke poster. Sørg også for, at overskrifter i datasættet er inkluderet.
  • Kriterier: Lad dette stå tomt.
  • Kopier til: Angiv den celleadresse, hvor du vil få listen over unikke poster.
  • Kopier kun unikke poster: Marker denne indstilling.
• Klik på OK.

Dette vil øjeblikkeligt give dig en liste over alle de unikke poster.

Advarsel: Når du bruger Avanceret filter til at få den unikke liste, skal du sørge for, at du også har valgt overskriften. Hvis du ikke gør det, vil den betragte den første celle som overskriften.

Eksempel 2 - Brug af kriterier i Excel Advanced Filter

At få unikke poster er en af ​​de mange ting, du kan gøre med avanceret Excel -filter.

Dens primære nytteværdi ligger i dens evne til at tillade brug af komplekse kriterier til filtrering af data.

Her er hvad jeg mener med komplekse kriterier. Antag, at du har et datasæt som vist nedenfor, og du vil hurtigt få alle de poster, hvor salget er større end 5000, og regionen er USA.

Sådan kan du bruge Excel Advanced Filter til at filtrere posterne baseret på de angivne kriterier:

• Det første trin, når du bruger Excel Advanced Filter med komplekse kriterier, er at angive kriterierne. For at gøre dette skal du kopiere overskrifterne og indsætte det et sted i regnearket.
• Angiv de kriterier, som du vil filtrere dataene for. I dette eksempel, da vi ønsker at få alle poster for USA med salg mere end 5000, skal du indtaste 'US' i cellen under Region og> 5000 i cellen under salg. Dette ville nu blive brugt som input i Avanceret filter til at hente de filtrerede data (som vist i de næste trin).
• Vælg hele datasættet (inklusive overskrifterne).
• Gå til fanen Data -> Sorter og filtrer -> Avanceret. Dette åbner dialogboksen Avanceret filter.
• Brug følgende oplysninger i dialogboksen Avanceret filter:
  • Handling: Vælg indstillingen 'Kopier til en anden placering'. Dette giver dig mulighed for at angive det sted, hvor du kan få listen over unikke poster.
  • Listeområde: Sørg for, at det refererer til det datasæt, hvorfra du vil finde unikke poster. Sørg også for, at overskrifter i datasættet er inkluderet.
  • Kriterier: Angiv de kriterier, vi konstruerede i ovenstående trin. I dette eksempel ville det være F1: I3.
  • Kopier til: Angiv den celleadresse, hvor du vil få listen over unikke poster.
  • Kopier kun unikke poster: Marker denne indstilling.
• Klik på OK.

Dette ville øjeblikkeligt give dig alle de rekorder, hvor regionen er USA, og salget er mere end 5000.

Ovenstående eksempel er et tilfælde, hvor filtreringen udføres ud fra to kriterier (USA og salg større end 5000).

Excel Advanced filter giver dig mulighed for at oprette mange forskellige kombinationer af kriterier.

Her er nogle eksempler på, hvordan du kan konstruere disse filtre.

Brug af AND -kriterierne

Når du vil bruge AND -kriterier, skal du angive det under overskriften.

For eksempel:

• For at filtrere poster, når regionen er USA OG salgsrepræsentanten er Joe.
• For at filtrere poster, når regionen er USA OG salgsværdien er større end 5000.
• Når regionen er USA OG registreres salget efter 31-03-2017.

Brug af ELLER -kriterierne

Når du vil bruge ELLER -kriterier, skal du angive kriterierne i den samme kolonne.

For eksempel:

• For at filtrere poster, når regionen er USA ELLER regionen er Asien.
• For at filtrere poster, når salgsrepræsentanten er Bob ELLER Martha.

Nu må du have indset, at når vi har kriterierne i samme række, er det en OG kriterier, og når vi har det i forskellige rækker, er det et ELLER kriterier.

Eksempel 3 - Brug af WILDCARD -tegn i avanceret filter i Excel

Excel Advanced Filter tillader også brug af jokertegn under konstruktion af kriterierne.

Der er tre jokertegn i Excel:

1. * (stjerne) - Det repræsenterer et vilkårligt antal tegn. For eksempel kan ex* betyde excel, excels, eksempel, ekspert osv.
2. ? (spørgsmålstegn) - Det repræsenterer en enkelt karakter. F.eks. Kan Tr? Mp betyde Trump eller Tramp.
3. ~ (tilde) - Det bruges til at identificere et jokertegn (~, *,?) I teksten.

Lad os nu se, hvordan vi kan bruge disse jokertegn til at foretage avanceret filtrering i Excel.

• For at filtrere poster, hvor sælgernavnet starter fra J.

Bemærk, at * repræsenterer et vilkårligt antal tegn. Så enhver rep med navnet, der starter med J, ville blive filtreret med disse kriterier.

På samme måde kan du også bruge de to andre jokertegn.

Bemærk: Hvis du bruger Office 365, skal du tjekke FILTER -funktionen. Det kan mange ting, som avanceret filter kan gøre med en simpel formel.

BEMÆRK:

1. Husk, at overskrifterne i kriterierne skal være nøjagtig de samme som i datasættet.
2. Avanceret filtrering kan ikke fortrydes, når den kopieres til andre steder.