Excel Advanced Filter - En komplett guide med eksempler

Se video - Excel Advanced Filter

Excel Advanced Filter er en av de mest undervurderte og underutnyttede funksjonene jeg har kommet over.

Hvis du jobber med Excel, er jeg sikker på at du har brukt (eller i det minste hørt om det vanlige excelfilteret). Det filtrerer raskt et datasett basert på valg, spesifisert tekst, nummer eller andre slike kriterier.

I denne guiden vil jeg vise deg noen kule ting du kan gjøre ved hjelp av det avanserte Excel -filteret.

Men først … Hva er Excel Advanced Filter?

Excel Advanced Filter - som navnet antyder - er den avanserte versjonen av det vanlige filteret. Du kan bruke dette når du trenger å bruke mer komplekse kriterier for å filtrere datasettet.

Her er noen forskjeller mellom det vanlige filteret og det avanserte filteret:

• Selv om det vanlige datafilteret vil filtrere det eksisterende datasettet, kan du også bruke avansert Excel -filter til å pakke ut datasettet til et annet sted.
• Excel Advanced Filter lar deg bruke komplekse kriterier. For eksempel, hvis du har salgsdata, kan du filtrere data på et kriterium der selgeren er Bob og regionen er enten nord eller sør (vi ser hvordan du gjør dette i eksempler). Kontorsupport har en god forklaring på dette.
• Du kan bruke Excel Advanced Filter til å trekke ut unike poster fra dataene dine (mer om dette på et sekund).

EXCEL AVANSERT FILTER (eksempler)

La oss se på et eksempel på bruk av det avanserte filteret i Excel.

Eksempel 1 - Trekke ut en unik liste

Du kan bruke Excel Advanced Filter til raskt å trekke ut unike poster fra et datasett (eller med andre ord fjerne duplikater).

I Excel 2007 og senere versjoner er det et alternativ for å fjerne duplikater fra et datasett. Men det endrer det eksisterende datasettet ditt. For å beholde de originale dataene intakte må du lage en kopi av dataene og deretter bruke alternativet Fjern dubletter. Excel Advanced filter lar deg velge et sted for å få en unik liste.

La oss se hvordan du bruker avanserte filtre for å få en unik liste.

Anta at du har et datasett som vist nedenfor:

Som du kan se, er det dupliserte poster i dette datasettet (markert med oransje). Disse kan skyldes en feil i datainføringen eller et resultat av datakompilering.

I et slikt tilfelle kan du bruke Excel Advanced Filter -verktøyet til raskt å få en liste over alle de unike postene på et annet sted (slik at de originale dataene forblir intakte).

Her er trinnene for å få alle de unike postene:

• Velg hele datasettet (inkludert overskriftene).
• Gå til kategorien Data -> Sorter og filtrer -> Avansert. (Du kan også bruke hurtigtasten - Alt + A + Q). Dette åpner dialogboksen Avansert filter.
• I dialogboksen Avansert filter bruker du følgende detaljer:
  • Handling: Velg alternativet ‘Kopier til et annet sted’. Dette lar deg spesifisere stedet der du kan få listen over unike poster.
  • Listeområde: Sørg for at det refererer til datasettet du vil finne unike poster fra. Sørg også for at overskrifter i datasettet er inkludert.
  • Kriterier: La dette stå tomt.
  • Kopier til: Angi celleadressen der du vil få listen over unike poster.
  • Bare kopier unike poster: Merk av for dette alternativet.
• Klikk OK.

Dette vil umiddelbart gi deg en liste over alle de unike postene.

Forsiktighet: Når du bruker Avansert filter for å få den unike listen, må du kontrollere at du også har valgt overskriften. Hvis du ikke gjør det, vil den betrakte den første cellen som overskrift.

Eksempel 2 - Bruke kriterier i Excel Advanced Filter

Å få unike poster er en av de mange tingene du kan gjøre med avansert Excel -filter.

Det primære verktøyet ligger i dets evne til å tillate bruk av komplekse kriterier for filtrering av data.

Her er det jeg mener med komplekse kriterier. Anta at du har et datasett som vist nedenfor, og du vil raskt få alle postene der salget er større enn 5000 og regionen er USA.

