Mõnikord on suure andmebaasiga töötades vaja valida teave teatud kriteeriumi järgi (näiteks näidata ainult neid inimesi, kes on sündinud 1980. aastal). Seda tüüpi ülesannete täitmiseks kasutage Excelis filtrit.

Video andmete filtreerimise ja sortimise kohta Excelis

Filtrite tüübid Excelis

Filtreerimine on vajalike andmete valimine loendist edasiseks sellega töötamiseks. Selle protseduuri tulemus on teatud stringid mis vastavad valikukriteeriumidele. Ülejäänud kirjed on ajutiselt peidetud ja neid ei kasutata enne, kui kasutaja filtri keelab. Valitud andmetega saate teha standardseid toiminguid: redigeerida, vormindada, printida, luua graafikuid, diagramme jne.

Excelis on kaks filtreerimismeetodit: automaatfilter ja täiustatud filter. Saate need käivitada menüüriba kaudu, klõpsates "Andmed - Filter". Kasutades esimest võimalust, tehakse kiire valik vajalikku teavet lihtsate otsingukriteeriumitega. Automaatse filtri režiimis on iga veeru tabeli pealkirjareal üks noolega nupp, millele klõpsates saab määrata valikukriteeriumid. Iga veeru jaoks saate määrata oma seaded. IN see režiim saate määrata järgmised parameetrid:

1. Sorteeri kasvavas või kahanevas järjekorras.
2. "Kõik" – Excel kuvab (taastab) kõiki ridu.
3. Top 10 – Excel kuvab 10 esimest kirjet. Selle üksuse valimisel avaneb uus aken, kus saate määrata kirjete arvu, valida, millist neist näidata (suurim või väikseim) ning määrata ka kuvatavate kirjete arvu piirangu.
4. "Tingimus" - siin saab kasutaja iseseisvalt luua 2 andmevaliku kriteeriumi, ühendades need lihtsad operaatorid JA, VÕI.
5. Ükskõik milline element. Siin saate valida mis tahes veerus oleva väärtuse. Näiteks kui veerg koosneb tootenimedest, näidatakse elementide loendis absoluutselt kõik tooted. Kasutaja saab määrata ükskõik millise neist.
6. Tühjad ja mittetühjad – Excel kuvab tühjad (või mittetühjad) lahtrid. See valik kuvatakse ainult siis, kui veerus on tühjad väljad.

   Filtri eemaldamiseks ühest veerust peate klõpsama elementide loendis üksusel "Kõik". Kui peate selle kogu tabeli jaoks tühistama, peate menüüribal valima "Andmed - Filter - Kuva kõik". Automaatfiltri eemaldamine toimub samamoodi nagu selle käivitamine.

   Täiustatud filter

   

   Näide täpsema filtri loomisest – peate määrama lähteandmete vahemiku, filtrid ja vahemiku, kuhu filtreeritud andmed paigutatakse

   Teine filtreerimisvalik pakub kasutajale rohkem võimalusi vajalike andmete valimiseks Täiustatud valiku käivitamiseks peate looma tabeli päistest koopia (st lihtsalt kopeerima päise). See on tingimuste valik. Seejärel peate selle vahemiku valikukriteeriumitega täitma. Kuid siin on oluline järgida reegleid: kui teil on vaja väärtusi valida kahe parameetri järgi (näiteks õpilase perekonnanimi ja hinne), kirjutatakse tingimused ühele reale; kui kriteeriumid on valitud režiimis VÕI (auto mark või mootori suurus), siis salvestatakse need erinevatele ridadele.

   Oletame, et on 2 veeruga tabel – toote nimi ja kogus. Kokku on 3 toodet - banaanid, apelsinid, mandariinid ja kogus on vastavalt 10, 20 ja 15 tk. Pärast päise kopeerimist saate luua tingimuse näiteks toodete kuvamiseks, mille kogus on väiksem või võrdne 15. See tähendab, et kopeeritud päise alla veerus “Kogus” tuleb kirjutada<=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).

   Seega peaks filtreerimise põhimõte olema selge. Nii saate andmebaasist valida mis tahes elemendid, olenemata selle suurusest. See protseduur aitab lihtsustada kasutaja tööd suurte andmemahtudega.

