Informaatika (1)

Overview

Tiitel
Sisukord
Ülesanne
Analüüs
Materjalid
Värvid
Det_Excel
Index
Korterid
mats
meie_mats
Ind_Mats
VLOOKUP
Det_VBA_Fun
Det_Fun_2
Oma_Ind_Mats
Det_VBA_Macro
Det_Makro_2
Piirkond
Otsi_Tab
Tootmine
Koond_Mat
Koond_Värv
SumIf

Sheet 1: Tiitel

Tallinna Tehnikaülikool

Informaatikainstituut
Tõõ Detail
Üliõpilane
Õppemärkmik
Õppejõud Vilipõld Õpperühm MASB-11
Palun täitke tühjad lahtrid

Sheet 2: Sisukord

Detail
Ülesande püstitus
Analüüs, skeem, valemid
Materjalid
Värvid
Detail. Exceli valemid
Funktsioon INDEX
Tabel Korterid
Funktsioon MATCH
VBA funktsioon Otsi_Nr
Funktsioonide INDEX ja MATCH kooskasutus
Funktsioon VLOOKUP
Detail. Kasutaja funktsioonid
Detail. VBA funktsioonid ruumala ja täispindala leidmiseks
VBA funktsioonid otsimiseks paralleelsetest vektoritest
Detail. Makro
Detail. VBA makro. Struktuur ja protseduurid
Detailide tootmine
Koondandmed materjalide koguste ja maksumuste kohta
Koondandmedvärvide koguste ja maksumuste kohta
Funktsioon SUMIF

Sheet 3: Ülesanne

Sheet 4: Analüüs

Rakendus "Detail". Analüüs. Geomeetria
Detaili skeem ja mõõdud ning andmed ja valemid geomeetriliste karakteristikute leidmiseks
Andmed detailide geomeetria kohta
Tulemid: V-ruumala (m^3), S-täispindala (m^2)
Algandmed : L, h, b, d (cm)
Vaheandmed: A, P
Insert/ Object /MS Equation 3.0

Sheet 5: Materjalid

Rakendus Detail.
Materjalid
Materjali margid ja hinnad
Mark Hind Kr/m3
Ma_18 320
Ma_02 250
Ma_04 320
Ma_03 350
Ma_06 500
Ma_10 430
Ma_09 440
Ma_07 450
Ma_08 480
Ma_12 532
Ma_05 520
Ma_11 540
Ma_13 350


M_mark M_hind

Sheet 6: Värvid

Rakendus Detail.
Värvid
Värvide margid, kulu ja hinnad
Mark Kulu L/m2 Hind Kr/L
Sv12 0.42 32.00
Sv02 0.30 47.20
Sv06 0.35 43.00
Sv08 0.30 61.40
Sv11 0.30 62.00
Sv03 0.35 48.20
Sv04 0.35 27.90
Sv09 0.35 48.90
Sv05 0.40 52.80
Sv10 0.40 39.50
Sv01 0.43 62.80
Sv07 0.45 52.40
Sv13 0.50 32.70




V_mark V_kulu V_hind

Sheet 7: Det_Excel

Detail. Exceli valemid


mark hind kogus maksumus
Materjal Ma_05 520 1.63 86.1
Värv Sv05 52.8 6.46 340.9
Muud kulud
Materjal ja värv kokku 427.1
1000
Maksumus kokku 1427.1
L 600
h 80
b 40
d 35
ruum 1.63
pind 16.14
A 2718.9436249191
P 259.9778714378

Sheet 8: Index

Funktsioon INDEX
Võimaldab viidata vektorite (rivid, tulbad) ja tabelite
elementidele (lahtritele) indeksite abil
Kaks põhivarianti:
INDEX ( vektor ; indeks) Vk = INDEX(V; k)

INDEX (tabel; riviindeks; tulbaindeks) Ti, j = INDEX(T; i; j)

vektor - rivi või tulp : ühemõõtmeline massiiv
indeks - elemendi (lahtri) järjenumber vektoris
piirkond - riskülikukujuline ala töölehel: kahemõõtmeline massiiv (tabel või maatriks ). Koosneb rividest ja tulpadest
rivi- ja tulbaindeks - rivi ja tulba järjenumber massiivi algusest.
Vektor V
k Vk
13 -27 65 89 -24
4 89
k 1 2 3 4 5
=INDEX(V; k)

