Sådan filtreres i excel efter kolonner. Avanceret filter i Excel og eksempler på dets muligheder

Sandsynligvis kender alle brugere, der konstant arbejder med Microsoft Excel, om en så nyttig funktion af dette program som datafiltrering. Men ikke alle er klar over, at dette værktøj også har avancerede muligheder. Lad os se på, hvad det avancerede Microsoft Excel-filter kan, og hvordan det bruges.

Det er ikke nok at starte det avancerede filter med det samme; for dette skal endnu en betingelse være opfyldt. Dernæst vil vi tale om rækkefølgen af handlinger, der skal tages.

Trin 1: Opret en tabel med valgbetingelser

For at installere et avanceret filter skal du først oprette en ekstra tabel med valgbetingelser. Dens header er nøjagtig den samme som den vigtigste, som vi faktisk vil filtrere. For eksempel placerede vi en ekstra tabel over den vigtigste og farvede dens celler orange. Selvom du kan placere den i ethvert ledigt rum og endda på et andet ark.

Nu indtaster vi i den ekstra tabel de oplysninger, der skal filtreres fra hovedtabellen. I vores specifikke tilfælde besluttede vi fra listen over lønudstedelser til ansatte at vælge data om det vigtigste mandlige personale for 25.07.2016.

Trin 2: Kør det avancerede filter

Først efter at den ekstra tabel er blevet oprettet, kan du fortsætte med at køre det avancerede filter.

Vi kan således konkludere, at det avancerede filter giver flere muligheder end almindelig datafiltrering. Men det skal bemærkes, at arbejdet med dette værktøj stadig er mindre bekvemt end med et standardfilter.

Filtrering af data i Excel vil give dig mulighed for at vise de oplysninger, der interesserer brugeren på et bestemt tidspunkt. Det forenkler i høj grad processen med at arbejde med store borde. Du vil være i stand til at kontrollere både de data, der vil blive vist i kolonnen, og hvad der udelukkes fra listen.

Sådan tilføjes

Hvis du kompilerede oplysninger via fanen "Indsæt" - "Tabel" eller fanen "Hjem" - "Formater som tabel", så vil filtreringsindstillingen være aktiveret som standard. Den ønskede knap vises i form af en pil, som er placeret i den øverste celle i højre side.

Hvis du blot udfyldte blokkene med data og derefter formaterede dem som en tabel, skal du aktivere filteret. For at gøre dette skal du vælge hele rækken af celler, inklusive rækken med overskrifter, da den knap, vi har brug for, tilføjes til den øverste række. Men hvis du vælger blokke, der starter fra cellen med data, vil den første række ikke relatere til den filtrerede information. Gå derefter til fanen Data og klik på knappen Filter.

I eksemplet er knappen med en pil i overskrifterne, og det er korrekt - alle data placeret nedenfor vil blive filtreret.

Hvis du er interesseret i spørgsmålet om, hvordan man laver en tabel i Excel, skal du følge linket og læse artiklen om dette emne.

Hvordan virker det

Lad os nu se på, hvordan et filter fungerer i Excel. Lad os f.eks. bruge følgende data. Vi har tre kolonner: "Produktets navn", "Kategori" og "Pris", vil vi anvende forskellige filtre på dem.

Klik på pilen i den øverste celle i den ønskede kolonne. Her vil du se en liste over ikke-gentagende data fra alle celler placeret i denne kolonne. Der vil være et flueben ud for hver værdi. Fjern markeringen i afkrydsningsfelterne for de værdier, du vil ekskludere fra listen.

Lad os for eksempel kun efterlade frugter i "Kategorien". Fjern markeringen i feltet "grøntsag", og klik på "OK".

For de tabelkolonner, hvor et filter er anvendt, vises et tilsvarende ikon i den øverste celle.

Sådan sletter du

Hvis du skal fjerne et datafilter i Excel, skal du klikke på cellen tilsvarende ikonet og vælg fra menuen "Fjern filter fra (kolonnenavn)".

Du kan filtrere oplysninger i Excel på forskellige måder. Der er tekst- og talfiltre. De anvendes i overensstemmelse hermed, hvis kolonnecellerne indeholder enten tekst eller tal.

Brug af et filter

Numerisk

Anvend "Numerisk..." på kolonnen "Pris". Klik på knappen i den øverste celle og vælg det tilsvarende punkt i menuen. Fra rullelisten kan du vælge den betingelse, du vil anvende på dataene. Lad os f.eks. vise alle produkter, hvis pris er under "25". Vælg "mindre".

