10 primerov VLOOKUP za začetnike in napredne uporabnike

Funkcija VLOOKUP je merilo uspešnosti.

Če znate uporabljati funkcijo VLOOKUP, veste nekaj o Excelu.

Če tega ne storite, je bolje, da Excela v svojem življenjepisu ne navedete kot eno od svojih prednosti.

Opravil sem skupinski razgovor in ko je kandidat omenil Excel kot svoje strokovno področje, je bila prva stvar, ki so jo vprašali – razumeli ste – funkcija VLOOKUP.

Zdaj, ko vemo, kako pomembna je ta Excelova funkcija, je smiselno, da jo v celoti razumemo, da lahko ponosno rečemo - "V Excelu poznam nekaj ali dve."

To bo ogromna vadnica VLOOKUP (po mojih standardih).

Pokril bom vse o tem, nato pa vam pokazal uporaben in praktičen primer VLOOKUP.

Kdaj uporabiti funkcijo VLOOKUP v Excelu?

Funkcija VLOOKUP najbolje deluje, ko iščete ujemajoče se podatkovne točke v stolpcu, in ko se najde ujemajoče se podatkovne točke, vzamete stolpec desno od vrstice in dobite vrednost iz določenega števila celic.

Vzemimo preprost primer, da razumemo, kdaj uporabiti Vlookup v Excelu.

Ne pozabite, ko so rezultati testov prišli in obtičali na oglasnih deskah, so vsi mrzlično iskali svoja imena in rezultate (vsaj tako se je veliko dogajalo, ko sem bil jaz v šoli).

Takole deluje:

• Pojdite na oglasno desko in začnete iskati svoje ime ali registracijsko številko (pomikajte prst navzgor in navzdol po seznamu).
• Ko najdete svoje ime, premaknete oči desno od imena/registrske številke, da vidite svoj rezultat.

Točno to naredi za vas funkcija Excel VLOOKUP (lahko uporabite ta primer v naslednjem intervjuju).

Funkcija VLOOKUP išče določeno vrednost v stolpcu (v zgornjem primeru je to vaše ime) in ko najde določeno ujemanje, vrne vrednost (žeton, ki ga dobite) v isti vrstici.

sintaksa

=VLOOKUP(vrednost_iskanja, niz_tabel, številka_indeks_stolpca, [iskanje_obsega])

Vhodni parametri

• iskalna_vrednost –To je iskalna vrednost, ki jo poskušate najti v skrajnem levem stolpcu tabele.Lahko je vrednost, sklic na celico ali besedilni niz.V primeru zapisnika bi bilo to vaše ime.
• niz_tabel –To je niz tabel, v katerem iščete vrednosti.To je lahko sklicevanje na obseg celic ali imenovani obseg.V primeru tabele rezultatov bi bila to celotna tabela, ki vsebuje ocene vseh za vsak predmet
• col_index –To je indeksna številka stolpca, iz katere želite dobiti ujemajoče se vrednosti.V primeru tabele z ulomki, če želite matematični ulomek (ki je prvi stolpec v tabeli, ki vsebuje ulomek), si lahko ogledate stolpec 1.Če želite fizični rezultat, si lahko ogledate stolpca 1 in 2.
• [razpon_iskanje] –Tukaj lahko določite, ali želite natančno ali približno ujemanje.Če je izpuščeno, je privzeto nastavljeno na TRUE - približno ujemanje(glejte dodatne opombe spodaj).

Dodatne opombe (dolgočasne, a pomembno vedeti)

• Ujemanja so lahko natančna (FALSE ali 0 v range_lookup) ali približna (TRUE ali 1).
• Pri približnem iskanju se prepričajte, da je seznam razvrščen v naraščajočem vrstnem redu (od zgoraj navzdol), sicer bodo rezultati morda netočni.
• Ko je range_lookup TRUE (približno iskanje) in so podatki razvrščeni v naraščajočem vrstnem redu:
  • Če funkcija VLOOKUP ne najde vrednosti, vrne največjo vrednost, manjšo od lookup_value.
  • Vrne napako #N/A, če je lookup_value manjša od minimalne vrednosti.
  • Če je lookup_value besedilo, lahko uporabite nadomestne znake (glejte spodnji primer).

