Funkcija VLOOKUP v Excelu – Vodnik za začetnike: sintaksa in primeri

Danes začenjamo serijo člankov, ki opisujejo eno najbolj uporabnih funkcij Excela − VPR (VLOOKUP). Ta funkcija je hkrati ena najbolj kompleksnih in najmanj razumljenih.

V tej vadnici o VPR Osnove bom poskušal predstaviti čim bolj preprosto, da bi bil učni proces čim bolj jasen za neizkušene uporabnike. Poleg tega bomo preučili več primerov z Excelovimi formulami, ki bodo prikazali najpogostejše primere uporabe funkcije VPR.

  • Splošni opis in sintaksa
  • VLOOKUP Primeri
    • Kako uporabljati VLOOKUP za iskanje v drugem Excelovem listu
    • Iščite v drugem delovnem zvezku z VLOOKUP
    • Kako uporabiti imenovan obseg ali tabelo v formulah z VLOOKUP
    • Uporaba nadomestnih znakov v formulah VLOOKUP
  • Natančno ali približno ujemanje v funkciji VLOOKUP
  • VLOOKUP v Excelu – to si morate zapomniti!

Funkcija VLOOKUP v Excelu – splošni opis in sintaksa

Kaj je torej? VPR? No, najprej je to Excelova funkcija. Kaj počne? Poišče vrednost, ki jo podate, in vrne ustrezno vrednost iz drugega stolpca. Tehnično gledano, VPR poišče vrednost v prvem stolpcu danega obsega in vrne rezultat iz drugega stolpca v isti vrstici.

V najpogostejši aplikaciji je funkcija VPR v bazi podatkov išče dani enolični identifikator in iz baze podatkov izvleče nekaj informacij, povezanih z njim.

Prva črka v imenu funkcije VPR (VLOOKUP) pomeni Вnavpično (Vnavpično). Po njem lahko ločite VPR iz GPR (HLOOKUP), ki išče vrednost v zgornji vrstici obsega − Гvodoravno (Hvodoravno).

funkcija VPR na voljo v Excelu 2013, Excelu 2010, Excelu 2007, Excelu 2003, Excelu XP in Excelu 2000.

Sintaksa funkcije VLOOKUP

funkcija VPR (VLOOKUP) ima naslednjo sintakso:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Kot lahko vidite, funkcija VPR v programu Microsoft Excel ima 4 možnosti (ali argumente). Prve tri so obvezne, zadnja je neobvezna.

  • lookup_value (lookup_value) – vrednost, ki jo je treba iskati. To je lahko vrednost (število, datum, besedilo) ali sklic na celico (ki vsebuje iskalno vrednost) ali vrednost, ki jo vrne kakšna druga Excelova funkcija. Na primer, ta formula bo iskala vrednost 40:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

Če je iskalna vrednost manjša od najmanjše vrednosti v prvem stolpcu obsega, ki ga iščete, funkcija VPR bo javil napako #AT (#N/A).

  • table_array (tabela) – dva ali več stolpcev podatkov. Ne pozabite, funkcija VPR vedno išče vrednost v prvem stolpcu obsega, podanega v argumentu table_array (miza). Vidni obseg lahko vsebuje različne podatke, kot so besedilo, datumi, številke, logične vrednosti. Funkcija ne razlikuje med velikimi in malimi črkami, kar pomeni, da so velike in male črke enake. Torej bo naša formula iskala vrednost 40 v celicah iz A2 do A15, ker je A prvi stolpec obsega A2:B15, podanega v argumentu table_array (tabela):

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

  • col_index_num (številka_stolpca) je številka stolpca v danem obsegu, iz katerega bo vrnjena vrednost v najdeni vrstici. Skrajno levi stolpec v danem obsegu je 1, drugi stolpec je 2, tretji stolpec je 3 in tako naprej. Zdaj lahko preberete celotno formulo:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

    Formula, ki išče vrednost 40 v območju A2: A15 in vrne ustrezno vrednost iz stolpca B (ker je B drugi stolpec v obsegu A2:B15).

Če je vrednost argumenta col_index_num (številka_stolpca) manj kot 1POTEM VPR bo javil napako #VALUE! (#VREDNOST!). In če je več kot je število stolpcev v obsegu table_array (tabela), bo funkcija vrnila napako #REF! (#POVEZAVA!).

  • iskanje_razpona (range_lookup) – določa, kaj iskati:
    • natančno ujemanje, argument mora biti enak FALSE (NEPRAVIČNO);
    • približno ujemanje, argument je enak PRAVA KODA (TRUE) ali sploh ni navedeno.

    Ta parameter je neobvezen, vendar zelo pomemben. Kasneje v tej vadnici VPR Pokazal vam bom nekaj primerov, ki pojasnjujejo, kako napisati formule za iskanje natančnih in približnih ujemanj.

