Informaatika I tunnitöö "Tabelid 3. Otsimine" (0)

Otsimis- ja viitamisfunktsioonid
Otsimis- ja viitamisfunktsioonid
Funktsioon INDEX - üldpõhimõtted
Funktsioon INDEX - näited 1
Funktsioon INDEX - näited 2
Andmed korterite kohta
Otsimise üldpõhimõtted
Funktsioon  LOOKUP
Funktsioon  VLOOKUP
Funktsioon  MATCH
Funktsiooni MATCH tööpõhimõte.  Demo
Funktsioonide INDEX ja MATCH kooskasutamine. Paralleelsed  vektorid
Funktsioonide  ekstreemumite  ja nende asukohtade leidmine
Funktsioonide INDEX ja MATCH kooskasutamine. Tabel
Vahemiku otsimine
Harjutus "Komisjonitasu"
Otsimine kahes suunas. INDEX & MATCH ja VLOOKUP. 
Funktsioonide INDEX ja MATCH kooskasutamine. Paralleelsed vektorid
veeb
klipp index
Funktsioon INDEX
Võimaldab viidata vektorite (rivid, tulbad) ja 
tabelite
elementidele (lahtritele) indeksite abil. Kaks 
V(k); V[k]
põ IhN
i D
va E
ri X
a  n(vekt
ti:
or; indeks)
V  = INDEX(V; k)
k
INDEX (tabel; riviindeks; tulbaindeks)
T  = INDEX(T; i; j)
i, j
T(i, j); T[i] [j]
vektor  - rivi või  tulp : ühemõõtmeline  massiiv
indeks - elemendi (lahtri)  järjenumber  vektoris
Err:512
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 
k
Vektor V
Vk
massiivi algusest.
13
-27
65
89
-24
5
-24
k
1
2
3
4
5
=INDEX(V; k)
Maatriks A  (4*3)
i
13
4
5
1
7
21
-5
2
i
j
Ai, j
-9
33
5
3
3
3
5
8
17
12
4
2
=INDEX(A; i; j)
j
1
2
3
veeb
klipp index
Funktsiooni INDEX kasutamine
Leida kuu numbrile vastav kuu nimi
lehel abi on vektor kuude nimedega
nr
kuu nimi
7
juuli
Leida kuupäevale vastav kuu nimi ja nädalapäeva nimi
kuupäev
kuu nimi päeva nimi
12/1/ 2016  detsember neljapäev
Palgaarvestus
Tari f leida lehel abi  olevast   vektorist
Nimi
kat
tunde
tariif
Palk
U. Saar
3
132
9.6
1267 .2
H.  Kask
4
143
10.8
1544.4
E. Tamm
6
137
14.2
1945.4
U. Paju
10
135
25.6
3456
K.  Lepp
5
68
12.6
856.8
päevad
kuud
kateg
tariifid
esmaspäev
jaanuar
1
3.8
teisipäev
veebruar
2
6.8
kolmapäev
märts
3
9.6
neljapäev
april
4
10.8
reede
mai
5
12.6
laupäev
juuni
6
14.2
pühapäev
juuli
7
17
august
8
18.8
september
9
21.8
oktoober
10
25.6
november
detsember
tariifid
Kuu_Nr
1
2
3
4
5
6
7
8
9
10
11
12
klipp index
Funktsiooni INDEX kasutamine
Leida korteri numbri järgi tabelist Ko
Ko r
rtte
erriid
d
omaniku nimi, inimeste arv ja korteri pindala
Kaks varianti:
1) eraldi nimi igal tulbal,
2) üks nimi tervel tabelil
krt
7
omanik
V. Tamm
V. Tamm
inimesi
3
3
pind
45.6
45.6
Variant 1
Variant 2
Korterid
1
2
3
4
1 Number
Omanik
Pindala
Inimesi
Vaadake millised piirkonnad 
vastavad  nimedele  Omanik, 
2
1
A. Kask
65.3
3
Pindala ja Inimesi
3
2
P. Lepp
74.5
2
Näita Omanik
4
3
K.  Mänd
65.3
5
5
4
L. Tamm
42.6
2
Näita  Pindala
6
5
H.  Kuusk
74.5
1
Näita Inimesi
7
6
O. Palm
63.7
2
8
7
V. Tamm
45.6
3
9
8
S. Mets
82.4
5
10
NB! Nimi korterid on määratud pi rkonnale,
mis sisaldab päise rivi, tühja rivi lõpus ning 
veergu  Number. Sellega peab arvestama 
funktsiooni INDEX  kasutamisel  
Näita
Vaadake millised piirkonnad 
vastavad nimedele Omanik, 
Pindala ja Inimesi
Näita Omanik
Näita  Pindala
Näita Inimesi
veeb
Funktsioon MATCH
Iseseisvalt harva. Sageli koos funktsiooniga INDEX.
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
kui otsitavaga võrdset väärtust ei ole,  tagastab  veateate #N/A
1 - vahemiku otsimine - väärtused vektoris peavad olema  kasvamise  järjekorras
kui otsitavaga võrdset väärtust ei ole, tagastab lähima väikseima otsitavale
koht
Leida võistleja koht ja punktide arv
1
kõrvalolevast tabelist
2
võistleja
koht
punkte
3
Kask
1
360
4
5
6
Leida kuu nimetuse järgi selle järjenumber
kuu nimetus
kuu number
november
11
Leida korteri omaniku järgi korteri number,
korteri pindala ja inimeste arv (tabel lehel Korterid)
Omanik
Korter
Pindala
Inimesi
H. Kuusk
5
74.5
1
Iseseisvalt harva. Sageli koos funktsiooniga INDEX.
klipp otsimine
. Kui puudub võetakse 1.
kui otsitavaga võrdset väärtust ei ole, tagastab veateate #N/A
 - väärtused vektoris peavad olema kasvamise järjekorras
