Microsoft Excel 2016 – Matemaatilised funktsioonid (HARJUTUS 9)

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.

Harjutus 9

Allalaaditav PDF