Namig: Poskusite uporabiti novi funkciji XLOOKUP in XMATCH , izboljšani različici funkcij, ki sta opisani v tem članku. Te nove funkcije delujejo v poljubni smeri in privzeto vrnejo natančne zadetke, zaradi česar so preprostejše in priročnejše za uporabo kot prejšnja opravila.
Recimo, da imate seznam številk lokacije pisarne in da morate vedeti, kateri zaposleni so v posamezni pisarni. Preglednica je ogromna, zato lahko menite, da je to zahtevno opravilo. To je pravzaprav precej preprosto delo s funkcijo iskanja.
Funkciji VLOOKUP in HLOOKUP skupaj s funkcijama INDEX in MATCH sta eni 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 jih funkcija potrebuje za delovanje. 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 želite poiskati vrednost, ki jo želite poiskati. Tretji argument je stolpec v tem 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 prvega argumenta. Z drugimi besedami, če pustite četrti argument prazen ali če vnesete TRUE, boste lahko bolj prilagodljivi.
Ta primer kaže, kako funkcija deluje. Ko v celico B2 (prvi argument) vnesete vrednost, funkcija VLOOKUP išče v celicah v obsegu C2:E7 (2. argument) in vrne najbli3/4je približek iz tretjega stolpca v obsegu, stolpcu E (3. argument).
Četrti argument je prazen, zato funkcija vrne približen rezultat. Če temu ni tako, morate vnesti eno od vrednosti v stolpec C ali D, da dobite rezultat.
Ko vam je na voljo funkcija VLOOKUP, je funkcija HLOOKUP prav tako preprosta za uporabo. Vnesete iste argumente, vendar ta išče v vrsticah namesto v stolpcih.
Uporaba funkcij INDEX in MATCH namesto funkcije VLOOKUP
Pri uporabi funkcije VLOOKUP obstajajo določene omejitve – funkcija VLOOKUP lahko poišče le vrednost od leve proti desni. To pomeni, da mora biti stolpec z vrednostjo, ki jo poiščete, vedno na levi strani stolpca, ki vsebuje vrnjeno vrednost. Če preglednica ni ustvarjena na ta način, ne uporabite funkcije VLOOKUP. Namesto tega uporabite kombinacijo funkcij INDEX in MATCH.
V tem primeru je prikazan majhen seznam, kjer vrednost, po kateri želimo iskati, Chicago, ni v skrajno levem stolpcu. Zato ne moremo uporabiti funkcije VLOOKUP. Namesto tega bomo s funkcijo MATCH našli Chicago v obsegu B1:B11. Našli smo ga v 4. vrstici. Nato index uporabi to vrednost kot argument za iskanje in poišče populacijo za Chicago v 4. stolpcu (stolpec D). Uporabljena formula je prikazana v celici A14.
Če želite več primerov uporabe funkcij INDEX in MATCH namesto funkcije VLOOKUP, https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ članka Bill Jelen, Microsoftov MVP.
Poskusite
Če želite preskusiti funkcije za iskanje, preden jih preskusite s svojimi podatki, je tukaj nekaj vzorčnih podatkov.
VLOOKUP Example at work
Te podatke kopirajte v prazno preglednico.
Namig: Preden prilepite podatke v Excel, nastavite širino stolpcev za stolpce od A do C na 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 funkcije 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 A do C na 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 1. vrstici in vrne vrednost iz 3. vrstice istega stolpca. Ker natančnega ujemanja za »B« ni mogoče najti, se uporabi največja vrednost v 1. vrstici, ki je manjša od vrednosti »B«: »Gredi« 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 število 3 v tri vrsticah konstante polja in vrne vrednost iz 2. vrstice v istem (v tem primeru tretji) stolpcu. V konstanti polja so tri vrstice vrednosti, vsaka vrstica pa je ločena s podpičjem (;). Ker je »c« najden v 2. vrstici in istem stolpcu kot 3, je vrnjena vrednost »c«. |
c |
Primeri funkcij INDEX in MATCH
V tem zadnjem primeru sta funkciji INDEX in MATCH uporabljeni skupaj, da vrneta številko najzgodnejšega računa in ustrezen datum za vsako od petih mest. Ker je datum vrnjen kot število, ga s funkcijo TEXT oblikujemo 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 A do D na 250 slikovnih pik in kliknite Prelomi besedilo (zavihek Osnovno, skupina Poravnava).
|
Račun |
Mesto |
Datum računa |
Najnovejši račun glede na mesto, z datumom |
|
3115 |
Atlanta |
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 |
Atlanta |
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 |
Atlanta |
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 |
Atlanta |
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 |
Atlanta |
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 |
Atlanta |
27.4.2012 |
|
|
3350 |
Atlanta |
28.4.2012 |
|
|
3390 |
Atlanta |
1.5.2012 |
|
|
3441 |
Atlanta |
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 |