kui otsitavaga võrdset väärtust ei ole, tagastab lähima väikseima otsitavale
nimi
punkte
Kask
360
Saar
322
Paju
301
Tamm
280
Kuusk
270
Mänd
265
Funktsiooni MATCH tööpõhimõte
Kindla väärtuse otsimine
Tagastab otsitava järjenumbri antud vektoris
või teate selle puudumise kohta
kLiigid
###
saar
###
kask
p #
aju
liik
nr
###
kuusk
tamm
7
j #
alakas
Näita
v #
aher
Sisestage  lahtrisse  liik väärtus ja vaadake 
t #
amm
J
kuidas toimub kindla väärtuse otsimine
###
mänd
h #
aab
x, V
k = 1
Otsi(x, V)
 * iga elemendi korral V-s
V(k) = x
tagasta k
ei
k = k + 1
Sisestage lahtrisse liik väärtus ja vaadake 
kuidas toimub kindla väärtuse otsimine
tagasta 0
veeb
klipp otsimine
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 väärtuste kasvamise järjekorras
Leida korteri omaniku järgi pindala ja inimeste arv
korterid
Omanik
pind
inimesi
H. Kuusk
Leida värvi kogus ja maksumus korterite lagede värvimiseks
korter
värv
kogus
maksumus  Kasutage  
1
VL_11
funktsioone 
INDEX() ja 
2
VL_06
MATCH()
3
VL_13
4
VL_02
5
VL_11
6
VL_11
7
VL_02
8
VL_11
klipp otsimine
Koodid
Nimetused
Pakendis Hinnad
214002
Dor  Blue  juust 50% 100 g
5
1.1
214003
ARLA juust Kvibille Grädd.140g
10
1.48
214006
Juust ROSENBORG sinihallitus 125g
8
2
214073
FETAKI juust 500 g
24
2.19
214071
Juust  Camembert  125 g
24
2.48
214157
Juust  Atleet  viil  vaakumis  (ca 150 g)
1
7.02
214158
Juust Eesti viil vaakumis (ca 150 g)
1
7.02
214004
ARLA juust Kvibille Grädd.3kg
1
27.49
Leida nimetuse alusel kauba kood ja hind
nimetus
kood
hind
Juust Atleet vi l vaakumis (ca 150 g)
veeb
INDEX & MATCH
Otsimine vertikaalses tabelis 
leiab rivi numbri
tulba number
INDEX(tabel; MATCH(otsitav; v_tulp; OV); t_tulp)
MATCH leiab otsitava väärtusele vastava järjenumbri (jnr) tulbas v_tulp
v_tulp - võtmete tulp tabelis (tavaliselt 1. tulp)
INDEX tagastab väärtuse lahtrist tabel(jnr, t_tulp) 
Funktsiooni VLOOKUP korral peab 
võtmete tulp olema alati vasemal tulemuse 
t_tulp - tulba number, kust võetakse tagastatav väärtustulbast.
INDEX & MATCH korral seda ei nõuta
Võrdluseks funktsioon VLOOKUP
VLOOKUP(otsitav; tabel; t_tulp; OV)
Leida värvi hind ja kulu, kasutades funktsioone
INDEX ja MATCH otsimiseks tabelis
Värv
Hind
Kulu
VL_30
3.92
0.47
Näide
Leida tabelist Juustud nimetuse järgi kauba kood ja hind
Nimetus
kood
hind
Juust Camembert 125 g
214071
2.5
) tulbas v_tulp
Funktsiooni VLOOKUP korral peab 
võtmete tulp olema alati vasemal tulemuse 
tulbast.
INDEX & MATCH korral seda ei nõuta
Koodid
Nimetused
Pakendis Hinnad
214002
Dor Blue juust 50% 100 g
5
1.1
214003
ARLA juust Kvibille Grädd.140g
10
1.5
214006
Juust ROSENBORG sinihallitus 125
8
2
214073
FETAKI juust 500 g
24
2.2
214071
Juust Camembert 125 g
24
2.5
214157
Juust Atleet viil vaakumis (ca 150 g)
1
7
214158
Juust Eesti viil vaakumis (ca 150 g)
1
7
214004
ARLA juust Kvibille Grädd.3kg
1
27.5
Funktsioonide ekstreemumite ja nende asukohtade leidmine
Koostada valemid, mis  leivad  fumktsiooni Y  maksimumi
ja funktsioni Z miinimumi ja nende asukohad
algus
samm
lõpp
Ymax
koht
Zmin
koht
-6
1
14
2.853455
12
-2.779473
7
x
Y
Z
-6 -2.247686 2.470079
4
-5 0.339172 2.434557
-4 1.135204 -0.00912
-3 -0.17857 -1.27292
3
-2
0 -0.531953
-1 2.215381
0
2
0 2.727892 2.524413
1 0.371533 2.393951
2 -1.226706 0.228742
1
3 -0.203495 -0.160602
4 0.348834
1.19716
Y
0
5 -1.579022 0.671556
-6
-5
-4
-3
-2
-1
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Z
6 -2.938372 -1.951235
7 -1.157793 -2.779473
-1
8 1.066788 -0.808136
9 0.632381 0.344032
-2
10 -0.456616 -0.850978
11 0.893279 -1.140759
12 2.853455 1.210296
-3
13 1.905189 2.902245
14 -0.651153
1.47076
-4
klipp otsimine
Funktsioonide ekstreemumite ja nende asukohtade leidmine
4
3
2
1
Y
0
-6
-5
-4
-3
-2
-1
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Z
-1
-2
-3
-4
veeb
Klipp  SumIF
Funktsioonid COUNTIF ja SUMIF 
COUNTIF leiab kriteeriumile vastavate väärtuste arvu antud piirkonnas
     COUNTIF(piirkond;  kriteerium )
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 ol a:
    konstant: 13, "kask"
    lahtrinimi või -aadress: kood , B15
   võrdlus kujul "võrdlusmärk väärtus":  "=5000"
