Kui ütled “Excel”, siis Exceli funktsioonid on kindlasti üheks märksõnaks, mis pähe tuleb. Eelmises postituses Exceli valemid ja enimkasutatud funktsioonid käsitlesin valemite ja funktsioonide erinevusi ning tõin hulganisti näited enimkasutatavatest matemaatilistest ja statistilistest funktsioonidest. Seekord on vaatluse all loogika-, teksti ning kuupäeva- ja ajafunktsioonid.
Loogikafunktsioonid
Loogikafunktsioonides on enimlevinud tingimusfunktsioon IF: IF funktsiooni sisestamisel annab Excel vihje:
IF(loogilisuse_test;(väärtus_kui_tõene);(väärtus_kui_väär))
Loogilisuse test on olulisim osa, kuna võimaldab seada tingimuse – miski on suurem, väiksem võrdne, mittevõrdne, suurem ja võrdne, väiksem ja võrdne jms. Nt d1=100 või siis s3<5. Tegu on seega kontrolltingimusega, millele peab mingi väärtus vastama või vastupidi, ei tohi vastata.
(väärtus_kui_tõene) sinna kirjuta siis see, mida lahtrisse kirjutada, kui loogilisuse test tõele vastab. Selleks võib olla mingi number, tekst, valem või uus/uued Exceli funktsioonid.
(väärtus_kui_väär) kirjuta väärtus, mida lahtris kuvatakse, kui loogilisuse test tõele ei vasta. Samamoodi võivad selleks olla mingi number, tekst, valem või uus/uued Exceli funktsioonid.
Nt allolevas funktsioonis on kirjas, et kui L veerus olev number on võrdne või suurem kui 18, siis kirjutatakse M veergu sõna JAH, kui see ei vasta tõele, siis kirjutatakse EI. Nagu juba eelneval mainitud, siis teksti lisamisel funktsiooni tuleb see eraldada jutumärkidega.
Kui funktsioon on kirja pandud, siis vajuta ENTER ja kopeeri täitepidemest kinni hoides valem ka järgmistele ridadele.
Teine näide, mida ma siinkohal toon IF funktsiooni kasutamise kohta on järgmine. Ma soovin, et lahtrisse R kirjutatakse sõna mees, kui lahtris Q on M täht ning kui seda seal ei ole, siis kirjutatakse lahtrisse R hoopis sõna naine. Antud juhul on IF funktsiooni lihtne kasutada, kuna valikuid on vaid kaks naine ja mees, N ja M. Jällegi tuleb meeles pidada, et kõik tekstid, mida valemis kasutad, tuleb kirjutada jutumärkidesse.
Mõnikord on tingimusi enam kui kaks, mida soovime välja tuua. Sel juhul kirjutame VÄÄRA vastuse asemel hoopis uue IF() funktsiooni jne. Nimelt võib neid IF lauseid kirjutada üksteise sisse mitmeid ja mitmeid – just nii palju, kui palju erinevaid tingimusi Sul on.
Näiteks teevad õpilased kontrolltöö, mille eest on võimalik saada maksimaalselt 100 punkti. See, kes saab 90 ja rohkem punkti, saab hindeks 5, kes saab vähemalt 75 punkti, saab hindaks 4, vähemalt 50 punkti annab 3, alla selle on 2. Kui Sul on teada õpilaste punktide arv, saad IF funktsiooni abil leida hinded.
Esimesena paned paika tingimuse, kui punktide arv on suurem või võrdne 90-ga, kirjutatakse hinde lahtrisse number 5 (=IF(L53>=90;5;. Järgmise hinde lisamiseks alusta uut IF funktsiooni ja kirjelda ära, millal kirjutatakse lahtrisse number neli – IF(L53>=75;4; – ning seejärel number kolm – IF(L53>=50;3;. Kui veerus L ei vasta mõni number ülaltoodud tingimustele, kirjutatakse hinde lahtrisse number 2. Vajuta ENTER ja kopeeri valem täitepideme abil tabeli lõpuni. Jälgi ka seda, et kuna iga funktsioon algab sulgudega, et funktsiooni lõpus kõik sulud olemas oleksid. Siin tuleb Excel appi eri värvidega, et näeksid, millised sulud Sa just lõpetasid.
Tekstifunktsioonid
PROPER() muudab iga sõna esimese tähe suureks. Seda on hea kasutada seega nimede kirjutamisel. Alltoodud näites olen valemit kasutanud veerus C ning kirjutanud valemi ilma võrdusmärgita välja veergu D. Näiteks maarja maa -> Maarja Maa.
UPPER() kirjutab tekstis kõik tähed suurtena. Näiteks maarja maa -> MAARJA MAA
LOWER() muudab aga kõik tähed väikesteks. Näiteks MAARJA MAA -> maarja maa
LEN() näitab Sulle ära, mitu märki Sinu otsitavas lahtris/lahtrites on. Alltoodud näites loendas LEN funktsioon lahtris 24 i tähte.
LEFT() toob välja valitud lahtris olevast tekstist just nii palju tähemärke teksti vasakult poolt, kui Sa soovid. Alltoodud näites kuvatakse lahtris C89 sõnast „ajalugu“ vaid 3 esimest tähte vasakult.
RIGHT() teeb seda sama asja ja toob välja Sinu soovitud tähemärkide arvu jagu tähti valitud teksti paremalt poolt. All näites soovisin sõnast „ajalugu“ kuvada neli viimast tähte paremalt.
MID() võtab välja tähemärgid sõna keskelt. Näites soovisin, et Excel kuvaks sõnast „ajalugu“ neli tähte sõna keskelt alates teisest tähest.
Kuupäeva- ja ajafunktsioonid
DATE() moodustab kuupäeva, kui see asub algselt kolmes erinevas lahtris. Nt lahtris A on päev(4), lahtris B on kuu (3) ja lahtris C aasta (2006), kirjutades lahtrisse D =DATE(A2;B2;C2), saad lahtrisse D 4. märts 2006. Kuupäeva vorm oleneb sellest, milline Sul hetkel valitud on.
YEAR() eraldab kuupäevast aasta.
MONTH() eraldab kuupäevast kuu – 1-12.
DAY() eraldab kuupäevast päeva.
HOUR() eraldab ajaväärtusest tunnid – 0-23.
MINUTE() eraldab ajaväärtusest minutid – 0-59.
SECOND() eraldab ajaväärtusest sekundid – 0-59.
WEEKDAY() näitab kuupäeva alusel, millise nädalapäevaga tegu on. Vastus antakse numbrina. Funktsioonis on oluline lisada ka tüüp. Vaikimisi on kasutusel tüüp 1, kus nädal algab pühapäevaga s.t pühapäev on nr 1, esmaspäev on nr 2. Meil on aga kasutusel tüüp 2.
NETWORKDAYS() annab Sulle teada, palju on Sinu poolt etteantud ajavahemikus tööpäevi. Nt alguskuupäev on 02.01.2020 ja lõpukuupäev 20.02.2020. Kasutades funktsiooni NETWORKDAYS() saame teada, et antud ajavahemikul on 36 tööpäeva. Sina pead vaid jälgima, mitu püha ehk töölt vaba päeva sellesse vahemikku jääb – selle arvu saad funktsiooni viimaseks numbriks märkida.
Alltoodud joonisel on helesinises lahtris kasutatud valemit, mis kuvatakse selle lahtri all.
Muutlikud funktsioonid on sellised, mille sulud jäetakse tühjaks ning funktsioon ise genereerib väärtuse. Nt
TODAY() väljastab tänase kuupäeva. Kui oled selle funktsiooni tabelisse lisanud, siis näitab see tabeli avades just seda kuupäeva, mis parasjagu käes on.
NOW() aga loob tänase kuupäeva koos kellaajaga.
Ka kuupäevadega saab teha tehteid.
- Üleeile =TODAY()-2.
- Homme =TODAY()+1.
- Mitu päeva on jäänud selle aasta lõpuni? =DATE(2019;12;31)-TODAY()
- Isikukoodist sünniaja leidmine, kui isikukood asub nt lahtris A2 (48803289933). =DATE(MID(A2;2;2);MID(A2;4;2);MID(A2;6;2)). Vastuseks on 28.03.1988.
Kokkuvõtteks
Sellised olid minu õppetunnid, mida tasuta Exceli koolituselt sain. Kui enne oskasin kasutada vaid murdosa Exceli võimalustest, siis nüüd on teadmised kindlasti vähemalt murdosa võrra paranenud. 😉
Muide, leht, kus tasuta Exceli koolitus üleval ja veel palju muud, on https://www.metshein.com/. Head avastamist ja õppimist!
Kui said ka ise mõne kasuliku mõtte sellest postitusest, siis palun jaga seda ka oma sõpradega!