Tabel (maatriks) A (4*3)
i
Kask 25 3400 1
Saar 21 5700 2 i j Ai, j
Tamm 33 5680 3 4 1 Paju
Paju 17 9800 4 1
=INDEX(A; i; j)
j 1 2 3
Leida korteri numbri järgi omanik, pindala ja inimeste arv
Andmed on lehel
Korterid
krt Omanik Inimesi Pindala
1 A. Kask 3 65.3 Variant 1. Tulpade nimed
1 A. Kask 3 65.3 Variant 2. Tabeli nimi
1 A. Kask 3 65.3 Variant 3. Oma funktsioon - Tabel
1 A. Kask 3 65.3 Variant 4. Oma funktsioon - Tulbad
Koostada oma funktsioon IND, mis vastab Exceli funktsioonile INDEX
Kask 25 3400 1
Saar 21 5700 2
Tamm 33 5680 3
Paju 17 9800 4
1 2 3
1 13
2 -27
3 65
4 89
5 -24
13 -27 65 89 -24
k 1 2 3 4 5

Sheet 9: Korterid

Korterid


Number K_omanik K_pind K_inimesi
1 A. Kask 65.3 3
2 P. Lepp 74.5 2
3 K. Mänd 65.3 5
4 L. Tamm 42.6 2
5 H. Kuusk 74.5 1
6 O. Palm 63.7 2
7 V. Tamm 45.6 3
8 S. Mets 82.4 5

Sheet 10: mats

Funktsioon MATCH
MATCH (otsitav; vektor; otsimisviis)




Leiab otsitava väärtuse järjenumbri antud vektoris
otsimisviis - ei ole kohustuslik. Võib olla 0 või 1. Kui puudub võetakse 1.
0 - kindla väärtuse otsimine - järjestus vektoris suvaline





1 - vahemiku otsimine - kasvamise järjekorras
Iseseisvalt harva. Sageli koos funktsiooniga INDEX.
Leida omaniku nime järgi korteri number, pindala ja inimeste arv
isik korter pind inimesi
H. Kuusk 5 74.5 1
Leida värvi margi järgi tema järjenumber, hind ja kulu
värv nr hind kulu
Sv04 7 #N/A 0.35

Sheet 11: meie_mats

Koostada VBA-funktsioon, mis leiab antud väärtuse asukoha (järjenumbri) vektoris
Realiseerida ainult kindla väärtuse otimine
Variant 1. For Each-lause
Sisestage lahtrisse liik väärtus ja vaadake kuidas töötavad
funktsioonid MATCH ja Otsi_Nr
k Liigid
liik nr
MATCH Otsi_Nr Otsi_Nr1
1 saar
haab 9
9 # VALUE !

2 kask
3 paju
k element
4 kuusk
9 haab
5 jalakas
6 vaher
7 tamm
8 mänd
9 haab
Variant 1. For-lause

Sheet 12: Ind_Mats

INDEX & MATCH
Otsimine paralleelsetes vektorites
INDEX(vek_2; MATCH(otsitav; vek_1; OV))
OV - otsimisviis - 0 või 1
MATCH leiab otsitava väärtusele vastava järjenumbri vek_1-s
INDEX tagastab leitud numbriga väärtuse vek_2-st
vrdl.
LOOKUP(otsitav; vek_1; vek_2)
NB! Ei võimalda määrata otsimisviisi
vektor_1 peab olema sorditud kasvamise järjekorras
Leida korteri omaniku järgi pindala ja inimeste arv
Omanik pind inimesi
L. Tamm 42.6
Leida värvi hind ja kulu marg järgi
värv hind kulu
Sv09
0.35

Sheet 13: VLOOKUP

Funktsioon VLOOKUP


VLOOKUP (otsitav; tabel; tulp; otsimisviis)





Otsimine vertikaalses tabelis
Otsib väärtust tabeli esimesest tulbast ja tagastab väärtuse antud tulbast
tulp - tabeli tulba number, kust võetakse tagastatav väärtus
otsimisviis - ei ole kohustuslik, vaikimisi - 1 või True





0 või False - kindla väärtuse otsimine, Kui ei leia - veateade #N/A
1 või True - vahemiku otsimine. Kui ei leia täpset, võtab lähima väiksema












Leida korteri omaniku järgi pindala ja inimeste arv
Omanik pind inimesi
L. Tamm
2
Leida värvi hind ja kulu tabelist Värvid
värv hind kulu
Sv09 48.9

Sheet 14: Det_VBA_Fun

Detail. Kalkulatsioonid . Kasutaja Funktsioonid


mark hind kogus maksumus
Materjal Ma_05 520 # VALUE ! #VALUE!
Värv Sv05 52.8 #VALUE! #VALUE!
Muud kulud
Materjal ja värv kokku #VALUE!
1000
Maksumus kokku #VALUE!
L 600
h 60
b 40
d 35
ruum #VALUE!
pind #VALUE!

Sheet 15: Det_Fun_2

Detail. VBA funktsioonid ruumala ja täispindala leidmiseks