Arvud
arv
23
21
3
Mitu korda esineb  loetelus  arv 13
-45
2
Mitu korda esineb loetelus  lahtris  arv olev arv
13
122
Positiivsete arvude summa
18
7
Positiivsete arvude arv
21
17.42857 Positiivsete arvude aritmeetiline keskmine
13
2
Mitu korda esineb loetelus arv 21
-16
-61
Negatiivsete arvude summa
21
2
Negatiivsete arvude arv
13
-30.5
Negatiivsete arvude aritmeetiline keskmine
Harjutustabel
Arvutite müükide arvestus
AM_SIF
Koosta  koondtabelid müüdud arvutite arvu ja 
nende maksumuse kohta:
  1. linnade lõikes,
A_Kuupäev
A_Linn
A_Arvuti
A_Arv
A_Hind
A_Maksumus
  2. arvutite lõikes,
02.01.15
Tallinn
Frodo
2
1,330
2,660
  3. kuupäevade lõikes.
02.01.15
Valga
Balrog
1
419
419
  
03.01.15
Tallinn
Aragorn
2
499
998
03.01.15
Tallinn
Balrog
3
419
1,257
03.01.15
Võru
Gandalf
2
849
1,698
04.01.15
Tallinn
Eldar
3
465
1,395
04.01.15
Tartu
Balrog
5
419
2,095
07.01.15
Pärnu
Eldar
3
465
1,395
07.01.15
Tallinn
Faram
1
599
599
07.01.15
Valga
Balrog
6
419
2,514
07.01.15
Valga
Frodo
1
1,330
1,330
09.01.15
Tallinn
Grimbold
6
1,149
6,894
10.01.15
Pärnu
Elend
1
1,299
1,299
10.01.15
Pärnu
Rohan
4
589
2,356
10.01.15
Tartu
Sauron
2
339
678
11.01.15
Tallinn
Frodo
1
1,330
1,330
11.01.15
Tartu
Balrog
2
419
838
11.01.15
Valga
Aragorn
1
499
499
14.01.15
Pärnu
Gandalf
3
849
2,547
14.01.15
Tartu
Frodo
4
1,330
5,320
14.01.15
Valga
Kalvar
2
699
1,398
16.01.15
Tallinn
Arven
5
369
1,845
17.01.15
Tallinn
Balrog
7
419
2,933
17.01.15
Tartu
Aragorn
3
499
1,497
18.01.15
Pärnu
Eldar
1
465
465
18.01.15
Tallinn
Aragorn
1
499
499
19.01.15
Tallinn
Frodo
3
1,330
3,990
19.01.15
Tartu
Kalvar
1
699
699
19.01.15
Võru
Elend
5
1,299
6,495
20.01.15
Tallinn
Elend
2
1,299
2,598
20.01.15
Võru
Sauron
3
339
1,017
21.01.15
Pärnu
Arven
1
369
369
25.01.15
Võru
Sauron
1
339
339
26.01.15
Pärnu
Gandalf
2
849
1,698
90
63,963
Koosta koondtabelid müüdud arvutite arvu ja 
nende maksumuse kohta:
  1. linnade lõikes,
  2. arvutite lõikes,
  3. kuupäevade lõikes.
  
