Hvordan filtrere i excel etter kolonner. Avansert filter i Excel og eksempler på dets muligheter

Sannsynligvis vet alle brukere som konstant jobber med Microsoft Excel om en så nyttig funksjon av dette programmet som datafiltrering. Men ikke alle er klar over at dette verktøyet også har avanserte funksjoner. La oss se på hva Microsoft Excel avanserte filter kan gjøre og hvordan du bruker det.

Det er ikke nok å starte det avanserte filteret umiddelbart, for dette må en annen betingelse være oppfylt. Deretter vil vi snakke om rekkefølgen av handlinger som bør iverksettes.

Trinn 1: Lag en tabell med utvalgsbetingelser

For å installere et avansert filter må du først lage en ekstra tabell med utvalgsbetingelser. Headeren er nøyaktig den samme som den viktigste, som vi faktisk vil filtrere. For eksempel plasserte vi en ekstra tabell over den viktigste og farget cellene oransje. Selv om du kan plassere den på hvilken som helst ledig plass og til og med på et annet ark.

Nå legger vi inn i tilleggstabellen informasjonen som må filtreres fra hovedtabellen. I vårt spesifikke tilfelle, fra listen over lønn utstedt til ansatte, bestemte vi oss for å velge data om det viktigste mannlige personalet for 25.07.2016.

Trinn 2: Kjør det avanserte filteret

Først etter at tilleggstabellen er opprettet, kan du fortsette å kjøre det avanserte filteret.

Dermed kan vi konkludere med at det avanserte filteret gir flere alternativer enn vanlig datafiltrering. Men det skal bemerkes at det fortsatt er mindre praktisk å jobbe med dette verktøyet enn med et standardfilter.

Filtrering av data i Excel vil tillate deg å vise informasjonen som interesserer brukeren på et bestemt tidspunkt. Det forenkler prosessen med å jobbe med store bord. Du vil kunne kontrollere både dataene som skal vises i kolonnen og hva som ekskluderes fra listen.

Hvordan legge til

Hvis du har samlet informasjon gjennom "Sett inn"-fanen - "Tabell" eller "Hjem"-fanen - "Formater som tabell", vil filtreringsalternativet være aktivert som standard. Den nødvendige knappen vises i form av en pil, som er plassert i den øverste cellen på høyre side.

Hvis du bare fylte blokkene med data og deretter formaterte dem som en tabell, må du aktivere filteret. For å gjøre dette, velg hele utvalget av celler, inkludert raden med overskrifter, siden knappen vi trenger vil bli lagt til øverste rad. Men hvis du velger blokker som starter fra cellen med data, vil den første raden ikke forholde seg til den filtrerte informasjonen. Gå deretter til fanen Data og klikk på Filter-knappen.

I eksemplet er knappen med en pil i overskriftene, og dette er riktig - alle data som ligger under vil bli filtrert.

Hvis du er interessert i spørsmålet om hvordan du lager en tabell i Excel, følg lenken og les artikkelen om dette emnet.

Hvordan virker det

La oss nå se på hvordan et filter fungerer i Excel. La oss for eksempel bruke følgende data. Vi har tre kolonner: "Produktets navn", "Kategori" og "Pris", vil vi bruke forskjellige filtre på dem.

Klikk på pilen i den øverste cellen i ønsket kolonne. Her vil du se en liste over ikke-repeterende data fra alle cellene i denne kolonnen. Det vil være en hake ved siden av hver verdi. Fjern avmerkingen i boksene for verdiene du vil ekskludere fra listen.

La oss for eksempel bare la frukt være i "Kategori". Fjern merket for "grønnsak" og klikk "OK".

For de tabellkolonnene som et filter er brukt på, vil et tilsvarende ikon vises i den øverste cellen.

Slik sletter du

Hvis du trenger å fjerne et datafilter i Excel, klikker du på cellen tilsvarende ikonet og velg fra menyen "Fjern filter fra (kolonnenavn)".

Du kan filtrere informasjon i Excel på ulike måter. Det er tekst- og tallfiltre. De brukes tilsvarende hvis kolonnecellene inneholder enten tekst eller tall.

Ved hjelp av et filter

Numerisk

Bruk "Numerisk..." på "Pris"-kolonnen. Klikk på knappen i den øverste cellen og velg det tilsvarende elementet fra menyen. Fra rullegardinlisten kan du velge betingelsen du vil bruke på dataene. La oss for eksempel vise alle produkter med pris under "25". Velg "mindre".

