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 veergude asemel ridade. "

Proovige järele!

Kui soovite otsingufunktsioonide katsetada, enne kui proovite neid oma andmetega, siin on mõned näidisandmed. Mõned Exceli kasutajad nagu abil VLOOKUP ja HLOOKUP; teised eelistate INDEX ja MATCH koos kasutamine. Proovige iga meetodi ja vaadake, millised teie parim.

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

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.

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

Linn

Arve kuupäev

Kuupäevaga linna varaseim arve

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

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.

×