veeb
Funktsioon LOOKUP
LOOKUP(otsitav; vek_1; vek_2)
Otsib otsitavale vastavat väärtust vektorist vek_1 ning 
tagasrtab sama numbriga väärtuse vektorist vek_2
kui otsitavaga võrdset väärtust ei ole, loetakse vastavaks otsitavale lähim väikseim väärtus
Väärtused vektoris vek_1 peavad olema kasvamise järjekorras!
NB! Ei võimalda määrata otsimisviisi. Sobib peamiselt vahemiku otsimiseks!
"Sõnaraamat"
Leida eestikeelsele sõnale ingliskeelne  vaste
Sõnad om lehel "Sõnad" tulpades "eesti" ja "inglise"
eesti
inglise
kass
cat
kui otsitavaga võrdset väärtust ei ole, loetakse vastavaks otsitavale lähim väikseim väärtus
Sobib peamiselt vahemiku otsimiseks!
eesti
inglise
ahv
monkey
elevant
elephant
gepard
cheetah
hiir 
mouse
hirv
deer
hobune
horse
hunt
wolf
jänes
hare
kalkun
turkey
kana
hen
karu
bear
kass
cat
kobras
beaver
koer
dog
kukk
cock
küülik
rabbit
lammas
sheep
lehm
cow
lõvi
lion
mäger
badger
põder
elk
põrsas
piglet
rebane
fox
rott
rat
siga
pig
siil
hedgehong
tall
lamb
tiiger
tiger
varss
foal
vasikas
calf
veeb
Funktsioon VLOOKUP
VLOOKUP (otsitav; tabel; t_tulp; otsimisviis)
Otsimine vertikaalses tabelis
Otsib väärtust tabeli  esimesest  tulbast 
t_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
1 või True  - vahemiku otsimine
Leida koodi järgi kauba hind ja nimetus tabelist  Kaubad
kood
hind
Nimetus
Funktsioon HLOOKUP
HLOOKUP (otsitav; tabel; t_rivi; otsimisviis)
Otsimine horisontaalses tabelis
Otsib väärtust tabeli esimesest rivist
t_rivi - tabeli rivi number, kust võetakse tagastatav väärtus
otsimisviis - sama nagu VLOOKUP'is
Koodid
Nimetus
Pakendis
Hind
214002
Dor Blue juust 50% 100 g
5
1.1
214003
ARLA juust Kvibille Grädd.140g
10
1.48
214006
Juust ROSENBORG sinihallitus 125g
8
2
214073
FETAKI juust 500 g
24
2.19
214071
Juust Camembert 125 g
24
2.48
214157
Juust Atleet viil vaakumis (ca 150 g)
1
7.02
214158
Juust Eesti viil vaakumis (ca 150 g)
1
7.02
214004
ARLA juust Kvibille Grädd.3kg
1
27.49
veeb
Otsimise ja otsimisfunktsioonide kasutamise üldpõhimõtted
Otsitav väärtus võib olla tekst, arv, kuupäev, jm. Sel ele vastavat väärtust otsitakse 
otsimistabeli võtmete  piirkonnast .
Otsimise piirkond ehk otsimistabel - tabel või vektor (tulp, rivi), kust otsitakse antud
väärtust ning sageli võetakse ka tagastatav väärtus
Võtmete piirkond ehk võtmete väli - pi rkond (tulp või rivi), kust otsitakse antud 
väärtusele vastavat väärtust. Sageli osa (näi.t tabeli tulp) otsimispi rkonnast.
Tagastatav väärtus - otsitava väärtuse asukoht (järjenumber) võtmepiirkonnas või
selle alusel valitud väärtus otsimispi rkonnast.                        
Võib kasutada kahte otsimisviisi:
  Järjestikune otsimine ehk kindla väärtuse otsimine
    Eeldatakse, et võtmete pi rkonnas on olemas täpselt sama väärtus, mida otsitakse.
    Kui otsitavaga võrdne väärtus võtmepi rkonnas puudub, siis tagastatakse 