Skriv inn ønsket verdi i det aktuelle feltet. Du kan bruke flere betingelser for å filtrere ved å bruke logiske OG og ELLER. Når du bruker "AND", må begge betingelsene være oppfylt; når du bruker "ELLER", må en av de spesifiserte betingelsene være oppfylt. For eksempel kan du stille inn: "mindre" - "25" - "Og" - "mer" - "55". Derfor vil vi ekskludere produkter med pris i området fra 25 til 55.

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

Tekst

"Tekstfilter" i eksempeltabellen, kan brukes på kolonnen "Produktets navn". Klikk på knappen med pilen øverst og velg elementet med samme navn fra menyen. I rullegardinlisten som åpnes, bruk for eksempel "begynner med".

La oss legge igjen i tabellen produkter som begynner med "ka". I det neste vinduet, i feltet skriver vi: "ka*". Klikk "OK".

"*" i et ord erstatter en sekvens av tegn. For eksempel, hvis du setter betingelsen "inneholder" - "s*l", vil ordene forbli: bord, stol, falk og så videre. "?" vil erstatte ethvert skilt. For eksempel «b?ton» – brød, knopp, betong. Hvis du trenger å legge igjen ord som består av 5 bokstaver, skriv "?????" .

Slik forlot jeg de jeg trengte "Produktnavn".

Etter cellefarge

Filteret kan konfigureres med tekstfarge eller cellefarge.

La oss gjøre det "Filtrer etter farge" kolonneceller "Produktets navn". Klikk på pilknappen og velg elementet med samme navn fra menyen. La oss velge rød farge.

Som et resultat var det bare røde produkter igjen, eller rettere sagt alle cellene som var fylt med den valgte fargen.

Etter tekstfarge

Nå i eksemplet som brukes, vises bare røde frukter.

Hvis du vil at alle tabellcellene skal være synlige, men rød først, deretter grønn, blå og så videre, bruk sortering i Excel. Ved å klikke på lenken kan du lese en artikkel om temaet.

Excel-datafiltrering inkluderer to filtre: AutoFilter og Advanced Filter. Anta at du har et stort datasett, men fra hele matrisen må du se på eller velge data som er knyttet til en bestemt dato, en bestemt person osv. Det finnes filtre for dette. For de som møter dette verktøyet for første gang, sletter ikke filteret, men skjuler poster som ikke oppfyller filtreringsbetingelsene du angir for dem.

Den første er et autofilter, designet for de mest enkle operasjonene - utheving av poster med en spesifikk verdi (for eksempel bare velge poster relatert til LeBron James), data som ligger i et bestemt område (eller over gjennomsnittet eller topp ti) eller celler /fonts av en bestemt farge (Forresten, det er veldig praktisk). Følgelig er den veldig enkel å bruke. Du trenger bare å velge dataene du vil se filtrert. Deretter kommandoen "Data" / "Filter". En listeboks vil vises på hver øverste celle i topptabellen, det er allerede lett å forstå hver kommando, det er enkelt å mestre og jeg håper det ikke er behov for å forklare ytterligere, bare nyansene ved bruk av autofilteret:

1) Fungerer kun med en ikke-brytende rekkevidde. Det er ikke lenger mulig å filtrere to forskjellige lister på ett ark.

2) Den øverste linjen i tabellen blir automatisk tildelt som en tittel og er ikke involvert i filtrering.

3) Du kan bruke alle filtre i forskjellige kolonner, men husk at avhengig av rekkefølgen filtrene brukes i, kan det hende at enkelte betingelser ikke blir brukt, fordi tidligere filtre har allerede skjult de nødvendige oppføringene. Det er ikke noe problem her, disse oppføringene ville vært skjult uansett, men hvis du vil bruke flere sett med filtre, er det bedre å starte med de forholdene som har minst bruk.

Praktisk applikasjon på jobben: for eksempel, du arbeider gjennom denne listen for å finne en feil eller sjekke data. Etter å ha brukt autofilteret, kan du gå gjennom hele tabellen én etter én, sekvensielt merke dataene som allerede er sett. "Tøm" og "Bruk på nytt"-knappene bestemmer utseendet til tabellen etter å ha brukt betingelsene. Deretter, etter å ha fullført arbeidet med tabellen, kan du returnere skriftene til den opprinnelige formen uten å endre selve dataene. Forresten, noen mennesker er forvirret over det faktum at alle postene i tabellen forsvinner etter å ha brukt noen betingelser. Vel, ta en nærmere titt, du har satt betingelser der det ikke er noen poster som tilfredsstiller disse betingelsene. At tabellen filtreres er når tabellradnumrene er uthevet i blått.

