Når man arbejder med Excel-tabeller, er det ofte nødvendigt at udføre udvælgelsen på et bestemt kriterium eller i flere forhold. Programmet kan gøre dette på forskellige måder ved hjælp af en række værktøjer. Lad os finde ud af, hvordan man laver en prøve i Excele ved hjælp af en række muligheder.
Prøveudtagning
Dataprøven består i udvælgelsesproceduren fra det samlede udvalg af de resultater, der opfylder de angivne betingelser, efterfulgt af udgangen af dem på et ark på en separat liste eller i kildesortimentet.Metode 1: Påfør en udvidet autofilt
Den nemmeste måde at gøre udvælgelsen er brugen af en udvidet autofilter. Overvej hvordan man gør dette på et bestemt eksempel.
- Vælg området på arket, blandt de data, som du vil lave en prøve. Klik på knappen "Sorter og filter" i hjemmet. Den er placeret i redigeringsindstillingsblokken. I listen, der åbner efter denne liste, skal du klikke på knappen "Filter".
Der er mulighed for at gøre og anderledes. For at gøre dette, efter at have valgt området på arket, flyttes vi til fanen "Data". Klik på knappen "Filter", som er placeret på båndet i sorterings- og filtergruppen.
- Efter denne handling i bordoverskriften synes piktogrammer at begynde at filtrere i form af kanterne af små trekanter i den højre kant af cellerne. Klik på dette ikon i titlen på den kolonne, hvorefter vi ønsker at lave en prøve. I menuen Menu, skal du gå igennem "Tekstfiltre" -genstanden. Vælg derefter POSITION "Customizable Filter ...".
- Brugerfiltreringsvinduet er aktiveret. I det kan du indstille en grænse, som udvælgelsen vil blive foretaget. I rullemenuen for en kolonne i den numeriske formatcelle, som vi bruger for eksempel, kan du vælge en af fem typer betingelser:
- lige med;
- ikke lige;
- mere;
- mere eller lige;
- mindre.
Lad os sætte tilstanden som et eksempel for at vælge kun de værdier, for hvilke antallet af indtægter overstiger 10.000 rubler. Vi etablerer en switch til "mere" position. I højre felt passer værdien "10.000". For at udføre handlingen skal du klikke på knappen "OK".
- Som vi ser efter filtrering, forblev kun linjer, hvor antallet af indtægter overstiger 10.000 rubler.
- Men i samme kolonne kan vi tilføje den anden betingelse. For at gøre dette, kom tilbage til det brugerdefinerede filtreringsvindue. Da vi kan se, er der i sin nederste del en anden switchtilstand og det tilsvarende felt for input. Lad os nu installere den øvre udvælgelsesgrænse på 15.000 rubler. For at gøre dette skal du sætte kontakten til "mindre" position, og i feltet til højre passer værdien "15000".
Derudover er der stadig en omskifter af forhold. Han har to bestemmelser "og" og "eller". Som standard er den installeret i den første position. Det betyder, at kun linjer vil forblive i stikprøven, der opfylder begge restriktioner. Hvis den er indstillet til "eller" position, vil de værdier, der er egnede til nogen af de to betingelser, forbliver. I vores tilfælde skal du indstille kontakten til "og" -positionen, det vil sige, forlad denne standardindstilling. Når al værdi er indtastet, skal du klikke på knappen OK.
- Nu forblev kun linjer i tabellen, hvor antallet af indtægter ikke er mindre end 10.000 rubler, men overstiger ikke 15.000 rubler.
- Tilsvarende kan du konfigurere filtre i andre kolonner. Det er muligt at opretholde filtreringen og på tidligere forhold, der blev indstillet i kolonnerne. Så lad os se, hvordan udvælgelsen er taget ved hjælp af filteret til datoformatet. Klik på filtreringsikonet i den tilsvarende kolonne. Konsekvent ved at klikke på listen "Filter efter dato" og filteret.
- Det brugerdefinerede AutoFilt-vindue starter igen. Udfør udvælgelsen af resultater i tabellen fra 4. til 6. maj 2016 inklusive. I udvælgelseskontakten, som du kan se, endnu flere muligheder end for et numerisk format. Vælg positionen "Efter eller Equal". I feltet til højre skal du sætte værdien "04.05.2016". I bundblokken sætter vi kontakten til positionen "op til eller lige". I det rigtige felt skal du indtaste værdien "06.05.2016". Tilstand Kompatibilitetsafbryder Forlad i standardpositionen - "og". For at anvende filtrering i aktion, tryk på knappen "OK".
- Som du kan se, er vores liste faldet endnu mere. Nu er kun linjer tilbage i den, hvor antallet af omsætning varierer fra 10.000 til 15.000 rubler for perioden fra 04.05 til 06.05.2016 inklusive.
- Vi kan nulstille filtrering i en af kolonnerne. Lad os gøre det for indtægtsværdier. Klik på ikonet Autofilter i den tilsvarende kolonne. Klik på "Slet filter" i rullelisten.
- Når du kan se, efter disse handlinger, vil prøven med mængden af indtægter blive deaktiveret, men vil kun tage udvælgelsen efter dato (fra 04/05/2016 til 06.05.2016).
- Denne tabel har en anden kolonne - "Navn". Den indeholder data i tekstformat. Lad os se, hvordan du danner en prøve ved hjælp af filtrering gennem disse værdier.
Klik på filterikonet i navnet på kolonnen. Gå konsekvent til navnene på listen "Tekstfiltre" og "Customizable Filter ...".
- Vinduet Bruger AutoFilter åbnes igen. Lad os lave en prøve ved navnene på "kartofler" og "kød". I den første blok sættes betingelserne indstillet til positionen "lige". I feltet til højre for det passer ordet "kartofler". Afbryderen af den nederste enhed vil også sætte "lige" positionen. I feltet modsat det, laver jeg en rekord - "Kød". Og nu udfører vi, hvad de ikke tidligere havde gjort: Indstil kompatibilitetsafbryderen til positionen "eller". Nu vises linjen, der indeholder nogen af de angivne betingelser på skærmen. Klik på knappen "OK".
- Som du kan se, er der begrænsninger på datoen i den nye prøve (fra 04.05.2016 til 06.05.2016) og ved navn (kartofler og kød). Der er ingen begrænsninger for mængden af indtægter.
- Du kan helt fjerne filteret med de samme metoder, der blev brugt til at installere det. Og det betyder ikke noget, hvilken metode der blev brugt. For at nulstille filtreringen, mens du er i fanen "Data", skal du klikke på knappen "Filter", som er placeret i gruppen "Sorter og Filter".
Den anden mulighed indebærer overgangen til fanen "Home". Vi udfører et klik på båndet på knappen "Sorter og filter" i redigeringsenheden. Klik på knappen "Filter" i den aktiverede liste.
Ved anvendelse af en af de to ovennævnte metoder vil filtrering blive slettet, og prøvningsresultaterne rengøres. Det vil sige, at bordet vil blive vist hele arrayet af de data, den har.
Lektie: Automatisk filterfunktion i Excel
Metode 2: Anvendelse af arrayformel
Gør markeringen kan også anvende den komplekse formel af arrayet. I modsætning til den tidligere version sørger denne metode til udgangen af resultatet i en separat tabel.
- På samme ark opretter vi et tomt bord med de samme navne på kolonner i overskriften som kilden.
- Allokere alle tomme celler i den første kolonne i det nye bord. Installer markøren i formlen streng. Lige her vil blive indtastet ved en formel, der producerer en prøve på de angivne kriterier. Vi vælger linjen, mængden af indtægter, der overstiger 15.000 rubler. I vores særlige eksempel vil den indførte formel se ud som følger:
= Indeks (A2: A29; mindste (hvis (15000
Naturligvis vil adressen på celler og intervaller i hvert enkelt tilfælde være din. På dette eksempel kan du sammenligne formlen med koordinaterne på illustrationen og tilpasse den til dine behov.
- Da dette er en array formel, for at anvende den i aktion, skal du trykke på ENTER-knappen, men CTRL + SHIFT + ENTER-tastekombination. Vi gør det.
- Efter at have fremhæver den anden kolonne med datoer og installerer markøren i formlen String, introducerer vi følgende udtryk:
= Indeks (B2: B29; mindste (hvis (15000
Klik på Ctrl + Shift + Enter-tastekombinationen.
- Tilsvarende i en kolonne med omsætning, indtast formlen for følgende indhold:
= Indeks (C2: C29; mindste (hvis (15000
Indtast igen CTRL + SHIFT + ENTER-tastekombinationen.
I alle tre tilfælde ændres kun den første værdi af koordinaterne, og resten af formlen er helt identisk.
- Som vi ser, er bordet fyldt med data, men dets udseende er ikke helt attraktivt, desuden er værdierne af datoer fyldt i det forkert. Det er nødvendigt at rette op på disse mangler. Datoets ukorrekthed er relateret til, at formatet af cellerne i den tilsvarende kolonne er almindeligt, og vi skal indstille datoformatet. Vi fremhæver hele kolonnen, herunder celler med fejl, og klik på, der fremhæver højre museknap. I listen, der vises, skal du gå igennem "celleformatet ...".
- I formateringsvinduet, der åbnes, skal du åbne fanen "Nummer". I de "numeriske formater" -blok tildele "Date" -værdien. På højre side af vinduet kan du vælge den ønskede datevisningstype. Når indstillingerne udstilles, skal du klikke på knappen "OK".
- Nu vises datoen korrekt. Men som vi ser, er hele bunden af bordet fyldt med celler, der indeholder den fejlagtige værdi "# nummer!". I det væsentlige er disse disse celler, data fra prøven, som de ikke havde nok. Det ville være mere attraktivt, hvis de blev vist overhovedet tomme. Til disse formål bruger vi betinget formatering. Vi fremhæver alle tabelceller, undtagen for overskriften. Mens du er i fanen Startside, skal du klikke på knappen "Betinget Formatering", som er i "Styles" Tool Block. I listen, der vises, skal du vælge "Opret regel ..." -genstanden.
- I vinduet, der åbnes, vælg typen af regel "Format kun celler, der indeholder". I det første felt under indskriften "format kun de celler, for hvilke følgende tilstand" vælger "fejlen". Klik derefter på knappen "Format ...".
- I formateringsvinduet kører, gå til fanen "Font" og vælg Hvid i det relevante felt. Efter disse handlinger skal du klikke på knappen "OK".
- På knappen med nøjagtigt samme navn skal du klikke på, når du vender tilbage til vinduet Oprettelse af adfærd.
Nu har vi en færdig prøve på den angivne grænse i et separat korrekt dekoreret bord.
Lektie: Betinget formatering i Excel
Metode 3: Prøveudtagning på flere betingelser ved anvendelse af formlen
Ligesom når du bruger filteret, kan du bruge formlen, du kan vælge på flere betingelser. Tag for eksempel samme kildebord, såvel som et tomt bord, hvor resultaterne vil blive output, med allerede udført numerisk og betinget formatering. Vi vil etablere den første begrænsning af den nederste grænse af udvælgelsen af indtægter i 15.000 rubler, og den anden betingelse for den øvre grænse på 20.000 rubler.
- Indtast i en separat kolonne, grænsevilkår for prøveudtagning.
- Som i den foregående metode fordeler skiftevis de tomme kolonner i det nye bord og indtast de tilsvarende tre formler i dem. I den første kolonne introducerer vi følgende udtryk:
= Indeks (A2: A29; mindste (hvis (($ D $ 2 = C2: C29); linje (C2: C29); ""); String (C2: C29) -strrk ($ C $ 1)) - Linje ($ C $ 1))
I de efterfølgende kolonner passer det nøjagtigt de samme formler, kun ved at ændre koordinaterne umiddelbart efter navnet på operatøren, indekset til de tilsvarende kolonner, vi har brug for analogt med den tidligere metode.
Hver gang efter indtastning må du ikke glemme at få CTRL + SHIFT + ENTER-tastekombination.
- Fordelen ved denne metode Før den forrige er, at hvis vi ønsker at ændre grænserne for prøven, vil det ikke være nødvendigt at ændre selve solidformlen, som i sig selv er ret problematisk. Det er nok i kolonnen af betingelser på arket for at ændre grænsestallene på dem, der er nødvendige af brugeren. Resultaterne af udvælgelsen ændres straks automatisk.
Metode 4: Tilfældig prøveudtagning
I eksilet, ved hjælp af en særlig formel, er det også muligt at anvende tilfældigt valg. Det kræves i nogle tilfælde, når du arbejder med en stor mængde data, når du har brug for at præsentere et fælles billede uden en omfattende analyse af alle data af arrayet.
- Til venstre for bordet spring over en kolonne. I cellen i den næste søjle, som er placeret modsat den første celle med dataene i tabellen, skal du indtaste formlen:
= Klæbemiddel ()
Denne funktion viser et tilfældigt nummer. For at aktivere det skal du klikke på Enter-knappen.
- For at lave en hel søjle af tilfældige tal skal markøren til nederste højre hjørne af cellen, som allerede indeholder formlen. Fyldemarkøren vises. Jeg strækker det ned med venstre museknap parallelt med bordet med dataene indtil dens ende.
- Nu har vi en række celler fyldt med tilfældige tal. Men han indeholder formlen for Calc. Vi skal arbejde med rene værdier. For at gøre dette skal du kopiere til en tom kolonne til højre. Vælg rækkevidde af celler med tilfældige tal. Beliggende i fanen "Home", klik på ikonet "Kopier" på båndet.
- Vi fremhæver en tom kolonne og klikker på højreklik, og kalder kontekstmenuen. I værktøjslinjen "Indsæt parametre" skal du vælge "Value" -klausulen, afbildet som piktogrammer med tal.
- Derefter skal du klikke på det allerede velkendte ikon på ikonet "Sorter og filter" i fanen "Home". I rullemenuen skal du stoppe valget på "Custom Sortering" -genstanden.
- Vinduet Sorteringsindstillinger er aktiveret. Sørg for at installere et tick modsat parameteren "Mine data indeholder overskrifter", hvis hætten er tilgængelig, men ingen flåter. Angiv navnet på den kolonne, hvor de kopierede værdier af tilfældige numre er indeholdt i feltet "Sorter efter". I feltet "Sorter" skal du forlade standardindstillingerne. I feltet "Ordre" kan du vælge parameteren som "stigende" og "faldende". For en tilfældig prøve har denne værdi ikke. Når indstillingerne er fremstillet, skal du klikke på knappen "OK".
- Derefter er alle værdier af tabellen bygget i stigende rækkefølge eller fald i tilfældige tal. Du kan tage et hvilket som helst nummer af de første linjer fra bordet (5, 10, 12, 15 osv.), Og de kan betragtes som resultatet af en tilfældig prøve.
Lektie: Sortering og filtrering af data til Excel
Som du kan se, kan prøven i Excel-tabellen produceres, både ved hjælp af autofilter og påføring af specielle formler. I det første tilfælde vil resultatet blive vist i kildetabellen, og i det andet - i et separat område. Det er muligt at producere udvælgelse, både en tilstand og flere. Derudover kan du lave en tilfældig prøve ved hjælp af klæbemiddelfunktionen.