Zdaj upajmo, da imate osnovno razumevanje, kaj lahko naredi funkcija VLOOKUP, olupimo čebulo in si oglejmo nekaj praktičnih primerov funkcije VLOOKUP.

10 primerov Excel VLOOKUP (osnovni in napredni)

Tukaj je 10 uporabnih primerov uporabe Excel Vlookup, ki vam pokažejo, kako ga uporabljati pri vsakodnevnem delu.

Primer 1 - Poiščite Bradov matematični rezultat

V spodnjem primeru VLOOKUP imam seznam z imeni študentov v skrajnem levem stolpcu in ocenami za različne predmete v stolpcih B do E.

Zdaj pa se lotimo dela in uporabimo funkcijo VLOOKUP, da naredimo tisto, kar je najbolje.Iz zgornjih podatkov moram vedeti, kaj je Brad dosegel pri matematiki.

Iz zgornjih podatkov moram vedeti, kaj je Brad dosegel pri matematiki.

Tukaj je formula VLOOKUP, ki vrne Bradovo matematično oceno:

=VLOOKUP("Brad",$A$3:$E$10,2,0)

Zgornja formula ima štiri parametre:

• "Brad": - To je iskalna vrednost.
• $A$3:$E$10 - To je obseg celic, ki ga gledamo.Ne pozabite, da Excel išče iskalne vrednosti v skrajnem levem stolpcu.V tem primeru išče ime Brad v A3:A10, ki je skrajni levi stolpec podane matrike.
• 2 – Ko funkcija najde Bradovo ime, gre v drugi stolpec matrike in vrne vrednost v isti vrstici kot Brad.Vrednost 2 tukaj pomeni, da iščemo ocene iz drugega stolpca podane matrike.
• 0 – To pove funkciji VLOOKUP, da išče samo natančna ujemanja.

Evo, kako deluje formula VLOOKUP v zgornjem primeru.

Najprej poišče vrednost Brad v skrajnem levem stolpcu.Išče vrednost v celici A6 od zgoraj navzdol.

Ko najde vrednost, se v drugem stolpcu premakne v desno in zgrabi vrednost v njem.

Uporabite lahko isto konstrukcijo formule, da dobite vsakogar oceno pri katerem koli predmetu.

Če želite na primer poiskati Marijin rezultat iz kemije, uporabite naslednjo formulo VLOOKUP:

=VLOOKUP("Maria",$A$3:$E$10,4,0)

V zgornjem primeru je iskalna vrednost (ime študenta) vnesena v dvojnih narekovajih.Uporabite lahko tudi reference celic, ki vsebujejo iskalne vrednosti.

Prednost uporabe referenc na celice je, da naredi formulo dinamično.

Na primer, če imate celico z imenom študenta in prejemate ocene iz matematike, se rezultati samodejno posodobijo, ko spremenite ime študenta (kot je prikazano spodaj):

Če vnesete iskalno vrednost, ki je ni mogoče najti v skrajnem levem stolpcu, se vrne napaka #N/A.

Primer 2 - Dvosmerno iskanje

V zgornjem primeru 1 smo trdo kodirali vrednosti stolpcev.Zato bo formula vedno vrnila rezultat matematike, ker uporabljamo 2 kot indeksno številko stolpca.

Kaj pa, če želite tako vrednost VLOOKUP kot številko indeksa stolpca narediti dinamično.Na primer, kot je prikazano spodaj, lahko spremenite ime študenta ali predmet in formula VLOOKUP bo dobila pravilen rezultat.Tukaj je primer dvosmerne formule VLOOKUP.

Tukaj je primer dvosmerne funkcije VLOOKUP.

Če želite narediti to dvosmerno formulo za iskanje, morate narediti tudi stolpec dinamičen.Torej, ko uporabnik spremeni temo, formula samodejno izbere pravilen stolpec (2 za matematiko, 3 za fiziko itd.).

