Alustame nüüd Exceli põhiliste töövahenditega – funktsioonidega. Võtame esimesena sihikule Matemaatilised ja trigonomeetrilised funktsioonid. Kuigi kogu Excel on suur matemaatika, siis siin grupis vaatame kuidas ümardada, summeerida, leida absoluutväärtusi, võtta ruutjuurt jne. Kõiki funktsioone ei jõua läbi vaadata, kuid vaatame neist enimkasutatavaid. Peatüki lõpust leiad sellesse gruppi kuuluvate funktsioonide nimekirja.
Excel 2016 on siia gruppi lisanud ka kolm uut funktsiooni:
- AGGREGATE
- CEILING.PRECISE
- FLOOR.PRECISE
Arvu ümardamine
Excel annab hulgi erinevaid ümardamise funktsioone, millega töötada. Kuigi põhimõte on neil üks – ümardamine – siis igaüks võib sõltuvalt arvust käituda erinevalt.
ROUND(), ROUNDUP(), ROUNDDOWN()
Kõige sagedamini kasutatav ümardamisfunktsioon on ROUND(). See funktsioon nõuab kahte argumenti – arvu ennast ja komakohtade arvu. Arvud 1-4 ümardatakse allapoole ja 5-9 ülespoole
=ROUND(arv; kohtade_arv)
Näiteks soovime ümardada arvu 3,987 kaks kohta pärast koma, saame tulemuseks 3,99
=ROUND(3,987;2)
Kui lisate kohtade arvuna 0, siis ümardatakse lähima täisarvuni. Lisades kohtade arvu miinusena, siis ümardatakse lähima 10, 100, 1000 jne. Näiteks 123,987 kohtade arvuga -1, saame tulemuseks 120
=ROUND(123,987;-1)
Sarnaselt ROUND() funktsioonile töötavad ka ROUNDUP() ja ROUNDDOWN(). Nende kahe erinevus on see ROUNDUP() ümaradab alati ülespoole ja ROUNDDOWN() alati allapoole.
=ROUNDUP(3,987;2) //tulemuseks 3,99
=ROUNDDOWN(3,987;2) //tulemuseks 3,98
MROUND(), CEILING(), FLOOR()
MROUND() funktsioon ümardab arvu etteantud täpsusega. Funktsiooni süntaks sarnaneb eelmisele
=MROUND(arv; kordne)
Näiteks võtame arvu 123 ja ümardame lähima kordseni 5 – tulemuseks 125
=MROUND(123;5)
Kasuta antud funktsiooni näiteks rahasummade ümardamiseks viiesendise täpsesuga.
=MROUND(123,942;0,05) //tulemuseks 123,95
CEILING() ja FLOOR() töötavad nagu MROUND(), kusjuures CEILING() ümardab ülespoole ja FLOOR() allapoole.
=CEILING(123,942;0,05) //tulemuseks 123,95
=FLOOR(123,942;0,05) //tulemuseks 123,90
Oluline on tähele panna, et kordse lisamisel on arv sama märgiline. Kui üks on positiivne ja teine negatiivne, siis saame #NUM! veateate. Excel 2010 võimaldab meil selleks kasutada CEILING.PRECISE() ja FLOOR.PRECISE() funktsioone.
=CEILING.PRECISE(123,942;-0,05) //tulemuseks 123,95
=FLOOR.PRECISE(123,942;-0,05) //tulemuseks 123,90
INT(), TRUNC()
INT() on funktsioon, mis tegelikult ei ümarda. See eemaldab arvu murdosa ning kuvab ainult selle täisosa.
=INT(12,999) //tulemuseks 12
Sarnaselt INT() funktsioonile töötab TRUNC() funktsioon, kuid see võimaldab määrata ka komakohtade arvu, mida kasutaja soovib alles jätta.
=TRUNC(12,999;1) //tulemuseks 12,9
Kasuta seda näiteks pii väärtuse lihtsustamiseks.
=TRUNC(PI();2) //tulemuseks 3,14
EVEN(), ODD()
EVEN() on funktsioon, mis ümardab positiivse aru ülespoole ja negatiivse allapoole lähima paaris täisarvuni.
=EVEN(12,654) //tulemuseks 14
ODD() seevastu ümardab lähima paaritu täisarvuni.
=ODD(12,654) //tulemuseks 13
Arvutamine arvu massiividega
Suurte andmetega on Excelis hulk funktsioone, mis lihtsustab nendega arvutamist, teha kokkuvõtteid ja pidada statistikat. Neist osa me juba vaatasime aga kordame üle
SUM()
SUM() on funktsioon, mis summeerib kõik etteantud arvud või nende massiivid. Selle süntaks on väga lihtne:
=SUM(arvud_või_lahtrivahemik)
Näiteks vahemikus A1 kuni C10 olevate arvude kokkuliitmiseks kasutame funktsiooni
=SUM(A1:C10)
Kui andmed asuvad vahemikus A1:A10 ja C1:C10, siis SUM() funktsioon lubab kokku liita ka mitut vahemikku
=SUM(A1:A10;C1:C10)
Vahemiku võib sisestada nö “käsitsi” või hiirega lohistades. Mitme vahemiku määramiseks hoia all Ctrl-klahvi.
PRODUCT()
PRODUCT() on funktsioon, mis võimaldab massiivi arve omavahel korrutada. Selle süntaks sarnaneb SUM() funktsioonile.
=PRODUCT(arvud_või_lahtrivahemik)
Näliteks vahemikus B1:B10 olevate arvude korrutamiseks kirjutan järgmise funktsiooni.
=PRODUCT(B1:B10)
SUMIF()
Järgmiseks tahan tutvustada summeerimise funktsiooni, kus arvud liidetakse siis kokku, kui need vastavad mõnele tingimusele. Jagaksin asjade selgitamise kaheks. Esimesel juhul kasutaksin süntaksit:
=SUMIF(vahemik;kriteerium)
See tähendab, et kui antud vahemikus vastavad arvud sinu poolt seatud tingimusele, siis need liidetakse. Vaatame järgmist pilti:
Liidame kokku arvud, mis on võrdsed 400’ga – siis kirjutame järgmise funktsiooni
=SUMIF(B2:B12;400)
Kui proovida hoopis kokku liita kõik arvud, mis on üle 1000, sel juhul tuleks “>1000″ lisada jutumärkidesse.
=SUMIF(B2:B12;”>1000″)
SUMIF() funktsiooni on võimalik edasi arendada, kasutades järgmist süntaksit:
=SUMIF(otsitav_vahemik;kriteerium;tulemus)
See tähendab, et esimesest vahemikust otsitakse vastavalt tingimusele mõnda kirjet, ja kui leitakse, siis antakse tulemuse vahemikust selle väärtus. Keeruline? Vaatame sama näidet. Meil on tabelis müügimehed ja nende tehtud tehingud. Et teada saada, kui palju müüs Jüri kokku, siis kasutame järgmist funktsiooni.
=SUMIF(A2:A12;”Jüri”;B2:B12)
NB! Tekstid lisatakse jutumärkide vahele!
Aritmeetika
Nüüd näitan hunniku matemaatikast tuntud aritmeetikatehteid, mis on Excelis kiirelt funktsioonidega teostatavad
FACT()
Leiab arvust faktoriaali. Näiteks 3! = 1*2*3 = 6
=FACT(3)
POWER()
POWER() funktsioon tõstab soovitud arvu astmesse. Näiteks 4^3=64
=POWER(4;3)
Jah, nagu näites kirjas saab selle esitada ^ – märgiga, mille saab Ctrl+Ä abil
SQRT()
SQRT() tagastab antud ruutjuure. Näiteks ruutjuur 9, saame vastuseks 3
=SQRT(9)
QUOTIENT()
QUOTIENT() eraldab jagatisest täisarvulise osa. Näiteks 100/6 = 16,6667, kuid kui kasutada seda funktsiooni siis saame tulemuseks 16.
=QUOTIENT(100;6)
MOD()
MOD() funktsioon töötab eelmisele funktsioonile vastupidiselt. Nimelt tagastab jagatisest jäärgi. Näiteks 100/6 jääk on 4.
=MOD(100;6)
Kasuta seda funktsiooni näiteks sellisel juhul, kui meil on teada, et tooteid ühte karpi mahub 6 ja meil on 100 toodet. Siis kasutades seda funktsiooni, saame teada, et 4 toodet jääb üle.
MOD() funktsiooni saab kasutada ka paaris ja paaritu arvu leidmisel. St. et kui jagada arv 2’ga ja jääk on null, siis on tegemist paarisarvuga ja kui jääl on 1, siis paarituarvuga.
=IF(MOD(100;2)=0;”paaris”;”paaritu”)
PI()
PI() funktsioon genereerib meile pii väärtuse 3,14159265358979
=PI()
RAND() ja RANDBETWEEN()
Need funktsioonid otseselt meile midagi ei arvuta, kuid on kasulikud suvaliste arvude väljamõtlemisel. RAND() funktsioon genereerib meile murdarvu, mis jääb 0 ja ühe vahele. Näiteks: 0,551236505755352
=RAND()
Seevastu RANDBETWEEN() genereerib suvalise täisarvu, teie poolt antud vahemikule. Näiteks, et saada arve 10-500, pane kirja järgmine funktsioon.
=RANDBETWEEN(10;500)
Genereeritud arvud ei ole staatilised – vaid muutuvad tegutsedes. Võite ise testida kui klikite nuppu F9 (värskenda). Et saada genereeritud arvud staatiliseks tuleb need kopeerida ja kleepida nende väärtused.
ROMAN()
Kui juhtub, et millegipärast on vaja meie araabianunber teisendada roomanumbriks, siis võta kasutusele just see funktsioon. Näiteks soovin teada kuidas on aasta 2011 roomanumbrites, siis antud funktsioon väljastab mulle XXMI
=ROMAN(2011)
AGGREGATE()
Lisan selle uue Excel 2016 funktsiooni AGGREGATE() eraldi pealkirja alla. Kui trükite funktsiooni nime Excelisse, siis esialgu võib tekkinud vihje olla arusaamatu. Seega proovn selle teha paari näitega selgeks. Võtame näiteks tabeli töötajate palkadega ning lõpus on summad kokku liidetud =SUM() funktsiooniga.
Nüüd soovin ära peita Jüride palgad, et teised kenasti välja printida. Kuigi peidsin Jüride read kenasti ära, jäi lõppsumma ikka samaks, mis annab väljaprinditud lehel valet infot.
Siinkohal tulebki appi AGGREGATE() funktsioon. Funktsiooni esimese parameetrina tuleb kirja panna millist funktsiooni kavatsen kasutada – hetkel SUM(), mis tähistatakse nr 9’ga.
Seejärel küsitakse, mida soovin soovin ignoreerida – hetkel soovin ignoreerida peidetud ridu.
Viimasena märgista arvude massiiv, mida soovid kokku liita. Nüüd kui ridu peita ja tuua peidust välja muudetakse ka kogusummat – nr 5
=AGGREGATE(9;5;B2:B12)
Sama lugu on näiteks veateadetega. Kui sul on sattunud arvutusse viga, siis AGGREGATE() saab ignoreerida ka seda. Sellisel juhul valin suvandiks nr 7
=AGGREGATE(9;7;B2:B12)
Trigonomeetria
Nagu kõik juba teavad, siis trigonomeetria tegeleb kolmnurga külgede ja nurkade vaheliste seoste arvutamisega. Excelis on olemas kõikvõimalikud siinus, koosinus, tangensi jt funktsioonid. Vaatame mõnda praktilist näidet.
Näide 1
Oletame, et meil on täisnurkne kolmnurk, mille alumine külg on 5cm ja nurk q = 32°. Leida oleks vaja kolmnurga kõrgus b.
Kuna meil on teada valem , siis sellest saame tuletada järgmise valemi . Excelisse lisamisel viime nurga radiaanidesse.
=5*TAN(RADIANS(32)
Pärast Enter vajutust, peaks vastus olema 3,12cm
Matemaatilised ja trigonomeetrilised funktsioonid
| Funktsioon | Kirjeldus |
| ABS | Annab vastuseks arvu absoluutväärtuse. |
| ACOS | Annab vastuseks arvu arkuskoosinuse. |
| ACOSH | Annab vastuseks arvu arkushüperboolse koosinuse. |
| ASIN | Annab vastuseks arvu arkussiinuse. |
| ASINH | Annab vastuseks arvu arkushüperboolse siinuse. |
| ATAN | Annab vastuseks arvu arkustangensi. |
| ATAN2 | Annab vastuseks arkustangensi x- ja y-koordinaatide alusel. |
| ATANH | Annab vastuseks arvu arkushüperboolse tangensi. |
| CEILING | Ümardab arvu lähima täisarvuni või ümardusaluse lähima kordseni. |
| COMBIN | Annab vastuseks antud arvu objektide kombinatsioonide arvu. |
| COS | Annab vastuseks arvu koosinuse. |
| COSH | Arvutab arvu hüperboolse koosinuse. |
| DEGREES | Teisendab radiaanid kraadideks. |
| EVEN | Ümardab arvu ülespoole lähima paaristäisarvuni. |
| EXP | Annab vastuseks e antud astmes. |
| FACT | Annab vastuseks arvu faktoriaali. |
| FACTDOUBLE | Annab vastuseks arvu topeltfaktoriaali. |
| FLOOR | Ümardab arvu allapoole, nulli suunas. |
| GCD | Annab vastuseks suurima ühisjagaja. |
| INT | Ümardab arvu allapoole lähima täisarvuni. |
| LCM | Annab vastuseks vähima ühiskordse. |
| LN | Annab vastuseks arvu naturaallogaritmi. |
| LOG | Annab vastuseks arvu logaritmi määratud alusel. |
| LOG10 | Annab vastuseks arvu kümnendlogaritmi. |
| MDETERM | Annab vastuseks massiivi maatriksi determinandi. |
| MINVERSE | Annab vastuseks massiivi pöördmaatriksi. |
| MMULT | Annab vastuseks kahe massiivi maatrikskorrutise. |
| MOD | Annab vastuseks jagatise jäägi. |
| MROUND | Annab vastuseks ümardusaluse lähima kordseni ümardatud arvu. |
| MULTINOMIAL | Annab vastuseks arvuhulga multinoomi. |
| ODD | Ümardab arvu ülespoole lähima paaritu täisarvuni. |
| PI | Annab vastuseks pii (π) väärtuse. |
| POWER | Annab vastuseks astendatud arvu. |
| PRODUCT | Korrutab antud argumente. |
| QUOTIENT | Annab vastuseks jagatise täisarvulise osa. |
| RADIANS | Teisendab kraadid radiaanideks. |
| RAND | Annab vastuseks juhusliku arvu vahemikus 0 kuni 1. |
| RANDBETWEEN | Annab vastuseks juhusliku arvu teie määratud arvude vahemikus. |
| ROMAN | Teisendab araabia numbri tekstina esitatud rooma numbriks. |
| ROUND | Ümardab arvu määratud kümnendkohtade arvuni. |
| ROUNDDOWN | Ümardab arvu allapoole, nulli suunas. |
| ROUNDUP | Ümardab arvu ülespoole, nullist eemale. |
| SERIESSUM | Annab vastuseks valemil põhineva astmerea summa. |
| SIGN | Annab vastuseks arvu märgi. |
| SIN | Annab vastuseks antud nurga siinuse. |
| SINH | Annab vastuseks arvu hüperboolse siinuse. |
| SQRT | Annab vastuseks arvu ruutjuure. |
| SQRTPI | Annab vastuseks ruutjuure korrutisest (arv * π). |
| SUBTOTAL | Annab vastuseks loendi või andmebaasi vahekokkuvõtte. |
| SUM | Liidab argumendid. |
| SUMIF | Liidab antud kriteeriumidega määratud lahtrid. |
| SUMIFS | Lisab lahtrid mitmele kriteeriumile vastavasse vahemikku. |
| SUMPRODUCT | Annab vastuseks vastavate massiivikompomentide korrutiste summa. |
| SUMSQ | Annab vastuseks argumentide ruutude summa. |
| SUMX2MY2 | Annab vastuseks kahe massiivi vastavate väärtuste ruutude vahede summa. |
| SUMX2PY2 | Annab vastuseks kahe massiivi vastavate väärtuste ruutude summade summa. |
| SUMXMY2 | Annab vastuseks kahe massiivi vastavate väärtuste vahede ruutude summa. |
| TAN | Annab vastuseks arvu tangensi. |
| TANH | Annab vastuseks arvu hüperboolse tangensi. |
| TRUNC | Kärbib arvu murdosa. |
| AGGREGATE (uus) | Annab vastuseks loendis või andmebaasis kokkuvõtte. |
| CEILING.PRECISE (uus) | Ümardab arvu lähima täisarvuni või ümardusaluse lähima kordseni. Arv ümardatakse ülespoole sõltumata arvu märgist. |
| FLOOR.PRECISE (uus) | Ümardab arvu allapoole lähima täisarvuni või ümardusaluse lähima kordseni. Arv ümardatakse allapoole sõltumata arvu märgist. |