Funkcija VLOOKUP

Patarimas: Pabandykite naudoti naująją funkciją XLOOKUP – patobulintą VLOOKUP versiją, kuri veikia bet kuria kryptimi ir pateikia tikslius atitikmenis pagal numatytuosius nustatymus, kad būtų lengviau ir patogiau naudoti nei ankstesnės versijos.

Naudokite VLOOKUP, kai reikia rasti ką nors lentelėje arba diapazone pagal eilutę. Pavyzdžiui, ieškokite automobilio dalies kainos pagal dalies numerį arba raskite darbuotojo vardą pagal jo darbuotojo ID.

Iš esmės funkcija VLOOKUP nurodo:

=VLOOKUP(Ko norite ieškoti, kur norite jo ieškoti, stulpelio numeris diapazone, kuriame yra grąžintina reikšmė, pateikti apytikslį arba tikslų atitikmenį – nurodytas kaip 1/TRUE arba 0/FALSE).

Jūsų naršyklė nepalaiko vaizdo įrašo. Įdiekite „Microsoft Silverlight“, „Adobe Flash Player“ ar „Internet Explorer 9“.

Patarimas: VLOOKUP svarbu savo duomenis sutvarkyti taip, kad reikšmė, kurios ieškote (Vaisius), būtų norimos rasti grąžinamos reikšmės (Suma) kairėje.

Naudokite funkciją VLOOKUP norėdami rasti reikšmę lentelėje.

Sintaksė

VLOOKUP (peržvalgos_reikšmė, lentelė_masyvas, stulpelio_indekso_numeris, [peržvalgos_diapazonas])

Pavyzdžiui:

=VLOOKUP(A2,A10:C20,2,TRUE)
=VLOOKUP("Kontrimas",B2:E7,2,FALSE)
=VLOOKUP(A2,'Kliento informacija'! A:F,3,FALSE)

Argumento pavadinimas	Aprašas
peržvalgos_reikšmė (būtina)	Reikšmė, kurios norite ieškoti. Reikšmė, kurios norite ieškoti, turi būti pirmame langelių diapazono stulpelyje, kurį nurodote argumente *table_array* . Pavyzdžiui, jei *lentelė-masyvas* apima langelius B2:D7, jūsų lookup_value turi būti stulpelyje B. *Peržvalgos_reikšmė* gali būti reikšmė arba nuoroda į langelį.
lentelė_masyvas (būtina)	Langelių diapazonas, kuriame VLOOKUP ieškos *peržvalgos_reikšmės* ir grąžinama reikšmė. Galite naudoti pavadintą diapazoną arba lentelę ir argumente vietoje langelių nuorodų naudoti pavadinimus. Pirmajame langelių diapazono stulpelyje turi būti *lookup_value*. Langelių diapazone taip pat turi būti grąžinama reikšmė, kurią norite rasti. Sužinokite, kaip pasirinkti darbalapio diapazonus.
stulpelio_indekso_numeris (būtina)	Stulpelio numeris (pradedant nuo 1 kairiajame *table_array* stulpelyje), kuriame yra grąžinama reikšmė.
peržvalgos_diapazonas (pasirinktinai)	Loginė reikšmė, kuri nurodo, ar norite, kad funkcija VLOOKUP rastų tikslų ar apytikslį atitikimą: Apytikslis atitikmuo – 1/TRUE daro prielaidą, kad pirmasis lentelės stulpelis rūšiuojamas pagal skaičių arba abėcėlę, tada ieškoma artimiausios reikšmės. Tai yra numatytasis būdas, jei nenurodote kito. Pavyzdžiui, =VLOOKUP(90,A1:B100,2,TRUE). Tikslus atitikmuo – 0/FALSE ieško tikslios reikšmės pirmame stulpelyje. Pavyzdžiui, =VLOOKUP("Smith",A1:B100,2,FALSE).

Kaip pradėti

Jums reikės keturių informacijos vienetų, kad galėtumėte sukurti VLOOKUP sintaksę:

Reikšmė, kurią norite ieškoti, dar vadinama ieškoma reikšme.
Diapazonas, kuriame yra ieškoma reikšmė. Atminkite: kad VLOOKUP veiktų tinkamai, ieškoma reikšmė visada turi būti pirmame diapazono stulpelyje. Pavyzdžiui, jei jūsų peržvalgos reikšmė yra langelyje C2, tada jūsų diapazonas turi prasidėti C stulpelyje.
Stulpelio numeris diapazone, kuriame yra grąžinama reikšmė. Pavyzdžiui, jei kaip diapazoną nurodote B2:D11, turėtumėte suskaičiuoti B kaip pirmą stulpelį, C – kaip antrą ir t. t.
Jei norite, galite nurodyti TRUE, jei norite apytikslio atitikmens arba FALSE, jei norite tikslios reikšmės. Jei nenurodysite nieko, numatytoji reikšmė visada bus TRUE arba apytikslis atitikmuo.

Dabar sujunkite visą anksčiau pateiktą informaciją taip:

=VLOOKUP(peržvalgos reikšmė, diapazonas, kuriame yra peržvalgos reikšmė, stulpelio numeris diapazone su grąžinama reikšme, apytikslis atitikmuo (TRUE) arba tikslus atitikmuo (FALSE)).

Pavyzdžiai

Toliau pateikiami keli VLOOKUP pavyzdžiai.

1 pavyzdys

=VLOOKUP (B3,B2:E7,2,FALSE)

VLOOKUP ieško Fontana pirmajame stulpelyje (B stulpelyje) table_array B2:E7, ir grąžina "Kas" iš table_array antro stulpelio (stulpelio C). False pateikia tikslų atitikmenį.

2 pavyzdys

=VLOOKUP (102,A2:C7,2,FALSE)

VLOOKUP ieško tikslaus 102 (lookup_value) pavardės atitikmens (FALSE) antrame diapazono A2:C7 stulpelyje (stulpelyje) ir grąžina Fontana.

3 pavyzdys

=IF(VLOOKUP(103,A1:E7,2,FALSE)="Souse","Located","Not found")

If checks to see if VLOOKUP returns Sousa as the last name of employee correspoinding to 103 (lookup_value) in A1:E7 (table_array). Kadangi 103 atitinkanti pavardė yra "Leal", sąlyga IF yra klaidinga ir rodoma Nerasta.

Pavyzdys 4

=INT(YEARFRAC(DATE(2014;6;30),VLOOKUP(105;A2:E7;5;FLASE),1))

VLOOKUP ieško darbuotojo gimimo datos, atitinkančios 109 (lookup_value) diapazone A2:E7 (table_array), ir pateikia 1955-03-04. Tada YEARFRAC atima šią gimimo datą iš 2014/6/30 ir pateikia reikšmę, kuri iny konvertuojama į sveikojo skaičiaus 59.

5 pavyzdys

IF(ISNA(VLOOKUP(105,A2:E7,2,FLASE))=TRUE,"Darbuotojas nerastas",VLOOKUP(105,A2:E7,2,FALSE))

If checks to see if VLOOKUP returns a value for last name from column B for 105 (lookup_value). Jei funkcija VLOOKUP randa pavardę, if rodys pavardę, kitu atveju funkcija IF grąžins Darbuotojo nerasta. ISNA užtikrina, kad jei funkcija VLOOKUP grąžins #N/A, tada klaida bus pakeista į Darbuotojas nerastas, o ne #N/A.

Šiame pavyzdyje grąžinama reikšmė yra "Burke", kuri yra 105 atitinkanti pavardė.

Naudodami VLOOKUP galite sujungti kelias lenteles į vieną, jei vienoje iš lentelių yra bendrų laukų. Tai gali būti ypač naudinga, jei reikia bendrinti darbaknygę su žmonėmis, kurie turi senesnes "Excel" versijas, kurios nepalaiko duomenų funkcijų su keliomis lentelėmis kaip duomenų šaltiniais, sujungdami šaltinius į vieną lentelę ir pakeisdami duomenų priemonės duomenų šaltinį į naują lentelę, duomenų funkciją galima naudoti senesnėse "Excel" versijose (jei pati duomenų funkcija palaikoma senesnės versijos).