Če želite to narediti, morate kot parameter stolpca uporabiti funkcijo MATCH.

Tukaj je formula VLOOKUP, ki bo naredila to:

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Zgornja formula uporablja MATCH(H3,$A$2:$E$2,0) kot številko stolpca.Funkcija MATCH vzame ime teme kot iskalno vrednost (v H3) in vrne njen položaj v A2:E2.Če torej uporabite matematiko, bo vrnil 2, ker je matematika najdena v B2 (ki je druga celica v določenem obsegu matrike).

Primer 3 - Uporaba spustnega menija kot iskane vrednosti

V zgornjem primeru moramo podatke vnesti ročno.To je lahko zamudno in nagnjeno k napakam, še posebej, če imate veliko vrednosti iskanja.

V tem primeru je dobra ideja ustvariti spustni seznam iskalnih vrednosti (v tem primeru je lahko ime študenta in predmet) in preprosto izberete s seznama.

Glede na izbiro bo formula samodejno posodobila rezultat.

Kot je prikazano spodaj:

To je odlična komponenta nadzorne plošče, ker imate lahko v ozadju ogromen nabor podatkov stotih učencev, vendar se lahko končni uporabniki (recimo učitelji) preprosto spustijo iz .

Kako to naredim:

Formula VLOOKUP, uporabljena v tem primeru, je enaka kot v primeru 2.

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Iskalne vrednosti so bile pretvorjene v spustne sezname.

Tu so koraki za ustvarjanje spustnega seznama:

• Izberite celico, ki potrebuje spustni seznam.V tem primeru v G4 potrebujemo ime študenta.
• Pojdite na Podatki -> Podatkovna orodja -> Preverjanje podatkov.
• Na zavihku Nastavitve pogovornega okna Preverjanje podatkov izberite Seznam na spustnem seznamu Dovoli.
• V viru izberite $A$3:$A$10
• Kliknite V redu.

Zdaj boste videli spustni seznam v celici G4.Prav tako ga lahko ustvarite v H3 za temo.

Primer 4 - Trosmerno iskanje

Kaj je trismerno iskanje?

V primeru 2 smo uporabili iskalno tabelo, ki vsebuje ocene za študente pri različnih predmetih.To je primer dvosmernega iskanja, ker uporabljamo dve spremenljivki za pridobivanje rezultatov (ime študenta in ime predmeta).

Zdaj, recimo, čez eno leto bo študent opravljal tri različne stopnje izpitov, enotne izpite, vmesne izpite in zaključne izpite (to je izpit iz mojih študentskih dni).

Trismerno iskanje bo lahko pridobilo oceno študenta za določen predmet iz določene izpitne stopnje.

Kot je prikazano spodaj:

V zgornjem primeru lahko funkcija VLOOKUP poišče in vrne ocene določenih študentov pri določenih predmetih v treh različnih tabelah (Enotni preizkusi, Vmesni in Finalni).

Tukaj je formula, uporabljena v celici H4:

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

Ta formula uporablja funkcijo IZBIRA, da zagotovi sklicevanje na pravilno tabelo.Analizirajmo IZBERI del formule:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

Prvi argument formule je IF(H2=”Test enote”,1,IF(H2=”Midterm”,2,3)), ki pregleda celico H2 in vidi referenčno testno raven.Če gre za test na enoto, se vrne $A$3:$E$7, ki vsebuje rezultat testa enote.Vrne $A$11:$E$15, če gre za vmesni izpit, v nasprotnem primeru vrne $A$19:$E$23.

S tem postane niz tabele VLOOKUP dinamičen, zaradi česar je trismerno iskanje.

Primer 5 - Pridobite zadnjo vrednost s seznama

Ustvarite lahko formulo VLOOKUP, da dobite zadnjo vrednost na seznamu.

Največje pozitivno število, ki ga lahko uporabite v Excelu, je 9.99999999999999 + 307 . To tudi pomeni, da je največja iskalna številka v številki VLOOKUP enaka.