VLOOKUP Primeri

Upam, da funkcija VPR ti bo malo bolj jasno. Zdaj pa si poglejmo nekaj primerov uporabe VPR v formulah z realnimi podatki.

Kako uporabljati VLOOKUP za iskanje v drugem Excelovem listu

V praksi formule s funkcijo VPR se redko uporabljajo za iskanje podatkov na istem delovnem listu. Pogosteje kot ne, boste iskali in pridobivali ustrezne vrednosti z drugega lista.

Za uporabo VPR, poiščite v drugem listu Microsoft Excel, morate v argumentu table_array (tabela) določite ime lista s klicajem, ki mu sledi obseg celic. Naslednja formula na primer kaže, da obseg A2: B15 je na listu z imenom Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Imena lista seveda ni treba vnesti ročno. Samo začnite vnašati formulo in ko pride do argumenta table_array (tabela), preklopite na želeni list in z miško izberite želeni obseg celic.

Formula, prikazana na spodnjem posnetku zaslona, ​​išče besedilo »Izdelek 1« v stolpcu A (to je 1. stolpec obsega A2:B9) na delovnem listu Cene.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Ne pozabite, da morate besedilno vrednost, ko iščete, dati v narekovaje (»«), kot je običajno v formulah Excel.

Za argument table_array (tabela) je zaželeno, da vedno uporabljate absolutne reference (z znakom $). V tem primeru bo obseg iskanja pri kopiranju formule v druge celice ostal nespremenjen.

Iščite v drugem delovnem zvezku z VLOOKUP

Za delovanje VPR delali med dvema delovnima zvezkoma Excel, morate pred imenom lista določiti ime delovnega zvezka v oglatih oklepajih.

Spodaj je na primer formula, ki išče vrednost 40 na listu Sheet2 v knjigi Številke.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Tukaj je najlažji način za ustvarjanje formule v Excelu z VPRki se povezuje z drugim delovnim zvezkom:

  1. Odpri obe knjigi. To ni obvezno, vendar je na ta način lažje ustvariti formulo. Ne želite ročno vnesti imena delovnega zvezka, kajne? Poleg tega vas bo zaščitil pred nenamernimi tipkarskimi napakami.
  2. Začnite vnašati funkcijo VPRin ko pride do argumenta table_array (tabela), preklopite na drug delovni zvezek in v njem izberite želeno območje iskanja.

Spodnji posnetek zaslona prikazuje formulo z iskanjem, nastavljenim na obseg v delovnem zvezku Cenik.xlsx na listu Cene.

funkcija VPR bo deloval tudi, ko zaprete iskalni delovni zvezek in se v vrstici s formulo prikaže celotna pot do datoteke delovnega zvezka, kot je prikazano spodaj:

Če ime delovnega zvezka ali lista vsebuje presledke, mora biti med apostrofi:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Kako uporabiti imenovan obseg ali tabelo v formulah z VLOOKUP

Če nameravate uporabiti isto območje iskanja v več funkcijah VPR, lahko ustvarite imenovan obseg in njegovo ime vnesete v formulo kot argument table_array (miza).

Če želite ustvariti imenovan obseg, preprosto izberite celice in vnesite ustrezno ime v polje Ime, levo od vrstice formule.

Zdaj lahko zapišete naslednjo formulo za iskanje cene izdelka Izdelek 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Večina imen obsegov deluje za celoten Excelov delovni zvezek, zato ni treba podati imena lista za argument table_array (tabela), tudi če sta formula in iskalni obseg na različnih delovnih listih. Če so v različnih delovnih zvezkih, morate pred imenom obsega določiti ime delovnega zvezka, na primer takole:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Tako je formula videti veliko bolj jasna, se strinjate? Poleg tega je uporaba imenovanih obsegov dobra alternativa absolutnim sklicem, ker se imenovani obseg ne spremeni, ko kopirate formulo v druge celice. To pomeni, da ste lahko prepričani, da bo obseg iskanja v formuli vedno ostal pravilen.

Če pretvorite obseg celic v popolno Excelovo preglednico z ukazom Tabela (Tabela) zavihek vstavljanje (Vstavi), potem ko z miško izberete obseg, bo Microsoft Excel formuli samodejno dodal imena stolpcev (ali ime tabele, če izberete celotno tabelo).

Končana formula bo videti nekako takole:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Ali morda celo takole:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Pri uporabi imenovanih obsegov bodo povezave kazale na iste celice, ne glede na to, kam kopirate funkcijo VPR znotraj delovnega zvezka.

Uporaba nadomestnih znakov v formulah VLOOKUP

