{"id":275,"date":"2016-04-26T14:46:49","date_gmt":"2016-04-26T11:46:49","guid":{"rendered":"http:\/\/helia.ee\/koolitus\/?page_id=275"},"modified":"2016-05-03T13:16:27","modified_gmt":"2016-05-03T10:16:27","slug":"microsoft-excel-2016-andmete-sorteerimine-ja-filtreerimine","status":"publish","type":"page","link":"https:\/\/helia.ee\/koolitus\/?page_id=275","title":{"rendered":"Microsoft Excel 2016 &#8211; Andmete sorteerimine ja Filtreerimine (HARJUTUS 8)"},"content":{"rendered":"<p>Andmete sorteerimisega j\u00e4rjestatakse andmed t\u00e4hestikulisse v\u00f5i suuruse j\u00e4rjekorda. Seda nii t\u00f5usvas v\u00f5i kahanevas j\u00e4rjekorras. Sorteerida saate tekste, numbreid kui kuup\u00e4evi.<\/p>\n<p><strong>Lihtne sorteerimine<\/strong><\/p>\n<p>V\u00f5tame aluseks pisikese tabeli, mis kuvab kui palju oli etendustel k\u00fclastajaid ja piletite hinda.<\/p>\n<p id=\"WbqCmSh\"><img loading=\"lazy\" decoding=\"async\" width=\"281\" height=\"155\" class=\"alignnone size-full wp-image-386 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286893d8278.png\" alt=\"\" \/><\/p>\n<ul>\n<li>Sorteerimiseks m\u00e4rgista \u00e4ra k\u00f5ik etendused koos k\u00fclastajate arvu ja pileti hinnaga.<\/li>\n<li>Seej\u00e4rel vali men\u00fc\u00fcst\u00a0<strong><strong>Avaleht&gt;Redigeerimine&gt;Sortimine ja filtreerimine<br \/>\n<\/strong><\/strong><\/p>\n<p id=\"vKTvKBu\"><img loading=\"lazy\" decoding=\"async\" width=\"175\" height=\"197\" class=\"alignnone size-full wp-image-387 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572868a03bd87.png\" alt=\"\" \/><\/p>\n<p><strong><br \/>\n<\/strong><\/li>\n<li>N\u00fc\u00fcd tuleb teha valik:\n<ul>\n<li><strong>Sordi A-st Y-ni<\/strong>&#8211; kasvavas j\u00e4rjekorras<\/li>\n<li><strong>Sordi Y-st Y-ni\u00a0<\/strong>&#8211; kahanevas j\u00e4rjekorras<\/li>\n<\/ul>\n<\/li>\n<li>Valisin n\u00e4iteks kasvavas j\u00e4rjekorras\n<p id=\"mPJxVQn\"><img loading=\"lazy\" decoding=\"async\" width=\"280\" height=\"152\" class=\"alignnone size-full wp-image-388 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572868b2e3624.png\" alt=\"\" \/><\/p>\n<\/li>\n<\/ul>\n<p>Selline valimismeetod sorteerib andmed vaid esimese veeru j\u00e4rgi. Juhul kui m\u00e4rgistate \u00e4ra ainult esimese veeru, siis sorteerimisega muutuvad andmed valeks, sest teised veerud j\u00e4\u00e4vad paigale. \u00d5nneks selle vea v\u00e4ltimiseks tuleb Excel 2016 appi ja teavitab kasutajat veast ning pakub valikut laiendada.<\/p>\n<p id=\"RrOagVH\"><img loading=\"lazy\" decoding=\"async\" width=\"514\" height=\"232\" class=\"alignnone size-full wp-image-389 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286913be0d0.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286913be0d0.png 514w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286913be0d0-300x135.png 300w\" sizes=\"auto, (max-width: 514px) 100vw, 514px\" \/><\/p>\n<p><strong>Mitme veeru sorteerimine<\/strong><\/p>\n<p>T\u00e4psemate sorteerimistele tuleb appi\u00a0<strong>Kohandatud sortimine&#8230;<br \/>\n<\/strong><\/p>\n<p id=\"AjpoYSC\"><img loading=\"lazy\" decoding=\"async\" width=\"177\" height=\"196\" class=\"alignnone size-full wp-image-390 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286921c3b0c.png\" alt=\"\" \/><\/p>\n<p>Kui tabel sisaldab p\u00e4iseid nagu minu tabelis, siis on kasulik need ka enne \u00e4ra m\u00e4rgistada. See v\u00f5imaldab neid n\u00e4ha\u00a0<strong>Sortimisaluste\u00a0<\/strong>valikus, kui on sisse l\u00fclitatud\u00a0<strong>Minu andmetel on p\u00e4ised<\/strong><\/p>\n<p id=\"iAOSxvm\"><img loading=\"lazy\" decoding=\"async\" width=\"371\" height=\"174\" class=\"alignnone size-full wp-image-391 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572869315b0d1.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572869315b0d1.png 371w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572869315b0d1-300x141.png 300w\" sizes=\"auto, (max-width: 371px) 100vw, 371px\" \/><\/p>\n<p>Juhul kui sinu tabelil pole p\u00e4iseid, siis kuvatakse sortimisalusena Veerg A, Veerg B jne. Sorteerime Etendused kasvavas j\u00e4rjekorras. Seej\u00e4rel soovin lisaks j\u00e4rjestada Pileti hinna j\u00e4rgi suurimast v\u00e4ikseimani.<\/p>\n<p>Uue sortimisaluse lisamiseks kliki\u00a0<strong>Lisa tase<\/strong>.<\/p>\n<p id=\"NZVFVSw\"><img loading=\"lazy\" decoding=\"async\" width=\"550\" height=\"256\" class=\"alignnone size-full wp-image-392 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572869565f7c8.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572869565f7c8.png 550w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572869565f7c8-300x140.png 300w\" sizes=\"auto, (max-width: 550px) 100vw, 550px\" \/><\/p>\n<p>Ega antud tabelis midagi eriti ei muutunud, sest meie tabel on liiga pisike. Muutuks sel juhul kui \u00fchesuguse algusega etendusi oleks rohkem.<\/p>\n<p id=\"RKAfYgo\"><img loading=\"lazy\" decoding=\"async\" width=\"283\" height=\"152\" class=\"alignnone size-full wp-image-393 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286960b792e.png\" alt=\"\" \/><\/p>\n<p><strong>Kohandatud loendi j\u00e4rgi sorteerimine<\/strong><\/p>\n<p>Huvitav sorteerimine on kohandatud loendi j\u00e4rgi. Nagu\u00a0<a href=\"http:\/\/metshein.com\/content\/view\/822\/90\/\">m\u00e4letate<\/a>\u00a0vaatasime kohandatud loendeid varem ja \u00fcks loenditest oli n\u00e4iteks n\u00e4dalap\u00e4evad &#8211; esmasp\u00e4ev, teisip\u00e4ev, &#8230;, p\u00fchap\u00e4ev. Kui me soovime j\u00e4rjestada andmeid n\u00e4dalap\u00e4evade j\u00e4rgi tavalise meetodi j\u00e4rgi, siis sorteeritakse need t\u00e4hestikulises j\u00e4rjekorras ning pole \u00f5ige.<\/p>\n<p id=\"xnudmSV\"><img loading=\"lazy\" decoding=\"async\" width=\"161\" height=\"152\" class=\"alignnone size-full wp-image-394 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572869a408a24.png\" alt=\"\" \/><\/p>\n<p>Selleks, et n\u00e4dalap\u00e4evad sorteerimisel ikka esmasp\u00e4evast alustaks, siis vali\u00a0<strong>Kohandatud sortimine<\/strong>. Avanenud aknas vali\u00a0<strong>J\u00e4rjestus: Kohandatud loend<\/strong>\u00a0ja seal omakorda loendite hulgast n\u00e4dalap\u00e4evad.<\/p>\n<p id=\"nCtdOPr\"><img loading=\"lazy\" decoding=\"async\" width=\"557\" height=\"287\" class=\"alignnone size-full wp-image-395 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572869b454951.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572869b454951.png 557w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572869b454951-300x155.png 300w\" sizes=\"auto, (max-width: 557px) 100vw, 557px\" \/><\/p>\n<p>Kui oled n\u00e4dalap\u00e4evad \u00e4ra valinud siis J\u00e4rjestuse alt leiad nii kasvavas kui kahanevas j\u00e4rjekorras loendi. Kasuta sama tehnikat, kui soovid j\u00e4rjestada andmeid n\u00e4iteks SUUR, KESKMINE, V\u00c4IKE vms j\u00e4rgi.<\/p>\n<p><strong>Ridade sorteerimine<\/strong><\/p>\n<p>Vaikimisi sorteeritakse andmeid alt \u00fcles v\u00f5i \u00fclevalt alla. Harvemal juhul on vaja andmeid sorteerida vasakult paremale ehk rea j\u00e4rgi. S\u00e4tted vasakult paremale sorteerimiseks leiad\u00a0<strong>Kohandatud sortimine<\/strong>\u00a0alt ja avanenud aknas kliki nupule\u00a0<strong>Suvandid&#8230;<br \/>\n<\/strong><\/p>\n<p id=\"gdttkfe\"><img loading=\"lazy\" decoding=\"async\" width=\"461\" height=\"216\" class=\"alignnone size-full wp-image-396 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286a207f53e.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286a207f53e.png 461w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286a207f53e-300x141.png 300w\" sizes=\"auto, (max-width: 461px) 100vw, 461px\" \/><\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<p>Pane t\u00e4hele &#8211; Sortimissuvandite alt leiad valiku\u00a0<strong>T\u00f5stutundlik<\/strong>, mille valikul arvestatakse suuri ja v\u00e4ikeseid t\u00e4hti.<\/p>\n<p><strong><br \/>\n<\/strong><\/p>\n<h1>Excel2016 &#8211; Andmete filtreerimine<\/h1>\n<p>Kui andmeid on v\u00e4ga palju, siis seal midagi leidmine on v\u00e4ga raske. J\u00e4rgmisel pildil on pisike andmebaas, kus on ligi 2000 kirjet.<\/p>\n<p id=\"ZLWneTm\"><img loading=\"lazy\" decoding=\"async\" width=\"550\" height=\"414\" class=\"alignnone size-full wp-image-397 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286a407d2f2.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286a407d2f2.png 550w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286a407d2f2-300x226.png 300w\" sizes=\"auto, (max-width: 550px) 100vw, 550px\" \/><br \/>\n\u00dcks v\u00f5imalus, mis meid siinkohal abistaks, oleks andmete filtreerimine. See t\u00e4hendab, et kuvatakse andmed ainult vastavalt meie poolt seatud tingimustele. Kui olete filtri lisanud, siis on seda n\u00e4iteks kiiresti printida, ilma et peaksime neid kuhugi kopeerima. Loomulikult on hea ka kopeerida, ilma et teised andmed kaasa tuleks.<\/p>\n<p><strong>Filtri lisamine<\/strong><\/p>\n<p>Filtri lisamiseks selekteeri kogu tabel v\u00f5i muuda v\u00e4hemalt \u00fcks lahter tabelis aktiivseks ning seej\u00e4rel vali men\u00fc\u00fcst\u00a0<strong>Avaleht&gt;Redigeerimine&gt;Sortimine ja filtreerimine&gt;Filtreeri<br \/>\n<\/strong><\/p>\n<p id=\"kOUGbAW\"><img loading=\"lazy\" decoding=\"async\" width=\"184\" height=\"203\" class=\"alignnone size-full wp-image-398 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286a71973fa.png\" alt=\"\" \/><\/p>\n<p>Vanemates versioonides nimetati seda Automaatfiltriks ja see lisab tabeli p\u00e4istele valikunooled\u00a0<img loading=\"lazy\" decoding=\"async\" width=\"23\" height=\"20\" class=\"alignnone size-full wp-image-400 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286aa2d4deb.png\" alt=\"\" \/>.<\/p>\n<p id=\"hdaGouu\"><img loading=\"lazy\" decoding=\"async\" width=\"350\" height=\"127\" class=\"alignnone size-full wp-image-399 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286a8fdef17.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286a8fdef17.png 350w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286a8fdef17-300x109.png 300w\" sizes=\"auto, (max-width: 350px) 100vw, 350px\" \/><\/p>\n<p><strong>Andmete filtreerimine<\/strong><\/p>\n<p>Klikkides suvalise veeru p\u00e4ises valikunoolel kuvatakse filtreerimise valikud.<\/p>\n<p>Hetkel on valitud k\u00f5ik alad ja seep\u00e4rast kuvatakse kenasti k\u00f5ik andmed. N\u00e4iteks teen valiku AA ja FF valikul ning kuvatakse vastavad read.<\/p>\n<p id=\"lffegre\"><img loading=\"lazy\" decoding=\"async\" width=\"255\" height=\"280\" class=\"alignnone size-full wp-image-402 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286ae0344ac.png\" alt=\"\" \/><\/p>\n<p>Veerud millele on lisatud filter t\u00e4histatakse\u00a0<strong>lehtri-ikooniga<\/strong>. Filtri t\u00fchistamiseks tee valik\u00a0<strong>Vali k\u00f5ik\u00a0<\/strong>\u00a0v\u00f5i kliki\u00a0<strong>T\u00fchjenda filter v\u00e4ljalt (veeru nimi)<\/strong><\/p>\n<p><strong>Kuup\u00e4eva filtreerimine<\/strong><\/p>\n<p>Kuup\u00e4evade filtreerimisel kuvatakse kenasti aastad ja selle kuud eraldi.<\/p>\n<p id=\"yllIIvj\"><img loading=\"lazy\" decoding=\"async\" width=\"252\" height=\"391\" class=\"alignnone size-full wp-image-403 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286aee22b79.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286aee22b79.png 252w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286aee22b79-193x300.png 193w\" sizes=\"auto, (max-width: 252px) 100vw, 252px\" \/><\/p>\n<p><strong>Andmete filtreerimine tingimuste abil<\/strong><\/p>\n<p>Iga filtri valikul n\u00e4eme tingimuste rida, mis on vastavalt andmetele erinev:<\/p>\n<ul>\n<li>Tekstifiltrid<\/li>\n<li>Arvufiltrid<\/li>\n<li>Kuup\u00e4evafiltrid<\/li>\n<\/ul>\n<p>V\u00f5tame n\u00e4itena arvufiltrid.<\/p>\n<p id=\"qJOybFL\"><img loading=\"lazy\" decoding=\"async\" width=\"424\" height=\"386\" class=\"alignnone size-full wp-image-404 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286b17a8f60.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286b17a8f60.png 424w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286b17a8f60-300x273.png 300w\" sizes=\"auto, (max-width: 424px) 100vw, 424px\" \/><\/p>\n<p>Siin saate v\u00e4ga t\u00e4pselt valida, millistele tingimustele peab filter vastama. Ekslikuks v\u00f5ib osutuda\u00a0<strong>Esik\u00fcmme&#8230;<\/strong>\u00a0valik, kus tegelikult saate ise valida kasv\u00f5i esikolmikut.<\/p>\n<p>Ei hakka siin neid eraldi lahti kirjutama, kuid juhin t\u00e4helepanu\u00a0<strong>Kohandatud filter&#8230;<\/strong>\u00a0valikule.<\/p>\n<p><strong>Kohandatud filter<\/strong><\/p>\n<p>Kohandatud filter on v\u00f5imalus ise oma &#8220;k\u00e4e ja jala&#8221; j\u00e4rgi filtrit seada.<\/p>\n<p id=\"wtMZuQQ\"><img loading=\"lazy\" decoding=\"async\" width=\"326\" height=\"216\" class=\"alignnone size-full wp-image-405 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286b6bcfa29.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286b6bcfa29.png 326w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57286b6bcfa29-300x199.png 300w\" sizes=\"auto, (max-width: 326px) 100vw, 326px\" \/><\/p>\n<p>Kui kasutate JA-valikut, siis filter peab vastama m\u00f5lemale tingimusele kui V\u00d5I-valikut, siis filter peab vastama v\u00e4hemalt \u00fchele valikule.<\/p>\n<p><a href=\"http:\/\/helia.ee\/koolitus\/dokumendid\/andmete_sorteerimine.xls\">Harjutus 8<\/a><\/p>\n<p><a href=\"http:\/\/helia.ee\/koolitus\/dokumendid\/Excel2016_andmete_sorteerimine_ja_filtreerimine.pdf\">Allalaaditav PDF<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Andmete sorteerimisega j\u00e4rjestatakse andmed t\u00e4hestikulisse v\u00f5i suuruse j\u00e4rjekorda. Seda nii t\u00f5usvas v\u00f5i kahanevas j\u00e4rjekorras. Sorteerida saate tekste, numbreid kui kuup\u00e4evi. Lihtne sorteerimine V\u00f5tame aluseks pisikese tabeli, mis kuvab kui palju oli etendustel k\u00fclastajaid ja piletite hinda. Sorteerimiseks m\u00e4rgista \u00e4ra k\u00f5ik etendused koos k\u00fclastajate arvu ja pileti hinnaga. Seej\u00e4rel vali men\u00fc\u00fcst\u00a0Avaleht&gt;Redigeerimine&gt;Sortimine ja filtreerimine N\u00fc\u00fcd tuleb teha [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":177,"menu_order":13,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"class_list":["post-275","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages\/275","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=275"}],"version-history":[{"count":6,"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages\/275\/revisions"}],"predecessor-version":[{"id":410,"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages\/275\/revisions\/410"}],"up":[{"embeddable":true,"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages\/177"}],"wp:attachment":[{"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}