Iskanje vrednosti s funkcijami VLOOKUP, INDEX ali MATCH

Namig: Poskusite uporabiti nove funkcije XLOOKUP in XMATCH , izboljšane različice funkcij, opisanih v tem članku. Te nove funkcije delujejo v kateri koli smeri in privzeto vrnejo natančne zadetke, tako da jih lažje in bolj priročno uporabite kot njihove predhodnike.

Recimo, da imate seznam Officeovih številk mest in morate vedeti, kateri zaposleni so v vsakem uradu. Preglednica je ogromna, tako da morda menite, da je zahtevna naloga. To je pravzaprav dokaj preprosto narediti s funkcijo lookup.

Funkcije VLOOKUP in HLOOKUP , skupaj z indeksom in ujemanjem,so nekatere od najbolj uporabnih funkcij v Excelu.

Opomba: Funkcija čarovnika za iskanje ni več na voljo v Excelu.

Tukaj je primer uporabe funkcije VLOOKUP.

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

V tem primeru je B2 prvi argument– element podatkov, ki ga mora funkcija delovati. Za funkcijo VLOOKUP je ta prvi argument vrednost, ki jo želite poiskati. Ta argument je lahko sklic na celico ali nespremenljiva vrednost, kot je» Smith «ali 21.000. Drugi argument je obseg celic, C2-: E7, v katerem lahko poiščete vrednost, ki jo želite poiskati. Tretji argument je stolpec v obsegu celic, ki vsebuje vrednost, ki jo iščete.

Četrti argument je neobvezen. Vnesite TRUE ali FALSE. Če vnesete TRUE ali če pustite argument prazen, funkcija vrne približek vrednosti, ki ste jo navedli v prvem argumentu. Če vnesete» FALSE «, se funkcija ujema z vrednostjo, ki jo zagotovi prvi argument. Z drugimi besedami, zapuščanje četrtega argumenta prazno – ali vnašanje TRUE – omogoča večjo fleksibilnost.

Ta primer kaže, kako funkcija deluje. Ko vnesete vrednost v celico B2 (prvi argument), Funkcija VLOOKUP poišče celice v obsegu C2: E7 (2. argument) in vrne najbližjo približno ujemanje iz tretjega stolpca v obsegu, stolpec E (Tretji argument).

Tipična raba funkcije VLOOKUP

Četrti argument je prazen, zato funkcija vrne približno ujemanje. Če temu ni tako, morate vnesti eno od vrednosti v stolpec C ali D, da dobite rezultat.

Ko ste zadovoljni s funkcijo VLOOKUP, je funkcija HLOOKUP enako enostavna za uporabo. Vnesete enake argumente, vendar iskanje v vrsticah namesto v stolpcih.

Uporaba KAZALa in UJEMAnja namesto funkcije VLOOKUP

Na voljo so določene omejitve z uporabo funkcije VLOOKUP – Funkcija VLOOKUP lahko le poišče vrednost od leve proti desni. To pomeni, da je stolpec, ki vsebuje vrednost, ki jo želite poiskati, vedno na levi strani stolpca, ki vsebuje vrnjeno vrednost. Če vaša preglednica ni zgrajena na ta način, ne uporabljajte funkcije VLOOKUP. Namesto tega uporabite kombinacijo funkcij INDEX in MATCH.

V tem primeru je prikazan majhen seznam, kjer vrednost, ki jo želite poiskati, v Chicagu ni v skrajno levem stolpcu. Zato ne moremo uporabiti funkcije VLOOKUP. Namesto tega bomo uporabili funkcijo MATCH za iskanje Chicaga v obsegu B1: B11. Najdeno je v vrstici 4. Nato INDEX uporabi to vrednost kot argument za iskanje in poišče populacijo za Chicago v četrtem stolpcu (stolpec D). Uporabljena formula je prikazana v celici A14.

Iskanje vrednosti s funkcijo INDEX in MATCH

Če želite več primerov uporabe KAZALa in UJEMAnja namesto funkcije VLOOKUP, si oglejte članek https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ z računom jelen, Microsoft MVP.

Poskusite

Če želite preskusiti funkcije za iskanje, preden jih preskusite s svojimi podatki, naredite nekaj vzorčnih podatkov.

Primer» VLOOKUP «na delovnem mestu

Kopirajte te podatke v prazno preglednico.

Namig: Preden prilepite podatke v Excel, nastavite širino stolpcev za stolpce od C do 250 slikovnih pik in kliknite prelomi besedilo (zavihekosnovno , skupina Poravnava ).

Gostota

Viskoznost

Temperatura

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

Formula

Opis

Rezultat

=VLOOKUP(1,A2:C10,2)

Funkcija, ki išče približno ujemanje, poišče vrednost 1 v stolpcu A, poišče največjo vrednost v stolpcu A, ki je manjša ali enaka 1, in sicer 0,946, nato pa vrne ustrezno vrednost v isti vrstici iz stolpca B.

2,17

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

Funkcija, ki išče približno ujemanje, poišče vrednost 1 v stolpcu A, poišče največjo vrednost v stolpcu A, ki je manjša ali enaka 1, in sicer 0,946, nato pa vrne ustrezno vrednost v isti vrstici iz stolpca C.

