Excel 2010 – Matemaatilised ja trigonomeetrilised funktsioonid (HARJUTUS 10)

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 enimakasutatavaid. Peatüki lõpust leiad sellesse gruppi kuuluvate funktsioonide nimekirja.

Excel 2010 on siia gruppi lisanud ka kolm uut funktsiooni:

  • AGGREGATE
  • CEILING.PRECISE
  • FLOOR.PRECISE

Arvu ümardamine

Excel hannab hulgi erinevaid ümardamise funktsioone, millega töötada. Kuigi põhimõte on neil üks – ümaradamine – 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 ümarada. 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:

372

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”)

373

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)

374.jpg

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

375.jpg

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.

376.jpg

Siinkohal tulebki appi AGGREGATE() funktsioon. Funktsiooni esimese parameetrina tuleb kirja panna millist funktsiooni kavatsen kasutada – hetkel SUM(), mis tähistatakse nr 9’ga.

377.jpg

Seejärel küsitakse, mida soovin soovin ignoreerida – hetkel soovin ignoreerida peidetud ridu.

378.jpg

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)

380.jpg

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.

381.jpg

Kuna meil on teada valem 382.jpg, siis sellest saame tuletada järgmise valemi 383.jpg. Excelisse lisamisel viime nurga radiaanidesse.

=5*TAN(RADIANS(32)

Pärast Enter vajutust, peaks vastus olema 3,12cm

Matemaatilised ja trigonomeetrilised funktsioonid

FunktsioonKirjeldus
ABSAnnab vastuseks arvu absoluutväärtuse.
ACOSAnnab vastuseks arvu arkuskoosinuse.
ACOSHAnnab vastuseks arvu arkushüperboolse koosinuse.
ASINAnnab vastuseks arvu arkussiinuse.
ASINHAnnab vastuseks arvu arkushüperboolse siinuse.
ATANAnnab vastuseks arvu arkustangensi.
ATAN2Annab vastuseks arkustangensi x- ja y-koordinaatide alusel.
ATANHAnnab vastuseks arvu arkushüperboolse tangensi.
CEILINGÜmardab arvu lähima täisarvuni või ümardusaluse lähima kordseni.
COMBINAnnab vastuseks antud arvu objektide kombinatsioonide arvu.
COSAnnab vastuseks arvu koosinuse.
COSHArvutab arvu hüperboolse koosinuse.
DEGREESTeisendab radiaanid kraadideks.
EVENÜmardab arvu ülespoole lähima paaristäisarvuni.
EXPAnnab vastuseks e antud astmes.
FACTAnnab vastuseks arvu faktoriaali.
FACTDOUBLEAnnab vastuseks arvu topeltfaktoriaali.
FLOORÜmardab arvu allapoole, nulli suunas.
GCDAnnab vastuseks suurima ühisjagaja.
INTÜmardab arvu allapoole lähima täisarvuni.
LCMAnnab vastuseks vähima ühiskordse.
LNAnnab vastuseks arvu naturaallogaritmi.
LOGAnnab vastuseks arvu logaritmi määratud alusel.
LOG10Annab vastuseks arvu kümnendlogaritmi.
MDETERMAnnab vastuseks massiivi maatriksi determinandi.
MINVERSEAnnab vastuseks massiivi pöördmaatriksi.
MMULTAnnab vastuseks kahe massiivi maatrikskorrutise.
MODAnnab vastuseks jagatise jäägi.
MROUNDAnnab vastuseks ümardusaluse lähima kordseni ümardatud arvu.
MULTINOMIALAnnab vastuseks arvuhulga multinoomi.
ODDÜmardab arvu ülespoole lähima paaritu täisarvuni.
PIAnnab vastuseks pii (π) väärtuse.
POWERAnnab vastuseks astendatud arvu.
PRODUCTKorrutab antud argumente.
QUOTIENTAnnab vastuseks jagatise täisarvulise osa.
RADIANSTeisendab kraadid radiaanideks.
RANDAnnab vastuseks juhusliku arvu vahemikus 0 kuni 1.
RANDBETWEENAnnab vastuseks juhusliku arvu teie määratud arvude vahemikus.
ROMANTeisendab 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.
SERIESSUMAnnab vastuseks valemil põhineva astmerea summa.
SIGNAnnab vastuseks arvu märgi.
SINAnnab vastuseks antud nurga siinuse.
SINHAnnab vastuseks arvu hüperboolse siinuse.
SQRTAnnab vastuseks arvu ruutjuure.
SQRTPIAnnab vastuseks ruutjuure korrutisest (arv * π).
SUBTOTALAnnab vastuseks loendi või andmebaasi vahekokkuvõtte.
SUMLiidab argumendid.
SUMIFLiidab antud kriteeriumidega määratud lahtrid.
SUMIFSLisab lahtrid mitmele kriteeriumile vastavasse vahemikku.
SUMPRODUCTAnnab vastuseks vastavate massiivikompomentide korrutiste summa.
SUMSQAnnab vastuseks argumentide ruutude summa.
SUMX2MY2Annab vastuseks kahe massiivi vastavate väärtuste ruutude vahede summa.
SUMX2PY2Annab vastuseks kahe massiivi vastavate väärtuste ruutude summade summa.
SUMXMY2Annab vastuseks kahe massiivi vastavate väärtuste vahede ruutude summa.
TANAnnab vastuseks arvu tangensi.
TANHAnnab vastuseks arvu hüperboolse tangensi.
TRUNCKä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 10

Lae alla fail harjutus10.xlsx

428.jpg

Viimased postitused

Meist

metshein.com on pakkunud juba üle kümne aasta tasuta eestikeelseid infotehnoloogiaga seotud kursusi. Sama kurssi püütakse järgida ka edaspidi. Eesmärk muuta arvutiõpe võimalikult lihtsaks!

metshein.com: parim eestikeelne koolitusportaal

Autorist ja kontakt: kliki siia

Kontrolli tunnistust

Tööribale