Informaatika I tunnitöö "Valemid" (0)

Andmed ja valemid  Excel 'is
Andmed ja valemid Excel'is
Andmete tüübid  Excelis
Valemid ja  avaldised
Funktsioonid
Arvandmed , -avaldised ja -funktsioonid
Aadressite ja nimede kasutamine  valemites . Harjutus " Kolmnurk "
Harjutus " Täisnurkne  kolmnurk "
Arvavaldised  -  tehete   prioriteedid , funktsioonid
Loogikaandmed, -avaldised ja funktsioonid
Võrdlused ja  loogikatehted
Võrdlused ja loogikatehted. Harjutused
IF-funktsioon
Palk & Kauba hind
Funktsioonide tabel
Minirakendus "Detail" - ülesande püstitus
"Detail" - kasutajaliides
"Detail" - materjalid
"Detail" - värvid
Ajaandmed, -avaldised ja -funktsioonid
Tekstandmed, -avaldised ja funktsioonid
Lisad
Nimede määramine ja kasutamine
Valideerimine
Matemaatikafunktsioonid
Tekstifunktsioonid
Loogikafunktsioonid
Ajafunktsioonid
Otsimine. Funktsioon VLOOKUP
Valemiredaktor MS Equation 3.0
Aadressite ja nimede kasutamine valemites. Harjutus "Kolmnurk"
Arvud
Tekstid
Ajaväärtused  Tõeväärtused
Märkandmete tüübid
Excelis eristatakse järgmisi andmetüüpe:
 - arvud 
13
-745.625
3.03E+14
 - tekstid
Peeter
Kask
P. Kask
 - ajaväärtused
01/12/16 22:10
12/1/ 2016
22:10:16
 - tõeväärtused
1
0
0
Iga tüübi jaoks on määratletud lubatavad  tehted  ja funktsioonid, väärtuste 
esitusviisid (vormingud) sisestamisel ja kuvamisel ning väärtuste diapasoon. 
Lahtris  võib olla ainult üks väärtus. See sisetatakse otse  lahtrisse  või leitakse 
valemi poolt. Väärtused võivad esineda konstanditena ka valemites. Väärtuste 
esituses valemites on teatud iseärasused, võrreldes lahtriväärtustega.
Excel määrab sisestatava väärtuse tüübi esitusviisi järgi. Arvude ja kuupäevade 
esitusviis (eraldajad) sõltub keeleseadetest, mis on määratud Window'sis
Keelesedetest sõltub ka  eraldajate  kasutamine loeteludes. Võivad esineda näiteks 
fumktsioonide argumentides: SUM(K13; L5:M12; N21) 
Eestis, on tüüpilised Eesti/Vene ja USA/Inglise keeleseaded (vt. tabelit)
Eraldajad sõltuvalt keeleseadetest
Eesti/Vene
USA/Inglise
Liik
eraldaja
näide
eraldaja
näide
Arvud
koma
13,05
punkt
13.05
Kuupäevad
punkt
13.05.2002
kaldkriips
05/13/01
Kellaajad
koolon
14:28:15
koolon
14:28:15
Loetelud
semikoolon
a; b; c
koma
a, b, c
NB! Keeleseadeid saab muuta   Windows 'i aknas  Control  Panel /  Region  and   Language
veeb
Control Panel / Region and   Language
Funktsioonid
Arvavaldised
Tekstavaldised
Loogikaavaldised Ajaavaldised
veeb
Valemid ja avaldised
Valem on korraldus Excelile leida (tuletada) mingi väärtus ja 
salvestada  see antud lahtris. Valem esitatakse kujul:
                =  avaldis
Võrdusmärk  ( = ) on Excelile tunnuseks, et tegemist on valemiga. 
Suvalist   sisendit , mis algab võrdusmärgiga, käsitleb Excel valemina. 
 avaldis - määrab, mil ised tehted peab täitma andmetega vajaliku 
väärtuse leidmiseks. Üldjuhul ta koosneb: operandidest, 
tehtemärgidest ja ümarsulgudest.
Operandideks võivad olla: 
   konstandid: 12      25,73      "N"    " Kasemets "    "01.01.2000"
   viited lahtritele ja lahtriplokkidele (muutujad): 
     -  aadressid :  B5,  H13, C5:H28,  $B$5,  H$13, ...,  Sheet2!B5, ...
     - nimed:   a,   x,    x_1,   c_,   pikkus,   palk, ... , Sheet2!palk, ...
   funktsiooniviidad ehk t funktsioonid: SIN(B3),   SQRT (a^2+b^2), 
   SUM(C3:C103), MAX(palk),  LEFT (nimi;1), VLOOKUP(värv; Värvid;3; 
0) 
Tehted ja tehtesümbolid, tehete prioriteedid
  1.   %       protsent:   18% = 0,18       10%*130 = 13
  2.   ^         astendamine  (Alt+94): (x + 2)^2      (x+2)^(1/3) 
  3.   *  ,  /    korrutamine ,  jagamine: a * b    a / b     2 * (a + b ) / d
  4.   +  ,  -  li tmine, lahutamine:  a + b   a - b  
  5.   &        sidurdamine (tekstide ühendamine): enimi&" "&pnimi
  6.   = ,  , =  võrdlustehted: A3>C4  palk0   