Mislim, da nikoli ne boste potrebovali izračunov, ki vključujejo tako velika števila.Točno to lahko uporabimo, da dobimo zadnjo številko na seznamu.

Recimo, da imate nabor podatkov, kot je naslednji(v A1:A14), in želite dobiti zadnjo številko na seznamu.

Tukaj je formula, ki jo lahko uporabite:

=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE)

Upoštevajte, da zgornja formula uporablja približno ujemajoče se VLOOKUP  (Upoštevajte TRUE na koncu formule, ne FALSE ali 0).Upoštevajte tudi, da ta formula VLOOKUP deluje brez razvrščanja seznama.

Tukaj je opisano, kako deluje približna funkcija VLOOKUP.Skenira skrajni levi stolpec od zgoraj navzdol.

• Ta vrednost se vrne, če se najde natančno ujemanje.
• Če najde vrednost, višjo od iskalne vrednosti, vrne vrednost v celici nad njo.
• Če je iskana vrednost večja od vseh vrednosti na seznamu, se vrne zadnja vrednost.

V zgornjem primeru je na delu tretji primer.

zaradi9.99999999999999 + 307je največje število, ki ga je mogoče uporabiti v Excelu, zato, ko se uporablja kot iskalna vrednost, vrne zadnjo številko na seznamu.

Prav tako ga lahko uporabite tudi za vrnitev zadnjega besedilnega elementa na seznamu.Tukaj je formula, ki lahko to naredi:

=VLOOKUP("zzz",$A$1:$A$8,1, TRUE )

Sledite isti logiki.Excel pregleda vsa imena in ker se zzz šteje za večje od katerega koli imena/besedila, ki se začne s črko pred zzz, bo vrnil zadnji element na seznamu.

Primer 6 - Delno iskanje z nadomestnimi znaki in VLOOKUP

Excelovi nadomestni znaki so uporabni v mnogih situacijah.

Prav ta čarobni napoj daje vašemu receptu supermoči.

Delno iskanje je potrebno, ko morate poiskati vrednost na seznamu in ni natančnega ujemanja.

Recimo, da imate nabor podatkov, kot je ta, in želite na seznamu najti podjetje ABC, vendar ima seznam ABC Ltd namesto ABC.

ABC ne morete uporabiti kot iskalno vrednost, ker v stolpcu A ni natančnega ujemanja.Približna ujemanja lahko vodijo tudi do napačnih rezultatov, seznam pa je treba razvrstiti v naraščajočem vrstnem redu.

Vendar pa lahko uporabite nadomestne znake v funkciji VLOOKUP, da dobite ujemanja.

V celico D2 vnesite naslednjo formulo in jo povlecite v druge celice:

=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,FALSE)

Kako deluje ta formula?

V zgornji formuli je namesto uporabe iskalne vrednosti, kakršna je, obdana z nadomestnimi zvezdicami (*) - "*"&C2&"*"

Zvezdice so nadomestni znaki v Excelu, ki lahko predstavljajo poljubno število znakov.

Z zvezdicami okoli iskalne vrednosti povejte Excelu, da mora poiskati katero koli besedilo, ki vsebuje besedo v C2.Pred ali za besedilom v C2 ima lahko poljubno število znakov.

Na primer, celica C2 vsebuje ABC, zato funkcija VLOOKUP pogleda imena v A2:A8 in išče ABC.V celici A2 najde ujemanje, ker vsebuje ABC od ABC Ltd. Ni pomembno, ali so znaki levo ali desno od ABC.Šteje se za ujemanje, dokler v besedilnem nizu ni ABC.

Opomba: funkcija VLOOKUP vedno vrne prvo ujemajočo se vrednost in ustavi nadaljnja iskanja.Torej, če je na seznamu ABC Ltdin ABC Corporation, bo vrnil prvo in prezrl ostalo.

Primer 7 - VLOOKUP vrne napako kljub ujemanju vrednosti iskanja

