Excel VLOOKUP funkcija

VLOOKUP je Excelova funkcija za iskanje podatkov v tabeli, postavljeni navpično. VLOOKUP podpira približno in natančno ujemanje ter nadomestnih znakov (*?) za delna ujemanja. Iskalne vrednosti se morajo prikazati v prvi stolpec tabele prešel v VLOOKUP.

• vrednost - Vrednost, ki jo je treba iskati v prvem stolpcu tabele.
• miza - Tabela, iz katere je mogoče pridobiti vrednost.
• col_index - Stolpec v tabeli, iz katerega je mogoče pridobiti vrednost.
• range_lookup - [neobvezno] TRUE = približno ujemanje (privzeto). FALSE = natančno ujemanje.

VLOOKUP je Excelova funkcija za pridobivanje podatkov iz tabele, organizirane navpično. Iskalne vrednosti se morajo prikazati v prvi stolpec tabele prešel v VLOOKUP. VLOOKUP podpira približno in natančno ujemanje ter nadomestnih znakov (*?) za delna ujemanja.

Navpični podatki | Številke stolpcev | Izgleda samo pravilno | Načini ujemanja | Natančno ujemanje | Približno ujemanje | Prva tekma | Nadomestni znak | Dvosmerno iskanje | Več meril | #N/A Napake | Video posnetki

V je za navpično

Namen VLOOKUP -a je pridobivanje informacij iz tabele, organizirane na naslednji način:

Z uporabo številke naročila v stolpcu B kot iskalne vrednosti lahko VLOOKUP dobi ID stranke, znesek, ime in stanje za vsako naročilo. Na primer, če želite dobiti ime stranke za naročilo 1004, je formula naslednja:

 
= VLOOKUP (1004,B5:F9,4,FALSE) // returns 'Sue Martin'

Za vodoravne podatke lahko uporabite HLOOKUP , INDEX in MATCH , oz XLOOKUP .

kako vnesti znanstvene zapise v

VLOOKUP temelji na številkah stolpcev

Ko uporabljate VLOOKUP, si predstavljajte, da je vsak stolpec v miza je oštevilčena, začenši z leve. Če želite dobiti vrednost iz določenega stolpca, vnesite ustrezno številko kot 'indeks stolpca'. Na primer, indeks stolpca za pridobitev imena spodaj je 2:

Priimek in e -poštni naslov je mogoče pridobiti s stolpcema 3 in 4:

 
= VLOOKUP (H3,B4:E13,2,FALSE) // first name = VLOOKUP (H3,B4:E13,3,FALSE) // last name = VLOOKUP (H3,B4:E13,4,FALSE) // email address

VLOOKUP izgleda samo pravilno

VLOOKUP lahko gleda le desno. Podatki, ki jih želite pridobiti (vrednosti rezultatov), ​​so lahko prikazani v katerem koli stolpcu na desno iskalnih vrednosti:

Če morate poiskati vrednosti na levi strani, glejte INDEX in MATCH , oz XLOOKUP .

Natančno in približno ujemanje

VLOOKUP ima dva načina ujemanja, natančen in približen. Ime argumenta, ki nadzoruje ujemanje, je ' range_lookup '. To ime je zmedeno, saj se zdi, da ima s tem nekaj opraviti celični razponi kot A1: A10. Pravzaprav se beseda 'obseg' v tem primeru nanaša na 'obseg vrednosti' - kdaj range_lookup je TRUE, VLOOKUP se bo ujemal z a obseg vrednosti namesto natančne vrednosti. Dober primer tega je uporaba VLOOKUP za izračun ocen .

To je pomembno razumeti range_lookup privzeto na TRUE , kar pomeni, da bo VLOOKUP privzeto uporabljal približno ujemanje, kar je lahko nevarno . Nastavljeno range_lookup na FALSE, da prisilite natančno ujemanje:

 
= VLOOKUP (value, table, col_index) // approximate match (default) = VLOOKUP (value, table, col_index, TRUE) // approximate match = VLOOKUP (value, table, col_index, FALSE) // exact match

Opomba: Namesto FALSE lahko za natančno ujemanje vnesete tudi nič (0).

Natančno ujemanje

V večini primerov boste verjetno želeli uporabiti VLOOKUP v načinu natančnega ujemanja. To je smiselno, če imate kot vrednost iskanja edinstven ključ, na primer naslov filma v teh podatkih:

Formulo v H6 najti Leto , ki temelji na natančnem ujemanju naslova filma, je:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Približno ujemanje

V primerih, ko želite najboljše ujemanje , ni nujno, da je natančno ujemanje , boste želeli uporabiti približen način. Na primer, spodaj želimo poiskati provizijo v tabeli G5: H10. Iskalne vrednosti prihajajo iz stolpca C. V tem primeru moramo uporabiti VLOOKUP v približno ujemanje način, ker v večini primerov natančno ujemanje nikoli ne bo najdeno. Formula VLOOKUP v D5 je konfigurirana za izvajanje približnega ujemanja tako, da zadnji argument nastavi na TRUE:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

VLOOKUP bo iskal vrednosti v stolpcu G za iskalno vrednost. Če se najde natančno ujemanje, ga bo uporabil VLOOKUP. V nasprotnem primeru se bo VLOOKUP 'umaknil' in se ujemal s prejšnjo vrstico.

Opomba: pri uporabi načina približnega ujemanja z VLOOKUP je treba podatke razvrstiti v naraščajočem vrstnem redu glede na iskalno vrednost.

