Namig
Poskusite uporabiti novi funkciji XLOOKUP in XMATCH , izboljšani različici funkcij, ki so opisane v tem članku. Te nove funkcije delujejo v kateri koli smeri in privzeto vrnejo natančne zadetke, zaradi česar so preprostejše in priročnejše za uporabo od svojih predhodnikov.
Recimo, da imate seznam številk lokacij pisarn in morate vedeti, kateri zaposleni so v katerih pisarnah. Preglednica je ogromna, zato se vam bo morda zdelo, da je to zahtevno opravilo. To lahko preprosto naredite z iskalno funkcijo.
Funkciji VLOOKUP in HLOOKUP ter funkciji INDEX in MATCH sta eni od najbolj uporabnih funkcij v Excelu.
Opomba
Funkcija »Čarovnik za iskanje« ni več na voljo v Excelu.
Tukaj je primer uporabe funkcije VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
V tem primeru je celica B2 prvi argument – element podatkov, ki ga funkcija potrebuje za delovanje. Za funkcijo VLOOKUP je ta prvi argument vrednost, ki jo želite najti. Ta argument je lahko sklic na celico ali nespremenljiva vrednost, kot na primer »smith« ali 21,000. Drugi argument je obseg celic, C2-:E7, v katerem iščete želeno vrednost. 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 pa vnesete FALSE, se bo funkcija ujemala z vrednostjo prvega argumenta. Drugače rečeno, če pustite četrti argument prazen ali če vnesete TRUE, zagotovite večjo prilagodljivost.
Ta primer kaže, kako funkcija deluje. Ko v celico B2 (prvi argument) vnesete vrednost, funkcija VLOOKUP preišče celice v obsegu C2:E7 (drugi argument) in vrne najbližji približek iz tretjega stolpca v obsegu, stolpec E (3. argument).
Četrti argument je prazen, tako da funkcija vrne približek vrednosti. Če temu ni tako, morate vnesti eno od vrednosti v stolpec C ali D, da dobite rezultat.
Ko ste zadovoljni s funkcijo VLOOKUP, je uporaba funkcije HLOOKUP enako preprosta. Vnesete iste argumente, le da funkcija 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 vrednost poišče le od leve proti desni. To pomeni, da mora biti stolpec z vrednostjo, po kateri iščete, vedno levo od stolpca, ki vsebuje vrnjeno vrednost. Če vaša 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, ki jo želimo iskati, Chicago, ni v skrajno levem stolpcu. Zato funkcije VLOOKUP ni mogoče uporabiti. Namesto tega bomo s funkcijo MATCH poiskali Chicago v obsegu B1:B11. Najdete jo v vrstici 4. Nato INDEX uporabi to vrednost kot argument za iskanje in poišče število prebivalcev za Chicago v 4. stolpcu (stolpec D). Uporabljena formula je prikazana v celici A14.
Poskusite
Če se želite preizkusiti z iskalnimi funkcijami, preden jih preskusite na svojih podatkih, uporabite te vzorčne podatke.
VLOOKUP Primer v službi
Te podatke kopirajte v prazno preglednico.
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 «).
| 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 (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 vrstici 1 in vrne vrednost iz vrstice 3, ki je v istem stolpcu. Ker ni mogoče najti natančnega ujemanja za »B«, se uporabi največja vrednost v vrstici 1, ki je manjša od »B«: »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 matrike s tremi vrsticami in vrne vrednost iz vrstice 2 v istem (v tem primeru tretjem) stolpcu. V konstanti polja so tri vrstice vrednosti, vsaka vrstica je ločena s podpičjem (;). Ker je »c« v vrstici 2 in v istem stolpcu kot 3, se vrne »c«. | c |
Primeri INDEX in MATCH
V zadnjem primeru sta funkciji INDEX in MATCH uporabljeni skupaj, da vrneta najstarejšo številko računa in ustrezen datum za vsako od petih mest. Ker je datum vrnjen kot število, ga oblikujemo kot datum s funkcijo TEXT. 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 |
Glejte tudi
Kartica za hitri sklic: Funkcija VLOOKUP
Funkcije za iskanje in sklicevanje (sklicevanje)