Indtast den ønskede værdi i det relevante felt. Du kan anvende flere betingelser for at filtrere ved hjælp af logiske OG og ELLER. Når du bruger "AND", skal begge betingelser være opfyldt; når du bruger "ELLER", skal en af de specificerede betingelser være opfyldt. For eksempel kan du indstille: "mindre" - "25" - "Og" - "mere" - "55". Derfor vil vi udelukke produkter, hvis pris er i området fra 25 til 55.

I eksemplet gjorde jeg det sådan her. Alle data med en "Pris" under 25 vises her.

Tekst

"Tekstfilter" i eksempeltabellen kan anvendes på kolonnen "Produktets navn". Klik på knappen med pilen øverst og vælg punktet med samme navn fra menuen. I rullelisten, der åbnes, skal du f.eks. bruge "starter med".

Lad os efterlade i tabellen produkter, der begynder med "ka". I det næste vindue skriver vi i feltet: "ka*". Klik på "OK".

"*" i et ord erstatter en sekvens af tegn. For eksempel, hvis du indstiller betingelsen "indeholder" - "s*l", forbliver ordene: bord, stol, falk og så videre. "?" vil erstatte ethvert skilt. For eksempel "b?ton" - brød, knop, beton. Hvis du har brug for at efterlade ord bestående af 5 bogstaver, skriv "?????" .

Sådan forlod jeg dem, jeg havde brug for "Produktnavne".

Efter cellefarve

Filteret kan konfigureres efter tekstfarve eller cellefarve.

Lad os gøre det "Filtrer efter farve" kolonneceller "Produktets navn". Klik på pileknappen og vælg elementet med samme navn fra menuen. Lad os vælge rød farve.

Som et resultat var der kun røde produkter tilbage, eller rettere sagt alle de celler, der var fyldt med den valgte farve.

Efter tekstfarve

Nu i det anvendte eksempel vises kun røde frugter.

Hvis du ønsker, at alle tabelceller skal være synlige, men først røde, derefter grøn, blå og så videre, skal du bruge sortering i Excel. Ved at klikke på linket kan du læse en artikel om emnet.

Excel-datafiltrering inkluderer to filtre: AutoFilter og Avanceret filter. Antag, at du har et stort datasæt, men fra hele arrayet skal du se på eller vælge data, der vedrører en bestemt dato, en bestemt person osv. Der er filtre til dette. For dem, der støder på dette værktøj for første gang, sletter filteret ikke, men skjuler poster, der ikke opfylder de filtreringsbetingelser, du har angivet for dem.

Den første er et autofilter, designet til de mest simple operationer - fremhævelse af poster med en bestemt værdi (for eksempel kun at vælge poster relateret til LeBron James), data, der ligger i et bestemt område (eller over gennemsnittet eller top ti) eller celler /skrifttyper af en bestemt farve (Forresten, det er meget praktisk). Derfor er den meget nem at bruge. Du skal blot vælge de data, du vil se filtreret. Derefter kommandoen "Data" / "Filter". En listeboks vises på hver øverste celle i den øverste tabel, det er allerede let at forstå hver kommando, det er nemt at mestre, og jeg håber, at der ikke er behov for at forklare yderligere, kun nuancerne ved at bruge autofilteret:

1) Virker kun med et ikke-brydende område. Det er ikke længere muligt at filtrere to forskellige lister på ét ark.

2) Den øverste linje i tabellen tildeles automatisk som en titel og er ikke involveret i filtrering.

3) Du kan anvende alle filtre i forskellige kolonner, men husk, at afhængigt af den rækkefølge, filtrene anvendes i, er nogle betingelser muligvis ikke anvendt, fordi tidligere filtre har allerede skjult de nødvendige poster. Der er ikke noget problem her, disse poster ville alligevel være blevet skjult, men hvis du vil bruge flere sæt filtre, så er det bedre at starte med de forhold, der har mindst anvendelse.

Praktisk anvendelse på arbejdspladsen: for eksempel gennemarbejder du denne liste for at finde en fejl eller kontrollere data. Efter at have anvendt autofilteret, kan du gå gennem hele tabellen én efter én og sekventielt markere de data, der allerede er blevet set. Knapperne "Slet" og "Anvend igen" bestemmer bordets udseende efter anvendelse af betingelserne. Derefter, efter at have afsluttet arbejdet med tabellen, kan du returnere skrifttyperne til deres oprindelige form uden at ændre selve dataene. Forresten er nogle mennesker forvirrede over det faktum, at alle poster i tabellen forsvinder efter anvendelse af betingelser. Nå, se nærmere, du har sat betingelser, hvorunder der ikke er nogen poster, der opfylder disse betingelser. At tabellen filtreres er, når tabelrækkenumrene er fremhævet med blåt.