Prva tekma

V primeru podvojenih vrednosti bo VLOOKUP našel datoteko prva tekma ko je način ujemanja natančen. Na spodnjem zaslonu je VLOOKUP nastavljen tako, da poišče ceno za barvo 'Zelena'. Obstajajo trije vnosi z zeleno barvo in VLOOKUP vrne ceno za prvi vstop, 17 dolarjev. Formula v celici F5 je:

 
= VLOOKUP (E5,B5:C11,2,FALSE) // returns 17

Nadomestni znak

Funkcija VLOOKUP podpira nadomestnih znakov , ki omogoča izvedbo delnega ujemanja pri iskalni vrednosti. Na primer, lahko uporabite VLOOKUP za pridobivanje vrednosti iz tabele samo po vnosu del iskalne vrednosti. Če želite uporabljati nadomestne znake z VLOOKUP, morate določiti način natančnega ujemanja, tako da za zadnji argument navedete FALSE ali 0, range_lookup . Formula v H7 po vnosu 'Aya' v celico H4 pridobi prvo ime 'Michael':

 
= VLOOKUP ($H&'*',$B:$E4,2,FALSE)

Preberi več podrobna razlaga tukaj .

Dvosmerno iskanje

Znotraj funkcije VLOOKUP je argument indeksa stolpca običajno trdo kodiran kot statično število. Lahko pa ustvarite tudi indeks dinamičnega stolpca z uporabo funkcije MATCH poiščite desni stolpec. Ta tehnika vam omogoča ustvarjanje dinamičnega dvosmernega iskanja, ki se ujema v obeh vrsticah in stolpci. Na spodnjem zaslonu je VLOOKUP konfiguriran za iskanje na podlagi imena in meseca. Formula v H6 je:

 
= VLOOKUP (H4,B5:E13, MATCH (H5,B4:E4,0),0)

Za več podrobnosti, glej ta primer .

Opomba: Na splošno je INDEX in MATCH je bolj prilagodljiv način izvajanje dvosmernih iskanj .

Več meril

Funkcija VLOOKUP izvorno ne obravnava več meril. Lahko pa uporabite a pomožni stolpec za združevanje več polj in uporaba teh polj kot več meril v VLOOKUP -u. V spodnjem primeru je stolpec B pomožni stolpec združuje ime in priimek skupaj s to formulo:

 
=C5&D5 // helper column

VLOOKUP je konfiguriran tako, da stori isto za ustvarjanje iskalne vrednosti. Formula v H6 je:

 
= VLOOKUP (H4&H5,B5:E13,4,0)

Za podrobnosti, glej ta primer .

Opomba: INDEX in MATCH in XLOOKUP so bolj robustni načini obravnavanja iskanj na podlagi več meril.

Napake VLOOKUP in #N/A

Če uporabljate VLOOKUP, boste neizogibno naleteli na napako #N/A. Napaka #N/A samo pomeni 'ni najdeno'. Na spodnjem zaslonu na primer vrednost iskanja 'Toy Story 2' ne obstaja v iskalni tabeli in vse tri formule VLOOKUP vrnejo #N/A:

Eden od načinov za ulov napake NA je uporaba datoteke Funkcija IFNA Všečkaj to:

Formula v H6 je:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'Not found')

Sporočilo lahko poljubno prilagodite. Če ne želite vrniti ničesar (tj. Prikazati praznega rezultata), ko VLOOKUP vrne #N/A, lahko uporabite prazen niz, kot je ta:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'') // no message

Napaka #N/A je uporabna, ker vam pove, da je nekaj narobe. V praksi obstaja veliko razlogov, zakaj bi lahko prišlo do te napake, med drugim:

• Iskalna vrednost ne obstaja v tabeli
• Iskalna vrednost je napačno črkovana ali vsebuje dodaten prostor
• Način ujemanja je natančen, vendar mora biti približen
• Območje tabel ni pravilno vneseno
• Kopirate VLOOKUP in tabelo referenca ni zaklenjena

Preberi več: VLOOKUP brez #N/A napak

Več o VLOOKUP -u

• Več primerov VLOOKUP
• Videoposnetki VLOOKUP
• 23 nasvetov za uporabo VLOOKUP -a

Druge opombe

• Range_lookup nadzoruje, ali vrednost se mora natančno ujemati ali ne. Privzeto je TRUE = dovoli nenatančno ujemanje.
• Nastavljeno range_lookup na LAŽNO na zahtevajo natančno ujemanje in TRUE to omogočajo nenatančno ujemanje .
• Če range_lookup je TRUE (privzeta nastavitev), zaradi nenatančnega ujemanja se funkcija VLOOKUP ujema z najbližjo vrednostjo v tabeli, ki je še manj kot vrednost .
• Kdaj range_lookup je izpuščena, bo funkcija VLOOKUP dovolila nenatančno ujemanje, vendar bo uporabila natančno ujemanje, če obstaja.
• Če range_lookup je TRUE (privzeta nastavitev), se prepričajte, da so iskalne vrednosti v prvi vrstici tabele razvrščene po naraščajočem vrstnem redu. V nasprotnem primeru lahko VLOOKUP vrne napačno ali nepričakovano vrednost.
• Če range_lookup je FALSE (zahtevajo natančno ujemanje), vrednosti v prvem stolpcu od miza ni treba razvrščati.