Väärtuste otsimine funktsiooniga VLOOKUP, INDEX või MATCH

NB! :  See artikkel on masintõlgitud, vaadake lahtiütlust. Selle artikli ingliskeelse versiooni leiate aadressilt (viiteks).

Oletagem, et teil on loetelu ettevõtte esinduste telefoninumbritega ja soovite teada saada, millised töötajad igas esinduses töötavad. Arvutustabel on aga väga mahukas – mida teha? Kasutage otsingufunktsiooni! Eriti on abiks funktsioonid VLOOKUP ja HLOOKUP ning INDEX ja MATCH.

Märkus. :  Kui otsite otsinguviisardit, siis teadke, et see funktsioon pole enam Exceli osa.

Funktsiooni VLOOKUP kasutamine käib nii.

=VLOOKUP(B2;C2:E7;3;TRUE)

Esimene argument (andmekild, mida funktsioon töötamiseks vajab) on väärtus, mida soovite leida. See võib olla nii lahtriviide kui ka konkreetne väärtus (nt "soo" või 21 000). Teine argument on lahtrivahemik, mis teie arvates võiks teie otsitavat väärtust sisaldada. Käesolevas näites on selleks C2–C7. Kolmas argument on veerg selles lahtrivahemikus, mis sisaldab teie soovitud väärtust.

Neljas argument pole kohustuslik. Võite sisestada väärtuse True (Tõene) või False (Väär). Kui sisestate väärtuse TRUE või jätate selle argumendi ära, tagastab funktsioon esimeses argumendis määratud väärtuse ligikaudse vaste. Kui sisestate väärtuse FALSE, otsib funktsioon esimesena argumendina sisestatud väärtusele täpse vaste. Teisisõnu annab neljanda argumendi ära jätmine või väärtuse TRUE sisestamine teile rohkem paindlikkust.

See näide illustreerib funktsiooni tööd. Kui sisestate väärtuse lahtrisse B2 (esimene argument), otsib VLOOKUP seda lahtrivahemikust C2–E7 (teine argument) ja tagastab lähima ligikaudse vaste selle vahemiku kolmandast veerust ehk veerust E (kolmas argument).

Funktsiooni VLOOKUP tüüpiline kasutusviis

Kuna neljas argument on tühi ehk ära jäetud, tagastab funktsioon ligikaudse vaste. Vastasel juhul peaksite sisestama ühe väärtustest veerus C või D, et üldse tulemust saada.

Kui olete funktsiooni VLOOKUP kasutamisega juba tuttav, ei tohiks ka funktsioon HLOOKUP teil üle jõu käia. Argumendid, mis tuleb sisestada, on samad, kuid see funktsioon otsib väärtusi veergude asemel ridadest.

Proovige ise järele

Kui soovite otsingufunktsioone katsetada enne oma andmetega töötamist, siis siin on veidi näidisandmeid. Mõnele inimesele meeldib kasutada funktsioone VLOOKUP ja HLOOKUP, teised aga eelistavad kasutada koos funktsiooni INDEX ja MATCH. Proovige iga võimalust ja vaadake, millised teile kõige paremini sobivad.

Funktsiooni VLOOKUP kasutamine

Kopeerige selle tabeli kõik lahtrid ja kleepige need tühja Exceli töövihiku lahtrisse A1.

Vihje.    Enne, kui kleebite andmed Excelisse, määrake veergude A kuni C laiuseks 250 pikslit ja klõpsake nuppu Murra teksti ridu (menüü Avaleht jaotis Joondus).

Tihedus

Viskoossus

Temperatuur

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Valem

Kirjeldus

Tulem

'=VLOOKUP(1;A2:C10;2)

Otsib veerust A ligikaudset vastet väärtusele 1, leiab veerust A suurima väärtuse, mis on väiksem või võrdne väärtusega 1 (see on 0,946), ja tagastab väärtuse sama rea veerust B.

=VLOOKUP(1;A2:C10;2)

=VLOOKUP(1;A2:C10;3;TRUE)