Filtreerige Excelis - See on andmete valik teatud tunnuste järgi.Filtreerimine Excelisteatud tingimustel teostatakse funktsiooni " Filtreerige Excelis ". Kui tabeli lahtrites on pilte, siis vaadake artiklit "Excelis pildi lisamine lahtrisse", kuidas lahtris pilte parandada nii, et need filtreerimise ajal ei liiguks.
Kuidas Excelis filtreerida.
Esmalt valige veerg, milles peate tabeli andmeid filtreerima. Seejärel klõpsake vahekaardil "Kodu" nuppu "Sorteeri ja filtreerige" ja valige funktsioon "Filter". Või minge vahekaardile "Andmed" ja klõpsake nuppu "Filter".
Valitud veeru ülemisse lahtrisse ilmus filtrinupp. Klõpsake sellel nupul ja valige "Numbrilised filtrid", seejärel "vähem". Ilmuvas aknas lahtri "vähem kui" vastas kirjutage arv, millest me peame andmeid valima, näiteks 7. Klõpsake "OK".

Nõutavad andmed jäävad tabeli veergu.

Nõuanne.
Selleks, et filtrinupp ei kataks ülemises lahtris olevat väärtust, võite panna ülal olevasse lahtrisse numbri ja valida ka selle või lihtsalt valida veeru kohal oleva tühja lahtri. Siis on filtri ikoon selles lisalahtris ja ei kata vajalikke andmeid.

Filtreerige Excelis kuupäeva järgi.
Kuidas tabelit õigesti seadistada ja kuupäeva järgi filtreerida, lugege artiklit "Kuupäeva järgi sortimine Excelis".
Filtreerige Excelis lahtri värvi järgi.
Saate andmeid filtreerida lahtri värvi järgi. Lahtreid saab värvida käsitsi või tingimusliku vorminguga. Kuidas lahtreid tingimusvorminguga numbri, sõnade, kuupäeva jne järgi värvida, "Tingimusvorming Excelis".
Klõpsake roosal värvil. Klõpsake "OK". Selgus selline.
Kuidas valige Excelis ainult nähtavad lahtrid Lisateavet Excelis ainult nähtavatesse ridadesse kleepimise kohta leiate artiklist "Kleepimine Excelis nähtavatesse ridadesse".
Excelis saate seadistada lahtri, mis näitab filtreeritud ridade arvu, olenemata sellest, kas filter on lubatud või mitte. See on vajalik selleks, et mitte sattuda segadusse, kui kasutame filtrit sageli või töötab mitu inimest ühes lauas jne. Loe selle kohta lähemalt artiklist "Kuidas Excelis filtrit seadistada".
Pärast filtreeritud andmetega töötamist (näiteks selle loendi printimist) saate tabeli taastada algsel kujul, st koos kõigi filtreerimata andmetega. Klõpsake veeru ülaosas filtrinupul ja valige "Vali kõik". Meil on nüüd tabel algsel kujul.
Kuidas filtri andmeid kopeerida, vaadake artiklit "Kopeerige filtreeritud andmed Excelisse" .
Saab eemalda filtri nupp Exceli tabelist. Klõpsake vahekaardil "Kodu" nuppu "Sorteeri ja filtreerige", seejärel "Filter".
Või minge vahekaardile "Andmed" ja klõpsake nuppu "Filter". Nupp on kadunud.
Kui on vaja printida filtreeritud andmetega tabel nii, et ridade numbrid oleksid reas (1, 2, 3...) või loendada filtreeritud ridade ja lahtrite arvu, siis saab seda seadistada. Vaadake jaotist "Ridade järjestamine pärast filtrit Excelis".
Kuidas tabeli andmeid sortida, lugege artiklit "

Tõenäoliselt teavad kõik kasutajad, kes töötavad pidevalt Microsoft Exceliga, selle programmi sellisest kasulikust funktsioonist nagu. Kuid mitte kõik ei tea, et sellel tööriistal on ka täiustatud võimalused. Vaatame, mida suudab Microsoft Exceli täiustatud filter ja kuidas seda kasutada.

Täiustatud filtri paigaldamiseks peate esmalt looma lisatabeli valikutingimustega. Selle tabeli päis on täpselt sama, mis põhitabeli päis, mida me tegelikult filtreerime.

Näiteks asetasime põhitabeli kohale lisatabeli ja värvisime selle lahtrid oranžiks. Kuigi saate selle laua paigutada mis tahes vabasse ruumi ja isegi teisele lehele.

Nüüd sisestame lisatabelisse andmed, mis tuleb põhitabelist filtreerida. Meie konkreetsel juhul otsustasime töötajatele väljastatud töötasude loetelust valida andmed meessoost põhipersonali kohta 25.07.2016.