La oss nå gå videre til det avanserte filteret. Det skiller seg fra autofilteret ved at det har mer finkornede innstillinger, men også et større utvalg ved filtrering av data. Spesielt:

1) Setter så mange betingelser som nødvendig.

2) Lar deg velge celler med unike (ikke-repeterende) data. Dette er ofte nødvendig når du arbeider med data og alternativet takler problemet perfekt.

3) Lar deg kopiere filterresultatet til et eget sted uten å berøre hovedgruppen.

Så hovedforskjellen i å jobbe med dette filteret er at vi først må utarbeide en tabell over forhold. Det er enkelt å gjøre. Overskriftene til hovedtabellen kopieres og limes inn på et sted som er praktisk for oss (jeg foreslår over hovedtabellen). Det skal være så mange rader i denne tabellen at etter å ha definert betingelsene vil du ikke kunne komme inn i hovedtabellen.

Eksempler på forhold:

1) ‘L*’ – celler som begynner med L

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

Hvis du sletter rader fra en filtrert tabell, vil de bli slettet uten å ta med seg naboene. De. hvis tabellen er filtrert og viser radene 26-29 og 31-25, vil det å velge alle rader og slette dem ikke resultere i sletting av rad 30. Dette er praktisk, jeg personlig bruker dette ofte når jeg skriver makroer. Hvilken fordel gir dette – ofte får vi tabeller som må bringes i arbeidsform, d.v.s. fjerne for eksempel tomme linjer. Det vi gjør er å bruke et filter på tabellen, og vise bare de radene vi ikke trenger, og deretter slette hele tabellen, inkludert overskriften. Unødvendige rader og overskrifter fjernes, mens tabellen ikke har mellomrom og danner et enkelt område. Og en overskriftslinje kan legges til ved enkle kopieringsoperasjoner fra et tidligere forberedt område. Hvorfor er dette viktig når du skriver makroer? Det er ikke kjent fra hvilken rad de uønskede dataene begynner, og det er ikke klart fra hvilken rad du skal begynne å slette dem; sletting av hele tabellen hjelper deg raskt å løse dette problemet.

Det avanserte filteret i Excel gir større muligheter for å administrere regnearkdata. Det er mer komplekst i innstillinger, men mye mer effektivt i drift.

Ved å bruke et standardfilter kan ikke en Microsoft Excel-bruker løse alle oppgavene som er tildelt. Det er ingen visuell visning av anvendte filtreringsforhold. Det er ikke mulig å bruke mer enn to utvalgskriterier. Du kan ikke filtrere dupliserte verdier for å bare beholde unike oppføringer. Og selve kriteriene er skjematiske og enkle. Funksjonaliteten til det avanserte filteret er mye rikere. La oss se nærmere på dens evner.

Hvordan lage et avansert filter i Excel?

Det avanserte filteret lar deg filtrere data etter et ubegrenset sett med betingelser. Ved å bruke verktøyet kan brukeren:

1. angi mer enn to utvalgskriterier;
2. kopier filtreringsresultatet til et annet ark;
3. angi en betingelse for enhver kompleksitet ved å bruke formler;
4. trekke ut unike verdier.

Algoritmen for å bruke et avansert filter er enkel:

Den øverste tabellen er resultatet av filtrering. Den nedre platen med forholdene er gitt side ved side for klarhet.



Hvordan bruke det avanserte filteret i Excel?

For å avbryte handlingen til et avansert filter, plasser markøren hvor som helst i tabellen og trykk på tastekombinasjonen Ctrl + Shift + L eller "Data" - "Sortering og filtrering" - "Slett".

Ved å bruke "Advanced Filter"-verktøyet finner vi informasjon om verdier som inneholder ordet "Set".

Vi vil legge til kriterier i vilkårstabellen. For eksempel disse:

I dette tilfellet vil programmet søke etter all informasjon om produkter hvis navn inneholder ordet "Set".

Du kan bruke "="-tegnet for å finne den nøyaktige verdien. La oss legge til følgende kriterier i vilkårstabellen:

Excel tolker tegnet "=" som et signal: brukeren vil nå angi en formel. For at programmet skal fungere riktig, må formellinjen inneholde en oppføring som: ="= Sett med region 6 celler."

Etter bruk av "Avansert filter":

