SantraukaVLOOKUP yra „Excel“ funkcija, skirta ieškoti duomenų vertikaliai suskirstytoje lentelėje. VLOOKUP palaiko apytikslį ir tikslų atitikimą ir pakaitos simboliai (*?) dalinėms rungtynėms. Peržiūros vertės turi būti rodomos Pirmas lentelės stulpelis perėjo į VLOOKUP.
Tikslas Rasti lentelės vertę pagal atitikimą pirmame stulpelyje. Grąžinti reikšmę Atitinkama lentelės vertė. Sintaksė = VLOOKUP (vertė, lentelė, col_index, [range_lookup]) Argumentai
- vertės - Reikšmė, kurios reikia ieškoti pirmajame lentelės stulpelyje.
- lentelę - Lentelė, iš kurios galima gauti vertę.
- col_index - Lentelės stulpelis, iš kurio galima gauti reikšmę.
- range_lookup - [neprivaloma] TRUE = apytikslė atitiktis (numatytoji). FALSE = tiksli atitiktis.
VLOOKUP yra „Excel“ funkcija, skirta gauti duomenis iš vertikaliai sutvarkytos lentelės. Peržiūros vertės turi būti rodomos Pirmas lentelės stulpelis perėjo į VLOOKUP. VLOOKUP palaiko apytikslį ir tikslų atitikimą ir pakaitos simboliai (*?) dalinėms rungtynėms.
Vertikalūs duomenys | Stulpelių numeriai | Tik teisingai atrodo | Atitinkami režimai | Tikslus atitikimas | Apytikslė atitiktis | Pirmosios rungtynės | Pakaitos simbolių rungtynės | Abipusė paieška | Keli kriterijai | #Netaikomos klaidos | Vaizdo įrašai
V yra vertikalus
VLOOKUP tikslas yra gauti informaciją iš lentelės, išdėstytos taip:
Naudodamas B stulpelio užsakymo numerį kaip paieškos vertę, VLOOKUP gali gauti bet kurio užsakymo kliento ID, sumą, pavadinimą ir būseną. Pavyzdžiui, norint gauti 1004 užsakymo kliento vardą, formulė yra tokia:
= VLOOKUP (1004,B5:F9,4,FALSE) // returns 'Sue Martin'
Horizontaliems duomenims galite naudoti PERŽIŪRĖTI , INDEX ir MATCH , arba XLOOKUP .
kaip sukurti „Excel“ eilutės pertrauką
VLOOKUP yra pagrįstas stulpelių numeriais
Kai naudojate VLOOKUP, įsivaizduokite, kad kiekvienas stulpelis lentelę yra sunumeruotas, pradedant nuo kairės. Norėdami gauti vertę iš tam tikro stulpelio, nurodykite atitinkamą skaičių kaip „stulpelio indeksą“. Pvz., Stulpelių rodyklė, skirta žemiau esančiam vardui gauti, yra 2:
Pavardę ir el. Pašto adresą galima gauti naudojant 3 ir 4 stulpelius:
= 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 atrodo tik teisingai
VLOOKUP gali žiūrėti tik į dešinę. Duomenys, kuriuos norite gauti (rezultatų reikšmės), gali būti rodomi bet kuriame stulpelyje į dešinę iš paieškos verčių:
Jei reikia ieškoti verčių kairėje, žr INDEX ir MATCH , arba XLOOKUP .
Tikslus ir apytikslis atitikimas
„VLOOKUP“ turi du suderinimo būdus - tikslų ir apytikslį. Atitiktį valdančio argumento pavadinimas yra „ range_lookup “. Tai painus pavadinimas, nes atrodo, kad jis turi kažką bendro ląstelių diapazonai kaip A1: A10. Tiesą sakant, žodis „diapazonas“ šiuo atveju reiškia „verčių diapazoną“ - kada range_lookup yra TRUE, VLOOKUP atitiks a verčių diapazonas o ne tikslią vertę. Geras to pavyzdys yra naudojimas VLOOKUP apskaičiuoti pažymius .
Svarbu tai suprasti range_lookup numatytoji reikšmė yra TRUE , o tai reiškia, kad VLOOKUP pagal numatytuosius nustatymus naudos apytikslę atitiktį, kuris gali būti pavojingas . Nustatyti range_lookup į FALSE, kad priverstumėte tiksliai suderinti:
= VLOOKUP (value, table, col_index) // approximate match (default) = VLOOKUP (value, table, col_index, TRUE) // approximate match = VLOOKUP (value, table, col_index, FALSE) // exact match
Pastaba: tikslios atitikties atveju taip pat galite pateikti nulį (0) vietoj FALSE.
Tikslus atitikimas
Daugeliu atvejų tikriausiai norėsite naudoti VLOOKUP tikslios atitikties režimu. Tai prasminga, kai turite unikalų raktą, kurį galite naudoti kaip paieškos vertę, pvz., Filmo pavadinimą šiuose duomenyse:
Rasti H6 formulę Metai , remiantis tikslia filmo pavadinimo atitiktimi, yra:
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match
Apytikslė atitiktis
Tais atvejais, kai norite ,. Geriausias atitikimas , nebūtinai an tikslus atitikimas , norėsite naudoti apytikslį režimą. Pavyzdžiui, žemiau norime ieškoti komisinių normos lentelėje G5: H10. Peržiūros reikšmės yra iš C stulpelio. Šiame pavyzdyje turime naudoti VLOOKUP in apytikslis atitikimas režimu, nes daugeliu atvejų tikslios atitikties niekada nebus galima rasti. D5 VLOOKUP formulė sukonfigūruota atlikti apytikslę atitiktį, nustatant paskutinį argumentą į TRUE:
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match
„VLOOKUP“ nuskaitys G stulpelio vertes, kad surastų paieškos vertę. Jei randama tiksli atitiktis, VLOOKUP ją naudos. Jei ne, VLOOKUP „atsitrauks“ ir atitiks ankstesnę eilutę.
Pastaba: kai naudojate apytikslę atitikties režimą su VLOOKUP, duomenys turi būti surūšiuoti didėjimo tvarka pagal paieškos vertę.
Pirmosios rungtynės
Pasikartojančių reikšmių atveju VLOOKUP ras pirmosios rungtynės kai atitikimo režimas yra tikslus. Žemiau esančiame ekrane VLOOKUP sukonfigūruotas taip, kad būtų galima rasti spalvos „žalia“ kainą. Yra trys įrašai su žalia spalva, o VLOOKUP grąžina kainą Pirmas įėjimas, 17 USD. Formulė ląstelėje F5 yra tokia:
= VLOOKUP (E5,B5:C11,2,FALSE) // returns 17
Pakaitos simbolių rungtynės
Funkcija VLOOKUP palaiko pakaitos simboliai , o tai leidžia atlikti dalinę paieškos vertės atitiktį. Pvz., Galite naudoti VLOOKUP, kad gautumėte reikšmes iš lentelės tik įvedę dalis paieškos vertės. Norėdami naudoti pakaitos simbolius su VLOOKUP, turite nurodyti tikslios atitikties režimą, paskutiniam argumentui nurodydami FALSE arba 0, range_lookup . H7 formulė suranda pirmąjį vardą „Michael“, įvedus „Aya“ į langelį H4:
= VLOOKUP ($H&'*',$B:$E4,2,FALSE)
Skaitykite daugiau išsamus paaiškinimas čia .
Abipusė paieška
Funkcijos VLOOKUP viduje stulpelio indekso argumentas paprastai yra užkoduotas kaip statinis skaičius. Tačiau taip pat galite sukurti dinaminis stulpelių indeksas naudodami MATCH funkciją, kad surastumėte dešinįjį stulpelį. Ši technika leidžia sukurti dinamišką dviejų krypčių peržiūrą, atitinkančią abi eilutes ir stulpelių. Žemiau esančiame ekrane VLOOKUP sukonfigūruotas atlikti paiešką pagal pavadinimą ir mėnesį. H6 formulė yra tokia:
= VLOOKUP (H4,B5:E13, MATCH (H5,B4:E4,0),0)
Daugiau detalių, žr. šį pavyzdį .
Pastaba: Apskritai, INDEX ir MATCH yra lankstesnis būdas atlikti dvipusę paiešką .
Keli kriterijai
Funkcija VLOOKUP savaime netvarko kelių kriterijų. Tačiau galite naudoti a pagalbinė kolona norėdami sujungti kelis laukus ir naudoti šiuos laukus kaip kelis kriterijus VLOOKUP. Toliau pateiktame pavyzdyje B stulpelis yra pagalbinė stulpelis, kuris sujungti vardą ir pavardę kartu su šia formule:
=C5&D5 // helper column
VLOOKUP sukonfigūruotas taip, kad sukurtų paieškos vertę. H6 formulė yra tokia:
= VLOOKUP (H4&H5,B5:E13,4,0)
Norėdami gauti daugiau informacijos, žr. šį pavyzdį .
Pastaba: INDEX ir MATCH ir XLOOKUP yra patikimesni būdai, kaip atlikti paieškas pagal kelis kriterijus.
VLOOKUP ir #N/A klaidos
Jei naudosite VLOOKUP, neišvengiamai susidursite su klaida #N/A. Klaida #N/A reiškia „nerasta“. Pavyzdžiui, žemiau esančiame ekrane peržiūros lentelėje nėra paieškos vertės „Žaislų istorija 2“, o visos trys VLOOKUP formulės pateikia #N/A:
Vienas iš būdų „įkalinti“ NA klaidą yra naudoti IFNA funkcija kaip šitas:
H6 formulė yra tokia:
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'Not found')
Pranešimą galima pritaikyti pagal pageidavimą. Norėdami nieko negrąžinti (t. Y. Rodyti tuščią rezultatą), kai VLOOKUP grąžina #N/A, galite naudoti tuščią eilutę:
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'') // no message
Klaida #N/A yra naudinga, nes ji nurodo, kad kažkas negerai. Praktiškai yra daug priežasčių, kodėl galite pamatyti šią klaidą, įskaitant:
- Ieškos vertės lentelėje nėra
- Ieškos vertė yra neteisingai parašyta arba joje yra daugiau vietos
- Atitikimo režimas yra tikslus, tačiau turėtų būti apytikslis
- Lentelės diapazonas įvestas neteisingai
- Kopijuojate VLOOKUP ir lentelę nuoroda nėra užrakinta
Skaityti daugiau: VLOOKUP be #N/A klaidų
Daugiau apie VLOOKUP
Kitos pastabos
- Range_lookup kontroliuoja, ar vertės reikia tiksliai sutapti ar ne. Numatytasis yra TRUE = leisti netikslią atitiktį.
- Nustatyti range_lookup KLAIDAI reikalauti tiksli atitiktis ir TIKRA leisti netikslią atitiktį .
- Jei range_lookup yra TRUE (numatytasis nustatymas), dėl netikslios atitikties funkcija VLOOKUP atitiks artimiausią lentelės vertę vis tiek mažiau nei vertės .
- Kada range_lookup yra praleista, funkcija VLOOKUP leis netikslią atitiktį, tačiau ji naudos tikslią atitiktį, jei tokia yra.
- Jei range_lookup yra TRUE (numatytasis nustatymas), įsitikinkite, kad paieškos vertės pirmoje lentelės eilutėje surūšiuotos didėjančia tvarka. Priešingu atveju VLOOKUP gali grąžinti neteisingą ar netikėtą vertę.
- Jei range_lookup yra FALSE (reikalaujama tikslios atitikties), vertės pirmame stulpelyje lentelę nereikia rūšiuoti.