Kot pri mnogih drugih funkcijah, VPR Uporabite lahko naslednje nadomestne znake:

  • Vprašaj (?) – nadomesti poljuben posamezen znak.
  • Zvezdica (*) – nadomesti poljubno zaporedje znakov.

Uporaba nadomestnih znakov v funkcijah VPR je lahko koristen v številnih primerih, na primer:

  • Ko se ne spomnite točno besedila, ki ga morate najti.
  • Ko želite najti neko besedo, ki je del vsebine celice. Vedeti to VPR išče po vsebini celice kot celote, kot da je možnost omogočena Ujema se s celotno vsebino celice (Celotna celica) v standardnem iskanju Excel.
  • Ko celica vsebuje dodatne presledke na začetku ali koncu vsebine. V takšni situaciji si lahko dolgo časa razbijate glavo in poskušate ugotoviti, zakaj formula ne deluje.

Primer 1: Iskanje besedila, ki se začne ali konča z določenimi znaki

Recimo, da želite poiskati določeno stranko v spodnji bazi podatkov. Njegovega priimka se ne spomnite, veste pa, da se začne na “ack”. Tukaj je formula, ki bo dobro opravila delo:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Zdaj, ko ste prepričani, da ste našli pravo ime, lahko uporabite isto formulo za iskanje zneska, ki ga je plačala ta stranka. Če želite to narediti, samo spremenite tretji argument funkcije VPR na želeno številko stolpca. V našem primeru je to stolpec C (3. v obsegu):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Tu je še nekaj primerov z nadomestnimi znaki:

~ Poiščite ime, ki se konča na "man":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Poiščite ime, ki se začne z "ad" in konča z "son":

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Na seznamu najdemo prvo ime, sestavljeno iz 5 znakov:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Za delovanje VPR z nadomestnimi znaki delovalo pravilno, kot četrti argument, ki ga morate vedno uporabiti FALSE (LAŽNO). Če obseg iskanja vsebuje več kot eno vrednost, ki se ujema z iskalnimi izrazi z nadomestnimi znaki, bo vrnjena prva najdena vrednost.

2. primer: združite nadomestne znake in sklice na celice v formulah VLOOKUP

Zdaj pa si poglejmo nekoliko bolj zapleten primer iskanja s funkcijo VPR po vrednosti v celici. Predstavljajte si, da je stolpec A seznam licenčnih ključev, stolpec B pa seznam imen, ki so lastniki licence. Poleg tega imate v celici C1 del (več znakov) neke vrste licenčnega ključa in želite najti ime lastnika.

To lahko storite z naslednjo formulo:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Ta formula poišče vrednost iz celice C1 v danem obsegu in vrne ustrezno vrednost iz stolpca B. Upoštevajte, da v prvem argumentu uporabimo znak & (&) pred in za sklicem na celico, da povežemo besedilni niz.

Kot lahko vidite na spodnji sliki, funkcija VPR vrne »Jeremy Hill«, ker njegov licenčni ključ vsebuje zaporedje znakov iz celice C1.

Upoštevajte, da argument table_array (tabela) na zgornjem posnetku zaslona vsebuje ime tabele (tabela7) namesto podajanja obsega celic. To smo naredili v prejšnjem primeru.

Natančno ali približno ujemanje v funkciji VLOOKUP

In končno, poglejmo si podrobneje zadnji argument, ki je določen za funkcijo VPR - iskanje_razpona (intervalni_pogled). Kot je bilo omenjeno na začetku lekcije, je ta argument zelo pomemben. V isti formuli z njeno vrednostjo lahko dobite popolnoma različne rezultate PRAVA KODA (TRUE) oz FALSE (LAŽNO).

Najprej ugotovimo, kaj Microsoft Excel pomeni natančna in približna ujemanja.

  • Če argument iskanje_razpona (iskanje_območja) je enako FALSE (FALSE), formula išče natančno ujemanje, tj. popolnoma enako vrednost, kot je navedena v argumentu lookup_value (iskana_vrednost). Če je v prvem stolpcu obsega tsposobna_matrika (tabela) naleti na dve ali več vrednosti, ki se ujemajo z argumentom lookup_value (search_value), potem bo izbrana prva. Če ne najde nobenega zadetka, bo funkcija javila napako #AT (#N/A). Naslednja formula bo na primer javila napako #AT (#N/A), če v območju A2:A15 ni vrednosti 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • Če argument iskanje_razpona (iskanje_območja) je enako PRAVA KODA (TRUE), formula išče približno ujemanje. Natančneje, najprej funkcija VPR išče natančno ujemanje in če ga ne najde, izbere približno. Približno ujemanje je največja vrednost, ki ne presega vrednosti, navedene v argumentu. lookup_value (iskana_vrednost).