Otsib veerust A ligikaudset vastet väärtusele 1, leiab veerust A suurima väärtuse, mis on väiksem või võrdne väärtusega 1 (see on 0,946), ja tagastab väärtuse sama rea veerust C.

=VLOOKUP(1;A2:C10;3;TRUE)

'=VLOOKUP(0;7;A2:C10;3;FALSE)

Otsib veerust A täpset vastet väärtusele 0,7. Kuna veerus A täpset vastet ei leidu, tagastatakse veaväärtus.

=VLOOKUP(0;7;A2:C10;3;FALSE)

'=VLOOKUP(0;1;A2:C10;2;TRUE)

Otsib veerust A ligikaudset vastet väärtusele 0,1. Kuna 0,1 on väiksem kui veeru A kõige väiksem väärtus, tagastatakse veaväärtus.

=VLOOKUP(0;1;A2:C10;2;TRUE)

'=VLOOKUP(2;A2:C10;2;TRUE)

Otsib veerust A ligikaudset vastet väärtusele 2, leiab veerust A suurima väärtuse, mis on väiksem või võrdne väärtusega 2 (see on 1,29), ja tagastab väärtuse sama rea veerust B.

=VLOOKUP(2;A2:C10;2;TRUE)

Funktsiooni HLOOKUP kasutamine

Kopeerige selle tabeli kõik lahtrid ja kleepige need tühja Exceli töövihiku lahtrisse A1.

Vihje.    Enne, kui kleebite andmed Excelisse, määrake veergude A kuni C laiuseks 250 pikslit ja klõpsake nuppu Murra teksti ridu (menüü Avaleht jaotis Joondus).

Teljed

Kuullaagrid

Poldid

4

4

9

5

7

10

6

8

11

Valem

Kirjeldus

Tulem

'=HLOOKUP("Teljed"; A1:C4; 2; FALSE)

Otsib 1. reast väärtust "Teljed" ja tagastab väärtuse sama veeru (veeru A) 2. reast.

=HLOOKUP("Teljed";A1:C4;2;TRUE)

'=HLOOKUP("Kuullaagrid"; A1:C4; 3; FALSE)

Otsib 1. reast väärtust "Kuullaagrid" ja tagastab väärtuse sama veeru (veeru B) 3. reast.

=HLOOKUP("Kuullaagrid";A1:C4;3;FALSE)

'=HLOOKUP("P"; A1:C4; 3; TRUE)

Otsib 1. reast väärtust "P" ja tagastab väärtuse sama veeru 3. reast. Kuna väärtusele "P" täpset vastet ei leita, kasutatakse suurimat väärtust reas 1, mis on väiksem kui "P", ehk "Kuullaagrid" veerus B.

=HLOOKUP("B";A1:C4;3;TRUE)

'=HLOOKUP("Poldid"; A1:C4; 4)

Otsib 1. reast väärtust "Poldid" ja tagastab väärtuse sama veeru (veeru C) 4. reast.

=HLOOKUP("Poldid";A1:C4;4)

'=HLOOKUP(3; {1\2\3;"a"\"b"\"c";"d"\"e"\"f"}; 2; TRUE)

Otsib kolmerealisest massiivikonstandist arvu 3 ja tagastab samas (käesoleval juhul kolmandas) veerus 2. reas asuva väärtuse. Massiivikonstandis on kolm rida väärtusi ja iga rida on eraldatud semikooloniga (;). Kuna 2. reas ning arvuga 3 samas veerus asub täht "c", tagastatakse tulemina "c".

=HLOOKUP(3;{1\2\3;"a"\"b"\"c";"d"\"e"\"f"};2;TRUE)

Funktsioonide INDEX ja MATCH kasutamine