Darbalapis su stulpeliais, kuriuose naudojama VLOOKUP duomenims iš kitų lentelių gauti

Čia stulpeliuose A–F ir H yra reikšmės arba formulės, kurios naudoja tik darbalapio reikšmes, o kiti stulpeliai naudoja VLOOKUP, o A stulpelio (kliento kodas) ir stulpelio B (advokatas) reikšmes duomenims gauti iš kitų lentelių.

Nukopijuokite lentelę, kurioje yra bendrųjų laukų, į naują darbalapį ir suteikite jai pavadinimą.
Spustelėkite Duomenų > Duomenų įrankiai > ryšiai, kad atidarytumėte dialogo langą ryšių valdymas.
Atkreipkite dėmesį į šiuos kiekvieno pateikto ryšio atvejus:
- Laukas, kuris susieja lenteles (pateiktas skliausteliuose dialogo lange). Tai yra jūsų VLOOKUP formulės lookup_value .
- Susijusios peržvalgos lentelės pavadinimas. Tai yra jūsų VLOOKUP formulės table_array.
- Laukas (stulpelis) susijusioje peržvalgos lentelėje, kuriame yra norimų duomenų naujame stulpelyje. Ši informacija nerodoma dialogo lange ryšių valdymas – turėsite peržiūrėti susijusią peržvalgos lentelę, kad pamatytumėte, kurį lauką norite nuskaityti. Norite atkreipti dėmesį į stulpelio numerį (A=1) – tai yra jūsų formulės col_index_num .
Norėdami įtraukti lauką į naują lentelę, įveskite VLOOKUP formulę pirmame tuščiame stulpelyje naudodami informaciją, kurią surinkote atlikdami 3 veiksmą.

Mūsų pavyzdyje G stulpelyje naudojama advokatas ( lookup_value), kad gautų sąskaitų tarifų duomenis iš ketvirtojo stulpelio (col_index_num = 4) iš darbalapių lentelės Advokatai, tblAttorneys ( table_array), su formule =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).

Formulė taip pat gali naudoti langelio nuorodą ir diapazono nuorodą. Mūsų pavyzdyje būtų =VLOOKUP(A2,'Attorneys'! A:D,4,FALSE).
Tęskite laukų įtraukimą, kol gausite visus reikiamus laukus. Jei bandote parengti darbaknygę, kurioje yra duomenų funkcijų, kurios naudoja kelias lenteles, pakeiskite duomenų funkcijos duomenų šaltinį į naują lentelę.