Lad os nu gå videre til det avancerede filter. Det adskiller sig fra autofilteret ved, at det har mere finkornede indstillinger, men også et større udvalg ved filtrering af data. I særdeleshed:

1) Indstiller så mange betingelser som nødvendigt.

2) Giver dig mulighed for at vælge celler med unikke (ikke-gentagende) data. Dette er ofte nødvendigt, når man arbejder med data, og muligheden håndterer problemet perfekt.

3) Giver dig mulighed for at kopiere filterresultatet til en separat placering uden at røre ved hovedarrayet.

Så den største forskel ved at arbejde med dette filter er, at vi først skal udarbejde en tabel over betingelser. Det er nemt at gøre. Hovedtabellens overskrifter kopieres og indsættes på et sted, der er praktisk for os (jeg foreslår over hovedtabellen). Der burde være så mange rækker i denne tabel, at efter at have defineret betingelserne, vil du ikke være i stand til at komme ind i hovedtabellen.

Eksempler på forhold:

1) 'L*' - celler, der starter med L

2) '>5' - data større end 5

Hvis du sletter rækker fra en filtreret tabel, vil de blive slettet uden at tage deres naboer med. De der. hvis tabellen er filtreret og viser række 26-29 og 31-25, vil markering af alle rækker og sletning af dem ikke resultere i sletning af række 30. Dette er praktisk, jeg personligt bruger dette ofte, når jeg skriver makroer. Hvilken fordel giver det - ofte får vi tabeller, der skal bringes i arbejdsform, dvs. fjerne for eksempel tomme linjer. Det, vi gør, er at anvende et filter på tabellen, der kun viser de rækker, som vi ikke har brug for, og derefter slette hele tabellen, inklusive overskriften. Unødvendige rækker og overskrift fjernes, mens tabellen ikke har mellemrum og danner et enkelt område. Og en overskriftslinje kan tilføjes ved simple kopieringsoperationer fra et tidligere forberedt område. Hvorfor er dette vigtigt, når du skriver makroer? Det vides ikke fra hvilken række de uønskede data begynder, og det er ikke klart fra hvilken række man skal begynde at slette dem; sletning af hele tabellen hjælper med at løse dette problem hurtigt.

Det avancerede filter i Excel giver større muligheder for at administrere regnearksdata. Det er mere komplekst i indstillinger, men meget mere effektivt i drift.

Ved at bruge et standardfilter kan en Microsoft Excel-bruger ikke løse alle de tildelte opgaver. Der er ingen visuel visning af anvendte filtreringsbetingelser. Det er ikke muligt at anvende mere end to udvælgelseskriterier. Du kan ikke filtrere duplikerede værdier for kun at beholde unikke poster. Og selve kriterierne er skematiske og enkle. Funktionaliteten af det avancerede filter er meget rigere. Lad os se nærmere på dens muligheder.

Hvordan laver man et avanceret filter i Excel?

Det avancerede filter giver dig mulighed for at filtrere data efter et ubegrænset sæt betingelser. Ved hjælp af værktøjet kan brugeren:

opstille mere end to udvælgelseskriterier;
kopier filtreringsresultatet til et andet ark;
sæt en betingelse for enhver kompleksitet ved hjælp af formler;
udtrække unikke værdier.

Algoritmen til at bruge et avanceret filter er enkel:

Den øverste tabel er resultatet af filtrering. Den nederste plade med betingelserne er givet side om side for overskuelighed.

Hvordan bruger man det avancerede filter i Excel?

For at annullere handlingen af et avanceret filter skal du placere markøren hvor som helst i tabellen og trykke på tastekombinationen Ctrl + Shift + L eller "Data" - "Sortering og filtrering" - "Ryd".

Ved at bruge værktøjet "Avanceret filter" finder vi information om værdier, der indeholder ordet "Set".

Vi tilføjer kriterier til betingelserstabellen. For eksempel disse:

I dette tilfælde vil programmet søge efter al information om produkter, hvis navne indeholder ordet "Set".

Du kan bruge tegnet "=" til at finde den nøjagtige værdi. Lad os tilføje følgende kriterier til tabellen over betingelser:

Excel fortolker tegnet "=" som et signal: brugeren vil nu indtaste en formel. For at programmet skal fungere korrekt, skal formellinjen indeholde en post som: ="= Sæt med region 6 celler."

Efter brug af "Avanceret filter":

