Uvod v funkcijo VLOOKUP
Funkcija VLOOKUP v Excelu je brez dvoma najbolj uporabljena in govorjena funkcija. Na vsakem razgovoru za službo, ki zahteva znanje Excel, je to najbolj zastavljeno vprašanje, če veste, kako uporabljati funkcijo VLOOKUP.
Funkcija VLOOKUP spada v kategorijo iskanja funkcij excel. V v VLOOKUP pomeni navpično. Funkcija se uporablja za iskanje podatkov, ki so strukturirani navpično. Za vodoravno iskanje obstaja funkcija, imenovana HLOOKUP.
Excel VLOOKUP funkcija poišče prvo ujemanje dane vrednosti, imenovane iskalna vrednost, v podatkovni tabeli in vrne vrednost iz danega indeksa stolpca. Ujemanje je lahko približno ali natančno.
Čeprav je VLOOKUP enostaven za uporabo, ima svoje omejitve in ozadja. Pogovarjali se bomo o vsem, vključno z močmi in slabostmi funkcije.
Sintaksa:
= VLOOKUP (iskalna_vrednost, matrika_tablica, številka_indeksa_številka, [obseg_ogled])
Kaj je iskalna vrednost
Lookup_value: Njegova vrednost, ki jo želite poiskati v matriki tabel.
Na primer, če iščete zvitek št. 110 v študentski mizi potem njegova 110 je iskalna vrednost.
Matrika_maza: Tabela, iz katere želite poiskati iskalno vrednost.
Na primer, če iščete 110 v matriki tabel B5: E17. Potem B5: E17 je vaša matrika tabel.
col_index_number: Številka stolpca v matriki tabel, iz katere želite pridobiti rezultate.
Na primer, če v matriki tabel B5: E17 želite dobiti vrednost iz stolpca D potem bo indeks stolpca 3 (štetje od B do D (B, C, D)).
[range_lookup]: FALSE, če želite iskati natančno vrednost, TRUE, če želite približno ujemanje.
Kako uporabljati funkcijo VLOOKUP
Če imate strukturirane podatke in želite poiskati podatke v teh podatkih, je rešitev VLOOKUP rešitev.
Načini ujemanja VLOOKUP
VLOOKUP ima dva načina ujemanja, približno in natančno ujemanje. VLOOKUP privzeto izvede približno ujemanje. Če pa želite prisiliti funkcijo VLOOKUP, da izvede natančno ujemanje, lahko to storite tudi vi. V večini primerov jaz in jaz močno verjamem, da tudi drugi poskušamo narediti natančno ujemanje s funkcijo VLOOKUP. Ne razumem, zakaj razvijalci Excela menijo, da bi uporabniki radi uporabljali približno približno ujemanje.
1. VLOOKUP Približno ujemanje
= VLOOKUP (iskalna_vrednost, matrika_masov, stolpec_indeksa_številka, 1)
V približnem ujemanju VLOOKUP, če vrednost ni najdena, potem zadnja vrednost, ki je manjša od iskalne vrednosti, se ujema in vrne vrednost iz podanega indeksa stolpca.
VLOOKUP se privzeto približuje, če izpustimo spremenljivko za iskanje obsega. Približno ujemanje je uporabno, če želite narediti približno ujemanje in ko razvrstite matriko po naraščajočem vrstnem redu.
Vlookup išče vrednost in če ne najde vrednosti v matriki tabel, se potem ujema z vrednostjo, ki je manjša od te vrednosti v matriki tabel. Naj to razumemo s primerom.
Primer 1
Tukaj imamo seznam študentov. Vsak učenec je na testu dosegel nekaj točk. Zdaj želim dati oceno glede na njihove ocene.
Vsak učenec, ki je dosegel manj kot 40, mora biti označen s F, študent, ki je dosegel med 40 in 60, mora biti označen s C itd., Kot je prikazano na spodnji sliki.
To formulo VLOOKUP bi zapisali v E2 in jo povlekli navzdol.
= VLOOKUP (D2, $ H $ 2: $ I $ 6,2, TRAJNO)
Kako deluje?
V zgornjem primeru so naše iskalne vrednosti v stolpcu D, začenši z D2. Niz tabel je H2: I6 (upoštevajte, da je razvrščen v naraščajočem vrstnem redu in absolutno). Stolpec, iz katerega pridobivamo podatke, je 2. In tip ujemanja smo določili približno tako, da podamo TRUE (nič in 1 ne bosta pomenila enako).
Zato preučimo vrednost iskanja D2, ki vsebuje 40.
- VLOOKUP išče 40 v prvem stolpcu (H) matrike tabel H2: I6.
- Na celici H3 najde 40 na drugem mestu.
- Zdaj se premakne v drugi stolpec od H3 do I3 in vrne D.
V tem primeru je vlookup našel natančno ujemanje. Poglejmo naslednji primer.
Druga iskalna vrednost D3, ki vsebuje 36.
- VLOOKUP išče 36 v prvem stolpcu matrike tabel H2: I6.
- VLOOKUP v prvem stolpcu nikjer ni mogel najti 36.
- Ker VLOOKUP ni mogel najti 36, se ujema z zadnjo najdeno vrednostjo, ki je manjša od iskalne vrednosti.
- Prva vrednost, ki je manjša od 36, je 0, zato vrne F, ki je povezano z 0.
Tu se to zgodi za vsak primer posebej. Za 92 je zadnja vrednost, ki je manjša od 92, 90. Zato se A vrne za približno ujemanje.
2. VLOOKUP Natančno ujemanje
Ta najpogosteje uporabljena oblika VLOOKUP -a in je lahko tudi najbolj uporabna. Ko želite poiskati natančno vrednost v prvem stolpcu matrike tabel, uporabite natančno ujemanje. Na primer, če iščete ID iz obrazca, bi si želeli natančno ujemanje namesto vrednosti, ki je manjša od te vrednosti.
Če želite VLOOKUP prisiliti v natančno ujemanje v Excelu, podamo 0 ali FALSE kot parameter range_lookup.
= VLOOKUP (iskalna_vrednost, matrika_masov, stolpec_indeksa_številka, 0)
Če vrednosti ne najdemo v prvem stolpcu matrike tabel, bo formula vrnila napako #N/A.
Poglejmo primer.
Primer 2
V tem primeru želim napisati samo zvitek študenta v A2, v B2 pa želim pridobiti ime študenta in v C2 njegov Ocena. Enostavno. Niz tabel je B5: D17. Zakaj?
Če želite to narediti, zapišite ti dve formuli v celico v celici B2 oziroma C2.
= VLOOKUP (A2, $ B $ 5: $ E $ 17,2,0)
= VLOOKUP (A2, $ B $ 5: $ E $ 17,3,0)
Ne glede na ID, ki ga boste zapisali v celico A2, bo funkcija VLOOKUP iskala natančno ujemanje v stolpcu B in vrnila vrednost iz danega col_index.
Kako deluje VLOOKUP z natančnim ujemanjem?
Natančno ujemanje VLOOKUP v Excelu je preprosto. Išče iskalno vrednost v prvem stolpcu danega matrike_tabel in vrne vrednost iz vrednosti iz dane številke stolpca_indeksa. Če vrednosti ni mogoče najti, VLOOKUP vrne napako #N/A.
Najboljša uporaba funkcije VLOOKUP
Vlookup ima veliko uporab. Razpravljal bom o nekaterih najbolj uporabnih primerih uporabe.
3. Namesto zapletene ugnezdene funkcije IF uporabite Excel VLOOKUP za razvrščanje
Za zamenjavo ugnezdene funkcije IF uporabite VLOOKUP približno ujemanje. V primeru 1 smo videli, kako lahko uporabimo vlookup za ocenjevanje učencev glede na njihove ocene.
Približno ujemanje VLOOKUP je hitrejše kot ugnezdeno, če od znotraj uporablja binarno iskanje.
4. Uporabite VLOOKUP za pridobivanje podatkov
Ta najpogostejša naloga, ki jo je mogoče enostavno opraviti s funkcijo VLOOKUP v Excelu. V primeru 2 smo to storili za pridobivanje podatkov študentov z uporabo njihovega zneska št.
5. S pomočjo Vlookupa preverite, ali je vrednost v zapisu.
Z VLOOKUP lahko preverite, ali je vrednost na kakšnem seznamu ali ne.
V primeru 2, ko zapišemo 152, vrne #N/A napako. Če VLOOKUP vrne #N/A, to pomeni, da v danih podatkih ni nikjer našel dane vrednosti.
Pripravil sem kratek članek o tem, ki ga lahko preberete tukaj.
S formulo VLOOKUP preverite, ali vrednost obstaja
Samodejno pridobivanje indeksa stolpca
Spadam v tisto skupino ljudi, ki ne želijo vedno znova ponavljati iste naloge. Zato me spreminjanje številke col_index vedno znova moti in tega se vedno izogibam. Pravzaprav se izogibam kakršnemu koli urejanju mojih formul, ko jih napišem, razen če in dokler ni druge možnosti.
Indeks stolpca se lahko samodejno izračuna v Excelu. Obstaja več načinov za to. Poglejmo jih nekaj tukaj.
6. Uporabite funkcijo VLOOKUP s funkcijo MATCH
Torej, kot vemo, funkcija MATCH vrne najdeni indeks ujemajoče se vrednosti.
Torej, če imamo na iskalnem mestu popolnoma enake naslove kot izvorni podatki, jih lahko uporabimo za pridobitev col_index. Kako? Poglejmo…
V spodnje podatke želimo samo zapisati številko zvitka v G2 in želimo pridobiti ime študenta, ocene in oceno v H2, ne glede na to, kateri naslov tam zapišemo.
To formulo zapišite v celico H2
= VLOOKUP (G2, B2: E14, MATCH (H1, B1: E1,0), 0)
Zdaj, ko spremenite naslov v H1, bo ta vrednost v H2 prikazana iz tega stolpca.
S pomočjo spustnega menija lahko uporabite potrditev podatkov, ki vsebuje vse naslove iz tabele, da bo uporabniku prijaznejša.
Tukaj se VLOOKUP popolnoma ujema. Zdaj VLOOKUP počne svoje običajne stvari in vleče podatke. Čarovnijo izvaja funkcija MATCH na položaju col_index.
Tu VLOOKUP vidi katero koli vrednost v G2 v prvem stolpcu matrike tabel B2: E14. Zdaj imamo na mestu col_index UJEMA (H1, B1: E1,0).
Funkcija MATCH išče poljubno vrednost v razponu H1 B1: E1 in vrne indeks vrednosti ujemanja.
Na primer, ko tipkamo študent v H1 ga išče v dosegu B1: E1. Najdemo ga v C2. zato vrne 2. Če vtipkamo Ocena vrnil bo oceno študenta.
Upoštevajte, da ko vnesemo Region, vrne #N/A. Ker je izven obsega tabele. O tem bomo govorili na koncu v članku.
To je najboljši način za samodejni VLOOKUP. Obstajajo še druge metode, vendar niso tako prilagodljive.
7. Uporabite funkcijo VLOOKUP s funkcijo COLUMN
Funkcija stolpca vrne podano številko stolpca. In kaj potrebujemo za pridobivanje podatkov z uporabo VLOOKUP? Col_index. S funkcijo COLUMN lahko torej dobimo podatke iz poljubnega niza podatkov.
Vzemimo zgornji primer. Zdaj pa moramo pridobiti celotne podatke. Ne samo en stolpec.
To formulo zapišite v H2 in kopirajte v I2 in J2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, STOLPEC (B1), 0)
Tako boste dobili dinamičen rezultat. Kako? Pa poglejmo.
VLOOKUP deluje kot običajno. Na column_index smo pisali STOLPEK (B1), kar bo prevedeno v 2, saj imamo ime študenta v stolpcu 2 iz stolpca B, vrne ime študenta.
Pomembno: To deluje, ker imamo tabelo iz stolpca B. Če je vaša miza na sredini lista, boste morali odšteti ali dodati nekaj številk, da ustrezajo vašim zahtevam.
Če bi na primer zgornja tabela začela s C1 v isti strukturi, bi vse spodnje formule delovale dobro.
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, STOLPEC (B1), 0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, STOLPEC (C1) -1,0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, STOLPEC (A1) +1,0)
8. VLOOKUP za združevanje podatkov
V večini baz podatkov obstaja ena poizvedba, ki se združi s tabelami, večinoma imenovana poizvedba za pridružitev, vendar MS Excel ni v nobeni zbirki podatkov, zato nima poizvedbe za pridružitev. Toda to ne pomeni, da se ne moremo združiti v dve tabeli, če imamo v dveh tabelah nekaj skupnih stolpcev.
Ko torej dobite nekaj podatkov iz dveh oddelkov istega podjetja, bi jih radi združili, da bi te podatke analizirali.
Na primer, ko od učiteljev dobite ocene študentov in nato iz upravnega oddelka zapis o prisotnosti, bi radi na enem mestu videli, kateri študent je dobil kakšno oceno in kakšen je njegov odstotek obiskanosti.
Moramo jih združiti v spodnjo tabelo.
Oznake, ki jih moramo dobiti iz tabele oznak, in prisotnost iz podatkov o prisotnosti.
Spodaj zapišite formule v celico D19, da dobite oznake in povlecite navzdol.
= VLOOKUP (B19, $ B $ 2: $ D $ 15,3,0)
Spodaj vnesite formulo v celico E19
= VLOOKUP (B19, $ G $ 3: $ I $ 15,3,0)
Dve tabeli smo združili v eno. Seveda lahko dodate več kot dve mizi. Z VLOOKUP -om morate na enem mestu dobiti podatke iz vseh želenih tabel.
Najboljše prakse VLOOKUP -a
Torej, vsi zgornji primeri smo pravkar uporabili vlookup s surovimi podatki. V vseh zgornjih primerih smo bili previdni pri podatkih, ki smo jih uporabljali. Morali smo biti previdni pri podajanju referenc tabel. Vendar obstajajo nekateri načini, ki lahko ta napor močno zmanjšajo.
9. Uporabite Absolute Reference z VLOOKUP
Morda ste opazili, da sem v nekaterih člankih uporabljal absolutne obsege (znak $ range). No, absolutni bes se uporablja, kadar ne želimo, da se obseg spreminja med kopiranjem celice formule v druge celice.
Na primer, če imam = VLOOKUP (G2,B2: E14, 2,0), 0) v celici H2 in če jo kopiram ali povlečem navzdol v celico H3, se bo formula spremenila v = VLOOKUP (G3,B3: E15, 2,0). Vse reference so relativno spremenjene. Tu bomo morda želeli spremeniti sklic na iskalno vrednost, ne pa tudi na matriko tabel. Ker B2 pride ven ali dosega in ga morda želimo imeti vedno v tabelarnih matrikah.
Torej, da preprečimo, da uporabljamo absolutne reference. Te obsege naredimo absolutne, ki jih ne želimo spremeniti, na primer table_array v H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). Ko kopirate H2 v H3 bo formula = VLOOKUP (G3,$ B $ 2: $ E $ 14, 2,0), 0). Upoštevajte, da se vrednost iskanja spremeni, ne pa tudi matrika_tabel.
10. Uporabite imenovane obsege z VLOOKUP -om
V zgornjem primeru smo uporabili absolutno sklicevanje za popravljanje matrike table_array. Izgleda precej nejasno in težko berljivo. Kaj pa, če lahko samo pišeš "Podatki" na mestu $ B $ 2: $ E $ 14 in bi se skliceval na $ B $ 2: $ E $ 14 nenehno. Tako bo branje in pisanje formule še lažje.
Zato samo izberite obseg in ga poimenujte "podatki".
Če želite poimenovati obseg, izberite obseg. Z desno tipko miške kliknite Define Name in poimenujte obseg, ki ga želite. Zdaj samo = VLOOKUP (G3,podatkov,2,0), 0) formula bo delovala popolnoma enako kot prej.
Več o poimenovanih obsegih lahko preberete v Excelu. Tu sem razložil vsak vidik imenovanega obsega.
11. Nadomestni znaki za delno ujemanje z uporabo VLOOKUP
VLOOKUP omogoča delno ujemanje. Da, pri natančnem ujemanju VLOOKUP -a lahko za delno ujemanje uporabite operatorje nadomestnih znakov. Na primer, če želite poiskati vrednost, ki se začne z "Gil", lahko uporabite operator nadomestnih znakov * z "Gil" kot "Gil *". Poglejmo primer.
Tukaj želim dobiti ocene študenta, katerega ime se začne z Gil. Če želite to narediti, bom spodaj zapisal formulo.
= VLOOKUP ("*škrge", C2: D14,2,0)
Za uporabo s funkcijo VLOOKUP sta v Excelu na voljo dva nadomestna znaka.
Zvezdica (*) nadomestni znak. To se uporablja, če uporabnik ne pozna števila znakov in pozna le nekatere znake ujemanja. Na primer, če uporabnik pozna ime, ki se začne z "Sm", bo napisal "Sm*". Če uporabnik ve, da je iskalna vrednost, ki se konča z "123", bo napisal "*123". (nadomestni znak deluje samo z besedili). In ve, da "se" pride v besedilo, potem bo napisal "*se*".
Vprašaj ("?"). To se uporabi, ko uporabnik ve, koliko manjka znakov. Na primer, če želim poiskati vrednost, ki vsebuje 4 znake, vendar ne vem, kaj so, bom preprosto napisal "????" na mestu iskalne vrednosti.
Vlookup vrne ustrezno vrednost prve najdene vrednosti, ki vsebuje točno štiri znake.
Pomembno: Nadomestni znak deluje samo z besedilnimi vrednostmi. Pretvorite številke v besedilo, če želite z njimi uporabiti nadomestne znake.
Slabosti funkcije VLOOKUP
VLOOKUP je res zmogljiva in enostavna funkcija za pridobivanje vrednosti, vendar obstaja veliko področij, kjer VLOOKUP ni tako koristen. Če delate na Excelu, jih morate poznati tudi in kako narediti nalogo, ki je VLOOKUP ne zmore.
12. Vrednosti ni mogoče pridobiti z leve strani matrike_tabel
Največja napaka funkcije VLOOKUP je, da ne more pridobiti vrednosti iz leve strani matrike tabel. VLOOKUP išče le desno od iskalne vrednosti.
To je zato, ker VLOOKUP išče dano iskalna vrednost v prvem stolpcu danega namizni niz. Nikoli ne bo vrnil vrednosti zunaj tabele. In če poskusite obdržati levi stolpec v matriki tabel, postane prvi stolpec matrike tabel, zato bo iskalna vrednost iskana v tem obsegu. Kar bo verjetno povzročilo napako.
Za iskanje vrednosti iz leve strani iskalne vrednosti namesto tega uporabimo INDEX-MATCH. Funkcija INDEX-MATCH lahko poišče vrednosti iz katerega koli stolpca na listu. Pravzaprav sta obseg iskanja in obseg, iz katerega želite poiskati vrednosti, popolnoma neodvisna, zaradi česar je ujemanje indeksa zelo prilagodljivo.
VLOOKUP vrne #N/A, tudi če obstaja vrednost iskanja.
#N/N Funkcija VLOOKUP vrne napako, ko iskalne vrednosti ni mogoče najti. Morda se vam zdi moteče, če vrednost obstaja v razponu, vendar VLOOKUP vrne napako #N/A.
14. Ko so številke oblikovane kot besedilo
To se večinoma zgodi, ko so številke oblikovane kot besedilo. Ko ugotovite, da z uporabo CTRL+F ukaz Excel najde, ko pa poskusite uporabiti VLOOKUP, vrne #N/A. Ta naloga je v večini intervjujev podana kot trik.
Na zgornji sliki lahko vidite, da je 101 ravno tam, vendar formula vrača napako. Kako, vi to obvladate? No, obstajata dve metodi.
1. Pretvorite besedilo v številko v podatkih
To zeleno oznako lahko vidite v zgornjem levem kotu v stolpcu rollno. Kažejo, da je vrednost celice nekaj nenavadnega. Ko izberete celico, bo prikazano, da je številka oblikovana kot besedilo.
Ko kliknete na klicaj (!), Se vam prikaže možnost Pretvori v številko. Izberite vse celice in kliknite to možnost. Vse vrednosti bodo pretvorjene v besedilo.
2. Pretvorite številko iskanja v besedilo v formuli
V zgornjem primeru smo mutirali izvirne podatke. V izvirnih podatkih morda ne boste želeli ničesar spremeniti. Namesto tega bi to želeli narediti v formuli. Če želite spremeniti številko v besedilo v formuli VLOOKUP, napišite to.
= VLOOKUP (BESEDILO (G2, "0"), B2: D14,2,0)
Tu formula dinamično spremeni število v besedilo in ga nato ujema z danim obsegom.
15. Besedilo z vodilnimi in zadnjimi presledki
Nekateri podatki, ki jih posredujejo operaterji za vnos podatkov, in podatki iz interneta niso čisti. Morda imajo zaključne presledke ali nekaj natisljivih znakov. Med iskanjem po teh vrednostih boste morda videli napako #N/A. Da bi se temu izognili, najprej očistite svoje podatke. Za odstranitev vodilnih presledkov uporabite funkcijo TRIM. TRIM odstrani poljubno število začetnih ali končnih presledkov iz besedila.
Zato predlagam, da dodate nov stolpec in tam prenesete očiščene podatke. Nato vrednost prilepite. Zdaj, če želite, se lahko znebite prvotnega stolpca.
= TRIM (ČISTO (besedilo))
Odpravljanje napak programa VLOOKUP
Kot veste, VLOOKUP ne najde vrednosti, vrne napako #N/A. No, v redu je, če dobite napako #N/A, morda včasih to nameravate dobiti, na primer, če želite preveriti, ali vrednost že obstaja na seznamu ali ne. Tam #N/A pomeni, da vrednosti ni.
Toda #N/A izgleda grdo. Kaj pa pridobivanje uporabniku prijaznega izhoda, na primer »ID ni najden« ali »Stranka ni najdena«. Če želite preprečiti #N/A, lahko uporabimo IFERROR s funkcijo VLOOKUP.
Uporabite spodnjo formulo, da ujamete napako v VLOOKUP.
= IFERROR (VLOOKUP (150, B2: E14,2,0), "Roll no not found")
16. VLOOKUP z VEČ MERI
VLOOKUP ne more delovati z več merili.Da, lahko uporabite samo eno merilo za iskanje vrednosti s formulo VLOOKUP.
Na primer, če imate ime in priimek v dveh ločenih stolpcih.
In če želite poiskati vrednost, katere ime je John in priimek Dave, tega ne morete storiti normalno.
Toda za to obstaja rešitev. Ustvarite lahko ločen stolpec tako, da združite ime in priimek, nato pa poiščete ta stolpec z imenom in priimkom.
Obstaja an Alternativa INDEX-MATCH, ki lahko poišče več meril brez stolpca za pomoč.
17. VLOOKUP pridobi samo prvo najdeno vrednost
Predstavljajte si, da imate nekaj podatkov v osrednji regiji. Zdaj želite dobiti prvih 5 podatkovnih zapisov osrednje regije. VLOOKUP bo vrnil samo prvo osrednjo vrednost v vseh petih zapisih. To je pomembno ozadje.
Vendar to ne pomeni, da tega ne moremo doseči. Lahko uporabimo kompleksno formulo nizov, kot je prikazano spodaj.
= INDEX ($ C $ 2: $ C $ 14, SMALL (IF (G2 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1), ROW (1: 1)))
Zapišite to formulo in pritisnite CTRL+SHIFT+ENTER.
In končano.
V članku sem razložil, kako v Excelu poiščete več vrednosti.
Torej, fantje, v tem članku sem po mojem mnenju obravnaval vse možne vidike delovanja VLOOKUP. Če menite, da sem kaj spregledal, mi to sporočite v spodnjem oddelku za komentarje.
Vlookup Top 5 vrednosti z podvojenimi vrednostmi z uporabo INDEX-MATCH v Excelu
VLOOKUP Več vrednosti
VLOOKUP z indeksom dinamičnega kolona
Delno ujemanje s funkcijo VLOOKUP
Uporabite VLOOKUP iz dveh ali več iskalnih tabel
Vlookup po datumu v Excelu
S formulo VLOOKUP preverite, ali vrednost obstaja
Kako VLOOKUP iz različnih Excelovih listov
VLOOKUP s številkami in besedilom
IF, ISNA in VLOOKUP
Funkciji ISNA in VLOOKUP
IFERROR in funkcija VLOOKUP