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

Naudokite VLOOKUP, kai reikia rasti dalykus 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(Ką norite ieškoti, kur norite ieškoti, stulpelio numeris diapazone, kuriame yra grąžintina reikšmė, pateikti apytikslį arba tikslų atitikmenį , nurodytą kaip 1/TRUE arba 0/FALSE).

Jūsų naršyklė nepalaiko vaizdo įrašo.

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 langeliai.

Pvz., jei lentelės masyvas apima langelius B2:D7, 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 vietoj langelių nuorodų naudoti pavadinimus argumente. 

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 kairiosios dalies stulpelio table_array), 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 skaitiniu arba abėcėlės būdu, tada ieškoma arčiausiai reikšmės. Tai yra numatytasis būdas, jei nenurodote kito. Pvz., =VLOOKUP(90,A1:B100,2,TRUE).

  • Tikslus atitikmuo – 0/FALSE ieško tikslios reikšmės pirmajame stulpelyje. Pvz., =VLOOKUP("Smith",A1:B100,2,FALSE).

Kaip pradėti

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

  1. Reikšmė, kurią norite ieškoti, dar vadinama ieškoma reikšme.

  2. 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.

  3. Stulpelio numeris diapazone, kuriame yra grąžinama reikšmė. Pavyzdžiui, jei kaip diapazoną nurodysite B2:D11, B turėtumėte skaičiuoti kaip pirmą stulpelį, C kaip antrą ir t. t.

  4. 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, kuriame yra grąžinama reikšmė, 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 Olivier iš antrojo stulpelio (C stulpelio) table_array.  False grąžina tikslų atitikmenį.

2 pavyzdys

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

VLOOKUP ieško tikslaus 102 (lookup_value) vardo atitikmens (FALSE) antrame stulpelyje (B stulpelyje) diapazone A2:C7 ir grąžina Fontana.

3 pavyzdys

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

IF tikrina, ar VLOOKUP grąžina Sousa kaip darbuotojo pavardę, kuri atitinka 103 (lookup_value) A1:E7 (table_array). Kadangi pavardė, atitinkanti 103, yra Leal, IF sąlyga 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ą nuo 2014/6/30 ir grąžina reikšmę, kurią INY konvertuos į 59 skaičius.

5 pavyzdys

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

IF tikrina, ar VLOOKUP grąžina 105 stulpelio B pavardės reikšmę (lookup_value). Jei VLOOKUP randa pavardę, if rodys pavardę, kitu atveju IF grąžina Darbuotojas nerastas. ISNA užtikrina, kad jei VLOOKUP grąžins #N/A, tada klaida pakeičiama darbuotojo nerasta, o ne #N/A.



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

Galite naudoti VLOOKUP norėdami sujungti kelias lenteles į vieną, jei vienoje iš lentelių yra laukų, bendrų su visais kitais. 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 – sujungiant šaltinius į vieną lentelę ir pakeitus duomenų funkcijos duomenų šaltinį į naują lentelę, duomenų funkciją galima naudoti senesnėse "Excel" versijose (jei pačią duomenų funkciją palaiko senesnė versija).

Darbalapis su stulpeliais, kurie naudoja VLOOKUP duomenims iš kitų lentelių gauti

Čia stulpeliuose A-F ir H yra reikšmių arba formulių, kurios darbalapyje naudoja tik reikšmes, o likusiuose stulpeliuose naudojama VLOOKUP ir stulpelio A (Kliento kodas) ir stulpelio B (teisininkas) reikšmės duomenims iš kitų lentelių gauti.

  1. Nukopijuokite lentelę su dažnais laukais į naują darbalapį ir suteikite jai pavadinimą.

  2. Spustelėkite Duomenų > Įrankiai ir>, kad atidarytumėte dialogo langą Ryšių valdymas.

    Dialogo langas Ryšių valdymas
  3. Atkreipkite dėmesį į kiekvieną nurodytą ryšį:

    • Laukas, kuris susieja lenteles (nurodytas skliausteliuose dialogo lange). Tai yra lookup_value VLOOKUP formulės dalis.

    • Susijusios peržvalgos lentelės pavadinimas. Tai yra table_array formulėje VLOOKUP.

    • Susijusios peržvalgos lentelės laukas (stulpelis), kuriame yra norimi naujo stulpelio duomenys. Ši informacija nerodoma dialogo lange Ryšių valdymas – turėsite peržiūrėti susijusią peržvalgos lentelę, kad pamatytumėte, kurį lauką norite gauti. Norite atkreipti dėmesį į stulpelio numerį (A=1) – tai col_index_num formulėje.

  4. Norėdami įtraukti lauką į naują lentelę, pirmame tuščiame stulpelyje įveskite savo VLOOKUP formulę naudodami informaciją, kurią surinkote atlikdami 3 veiksmą.

    Mūsų pavyzdyje stulpelyje G naudojamas advokatas (lookup_value) gauti sąskaitos normos duomenis iš ketvirto stulpelio (col_index_num = 4) iš darbalapio lentelės Advokatai, tblAttorneys (table_array), su formule =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).

    Formulėje taip pat gali būti naudojama langelio nuoroda ir diapazono nuoroda. Mūsų pavyzdyje būtų =VLOOKUP(A2,'Attorneys'! A:D,4,FALSE).

  5. Tęskite laukų įtraukimą, kol turėsite visus norimus laukus. Jei bandote paruošti darbaknygę su duomenų funkcijomis, 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 klaida #SPILL! paprastai reiškia, kad formulė remiasi numanoma peržvalgos reikšmės sankirta ir kaip nuorodą naudoja visą stulpelį. Pvz., =VLOOKUP(A:A,A:C,2,FALSE). Problemą galite išspręsti pritvirtinę peržvalgos nuorodą @ operatoriumi, pvz.: =VLOOKUP(@A:A,A:C,2,FALSE). Arba galite naudoti tradicinį VLOOKUP metodą ir nurodyti vieną langelį, o ne visą stulpelį: =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ą (~).

Pvz., =VLOOKUP("Fontan?",B2:E7,2,FALSE) ieškos visų Fontana egzempliorių 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.

Reikia daugiau pagalbos?

Visada galite paprašyti specialisto iš „Excel“ technologijų bendruomenės arba gauti pagalbos iš Atsakymų bendruomenės.

Taip pat žr.

Sparčiųjų nuorodų kortelė: VLOOKUP atnaujinimo priemonė
Trumpųjų nuorodų kortelė: VLOOKUP trikčių diagnostikos patarimai
Kaip ištaisyti klaidos #VALUE! VLOOKUP funkcijoje
Kaip ištaisyti #N/A klaidą funkcijoje VLOOKUP
Programos „Excel“ formulių apžvalga
Kaip išvengti sugadintų formulių
Klaidų aptikimas formulėse
„Excel“ funkcijos (pagal abėcėlę)
„Excel“ funkcijos (pagal kategoriją)
VLOOKUP (nemokama peržiūra)

Reikia daugiau pagalbos?

Tobulinkite savo įgūdžius
Ieškoti mokymo

Ar ši informacija buvo naudinga?

Ar esate patenkinti kalbos kokybe?
Kas turėjo įtakos jūsų įspūdžiams?

Dėkojame už jūsų atsiliepimus!

×