Täiustatud filtri käitamine

Alles pärast lisatabeli loomist saate jätkata täpsema filtri käivitamist. Selleks minge vahekaardile "Andmed" ja klõpsake tööriistaploki "Sortimine ja filtreerimine" lindil nuppu "Täpsemalt".

Avaneb täpsema filtri aken.

Nagu näete, on selle tööriista kasutamiseks kaks režiimi: "Filtreeri loend paigas" ja "Kopeeri tulemused teise asukohta". Esimesel juhul tehakse filtreerimine otse lähtetabelis ja teisel juhul eraldi teie enda määratud lahtrivahemikus.

Väljal Allikavahemik peate määrama lähtetabeli lahtrite vahemiku. Seda saab teha käsitsi, sisestades koordinaadid klaviatuurilt või valides hiirega soovitud lahtrivahemiku. Väljale "Tingimuste vahemik" peate sarnaselt märkima lisatabeli päise ja tingimusi sisaldava rea ​​vahemiku. Samal ajal peate tähelepanu pöörama sellele, et see vahemik ei sisalda tühje ridu, vastasel juhul ei tööta midagi. Pärast kõigi seadistuste lõpetamist klõpsake nuppu "OK".

Nagu näete, jäid algsesse tabelisse ainult need väärtused, mida otsustasime filtreerida.

Kui valisite tulemuse väljastamiseks teise asukohta, siis tuleb väljal "Koht tulemus vahemikus" määrata lahtrite vahemik, kuhu filtreeritud andmed väljastatakse. Saate määrata ka ühe lahtri. Sel juhul saab sellest uue tabeli ülemine vasak lahter. Pärast valiku tegemist klõpsake nuppu "OK".

Nagu näete, jääb pärast seda toimingut lähtetabel muutumatuks ja filtreeritud andmed kuvatakse eraldi tabelis.

Kohapealse loendi koostamise kasutamisel filtri lähtestamiseks peate klõpsama tööriistaploki "Sortimine ja filtreerimine" lindil nuppu "Tühjenda".

Seega võime järeldada, et täiustatud filter pakub rohkem võimalusi kui tavaline andmete filtreerimine. Samal ajal tuleb märkida, et selle tööriistaga töötamine on endiselt vähem mugav kui tavalise filtriga.

Tabelites suurte andmemahtudega töötamise mugavuse huvides tuleb neid pidevalt teatud kriteeriumi järgi korraldada. Lisaks pole teatud eesmärkide täitmiseks mõnikord vaja kogu andmemassiivi, vaid ainult üksikuid ridu. Seetõttu, et mitte sattuda segadusse tohutu infohulgaga, oleks ratsionaalne lahendus andmete korrastamine ja filtreerimine muude tulemuste põhjal. Uurime, kuidas Microsoft Excelis andmeid sortida ja filtreerida.

Sorteerimine on Microsoft Excelis töötamisel üks mugavamaid tööriistu. Selle abil saate järjestada tabeli read tähestikulises järjekorras vastavalt veeru lahtrites olevatele andmetele.

Andmeid saate sortida Microsoft Excelis, kasutades nuppu "Sortimine ja filtreerimine", mis asub tööriistaploki "Redigeerimine" lindi vahekaardil "Kodu". Kuid kõigepealt peame klõpsama mis tahes lahtril veerus, mille järgi sorteerime.

Näiteks allolevas tabelis tuleks töötajad järjestada tähestikulises järjekorras. Minge veerus "Nimi" mis tahes lahtrisse ja klõpsake nuppu "Sorteeri ja filtreerige". Nimede sortimiseks tähestikulises järjekorras valige kuvatavast loendist "Sorteeri A-st Z-ni".

Nagu näete, on kõik tabelis olevad andmed järjestatud perekonnanimede tähestikulise loendi järgi.

Vastupidises järjestuses sortimiseks valige samast menüüst nupp Sorteeri Z kuni A.

Nimekiri koostatakse ümber vastupidises järjekorras.

Tuleb märkida, et seda tüüpi sortimine on näidatud ainult tekstiandmete vormingu jaoks. Näiteks numbrivormingu puhul on sortimiseks määratud "Minimaalsest maksimumini" (ja vastupidi) ja kuupäevavorminguga - "Vanast uueni" (ja vastupidi).

Kohandatud sortimine