Slik kan du bruke Excel Advanced Filter til å filtrere postene basert på de angitte kriteriene:

• Det første trinnet når du bruker Excel Advanced Filter med komplekse kriterier er å spesifisere kriteriene. For å gjøre dette, kopierer du overskriftene og limer det inn et sted i regnearket.
• Angi kriteriene du vil filtrere dataene for. I dette eksemplet, siden vi ønsker å få alle postene for USA med salg mer enn 5000, skriver du inn ‘USA’ i cellen under Region og> 5000 i cellen under salg. Dette vil nå bli brukt som inngang i Avansert filter for å hente filtrerte data (som vist i de neste trinnene).
• Velg hele datasettet (inkludert overskriftene).
• Gå til kategorien Data -> Sorter og filtrer -> Avansert. Dette åpner dialogboksen Avansert filter.
• I dialogboksen Avansert filter bruker du følgende detaljer:
  • Handling: Velg alternativet ‘Kopier til et annet sted’. Dette lar deg spesifisere stedet der du kan få listen over unike poster.
  • Listeområde: Sørg for at det refererer til datasettet du vil finne unike poster fra. Sørg også for at overskrifter i datasettet er inkludert.
  • Kriterier: Spesifiser kriteriene vi konstruerte i trinnene ovenfor. I dette eksemplet vil det være F1: I3.
  • Kopier til: Angi celleadressen der du vil få listen over unike poster.
  • Bare kopier unike poster: Merk av for dette alternativet.
• Klikk OK.

Dette vil umiddelbart gi deg alle postene der regionen er USA og salget er mer enn 5000.

Eksemplet ovenfor er et tilfelle der filtreringen utføres basert på to kriterier (USA og salg større enn 5000).

Excel Advanced filter lar deg lage mange forskjellige kombinasjoner av kriterier.

Her er noen eksempler på hvordan du kan konstruere disse filtrene.

Bruke AND -kriteriene

Når du vil bruke AND -kriterier, må du spesifisere det under overskriften.

For eksempel:

• For å filtrere poster når regionen er USA OG selgeren er Joe.
• For å filtrere poster når regionen er USA OG salgsverdien er større enn 5000.
• Når regionen er USA OG blir salget registrert etter 31-03-2017.

Bruke ELLER -kriteriene

Når du vil bruke ELLER -kriterier, må du angi kriteriene i samme kolonne.

For eksempel:

• For å filtrere poster når regionen er USA ELLER regionen er Asia.
• For å filtrere poster når salgsrepresentanten er Bob OR Martha.

Nå må du ha innsett at når vi har kriteriene i samme rad, er det en OG kriterier, og når vi har det i forskjellige rader, er det en ELLER kriterier.

Eksempel 3 - Bruke WILDCARD -tegn i avansert filter i Excel

Excel Advanced Filter tillater også bruk av jokertegn mens du bygger kriteriene.

Det er tre jokertegn i Excel:

1. * (stjerne) - Det representerer et hvilket som helst antall tegn. Eks* kan for eksempel bety excel, utmerker seg, eksempel, ekspert, etc.
2. ? (spørsmålstegn) - Det representerer en enkelt karakter. For eksempel kan Tr? Mp bety Trump eller Tramp.
3. ~ (tilde) - Den brukes til å identifisere et jokertegn (~, *,?) I teksten.

La oss nå se hvordan vi kan bruke disse jokertegnene til å gjøre avansert filtrering i Excel.

• For å filtrere poster der selgernavnet starter fra J.

Vær oppmerksom på at * representerer et hvilket som helst antall tegn. Så enhver representant med navnet som begynner med J ville bli filtrert med disse kriteriene.

På samme måte kan du også bruke de to andre jokertegnene.

Merk: Hvis du bruker Office 365, bør du sjekke FILTER -funksjonen. Det kan gjøre mange ting som avansert filter kan gjøre med en enkel formel.

MERK:

1. Husk at overskriftene i kriteriene skal være nøyaktig de samme som i datasettet.
2. Avansert filtrering kan ikke angres når den kopieres til andre steder.