Lad os nu filtrere kildetabellen ved hjælp af "ELLER"-betingelsen for forskellige kolonner. "ELLER"-operatoren er også tilgængelig i AutoFilter-værktøjet. Men der kan det bruges inden for én kolonne.

I vilkårstabellen vil vi indtaste udvælgelseskriterierne: ="= Sæt af region 6. klasse." (i kolonnen "Navn") og ="

Bemærk venligst, at kriterierne skal skrives under de relevante overskrifter på FORSKELLIGE linjer.

Udvælgelsesresultat:

Det avancerede filter giver dig mulighed for at bruge formler som kriterier. Lad os se på et eksempel.

Valg af rækken med den maksimale gæld: =MAX(Tabel1[Gæld]).

Dermed får vi de samme resultater som efter at have udført flere filtre på ét Excel-ark.

Hvordan laver man flere filtre i Excel?

Lad os oprette et filter baseret på flere værdier. For at gøre dette indtaster vi flere dataudvælgelseskriterier i betingelserstabellen:

Lad os bruge værktøjet "Avanceret filter":

Fra tabellen med de valgte data vil vi nu udtrække ny information udvalgt efter andre kriterier. For eksempel kun forsendelser for 2014.

Vi indtaster et nyt kriterium i betingelserstabellen og bruger filtreringsværktøjet. Det indledende interval er en tabel med data valgt i henhold til det foregående kriterium. Sådan filtrerer du på tværs af flere kolonner.

For at bruge flere filtre kan du oprette flere betingelsestabeller på nye ark. Implementeringsmetoden afhænger af den opgave, som brugeren har indstillet.

Hvordan filtreres efter række i Excel?

Standardmetoder - ingen måde. Microsoft Excel vælger kun data i kolonner. Derfor er vi nødt til at lede efter andre løsninger.

Her er eksempler på strengkriterier for et avanceret filter i Excel:

For at give et eksempel på, hvordan et rækkefilter fungerer i Excel, lad os oprette en tabel.

Nogle gange kan de data, der indtastes i et Excel-dokument, bestå af tusindvis af linjer, og det er ret svært at finde de nødvendige linjer blandt dem. Men hvis de data, du leder efter, opfylder nogle bestemte kriterier, takket være hvilke du kan filtrere unødvendig information fra, skal du bruge et filter i Excel.

For alle tabeller i Excel oprettet via menuen "Bord" på fanen "Indsæt" eller som formatering er blevet anvendt på, som en tabel, allerede har et indbygget filter.

Hvis dataene, formateret som en tabel, ikke har et filter, skal det installeres. For at gøre dette skal du vælge tabeloverskriften eller blot vælge en celle inde i tabellen og gå til fanen "Data" og vælg fra menuen "Filter". Det samme menupunkt kan findes på fanen "Hjem" på menuen "Sortering og filtrering". Efter tilslutning af filteret vises et tilsvarende tegn, en firkant med en omvendt trekant, i tabeloverskriften på højre side af titlen på hver kolonne.

Den enkleste brug af et filter er at skjule alle værdier på listen undtagen de valgte. Bare klik på filterikonet og efterlad et flueben i listen over værdier ud for den påkrævede. Efter at have anvendt et filter, ændres ikonet i den filtrerede kolonne også, hvilket giver dig mulighed for visuelt at bestemme, om filteret anvendes på dataene. For at fjerne et filter skal du klikke på filterikonet og vælge "Fjern filter fra kolonne...".

Du kan også tilpasse mere komplekse filtre. Vores første kolonne har kun tekstdata, så når vi klikker på filterikonet, er der et aktivt element i denne kolonne "Tekstfiltre", hvor du kan vælge nogle betingelser. I tilfælde af numeriske data, som i anden kolonne, vil muligheden være tilgængelig "Numeriske filtre".

Når du vælger en brugerdefineret indstilling, åbnes et brugerdefineret filtervindue, hvor du kan vælge to betingelser på én gang med en kombination "OG" Og "ELLER".

Det er også muligt at indstille et filter efter tekstfarve og cellefarve. I dette tilfælde vil kun tekst- og cellefarverne, der er anvendt i denne kolonne, blive vist på listen.

I de nyeste versioner af Excel er det muligt for tabeller at oprette dataudsnit. For at gøre dette skal du aktivere en tabelcelle og gå til den fane, der vises "Konstruktør", og vælg menuen "Indsæt skive". Et vindue vises, hvor du skal vælge den kolonne, som dataudsnittet skal anvendes på. Når du har valgt en kolonne, vises et flydende vindue med kontroller, hvor alle udsnitselementerne vil blive opført. Ved at vælge en værdi vil vi modtage det tilsvarende dataudsnit.