veateade .
    Väärtused võtmete piirkonnas ei pea olema järjestatud.
 Kahendotsimine ehk vahemiku otsimine
    Ei eeldata otsitava väärtusega võrdse väärtuse olemasolu võtmete piirkonnas. 
    Kui otsitav väärtus puudub, si s loetakse vastavaks lähim väärtus, mis on 
   väiksem  otsitavast. 
    Väärtused võtmepiirkonnas peavad olema järjestatud kasvamise järjekorras.
Puidu hind sõltub läbimõõdust.
Vahemiku otsimine
vahemik
hind
piirid
hinnad
= 30
56
30
56
läbimõõt
hind
18
52
Hinnakiri . Puidu hind sõltub li gist ja läbimõõdust
Sort
Liik
1
2
3
haab
50.00
45.00
40.00
kask
70.00
63.00
56.00
kuusk
80.00
72.00
64.00
küte
47.00
42.30
37.60
lepp
65.00
58.50
52.00
mänd
75.00
67.50
60.00
saar
85.00
76.50
68.00
tamm
110.00
99.00
88.00
vaher
95.00
85.50
76.00
Liik
sort
hind
lepp
2
58.5
VLOOKUP(otsitav; tabel; v_nr [; tunnus])
Hinnakiri
liigid
hinnad
liik
hind
tamm
230
mänd
150
 =VLOOKUP(liik; Hinnakiri; 2; 0)
vaher
220
150
 =VLOOKUP(liik; Hinnakiri; 2)
saar
210
kuusk
160
mänd
150
kask
120
lepp
110
haab
76
paju
76
Otsimistabelite näiteid
Koodi järgi võib otsida  nimetust , hinda ja ühikute arvu pakendis
Nimetuse järgi võib otsida koodi, hinda ja ühikute arvu pakendis
kood
kaup
hind
214004
ARLA Juust Kvibille Grädd.3kg
22.91
kood
kaup
pakendis
hind
214003
ARLA Juust Kvibille Grädd.140g
10
1.24
214004
ARLA Juust Kvibille Grädd.3kg
1
22.91
214002
Dor Blue juust 50% 100 g
5
0.92
214073
FETAKI juust 500 g
24
1.82
214157
Juust Atleet viil vaakumis (ca 150 g
1
5.85
214071
Juust Camembert 125 g
24
2.07
214158
Juust Eesti viil vaakumis (ca 150 g
1
5.85
Hinnakiri. Puidu hind sõltub li gist ja läbimõõdust
=30
Liigid
0
10
15
20
25
30
haab
12
17
19
24
25
25
kask
15
23
27
32
33
33
kuusk
29
32
35
37
39
38
mänd
32
35
38
41
43
42
saar
34
36
41
44
45
45
tamm
40
45
50
57
60
73
vaher
36
40
43
45
47
53
liik
läbimõõt
hind
kuusk
23
37
 =VLOOKUP(liik; Hinnakiri; 2; 0)
 =VLOOKUP(liik; Hinnakiri; 2)
Värvide hinnakiri
v_mark v_kulu v_hind
v_tootja
VL_13
0.52
3.60
Sadolin
VL_30
0.47
3.92
Kessu
VL_01
0.52
3.98
JukuColor
VL_25
0.42
4.08
Kessu
VL_06
0.45
4.13
JukuColor
VL_11
0.48
4.14
JukuColor
VL_02
0.48
4.40
Sadolin
VL_21
0.39
5.17
Sadolin
VL_09
0.55
5.24
JukuColor
VL_05
0.65
5.29
JukuColor
VL_32
0.55
8.23
Kessu
Vahemiku otsimine
Funktsioonide LOOKUP ning INDEX ja MATCH kasutamine vahemiku otsimiseks
Kauba hind sõltub ostetavast kogusest
kogus - K
hind
1