{"id":423,"date":"2016-05-03T14:14:41","date_gmt":"2016-05-03T11:14:41","guid":{"rendered":"http:\/\/helia.ee\/koolitus\/?page_id=423"},"modified":"2016-10-26T11:03:08","modified_gmt":"2016-10-26T08:03:08","slug":"microsoft-excel-2016-loogikafunktsioonid","status":"publish","type":"page","link":"https:\/\/helia.ee\/koolitus\/?page_id=423","title":{"rendered":"Microsoft Excel 2016 \u2013 Loogikafunktsioonid"},"content":{"rendered":"<p>Loogikafunkstioonide grupp on \u00fcks v\u00e4iksemaid kuid \u00fcks v\u00f5imsamaid. Loogikafunktsioone kasutame otsuste tegemiseks ja selle eestvedajaks on\u00a0<strong>IF()<\/strong>\u00a0funktsioon. Vaatame IF() funktsiooni s\u00fcntaksit:<\/p>\n<p>=IF(tingimus;v\u00e4\u00e4rtus_kui_t\u00f5ene;v\u00e4\u00e4rtus_kui_v\u00e4\u00e4r)<\/p>\n<p>Funktsioon koosneb kolmest osast:<\/p>\n<ul>\n<li><strong>tingimus\u00a0<\/strong>&#8211; tingimus, mille peate seadma. Kasutatakse tuntud\u00a0v\u00f5rdlustehteid\n<ul>\n<li><strong>&gt;<\/strong>\u00a0(suurem kui)<\/li>\n<li><strong>&lt;\u00a0<\/strong>(v\u00e4iksem kui)<\/li>\n<li><strong>=<\/strong>\u00a0(v\u00f5rdne)<\/li>\n<li><strong>&gt;=<\/strong>\u00a0(suurem v\u00f5i v\u00f5rdne)<\/li>\n<li><strong>&lt;=<\/strong>\u00a0(v\u00e4iksem v\u00f5i v\u00f5rdne)<\/li>\n<li><strong>&lt;&gt;<\/strong>\u00a0(ei v\u00f5rdu)<\/li>\n<\/ul>\n<\/li>\n<li><strong>v\u00e4\u00e4rtus_kui_t\u00f5ene<\/strong>\u00a0&#8211; kui seatud tingimus on T\u00d5ENE (ingl. k\u00a0<em>true<\/em>), siis teostatakse j\u00e4rgmine tegevus, milleks v\u00f5ib olla tekst, number, valem v\u00f5i uus funktsioon<\/li>\n<li><strong>v\u00e4\u00e4rtus_kui_v\u00e4\u00e4r<\/strong>\u00a0&#8211; \u00a0kui seatud tingimus on V\u00c4\u00c4R(ingl. k\u00a0<em>false<\/em>), siis teostatakse j\u00e4rgmine tegevus, milleks v\u00f5ib olla tekst, number, valem v\u00f5i uus funktsioon\n<p id=\"BtGncGo\"><img loading=\"lazy\" decoding=\"async\" width=\"517\" height=\"229\" class=\"alignnone size-full wp-image-424 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728852f0ee9e.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728852f0ee9e.png 517w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728852f0ee9e-300x133.png 300w\" sizes=\"auto, (max-width: 517px) 100vw, 517px\" \/><\/p>\n<\/li>\n<\/ul>\n<p><strong>N\u00e4ide 1\u00a0<\/strong><\/p>\n<p>Vaatame n\u00e4idet, kus \u00fches veerus on t\u00e4hised &#8216;m&#8217; ja &#8216;n&#8217;.<\/p>\n<p id=\"JQLMQdy\"><img loading=\"lazy\" decoding=\"async\" width=\"169\" height=\"254\" class=\"alignnone size-full wp-image-425 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57288551d579c.png\" alt=\"\" \/><\/p>\n<p>Laseme programmil otsustada, et kui lahtris on &#8216;m&#8217;, siis kirjutatakse k\u00f5rvallahtrisse s\u00f5na &#8216;mees&#8217; ja kui &#8216;n&#8217;, siis &#8216;naine&#8217;. Vaatame otsustamist skeemilt:<\/p>\n<p id=\"PRGatRK\"><img loading=\"lazy\" decoding=\"async\" width=\"450\" height=\"195\" class=\"alignnone size-full wp-image-426 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885650ccc7.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885650ccc7.png 450w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885650ccc7-300x130.png 300w\" sizes=\"auto, (max-width: 450px) 100vw, 450px\" \/><\/p>\n<p>Funktsioonina n\u00e4eb rida v\u00e4lja selline:<br \/>\n<img loading=\"lazy\" decoding=\"async\" width=\"308\" height=\"257\" class=\"alignnone size-full wp-image-427 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728857b9558d.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728857b9558d.png 308w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728857b9558d-300x250.png 300w\" sizes=\"auto, (max-width: 308px) 100vw, 308px\" \/><\/p>\n<ol>\n<li>=IF(A2=&#8221;m&#8221;;&#8221;mees&#8221;;&#8221;naine&#8221;)<\/li>\n<\/ol>\n<p><strong>N\u00e4ide 2<\/strong><\/p>\n<p>Vaatame n\u00e4idet arvudega. Meil on olemas teenitud palk ja tehtud t\u00fckit\u00f6\u00f6.<\/p>\n<p id=\"BwViMuB\"><img loading=\"lazy\" decoding=\"async\" width=\"339\" height=\"251\" class=\"alignnone size-full wp-image-428 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885c93d73a.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885c93d73a.png 339w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885c93d73a-300x222.png 300w\" sizes=\"auto, (max-width: 339px) 100vw, 339px\" \/><\/p>\n<p>Motiveerime t\u00f6\u00f6tajaid &#8211; kui t\u00fckit\u00f6\u00f6 \u00fcletab 8, siis anname preemiat 100. Kui mitte, siis j\u00e4\u00e4b palk samaks. Vaatame skeemilt:<\/p>\n<p id=\"UZHtUhX\"><img loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"194\" class=\"alignnone size-full wp-image-429 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885d9cbe80.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885d9cbe80.png 454w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885d9cbe80-300x128.png 300w\" sizes=\"auto, (max-width: 454px) 100vw, 454px\" \/><\/p>\n<p>Funktsioon n\u00e4eb v\u00e4lja selline<\/p>\n<ol>\n<li>=IF(B2&gt;8;A2+100;A2)\n<p id=\"AFUIUOa\"><img loading=\"lazy\" decoding=\"async\" width=\"337\" height=\"253\" class=\"alignnone size-full wp-image-430 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885ea13a69.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885ea13a69.png 337w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572885ea13a69-300x225.png 300w\" sizes=\"auto, (max-width: 337px) 100vw, 337px\" \/><\/p>\n<\/li>\n<\/ol>\n<p><strong>N\u00e4ide 3<\/strong><\/p>\n<p>\u00dcks v\u00f5imalus, mida palju kasutatakse ja mis ajab kasutajad segadusse on IF() funktsioonid \u00fcksteise sees. See t\u00e4hendab seda, et kui n\u00e4iteks funktsioon ei vasta tingimusele, siis saab esitada uue tingimuse jne jne. Seda nimetatakse\u00a0<strong>pesastamiseks.\u00a0<\/strong>Vanemad versioonid toetasid kuni 7 pesaastamist ning uus saab hakkama kuni 64-ga.<\/p>\n<p>V\u00f5tame n\u00e4itena eksami tabeli, kus laseme Excelil lisada automaatselt \u00f5ppuritele hinde.<\/p>\n<p id=\"XdzjudK\"><img loading=\"lazy\" decoding=\"async\" width=\"501\" height=\"278\" class=\"alignnone size-full wp-image-431 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57288622d5e43.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57288622d5e43.png 501w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57288622d5e43-300x166.png 300w\" sizes=\"auto, (max-width: 501px) 100vw, 501px\" \/><\/p>\n<p>Kogu eksam oli 20 punkti. Kui \u00f5ppur sai 18p sai hindeks &#8220;5&#8221;, kui 15p hinne &#8220;4&#8221;, 9p hinne &#8220;3&#8221; ja alla 9p saab hindeks &#8220;MA&#8221; (mittearvestatud). Paremaks arusaamiseks panen m\u00f5tte skeemi.<\/p>\n<p>Kasutan j\u00e4rgmist funktsiooni, kus V\u00c4\u00c4R vastuse kohale kirjutan uue tingimuse.<\/p>\n<p id=\"NIQrAKl\"><img loading=\"lazy\" decoding=\"async\" width=\"707\" height=\"402\" class=\"alignnone size-full wp-image-432 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572886713fc9b.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572886713fc9b.png 707w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572886713fc9b-300x171.png 300w\" sizes=\"auto, (max-width: 707px) 100vw, 707px\" \/><\/p>\n<ol>\n<li>=IF(B3&gt;=18;5;\u00a0IF(B3&gt;=15;4;\u00a0IF(B3&gt;=9;3;\u00a0&#8220;MA&#8221;)))<\/li>\n<\/ol>\n<p><strong>AND, OR ja NOT<br \/>\n<\/strong><\/p>\n<p id=\"NobwlLA\"><img loading=\"lazy\" decoding=\"async\" width=\"341\" height=\"251\" class=\"alignnone size-full wp-image-433 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57288785c8523.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57288785c8523.png 341w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_57288785c8523-300x221.png 300w\" sizes=\"auto, (max-width: 341px) 100vw, 341px\" \/><\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<p>Tegemist on funktsioonidega, mida reeglina eraldi ei kasutata, vaid ikka koos IF() funktsiooniga. K\u00f5igi kolme \u00a0tulemuseks on alati T\u00d5ENE v\u00f5i V\u00c4\u00c4R. <strong>AND()<\/strong>\u00a0funktsiooni kasutatakse kui <strong>k\u00f5ik <\/strong>argumendid peavad olema t\u00f5esed. N\u00e4iteks mul on nimekiri vanustega. Soov on teada saada kes on teismelised. Kasutan siin AND() funktsiooni, kus on kaks tingimust &#8211; vanus peab olema \u00fcle 10 <strong>JA <\/strong>alla 20.<\/p>\n<p id=\"adRDcXE\"><img loading=\"lazy\" decoding=\"async\" width=\"416\" height=\"134\" class=\"alignnone size-full wp-image-434 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572887a06afd8.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572887a06afd8.png 416w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572887a06afd8-300x97.png 300w\" sizes=\"auto, (max-width: 416px) 100vw, 416px\" \/><\/p>\n<p><strong>OR()<\/strong>\u00a0funktsiooni kasutame kui t\u00e4idetud peab olema <strong>ainult \u00fcks<\/strong> argument. N\u00e4iteks kontrollime, kas vanus on \u00fcle 20 <strong>V\u00d5I<\/strong>\u00a0alla 10.<\/p>\n<p id=\"zXshCuJ\"><img loading=\"lazy\" decoding=\"async\" width=\"419\" height=\"136\" class=\"alignnone size-full wp-image-435 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572887ccccc23.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572887ccccc23.png 419w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_572887ccccc23-300x97.png 300w\" sizes=\"auto, (max-width: 419px) 100vw, 419px\" \/><\/p>\n<p>Kui OR() v\u00f5i AND() funktsioon kujuneb liiga pikaks, siis on m\u00f5tekas kaaluda eituse funktsiooni <strong>NOT()<\/strong>. Loome funktsiooni, mis &#8220;\u00fctleb&#8221;, et kui kasutaja <strong>EI OLE<\/strong>\u00a0\u00fcle 20, siis on ta noor, vastasel juhul vana.<\/p>\n<p id=\"mNHDFZI\"><img loading=\"lazy\" decoding=\"async\" width=\"417\" height=\"134\" class=\"alignnone size-full wp-image-436 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728880d56ea3.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728880d56ea3.png 417w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728880d56ea3-300x96.png 300w\" sizes=\"auto, (max-width: 417px) 100vw, 417px\" \/><\/p>\n<p><strong>Veateated<\/strong><\/p>\n<p>Veateadetele nagu \u00a0#N\/A, #VALUE!, #REF!, #DIV\/0!, #NUM!, #NAME? v\u00f5i #NULL! \u00a0reageerimiseks on loogikafunkstioonidesse lisatud <strong>IFERROR()<\/strong>\u00a0funktsioon. N\u00e4iteks kui sinu lahendus annab veateate, siis saab sellest kasutajat teavitada oma tekstiga.<\/p>\n<p id=\"RfkMYxz\"><img loading=\"lazy\" decoding=\"async\" width=\"417\" height=\"134\" class=\"alignnone size-full wp-image-437 \" src=\"http:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728881a53846.png\" alt=\"\" srcset=\"https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728881a53846.png 417w, https:\/\/helia.ee\/koolitus\/wp-content\/uploads\/2016\/05\/img_5728881a53846-300x96.png 300w\" sizes=\"auto, (max-width: 417px) 100vw, 417px\" \/><\/p>\n<p><strong>K\u00f5ik loogikafunktsioonid<\/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>AND<\/strong><\/td>\n<td>Annab vastuseks v\u00e4\u00e4rtuse TRUE, kui k\u00f5igi selle argumentide v\u00e4\u00e4rtus on TRUE.<\/td>\n<\/tr>\n<tr>\n<td><strong>FALSE<\/strong><\/td>\n<td>Annab vastuseks loogikav\u00e4\u00e4rtuse FALSE.<\/td>\n<\/tr>\n<tr>\n<td><strong>IF<\/strong><\/td>\n<td>M\u00e4\u00e4rab loogikaavaldise t\u00f5esusv\u00e4\u00e4rtuse.<\/td>\n<\/tr>\n<tr>\n<td><strong>IFERROR<\/strong><\/td>\n<td>Annab vastuseks teie m\u00e4\u00e4ratud v\u00e4\u00e4rtuse, kui valem annab tulemuseks vea, muul juhul annab vastuseks valemi tulemi.<\/td>\n<\/tr>\n<tr>\n<td><strong>NOT<\/strong><\/td>\n<td>Muudab argumendi t\u00f5esusv\u00e4\u00e4rtuse vastupidiseks.<\/td>\n<\/tr>\n<tr>\n<td><strong>OR<\/strong><\/td>\n<td>Annab vastuseks v\u00e4\u00e4rtuse TRUE, kui m\u00f5ni argumendi v\u00e4\u00e4rtus on TRUE.<\/td>\n<\/tr>\n<tr>\n<td><strong>TRUE<\/strong><\/td>\n<td>Annab vastuseks loogikav\u00e4\u00e4rtuse TRUE.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><a href=\"http:\/\/helia.ee\/koolitus\/dokumendid\/Excel2016_-_loogika_funktsioonid.pdf\">Allalaaditav PDF<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Loogikafunkstioonide grupp on \u00fcks v\u00e4iksemaid kuid \u00fcks v\u00f5imsamaid. Loogikafunktsioone kasutame otsuste tegemiseks ja selle eestvedajaks on\u00a0IF()\u00a0funktsioon. Vaatame IF() funktsiooni s\u00fcntaksit: =IF(tingimus;v\u00e4\u00e4rtus_kui_t\u00f5ene;v\u00e4\u00e4rtus_kui_v\u00e4\u00e4r) Funktsioon koosneb kolmest osast: tingimus\u00a0&#8211; tingimus, mille peate seadma. Kasutatakse tuntud\u00a0v\u00f5rdlustehteid &gt;\u00a0(suurem kui) &lt;\u00a0(v\u00e4iksem kui) =\u00a0(v\u00f5rdne) &gt;=\u00a0(suurem v\u00f5i v\u00f5rdne) &lt;=\u00a0(v\u00e4iksem v\u00f5i v\u00f5rdne) &lt;&gt;\u00a0(ei v\u00f5rdu) v\u00e4\u00e4rtus_kui_t\u00f5ene\u00a0&#8211; kui seatud tingimus on T\u00d5ENE (ingl. k\u00a0true), siis teostatakse [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":177,"menu_order":33,"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-423","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages\/423","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=423"}],"version-history":[{"count":2,"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages\/423\/revisions"}],"predecessor-version":[{"id":439,"href":"https:\/\/helia.ee\/koolitus\/index.php?rest_route=\/wp\/v2\/pages\/423\/revisions\/439"}],"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=423"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}