Sheet 16: Oma_Ind_Mats

VBA funktsioonid otsimiseks paralleelsetest vektoritest
Koostada VBA-funktsioon kindla väärtuse otsimiseks paralleelsetes vektorites
Realiseerida kaks varianti
a) Otsi1: kasutab funktsiooni Otsi_Nr, otsitava järjenumbri leidmiseks
b) Otsi2: täidab ise kõik vajalikud tegevused
Omanik Pind Inimesi
värv hind kulu
K. Mänd #VALUE! #VALUE!
Sv05

Sheet 17: Det_VBA_Macro

Detail. Kalkulatsioonid. Kasutaja Funktsioonid


mark hind kogus maksumus
Materjal Ma_05 520 1.88 977.9
Värv Sv05 52.8 6.28 331.4
Muud kulud
Materjal ja värv kokku 1309.3
50%
Maksumus kokku 1964.0
L 600
h 80
b 40
d 20
ruum 1.83
pind 15.69
mastaap 0.1

Sheet 18: Det_Makro_2

Detail. VBA makro. Struktuur ja protseduurid

Sheet 19: Piirkond

Töölehe piirkonna (lahtriploki) sidumine muutujaga
Viitamine piirkonna lahtritele
Korterid
Number Omanik Pind Inimesi
1 A. Kask 65.3 3 1 2 P. Lepp 74.5 2 2 3 K. Mänd 65.3 5 3 4 L. Tamm 42.6 2 4 5 H. Kuusk 74.5 1 5 6 O. Palm 63.7 2 6 7 V. Tamm 45.6 3 7 8 S. Mets 82.4 5 8
9
1 2 3

Sheet 20: Otsi_Tab

VBA funktsioon otsimiseks tabelist
Mark Kulu L/m2 Hind Kr/L
Sv12 0.42 32.00
Sv02 0.30 47.20
värv hind kulu
Sv06 0.35 43.00
Sv05
Sv08 0.30 61.40
Sv11 0.30 62.00
Sv03 0.35 48.20
Sv04 0.35 27.90
Sv09 0.35 48.90
Sv05 0.40 52.80
Sv10 0.40 39.50
Sv01 0.43 62.80
Sv07 0.45 52.40
Sv13 0.50 32.70

Sheet 21: Tootmine

Detailide tootmine
kuupäev L h b d T_materjal T_värv arv M_kogus V_kogus M_maks V_maks Y_maks
03/01/06 900 90 60 20 Ma_08 Sv10 10 #VALUE!

Sheet 22: Koond_Mat

Materjali kogused ja maksumused markide lõikes
Materjali kogused ja maksumused markide lõikes
Funktsiooni SUMIF kasutamine
Risttabeli kasutamine
Andmeloendid

Sheet 23: Koond_Värv

Värvide kogused ja maksumus kuupäevade lõikes
Värvide kogused ja maksumus kuupäevade lõikes
Funktsiooni SUMIF kasutamine
Risttabeli kasutamine
Andmeloendid

Sheet 24: SumIf

Funktsioon SUMIF
SUMIF leiab kriteeriumile vastavad väärtused piirkonnas1 ning liidab kokku samades positsioonides olevad väärtused piirkonnas2.
SUMIF(piirkond1; kriteerium [ ; piirkond2 ] )
Kui piirkond2 puudub, siis liidetakse piirkonna1 vastavad väärtused.
Kriteeriumiks võib olla:
 konstant: 13, "kask"
 lahtrinimi või -aadress: kood, B15
 võrdlus kujul "võrdlusmärk väärtus": "=5000"


Teha müükide arvestamise tabeli alusel koondtabelid müüjate ja puidu lõikes.
Müükide arvestus
November
Kuupäev Kaup Kogus Hind Summa Müüja
11/1/2005 riis 35 7.00 245.00 Kask
11/1/2005 suhkur 45 6.50 292.50 Kask
11/1/2005 suhkur 60 5.00 300.00 Saar
11/3/2005 jahu 300 5.00 1,500.00 Paju
11/3/2005 suhkur 140 7.00 980.00 Tamm
11/4/2005 manna 75 7.50 562.50 Kask
11/4/2005 riis 120 8.50 1,020.00 Saar
11/7/2005 manna 45 5.00 225.00 Paju
11/7/2005 jahu 400 6.00 2,400.00 Saar
11/7/2005 jahu 350 5.50 1,925.00 Saar
11/7/2005 manna 30 5.50 165.00 Tamm
1600
9,615.00
Koondandmed müüjate lõikes
isik kogus summa
Kask 155 1,100.00
Paju 345 1,725.00
Saar 930 5,645.00
Tamm 170 1,145.00
Kokku 1600 9,615.00