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

Näpunäide.: Proovige kasutada uusi XLOOKUP -ja XMATCH funktsioone ning selles artiklis kirjeldatud funktsioonide täiustatud versioone. Need uued funktsioonid toimivad mis tahes suunas ja tagastavad täpsed vasted vaikimisi, muutes need hõlpsamaks ja mugavamaks kui nende eelkäijad.

Oletame, et teil on Office ' i asukohtade numbrid ja teil on vaja teada, millised töötajad asuvad igas Office ' is. Arvutustabel on tohutu, nii et võite arvata, et see on keeruline ülesanne. Otsingu funktsiooni abil on see tegelikult üsna lihtne.

Funktsioonide VLOOKUP ja HLOOKUP koos indeksiga ja vastegaon Exceli kõige kasulikumad funktsioonid.

Märkus.: Otsinguviisard pole Excelis enam saadaval.

Funktsiooni VLOOKUP kasutamine on näide.

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

Selles näites on B2 esimene argument– andmete element, mida funktsioon peab töötama. Funktsiooni VLOOKUP puhul on see esimene argument väärtus, mida soovite otsida. See argument võib olla lahtriviide või püsiv väärtus (nt "Smith" või 21 000). Teine argument on lahtrivahemik, C2 –: E7, kus otsitakse otsitavat väärtust. Kolmas argument on selle lahtrivahemiku veerg, mis sisaldab teie soovitud väärtust.

Neljas argument pole kohustuslik. Sisestage kas TRUE või FALSE. 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, siis vastab funktsioon esimesele argumendile. Teisisõnu, kui jätate neljanda argumendi tühjaks või sisestate TÕESEd, annab see teile suurema paindlikkuse.

See näide illustreerib funktsiooni tööd. Kui sisestate väärtuse lahtris B2 (esimene argument), otsib funktsioon VLOOKUP lahtritest vahemikku C2: E7 (teine argument) ja tagastab vahemiku veeru E (kolmas argument) lähima ligikaudse vaste.

Funktsiooni VLOOKUP tüüpiline kasutusviis

Neljas argument on tühi, seega annab funktsioon tulemiks ligikaudse vaste. Vastasel juhul peaksite sisestama ühe väärtustest veerus C või D, et üldse tulemust saada.

Kui kasutate funktsiooni VLOOKUP, on funktsioon HLOOKUP sama lihtne kui kasutatav. Saate sisestada samad argumendid, kuid see otsib veergude asemel ridu.

Funktsiooni VLOOKUP asemel INDEX ja MATCH kasutamine

Funktsiooni VLOOKUP kasutamisel on teatud piirangud – funktsioon VLOOKUP saab otsida ainult väärtuse vasakult paremale. See tähendab, et veerg, mis sisaldab teie otsitud väärtust, peab alati asuma tagastatavat väärtust sisaldavast veerust vasakule. Nüüd, kui teie arvutustabel pole sel viisil loodud, siis ärge kasutage funktsiooni VLOOKUP. Kasutage selle asemel funktsioonide INDEX ja MATCH kombinatsiooni.

Selles näites on kujutatud väikest loendit, kus väärtus, mida soovite otsida, Chicagos, pole vasakpoolses veerus. Seega ei saa me kasutada funktsiooni VLOOKUP. Selle asemel kasutame funktsiooni MATCH, et leida Chicago vahemikus B1: B11. See leiti reas 4. Seejärel kasutab register seda väärtust otsingu argumendina ja leiab, et Chicago rahvaarv on neljandas veerus (veerg D). Kasutatav valem kuvatakse lahtris a14.

Funktsioonide INDEX ja MATCH kasutamine väärtuse otsimiseks

Lisanäiteid funktsiooni VLOOKUP asemel indeksi ja VASTE kasutamise kohta leiate artiklist https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill Jelen, Microsoft MVP.

Proovige järele!

Kui soovite katsetada otsingu funktsioone enne, kui proovite neid oma andmetega läbi vaadata, siis siin on mõned Näidisandmete andmed.

Funktsiooni VLOOKUP näide tööl

Kopeerige järgmised andmed tühja arvutustabelisse.

Näpunäide.: Enne andmete kleepimist Excelisse seadke Veergude laiused veergudesse C – 250 pikslit ja klõpsake nuppu Murra teksti (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.

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 kleepimist Excelisse seadke Veergude laiused veergudesse C – 250 pikslit ja klõpsake nuppu Murra teksti (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.

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 rida 1 üles "B" ja annab tulemiks sama veeru realt 3 oleva väärtuse. Kuna täpset vastet "B" ei leita, kasutatakse rea 1 suurimat väärtust, mis on väiksem kui "B", "teljed" veerus A.

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 kolme rea konstanti arvu 3 ja annab tulemiks rea 2 väärtuse samast veerust (antud juhul kolmandale). Massiivi konstantis on kolm rida väärtusi, mille iga rida eraldatakse semikooloniga (;). Kuna "c" on leitud reas 2 ja samast veerust kui 3, tagastatakse "c".

c

Näited INDEX ja MATCH

Viimases näites kasutatakse funktsioone INDEX ja MATCH koos, et tagastada varaseim arve number ja vastav kuupäev iga viie linna kohta. Kuna kuupäev tagastatakse arvuna, kasutame funktsiooni TEXT selle vormindamiseks kuupäevana. 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.

Näpunäide.: Enne andmete kleepimist Excelisse seadke Veergude laiused veergudesse A kuni D kuni 250 pikslit ja klõpsake nuppu Murra teksti (menüüAvaleht , jaotis joondus ).

Arve

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

Atlanta

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

Austin

09.04.12

3155

Austin

11.04.12

3177

Austin

19.04.12

3357

Austin

28.04.12

3492

Tallinn

06.05.12

3316

Dallas

25.04.12

3346

Dallas

28.04.12

3372

Pärnu

01.05.12

3414

Pärnu

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

New Orleans

09.04.12

3438

New Orleans

02.05.12

3471

Valga

04.05.12

3160

Tampa

18.04.12

3328

Tampa

26.04.12

3368

Tampa

29.04.12

3420

Tampa

01.05.12

3501

Haapsalu

06.05.12

Vt ka

Lühijuhend: funktsioon VLOOKUP: VLOOKUP

ja teatmematerjalid (teatmematerjalid)

Kasutage funktsiooni VLOOKUP table_array argumenti

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.

×