{"id":459,"date":"2016-05-10T15:04:13","date_gmt":"2016-05-10T12:04:13","guid":{"rendered":"http:\/\/helia.ee\/koolitus\/?page_id=459"},"modified":"2016-05-17T11:28:14","modified_gmt":"2016-05-17T08:28:14","slug":"microsoft-excel-2016-otsingu-funktsioonid-harjutus-13","status":"publish","type":"page","link":"https:\/\/helia.ee\/koolitus\/?page_id=459","title":{"rendered":"Microsoft Excel 2016 &#8211; Otsingu Funktsioonid (Harjutus 13)"},"content":{"rendered":"<p>Otsingu- ja viitefunktsioonid on m\u00f5eldud n\u00e4iteks kiiresti suurtest tabelistest andmete leidmiseks ja luua omavahelisi seoseid. K\u00f5ige selle teostamiseks on mitu erinevat funktsiooni, mis p\u00f5him\u00f5tteliselt v\u00f5ivad teha sama asja ning erinevus seisneb teostuses.<\/p>\n<p><strong>INDEX()<\/strong><\/p>\n<p><strong>INDEX()<\/strong>\u00a0funktsioon leiab massiivist\u00a0<strong>reanumbri j\u00e4rgi lahtri sisu<\/strong>. Tegemist on rea ja veeru ristumiskohaga. V\u00f5tame n\u00e4iteks nimede massiivi ja palume v\u00e4ljastada kolmanda rea sisu.<\/p>\n<p id=\"PPfdmXi\"><img loading=\"lazy\" decoding=\"async\" width=\"267\" height=\"132\" class=\"alignnone size-full wp-image-474 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac52ace0a7.png\" alt=\"\" \/><\/p>\n<p>Kui andmete massiiv on mitmem\u00f5\u00f5tmeline ehk on rohkem veerge, siis on v\u00f5imalik \u00e4ra m\u00e4\u00e4rata ka veerg. Palume n\u00fc\u00fcd funktsioonil v\u00e4ljastada massiivist kolmanda rea ja teise veeru sisu.<\/p>\n<p id=\"NfeqEao\"><img loading=\"lazy\" decoding=\"async\" width=\"433\" height=\"127\" class=\"alignnone size-full wp-image-475 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac53e24522.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac53e24522.png 433w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac53e24522-300x88.png 300w\" sizes=\"auto, (max-width: 433px) 100vw, 433px\" \/><\/p>\n<p>Kuna otsingufunktsioonid tunduvad keskmisest raskemad, siis vaatame veel m\u00f5nda n\u00e4idet. V\u00f5tame n\u00e4iteks korterite nimekirja.<\/p>\n<p id=\"JHcuviB\"><img loading=\"lazy\" decoding=\"async\" width=\"696\" height=\"195\" class=\"alignnone size-full wp-image-476 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac55133ffb.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac55133ffb.png 696w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac55133ffb-300x84.png 300w\" sizes=\"auto, (max-width: 696px) 100vw, 696px\" \/><\/p>\n<p>Eesm\u00e4rk: kui olen sisestanud korteri numbri, siis INDEX() funktsioon v\u00e4ljastab omaniku nime. Selleks sisestame j\u00e4rgmise valemi:<\/p>\n<p><strong>=INDEX(A2:D9;F3;2)<\/strong><\/p>\n<p>Jah, korteri numbri peab ise sisestama. Aga mis siis saab, kui korteri number mida andmebaasis pole? Siis peaks kasutajat veast teavitama.<\/p>\n<p><strong>=IFERROR(INDEX(A2:D9;F3;2);&#8221;Viga korteri numbris!&#8221;)<br \/>\n<\/strong><\/p>\n<p id=\"XHgdMxV\"><img loading=\"lazy\" decoding=\"async\" width=\"485\" height=\"80\" class=\"alignnone size-full wp-image-477 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac575c4789.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac575c4789.png 485w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac575c4789-300x49.png 300w\" sizes=\"auto, (max-width: 485px) 100vw, 485px\" \/><\/p>\n<p><strong>MATCH()<\/strong><\/p>\n<p><strong>MATCH()<\/strong>\u00a0funktsiooni abil leiame otsitava v\u00e4\u00e4rtuse\u00a0<strong>j\u00e4rjekorra numbri<\/strong>\u00a0massiivis. Lisaks massiivile ja otsitavale v\u00e4\u00e4rtusele tuleb m\u00e4\u00e4rata otsitava v\u00e4\u00e4rtuse t\u00e4psus:<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li><strong>1 v\u00f5i puudub\u00a0<\/strong>&#8211; otsimassiivist otsitakse v\u00e4iksemat v\u00e4\u00e4rtust, mis ei \u00fcletaks otsiv\u00e4\u00e4rtust<\/li>\n<li><strong>0<\/strong>&#8211; etteantud ja otsitav v\u00e4\u00e4rtus peavad t\u00e4pselt \u00fchtima.<\/li>\n<li><strong>-1<\/strong>&#8211; otsitakse suurimat v\u00e4\u00e4rtust, mis ei oleks v\u00e4iksem otsiv\u00e4\u00e4rtuset.<\/li>\n<\/ul>\n<p>Vaatame uuesti nimede tabelit &#8211; soovin teada saada mitmes nimekirjas on &#8220;kalle&#8221; ning otsin t\u00e4pset vastet.<\/p>\n<p id=\"bPVmJaG\"><img loading=\"lazy\" decoding=\"async\" width=\"316\" height=\"136\" class=\"alignnone size-full wp-image-478 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac58b89473.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac58b89473.png 316w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac58b89473-300x129.png 300w\" sizes=\"auto, (max-width: 316px) 100vw, 316px\" \/><\/p>\n<p>Juhul kui tabel ei eelda t\u00e4pseid v\u00e4\u00e4rtusi ja antud on vahemikud, \u00a0tuleb kasutada 1 v\u00f5i -1 t\u00e4psust.\u00a0Kui v\u00f5rdluse t\u00fc\u00fcbiks on 1, peavad otsimassiivi elemendid olema kasvavas, t\u00fc\u00fcbi -1 puhul kahanevas j\u00e4rjestuses.\u00a0Allj\u00e4rgnevas tabelis vahemikud 0, 100, 250 ja 500. Kui otsitav on 200, siis soovin saada teist tulemust, sest see ei \u00fcleta j\u00e4rgmist piiri. Selleks kasutan t\u00e4psust 1.<\/p>\n<p id=\"cFccbMr\"><img loading=\"lazy\" decoding=\"async\" width=\"371\" height=\"127\" class=\"alignnone size-full wp-image-479 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac59ad7a29.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac59ad7a29.png 371w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac59ad7a29-300x103.png 300w\" sizes=\"auto, (max-width: 371px) 100vw, 371px\" \/><\/p>\n<p><strong>INDEX() ja MATCH()<\/strong><\/p>\n<p>Sagedamini kasutatakse INDEX() ja MATCH() funktsiooni pesastatuna. INDEX() vastutab ikka lahtri sisu eest ja MATCH() leiab INDEX() jaoks reanumbri. Keeruline? Vaatame n\u00e4idet:<\/p>\n<p id=\"FdnPlFp\"><img loading=\"lazy\" decoding=\"async\" width=\"731\" height=\"164\" class=\"alignnone size-full wp-image-480 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac60ea7e56.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac60ea7e56.png 731w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac60ea7e56-300x67.png 300w\" sizes=\"auto, (max-width: 731px) 100vw, 731px\" \/><\/p>\n<p>Tegemist on pisikese s\u00f5naraamatuga. Kui mina sisestan t\u00f5lkelahtrisse ingliskeelse s\u00f5na, siis Excel &#8220;\u00fctleb&#8221; mulle selle eestikeelse vaste.<\/p>\n<p><strong>=INDEX(A3:A6; MATCH(D2; B3:B6; 0))<br \/>\n<\/strong><\/p>\n<p id=\"AspWBLb\"><img loading=\"lazy\" decoding=\"async\" width=\"733\" height=\"250\" class=\"alignnone size-full wp-image-481 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac61f35286.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac61f35286.png 733w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac61f35286-300x102.png 300w\" sizes=\"auto, (max-width: 733px) 100vw, 733px\" \/><\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<p>S\u00f5naraamatus kasutasime ainult rea j\u00e4rgi otsimist. Aga vaatame tabelit, kus v\u00f5tame kasutusele nii read kui veerud. V\u00f5tame n\u00e4itena k\u00fcmne aasta poiste ja t\u00fcdrukute s\u00fcnnid.<\/p>\n<p id=\"ehVQELl\"><img loading=\"lazy\" decoding=\"async\" width=\"458\" height=\"289\" class=\"alignnone size-full wp-image-482 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac6329939f.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac6329939f.png 458w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac6329939f-300x189.png 300w\" sizes=\"auto, (max-width: 458px) 100vw, 458px\" \/><\/p>\n<p>Eesm\u00e4rgiks on leida Soo ja Aasta sisestamisel s\u00fcndinud laste arv. Valem tundub v\u00f5ibolla pikk ja lohisev aga k\u00f5ik on loogiline. Nimelt nii read kui veerud leiame MATCH() abil.<\/p>\n<p id=\"mKwACLk\"><img loading=\"lazy\" decoding=\"async\" width=\"749\" height=\"302\" class=\"alignnone size-full wp-image-483 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac657c9dc0.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac657c9dc0.png 749w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac657c9dc0-300x121.png 300w\" sizes=\"auto, (max-width: 749px) 100vw, 749px\" \/><\/p>\n<p><strong>VLOOKUP() ja HLOOKUP()<\/strong><\/p>\n<p>VLOOKUP() ja HLOOKUP() on olemuselt sarnased otsingufunktsioonid.<strong>\u00a0VLOOKUP()<\/strong>\u00a0otsib\u00a0<strong>vertikaalses<\/strong>\u00a0tabelis ning\u00a0<strong>HLOOKUP()<\/strong>\u00a0<strong>horisontaalses<\/strong>\u00a0tabelis.<\/p>\n<p>Valime \u00f5ppimiseks tabeli, kus meil on tehtud klientidele tehtud Sooduka tabel.<\/p>\n<p id=\"sRxsZgW\"><img loading=\"lazy\" decoding=\"async\" width=\"521\" height=\"200\" class=\"alignnone size-full wp-image-484 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac67202b6c.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac67202b6c.png 521w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac67202b6c-300x115.png 300w\" sizes=\"auto, (max-width: 521px) 100vw, 521px\" \/><\/p>\n<p>Kui klient tellib \u00fcle 500\u20ac, saab ta 2% soodukat jne. Vaikimisi kasutab VLOOKUP() ja HLOOKUP() ligikaudset vastust. Massiivina tuleb \u00e4ra m\u00e4rgistada k\u00f5ik andmed ning veeru arv, kust vastus tuleb leida.<\/p>\n<p id=\"cFtgAok\"><img loading=\"lazy\" decoding=\"async\" width=\"702\" height=\"227\" class=\"alignnone size-full wp-image-485 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac69905125.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac69905125.png 702w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac69905125-300x97.png 300w\" sizes=\"auto, (max-width: 702px) 100vw, 702px\" \/><\/p>\n<p>T\u00c4PSE vastuse leidmiseks tuleb lisada funktsioonile neljas argument &#8211; FLASE.<\/p>\n<p><strong>LOOKUP()<\/strong><\/p>\n<p>LOOKUP() on \u00a0v\u00e4ga sarnane eelmistele ja isegi lihtsam. Selle ainuke\u00a0puudus on, et otsitav massiiv peab olema j\u00e4rjestatud kasvavalt!<\/p>\n<p>K\u00f5ige lihtsam on m\u00e4\u00e4rata otsitav ja massiiv kust soovid seda leida.<\/p>\n<p id=\"kXiZOJj\"><img loading=\"lazy\" decoding=\"async\" width=\"705\" height=\"219\" class=\"alignnone size-full wp-image-486 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac6b78667e.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac6b78667e.png 705w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac6b78667e-300x93.png 300w\" sizes=\"auto, (max-width: 705px) 100vw, 705px\" \/><\/p>\n<p>Juhul kui v\u00e4\u00e4rtused on erikohtades, siis on oluline j\u00e4lgida, et andete arv oleks ikka sama.<\/p>\n<p id=\"ZbludOZ\"><img loading=\"lazy\" decoding=\"async\" width=\"739\" height=\"231\" class=\"alignnone size-full wp-image-487 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac6c801b9a.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac6c801b9a.png 739w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_573ac6c801b9a-300x94.png 300w\" sizes=\"auto, (max-width: 739px) 100vw, 739px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>K\u00f5ik otsingu- ja viitefunktsioonid<\/strong><\/p>\n<table>\n<tbody>\n<tr>\n<td><strong>Funktsioon<\/strong><\/td>\n<td><strong>Kirjeldus<\/strong><\/td>\n<\/tr>\n<tr>\n<td><strong>ADDRESS<\/strong><\/td>\n<td>Annab vastuseks tekstiviite t\u00f6\u00f6lehe \u00fchele lahtrile.<\/td>\n<\/tr>\n<tr>\n<td><strong>AREAS<\/strong><\/td>\n<td>Annab vastuseks viites olevate alade arvu.<\/td>\n<\/tr>\n<tr>\n<td><strong>CHOOSE<\/strong><\/td>\n<td>Annab vastuseks \u00fche v\u00e4\u00e4rtuse v\u00e4\u00e4rtuste loendist.<\/td>\n<\/tr>\n<tr>\n<td><strong>COLUMN<\/strong><\/td>\n<td>Annab vastuseks viite veerunumbri.<\/td>\n<\/tr>\n<tr>\n<td><strong>COLUMNS<\/strong><\/td>\n<td>Annab vastuseks viites olevate veergude arvu.<\/td>\n<\/tr>\n<tr>\n<td><strong>HLOOKUP<\/strong><\/td>\n<td>Otsib v\u00e4\u00e4rtust massiivi esimesest reast ja annab vastuseks n\u00e4idatud lahtri v\u00e4\u00e4rtuse.<\/td>\n<\/tr>\n<tr>\n<td><strong>HYPERLINK<\/strong><\/td>\n<td>Loob otsetee, mis avab v\u00f5rguserveris, sisev\u00f5rgus v\u00f5i Internetis talletatud dokumendi.<\/td>\n<\/tr>\n<tr>\n<td><strong>INDEX<\/strong><\/td>\n<td>Otsib indeksi abil v\u00e4\u00e4rtuse viitest v\u00f5i massiivist.<\/td>\n<\/tr>\n<tr>\n<td><strong>INDIRECT<\/strong><\/td>\n<td>Annab vastuseks tekstiv\u00e4\u00e4rtuse poolt viidatud viite.<\/td>\n<\/tr>\n<tr>\n<td><strong>LOOKUP<\/strong><\/td>\n<td>Otsib v\u00e4\u00e4rtusi vektorist v\u00f5i massiivist.<\/td>\n<\/tr>\n<tr>\n<td><strong>MATCH<\/strong><\/td>\n<td>Otsib v\u00e4\u00e4rtusi viitest v\u00f5i massiivist.<\/td>\n<\/tr>\n<tr>\n<td><strong>OFFSET<\/strong><\/td>\n<td>Annab vastuseks viite kauguse antud viitest.<\/td>\n<\/tr>\n<tr>\n<td><strong>ROW<\/strong><\/td>\n<td>Annab vastuseks viite reanumbri.<\/td>\n<\/tr>\n<tr>\n<td><strong>ROWS<\/strong><\/td>\n<td>Annab vastuseks viites olevate ridade arvu.<\/td>\n<\/tr>\n<tr>\n<td><strong>RTD<\/strong><\/td>\n<td>Laadib reaalajas alla andmed programmist, mis toetab\u00a0COM-automatiseerimist<\/td>\n<\/tr>\n<tr>\n<td><strong>TRANSPOSE<\/strong><\/td>\n<td>Annab vastuseks transponeeritud massiivi.<\/td>\n<\/tr>\n<tr>\n<td><strong>VLOOKUP<\/strong><\/td>\n<td>Otsib massiivi esimesest veerust n\u00e4idatud v\u00e4\u00e4rtusega lahtri ja annab vastuseks lahtri v\u00e4\u00e4rtuse, liikudes \u00fcle rea.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/helia.ee\/koolitus\/dokumendid\/Excel2016_Otsingu_Funktsioonid.pdf\"><br \/>\nAllalaaditav PDF<\/a><\/p>\n<p><a href=\"http:\/\/helia.ee\/koolitus\/dokumendid\/harjutus_otsingufunktsioonid.xlsx\">Harjutus 13<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Otsingu- ja viitefunktsioonid on m\u00f5eldud n\u00e4iteks kiiresti suurtest tabelistest andmete leidmiseks ja luua omavahelisi seoseid. K\u00f5ige selle teostamiseks on mitu erinevat funktsiooni, mis p\u00f5him\u00f5tteliselt v\u00f5ivad teha sama asja ning erinevus seisneb teostuses. INDEX() INDEX()\u00a0funktsioon leiab massiivist\u00a0reanumbri j\u00e4rgi lahtri sisu. Tegemist on rea ja veeru ristumiskohaga. V\u00f5tame n\u00e4iteks nimede massiivi ja palume v\u00e4ljastada kolmanda rea sisu. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":177,"menu_order":31,"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-459","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages\/459","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=459"}],"version-history":[{"count":4,"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages\/459\/revisions"}],"predecessor-version":[{"id":488,"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages\/459\/revisions\/488"}],"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=459"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}