Kuid nagu näeme, on seda tüüpi ühe väärtuse järgi sorteerimise korral sama isiku nimesid sisaldavad andmed paigutatud vahemikku juhuslikus järjekorras.

Aga mis siis, kui tahame nimesid järjestada tähestikulises järjekorras, aga näiteks kui nimi klapib, siis jälgi, et andmed oleksid kuupäeva järgi järjestatud? Selleks, aga ka mõne muu funktsiooni kasutamiseks, kõik samas menüüs "Sortimine ja filtreerimine", peame minema üksusesse "Kohandatud sortimine...".

Pärast seda avaneb sortimisseadete aken. Kui teie tabelil on päised, siis veenduge, et selles aknas oleks valiku "Minu andmed sisaldavad päiseid" kõrval linnuke.

Väljale "Veerg" märkige selle veeru nimi, mille järgi sorteerimine toimub. Meie puhul on see veerg "Nimi". Väli „Sortimine” näitab, millist tüüpi sisu sorteeritakse. Valikuid on neli:

• Väärtused;
• Raku värv;
• Fondi värv;
• Lahtri ikoon.

Kuid enamikul juhtudel kasutatakse elementi "Väärtused". See on vaikimisi määratud. Meie puhul kasutame ka seda konkreetset punkti.

Veerus “Tellimus” peame märkima, millises järjekorras andmed järjestatakse: “A-st Z-ni” või vastupidi. Valige väärtus "A-st Z-ni".

Niisiis, oleme seadistanud sortimise ühe veeru järgi. Teise veeru järgi sortimise konfigureerimiseks klõpsake nuppu "Lisa tase".

Ilmub veel üks väljade komplekt, mis tuleb teise veeru järgi sortimiseks täita. Meie puhul vastavalt veerule "Kuupäev". Kuna nendes lahtrites on määratud kuupäevavorming, määrame väljal "Tellimus" väärtused mitte "A-st Z-ni", vaid "vanast uueks" või "uuest vanani".

Samamoodi saab selles aknas seadistada vajadusel ka teiste veergude järgi sortimise prioriteetsuse järjekorras. Kui kõik seadistused on tehtud, klõpsake nuppu "OK".

Nagu näete, on nüüd meie tabelis kõik andmed sorteeritud ennekõike töötajate nimede ja seejärel maksekuupäevade järgi.

Kuid see pole kõik kohandatud sortimise võimalused. Soovi korral saab selles aknas konfigureerida sorteerimist mitte veergude, vaid ridade järgi. Selleks klõpsake nuppu "Valikud".

Avanevas sortimisvalikute aknas liigutage lüliti asendist "Range Rows" asendisse "Range Columns". Klõpsake nuppu "OK".

Nüüd saate analoogselt eelmise näitega sisestada andmeid sortimiseks. Sisestage andmed ja klõpsake nuppu "OK".

Nagu näha, on pärast seda veerud vastavalt sisestatud parameetritele kohad vahetanud.

Muidugi ei ole meie tabeli puhul, kui näitena võtta, veergude asukoha muutmise teel sortimise kasutamine eriti kasulik, kuid mõne teise tabeli puhul võib selline sortimine vägagi sobida.

Filter

Lisaks on Microsoft Excelil andmefiltri funktsioon. See võimaldab teil jätta nähtavaks ainult need andmed, mida peate vajalikuks, ja peita ülejäänud. Vajadusel saab peidetud andmed alati nähtavasse režiimi tagasi viia.

Selle funktsiooni kasutamiseks seiske tabeli suvalises lahtris (eelistatavalt päises), klõpsake uuesti tööriistaplokis "Redigeerimine" nuppu "Sorteeri ja filtreerige". Kuid seekord valige kuvatavas menüüs üksus "Filter". Nende toimingute asemel võite lihtsalt vajutada klahvikombinatsiooni Ctrl+Shift+L.

Nagu näete, kuvatakse kõigi veergude nimedega lahtrites ruudu kujul ikoon, millesse on kirjutatud ümberpööratud kolmnurk.

Klõpsake sellel ikoonil veerus, mille järgi me filtreerime. Meie puhul otsustasime filtreerida nime järgi. Näiteks peame jätma ainult Nikolajevi töötaja andmed. Seetõttu eemaldame kõigi teiste töötajate nimedelt linnukesed.

Kui protseduur on lõpetatud, klõpsake nuppu "OK".