See näide kasutab kõigi viie linna jaoks varaseima arvenumbri ja vastava kuupäeva väljaselgitamiseks funktsioone INDEX ja MATCH koos. Kuna kuupäev tagastatakse arvuna, siis kasutame selle vormindamiseks kuupäevana funktsiooni TEXT. Funktsioon INDEX kasutab ühe argumendina funktsiooni MATCH tulemit. Funktsioonide INDEX ja MATCH kombinatsiooni kasutatakse igas valemis kaks korda – esiteks arvenumbri tagastamiseks ja teiseks kuupäeva tagastamiseks.

Kopeerige selle tabeli kõik lahtrid ja kleepige need tühja Exceli töövihiku lahtrisse A1.

Vihje.    Enne, kui kleebite andmed Excelisse, määrake veergude A kuni D laiuseks 250 pikslit ja klõpsake nuppu Murra teksti ridu (menüü Avaleht jaotis Joondus).

Arve

Linn

Arve kuupäev

Linna varaseim arve ja selle kuupäev

3115

Tartu

07.04.12

="Tartu = "&INDEX($A$2:$C$33;MATCH("Tartu";$B$2:$B$33;0);1)& "; Arve kuupäev: " & TEXT(INDEX($A$2:$C$33;MATCH("Tartu";$B$2:$B$33;0);3);"dd.mm.yyyy")

3137

Tartu

09.04.12

="Tallinn = "&INDEX($A$2:$C$33;MATCH("Tallinn";$B$2:$B$33;0);1)& "; Arve kuupäev: " & TEXT(INDEX($A$2:$C$33;MATCH("Tallinn";$B$2:$B$33;0);3);"dd.mm.yyyy")

3154

Tartu

11.04.12

="Pärnu = "&INDEX($A$2:$C$33;MATCH("Pärnu";$B$2:$B$33;0);1)& "; Arve kuupäev: " & TEXT(INDEX($A$2:$C$33;MATCH("Pärnu";$B$2:$B$33;0);3);"dd.mm.yyyy")

3191

Tartu

21.04.12

="Valga = "&INDEX($A$2:$C$33;MATCH("Valga";$B$2:$B$33;0);1)& "; Arve kuupäev: " & TEXT(INDEX($A$2:$C$33;MATCH("Valga";$B$2:$B$33;0);3);"dd.mm.yyyy")

3293

Tartu

25.04.12

="Haapsalu = "&INDEX($A$2:$C$33;MATCH("Haapsalu";$B$2:$B$33;0);1)& "; Arve kuupäev: " & TEXT(INDEX($A$2:$C$33;MATCH("Haapsalu";$B$2:$B$33;0);3);"dd.mm.yyyy")

3331

Tartu

27.04.12

3350

Tartu

28.04.12

3390

Tartu

01.05.12

3441

Tartu

02.05.12

3517

Tartu

08.05.12

3124

Tallinn

09.04.12

3155

Tallinn

11.04.12

3177

Tallinn

19.04.12

3357

Tallinn

28.04.12

3492

Tallinn

06.05.12

3316

Pärnu

25.04.12

3346

Pärnu

28.04.12

3372

Pärnu

01.05.12

3414

Pärnu

01.05.12

3451

Pärnu

02.05.12

3467

Pärnu

02.05.12

3474

Pärnu

04.05.12

3490

Pärnu

05.05.12

3503

Pärnu

08.05.12

3151

Valga

09.04.12

3438

Valga

02.05.12

3471

Valga

04.05.12

3160

Haapsalu

18.04.12

3328

Haapsalu

26.04.12

3368

Haapsalu

29.04.12

3420

Haapsalu

01.05.12

3501

Haapsalu

06.05.12

Lisateavet otsingufunktsioonide kohta

Lehe algusesse

Märkus. : Masintõlke lahtiütlus. Selle artikli tõlkis arvutisüsteem ilma inimese sekkumiseta. Microsoft pakub selliseid masintõlkeid, et inglise keelt mittekõnelevad kasutajad saaksid vaadata sisu Microsofti toodete, teenuste ja tehnoloogiate kohta. Kuna artikkel on masintõlgitud, võib see sisaldada sõnavara-, süntaksi- või grammatikavigu.

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×