Filtrera data efter listvillkor. Filtrera data i listan. Filtrera med hjälp av dataformulär
Målet med arbetet: utföra datasortering, bekanta dig med metoden för att filtrera listposter, autofiltrera och arbeta med dataformulär.
Övning 1.
Sortera uppgifterna i Tabell 5.5 flera gånger i enlighet med följande kriterier - i alfabetisk ordning efter köpares efternamn, i fallande ordning på transaktionsbelopp, i stigande ordning efter transaktionsdatum, i kombination av egenskaper (efternamn, datum, belopp).
Metod för att utföra arbetet
1. Öppna en ny arbetsbok och spara den som "Sortera" i din arbetsmapp .
2. Skapa tabellen som visas i figur 5.56.
Figur 5.56 – Inledande tabell med data
3. Ställ in formateringsalternativ för tabellen.
Teckensnitt Times New Roman, teckenstorlek 12 pt., för rubriker, fet stil och mittjustering, radbrytning, grå fyllning; för huvuddelen. Som en påminnelse finns formateringskommandon tillgängliga på menyfliksområdet Hem Þ Celler .
4. För att sortera efter köparens efternamnsfält, placera markören var som helst i den här kolumnen och kör kommandot Data Þ Sortering (Bild 5.51) .
I dialogrutan som öppnas, i fältet Sortera efter Välj Köparens efternamn. Stigande.
5. Upprepa alla steg i steg 4 och ställ in sorteringen efter "Transaktionsbelopp", i fallande ordning.
6. Sortera om efter fältet "Transaktionsdatum", stigande.
7. Kopiera tabellen till nytt löv och sortera på den efter en uppsättning egenskaper. För att göra detta, ring kommandot Data Þ Sortering. Installera Sortera efter efternamn i stigande ordning, Sedan av datum i stigande ordning, Till sist, av belopp i fallande ordning.
8. Använda ett kommando Döp om Ge namn åt dessa två blad.
Uppgift 2. Välj information från listan baserat på kommandot AutoFilter.
Metod för att utföra arbetet.
1. På blad 4, skapa en tabell och fyll den med information från tabell 5.5.
2. Byt namn på Sheet4, ge det namnet "AutoFilter #1".
3. För att använda autofiltrering, placera markören i listområdet och kör kommandot Data ÞFilter. Nedåtpilar visas bredvid namnen på tabellkolumnerna och visar en lista med möjliga värden. Välj "M" i kolumnen "Kön" Kopiera tabellen till blad 5 och döp om den till "Autofilter nr 2".
4. På bladet "Autofilter No. 1", i kolumnen "Kön", öppnar du filtreringslistan och väljer "Alla". Sedan, i kolumnen "Födelsedatum", välj "Kondition" från filtreringslistan och ställ in villkoret (Fig. 5.57):
Tabell 5.5
| Efternamn | namn | anställningsdatum | Födelsedatum | Golv | Lön | Ålder |
| Pasjkov | Igor | 16.05.74 | 15.03.49 | M | ||
| Andreeva | Anna | 16.01.93 | 19.10.66 | OCH | ||
| 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 | OCH | ||
| 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 | OCH | ||
| Bersheva | Olga | 14.12.01 | 22.12.74 | OCH | ||
| Rusanova | Hoppas | 24.05.87 | 22.01.54 | OCH |
Figur 5.57 – Inställning av filtreringsvillkor
5. Kopiera den filtrerade tabellen till blad 6 och döp om den till "Autofilter No. 3. Avmarkera på arket AutoFilter nr. 1.
Figur 5.58 – Anpassat filter
6. I kolumnen "Efternamn", välj "Villkor" i filtreringslistan och ställ in ett villkor för att välja alla anställda vars efternamn börjar med "B" (Fig. 5.58).
7. Kopiera den filtrerade listan till blad 7 och döp om den till "Autofilter nr. 4".
8. På bladet "Autofilter nr 1" för kolumnen "Efternamn" ställ in "Alla", och i kolumnen "Lön" ställ in "Första 10..." där i dialogrutan anger "Visa de 5 största elementen på listan”.
9. Spara filen.
Uppgift 3. Välj poster från listan med kommandot Avancerat filter.
Metodik för att utföra arbetet.
1. Gå till blad 8 och döp om det till "Avancerat filter".
2. Kopiera tabellen från föregående uppgift (tabell 5.5) till detta ark, klistra in den från rad 7. De första 6 raderna är reserverade för inställningsvillkor.
3. Låt oss skapa en rad villkor. Anta att vi måste välja namnen på anställda som tjänar mer än 5 000 rubel. Eller vars ålder överstiger 50 år. Fyll i villkoren som visas i figur 5.59.
Figur 5.59 – Villkor för ett avancerat filter
4. Kör kommandot Data Þ Ytterligare . Fyll i dialogrutan enligt följande (Fig. 5.60):
Figur 5.60 – Fönstret Avancerade filterparametrar
Se urvalsresultaten. När du skriver villkor på en rad implementeras logisk AND. När du skriver villkor på olika rader anses de vara sammankopplade med logiskt ELLER. Vi har övervägt det första alternativet, nu ska vi överväga det andra.
5. Anta att vi bara behöver visa de anställda vars efternamn börjar med bokstäverna A, G eller N. Fyll i villkoren (Figur 5.61).
Figur 5.61 – Villkor för ett avancerat filter
6. Kör kommandot DataÞ Ytterligare och fyll i dialogrutan (Figur 5.62).
Figur 5.62 – Fönstret Avancerade filterparametrar
Se resultatet av urvalet av poster.
1. Lista alla anställda lön som är mer än genomsnittet. Innan du skapar det här filtret anger du formeln =MEDEL(F8:F20) i cell H2 för att beräkna medellönen.
2. Sedan anger vi i cell A2 det beräknade villkoret =F8>$H$2, vilket hänvisar till cell H2 (figur 5.63 och 5.64).
Figur 5.63 – Villkor för ett avancerat filter
Figur 5.64 – Avancerade filterparametrar
Ett filter är ett snabbt och enkelt sätt att hitta en delmängd av data och arbeta med den i en lista. Den filtrerade listan visar endast rader som uppfyller kriterierna. Till skillnad från sortering ändrar inte ett filter ordningen på poster i listan. Filtrering döljer tillfälligt rader som du inte vill visa.
Rader valda genom filtrering kan redigeras, formateras, skapas till diagram och skrivas ut utan att ändra radordningen eller flytta dem.
Filtrering väljer endast nödvändig data och döljer återstående data. På så sätt visas bara det du vill se, och det kan göras med ett klick.
Vid filtrering ändras inte data på något sätt. När filtret har tagits bort visas all data igen i samma form som den var innan filtret användes.
Det finns två kommandon tillgängliga i Excel för att filtrera listor:
- Autofilter, inklusive filtrera efter urval, för enkla förhållanden urval.
- Avancerat filter för mer svåra förhållanden urval.
Autofilter
Att möjliggöra Autofilter du måste markera valfri cell i tabellen och sedan på fliken Data i grupp Sortering Och filtrera tryck på den stora knappen :
Efter detta visas en nedåtpil i tabellrubriken till höger om varje kolumnrubrik:
Genom att klicka på en pil öppnas en listmeny för motsvarande kolumn. Listan innehåller alla element i en kolumn i alfabetisk eller numerisk ordning (beroende på datatyp), så att du snabbt kan hitta det element du behöver:
Om vi behöver ett filter för endast en kolumn, behöver vi inte visa pilknappar för de återstående kolumnerna. För att göra detta, innan du trycker på knappen markera flera celler i den önskade kolumnen tillsammans med rubriken.
Filtrera efter exakt värde
Sätta på Autofilter, klicka på pilknappen och välj ett värde från rullgardinsmenyn. För att snabbt markera alla element i en kolumn eller avmarkera alla element, klicka på objektet (Välj alla) :
I det här fallet är alla rader vars fält inte innehåller det valda värdet dolda.
Genom att göra laboratoriearbete, välj filtreringsresultatet, kopiera det till en annan plats på arket och signera det.
Att stänga av Autofilter du måste trycka på knappen igen .
För att avbryta filteråtgärden utan att lämna filtreringsläget, klicka på knappen och välj objektet från rullgardinsmenyn (Välj alla) . I det här fallet visas tabellrader dolda av filtret.
Tecken på datafiltrering
Filter döljer data. Det är precis vad de är designade för. Men om datafiltrering inte är känd kan det verka som om vissa data saknas. Du kan till exempel öppna någon annans filtrerade ark, eller till och med glömma att du själv tidigare använde ett filter. Så när du har filter på ett ark kan du hitta olika visuella ledtrådar och meddelanden.
(finns längst ner till vänster i fönstret). Det initiala tillståndet:
Omedelbart efter att data har filtrerats visas resultatet av tillämpningen av filtret i det nedre vänstra hörnet av statusfältet. Till exempel, " Poster hittades: 2 av 11”:
Radnummer . De streckade linjenumren indikerar att vissa linjer är dolda, och den ändrade färgen på siffrorna synliga linjer indikerar att de markerade raderna är resultatet av ett filterval.
Typ av pil . När AutoFilter-pilen i en filtrerad kolumn ändras till, indikerar det att kolumnen är filtrerad.
”
"" är ett annat universellt filter som kan tillämpas på kolumner med siffror eller datum.
"" är ett mycket vanligt namn. Faktum är att kapaciteten hos detta filter är mycket bredare. Med detta filter kan du hitta antingen de första elementen eller de sista elementen (minsta eller största siffror eller datum). Och, i motsats till filtrets namn, är de erhållna resultaten inte begränsade till de första 10 elementen eller de sista 10 elementen. Antalet objekt som visas kan väljas från 1 till 500.
” låter dig också filtrera data efter procent av det totala antalet rader i en kolumn. Om en kolumn innehåller 100 siffror och du vill se de största femton, välj sedan 15 procent.
Du kan använda filtret för att hitta produkter med de högsta eller lägsta priserna, för att bestämma listan över senast anställda anställda eller för att se en lista över elever med de bästa eller sämsta betygen. För att tillämpa filtret "" på en datakolumn ( bara siffror eller datum!!!), klicka på pilen i kolumnen och välj objektet Numeriska filter Ytterligare :
Efter detta öppnas en dialogruta Täcka över betingelser Förbi lista :
Välj i dialogrutan siffra(rader eller procentsatser), största eller minsta, lista element eller % av antalet element.
Skapa dina egna anpassade filter
Till exempel behöver vi bara mata ut rader med positioner som börjar med bokstaven ' D’. För att göra detta, klicka på autofilterpilen i den första kolumnen och välj Textfilter , sedan objekt börja med… :
En dialogruta visas (Oavsett vilket objekt till höger du väljer, kommer samma dialogruta fortfarande att visas.):
I fält Jobbtitel välj - börja med , till höger går vi in d:
I fönstret det finns ett tips:
Frågetecken " ? ” betyder vilket tecken som helst.
Skylt " * ” anger en sekvens av alla tecken.
Du kan välja nödvändig data från listan med filtrering, det vill säga genom att dölja alla rader i listan utom de som uppfyller de angivna kriterierna. För att använda filtreringsfunktionen måste du placera tabellmarkören på en av listrubrikcellerna (i vår tabell är detta intervallet A1:U11) och anropa kommandot Data/Filter/AutoFilter. När den har aktiverats kommer en liten fyrkant med en rullgardinspil att visas i det nedre högra hörnet av varje rubrikcell.
Låt oss titta på hur man arbetar med ett autofilter med hjälp av följande exempel. Låt oss bestämma hur många representanter för det starkare könet som arbetar på företaget. Klicka på filterknappen i cellen med rubriken Kön och välj bokstaven M (man) från listan som öppnas. Meddelandet Filter: val visas i statusfältet (fig. 4.20). Alla rader som inte uppfyller de angivna kriterierna kommer att döljas. Pilen på listknappen blir blå för att indikera det av detta fält Autofilter är aktiverat.
Ris. 4,20. Använda ett autofilter för att välja poster baserat på "M" (man)
Om du vill förtydliga hur många av dessa män som är chefer, klicka även på autofiltreringsknappen i Positionscellen och välj ordet Chef i motsvarande lista. Ett meddelande visas i statusfältet som anger hur många rader som uppfyller det angivna kriteriet: Poster hittades: 2 av 10 (det vill säga svaret kommer att ges omedelbart). Resultatet visas i fig. 4.21.
För att avbryta filtrering efter en specifik kolumn, öppna bara listan AutoFilter i den kolumnen och välj Alla. Men om filtreringsfunktionen är inställd på flera kolumner, måste du upprepa denna operation flera gånger. I det här fallet är det bättre att använda kommandot Data/Filter/Visa alla.
Ris. 4.21. Arbetsblad efter filtrering av listan över anställda efter kriteriet "manlig chef"
Filtreringsfunktionen fungerar korrekt om du är försiktig när du anger data. I synnerhet måste du se till att det inte finns några extra mellanslag i början och slutet av textdata. De märks inte på skärmen, men kan leda till felaktiga resultat, och mycket tid ägnas åt att identifiera dem.
Filtrering väljer data som exakt uppfyller ett givet kriterium. Därför, om istället för ordet "Head" ordet "Head_" visas i en kolumn, det vill säga med ett mellanslag i slutet, behandlar Excel dessa värden som olika. För att bli av med inkonsekvenser av detta slag, kopiera cellen med ordet "Boss" till urklippet, aktivera filtret för att välja med "Boss_" och ersätt de felaktiga värdena med innehållet i bufferten.
Du kan visa information om en/flera parametrar med hjälp av datafiltrering i Excel.
Det finns två verktyg för detta ändamål: AutoFilter och Advanced Filter. De raderar inte, utan döljer data som inte uppfyller villkoren. Autofilter utför de enklaste operationerna. Det avancerade filtret har mycket fler alternativ.
AutoFilter och Advanced Filter i Excel
Jag har en enkel tabell som inte är formaterad eller deklarerad som en lista. Du kan aktivera det automatiska filtret via huvudmenyn.
Om du formaterar dataområdet som en tabell eller deklarerar det som en lista, kommer det automatiska filtret att läggas till omedelbart.
Att använda ett autofilter är enkelt: du måste välja posten med önskat värde. Till exempel visa leveranser till butik nr 4. Placera en bock bredvid motsvarande filtreringsvillkor:
Vi ser direkt resultatet:
Funktioner hos verktyget:
- Autofiltret fungerar bara i ett oavbrutet område. Olika tabeller på samma ark filtreras inte. Även om de har samma typ av data.
- Verktyget behandlar den översta raden som kolumnrubriker - dessa värden ingår inte i filtret.
- Det är tillåtet att tillämpa flera filtreringsvillkor samtidigt. Men varje tidigare resultat kan dölja de poster som behövs för nästa filter.
Det avancerade filtret har mycket fler alternativ:
- Du kan ställa in så många filtreringsvillkor som behövs.
- Kriterierna för att välja data är synliga.
- Med hjälp av det avancerade filtret kan användaren enkelt hitta unika värden i en multi-line array.
Hur man gör ett avancerat filter i Excel
Ett färdigt exempel - hur man använder ett avancerat filter i Excel:
Endast raderna som innehåller värdet "Moskva" fanns kvar i den ursprungliga tabellen. För att avbryta filtreringen måste du klicka på knappen "Rensa" i avsnittet "Sortera och filtrera".
Hur man använder det avancerade filtret i Excel
Låt oss överväga att använda ett avancerat filter i Excel för att välja rader som innehåller orden "Moskva" eller "Ryazan". Filtreringsvillkoren måste finnas i samma kolumn. I vårt exempel - under varandra.
Fyller i den avancerade filtermenyn:
Vi får en tabell med rader valda enligt ett givet kriterium:
Låt oss välja rader som innehåller värdet "Nr. 1" i kolumnen "Butik" och ">1 000 000 rubel" i kostnadskolumnen. Kriterierna för filtrering måste finnas i lämpliga kolumner i villkorstabellen. På en rad.
Fyll i filtreringsparametrarna. Klicka på OK.
Låt oss bara lämna i tabellen de rader som innehåller ordet "Ryazan" i kolumnen "Region" eller värdet ">10 000 000 rubel" i kolumnen "Kostnad". Eftersom urvalskriterierna tillhör olika kolumner placerar vi dem på olika rader under motsvarande rubriker.
Låt oss använda verktyget "Avancerat filter":
Detta verktyg kan arbeta med formler, vilket gör att användaren kan lösa nästan alla problem när man väljer värden från matriser.
Grundläggande regler:
- Resultatet av formeln är urvalskriteriet.
- Den skrivna formeln returnerar TRUE eller FALSE.
- Det initiala intervallet anges med absoluta referenser, och urvalskriteriet (i form av en formel) anges med relativa.
- Om TRUE returneras kommer raden att visas efter att filtret har tillämpats. FALSK - nej.
Låt oss visa rader som innehåller kvantiteter över genomsnittet. För att göra detta, förutom plattan med kriterierna (i cell I1), ange namnet "Största kvantitet". Nedan är formeln. Vi använder funktionen AVERAGE.
Välj valfri cell i källområdet och anrop "Avancerat filter". Vi anger I1:I2 som urvalskriterium (relativa länkar!).
Endast de rader där värdena i kolumnen "Quantity" är över genomsnittet finns kvar i tabellen.
För att endast lämna rader som inte upprepas i tabellen, i fönstret "Avancerat filter", markera rutan bredvid "Endast unika poster".
Klicka på OK. Dubbletter kommer att döljas. Endast unika poster kommer att finnas kvar på arket.
Att filtrera data i en lista är att välja data enligt ett givet kriterium, dvs. Detta är en operation som låter dig välja nödvändiga data från de tillgängliga.
Med hjälp av filter kan du endast visa och visa data som uppfyller vissa villkor. Excel låter dig snabbt och bekvämt se nödvändiga data från listan med ett enkelt kommando - "Autofiltrering". Mer komplexa frågor till databasen kan implementeras med kommandot "Advanced Filter".
Autofiltrering
För att utföra autofiltrering måste du initialt kopiera källdatabasen från bladet "Databeräkning med formler" till ett nytt "Autofiltrering"-ark. Placera sedan markören i listområdet och utför kommandot "Data" - "Filter" - "Autofilter". Av det här Excel-team placerar rullgardinslistor direkt i listkolumnnamn. Genom att klicka på pilen kan du se en lista över möjliga urvalskriterier. Om knappen användes för att tilldela ett filter, blir pilen blå. Följande alternativ för kriterielistan är tillgängliga:
· "Alla" - alla poster är valda;
· "Topp 10" - i dialogrutan "Att införa ett villkor på en lista" väljer du ett visst antal av de minsta eller största elementen i listan som du vill visa;
· "Värden" - endast de poster som skapar det angivna värdet i denna kolumn kommer att väljas;
· "Villkor" - poster väljs baserat på ett användargenererat villkor i dialogrutan "Anpassat autofilter";
· "Tom" - rader presenteras som inte innehåller data i kolumnen;
· "Non-empty" - endast de poster som innehåller icke-tomma rader i kolumnen visas.
I det här fallet är det nödvändigt att skapa följande villkor för operationen "Autofiltrering": för fältet "Fördelar" måste du ställa in värdet "Veteran eller funktionshindrad", och för fältet "Antal familjemedlemmar" måste ställa in villkoret - "Större än eller lika med 3." I enlighet med det faktum att filter installeras i två kolumner samtidigt, kommer filtreringen av poster att utföras enligt två villkor samtidigt, det vill säga som ett resultat kommer veteran- och handikappförmåner att väljas, antalet familjemedlemmar varav är större än eller lika med 3. Som ett resultat har man hittat hyresgäster som uppfyller ovanstående villkor. Detta resultat presenteras i figur tabell 4 "Autofiltrering".
Avancerat filter
Filtrering med ett avancerat filter utförs med kommandot: "Data" - "Filter" - "Avancerat filter".
För att använda kommandot "Advanced Filter" måste du först skapa en kriterietabell, som vi sedan placerar på samma "Advanced Filter"-kalkylblad som den ursprungliga tabellen "Data Calculation by Formulas", men för att inte dölja arket under filtrering.
I "Avancerat filter" såväl som i "Autofilter" finns det flera alternativ för olika typer av kriterier, till exempel:
Jämförelsekriteriet inkluderar operationer av följande typ:
· exakt värde;
· värden som skapas med hjälp av relationsoperatorer;
ett värdemönster inklusive tecken eller
Flera kriterium - ett kriterium bildat i flera kolumner.
· Om kriterierna anges i varje kolumn på en rad, anses de vara bundna av OCH-villkoret.
· Om kriterier skrivs på flera rader, anses de vara sammankopplade med ett ELLER-villkor.
Beräknat kriterium - är en formel skriven på en rad i villkorsområdet som returnerar det logiska värdet "TRUE" eller "FALSE".