Nagu näete, jäävad tabelisse ainult Nikolajevi töötaja nimega read.

Teeme ülesande keerulisemaks ja jätame tabelisse ainult Nikolajeviga seotud andmed 2016. aasta kolmanda kvartali kohta. Selleks klõpsake lahtris "Kuupäev" ikooni. Avanevas loendis eemaldage märge kuude „mai“, „juuni“ ja „oktoober“, kuna need ei kuulu kolmandasse kvartalisse, ja klõpsake nuppu „OK“.

Nagu näete, jäävad alles vaid meile vajalikud andmed.

Konkreetse veeru filtri eemaldamiseks ja peidetud andmete kuvamiseks klõpsake uuesti ikooni, mis asub selle veeru pealkirjaga lahtris. Avanevas menüüs klõpsake üksusel "Eemalda filter...".

Kui soovite tabeli kui terviku filtri lähtestada, peate klõpsama lindil nuppu "Sorteeri ja filtreerima" ja valima üksuse "Tühjenda".

Kui teil on vaja filter täielikult eemaldada, siis nagu selle käivitamisel, valige samast menüüst üksus "Filter" või tippige kiirklahv Ctrl+Shift+L.

Lisaks tuleb märkida, et pärast funktsiooni „Filter” lubamist, kui klõpsate tabeli päise lahtrites vastavat ikooni, muutuvad kuvatavas menüüs kättesaadavaks sortimisfunktsioonid, mida eespool käsitlesime: „Sortimine A-st Z-ni”, “Sorteeri Z-st A-ni” ja “Sordi värvi järgi”.

Nutikas laud

Sorteerimist ja filtreerimist saab aktiveerida ka muutes töötava andmeala nn nutikaks tabeliks.

Nutika tabeli loomiseks on kaks võimalust. Neist esimese kasutamiseks tuleb valida kogu tabeliala ja vahekaardil “Avaleht” olles klõpsata lindil nuppu “Vorminda tabelina”. See nupp asub tööriistaplokis "Stiilid".

Pärast seda avaneb dialoogiboks, kus saate muuta tabeli koordinaate. Kuid kui valisite ala varem õigesti, ei pea te midagi muud tegema. Peaasi on veenduda, et parameetri "Päistega tabel" kõrval on linnuke. Järgmisena klõpsake lihtsalt nuppu "OK".

Kui otsustate kasutada teist meetodit, peate valima ka kogu tabeliala, kuid seekord minge vahekaardile "Sisesta". Siin olles klõpsake tööriistaploki "Tabelid" lindil nuppu "Tabel".

Pärast seda, nagu eelmiselgi korral, avaneb aken, kus saate reguleerida tabeli paigutuse koordinaate. Klõpsake nuppu "OK".

Olenemata sellest, millist meetodit kasutate "targa tabeli" loomisel, saate lõpuks tabeli, mille päise lahtritesse on juba installitud filtriikoonid, mida varem kirjeldasime.

Kui klõpsate sellel ikoonil, on saadaval kõik samad funktsioonid, mis tavalisel viisil filtri käivitamisel nupu "Sorteeri ja filtreerige" kaudu.

Nagu näete, võivad sortimis- ja filtreerimistööriistad õigel kasutamisel muuta tabelitega töötamise kasutajatele palju lihtsamaks. Nende kasutamise küsimus muutub eriti aktuaalseks, kui tabel sisaldab väga suurt hulka andmeid.

Kui loete lõpuni, saate teada, kuidas kasutada sellist kasulikku Exceli funktsiooni nagu filter. Nüüd näitan väga reaalset näidet kasutades, mis on Exceli filtrid ja kuidas suurte tabelitega töötamisel aega säästa. See pole üldse raske. Artikli lõpus saate alla laadida tabeli, mille näitel käsitlen siin Exceli filtritega töötamist.

Miks vajame Exceli tabelites filtreid?

Ja siis selleks, et saaks kiire valige ainult vajalikud andmed, peidus mittevajalik read tabelid. Seega filter võimaldab ilma kustutamiseta Exceli tabeli read lihtsalt peidavad need ajutiselt.

Filtriga peidetud tabeliread ei kao. Võite umbkaudu ette kujutada, et nende kõrgus muutub nulliks (ma varem rääkisin ridade kõrguse ja veergude laiuse muutmisest). Seega on ülejäänud jooned, mida filter ei varja, justkui kokku liimitud. Tulemuseks on tabel, millele on rakendatud filter.