Obnorelo vas bo, ko boste videli, da obstaja ustrezna vrednost iskanja in funkcija VLOOKUP vrne napako.

Na primer, v spodnjem primeru je ujemanje (Matt), vendar funkcija VLOOKUP še vedno vrne napako.

Medtem ko lahko vidimo, da obstaja ujemanje, s prostim očesom ne moremo videti, da so morda vodilni ali zadnji presledki.Če imate te dodatne presledke pred, za ali med iskalno vrednostjo, se ne ujemajo natančno.

To se običajno zgodi, ko uvozite podatke iz baze podatkov ali pridobite podatke od nekoga drugega.Ti vodilni/končni prostori se nagibajo k širjenju.

Rešitev tukaj je funkcija TRIM.Odstrani vse začetne ali končne presledke ali dodatne presledke med besedami.

Tukaj je formula, ki vam bo dala pravilen rezultat.

=VLOOKUP("Matt", TRIM($A$2:$A$9),1,0)

Ker je to formula matrike, namesto Enter uporabite Control+Shift+Enter.

Drug pristop bi lahko bil, da najprej obdelate iskalno matriko s funkcijo TRIM, da zagotovite, da so vsi dodatni presledki izginili, nato pa uporabite funkcijo VLOOKUP kot običajno.

Primer 8 - Izvajanje iskanja, ki razlikuje velike in male črke

Vrednosti iskanja v funkciji VLOOKUP privzeto niso občutljive na velike in male črke.Na primer, če je vaša iskalna vrednost MATT, matt ali Matt, je za funkcijo VLOOKUP vse enako.Vrne prvo ujemajočo se vrednost ne glede na velikost črke.

Če pa želite iskanje glede na velike in male črke, morate uporabiti funkcijo EXACT in funkcijo VLOOKUP.

Tukaj je primer:

Kot lahko vidite, imajo tri celice isto ime (v A2, A4 in A5), vendar z različnimi velikimi črkami.Na desni strani imamo tri imena (Matt, MATT in matt) in njihove rezultate pri matematiki.

Funkcija VLOOKUP trenutno ni opremljena za obdelavo vrednosti iskanja, občutljivih na velike in male črke.V zgornjem primeru vedno vrne 38, kar je Mattov rezultat v A2.

Za razlikovanje med velikimi in malimi črkami moramo uporabiti stolpec pomoči (kot je prikazano spodaj):

Če želite dobiti vrednost v stolpcu pomoči, uporabite funkcijo =ROW().Dobil bo samo številko vrstice v celici.

Ko imate stolpec za pomočnike, je tukaj formula, ki daje rezultat iskanja, ki razlikuje velike in male črke.

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

Zdaj ga razčlenimo in razumemo, kaj počne:

• EXACT(E2,$A$2:$A$9) – Ta del primerja vrednost iskanja v E2 z vsemi vrednostmi v A2:A9.Vrne matriko TRUE/FALSE, kjer se v primeru natančnega ujemanja vrne TRUE.V tem primeru bo vrnil naslednjo matriko: {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9) - Ta del pomnoži niz TRUE/FALSE s številko vrstice. Dokler je TRUE, bo dal številko vrstice , sicer daje 0. V tem primeru bo vrnil {2;0;0;0;0;0;0;0}.
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。V tem primeru bo vrnil 2 (kar je natančna številka vrstice).
• Zdaj samo to številko uporabimo kot iskalno vrednost in uporabimo iskalno matriko kot B2:C9

Opomba: ker je to formula matrike, namesto samo tipkanja uporabite Control + Shift + Enter.

Primer 9 - Uporaba VLOOKUP z več pogoji

Excelova funkcija VLOOKUP v svoji osnovni obliki poišče iskalno vrednost in vrne ustrezno vrednost iz podane vrstice.

Toda običajno je VLOOKUP potreben v Excelu z več merili.

Recimo, da imate podatke, ki vsebujejo imena študentov, vrste testov in matematične rezultate (prikazano spodaj):

