Olen Excelist juba kirjutanud kahes postituses Mida tasuta Exceli koolitus mulle õpetas ja Exceli kasutamine: filtreerimine, sorteerimine, vormistamine. Seekord on vaatluse alla Exceli valemid ning nende erinevus funktsioonidest. Lisaks toon hulganisti näited enimkasutatavatest matemaatilistest ja statistilistest funktsioonidest.
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.
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.
Exceli valemid ja funktsioonid – mis on neil vahet?
Nagu ma olen aru saanud, on Exceli valemid pigem lihtsakoelised – liidan, lahutan, korrutan ja jagan omavahel kahte arvu. Funktsioon seevastu on loodud keerulisemate tehete tegemiseks. Samas saab funktsiooni kasutada samuti lihtsate tehete tegemiseks. Funktsioon ei ole midagi muud kui 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.
Funktsiooni struktuur on toodud kõrvaloleval joonisel. Funktsioon algab alati võrdusmärgiga, millele järgneb funktsiooni nimi ja sulud. Sulgude sees on argumendid ehk näiteks arvud, millega tehteid tehakse, aga ka mingi teksti või viide lahtrile jms. Samuti võivad nendeks olla valemid ja teised funktsioonid. Argumendid eraldatakse üksteisest semikooloniga (;). Funktsiooni sisestamisel tuleb Excel Sulle appi ja kuvab vihje, mida ja millises järjekorras funktsiooni kirjutada. Kui vihjes on mõni element nurgeliste sulgude vahel […], siis selle lisamine on vabatahtlik ehk siis seda elementi ei pea lisama.
Funktsioone on väga palju. Mõned näited..
Matemaatilised funktsioonid
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.
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.
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”).
Kokkuvõtteks
Selleks korraks siinkohal lõpetan. Kui Exceli valemid said antud postituses käsitletud, siis Exceli funktsioonidega jätkan postituses Enimkasutatud Exceli funktsioonid – jätk…. Nimel võtan seal vaatluse alla ülejäänud enimkasutatud Exceli funktsioonid, milleks on loogika-, teksti ning kuupäeva- ja ajafunktsioonid.
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 siit, siis palun jaga postitust oma sõpradega!