Exceli valemid ja funktsioonid - millal mida kasutada?

Valemid ja funktsioonid algavad alati võrdusmärgiga, millele järgneb valemi/funktsiooni nimi ja sulud. Sulgude sees on argumendid ehk näiteks arvud, millega tehteid tehakse, aga ka mingi teksti või viide lahtrile jms.

Exceli valemid ja funktsioonid – mis on neil vahet?
Exceli valemid on pigem lihtsakoelised – liidan, lahutan, korrutan ja jagan omavahel kahte arvu. Funktsioon seevastu on loodud keerulisemate tehete tegemiseks. Samas tuleb ära märkida, et saab funktsiooni kasutada samuti lihtsate tehete tegemiseks. Funktsioon on eeldefineeritud valem selleks, et lihtsustada tehete tegemist, vähendada vigu ja hoida kokku aega. Lisaks tehetele, saab funktsioonide abil kuvada tänast kuupäeva, leida arvuhulga keskmine väärtus või ka näiteks mediaanväärtus jms.

VALEMID EXCELIS on avaldis, mis kasutab väärtusi lahtri aadresside ja operaatorite vahemikus. Näiteks =A1+A2+A3, mis leiab väärtuste vahemiku summa lahtrist A1 kuni lahtrini A3. Näide valemist, mis koosneb diskreetsetest väärtustest nagu =6*3.

=A2 * D2 / 2

• "=" ütleb Excelile, et see on valem, ja ta peaks seda hindama.
• "A2" * D2" viitab lahtri aadressidele A2 ja D2, seejärel korrutab nendes lahtriaadressides leitud väärtused.
• "/" on jagamise aritmeetiline operaator
• "2" on diskreetne väärtus

Kuidas valemisse teksti lisada?
Excelis on võimalik lisaks arvudele liita kokku ka tekste. Selleks kasutatakse & märki. See märk tahab koheselt ja-ks muutuda. Kui nii juhtub, vajuta kustuta ja saad & märgi tagasi. Lisatavad tekstid tuleb aga kirjutada jutumärkide vahele s.h tühik. Exceli valemid on mängulised, kuna omavahel saab kokku liita erinevaid tehteid ja teksti. Järgnevas näites on kõik need asjad koos. Nimelt, kui ma soovin kirjutada, et Ave Aun ja Agu Aun olid abielus 26 aastat, saan ma kasutada alltoodud valemit.

FUNKTSIOON EXCELIS on eelmääratletud valem, mida kasutatakse konkreetsete väärtuste jaoks kindlas järjekorras. Funktsiooni kasutatakse kiirete toimingute jaoks, nagu lahtrivahemiku summa, loenduse, keskmise, maksimaalse väärtuse ja minimaalsete väärtuste leidmine. Näiteks allpool olev lahter A3 sisaldab funktsiooni SUM, mis arvutab vahemiku A1:A2 summa.

Mõned näited kombineeritud valemitest. Meil on olemas ringi raadius ning soovime arvutada ringi ümbermõõtu kahe komakoha täpsusega. Ringi ümbermõõdu saame, kui korrutame 2 x pii x raadius. Kirjutamist alustame ümardamise funktsiooniga, kus esmalt tuleb kirja panna arv, seejärel komakohtade arv. Komakohtade arvuks on 2, arvuks aga ringi ümbermõõt, mille leiame PRODUCT funktsiooniga. K16 on antud juhul r lahtri väärtus.

Arvutame näiteks kahe ruudu pindalad ja liidame need kokku. Selleks võtame ühe ruudu külje ruutu ja seejärel teise ruudu külje ruutu ning liidame need omavahel kokku. Excelis näeb asi välja järgmine.