Če argument iskanje_razpona (iskanje_območja) je enako PRAVA KODA (TRUE) ali ni določeno, potem morajo biti vrednosti v prvem stolpcu obsega razvrščene v naraščajočem vrstnem redu, to je od najmanjše do največje. Sicer pa funkcija VPR lahko vrne napačen rezultat.

Za boljše razumevanje pomena izbire PRAVA KODA (RESNICA) oz FALSE (FALSE), poglejmo še nekaj formul s funkcijo VPR in poglejte rezultate.

Primer 1: Iskanje natančnega ujemanja z VLOOKUP

Kot se spomnite, je za iskanje natančnega ujemanja četrti argument funkcije VPR bi moralo biti pomembno FALSE (LAŽNO).

Vrnimo se k tabeli iz prvega primera in ugotovimo, katera žival se lahko premika s hitrostjo 50 milj na uro. Verjamem, da vam ta formula ne bo povzročala težav:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Upoštevajte, da naš obseg iskanja (stolpec A) vsebuje dve vrednosti 50 – v celicah A5 и A6. Formula vrne vrednost iz celice B5. Zakaj? Ker pri iskanju natančnega ujemanja funkcija VPR uporabi prvo najdeno vrednost, ki se ujema z iskano.

Primer 2: Uporaba funkcije VLOOKUP za iskanje približnega ujemanja

Ko uporabljate funkcijo VPR za iskanje približnega ujemanja, tj. ko argument iskanje_razpona (iskanje_območja) je enako PRAVA KODA (TRUE) ali izpuščeno, je prva stvar, ki jo morate storiti, razvrstiti obseg po prvem stolpcu v naraščajočem vrstnem redu.

To je zelo pomembno, ker funkcija VPR vrne naslednjo največjo vrednost za dano, nato pa se iskanje ustavi. Če zanemarite pravilno razvrščanje, boste na koncu prejeli zelo čudne rezultate ali sporočilo o napaki. #AT (#N/A).

Zdaj lahko uporabite eno od naslednjih formul:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Kot lahko vidite, želim ugotoviti, katera od živali ima najbližjo hitrost 69 milj na uro. In tukaj je rezultat, ki mi ga je vrnila funkcija VPR:

Kot lahko vidite, je formula vrnila rezultat Antilopa (Antilopa), katere hitrost 61 milj na uro, čeprav seznam vključuje tudi Cheetah (Cheetah), ki hitro teče 70 milj na uro in 70 je bližje 69 kot 61, kajne? Zakaj se to dogaja? Ker funkcija VPR pri iskanju približnega ujemanja vrne največjo vrednost, ki ni večja od iskane.

Upam, da ti primeri nekoliko osvetlijo delo s funkcijo VPR v Excelu in nanjo ne gledaš več kot na tujca. Zdaj ne boli, če na kratko ponovimo ključne točke gradiva, ki smo ga preučevali, da bi ga bolje utrdili v spominu.

VLOOKUP v Excelu – to si morate zapomniti!

  1. funkcija VPR Excel ne more pogledati levo. Vedno išče vrednost v skrajno levem stolpcu obsega, ki ga poda argument table_array (miza).
  2. V funkciji VPR vse vrednosti so neobčutljive na velike in male črke, tj. male in velike črke so enakovredne.
  3. Če je vrednost, ki jo iščete, manjša od najmanjše vrednosti v prvem stolpcu obsega, ki ga iščete, funkcija VPR bo javil napako #AT (#N/A).
  4. Če je 3. argument col_index_num (številka_stolpca) manj kot 1funkcija VPR bo javil napako #VALUE! (#VREDNOST!). Če je večje od števila stolpcev v obsegu table_array (tabela), bo funkcija javila napako #REF! (#POVEZAVA!).
  5. V argumentu uporabite absolutne sklice na celice table_array (tabela), tako da se pri kopiranju formule ohrani pravilno območje iskanja. Namesto tega poskusite uporabiti imenovane obsege ali tabele v Excelu.
  6. Pri iskanju približnega ujemanja ne pozabite, da mora biti prvi stolpec v obsegu, ki ga iščete, razvrščen v naraščajočem vrstnem redu.
  7. Na koncu se spomnite pomembnosti četrtega argumenta. Uporabite vrednosti PRAVA KODA (RESNICA) oz FALSE (NEPRAV) namerno in znebili se boste številnih glavobolov.

V naslednjih člankih naše vadnice o funkcijah VPR v Excelu se bomo naučili naprednejših primerov, kot je izvajanje različnih izračunov z uporabo VPR, pridobivanje vrednosti iz več stolpcev in drugo. Hvala, ker ste prebrali to vadnico in upam, da se naslednji teden spet vidimo!

Pustite Odgovori