Uporaba funkcije VLOOKUP za pridobitev matematičnih rezultatov vsakega študenta na njihovi ravni preizkusa je lahko izziv.

Če na primer poskusite uporabiti VLOOKUP z Matt kot iskalno vrednostjo, bo vedno vrnil 91, rezultat za prvo pojavljanje Matt na seznamu.Če želite dobiti oceno Matt za vsako vrsto izpita (enota, vmesni in končni), morate ustvariti edinstveno iskalno vrednost.

To lahko storite s pomožnimi stolpci.Prvi korak je, da vstavite pomožni stolpec levo od ulomka.

Zdaj, da ustvarite edinstven kvalifikator za vsak primerek imena, uporabite naslednjo formulo v C2: =A2&”|”&B2

Kopirajte to formulo v vse celice v pomožnem stolpcu.To bo ustvarilo edinstveno iskalno vrednost za vsak primerek imena (kot je prikazano spodaj):

Zdaj, ko obstajajo podvojena imena, ko so imena združena z izpitnimi ravnmi, ni dvojnikov.

To je preprosto, saj lahko zdaj uporabite vrednost stolpca pomočnika kot vrednost za iskanje.

To je formula, ki vam daje rezultat v G3:I8.

=VLOOKUP($F3&"|"&G$2,$C$2:$D$19,2,0)

Tukaj združimo ime študenta in raven izpita, da dobimo iskano vrednost, to iskalno vrednost uporabimo za preverjanje ujemajočih se zapisov v stolpcu pomoči.

Opomba: V zgornjem primeru smo uporabili | Uporablja se kot ločilo pri dodajanju besedila v pomožne stolpce.V nekaterih zelo redkih (vendar možnih) primerih imate morda dva različna merila, vendar bo njihova kombinacija na koncu dala enak rezultat.Tukaj je primer:

Upoštevajte, da čeprav sta A2 in A3 različna ter B2 in B3 različna, je kombinacija na koncu enaka.Če pa uporabite ločila, bo celo kombinacija drugačna (D2 in D3).

Tukaj je vadnica o tem, kako uporabljati VLOOKUP z več pogoji brez uporabe pomožnih stolpcev.

Primer 10 - Obravnava napak pri uporabi funkcije VLOOKUP

Excelova funkcija VLOOKUP vrne napako, ko podane vrednosti iskanja ni mogoče najti.Če VLOOKUP ne najde vrednosti, verjetno ne želite, da bi grde vrednosti napak motile estetiko vaših podatkov.

Z lahkoto lahko odstranite vrednosti napak s celotnim besedilom katerega koli pomena, na primer "Ni na voljo" ali "Ni najdeno".

Na primer, v spodnjem primeru, ko poskušate najti Bradovo oceno na seznamu, vrne napako, ker Bradovega imena ni na seznamu.

Če želite odstraniti to napako in jo nadomestiti z nečim smiselnim, ovijte funkcijo VLOOKUP v funkcijo IFERROR.

Tukaj je formula:

=ČE NAPAKA(VLOOKUP(D2,$A$2:$B$7,2,0),"Ni najdeno")

Funkcija IFERROR preveri, ali je vrednost, ki jo vrne prvi argument (v tem primeru funkcija VLOOKUP), napaka.Če ne gre za napako, vrnite vrednost prek funkcije VLOOKUP, sicer pa vrnite Not Found.

Funkcija IFERROR je na voljo od Excela 2007.Če uporabljate prejšnjo različico, uporabite naslednje funkcije:

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

To je vse za to vadnico VLOOKUP.

Poskušam predstaviti glavni primer uporabe funkcije Vlookup v Excelu.Sporočite mi v razdelku za komentarje, če želite na ta seznam dodati več primerov.

Povezane funkcije Excela:

• Excelova funkcija HLOOKUP.
• Excelova funkcija XLOOKUP
• Indeksna funkcija Excel.
• Posredno funkcijo Excel.
• Funkcija ujemanja v Excelu.
• Excelova funkcija odmika.