• SUM() liidab kokku kõik numbrid/lahtrite vahemikud. Näiteks, kui soovid kokku liita arvud, mis asuvad lahtrite vahemikus A2 kuni B7, siis näeb funktsioon välja järgmine: =SUM(A2:B7). Kui soovid liita kokku mitu vahemikku arve, siis lisad vahemike vahele semikooloni. Näiteks soovime eelmise näite vahemikule liita juurde F4 kuni F8, siis kirjutame funktsiooni =SUM(A2:B7;F4:F8).
• PRODUCT() korrutab etteantud arvud või lahtrivahemiku. Nt 88 korda 41 arvutamiseks kirjutame =PRODUCT(88;41).
• ROMAN() teeb araabia numbritest rooma numbrid. Näiteks =ROMAN(33) annab vastuseks XXXIII.
• ARABIC() teisendab rooma numbri jällegi araabia numbriks. Näiteks =ARABIC(„CXI“) annab vastuseks 111. Antud juhul on tarvilik kasutada jutumärke, kuna Excel näeb Rooma numbrit kui teksti ning tekst tuleb alati jutumärkide vahele paigutada.
• SQRT() võtab arvust ruutjuure. Nt ruutjuur 49 kirjutatakse =SQRT(49), vastuseks on 7.
• POWER() võtab arvu astmesse. Nt soovid võtta arvu 56 astmesse 2, siis funktsioon näeb välja järgmine: =POWER(56;2). Vastuseks on 3136.
• ROUND() ümardab. Funktsiooni tuleb lisada esmalt soovitud arv, mida hakkad ümardama ning seejärel arv, mis näitab komakohtade arvu, milleni ümardatakse. Arvud 1-4 ümardatakse allapoole, 5-9 ülespoole. Nt funktsioon =ROUND(3,6666;2) annab vastuseks 3,37, kuid funktsioon =ROUND(3,33333;1) annab vastuseks 3,3. Kui Sa aga soovid tulemusena näha täisarvu, lisa komakohtade arvuks 0. Kui lisad aga komakohtade arvu ette miinusmärgi, siis ümardatakse lähima 10, 100, 1000 jne. Nt =ROUND(767,65;-1) annab vastuseks 770.
• PI() annab pii väärtuse. Antud juhul sulgudesse miskit kirjutama ei pea.

Matemaatilised funtsioonid

• SUM() liidab kokku kõik numbrid/lahtrite vahemikud. Näiteks, kui soovid kokku liita arvud, mis asuvad lahtrite vahemikus A2 kuni B7, siis näeb funktsioon välja järgmine: =SUM(A2:B7). Kui soovid liita kokku mitu vahemikku arve, siis lisad vahemike vahele semikooloni. Näiteks soovime eelmise näite vahemikule liita juurde F4 kuni F8, siis kirjutame funktsiooni =SUM(A2:B7;F4:F8).
• PRODUCT() korrutab etteantud arvud või lahtrivahemiku. Nt 88 korda 41 arvutamiseks kirjutame =PRODUCT(88;41).
• ROMAN() teeb araabia numbritest rooma numbrid. Näiteks =ROMAN(33) annab vastuseks XXXIII.
• ARABIC() teisendab rooma numbri jällegi araabia numbriks. Näiteks =ARABIC(„CXI“) annab vastuseks 111. Antud juhul on tarvilik kasutada jutumärke, kuna Excel näeb Rooma numbrit kui teksti ning tekst tuleb alati jutumärkide vahele paigutada.
• SQRT() võtab arvust ruutjuure. Nt ruutjuur 49 kirjutatakse =SQRT(49), vastuseks on 7.
• POWER() võtab arvu astmesse. Nt soovid võtta arvu 56 astmesse 2, siis funktsioon näeb välja järgmine: =POWER(56;2). Vastuseks on 3136.
• ROUND() ümardab. Funktsiooni tuleb lisada esmalt soovitud arv, mida hakkad ümardama ning seejärel arv, mis näitab komakohtade arvu, milleni ümardatakse. Arvud 1-4 ümardatakse allapoole, 5-9 ülespoole. Nt funktsioon =ROUND(3,6666;2) annab vastuseks 3,37, kuid funktsioon =ROUND(3,33333;1) annab vastuseks 3,3. Kui Sa aga soovid tulemusena näha täisarvu, lisa komakohtade arvuks 0. Kui lisad aga komakohtade arvu ette miinusmärgi, siis ümardatakse lähima 10, 100, 1000 jne. Nt =ROUND(767,65;-1) annab vastuseks 770.
• PI() annab pii väärtuse. Antud juhul sulgudesse miskit kirjutama ei pea.