100

=VLOOKUP(0.7,A2:C10,3,FALSE)

Funkcija, ki išče vrednost, ki se popolnoma ujema, poišče vrednost 0,7 v stolpcu A. Ker v stolpcu ni vrednosti, ki bi se popolnoma ujemala, vrne funkcija napako.

#N/V

=VLOOKUP(0.1,A2:C10,2,TRUE)

Funkcija, ki išče približek vrednosti, poišče vrednost 0,1 v stolpcu A. Ker je 0,1 manj kot najmanjša vrednost v stolpcu A, funkcija vrne napako.

#N/V

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

Funkcija, ki išče približno ujemanje, poišče vrednost 2 v stolpcu A, najde največjo vrednost v stolpcu A, ki je manjša ali enaka 2, in sicer 1,29, nato pa vrne ustrezno vrednost v isti vrstici iz stolpca B.

1,71

Primer» HLOOKUP «

Kopirajte vse celice v tej tabeli in jih prilepite v celico A1 na praznem Excelovem delovnem listu.

Namig: Preden prilepite podatke v Excel, nastavite širino stolpcev za stolpce od C do 250 slikovnih pik in kliknite prelomi besedilo (zavihekosnovno , skupina Poravnava ).

Gredi

Ležaji

Zapahi

4

4

9

5

7

10

6

8

11

Formula

Opis

Rezultat

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

Poišče »Gredi« v 1. vrstici in vrne vrednost iz 2. vrstice istega stolpca (stolpec A).

4

=HLOOKUP("Ležaji"; A1:C4; 3; FALSE)

Poišče »Ležaji« v 1. vrstici in vrne vrednost iz 3. vrstice istega stolpca (stolpec B).

7

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

Poišče» B «v vrstici 1 in vrne vrednost iz vrstice 3, ki je v istem stolpcu. Ker ni mogoče najti natančnega ujemanja za» B «, je največja vrednost v vrstici 1, ki je manjša od» B «, uporabljena:» osi «v stolpcu A.

5

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

Poišče »Zapahi« v 1. vrstici in vrne vrednost iz 4. vrstice istega stolpca (stolpec C).

11

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

Poišče številko 3 v konstanti polja s tremi vrsticami in vrne vrednost iz vrstice 2 v istem stolpcu (v tem primeru tretjega). V konstanti polja so tri vrstice vrednosti, vsaka vrstica je ločena s podpičjem (;). Ker je» c «najden v vrstici 2 in v istem stolpcu kot 3, se vrne» c «.

c

Primeri KAZALa in UJEMAnja

Ta zadnji primer uporablja funkcije INDEX in MATCH skupaj, da vrne najzgodnejšo številko računa in ustrezni datum za vsako od petih mest. Ker je datum vrnjen kot število, uporabite funkcijo TEXT, da jo oblikujete kot datum. Funkcija INDEX dejansko uporabi rezultat funkcije MATCH kot argument. Kombinacija funkcij INDEX in MATCH je uporabljena dvakrat v vsaki formuli – prvič zaradi pridobivanja številke računa, drugič zaradi pridobivanja datuma.

Kopirajte vse celice v tej tabeli in jih prilepite v celico A1 na praznem Excelovem delovnem listu.

Namig: Preden prilepite podatke v Excel, nastavite širino stolpcev za stolpce od D do 250 slikovnih pik in kliknite prelomi besedilo (zavihekosnovno , skupina Poravnava ).

Račun

Mesto

Datum računa

Najnovejši račun glede na mesto, z datumom

3115

Brezje

7.4.2012

="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Datum računa: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"m/d/ll")

3137

Brezje

9.4.2012

="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Datum računa: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"m/d/ll")

3154

Brezje

11.4.2012

="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Datum računa: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"m/d/ll")

3191

Brezje

21.4.2012

="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Datum računa: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"m/d/ll")

3293

Brezje

25.4.2012

="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Datum računa: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"m/d/yy")

3331

Brezje

27.4.2012

3350

Brezje

28.4.2012

3390

Brezje

1.5.2012

3441

Brezje

2.5.2012

3517

Atlanta

8.5.2012

3124

Austin

9.4.2012

3155

Austin

11.4.2012

3177

Austin

19.4.2012

3357

Austin

28.4.2012

3492

Austin

6.5.2012

3316

Dallas

25.4.2012

3346

Dallas

28.4.2012

3372

Dallas

1.5.2012

3414

Dallas

1.5.2012

3451

Dallas

2.5.2012

3467

Dallas

2.5.2012

3474

Dallas

4.5.2012

3490

Dallas

5.5.2012

3503

Dallas

8.5.2012

3151

New Orleans

9.4.2012

3438

New Orleans

2.5.2012

3471

New Orleans

4.5.2012

3160

Tampa

18.4.2012

3328

Tampa

26.4.2012

3368

Tampa

29.4.2012

3420

Tampa

1.5.2012

3501

Tampa

6.5.2012

Glejte tudi

Kartica s kratkimi

in» reference «(sklic)

uporabite argument» TABLE_ARRAY «v funkciji VLOOKUP

Ali potrebujete dodatno pomoč?

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×