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

Märkus.: Soovime pakkuda teie keeles kõige ajakohasemat spikrisisu niipea kui võimalik. See leht on tõlgitud automaatselt ja sellel võib leiduda grammatikavigu või ebatäpsusi. Tahame, et sellest sisust oleks teile abi. Palun märkige selle lehe allservas, kas sellest teabest oli teile kasu või mitte. Soovi korral saab ingliskeelset artiklit lugeda siit.

Oletame, et teil on office asukoht arvude loendi ja on vaja teada, mis töötajad on iga Office'i. Arvutustabel on suur, võib arvate, et see keeruline ülesanne. See on tegelikult üsna lihtne teha, funktsioon lookup.

Funktsioonide VLOOKUP ja HLOOKUP koos INDEX ja MATCH,on kõige kasulikumad funktsioonid Excelis.

Märkus.: Otsinguviisardi funktsioon pole enam saadaval Excelis.

Siin on näide sellest, kuidas kasutada funktsiooni VLOOKUP.

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

Selles näites on B2 esimese argumendi– element, mis funktsioon vajab töötamiseks andmeid. Funktsiooni VLOOKUP, on see esimese argumendi väärtus, mida soovite leida. See argument võib olla lahtriviide või fikseeritud väärtus, nt "kask" või 21000. Teine argument on lahtrivahemik, C2-:E7, kust otsida väärtuseks soovite leida. Kolmas argument on see lahtrivahemik, mis sisaldab väärtust, et te soovite veeru.

Neljas argument pole kohustuslik. Sisestage TRUE või FALSE. Kui sisestate väärtuse TRUE, või jätke argument tühjaks, tagastab funktsioon ligikaudse vaste määratud esimese argumendi väärtus on. Kui sisestate FALSE, funktsioon vastavad väärtuse esimene argument abil. Teisisõnu, jättes neljas argument tühjaks – või sisestada TRUE – pakub rohkem paindlikkust.

Selles näites kirjeldatakse, kuidas funktsioon toimib. Kui sisestate väärtus lahtris B2 (esimese argumendi), VLOOKUP otsib lahtrid vahemikus C2:E7 (2 argument) ja tagastab lähima ligikaudse vaste kolmanda veeru ristumiskoht vahemikus, veerg E (3 argument).

Funktsiooni VLOOKUP tüüpiline kasutusviis

Neljas argument on tühi, tagastab funktsioon ligikaudse vaste. Kui see ei ole, siis oleks sisestamiseks ühte väärtusteloendisse veerud C ja D tulemi üldse.

Kui olete rahul VLOOKUP, Funktsioon HLOOKUP on sama lihtne kasutada. Sisestage samadel argumentidel, kuid see otsib ridade, veergude asemel.

Funktsioonide INDEX ja MATCH kasutamise asemel VLOOKUP

Kehtivad teatud piirangud VLOOKUP abil – funktsiooni VLOOKUP saab ainult soovitud väärtuse otsimiseks vasakult paremale. See tähendab, et veerg, mis sisaldab väärtust, saate otsida tuleks alati asub vasakul veerg, mis sisaldab tagastatavat väärtust. Nüüd kui arvutustabeli pole ehitatud sellisel viisil, siis kasutage funktsiooni VLOOKUP. Kasutage selle asemel funktsioonide INDEX ja MATCH kombinatsiooni.

See näide sisaldab lühikest loendit, kus väärtus, mille järgi soovime otsida – Chicago, ei asu vasakpoolseimas veerus. Seega ei saa me kasutada VLOOKUP-i. Selle asemel kasutame Chicago otsimiseks vahemikust B1:B11 funktsiooni MATCH. See asub 4. real. Seejärel kasutab INDEX seda väärtust otsinguargumendina ja otsib Chicago elanike arvu 4. veerust (veerg D). Kasutatav valem on kuvatud lahtris A14.

Veel näiteid INDEX ja MATCH kasutamise asemel funktsiooni VLOOKUP, vaadake artiklis https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ , arve Jelen Microsoft MVP.

Proovige järele!

Kui soovite otsingufunktsioonide katsetada, enne kui proovite neid oma andmetega, siin on mõned näidisandmed.

Funktsiooni VLOOKUP näide tööl

Kopeerige järgmised andmed tühja arvutustabeli.

Näpunäide.: Enne andmete kleepimine Exceli Veerulaiused jaoks veergudes A kuni C 250 pikslit, ja klõpsake nuppu Murra teksti ridu (menüüAvaleht jaotises 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

Tulemus

=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.

2,17

=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.

100

=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.

#N/A

=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.

#N/A

=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.

1,71

HLOOKUP näide

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

Näpunäide.: Enne andmete kleepimine Exceli Veerulaiused jaoks veergudes A kuni C 250 pikslit, ja klõpsake nuppu Murra teksti ridu (menüüAvaleht jaotises 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.

4

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

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

7

=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.

5

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

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

11

=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".

c

Funktsioonide INDEX ja MATCH näited

Viimase näites töötab koos tagastamiseks varaseim arve number ja selle vastava kuupäeva iga viis linna funktsioonide INDEX ja MATCH. Kuna numbrina, tagastatakse kuupäeva, kasutame funktsiooni TEXT vormindage kuupäevana. Funktsiooni INDEX kasutab funktsiooni MATCH tulemus tegelikult argumendina. Funktsioonide INDEX ja MATCH kombinatsiooni on kaks korda valemis kasutada iga – esmalt tagastamiseks arve number, ja siis tagasi kuupäeva.

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

Näpunäide.: Enne andmete kleepimine Exceli määratud veergudes A – D jaoks veeru laiust 250 pikslit, ja klõpsake nuppu Murra teksti ridu (menüüAvaleht jaotises joondus ).

Arve

City (Linn)

Arve kuupäev

Linna varaseim arve ja selle kuupäev

3115

Atlanta

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

Atlanta

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

Atlanta

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

Atlanta

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

Atlanta

27.04.12

3350

Atlanta

28.04.12

3390

Atlanta

01.05.12

3441

Atlanta

02.05.12

3517

Tartu

08.05.12

3124

Tallinn

09.04.12

3155

Austin

11.04.12

3177

Austin

19.04.12

3357

Austin

28.04.12

3492

Tallinn

06.05.12

3316

Pärnu

25.04.12

3346

Dallas

28.04.12

3372.

Dallas

01.05.12

3414

Dallas

01.05.12

3451

Dallas

02.05.12

3467

Dallas

02.05.12

3474

Dallas

04.05.12

3490

Dallas

05.05.12

3503

Pärnu

08.05.12

3151

Valga

09.04.12

3438

New Orleans

02.05.12

3471

Valga

04.05.12

3160

Haapsalu

18.04.12

3328

Haapsalu

26.04.12

3368

Haapsalu

29.04.12

3420

Tampa

01.05.12

3501

Haapsalu

06.05.12

Vt ka

Kiirülevaate kaart: funktsiooni VLOOKUP näpunäited

Otsingu- ja viitamisfunktsioonid (teatmematerjalid)

Funktsioon VLOOKUP argumendi tabeli_massiiv kasutamine

Täiendage Office'i kasutamise 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.

×