NB! Võrdse prioriteediga tehteid täidetakse järjest vasakult 
paremale, tehete täitmise järjekorra  määramiseks  võib kasutada 
ümarsulge.
     =2,67*(13,7- 2,68) / 14,1      =B3 * B4 / B5      =B3 / B4 * B5
     =(a + b) /( x + y)       =(a + b) / (1 + x / (a + d))     
 
1
3 a  b  (a  b) 3  (a  b)  1
( / )
3
(a  b)1 a  b
(a  b) 1/ 3 

3
3
Ekraanivisioon "Valemid ja avaldised"
Exceli  valemite olemus , valemite 
sisestamise ja  töötlemise 
põhimõtted, vead ja nende 
parandamine, avaldised ja nende  
elemendid. Tehted ja tehete  
prioriteedid, konstantide esitamine 
avaldistes. 
Klip sisaldab ka  viiteid  klipidele 
aadressite  
ja nimede kasutamise kohta
Funktsioonid
Arvavaldised
Tekstavaldised
Loogikaavaldised
Ajaavaldised
veeb
Sisefunktsioonid
Funktsioonid esitatakse valemites funktsioonviida abil:
       fun_nimi(argument; argument;…)
fun_nimi - funktsiooni nimi: SIN, SQRT, LOG, …
argument - väärtus, mil e jaoks on vaja leida funktsiooni väärtus. 
Võib olla konstant, aadress, nimi või avaldis. 
NB! Argumendid peavad olema  sulgudes!!! Ka siis, kui on ainult 
üks.
NB! Eesti keeleseadete korral on argumentide eraldajaks semikoolon 
(;)
        Inglise keeleseadete korral - koma ( , ) 
Argumentide arv ja nende järjestus sõltub funktsioonist. Paljudel 
funktsioonidel on ainult üks argument.  Mõnedel funktsioonidel 
argumendid puuduvad, nendel peavad nime järel olema tühjad 
sulud. PI(), NOW(),  TODAY (), ...
Funktsioonide sisestamiseks võib kasutada mitmeid abivahendeid. 
   Valimine valemi sisestamisel ilmuvast funktsioonide ja nimede 
loetelust
   Vahekaardil Formulas (Valemid) funktsioonide teegist
   Dialoogiboks  Insert  Function (kuvatakse nupuga fx) võimaldab 
saada 
   kõikide funktsioonide  loetelu  ja kirjelduse. Seda saab kasutada ka 
   fu
x n
2 kt
 syi2oon
 id
S e
Q s
R ise
T(xstami
2  se
y l
. 2)  SQRT(x * x  y * y)
  
  
   x  a  SQR (T AB (S (xa)/(yb)) )
y  b
2
π  d
  x

