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 ujemanja, zaradi česar so preprostejše in priročnejše za uporabo kot njihove predhodnike.
Recimo, da imate seznam številk pisarn in morate vedeti, kateri zaposleni so v vsaki pisarni. Preglednica je ogromna, zato morda mislite, da je to zahtevna naloga. Pravzaprav je to precej enostavno narediti s funkcijo iskanja.
Funkciji VLOOKUP in HLOOKUP sta skupaj s funkcijama INDEX in MATCH ena 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 funkcija potrebuje za delovanje. Za VLOOKUP je ta prvi argument vrednost, ki jo želite najti. Ta argument je lahko sklic na celico ali fiksna vrednost, na primer »koval« ali 21.000. Drugi argument je obseg celic C2-:E7, v katerem želite poiskati vrednost, ki jo želite najti. 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 bo funkcija ujemala z vrednostjo, ki jo je navedel prvi argument. Z drugimi besedami, če pustite četrti argument prazen – ali vnesete TRUE – vam daje več prilagodljivosti.
Ta primer kaže, kako funkcija deluje. Ko vnesete vrednost v celico B2 (prvi argument), funkcija VLOOKUP preišče celice v obsegu C2:E7 (2. argument) in vrne najbližje približno ujemanje iz tretjega stolpca v obsegu, stolpca E (3. argument).
Č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.
Če vam VLOOKUP ustreza, je funkcija HLOOKUP enako enostavna za uporabo. Vnesete iste argumente, vendar iščete v vrsticah namesto po stolpcih.
Uporaba funkcij INDEX in MATCH namesto funkcije VLOOKUP
Pri uporabi funkcije VLOOKUP obstajajo določene omejitve – funkcija VLOOKUP lahko poišče vrednost le od leve proti desni. To pomeni, da mora biti stolpec z vrednostjo, ki jo iščete, vedno levo od stolpca z vrnjeno vrednostjo. Če preglednica ni ustvarjena na ta način, ne uporabljajte funkcije VLOOKUP. Namesto tega uporabite kombinacijo funkcij INDEX in MATCH.
Ta primer prikazuje majhen seznam, kjer vrednost, ki jo želimo iskati, Chicago, ni v skrajnem levem stolpcu. Zato ne moremo uporabiti funkcije VLOOKUP. Namesto tega bomo uporabili funkcijo MATCH, da poiščemo Chicago v obsegu B1:B11. Najdemo ga v vrstici 4. Nato funkcija INDEX uporabi to vrednost kot argument za iskanje in v 4. stolpcu (stolpec D) poišče populacijo za Chicago. Uporabljena formula je prikazana v celici A14.
Poskusite
Če želite preizkusiti funkcije iskanja, preden jih preizkusite z lastnimi podatki, je tukaj nekaj vzorčnih podatkov.
Primer VLOOKUP v službi
Kopirajte naslednje podatke 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 (zavihek Osnovno , 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 A–C na 250 slikovnih pik in kliknite »Prelomi besedilo « (zavihek »Osnovno «, 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, ki je v istem stolpcu. Ker ni bilo najdeno natančno ujemanje za »B«, je uporabljena največja vrednost v 1. vrstici, ki je manjša kot »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 trivrstični konstanti v obliki polja in vrne vrednost iz 2. vrstice v istem stolpcu (v tem primeru tretjem). V konstanti v obliki polja so tri vrstice vrednosti, pri čemer je vsaka ločena s podpičjem (;). Ker je vrednost »c« najdena v 2. vrstici in v istem stolpcu kot število 3, je »c« vrnjena vrednost. | c |
Primera funkcij INDEX in MATCH
V zadnjem primeru sta funkciji INDEX in MATCH uporabljeni hkrati zaradi pridobivanja številke najnovejšega računa in ustreznega datuma za vsako od petih mest. Ker je datum vrnjen kot številka, uporabimo funkcijo TEXT, da ga 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 A–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 |
Glejte tudi
Kartica za hitri sklic: Funkcija VLOOKUP
Funkcije za iskanje in sklicevanje (sklicevanje)