Statistilised funktsioonid

• MIN() ja MAX() leiavad antud arvude vahemikust vastavalt väikseima ja suurima arvu. Nt soovime teada, milline on veerus D ridadel 1-600 suurim väärtus – funktsioon näeb välja järgmine =MAX(D1:D600). Kui soovid suurima arvu asemel hoopis väikseimat leida, kirjuta MAX asemel MIN.
• AVERAGE() arvutab aritmeetilise keskmise ehk siis liidab kõik väärtused kokku ja jagab objektide arvuga. Kui soovime veerus D leida ridadel 1-10 olevate arvude aritmeetilise keskmise, kirjutame valemi: =AVERAGE(D1:D10).
• MEDIAN() aitab ka leida arvude keskmist, kuid seda sel viisil, et jagab arvude massiivi võrdselt kaheks – pooled neist jäävad alla mediaani ja pooled on sellest suuremad. Nii ei ole keskmine väärtus niivõrd mõjutatud suurimatest ja väikseimatest väärtustest. Nt asutuses töötavad inimesed, kelle vanus on 21, 23, 30, 35 ja 70. Funktsioon =AVERAGE(21;23;30;35;70) annaks vastuseks 35,8 eluaastat, funktsioon =MEDIAN(21;23;30;35;70) aga 30 eluaastat s.t pooled töötajad on nooremad kui 30 eluaastat ja pooled on vanemad kui 30 eluaastat.
• MODE() on abiks siis, kui soovime teada, milline väärtus esineb valitud vahemikus kõige enam. Näiteks on veerus D ridadel 1-20 arvud 3 ,5, 7, 2, 6, 5, 7, 8, 2, 1, 4, 5, 7, 4, 8, 9, 4, 2, 4, 6. Funktsioon näeb välja =MODE(D1:D20) ja annab vastuseks 4.
• COUNT() loeb kokku kõik lahtrid, kus on kirjas arv. Nt =COUNT(A1:A7).
• COUNTA() loeb kokku kõik täidetud lahtrid, olenemata sellest, kas seal on kirjas numbrid või tähed. Nt =COUNTA(A1:A7).
• COUNTBLANK() loeb kokku kõik tühjad lahtrid. Nt =COUNTBLANK(A1:A7).
• COUNTIF() loeb aga kokku vaid lahtrid, kus on mingi kindel väärtus. Nt kui ma soovin teada, kui paljudes veeru A ridade 1-100 lahtrites on kirjas sõna mees, siis funktsioon näeb välja järgmine: =COUNTIF(A1:A100;“mees“). Kui mind aga huvitab, kui palju on veeru E ridade 1-100 lahtreid, mille väärtus on väiksem kui 18, siis kirjutan =COUNTIF(E1:E100;“<18”).

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.
• 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.

Dollarimärk – abimees, mis fikseerib Exceli valemis lahtri

Lihtsamate valemite loomisega olen seni Excelis kenasti hakkama saanud, alustades võrdusmärgiga ja sisestades tehte, mida soovin. Mida koolitusel juurde õppisin, oli see, et kui ma soovin näiteks, et valemis oleva lahtri K3 sisu jääks muutumatuks s.t ei valemit täitepidemest sikutades ehk siis kopeerides K3 sisu ei muutuks L3-ks, M3-ks jne, vaid jääks ikka K3-ks, siis tulevad appi dollari ($) märgid. Selleks tuleb dollarimärki kasutada nii rea- kui ka veerutähise ees – $K$3 – nii fikseerime kopeerides valemis lahtri K3. Siin on abimeheks F4, mis võimaldab dollarimärgid kiiresti valemisse saada, ilma et Sa peaksid ise kursori sättima veerutähise ette ja vajutama dollarimärgile ning seejärel reatähise ette ja taaskord vajutama dollarimärgile.

Vaata lisaks: Exceli valemid ja funktsioonid: õppige põhinäidete abil