Funkcija Excel VLOOKUP
O Excel VLOOKUP funkcija je zmogljivo orodje, ki vam pomaga poiskati določeno vrednost z ujemanjem v prvem stolpcu tabele ali obsega navpično in nato vrne ustrezno vrednost iz drugega stolpca v isti vrstici. Čeprav je funkcija VLOOKUP izjemno uporabna, je lahko začetnikom včasih težko razumeti. Ta vadnica vam pomaga obvladati VLOOKUP z zagotavljanjem razlaga argumentov po korakih, uporabni primeri in rešitve pogostih napak na katere lahko naletite pri uporabi funkcije VLOOKUP.
Related Videos
Postopna razlaga argumentov
Kot je prikazano na zgornjem posnetku zaslona, se funkcija VLOOKUP uporablja za iskanje e-pošte na podlagi dane ID številke. Zdaj bom podrobno razložil, kako uporabljati VLOOKUP v tem primeru, tako da bom vsak argument razčlenil korak za korakom.
1. korak: Zaženite funkcijo VLOOKUP
Izberite celico (v tem primeru H6), da izpišete rezultat, nato zaženite funkcijo VLOOKUP tako, da v polje vnesete naslednjo vsebino. Formula Bar.
=VLOOKUP(
2. korak: Določite iskalno vrednost
Najprej določite iskalno vrednost (kar iščete) v funkciji VLOOKUP. Tukaj se sklicujem na celico G6, ki vsebuje določeno ID številko 1005.
=VLOOKUP(G6
3. korak: Določite polje tabele
Nato določite obseg celic, ki vsebuje vrednost, ki jo iščete, in vrednost, ki jo želite vrniti. V tem primeru izberem obseg B6:E12. Formula je zdaj videti takole:
=VLOOKUP(G6,B6:E12
=VLOOKUP(G6,$B$6:$E$12
4. korak: Določite stolpec, iz katerega želite vrniti vrednost
Nato določite stolpec, iz katerega želite vrniti vrednost.
V tem primeru, ker moram vrniti e-pošto na podlagi številke ID, tukaj vnesem številko 4, da VLOOKUP-u sporočim, naj vrne vrednost iz četrtega stolpca obsega podatkov.
=VLOOKUP(G6,B6:E12,4
5. korak: Poiščite približno ali natančno ujemanje
Na koncu ugotovite, ali iščete približno ujemanje ali natančno ujemanje.
- Če želite najti natančno ujemanje, morate uporabiti FALSE kot zadnji argument.
- Če želite najti približno ujemanje, Uporaba TRUE kot zadnji argument ali pa pustite prazno.
V tem primeru uporabljam FALSE za natančno ujemanje. Formula zdaj izgleda takole:
=VLOOKUP(G6,B6:E12,4,FALSE
Pritisnite tipko Enter, da dobite rezultat
Z razlago vsakega argumenta enega za drugim v zgornjem primeru so sintaksa in argumenti funkcije VLOOKUP zdaj veliko lažje razumljivi.
Sintaksa in argumenti
=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])
- Iskalna_vrednost (obvezno): vrednost (resnična vrednost ali sklic na celico), ki jo iščete. Ne pozabite, da mora biti ta vrednost v prvem stolpcu table_array.
- Tabela_ matrika (obvezno): obseg celic vsebuje stolpec iskalne vrednosti in stolpec vrnjene vrednosti.
- Col_index (obvezno): Celo število predstavlja številko stolpca, ki vsebuje vrnjeno vrednost. Začne se s številko 1 za skrajno levi stolpec table_array.
- Območje_iskanje (neobvezno): logična vrednost, ki določa, ali želite, da VLOOKUP najde približno ali natančno ujemanje.
- Približno ujemanje - Ta argument nastavite na TRUE, 1 ali pusti prazno.
Pomembno: Če želite najti približno ujemanje, morajo biti vrednosti v prvem stolpcu table_array razvrščene v naraščajočem vrstnem redu, če VLOOKUP vrne napačen rezultat. - Natančna tekma - Ta argument nastavite na FALSE or 0.
- Približno ujemanje - Ta argument nastavite na TRUE, 1 ali pusti prazno.
Primeri
V tem razdelku je prikazanih nekaj primerov, ki vam bodo pomagali bolje razumeti funkcijo VLOOKUP.
Primer 1: Natančno ujemanje proti približnemu ujemanju v VLOOKUP
Če ste zmedeni glede natančnega in približnega ujemanja pri uporabi funkcije VLOOKUP, vam lahko ta razdelek pomaga odpraviti to zmedo.
Natančno ujemanje v VLOOKUP
V tem primeru bom poiskal ustrezna imena na podlagi rezultatov, navedenih v obsegu E6:E8, zato bom v celico F6 vnesel naslednjo formulo in ročico za samodejno izpolnjevanje povlekel navzdol do F8. V tej formuli je zadnji argument določen kot FALSE za iskanje natančnega ujemanja.
=VLOOKUP(E6,$B$6:$C$12,2,FALSE)
Ker pa ocena 98 ne obstaja v prvem stolpcu obsega podatkov, VLOOKUP vrne rezultat napake #N/A.
Približno ujemanje v VLOOKUP
Še vedno uporabljate zgornji primer, če spremenite zadnji argument v TRUE, bo VLOOKUP izvedel iskanje približnega ujemanja. Če ne najde nobenega ujemanja, bo poiskal naslednjo največjo vrednost, ki je manjša od iskalne vrednosti, in vrnil ustrezen rezultat.
=VLOOKUP(E6,$B$6:$C$12,2,TRUE)
Ker rezultat 98 ne obstaja, VLOOKUP poišče naslednjo največjo vrednost, ki je manjša od 98, kar je 95, in vrne ime rezultata 95 kot najbližji rezultat.
- V tem primeru približnega ujemanja morajo biti vrednosti v prvem stolpcu table_array razvrščene v naraščajočem vrstnem redu. V nasprotnem primeru VLOOKUP morda ne bo vrnil pravilne vrednosti.
- Tukaj sem zaklenil matriko tabel ($B$6:$C$12) v funkciji VLOOKUP, da bi se hitro skliceval na dosleden niz podatkov glede na več iskalnih vrednosti.
2. primer: uporabite VLOOKUP z več kriteriji
Ta razdelek prikazuje, kako uporabljati VLOOKUP z več pogoji v Excelu. Kot je prikazano na spodnjem posnetku zaslona, če poskušate poiskati plačo na podlagi navedenega imena (v celici H5) in oddelka (v celici H6), sledite spodnjim korakom, da to storite.
1. korak: Dodajte pomožni stolpec za združevanje vrednosti iz iskalnih stolpcev
V tem primeru moramo ustvariti pomožni stolpec za združevanje vrednosti iz Ime stolpec in Oddelek stolpec.
- Dodajte pomožni stolpec na levo od obsega podatkov in temu stolpcu dodajte glavo. Oglejte si posnetek zaslona:
- V tem pomožnem stolpcu izberite prvo celico pod glavo, v polje vnesite naslednjo formulo Vrstica formulein pritisnite Vnesite.
=C6&" "&D6
Opombe: V tej formuli uporabljamo ampersand (&), da združimo besedilo v dveh stolpcih, da ustvarimo en sam del besedila.- C6 je prvo ime Ime stolpec za pridružitev, D6 je prvi oddelek v Oddelek stolpcu, da se pridružite.
- Vrednosti teh dveh celic sta povezani s presledkom vmes.
- Izberite to celico z rezultati in nato povlecite Ročaj za samodejno izpolnjevanje navzdol, da uporabite to formulo za druge celice v istem stolpcu.
2. korak: Uporabite funkcijo VLOOKUP z danimi kriteriji
Izberite celico, v katero želite izpisati rezultat (tu izberem I7), v polje vnesite naslednjo formulo Vrstica formule, nato pritisnite Vnesite.
=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
Rezultat
- Pomožni stolpec je treba uporabiti kot prvi stolpec obsega podatkov.
- Zdaj je stolpec s plačami peti stolpec obsega podatkov, zato uporabljamo številko 5 kot indeks stolpca v formuli.
- Pridružiti se moramo merilom I5 in I6 (I5& " "&I6) na enak način kot pomožni stolpec in uporabite povezano vrednost kot lookup_value argument v formuli.
- Oba pogoja lahko postavite tudi neposredno v argument lookup_value in ju ločite s presledkom (če sta pogoja besedilo, ju ne pozabite dati v dvojne narekovaje).
=VLOOKUP("Albee IT",B6:F12,5,FALSE)
- Boljša alternativa - iskanje z več kriteriji v nekaj sekundah
O Iskanje v več pogojih značilnost Kutools for Excel vam lahko pomaga enostavno poiskati z več merili v nekaj sekundah. Zagotovite si 30-dnevno brezplačno preskusno različico vseh funkcij!
Pogoste napake VLOOKUP in rešitve
V tem razdelku so navedene pogoste napake, na katere lahko naletite pri uporabi funkcije VLOOKUP, in rešitve za njihovo odpravo.
Vrnjena napaka #N/A
Najpogostejša napaka pri VLOOKUP je napaka #N/A, kar pomeni, da Excel ni mogel najti vrednosti, ki ste jo iskali. Tukaj je nekaj razlogov, zakaj lahko VLOOKUP vrne napako #N/A.
1. razlog: iskalna vrednost ni v prvem stolpcu table_array
Ena od omejitev Excelovega VLOOKUP-a je, da omogoča samo pogled od leve proti desni. Torej morajo biti iskalne vrednosti v prvem stolpcu table_array.
Kot je prikazano na spodnjem posnetku zaslona, želim vrniti ime glede na dani naziv delovnega mesta. Tukaj je iskalna vrednost (vodja prodaje) je v drugem stolpcu table_array in vrnjena vrednost je levo od stolpca za iskanje, zato VLOOKUP vrne napako #N/A.
rešitve
Za odpravo te napake lahko uporabite katero koli od naslednjih rešitev.
- Preuredite stolpce
Stolpce lahko preuredite tako, da stolpec za iskanje postavite v prvi stolpec table_array. - Uporabite funkciji INDEX in MATCH skupaj
Tukaj skupaj uporabljamo funkciji INDEX in MATCH kot alternativo VLOOKUP za rešitev te težave.=INDEX(B6:B12,MATCH(F6,C6:C12,0))
- Uporabite funkcijo XLOOKUP (na voljo v Excelu 365, Excelu 2021 in novejših različicah)
=XLOOKUP(F6,C6:C12,B6:B12)
2. razlog: iskalne vrednosti ni mogoče najti v iskalnem stolpcu (natančno ujemanje)
Eden od najpogostejših razlogov, zakaj VLOOKUP vrne napako #N/A, je, ker vrednost, ki jo iščete, ni najdena.
Kot je prikazano v spodnjem primeru, bomo poiskali ime na podlagi podane ocene 98 v E6. Vendar ta rezultat ne obstaja v prvem stolpcu obsega podatkov, zato VLOOKUP vrne rezultat napake #N/A.
rešitve
Če želite odpraviti to napako, lahko poskusite z eno od naslednjih rešitev.
- Če želite, da VLOOKUP išče naslednjo največjo vrednost, ki je manjša od iskalne vrednosti, spremenite zadnji argument FALSE (natančno ujemanje) z TRUE (približno ujemanje). Za več informacij glejte Primer 1: Natančno ujemanje proti približnemu ujemanju z uporabo funkcije VLOOKUP.
- Če se želite izogniti spreminjanju zadnjega argumenta in prejeti opomnik, če iskalna vrednost ni najdena, lahko funkcijo VLOOKUP vključite v funkcijo IFERROR:
=IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")
3. razlog: iskalna vrednost je manjša od najmanjše vrednosti v iskalnem stolpcu (približno ujemanje)
Kot je prikazano na spodnjem posnetku zaslona, izvajate iskanje približnega ujemanja. Vrednost, ki jo iščete (ID številka 1001 v tem primeru) je manjša od najmanjše vrednosti 1002 v stolpcu za iskanje, zato VLOOKUP vrne napako #N/A.
rešitve
Tukaj sta dve rešitvi za vas.
- Prepričajte se, da je iskalna vrednost večja ali enaka najmanjši vrednosti v iskalnem stolpcu.
- Če želite, da vas Excel opomni, da iskalna vrednost ni bila najdena, samo ugnezdite funkcijo VLOOKUP v funkcijo IFERROR, kot sledi:
=IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")
4. razlog: Številke so oblikovane kot besedilo
Kot lahko vidite na spodnjem posnetku zaslona, je rezultat napake #N/A v tem primeru posledica neujemanja vrste podatkov med celico za iskanje (G6) in stolpcem za iskanje (B6:B12) izvirne tabele. Tu je vrednost v G6 številka, vrednosti v obsegu B6:B12 pa so številke, oblikovane kot besedilo.
rešitve
Če želite rešiti to težavo, morate iskalno vrednost pretvoriti nazaj v število. Tukaj sta dve metodi za vas.
- Uporabite funkcijo Pretvori v številko
Kliknite na celico, v kateri želite besedilo pretvoriti v številko, izberite ta gumbpoleg celice in nato izberite Pretvori v številko.
- Uporabite priročno orodje za paketno pretvorbo med besedilom in številko
O Pretvori med besedilo in številko značilnost Kutools for Excel vam pomaga enostavno pretvoriti vrsto celic iz besedila v številko in obratno. Zagotovite si 30-dnevno brezplačno preskusno različico vseh funkcij!
5. razlog: table_array ni konstantna, ko povlečete formulo VLOOKUP v druge celice
Kot je prikazano na spodnjem posnetku zaslona, sta v E6 in E7 dve iskalni vrednosti. Ko dobite prvi rezultat v F6, povlecite formulo VLOOKUP iz celice F6 v F7, vrnjen je rezultat napake #N/A. To je zato, ker so sklice na celice (B6:C12) privzeto relativne in se prilagajajo, ko se premikate navzdol po vrsticah. Matrika tabel je bila premaknjena navzdol na B7:C13, ki ne vsebuje več rezultata iskanja 73.
Rešitev
Matriko tabel morate zakleniti, da ostane konstantna, tako da dodate a $ znak pred vrsticami in stolpci v referencah celic. Če želite izvedeti več o absolutni referenci v Excelu, si oglejte to vadnico: Excel absolutna referenca (kako narediti in uporabljati).
Vrnjena napaka #VALUE
Naslednji pogoji lahko povzročijo, da VLOOKUP vrne rezultat napake #VALUE.
1. razlog: iskalna vrednost presega 255 znakov
Kot je prikazano na spodnjem posnetku zaslona, iskalna vrednost v celici H4 presega 255 znakov, zato VLOOKUP vrne rezultat napake #VALUE.
rešitve
Če se želite izogniti tej omejitvi, lahko uporabite drugo funkcijo iskanja, ki lahko obravnava daljše nize. Poskusite eno od naslednjih formul.
- INDEX in MATCH:
=INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
- Funkcija XLOOKUP (na voljo v Excelu 365, Excelu 2021 in novejših različicah):
=XLOOKUP(H4,B5:B11,E5:E11)
2. razlog: argument col_index je manjši od 1
Indeks stolpca določa številko stolpca v matriki tabele, ki vsebuje vrednost, ki jo želite vrniti. Ta argument mora biti pozitivno število, ki ustreza veljavnemu stolpcu v matriki tabele.
Če vnesete indeks stolpca, ki je manjši od 1 (tj. nič ali negativen), VLOOKUP ne bo mogel poiskati stolpca v matriki tabele.
Rešitev
Če želite odpraviti to težavo, se prepričajte, da je argument indeksa stolpca v formuli VLOOKUP pozitivno število, ki ustreza veljavnemu stolpcu v matriki tabele.
Vrnjena napaka #REF
Ta razdelek navaja enega od razlogov, zakaj VLOOKUP vrne napako #REF, in nudi rešitve za to težavo.
Razlog: argument col_index je večji od števila stolpcev
Kot lahko vidite na spodnjem posnetku zaslona, ima matrika tabele le 4 stolpce. Vendar pa je indeks stolpca, ki ste ga določili v formuli VLOOKUP, 5, kar je večje od števila stolpcev v matriki tabele. Posledično VLOOKUP ne bo mogel poiskati stolpca in bo na koncu vrnil napako #REF.
rešitve
- Določite pravilno številko stolpca
Prepričajte se, da je argument indeksa stolpca v formuli VLOOKUP številka, ki ustreza veljavnemu stolpcu v matriki tabele.
- Samodejno pridobite številko stolpca na podlagi določene glave stolpca
Če tabela vsebuje veliko stolpcev, boste morda imeli težave pri določanju pravilne številke indeksa stolpca. Tukaj lahko ugnezdite funkcijo MATCH v funkcijo VLOOKUP, da poiščete položaj stolpca na podlagi določene glave stolpca.
=VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
Opombe: V zgornji formuli je MATCH("E-pošta",B5:E5, 0) funkcija se uporablja za pridobitev številke stolpca "E-pošta" v časovnem obsegu B6:E12. Tukaj je rezultat 4, ki se uporablja kot col_index v funkciji VLOOKUP.
Vrnjena je napačna vrednost
Če ugotovite, da VLOOKUP ne vrne pravilnega rezultata, je to lahko posledica naslednjih razlogov
1. razlog: stolpec za iskanje ni razvrščen v naraščajočem vrstnem redu
Če ste zadnji argument nastavili na TRUE (ali pustil prazno) za približno ujemanje in stolpec za iskanje ni razvrščen v naraščajočem vrstnem redu, je lahko dobljena vrednost napačna.
Rešitev
Razvrščanje stolpca za iskanje v naraščajočem vrstnem redu vam lahko pomaga rešiti to težavo. Če želite to narediti, sledite spodnjim korakom:
- Izberite podatkovne celice v stolpcu za iskanje, pojdite na datum jeziček, kliknite Razvrsti od Najmanjše do Največje v Razvrsti in filtriraj skupina.
- v Razvrsti opozorilo v pogovornem oknu izberite Razširite izbor in kliknite OK.
2. razlog: stolpec je vstavljen ali odstranjen
Kot je prikazano na spodnjem posnetku zaslona, je vrednost, ki sem jo prvotno želel vrniti, v četrtem stolpcu matrike tabele, zato podam številko col_index kot 4. Ko je vstavljen nov stolpec, stolpec z rezultati postane peti stolpec tabele matriko, zaradi česar VLOOKUP vrne rezultat iz napačnega stolpca.
rešitve
Tukaj sta dve rešitvi za vas.
- Številko indeksa stolpca lahko ročno spremenite tako, da se ujema s položajem povratnega stolpca. Tukaj je treba formulo spremeniti v:
=VLOOKUP(H6,B6:F12,5,FALSE)
- Če želite vedno vrniti rezultat iz določenega stolpca, kot je stolpec E-pošta v tem primeru. Naslednja formula lahko pomaga samodejno ujemati indeks stolpca na podlagi podane glave stolpca, ne glede na to, ali so stolpci vstavljeni ali odstranjeni iz matrike tabele.
=VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)
Druge opombe o funkcijah
- VLOOKUP išče samo vrednost od leve proti desni.
Iskalna vrednost je v skrajnem levem stolpcu, vrednost rezultata pa mora biti v katerem koli stolpcu desno od iskanega stolpca. - Če pustite zadnji argument prazen, VLOOKUP privzeto uporabi približno ujemanje.
- VLOOKUP izvede iskanje, ki ne razlikuje med velikimi in malimi črkami.
- Za več ujemanj VLOOKUP vrne samo prvo ujemanje, ki ga najde v matriki tabele, glede na vrstni red vrstic v matriki tabele.
Povezani članki
20+ primerov VLOOKUP za začetnike in napredne uporabnike programa Excel
Ta vadnica prikazuje, kako uporabljati funkcijo Vlookup v Excelu z desetinami osnovnih in naprednih primerov korak za korakom.
VLOOKUP od desne proti levi
Če želite poiskati določeno vrednost v katerem koli drugem stolpcu in vrniti relativno vrednost na levo, vam lahko metode v tej vadnici pomagajo pri izpolnitvi te naloge.
Vpogled od spodaj navzgor
Ta vadnica ponuja dve metodi za pomoč pri iskanju ujemajoče se vrednosti od spodaj navzgor.
Izvedite vpogled z upoštevanjem velikih in malih črk
Če želite VLOOKUP v Excelu razlikovati med velikimi in malimi črkami, vam lahko metoda v tej vadnici naredi uslugo.
VLOOKUP ohrani izvorno oblikovanje
Ta vadnica ponuja metodo, ki vam pomaga ohraniti celotno oblikovanje nastale celice, ko izvajate Vlookup v Excelu.
Najboljša pisarniška orodja za produktivnost
Napolnite svoje Excelove spretnosti z Kutools for Excel, in izkusite učinkovitost kot še nikoli prej. Kutools for Excel Ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa. Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...
Office Tab Prinaša vmesnik z zavihki v Office in vam olajša delo
- Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
- Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
- Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!

Kazalo
- Related Videos
- Postopna razlaga argumentov
- Sintaksa in argumenti
- VLOOKUP Primeri
- Natančno ujemanje proti približnemu ujemanju
- VLOOKUP z več pogoji
- Pogoste napake in rešitve
- Napaka #N/A
- Napaka #VALUE
- Napaka #REF
- Nepravilna vrednost
- Druge opombe o funkcijah
- Povezani članki
- Najboljša orodja za pisarniško produktivnost
- Komentarji