V tem svetu, ki temelji na podatkih, potrebujemo različna orodja za upravljanje podatkov. Podatkov v realnem času je ogromno in pridobivanje podrobnosti o določenem delu podatkov bi bilo vsekakor mučno opravilo, vendar z VLOOKUP v Excel , to nalogo je mogoče doseči z eno vrstico ukaza. V tem članku boste spoznali enega pomembnih Funkcije Excel tj. funkcija VLOOKUP.
Preden nadaljujemo, si na hitro oglejmo vse teme, ki so obravnavane tukaj:
- Kaj je VLOOKUP v Excelu?
- Kako deluje?
- Natančno ujemanje
- Približno ujemanje
- Prva tekma
- Čutnost med velikimi in malimi črkami
- Napake
- Dvosmerno iskanje
- Uporaba nadomestnih znakov
- Več iskalnih tabel
Kaj je VLOOKUP v Excelu?
V Excelu je VLOOKUP vgrajena funkcija ki se uporablja za iskanje in pridobivanje določenih podatkov z Excelovega lista. V pomeni Vertical (Navpično) in če želite uporabljati funkcijo VLOOKUP v Excelu, morajo biti podatki razporejeni navpično. Ta funkcija je zelo priročna, če imate ogromno podatkov in bi bilo praktično nemogoče ročno poiskati določene podatke.
Kako deluje?
Funkcija VLOOKUP sprejme vrednost, tj. Vrednost iskanja, in jo začne iskati v skrajnem levem stolpcu. Ko najdemo prvi pojav iskalne vrednosti, se začne premikati desno v tej vrstici in vrne vrednost iz stolpca, ki ste ga določili. S to funkcijo lahko vrnete tako natančna kot tudi približna ujemanja (privzeto ujemanje je približno ujemanje).
Sintaksa:
Sintaksa te funkcije je naslednja:
VLOOKUP (lookup_value, tabela_ matrika, col_index_num, [range_lookup])
kje,
- lookup_value je vrednost, na katero je treba paziti v prvem stolpcu dane tabele
- index_index je tabela, iz katere naj se pridobijo podatki
- col_index_num je stolpec, iz katerega je treba pridobiti vrednost
- range_lookup je logična vrednost, ki določa, ali naj bo vrednost iskanja popolnoma ujemanje ali približno ujemanje ( PRAV bo našel najbližje ujemanje NAPAKA preverja natančno ujemanje)
Natančno ujemanje:
Ko želite, da funkcija VLOOKUP išče natančno ujemanje iskalne vrednosti, boste morali nastaviti range_lookup vrednost na FALSE. Oglejte si naslednji primer, ki je tabela s podrobnostmi o zaposlenih:
Če želite poiskati imenovanje katerega koli od teh zaposlenih, lahko storite naslednje:
- Izberite celico, v kateri želite prikazati izhodne podatke, in nato vnesite znak '='
- Uporabite funkcijo VLOOKUP in vnesite lookup_value (Tukaj bo ID zaposlenega)
- Nato vnesite druge parametre, tj polje_tabele , col_index_num in nastavite range_lookup vrednost na FALSE
- Zato bodo funkcija in njeni parametri: = VLOOKUP (104, A1: D8, 3, FALSE)
Funkcija VLOOKUP začne iskati ID zaposlenega 104 in se nato premakne desno v vrstico, kjer je vrednost najdena. Nadaljuje se do col_index_num in vrne vrednost, prisotno na tem mestu.
Približno ujemanje:
Ta funkcija funkcije VLOOKUP vam omogoča pridobivanje vrednosti, tudi če nimate natančnega ujemanja za loopup_value. Kot smo že omenili, boste morali nastaviti, da bo VLOOKUP iskal približno ujemanje range_lookup vrednost na TRUE. Oglejte si naslednji primer, kjer so oznake preslikane skupaj z njihovimi ocenami in razredom, ki mu pripadajo.
- Tako kot pri natančnem ujemanju sledite istim korakom
- Namesto vrednosti range_lookup uporabite TRUE namesto FALSE
- Zato bo funkcija skupaj s svojimi parametri: = VLOOKUP (55, A12: C15, 3, TRUE)
V tabeli, ki je razvrščena po naraščajočem vrstnem redu, začne VLOOKUP iskati približno ujemanje in se ustavi pri naslednji največji vrednosti, ki je manjša od vnesene vrednosti iskanja. Nato se premakne desno v tej vrstici in vrne vrednost iz določenega stolpca. V zgornjem primeru je vrednost iskanja 55, naslednja največja vrednost iskanja v prvem stolpcu pa 40. Izhod je torej drugi razred.
Prva tekma:
Če imate tabelo, ki je sestavljena iz več iskalnih vrednosti, se VLOOKUP ustavi ob prvem ujemanju in v navedeni vrstici v podanem stolpcu pridobi vrednost.
Oglejte si spodnjo sliko:
Talend Open Studio tutorial pdf
ID 105 se ponovi in ko je iskalna vrednost podana kot 105, je VLOOKUP vrnil vrednost iz vrstice, ki ima prvi pojav iskalne vrednosti.
Čutnost med velikimi in malimi črkami:
Funkcija VLOOKUP ne razlikuje med velikimi in malimi črkami. Če imate iskalno vrednost, ki je v velikih črkah in je vrednost v tabeli majhna, bo VLOOKUP še vedno prinesel vrednost iz vrstice, v kateri je vrednost. Oglejte si spodnjo sliko:
Kot lahko vidite, je vrednost, ki sem jo določil kot parameter, 'RAFA', medtem ko je vrednost v tabeli 'Rafa', vendar je VLOOKUP še vedno vrnil določeno vrednost. Če se natančno ujemate tudi s primerom, bo VLOOKUP še vedno vrnil prvo ujemanje iskalne vrednosti ne glede na uporabljeni primer. Oglejte si spodnjo sliko:
Napake:
Naravno je, da naletimo na napake, kadar uporabimo funkcije. Podobno lahko tudi pri uporabi funkcije VLOOKUP naletite na napake, nekatere pogoste napake pa so:
- #NAME
- # N / A
- #REF
- #VREDNOST
Napaka #NAME:
Ta napaka vas v bistvu obvešča, da ste pri sintaksi naredili napako. Da bi se izognili skladenjskim napakam, je za vsako funkcijo bolje uporabiti čarovnika za funkcije, ki ga nudi Excel. Čarovnik za funkcije vam pomaga z informacijami o vseh parametrih in vrsti vrednosti, ki jih morate vnesti. Oglejte si spodnjo sliko:
Kot lahko vidite, vas čarovnik za funkcije obvesti, da namesto parametra lookup_value vnesete katero koli vrsto vrednosti, in prav tako poda njen kratek opis. Podobno, ko izberete druge parametre, boste videli tudi informacije o njih.
# N / A Napaka:
Ta napaka se vrne, če za dano iskalno vrednost ni najdenega ujemanja. Če na primer vnesem »AFA« namesto »RAFA«, se prikaže napaka # N / A.
Če želite določiti sporočilo o napaki za zgornji dve napaki, lahko uporabite funkcijo IFNA. Na primer:
Napaka #REF:
Do te napake pride, ko se sklicujete na stolpec, ki ni na voljo v tabeli.
Napaka #VALUE:
Do te napake pride, ko parametrom postavite napačne vrednosti ali zgrešite nekatere obvezne parametre.
Dvosmerno iskanje:
Dvosmerno iskanje se nanaša na pridobivanje vrednosti iz dvodimenzionalne tabele iz katere koli celice referenčne tabele. Za izvedbo dvosmernega iskanja z uporabo VLOOKUP-a boste morali skupaj z njim uporabiti funkcijo MATCH.
Sintaksa MATCH je naslednja:
MATCH (lookup_value, lookup_array, match_type)
- lookup_value je vrednost, ki jo je treba iskati
- lookup_array je obseg celic, ki so sestavljene iz iskalnih vrednosti
- type_type je lahko število, tj. 0, 1 ali -1, ki predstavlja natančno ujemanje, manjše ali večje od
Namesto da uporabljate trdo kodirane vrednosti z VLOOKUP, lahko v referencah celic naredite dinamično zaobide. Upoštevajte naslednji primer:
Kot lahko vidite na zgornji sliki, funkcija VLOOKUP referenčno celico za referenčno vrednost sprejme F6, vrednost indeksa stolpca pa določi funkcija MATCH. Ko spremenite katero koli od teh vrednosti, se bo tudi rezultat spremenil. Oglejte si spodnjo sliko, kjer sem vrednost, ki je prisotna v F6, iz Chrisa spremenil v Leo in spremenil tudi izhod:
V primeru, da spremenim vrednost G5 ali tako F6 kot G5, bo ta formula delovala ustrezno in prikazala ustrezne rezultate.
Ustvarite lahko tudi spustne sezname, da bo naloga spreminjanja vrednosti zelo priročna. V zgornjem primeru je treba to storiti za F6 in G5. Tako lahko ustvarite spustne sezname:
- Na zavihku traku izberite Podatki
- V skupini Data Tools izberite Validation Data
- Odprite podokno z nastavitvami in v možnosti Dovoli izberite Seznam
- Določite polje izvornega seznama
Evo, kako izgleda, ko ustvarite spustni seznam:
Uporaba nadomestnih znakov:
Če ne poznate natančne vrednosti iskanja, ampak le njen del, lahko uporabite nadomestne znake. V Excelu simbol »*« predstavlja nadomestni znak. Ta simbol sporoča Excelu, da je treba iskati zaporedje, ki je pred, za ali med njimi, in pred ali za njimi je lahko poljubno število znakov. Na primer, v tabelo, ki sem jo ustvaril, bo VLOOKUP, če na obeh straneh vnesete »erg« skupaj z nadomestnimi znaki, vrnil izhod za »Sergio«, kot je prikazano spodaj:
Več iskalnih tabel:
Če imate več iskalnih tabel, lahko skupaj uporabite funkcijo IF, da si ogledate katero koli tabelo glede na določen pogoj. Če je na primer tabela s podatki dveh supermarketov in morate na podlagi prodaje ugotoviti dobiček vsakega od njih, lahko storite naslednje:
Ustvarite glavno tabelo, kot sledi:
Nato ustvarite dve tabeli, iz katerih je treba pridobiti dobiček.
Ko je to storjeno, ustvarite poimenovani obseg za vsako novo ustvarjeno tabelo. Če želite ustvariti imenovani obseg, sledite spodnjim korakom:
- Izberite tabelo za celotno tabelo, ki ji želite dodeliti ime
- Na zavihku traku izberite Formule in nato iz skupine Določena imena izberite Določi ime
- Videli boste naslednje pogovorno okno
- Navedite poljubno ime po svoji izbiri
- Kliknite V redu
Ko je to storjeno za obe tabeli, lahko te imenovane obsege v funkciji IF uporabite na naslednji način:
Kot lahko vidite, je VLOOKUP vrnil ustrezne vrednosti za zapolnitev stolpca Dobiček glede na to, kateremu supermarketu pripadajo. Namesto da bi napisal formulo v vsako celico stolpca Dobiček, sem samo kopiral formulo da prihranite čas in energijo.
S tem smo prišli do konca tega članka o VLOOKUP-u v Excelu. Upam, da ste na jasnem z vsem, kar ste delili z vami. Poskrbite, da boste čim več vadili in si povrnili izkušnje.
Imate vprašanje za nas? Prosimo, omenite ga v oddelku za komentarje tega spletnega dnevnika »VLOOKUP v Excelu« in v najkrajšem možnem času vam bomo odgovorili.
Če želite pridobiti poglobljeno znanje o vseh trendovskih tehnologijah, skupaj z različnimi aplikacijami, se lahko prijavite v živo s 24-urno podporo in življenjskim dostopom.