La oss nå filtrere kildetabellen ved å bruke "ELLER"-betingelsen for forskjellige kolonner. "ELLER"-operatoren er også tilgjengelig i AutoFilter-verktøyet. Men der kan den brukes innenfor én kolonne.

I vilkårstabellen vil vi legge inn utvalgskriteriene: ="= Sett med region 6. klasse." (i «Navn»-kolonnen) og ="

Vær oppmerksom på at kriteriene må skrives under de aktuelle overskriftene på ULIKE linjer.

Utvalgsresultat:

Det avanserte filteret lar deg bruke formler som kriterier. La oss se på et eksempel.

Valg av raden med maksimal gjeld: =MAX(Tabell1[Gjeld]).

Dermed får vi de samme resultatene som etter å ha utført flere filtre på ett Excel-ark.

Hvordan lage flere filtre i Excel?

La oss lage et filter basert på flere verdier. For å gjøre dette legger vi inn flere datavalgkriterier i vilkårstabellen:

La oss bruke verktøyet "Avansert filter":

Nå, fra tabellen med de valgte dataene, vil vi trekke ut ny informasjon valgt i henhold til andre kriterier. For eksempel kun forsendelser for 2014.

Vi legger inn et nytt kriterium i betingelsestabellen og bruker filtreringsverktøyet. Startområdet er en tabell med data valgt i henhold til det forrige kriteriet. Slik filtrerer du over flere kolonner.

For å bruke flere filtre kan du opprette flere tilstandstabeller på nye ark. Metoden for implementering avhenger av oppgaven satt av brukeren.

Hvordan filtrere etter rad i Excel?

Standardmetoder - ingen måte. Microsoft Excel velger kun data i kolonner. Derfor må vi se etter andre løsninger.

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

For å gi et eksempel på hvordan et radfilter fungerer i Excel, la oss lage en tabell.

Noen ganger kan dataene som legges inn i et Excel-dokument bestå av tusenvis av linjer, og det er ganske vanskelig å finne de nødvendige linjene blant dem. Men hvis dataene du leter etter oppfyller noen spesifikke kriterier, takket være at du kan filtrere ut unødvendig informasjon, må du bruke et filter i Excel.

For alle tabeller i Excel opprettet gjennom menyen "Bord" på fanen "Sett inn" eller som formatering er brukt på, som en tabell, allerede har et innebygd filter.

Hvis dataene, formatert som en tabell, ikke har et filter, må de installeres. For å gjøre dette, velg tabelloverskriften eller velg ganske enkelt en celle inne i tabellen og gå til fanen "Data" og velg fra menyen "Filter". Det samme menypunktet finner du på fanen "Hjem" på menyen "Sortering og filtrering". Etter å ha koblet til filteret, vises et tilsvarende tegn, en firkant med en omvendt trekant, i tabelloverskriften på høyre side av tittelen til hver kolonne.

Den enkleste bruken av et filter er å skjule alle verdier i listen unntatt de valgte. Bare klikk på filterikonet og la et merke i listen over verdier på motsatt side av den nødvendige. Etter å ha brukt et filter, endres også ikonet i den filtrerte kolonnen, noe som lar deg visuelt bestemme om filteret brukes på dataene. For å fjerne et filter, klikk på filterikonet og velg "Fjern filter fra kolonne...".

Du kan også tilpasse mer komplekse filtre. Vår første kolonne har kun tekstdata, så når vi klikker på filterikonet, er det et aktivt element i denne kolonnen "Tekstfiltre", hvor du kan velge noen forhold. Når det gjelder numeriske data, som i den andre kolonnen, vil alternativet være tilgjengelig "Numeriske filtre".

Når du velger et tilpasset alternativ, åpnes et tilpasset filtervindu, der du kan velge to forhold samtidig med en kombinasjon "OG" Og "ELLER".

Det er også mulig å sette et filter etter tekstfarge og cellefarge. I dette tilfellet vil bare tekst- og cellefargene som er brukt i denne kolonnen vises i listen.

I de nyeste versjonene av Excel er det mulig for tabeller å lage datastykker. For å gjøre dette, aktiver en hvilken som helst tabellcelle og gå til fanen som vises "Konstruktør", og velg menyen "Sett inn skive". Et vindu vises der du må velge kolonnen som datastykket skal brukes på. Etter å ha valgt en kolonne, vises et flytende vindu med kontroller, der alle skiveelementene vil bli oppført. Ved å velge hvilken som helst verdi vil vi motta den tilsvarende datadelen.