Sveiki! Įsivaizduokite, kad „Excel“ turite tūkstančius darbuotojų duomenų eilučių, o jūsų viršininkas atsitiktinai klausia jūsų atlyginimo ir Rameshas. Arba dar blogiau, jis pateikia jums šimto darbuotojų sąrašą ir prašo atsiųsti išsamią informaciją apie šiuos darbuotojus. Ką tu darytum? Negalite jo rasti rankiniu būdu. Tai tiesiog neįmanoma ir visai nėra protinga. Bet tu esi protingas, todėl esi čia. Leiskite supažindinti jus su „Excel“ VLOOKUP funkcija.
Kodėl mums reikia VLOOKUP?
Kas yra VLOOKUP? VLOOKUP reiškia vertikalią paiešką.
VLOOKUP tiesiog ieško nurodytos vertės eilutės pirmajame lentelės stulpelyje ir grąžina prašomos stulpelio vertę toje eilutėje.
VLOOKUP sintaksė:
= VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])
Lookup_value: Vertė, pagal kurią norite ieškoti pirmajame lentelės masyvo stulpelyje.
Lentelės_masyvas: Lentelė, kurioje norite ieškoti/ieškoti
col_index_number: Lentelės masyvo stulpelio numeris, iš kurio norite gauti rezultatus.
[range_lookup]: NETIESA, jei norite ieškoti tikslios vertės, TIESA, jei norite apytikslės atitikties.
Gerai, užteks teorijos. Pereikime prie „Excel VLOOKUP“ pavyzdžio.
VLOOKUP 1 pavyzdys.
„Excel“ lape turite šiuos darbuotojų duomenis. Pirmame stulpelyje (A stulpelis) turite darbuotojų vardus. Toliau turite jų pavadinimą ir pan., Kaip parodyta paveikslėlyje žemiau.
Kad būtų lengviau, pasiėmiau nedidelį stalą.
Dabar jūsų viršininkas viršininkas atsiuntė jums šį sąrašą ir paprašė išsamios informacijos tuščiuose stulpeliuose.
Turite pasakyti Ramesh ir Divya atlyginimus ir paskyrimus. Kaip tai padarytumėte?
Norėdami tai padaryti, galite ieškoti rankiniu būdu Arba … Arba galite naudoti „Excel VLOOKUP“ funkciją.
Tiesiog parašykite šią VLOOKUP formulę langelyje H2 ir nukopijuokite ją į žemiau esantį langelį:
= VLOOKUP (G3, $ A $ 3: $ D $ 11,4, FALSE)
Jūs gausite rezultatus, kaip parodyta žemiau.
Paaiškinimas:
Čia pasakėme „Excel“, kad ieškotų Ramzho (G3) pirmajame stulpelyje (A) $ A $ 3 lentelė: $ D $ 11.
Jei duota vertė randama, grąžinkite ketvirtąją (4) stulpelyje ant tos lentelės.
Suteikdami FALSE į range_lookup, jūs liepiate VLOOKUP rasti tikslią atitiktį (No ifs n buts).
Čia Rameshas randamas pirmoje A skiltyje, o jo atlyginimas grąžinamas kaip 105347.
Panašiai VLOOKUP stulpelyje ieško Divya ir grįžta #N/A klaida, nes Divya įrašo jūsų lentelėje nėra.
Ar čia matote kitą VLOOKUP naudojimą? Naudodami funkciją VLOOKUP galite sužinoti, ar sąraše yra vertė, ar ne. Taip pat galite suderinti du sąrašus ir žinoti, kokios vertybės juose yra bendros.
„Pro“ patarimas: visada užrakinkite „Table_array“ nuorodą, kai naudojate „VLOOKUP“ programoje „Excel“. Priešingu atveju lentelės matrica pasikeis, kai nukopijuosite formulę į kitus langelius.
Pagal numatytuosius nustatymus, kai VLOOKUP iš kito lapo ar darbaknygės, bet tame pačiame lape, turite tai padaryti rankiniu būdu.
Ar galite parašyti VLOOKUP formulę, kad gautumėte Ramesh paskyrimą? Komentarų skiltis yra jūsų.
Anksčiau pateiktame pavyzdyje matėme VLOOKUP pavyzdį, kuriame norėjome tikslios atitikties. Bet ką daryti, jei norime sužinoti apytikslę atitiktį?
Kada apytikslę atitiktį VLOOKUP naudojate kaip Range_lookup?
Supraskime tai naudodami kitą VLOOKUP pavyzdį.
2 pavyzdys: apytikslės atitikties naudojimas VLOOKUP
Anksčiau naudodami VLOOKUP teisingai atsakėte į savo viršininko užklausą. Dabar jūsų „brangus“ viršininkas nori sužinoti artimiausią atlyginimą iki 150000 (tik mažiau nei).
Norėdami atsakyti į tai, mes vėl naudosime „VLOOKUP“, bet šį kartą su apytiksle atitiktimi.
Svarbi pastaba: Norėdami rasti apytikslę atitiktį, turite surūšiuoti duomenis. Čia surūšiavau duomenis didėjančia tvarka pagal atlyginimą.
Ląstelėje H2 parašykite šią formulę ir vilkite ją į žemiau esantį langelį:
= VLOOKUP (G9, $ 3 $: $ D $ 11,1,1)
Pastaba: 1 yra aiškinama kaip TIESA ir 0 kaip NETIESA „Excel“.
Galų gale turėsite tokį rezultatą:
Paaiškinimas.
Mūsų paieškos vertė yra 150000 (G9) ir mes norime artimiausio atlyginimo.
Mūsų asortimentas yra 3 USD: 11 USD nes norime grąžinti vertę iš to paties stulpelio. Todėl col_index_num taip pat yra 1. Ir kadangi mes norime apytikslės atitikties, mes pateikėme range_lookup as 1 (TIKRA).
Norint gauti apytikslę atitiktį, jūsų duomenys turi būti surūšiuoti. Priešingu atveju „Excel VLOOKUP“ bus neteisingas rezultatas.
Svarbios pastabos:
-
- VLOOKUP visada veikia iš kairės į dešinę. Negalite gauti vertės iš pirmojo stulpelio dešinės.
- Stulpelių numeracija prasideda nuo pasirinkto diapazono. Pavyzdžiui, jei pateikiate lentelės masyvą D4: F10. Tada stulpelių skaičius prasidės D, o ne nuo faktinės lentelės pradžios.
- Visada užrakinkite lentelės nuorodą naudodami $ ženklą, kad išvengtumėte klaidų kopijuodami „V“ paieškos formulę „Microsoft Excel 2016“.
- Paieškos vertėje galite naudoti pakaitos simbolį (*), kai prisimenate tik tam tikrą paieškos vertės dalį.
- Nustatykite Range_Lookup į FALSE (0), kad tiksliai atitiktų jūsų vertę.
- Nustatykite „Range_Lookup“ į „TRUE“ (1), kad artimiausia būtų mažesnė nei nurodyta vertė.
- Visada surūšiuokite duomenis didėjančia tvarka, kad apytiksliai atitiktų.
Dabar galite atsakyti į savo viršininko užklausą per kelias sekundes. Galite lengvai palyginti du sąrašus. Masinę paiešką galite atlikti naudodami „VLOOKUP“ programoje „Excel 2016“, 2013, 2010 ir senesnėse „Excel“ versijose.
Taigi ar tai buvo naudinga? Jei ne, praneškite man tikslią užklausą komentarų skiltyje.
17 dalykų apie „Excel“ VLOOKUP
„Vlookup“ 5 populiariausios vertės su pasikartojančiomis vertėmis naudojant „INDEX-MATCH“ programoje „Excel“
VLOOKUP Kelios vertės
VLOOKUP su dinaminiu kolonų indeksu
Dalinis atitikimas su VLOOKUP funkcija
Naudokite VLOOKUP iš dviejų ar daugiau peržiūros lentelių
„Vlookup“ pagal datą „Excel“
Naudojant VLOOKUP formulę, norint patikrinti, ar yra vertė
Populiarūs straipsniai
Kaip VLOOKUP iš skirtingų „Excel“ lapų
VLOOKUP su skaičiais ir tekstu
IF, ISNA ir VLOOKUP funkcija
ISNA ir VLOOKUP funkcija
IFERROR ir VLOOKUP funkcija