sin
 PI() * d 2 / 4
 SI (
N PI() * x/4)
3
4
sin x  cos 2x  tan x2 
2
cos x  SIN(X)  COS(2 * x)  TAN(x 2)  COS(x)  2
log( 4
x  3sin 4 2x)  LO (
G x  4  3 * SI (
N 2 * x) 4)
2
x  2
a
ln
 L (
N (x  2  a 2)/(2 * LO (
G b 4)))
4
2log b
2
e x  2
e x  (EX (P2* x)EX (P 2
 * x))/(2 *L (
N x * x  a))
2ln( 2
x  a)
  =SUM(B3; C5:H35; K13)   =MAX(B4; C4; D4)
  ="01.01.2012" - NOW()       TODAY() - algus
 =LEFT( eesnimi ; 1) & ". " &  perenimi )       =MID(kood; 3; 2)
 =IF(x  75
0.75
e või E
Eksponendi eraldaja: 2,1e6 => 2,1·106  4e-8 => 2,38·10-8        
2.10E+06
( )
Negatiivse arvu alternatiivesitus: (562,75) => -562,75
-5376
=
Valemi  erijuht : =562,75 => 562,75     =-562,75  => -562,75
562.75
€
Rahavormingu tunnus:  562,75€ 
    4,940.00 € 
%
Protsenditehe:   18% => 0,18. Eelnev väärtus jagatakse 100-ga 
13%
tühik
Kolmikute eraldaja arvu täisosas:  3 456 562,75
3,456,562.72
/
Tavaline murd: 562 3/4 => 562,75
562 3/4
Arvavaldiste operandideks võivad ol a arvkonstandid, viited lahtritele ja lahtriplokidele (aadressid ja 
nimed), mis sisaldavad arve, ja funktsioonid, mille tagastatavateks väärtusteks on arvud.                         
       
Arvkonstandid valemites võivad  sisaldada  ainult  numbreid , arvu märki, koma ja eksponenti
 =2,67*(13,7-2,68)/14,1    =-3,64+45*A5    =a*b*h/1e6
Aritmeetikatehted  ja nende prioriteedid
Prioriteet
Tehe
Kommentaar
NB! 
1
%
protsent:   18% = 0,18       10%*130 = 13
Võrdse prioriteediga tehteid täidetakse järjest vasakult 
2
^
astendamine (Alt+94): (x+2)^3      (x+2)^(1/3)paremale.
        Eelmise  tehte  tulem on sisendiks järgmisele tehtele
3
* ,  /
korrutamine,  jagamine: a*b    a/b     2*(a+b)/d
NB! 
Tehete täitmise järjekorda saab reguleerida ümarsulgude abil
NB! 
Võrdse prioriteediga tehteid täidetakse järjest vasakult 
paremale.
        Eelmise tehte tulem on sisendiks järgmisele tehtele
NB! 
Tehete täitmise järjekorda saab reguleerida ümarsulgude abil
4
 + , -
liitmine , lahutamine:  a+b   a-b  
Arvfunktsioonid
Funktsioonid, mil e tulemuseks on arvud. Si a kuuluvad eeskätt matemaatikafunktsioonid, 
kuid ka paljude teisre funktsioonide tulemiks on arvud (näiteks statistikafunktsioonid jm)
Vt. lehte
Matemaatikafunktsioonid
Arvude põhivormingud (esitusviisid). Täpsemalt vaata töövihiku
 V tormindamine.xls
Nimetus ja 
Tähendus, arvude esitusviis 
Näided
kood
Üldine. Kehtib, kui lahtrile pole määratud mingit teist vormingud. 
General
Väärtus kuvatakse sel isel kujul nagu ta sisestatakse või saadakse 
3562.725
valemist .    
Number
Püsikoma. Määrab  murdosa  pikkuse d. 
3562.73
0,00…
Arv 562,725: d=2 => 562,73;  d=5 => 562,72500;  d=0 => 563     
Scientific
 
Ujukoma- ehk  eksponent :    5,6275E+02 
3.5627E+03
0,00...E+0
0Currency
Raha.  54 562,40 €. Arvu järele lisatakse €, murdosas 2 kohta, 
 # ##0,00 
      3,562.73 € 
kolmikud  täisosas eraldatakse tühikutega.
kr
Percentag
Protsent:  18%. Väärtus kuvatakse korrutatuna 100-ga, lõppu 
e 
18%
lisatakse %. Säilitatav väärtus ei muutu.  
0,0...%
Text 
Tekst. Kuvamisel arvu käsitletakse tekstina. Kui  vorming  on määratud  3562,725
@
enne sisestamist, siis säilitakse kõik märgid. 
Custom  
Kasutaja.  Luuakse  olemasoleva  vormingu  alusel. Näiteks vorming 
37.58 cm
0,00” cm” kuvab arvu järel  sümbolid  cm.
NB! 
Võrdse prioriteediga tehteid täidetakse järjest vasakult 
paremale.
        Eelmise tehte tulem on sisendiks järgmisele tehtele
NB! 
Tehete täitmise järjekorda saab reguleerida ümarsulgude abil
NB! 
Võrdse prioriteediga tehteid täidetakse järjest vasakult 
paremale.
        Eelmise tehte tulem on sisendiks järgmisele tehtele
NB! 
Tehete täitmise järjekorda saab reguleerida ümarsulgude abil
14806209482703.xlsm lhk 17
Juhuslikud arvud. Funktsioon  RAND ()
Juhuslikke arve kasutatakse sageli andmete genereerimiseks rakenduste 
kontrol imisel ja testimisel, model eerimisel, loosimistel, mängude 
programmeerimisel jm. 
Näide 1: loosimine, arvude aritmeetiline keskmine.
VBA  protseduur  genereerib 10 juhuslikku arvu vahemikus mini... maxi  (n. Keno: 
1...64)
Exceli funktsioon  AVERAGE  leiab nende keskmise
mini
1
maxi
64
Loosi
keskmine
3
6
14
17
30
36
39
51
61
63
32
Näide 2. VBA programm genereerib juhusliku arvu vahemikus 1 kuni 100.
Kasutaja peab  arvama  sel e ära võimalikult väikese katsete arvuga.
Vale vastuse korral muudetakse Juku   asukohta  juhusliku väärtuse abil.
J
Arva  ära arv
Juhuslike arvude genereerimiseks kasutatakse spetsialseid programme. 
Exceli valemites saab luua juhuslikke arve funktsiooni RAND() abil.
Funktsioon genereerib juhusliku  reaalarvu  vahemikus 0 kuni 1, täpsemalt 0