"Excel" VLOOKUP funkcija naudojama informacijos paieškai ir grąžai iš duomenų lentelės, pagrįstos pasirinkta paieškos verte.
Paprastai VLOOKUP reikalauja, kad paieškos vertė būtų kairiojoje lentelės stulpelyje, o funkcija grąžina kitą duomenų lauką, esantį toje pačioje eilutėje dešinėje šios vertės.
Tačiau, derinant VLOOKUP su funkcija CHOOSE, galima sukurti kairiosios paieškos formulę, kuri:
- Patikrina paieškos vertę iš bet kokio duomenų lentelės stulpelio
- Grąžina informaciją, esančią bet kuriame stulpelyje kairėje paieškos aplanko
Pastaba: Šios instrukcijos taikomos "Excel" versijoms 2019, 2016, 2013, 2010, "Excel" ir "Office 365".
01 iš 05Mokomoji medžiaga: naudojamas VLOOKUP ir PASIRINKITE funkcijas kairiojoje paieškos formoje
Norėdami sukurti kairiojo paieškos formą, matomą pavyzdiniame paveikslėlyje, naudokite šią formulę:
= VLOOKUP ($ D $ 2, CHOOSE ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
Šiame pavyzdyje formulė leidžia rasti dalį, kurią teikia skirtingos bendrovės, išvardytos duomenų lentelės 3 skiltyje.
Formulės CHOOSE funkcija yra sugadinti VLOOKUP, manydama, kad 3 stulpelis yra 1 stulpelis. Dėl to įmonės pavadinimas gali būti naudojamas kaip paieškos vertė, norint rasti kiekvienos įmonės pateiktos dalies pavadinimą.
Įveskite mokymo duomenis
- Įveskite antraštę Tiekėjas į ląstelę D1.
- Įveskite antraštę Dalis ląstelėje E1.
- Į lentelę įveskite lentelę, esančią paveikslėlyje aukščiau Nuo D4 iki F9
- Eilutės 2 ir 3 yra paliktas tuščias, kad atitiktų paieškos kriterijus ir kairiosios paieškos formulę, sukurtą per šią mokymo programą
Atidarykite VLOOKUP dialogo langą
Nors į darbo lapą galima įrašyti formulę tiesiai į ląstelę F1, daugeliui žmonių sunku suformuluoti sintaksę.
Šiuo atveju saugiau naudoti dialogo langą VLOOKUP. Beveik visi "Excel" funkcijos turi dialogo langą, pagal kurį galite įvesti kiekvieną funkcijos argumentą atskiroje eilutėje.
- Spustelėkite langelį E2 darbalapio. E2 yra vieta, kurioje bus rodomos kairiojo paieškos formos rezultatai.
- Spustelėkite ant Formulės juostos juosta.
- Spustelėkite ant Ieškoti ir nuoroda parinktis juostoje, kad atidarytumėte išskleidžiamąjį sąrašo funkciją.
- Spustelėkite VLOOKUP sąraše, kad pamatytumėte funkcijos dialogo langą.
Argumentų įvedimas į dialogo langą VLOOKUP
Funkcijos argumentai yra vertės, naudojamos funkcijai apskaičiuoti rezultatą.
Funkcijos dialogo lange kiekvieno argumento pavadinimas yra atskiroje eilutėje, o po to - laukas, į kurį reikia įvesti vertę.
Įveskite šias vertes kiekvienam iš VLOOKUP argumentų teisingoje dialogo lango eilutėje, kaip parodyta pridėtame paveikslėlyje.
"Lookup Value"
Paieškos vertė yra informacijos laukas, naudojamas ieškant lentelių masyvo. VLOOKUP grąžina kitą duomenų lauką iš tos pačios eilutės kaip paieškos reikšmė.
Šiame pavyzdyje naudojama ląstelių nuoroda į vietą, kurioje įmonės pavadinimas bus įtrauktas į darbalapį. Tai yra tai, kad lengva pakeisti įmonės pavadinimą be redagavimo formulės.
- Spustelėkite ant lookup_value dialogo lango eilutė.
- Spustelėkite langelį D2 pridėti šią langelį nuoroda į lookup_value linija
- Paspauskite F4 klaviatūros klavišą, kad ląstelių atskaitos lygis būtų absoliutus - $ D $ 2.
Absoliutus ląstelių nuorodos
Absoliučios ląstelių nuorodos naudojamos paieškos vertei ir lentelės masyvo argumentams, kad išvengtumėte klaidų, jei paieškos formule nukopijuojama į kitas lakštas.
Įeikite į CHOICE funkciją
Stalo masyvo argumentas yra gretimų duomenų blokas, iš kurio gaunama konkreti informacija.
Paprastai VLOOKUP žiūri į dešinę nuo paieškos vertės argumentų, norėdami rasti duomenis lentelės masyvo. Jei norite, kad jis atrodytų kairėje, VLOOKUP turi būti apgaulingas pakeitus stulpelių lentelės masyvą naudodami funkciją CHOOSE.
Šioje formulėje funkcija CHOOSE vykdo dvi užduotis:
- Tai sukuria lentelę, kuri yra tik dviejų pločių stulpelių (D ir F stulpeliai).
- Jis keičia lentelės masyvo stulpelių dešinę į kairę, kad stulpelis F būtų pirmas, o stulpelis D yra antra.
Funkcijų įvedimas
Įvesdami funkcijas rankiniu būdu, kiekviena iš funkcijų argumentų turi būti atskirta kableliu.
- Dialogo lange VLOOKUP funkcijos spustelėkite mygtuką Table_array linija
- Įveskite toliau nurodytą informaciją PASIRINKITE funkcija:PASIRINKITE ({1,2}, $ F: $ F, $ D: $ D)
Stulpelio indekso numeris
Paprastai stulpelio indekso numeris nurodo, kuris stulpelio masyvo stulpelis turi duomenis, kuriuos jūs gaunate. Tačiau šioje formulėje kalbama apie stulpelių tvarką, kurią nustato funkcija CHOOSE.
Funkcija CHOOSE sukuria lentelės masyvą, kuris yra dviejų pločių pločio, o stulpelis F, pirmiausia po stulpeliu D. Kadangi reikalaujama informacija - dalies pavadinimas yra D stulpelyje, stulpelių rodyklės argumento reikšmė turi būti 2.
- Spustelėkite ant Col_index_num dialogo lango eilutė.
- Įveskite a 2 šioje eilutėje.
Range Lookup
VLOOKUP "Range_lookup" argumentas yra loginė reikšmė (tik TRUE arba FALSE), kuri nurodo, ar norite, kad VLOOKUP surastų tikslią arba apytikstą atitikimą paieškos vertei.
- Jei TRUE arba jei šis argumentas yra praleistas, VLOOKUP grąžina tikslų atitikimą "Lookup_value" arba, jei tikslios atitikties nerasta, VLOOKUP grąžina kitą didžiausią vertę. Kad tai atliktų formulė, lentelės_ražinio pirmojo stulpelio duomenys turi būti rūšiuojami didėjančia tvarka.
- Jei FALSE, VLOOKUP naudoja tik tikslią atitikmenį "Lookup_value". Jei pirmajame "Table_array" stulpelyje, kuris atitinka paieškos vertę, yra dvi ar daugiau verčių, naudojama pirmoji rasta reikšmė. Jei tikslios atitikties nerasta, grąžinama # N / A klaida.
Šiame vadove, kadangi mes ieškome tam tikros dalies pavadinimo, "Range_lookup" yra nustatytas į Klaidinga kad formulė grąžintų tik tikslius atitikmenis.
- Spustelėkite ant "Range_lookup" dialogo lango eilutė.
- Įveskite žodį Klaidinga šioje eilutėje nurodykite, kad mes norime, kad VLOOKUP grąžintų tikslią atitin kamą informaciją, kurią mes ieškome.
- Spustelėkite Gerai užpildyti kairiosios paieškos formules ir uždaryti dialogo langą.
- Kadangi dar nepateikėme įmonės pavadinimo į D2 langelį, lange E2 atsiranda # N / A klaida.
Duomenų grąžinimas naudojant kairę paieškos formą
Norėdami sužinoti, kurios įmonės pateikia kokias dalis, įveskite įmonės pavadinimą į langelį D2 ir paspauskite ENTER raktas ant klaviatūros.
Dalies pavadinimas rodomas lange E2.
- Spustelėkite langelį D2 savo darbalapyje.
- Įveskite "Gadgets Plus" į ląstelę D2 ir paspauskite ENTER raktas ant klaviatūros.
- Tekstas "Gadgets" - dalis, kurią pateikia bendrovė "Gadgets Plus", turėtų būti rodoma lange E2.
Toliau išbandykite paieškos formą, įvesdami kitų kompanijų pavadinimus į ląstelę D2, o atitinkama dalis pavadinimą turėtų pasirodyti ląstelėje E2.
Jei lange E2 pasirodo klaidos pranešimas, pvz., # N / A, patikrinkite rašybos klaidų langelyje D2.
04 iš 05Dviejų stulpelių lentelės masyvo kūrimas
Funkcijos CHOOSE sintaksė yra:
= PASIRINKTI (Index_number, Value1, Value2, … Value254)
Funkcija CHOOSE paprastai grąžina vertę iš vertės sąrašo (Value1 į Value254), atsižvelgiant į įvestą indekso numerį.
Jei indekso numeris yra 1, funkcija grąžina vertę 1 iš sąrašo; jei indekso numeris yra 2, funkcija grąžina reikšmę 2 iš sąrašo ir tt
Įvedus kelis indekso numerius, funkcija grąžina keletą reikšmių bet kuria norima tvarka. Kaip pasirinkti CHASE, kad grąžintų keletą reikšmių, atliekama kuriant masyvą.
Norėdami įvesti masyvą, supjuokite numerius, įvestus su garbančiais petnešomis ar skliaustuose. Rodyklės numeriu įvedami du numeriai:{ 1,2 }.
Reikėtų pažymėti, kad pasirenkama ne tik dviejų stulpelių lentelės sukūrimas. Įtraukdami papildomą skaičių masyve, pvz., {1,2,3} ir papildomą reikšmių argumento diapazoną, CHOOSE sukuria trijų stulpelių lentelę.
Papildomi stulpeliai leidžia jums grąžinti skirtingą informaciją su kairiojo paieškos formulu, pakeisdami VLOOKUP stulpelio indekso skaičiaus argumentą į stulpelio, kuriame yra norima informacija, numerį.
05 iš 05Pakeiskite stulpelių tvarką pasirinkdami funkciją "CHOOSE"
Pasirinktoje funkcijoje CHASE pasirinkite šią formulę:
PASIRINKITE ({1,2}, $ F: $ F, $ D: $ D)
stulpelio F diapazonas yra nurodytas prieš stulpelį D.
Kadangi funkcija CHOOSE nustato VLOOKUP lentelių masyvą (šios funkcijos duomenų šaltinį), stulpelių tvarka funkcijoje CHOOSE perduodama kartu su VLOOKUP.
Kalbant apie VLOOKUP, stalo masyvas yra tik dviejų pločių pločio, o stulpelis F kairėje ir stulpelis D dešinėje. Kadangi stulpelyje F yra įmonės, kurią mes norime ieškoti, pavadinimas, ir kadangi stulpelyje D yra dalių pavadinimai, VLOOKUP gali atlikti savo įprastą paieškos užduotį, ieškodamas duomenų, esančių kairėje paieškos vertei.
Dėl to VLOOKUP gali naudoti įmonės pavadinimą, norėdamas rasti dalį, kurią jie tiekia.