Problema	Kas nutiko
Grąžinta klaidinga reikšmė	Jei *peržvalgos_diapazonas* yra TRUE (teisinga) arba už ribų, pirmas stulpelis turi būti rikiuojamas abėcėlės tvarka arba pagal skaičių. Jei pirmas stulpelis nebus surikiuotas, gali būti grąžinta nenumatyta reikšmė. Surikiuokite pirmą stulpelį arba naudokite FALSE tiksliai atitikčiai.
#N/A langelyje	Jei *peržvalgos_diapazonas* yra TRUE, tada jei *peržvalgos_reikšmė* yra mažesnė už mažiausią pirmo *lentelė_masyvas* stulpelio reikšmę, gausite klaidos reikšmę #N/A. Jei *peržvalgos_diapazonas* yra FALSE, #N/A klaidos reikšmė nurodo, kad nerastas tikslus skaičius. Daugiau informacijos apie #N/A klaidų funkcijoje VLOOKUP taisymą, ieškokite Klaidos #N/A taisymas funkcijoje VLOOKUP.
#REF! langelyje	Jei *stulpelio_indekso_numeris* yra didesnis nei stulpelių skaičius *lentelėje-masyve*, gausite #REF! klaidos reikšmę. Daugiau informacijos apie #REF! klaidų funkcijoje VLOOKUP taisymą, ieškokite Klaidos #REF! taisymas.
#VALUE! langelyje	Jei *lentelė_masyvas* yra mažesnis už 1, gausite #VALUE! klaidos reikšmę. Daugiau informacijos apie #VALUE! klaidų funkcijoje VLOOKUP taisymą, ieškokite Klaidos #VALUE! taisymas funkcijoje VLOOKUP.
#NAME? langelyje	Klaidos reikšmė #NAME? paprastai reiškia, kad formulėje trūksta kabučių. Jei norite ieškoti asmens vardo, įsitikinkite, kad formulėje naudojate kabutes aplink vardą. Pavyzdžiui, įveskite pavardę kaip "Kontrimas" – = VLOOKUP("Kontrimas",B2:E7,2,FALSE). Daugiau informacijos žr..
#SPILL! langelyje	Ši konkreti #SPILL! klaida paprastai reiškia, kad formulė naudoja numanomą peržvalgos reikšmės sankirtą ir naudoja visą stulpelį kaip nuorodą. Pavyzdžiui, =VLOOKUP(A:A,A:C,2,FALSE). Problemą galite išspręsti prisegdami peržvalgos nuorodą prie operatoriaus @, pvz.: =VLOOKUP(@A:A,A:C,2,FALSE). Arba galite naudoti tradicinį metodą VLOOKUP ir vietoj viso stulpelio nurodyti vieną langelį: =VLOOKUP(A2,A:C,2,FALSE).

Atlikite tai	Kodėl
Naudokite peržvalgos_diapazonas absoliučiąsias koordinates	Naudojant absoliučiąsias koordinates galima užpildyti žemyn formulę, kad ji visada ieškotų tiksliame peržvalgos diapazone. Sužinokite, kaip naudoti absoliučiąsias langelių koordinates.
Nesaugokite skaičiaus arba datos reikšmių kaip teksto.	Ieškodami skaičių ar datų reikšmių, įsitikinkite, kad pirmame *lentelė_masyvas* stulpelyje duomenys nėra saugomi kaip teksto reikšmės. Tokiu atveju funkcija VLOOKUP gali grąžinti neteisingas arba nenumatytas reikšmes.
Pirmo stulpelio rikiavimas	Rikiuokite pirmąjį *lentelė_masyvas* stulpelį prieš naudodami VLOOKUP, kai peržvalgos_diapazonas yra TRUE.
Pakaitos simbolių naudojimas	Jei *peržvalgos_diapazonas* yra FALSE ir *peržvalgos_reikšmė* yra tekstas, galite į *peržvalgos_reikšmė* įtraukti pakaitos simbolius – klaustuką (?) ir žvaigždutę () . Klaustukas atitinka bet kokį vieną simbolį. Žvaigždutė atitinka bet kokią simbolių seką. Jei norite rasti tikrą klaustuką ar žvaigždutę, prieš simbolį įveskite tildės ženklą (~). Pavyzdžiui, =VLOOKUP("Fontan?",B2:E7,2,FALSE) ieškos visų Fontana* atvejų su paskutine raide, kuri gali skirtis.
Įsitikinkite, kad jūsų duomenyse nėra klaidingų simbolių.	Ieškodami teksto reikšmių pirmame stulpelyje, įsitikinkite, kad pirmo stulpelio duomenų pradžioje ir pabaigoje nėra tarpų, nenaudojamos tiesios ( ' arba " ) ir lenktos ( ‘ arba “) kabutės ir nėra nespausdinamų simbolių. Tokiais atvejais funkcija VLOOKUP gali grąžinti nenumatytą reikšmę. Kad gautumėte tikslius rezultatus, pabandykite naudoti funkciją CLEAN arba funkciją TRIM, kad pašalintumėte tarpus po lentelės reikšmėmis langelyje.