Filtrer data etter listebetingelser. Filtrering av data i listen. Filtrering med et dataskjema
Målet med arbeidet: utføre datasortering, bli kjent med metoden for filtrering av listeoppføringer, autofiltrering, arbeid med dataskjemaet.
Øvelse 1.
Sorter dataene i tabell 5.5 flere ganger i samsvar med følgende funksjoner - i alfabetisk rekkefølge etter navnene på kjøperne, i synkende rekkefølge av transaksjonsbeløpet, i stigende rekkefølge etter transaksjonsdatoen, etter totalen av egenskaper (etternavn, dato , beløp).
Metodikk for å utføre arbeid
1. Åpne en ny arbeidsbok og lagre den som "Sorter" i arbeidsmappen din .
2. Lag tabellen vist i figur 5.56.
Figur 5.56 - Starttabell med data
3. Angi formateringsalternativene for tabellen.
Font Times New Roman, skriftstørrelse 12 pt., fet skrift og senterjustering for overskrifter, tekstbryting, grå fyll; for hoveddelen. Som en påminnelse er formateringskommandoer tilgjengelige på båndet Hjem Þ Celler .
4. For å sortere etter kundens etternavnsfelt, plasser markøren hvor som helst i denne kolonnen og kjør kommandoen Data Þ Sorter (Fig. 5.51) .
I dialogboksen som åpnes, i feltet Sorter etter velg "Kundens etternavn". Stigende.
5. Gjenta alle trinnene i avsnitt 4 og angi sorteringen etter "Transaksjonsbeløp", i synkende rekkefølge.
6. Sorter på nytt etter feltet Handelsdato, i stigende rekkefølge.
7. Kopier tabellen til nytt løv og sorter det etter sett med funksjoner. For å gjøre dette, ring kommandoen Data Þ Sorter. Installere Sorter etter etternavn i stigende rekkefølge, Så ved dato i stigende rekkefølge Til slutt, av sum i synkende rekkefølge.
8. Med kommando Gi nytt navn navngi disse to arkene.
Oppgave 2. Velg informasjon fra listen basert på AutoFilter-kommandoen.
Metodikk for å utføre arbeid.
1. På ark 4, lag en tabell og fyll den med informasjon fra tabell 5.5.
2. Gi nytt navn til Ark4 som AutoFilter #1.
3. For å bruke autofiltrering, plasser markøren i listeområdet og utfør kommandoen Data ÞFilter. Pil ned vil vises ved siden av navnene på tabellkolonnene, som viser en liste over mulige verdier. I kolonnen "Kjønn" velg "M" Kopier tabellen til ark 5 og gi den nytt navn til "Autofilter nr. 2".
4. På arket "AutoFilter No. 1", i kolonnen "Kjøn", åpner du filtreringslisten og velger "Alle". Deretter, i kolonnen "Fødselsdato", velg "Betingelse" i filtreringslisten og angi betingelsen (fig. 5.57):
Tabell 5.5
| Etternavn | Navn | ansettelsesdato | Fødselsdato | Gulv | Lønn | Alder |
| Pashkov | Igor | 16.05.74 | 15.03.49 | M | ||
| Andreeva | Anna | 16.01.93 | 19.10.66 | OG | ||
| Erokhin | Vladimir | 23.10.81 | 24.04.51 | M | ||
| Popov | Alexei | 02.05.84 | 07.10.56 | M | ||
| Tyunkov | Vladimir | 03.11.88 | 19.07.41 | M | ||
| Notkin | Eugene | 27.08.85 | 17.08.60 | M | ||
| Kubrina | Marina | 20.04.93 | 26.06.61 | OG | ||
| Gudkov | Nikita | 18.03.98 | 05.04.58 | M | ||
| Gorbatov | Michael | 09.08.99 | 15.09.52 | M | ||
| Bystrov | Alexei | 06.12.00 | 08.10.47 | M | ||
| Krylova | Tatiana | 28.12.93 | 22.03.68 | OG | ||
| Bersheva | Olga | 14.12.01 | 22.12.74 | OG | ||
| Rusanova | Håp | 24.05.87 | 22.01.54 | OG |
Figur 5.57 – Innstilling av filtreringsbetingelser
5. Kopier den filtrerte tabellen til ark 6 og gi den nytt navn til "AutoFilter #3. Fjern markeringen i arket AutoFilter #1.
Figur 5.58 - Egendefinert filter
6. I kolonnen "Etternavn", velg "Betingelse" i filtreringslisten og sett betingelsen for å velge alle ansatte hvis etternavn begynner med "B" (Fig. 5.58).
7. Kopier den filtrerte listen til ark 7, gi den nytt navn til "AutoFilter #4".
8. På arket "Autofilter nr. 1", for kolonnen "Etternavn" angir du "Alle", og i kolonnen "Lønn" angir du "Første 10 ..." hvor i dialogboksen skriver du inn "Vis" de 5 største listeelementene".
9. Lagre filen.
Oppgave 3. Filtrer poster fra listen ved å bruke kommandoen Avansert filter.
Metodikken for å utføre arbeidet.
1. Gå til ark 8 og gi det nytt navn til "Avansert filter".
2. Kopier tabellen fra forrige oppgave til dette arket (tabell 5.5), lim den inn fra linje 7. De første 6 linjene er reservert for innstillingsbetingelser.
3. La oss skape en rekke forhold. Anta at vi må velge navnene på ansatte som mottar mer enn 5000 rubler. Eller noen over 50 år. Fyll ut betingelsene som vist i figur 5.59.
Figur 5.59 - Forutsetninger for avansert filter
4. Kjør kommandoen Data Þ Avansert . Fyll ut dialogboksen som følger (fig. 5.60):
Figur 5.60 - Vinduet Avanserte filterparametere
Se utvalgsresultater. Når betingelser er skrevet på én linje, implementeres en logisk OG Når betingelser skrives på forskjellige linjer, anses de å være forbundet med en logisk ELLER. Vi har vurdert det første alternativet, nå vurdere det andre.
5. Anta at vi kun vil vise de ansatte hvis etternavn begynner med bokstavene A, G eller N. Fyll ut vilkårene (Figur 5.61).
Figur 5.61 - Forutsetninger for avansert filter
6. Kjør kommandoen DataÞAvansert og fyll ut dialogboksen (figur 5.62).
Figur 5.62 - Vinduet Avanserte filterparametere
Se resultatene av utvalget av poster.
1. Vis en liste over alle ansatte, lønn som er over gjennomsnittet. Før du oppretter dette filteret, skriv inn formelen =AVERAGE(F8:F20) i celle H2 for å beregne gjennomsnittslønnen.
2. Så i celle A2 legger vi inn den beregnede betingelsen =F8>$H$2, med henvisning til celle H2 (Figur 5.63 og 5.64).
Figur 5.63 - Forutsetninger for avansert filter
Figur 5.64 Avanserte filteralternativer
Et filter er en rask og enkel måte å finne og arbeide med et undersett av data i en liste. Den filtrerte listen viser bare radene som samsvarer med kriteriene. I motsetning til sortering, endrer ikke et filter rekkefølgen på oppføringene i listen. Filtrering skjuler rader som du ikke vil vise midlertidig.
Linjene som er valgt under filtrering kan redigeres, formateres, lages diagrammer basert på dem, skrives ut uten å endre rekkefølgen på linjene og uten å flytte dem.
Ved filtrering velges kun de nødvendige dataene, og resten av dataene skjules. På denne måten vises kun det du ønsker å se, og det kan gjøres med et enkelt klikk.
Filtrering endrer ikke dataene på noen måte. Når filteret er fjernet, vises alle data igjen slik de var før filteret ble brukt.
Det er to kommandoer tilgjengelig i Excel for filtrering av lister:
- Autofilter, inkludert filtrering etter valg, for enkle forhold utvalg.
- Avansert filter for mer vanskelige forhold utvalg.
Autofilter
For å aktivere Autofilter du må velge hvilken som helst celle i tabellen og deretter på fanen Data i gruppe Sortering Og filter trykk på den store knappen :
Etter det vil en knapp med pil ned vises i tabelloverskriften til høyre for hver kolonneoverskrift:
Ved å klikke på en pil åpnes en listemeny for den tilsvarende kolonnen. Listen inneholder alle elementene i en kolonne i alfabetisk eller numerisk rekkefølge (avhengig av datatypen), slik at du raskt kan finne det nødvendige elementet:
Hvis vi trenger et filter for bare én kolonne, kan vi ikke vise pilknapper for de resterende kolonnene. For å gjøre dette, før du trykker på knappen velg flere celler i den ønskede kolonnen sammen med overskriften.
Filtrering etter eksakt verdi
Slå på Autofilter, klikk på knappen med en pil og velg en verdi fra rullegardinlisten. For raskt å velge alle elementene i en kolonne eller fjerne merket for alle elementer, klikk (Velg alle) :
I dette tilfellet er alle rader som ikke inneholder den valgte verdien i feltet skjult.
Ved å gjøre laboratoriearbeid, velg resultatet av filtreringen, kopier det til et annet sted på arket og signer det.
Å skru av Autofilter må trykke på knappen igjen .
For å avbryte filterhandlingen uten å gå ut av filtreringsmodus, klikk på knappen og velg elementet fra rullegardinlisten. (Velg alle) . Samtidig vises radene i tabellen som er skjult av filteret.
Funksjoner ved datafiltrering
Filtre skjuler data. Det er det de er designet for. Men hvis du ikke vet om datafiltrering, kan du få inntrykk av at noen data mangler. Du kan for eksempel åpne noens filtrerte ark, eller til og med glemme at du selv har brukt filteret tidligere. Så når det er filtre på et ark, kan forskjellige visuelle signaler og meldinger bli funnet.
(plassert nederst til venstre i vinduet). Den opprinnelige tilstanden:
Umiddelbart etter filtrering av dataene, vises resultatet av filterapplikasjonen i nedre venstre hjørne av statuslinjen. For eksempel, " Oppføringer funnet: 2 av 11”:
Linjenummer . Ved brutte linjenummer kan du se at noen linjer er skjult, og den endrede fargen på tallene synlige linjer indikerer at de valgte radene er et resultat av filtervalg.
Type piler . Hvis du endrer AutoFilter-pilen i en filtrert kolonne til å indikere at kolonnen er filtrert.
”
"" er et annet universelt filter som kan brukes på kolonner med tall eller datoer.
"" er et svært betinget navn. Faktisk er mulighetene for dette filteret mye bredere. Med dette filteret kan du finne enten de første elementene eller de siste elementene (minste eller største tall eller datoer). Og, i motsetning til navnet på filteret, er resultatene ikke begrenset til de første 10 elementene eller de siste 10 elementene. Antall viste elementer kan velges fra 1 til 500.
” lar deg også filtrere data etter prosentandel av det totale antallet rader i kolonnen. Hvis kolonnen inneholder 100 tall og du vil se på de femten beste, velg 15 prosent.
Filteret kan brukes til å finne produktene med høyest eller lavest pris, for å finne listen over sist ansatte ansatte, eller for å se listen over elever med best eller dårligst karakter. For å bruke filteret "" på en kolonne med data ( bare tall eller datoer!!!), klikk på pilen i kolonnen og velg elementet Numeriske filtre Lengre :
Dette vil åpne en dialogboks. overlegg forhold Av liste :
Velg i dialogboksen Antall(linjer eller prosenter), størst eller minst, liste elementer eller % av antall elementer.
Lag dine egne tilpassede filtre
For eksempel vil vi kun vise rader med posisjoner som begynner med bokstaven ' D'. For å gjøre dette, klikk på autofilter-pilen i den første kolonnen og velg Tekstfiltre , så pek starte med… :
Dette vil få opp en dialogboks. (Uansett hvilket alternativ du velger til høyre, vil den samme dialogboksen fortsatt vises.):
I felt Jobbtittel velg - starte med , til høyre går vi inn d:
I vinduet det er et hint:
Spørsmålstegn " ? " står for et hvilket som helst tegn.
Signer " * ” angir en sekvens av alle tegn.
Du kan velge de nødvendige dataene fra listen ved å bruke filtrering, det vil si ved å skjule alle linjene i listen, bortsett fra de som oppfyller de angitte kriteriene. For å bruke filtreringsfunksjonen må du plassere tabellmarkøren på en av cellene i listeoverskriften (i vår tabell er dette området A1:U11) og ringe kommandoen Data/Filter/AutoFilter. Når den er aktivert, vil en liten boks med en rullegardinpil vises i nedre høyre hjørne av hver overskriftscelle.
Vurder metodene for å jobbe med autofilteret ved å bruke følgende eksempel. La oss bestemme hvor mange representanter for det sterkere kjønn som jobber i bedriften. Klikk på filterknappen i cellen med overskriften Kjønn, og velg bokstaven M (mann) fra listen som åpnes. Meldingen Filter: selection vil vises i statuslinjen (fig. 4.20). Alle rader som ikke oppfyller de angitte kriteriene vil bli skjult. Pilen på listeknappen blir blå, noe som indikerer at gitt felt autofilter aktivert.
Ris. 4.20. Bruke et autofilter for å velge poster basert på "M" (mann)
Hvis du trenger å spesifisere hvor mange sjefer som er blant disse mennene, klikker du også på autofilter-knappen i Posisjonscellen og velger ordet Boss i den tilsvarende listen. En melding vil vises i statuslinjen som angir hvor mange rader som oppfyller de angitte kriteriene: Poster funnet: 2 av 10 (det vil si at svaret vil bli gitt umiddelbart). Resultatet er vist i fig. 4.21.
For å avbryte filtrering på en bestemt kolonne, åpne bare autofilterlisten i den kolonnen og velg Alle. Men hvis filtreringsfunksjonen er definert for flere kolonner, må du gjenta denne operasjonen flere ganger. I dette tilfellet er det bedre å bruke kommandoen Data/Filter/Vis alle.
Ris. 4.21. Regneark etter filtrering av listen over ansatte etter kriteriet "mann - sjef"
Filtreringsfunksjonen vil fungere riktig hvis du er forsiktig når du legger inn data. Spesielt må du sørge for at det ikke er ekstra mellomrom i begynnelsen og slutten av tekstdataene. De er ikke synlige på skjermen, men kan føre til feilaktige resultater, og det tar mye tid å identifisere dem.
Filtrering velger data som nøyaktig oppfyller de angitte kriteriene. Derfor, hvis i stedet for ordet "Head" ordet "Head_" forekommer i kolonnen, det vil si med et mellomrom på slutten, behandler Excel disse verdiene som forskjellige. For å bli kvitt denne typen inkonsekvenser, kopier cellen med ordet "Head" til utklippstavlen, aktiver filteret for utvalget på grunnlag av "Head_" og erstatt de uriktige verdiene med innholdet i bufferen.
Du kan vise informasjon om en eller flere parametere ved å filtrere data i Excel.
Det er to verktøy for dette formålet: AutoFilter og Advanced Filter. De sletter ikke, men skjuler data som ikke passer til betingelsen. Autofilteret utfører de enkleste operasjonene. Det avanserte filteret har mange flere alternativer.
Autofilter og avansert filter i Excel
Det er en enkel tabell, ikke formatert og ikke erklært som en liste. Du kan slå på det automatiske filteret gjennom hovedmenyen.
Hvis du formaterer et dataområde som en tabell eller erklærer det som en liste, vil et automatisk filter bli lagt til umiddelbart.
Å bruke autofilteret er enkelt: du må velge oppføringen med ønsket verdi. Vis for eksempel leveranser til butikk #4. Sett et merke ved siden av den tilsvarende filtreringsbetingelsen:
Vi ser umiddelbart resultatet:
Funksjoner av verktøyet:
- Autofilteret fungerer bare i et ikke-brytende område. Ulike tabeller på samme ark filtreres ikke. Selv om de har samme type data.
- Verktøyet behandler den øverste raden som kolonneoverskrifter - disse verdiene er ikke inkludert i filteret.
- Du kan bruke flere filtreringsbetingelser samtidig. Men hvert forrige resultat kan skjule postene som er nødvendige for neste filter.
Det avanserte filteret har mange flere alternativer:
- Du kan angi så mange betingelser for filtrering som du trenger.
- Datautvelgelseskriteriene er klare.
- Med det avanserte filteret kan brukeren enkelt finne unike verdier i en multiline-array.
Hvordan lage et avansert filter i Excel
Et ferdig eksempel er hvordan du bruker et avansert filter i Excel:
I den opprinnelige tabellen var det bare rader som inneholder verdien "Moskva" igjen. For å avbryte filtrering må du klikke på "Slett"-knappen i "Sorter og filtrer"-delen.
Slik bruker du det avanserte filteret i Excel
Vurder å bruke et avansert filter i Excel for å velge rader som inneholder ordene "Moskva" eller "Ryazan". Betingelsene for filtrering må være i samme kolonne. I vårt eksempel, den ene under den andre.
Fyll ut den avanserte filtermenyen:
Vi får en tabell med rader valgt i henhold til et gitt kriterium:
La oss velge radene som inneholder verdien "No. 1" i kolonnen "Store", og "> 1 000 000 rubler" i kostnadskolonnen. Kriteriene for filtrering må være i de aktuelle kolonnene i betingelsesetiketten. På en linje.
Fyll inn filtreringsparametrene. Vi trykker OK.
La oss bare la i tabellen være de radene som inneholder ordet "Ryazan" i kolonnen "Region" eller verdien "> 10 000 000 rubler" i kolonnen "Kostnad". Siden utvalgskriteriene refererer til ulike kolonner, plasserer vi dem på ulike linjer under de tilsvarende overskriftene.
Bruk avansert filterverktøy:
Dette verktøyet kan arbeide med formler, som lar brukeren løse nesten alle oppgaver når de velger verdier fra matriser.
Grunnleggende regler:
- Resultatet av formelen er utvalgskriteriet.
- Den skrevne formelen returnerer TRUE eller FALSE.
- Kildeområdet angis ved hjelp av absolutte referanser, og utvalgskriteriet (i form av en formel) spesifiseres ved hjelp av relative referanser.
- Hvis TRUE returneres, vil raden vises etter at filteret er brukt. FALSKT - nei.
La oss vise radene som inneholder mengden over gjennomsnittet. For å gjøre dette, bortsett fra platen med kriteriene (i celle I1), vil vi skrive inn navnet "Det største tallet". Nedenfor er formelen. Vi bruker AVERAGE-funksjonen.
Velg en hvilken som helst celle i kildeområdet og kall opp "Avansert filter". Vi angir I1:I2 som et utvalgskriterium (relative lenker!).
Bare de radene gjensto i tabellen der verdiene i "Beløp"-kolonnen er over gjennomsnittet.
For å la bare rader som ikke gjentar seg i tabellen, i "Avansert filter"-vinduet, merk av i boksen ved siden av "Kun unike poster".
Klikk OK. Dupliserte linjer vil bli skjult. Bare unike poster vil forbli på arket.
Filtrering av data i listen er valg av data etter et gitt kriterium, dvs. dette er en operasjon som lar deg velge ønsket data blant de tilgjengelige.
Filtre lar deg vise og vise kun data som oppfyller visse betingelser. Excel lar deg raskt og enkelt vise de nødvendige dataene fra listen ved å bruke en enkel kommando - "AutoFilter". Mer komplekse spørsmål til databasen kan implementeres ved å bruke kommandoen "Advanced Filter".
Autofiltrering
For å utføre autofiltrering må du først kopiere kildedatabasen fra arket "Databeregning etter formler" til det nye "Autofiltrering"-arket. Sett deretter markøren i listeområdet og utfør kommandoen "Data" - "Filter" - "AutoFilter". Av dette Excel-teamet legger ned rullegardinlister direkte i listekolonnenavnene. Ved å klikke på pilen kan du se listen over mulige utvalgskriterier. Hvis knappen ble brukt til å tilordne et filter, blir pilen blå. Det er følgende kriterielistealternativer:
«Alle» - alle poster er valgt;
· "Topp 10" - i dialogboksen "Pålegge betingelser på listen" velg et visst antall av de minste eller største elementene i listen som du vil vise;
· "Verdier" - bare de postene som skaper den angitte verdien i denne kolonnen vil bli valgt;
· "Betingelse" - poster velges i henhold til tilstanden som er dannet av brukeren i dialogboksen "Egendefinert autofilter";
· "Tom" - linjer presenteres som ikke inneholder data i kolonnen;
· "Ikke-tom" - bare de postene presenteres som inneholder ikke-tomme linjer i kolonnen.
I dette tilfellet er det nødvendig å opprette følgende betingelser for operasjonen "Autofiltrering": for feltet "Fordeler" må du angi verdien "Veteran eller funksjonshemmet" og for feltet "Antall familiemedlemmer" , må du angi betingelsen - "Større enn eller lik 3". I samsvar med det faktum at filtre er satt i to kolonner samtidig, vil filtreringen av poster utføres i henhold til to forhold samtidig, det vil si at veteran- og funksjonshemmede fordeler vil bli valgt, antall familiemedlemmer hvorav er større enn eller lik 3. Som et resultat ble det funnet leietakere som tilfredsstiller vilkårene ovenfor. Dette resultatet vist i figur tabell 4 "Autofiltrering".
Avansert filter
Filtrering ved hjelp av et avansert filter utføres ved hjelp av kommandoen: "Data" - "Filter" - "Avansert filter".
For å bruke kommandoen "Advanced Filter" må du først lage en kriterietabell, som vi deretter plasserer på det samme "Advanced Filter"-regnearket som den originale "Data Calculation by Formulas"-tabellen, men på en slik måte at arket er ikke skjult under filtrering.
I "Avansert filter" så vel som i "Autofilter" er det flere alternativer for kriterietyper, for eksempel:
Sammenligningskriteriet inkluderer operasjoner av følgende type:
Den nøyaktige verdien
verdi dannet ved hjelp av relasjonsoperatorer;
en verdimal som inneholder tegn eller
Flere kriterium - et kriterium dannet i flere kolonner.
· Hvis kriteriene er spesifisert i hver kolonne på én linje, anses de for å være knyttet til AND-betingelsen.
· Hvis kriteriene er skrevet på flere linjer, anses de å være knyttet sammen av OR-betingelsen.
Beregnet kriterium - er en formel skrevet i linjen i betingelsesområdet, som returnerer den logiske verdien "TRUE" eller "FALSE".
