Andmete filtreerimine Excelis
Kirjutasin hiljuti artikli kokkuvõttefunktsioonide kasutamise kohta Excelis, et koondada suuri andmemahtusid, kuid see artikkel võttis arvesse kõiki töölehe andmeid. Mis siis, kui tahate vaadata ainult alamhulka ja kokku võtta andmete alamhulka?
Excelis saate luua filtreid veergudes, mis peidavad ridu, mis ei vasta teie filtrile. Lisaks saate Excelis kasutada ka erifunktsioone andmete kokkuvõtmiseks, kasutades ainult filtreeritud andmeid.
Käesolevas artiklis räägin teid läbi filtrite loomise samme Excelis ja ka sisseehitatud funktsioone kasutades, et kokku võtta filtreeritud andmed.
Loo Excelis lihtsad filtrid
Excelis saate luua lihtsaid filtreid ja keerukaid filtreid. Alustame lihtsate filtritega. Filtritega töötamisel peaksite ülaosas alati olema üks rida, mida kasutatakse siltide jaoks. See ei ole nõue, et see rida oleks, kuid see muudab töö filtritega veidi lihtsamaks.
Üle, mul on mõned võltsitud andmed ja tahan luua filtri Linn veerg. Excelis on see väga lihtne. Mine edasi ja kliki Andmed klõpsa lintil ja klõpsa seejärel Filter nuppu. Sa ei pea lehel andmeid valima ega esimeses reas klikkima.
Kui klõpsate nuppu Filter, on igal esimesel real olev veerg automaatselt väikese rippmenüüga paremal.
Nüüd mine edasi ja kliki rippmenüüst linna veerus. Näete paari erinevat valikut, mida ma allpool selgitan.
Üles, saate kiiresti sorteerida kõik väärtused linna veerus. Pange tähele, et andmete sorteerimisel liigub see kogu rida, mitte ainult väärtustesse linna veerus. See tagab, et teie andmed jäävad samaks, nagu see oli varem.
Samuti on hea lisada esiplaanile veerg, mida nimetatakse ID-ks, ja nummerdada see ühest kuni paljude töölehe ridade hulka. Nii saate alati sortida ID-veeru järgi ja saada oma andmed samas järjekorras, kui see algselt oli, kui see on teile tähtis.
Nagu näete, sorteeritakse kõik arvutustabelis olevad andmed linna veeru väärtuste alusel. Seni ei ole ühtegi rida peidetud. Nüüd vaatame filtri dialoogi allosas olevaid märkeruute. Minu näites on linna veerus ainult kolm unikaalset väärtust ja need kolm loendis kuvatakse.
Ma läksin edasi ja kontrollisin kahte linna ning jätsin ühe kontrollima. Nüüd on mul ainult 8 rida andmeid ja ülejäänud on peidetud. Te saate hõlpsasti öelda, et vaatate filtreeritud andmeid, kui kontrollite rida numbreid vasakul vasakul. Sõltuvalt sellest, mitu rida on peidetud, näete mõningaid ekstra horisontaaljooni ja numbrite värv on sinine.
Oletame nüüd, et tahan filtreerida teises veerus tulemuste arvu vähendamiseks. C-veerus on igas perekonnas liikmete koguarv ja ma soovin näha ainult rohkem kui kahe liikmega perede tulemusi.
Mine edasi ja klõpsa veerus C rippmenüüst ning näete samu märkeruute iga veeru unikaalse väärtuse kohta. Sellisel juhul tahame aga klõpsata Numbrifiltrid ja seejärel klõpsake nuppu Suurem kui. Nagu näete, on ka teisi võimalusi.
Ilmub uus dialoog ja siin saab sisestada filtri väärtuse. Samuti saate lisada rohkem kui ühe kriteeriumi AND- või OR-funktsiooniga. Võiks öelda, et soovite ridu, kus väärtus on suurem kui 2 ja mitte võrdne 5-ga.
Nüüd olen ma ainult 5 rida andmeid: perekonnad ainult New Orleansist ja 3 või enama liikmega. Kas piisavalt lihtne? Pange tähele, et saate veerust filtri kergesti kustutada, klõpsates rippmenüüst ja seejärel klõpsates nuppu Filtri kustutamine „veeru nimest” link.
Nii on see lihtsate filtrite jaoks Excelis. Neid on väga lihtne kasutada ja tulemused on üsna lihtsad. Vaatame nüüd keerukaid filtreid, kasutades Täpsem filtrite dialoog.
Loo Excelis täiustatud filtrid
Kui soovite luua rohkem täiustatud filtreid, peate seda kasutama Täpsem filtri dialoog. Oletame näiteks, et tahtsin näha kõiki peresid, kes elavad New Orleanes rohkem kui 2 pereliikmega VÕI kõik perekonnad Clarksville'is, kus on rohkem kui 3 pereliiget JA ainult need, kellel on a .EDU e-posti aadressi lõpetamine. Nüüd ei saa seda teha lihtsa filtriga.
Selleks peame Excel-lehed veidi erinevalt seadistama. Mine edasi ja sisestage paar rida oma andmekogumite kohal ja kopeeri pealkirja sildid täpselt esimesele reale nagu allpool näidatud.
Nüüd on siin, kuidas täiustatud filtrid töötavad. Kõigepealt peate oma kriteeriumid tippude veergudesse ja seejärel klikkima Täpsem nuppu all Sorteeri ja filtreerige kohta Andmed vahekaart.
Mis siis täpselt nendesse rakkudesse saab kirjutada? OK, alustame meie eeskujust. Me tahame näha ainult New Orleansi või Clarksville'i andmeid, nii et kirjutame need lahtritesse E2 ja E3.
Kui sisestate väärtusi erinevatele ridadele, tähendab see OR. Nüüd tahame, et New Orleans'i perekonnad, kellel on rohkem kui kaks liiget, ja Clarksville'i perekonnad, kus on rohkem kui 3 liiget. Selleks sisesta > 2 C2 ja > 3 C3.
Kuna> 2 ja New Orleans on samal real, on see AND operaator. Sama kehtib eespool 3. rea kohta. Lõpuks tahame ainult peresid, millel on .EDU lõpp-e-posti aadress. Selleks kirjuta lihtsalt * .edu nii D2 kui ka D3. Sümbol * tähistab mis tahes märke.
Kui olete seda teinud, klõpsake ükskõik millises oma andmekogus ja seejärel klõpsake Täpsem nuppu. The Nimekiri RangVäljal kuvatakse automaatselt teie andmekogum, kuna klõpsasite enne nupule Advanced (Täpsemalt) klõpsamist. Nüüd klõpsa väikest väikest nuppu, mis asub Kriteeriumide vahemik nuppu.
Valige kõik A1-st E3-le ja seejärel klõpsake uuesti nupul Edasijõudnud filtri avamiseks uuesti sama nuppu. Klõpsake nuppu OK ja teie andmed tuleb nüüd filtreerida!
Nagu näete, on mul nüüd ainult kolm tulemust, mis vastavad kõigile nendele kriteeriumidele. Pidage meeles, et selleks, et see toimiks, peavad kriteeriumide vahemiku märgised vastama täpselt andmekogu siltidele.
Selle meetodi abil saate ilmselt luua palju keerulisemaid päringuid, et mängida sellega soovitud tulemusi. Lõpuks räägime summeerimisfunktsioonide rakendamisest filtreeritud andmetele.
Filtreeritud andmete kokkuvõte
Ütleme nüüd, et soovin oma filtreeritud andmetel kokku võtta pereliikmete arvu, kuidas ma seda tegema sain? Noh, puhastame meie filtri, klõpsates Selge nupp ribal. Ära muretse, täiustatud filtrit on väga lihtne uuesti kasutada, klõpsates lihtsalt nupul Täpsemalt ja klõpsates uuesti OK.
Andmekogumi allosas lisame kutsutud lahtri Kokku ja seejärel lisage summafunktsioon, et kokku võtta kogu pereliikmed. Minu näites kirjutasin ma lihtsalt = SUM (C7: C31).
Nii et kui ma vaatan kõiki perekondi, on mul 78 liiget. Nüüd lähme edasi ja rakendame uuesti meie täiustatud filtrit ja vaatame, mis juhtub.
Hoppu! Õige numbri näitamise asemel näen, et see on kokku 78! Miks nii? Noh, SUM-funktsioon ei eira varjatud ridu, nii et see teeb arvutust veel kõigi ridade abil. Õnneks on peidetud ridade eiramiseks mitmeid funktsioone.
Esimene on SUBTOTAL. Enne nende erifunktsioonide kasutamist peate oma filtri tühjendama ja seejärel sisestama funktsiooni.
Kui filter on kustutatud, siis mine sisse ja tippige = SUBTOTAL ( ja sa peaksid nägema rippmenüüd, millel on hulk võimalusi. Selle funktsiooni abil valite esmalt summeerimisfunktsiooni, mida soovite numbri abil kasutada.
Meie näites tahan kasutada SUM, nii et ma kirjutaksin numbri 9 või lihtsalt rippmenüüst. Seejärel tippige koma ja valige rakkude vahemik.
Kui vajutate sisestusklahvi, peaksite nägema, et väärtus 78 on sama, mis varem. Kui te aga nüüd filtrit uuesti rakendate, näeme 11!
Suurepärane! See on täpselt see, mida me tahame. Nüüd saate oma filtreid korrigeerida ja väärtus peegeldab alati ainult parajasti kuvatavaid ridu.
Teine funktsioon, mis toimib päris täpselt sama mis SUBTOTAL funktsioon on KOOSTÖÖ. Ainus erinevus on see, et funktsioonis AGGREGATE on veel üks parameeter, kus peate määrama, et soovid peidetud ridu ignoreerida.
Esimene parameeter on summeerimisfunktsioon, mida soovite kasutada ja nagu SUBTOTAL, 9 tähistab SUM funktsiooni. Teine võimalus on see, kus peidetud ridade eiramiseks peate sisestama 5-sse. Viimane parameeter on sama ja see on rakkude vahemik.
Samuti saate lugeda minu artiklit kokkuvõttefunktsioonidest, et õppida üksikasjalikumalt kasutama AGGREGATE funktsiooni ja muid funktsioone, nagu MODE, MEDIAN, AVERAGE jne..
Loodetavasti annab see artikkel hea lähtepunkti filtrite loomiseks ja kasutamiseks Excelis. Kui teil on küsimusi, saatke palun kommentaar. Naudi!
