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 ją būtų lengviau ir patogiau naudoti nei jos pirmtakė.

Naudokite VLOOKUP, kai jums reikia rasti ką nors lentelėje ar 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ė, pateikia apytikslį arba tikslų atitikmenį – nurodytą kaip 1/TRUE arba 0/FALSE).

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.
  • Jei esate "Microsoft Copilot" prenumeratorius, "Copilot" gali dar lengviau įterpti ir naudoti funkcijas "VLookup" arba "XLookup". Žr . "Copilot" palengvina peržvalgas programoje "Excel".

Techninė informacija

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,'Išsami kliento informacija'! A:F, 3, FALSE)
Argumento pavadinimas Aprašymas
peržvalgos_reikšmė (būtina) Reikšmė, kurios norite ieškoti. Reikšmė, kurios norite ieškoti, turi būti pirmame stulpelyje stulpelių diapazone, kurį nurodote table_array argumente.
Pavyzdžiui, jei lentelės masyvas apima stulpelius B2:D7, tada jūsų lookup_value turi būti stulpelyje B.
Lookup_value gali būti reikšmė arba nuoroda į langelį.
lentelė_masyvas (būtina) Langelių diapazonas, kuriame VLOOKUP ieškos lookup_value ir grąžinamos reikšmės. Galite naudoti pavadintą diapazoną arba lentelę ir argumente vietoj langelio nuorodų galite naudoti pavadinimus.
Pirmajame langelių diapazono stulpelyje turi būti lookup_value. Langelių diapazone taip pat turi būti norima rasti grąžinama reikšmė.
Sužinokite, kaip pasirinkti darbalapio diapazonus.
stulpelio_indekso_numeris (būtina) Stulpelio numeris (pradedant nuo 1 kairiausiame 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 pirmas lentelės stulpelis yra rūšiuojamas abėcėlės tvarka arba pagal skaičių, tada ieškoma artimiausios reikšmės. Tai yra numatytasis būdas, jei nenurodote kito. Pavyzdžiui, =VLOOKUP(90,A1:B100,2,TRUE).
  • Tikslus atitikimas – 0/FALSE Ieškoma pirmame stulpelyje tikslios reikšmės. Pavyzdžiui, =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 nurodėte B2:D11 kaip diapazoną, galite suskaičiuoti B 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) Funkcija VLOOKUP table_array B2:E7 pirmajame stulpelyje (stulpelyje B) ieško Fontana, o iš table_array antrojo stulpelio (stulpelis C) grąžina Olivier. False pateikia tikslų atitikmenį.

2 pavyzdys

=VLOOKUP (102,A2:C7,2,FALSE) VLOOKUP ieško tikslaus (FALSE) 102 (lookup_value) pavardės atitikmens antrame stulpelyje (stulpelyje B) diapazone A2:C7 ir grąžina Kontaną.

3 pavyzdys

=IF(VLOOKUP(103,A1:E7,2,FALSE)=Souse,Rasti,Nerasta) IF patikrina, ar VLOOKUP grąžina Sousa kaip darbuotojo pavardę, atitinkančią 103 (lookup_value) langeliuose A1:E7 (table_array). Kadangi 103 atitinkanti pavardė yra Leal, sąlyga IF yra klaidinga ir rodoma Not Found.

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) A2:E7 diapazone (table_array), ir pateikia 03/04/1955. Tada funkcija YEARFRAC atims šią gimimo datą iš 2014/6/30 ir grąžins reikšmę, kurią INY konvertuoja į sveikąjį skaičių 59.

5 pavyzdys

IF(ISNA(VLOOKUP(105,A2:E7,2,FLASE))=TRUE,Darbuotojo nerasta,VLOOKUP(105,A2:E7,2,FALSE)) IF patikrina, ar VLOOKUP pateikia pavardės reikšmę iš stulpelio B reikšmės, reiškiančios 105 (lookup_value). Jei VLOOKUP randa pavardę, IF rodo pavardę, kitu atveju IF grąžina Darbuotojas nerastas. ISNA užtikrina, kad jei VLOOKUP pateikia #N/A, klaida pakeičiama į Darbuotojas nerastas, o ne #N/A. Šiame pavyzdyje grąžinama reikšmė yra Jurgis, kuris yra pavardė, atitinkanti 105.

Dažniausiai pasitaikančios problemos

Problema Kas nutiko
Grąžinta klaidinga reikšmė Jei range_lookup yra TRUE 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 range_lookup yra TRUE, tada, jei reikšmė lookup_value yra mažesnė nei mažiausia reikšmė pirmame table_array stulpelyje, gausite klaidos reikšmę #N/A.
  • Jei range_lookup 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 col_index_num didesnis nei lentelės masyvo stulpelių skaičius, gausite #REF! klaidos reikšmę.
Daugiau informacijos apie #REF! klaidų funkcijoje VLOOKUP taisymą, ieškokite klaidos "#REF!" taisymas.
#VALUE! langelyje Jei table_array mažesnis nei 1, gausite #VALUE! klaidos reikšmę.
Daugiau informacijos apie #VALUE! klaidų funkcijoje VLOOKUP taisymą, žr. Klaidos #VALUE! taisymas funkcijoje VLOOKUP.
#NAME? langelyje #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. Klaidos #NAME taisymas.
#IŠPLĖTIMAS! langelyje Ši konkreti #SPILL! klaida paprastai reiškia, kad jūsų formulė remiasi netiesiogine peržvalgos reikšmės sankirta ir naudoja visą stulpelį kaip nuorodą. Pavyzdžiui, =VLOOKUP( A:A,A:C,2,FALSE). Galite išspręsti šią problemą pritvirtindami peržvalgos nuorodą operatoriumi @ taip: =VLOOKUP(@A:A,A:C,2,FALSE). Taip pat galite naudoti tradicinį metodą VLOOKUP ir nurodyti vieną langelį, o ne visą stulpelį: =VLOOKUP(A2,A:C,2,FALSE).

Geriausios praktikos

Atlikite šiuos veiksmus Kodėl
Absoliučiųjų koordinačių naudojimas range_lookup 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 pirmojo table_array stulpelio 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į table_array stulpelį prieš naudodami VLOOKUP , kai range_lookup yra TRUE.
Pakaitos simbolių naudojimas Jei range_lookup yra FALSE, o lookup_value yra tekstas, lookup_value galite naudoti 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ų Kontrimo atvejų, kai paskutinė raidė 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 kreiptis eksperto į "Excel" technologijų bendruomenę arba gauti pagalbos bendruomenėse.