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