Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodo, kaip naudotis „Excel VLOOKUP“ funkcija „Excel“, kad surastumėte vertę.
VLOOKUP funkcijų apžvalga
„VLOOKUP“ funkcija „Vlookup“ reiškia vertikalią paiešką. Ji ieško vertės kairiajame lentelės stulpelyje. Tada grąžina vertę nurodytą skaičių stulpelių į dešinę nuo rastos vertės. Tai tas pats kaip „hlookup“, išskyrus tai, kad vertės ieškomos vertikaliai, o ne horizontaliai.
(Atkreipkite dėmesį, kaip atrodo formulės įvestys)
Funkcijos VLOOKUP sintaksė ir argumentai:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
lookup_value - Vertė, kurios norite ieškoti.
table_array - Duomenų diapazonas, kuriame yra norima ieškoti vertė ir vertė, kurią norite grąžinti „vlookup“. Stulpelis, kuriame yra paieškos vertės, turi būti kairėje.
col_index_num - Duomenų diapazono, iš kurio norite grąžinti vertę, stulpelio numeris.
range_lookup - Tiesa ar melas. FALSE ieško tikslios atitikties. TRUE ieško artimiausios atitikties, kuri yra mažesnė arba lygi lookup_value. Jei pasirinkta TRUE, kairėje esantis stulpelis (paieškos stulpelis) turi būti surūšiuotas didėjančiai (nuo mažiausio iki aukščiausio).
Kas yra VLOOKUP funkcija?
Kaip viena iš senesnių funkcijų skaičiuoklių pasaulyje, naudojama funkcija VLOOKUP Vertinis Peržiūros. Jis turi keletą apribojimų, kurie dažnai įveikiami naudojant kitas funkcijas, pvz., INDEX/MATCH. Tačiau ji turi vieną funkciją, kuri gali atlikti tikslios atitikties paiešką. Tai taip pat yra viena iš pirmųjų funkcijų, apie kurias žmonės sužino.
Pagrindinis pavyzdys
Pažvelkime į pažymių knygos duomenų pavyzdį. Išnagrinėsime keletą pavyzdžių, kaip išgauti informaciją konkretiems studentams.
Jei norime sužinoti, kuriai klasei priklauso Bobas, parašytume formulę:
| 1 | = VLOOKUP („Bob“, A2: C5, 2, FALSE) |
Svarbu atsiminti, kad mūsų ieškomas elementas (Bobas) turi būti pirmame paieškos diapazono stulpelyje (A2: C5). Mes pasakėme funkcijai, kad norime grąžinti vertę iš 2antra stulpelį, kuris šiuo atveju yra stulpelis B. Galiausiai nurodėme, kad norime padaryti an tikslus atitikimas paskutiniu argumentu pateikdami klaidingą. Čia atsakymas bus „Skaitymas“.
Šoninis patarimas: Galutiniame argumente vietoj klaidingo galite naudoti skaičių 0, nes jie turi tą pačią reikšmę. Kai kuriems žmonėms tai labiau patinka, nes greičiau rašoma. Tiesiog žinokite, kad abu yra priimtini.
Perkelti duomenys
Norėdami patikslinti pirmąjį pavyzdį, paieškos elementas neturi būti skaičiuoklės A stulpelyje, o tik pirmajame paieškos diapazono stulpelyje. Naudokime tą patį duomenų rinkinį:
Dabar suraskime gamtos mokslų klasės pažymį. Mūsų formulė būtų
| 1 | = VLOOKUP („Mokslas“, B2: C5, 2, FALSE) |
Tai vis dar galiojanti formulė, nes pirmasis mūsų paieškos diapazono stulpelis yra B stulpelis, kuriame rasite mūsų paieškos terminą „Mokslas“. Grąžiname vertę iš 2antra paieškos diapazono stulpelį, kuris šiuo atveju yra C stulpelis. Tada atsakymas yra „A-“.
Pakaitos simbolių naudojimas
Funkcija VLOOKUP palaiko pakaitos simbolių „*“ ir „?“ Naudojimą darant paieškas. Pavyzdžiui, tarkime, kad pamiršome, kaip parašyti Franko vardą, ir tiesiog norėjome ieškoti vardo, prasidedančio „F“. Galėtume parašyti formulę
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
Taip būtų galima rasti pavadinimą Frank 5 eilutėje ir grąžinti reikšmę iš 2antra santykinis stulpelis. Šiuo atveju atsakymas bus „mokslas“.
Netiksli atitiktis
Dažniausiai norėsite įsitikinti, kad paskutinis VLOOKUP argumentas yra klaidingas (arba 0), kad gautumėte tikslią atitiktį. Tačiau kartais galite ieškoti netikslios atitikties. Jei turite surūšiuotų duomenų sąrašą, taip pat galite naudoti VLOOKUP, kad grąžintumėte tos pačios arba kitos mažesnės prekės rezultatą. Tai dažnai naudojama sprendžiant didėjančius skaičių diapazonus, pvz., Mokesčių lentelėje ar komisinių premijas.
Tarkime, kad norite rasti pajamų, įvestų į D2 langelį, mokesčių tarifą. D4 formulė gali būti tokia:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
Šios formulės skirtumas yra tas, kad paskutinis mūsų argumentas yra „tiesa“. Mūsų konkrečiame pavyzdyje matome, kad kai mūsų individas gauna 45 000 USD pajamų, jiems bus taikomas 15%mokesčių tarifas.
Pastaba: Nors paprastai norime tikslios atitikties, o argumentas yra „False“, pamirštate nurodyti 4tūkst argumentas VLOOKUP, numatytasis yra „True“. Dėl to galite gauti netikėtų rezultatų, ypač kai kalbate apie teksto reikšmes.
Dinaminis stulpelis
VLOOKUP reikalauja pateikti argumentą, nurodant, iš kurio stulpelio norite grąžinti vertę, tačiau tokia proga gali atsirasti, kai nežinote, kur bus stulpelis, arba norite leisti vartotojui pakeisti, iš kurio stulpelio grįžti. Tokiais atvejais stulpelio numeriui nustatyti gali būti naudinga naudoti funkciją MATCH.
Dar kartą apsvarstykime savo pažymių knygos pavyzdį su kai kuriomis įvestimis E2 ir E4. Norėdami gauti stulpelio numerį, galime parašyti formulę
| 1 | = ATITIKTI (E2, A1: C1, 0) |
Taip bus bandoma rasti tikslią „Įvertinimo“ padėtį diapazone A1: C1. Atsakymas bus 3. Žinodami tai, galime prijungti jį prie VLOOKUP funkcijos ir E6 parašyti tokią formulę:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Taigi, funkcija MATCH bus įvertinta iki 3, ir tai nurodys VLOOKUP grąžinti rezultatą iš 3rd stulpelį A2: C5 diapazone. Apskritai gauname norimą „C“ rezultatą. Mūsų formulė dabar yra dinamiška, nes galime pakeisti stulpelį, į kurį reikia žiūrėti, arba pavadinimą, kurio ieškoti.
VLOOKUP apribojimai
Kaip minėta straipsnio pradžioje, didžiausias „VLOOKUP“ trūkumas yra tas, kad reikia, kad paieškos terminas būtų surastas kairiajame paieškos diapazono stulpelyje. Nors yra keletas išgalvotų triukų, kuriuos galite padaryti, kad tai įveiktumėte, įprasta alternatyva yra naudoti INDEX ir MATCH. Šis derinys suteikia daugiau lankstumo ir kartais netgi gali būti greitesnis skaičiavimas.
VLOOKUP „Google“ skaičiuoklėse
Funkcija VLOOKUP „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
papildomi užrašai
Naudokite funkciją VLOOKUP, kad atliktumėte vertikalų paiešką. VLOOKUP ieško tikslios atitikties (range_lookup = FALSE) arba artimiausia atitiktis, lygi arba mažesnė už lookup_value (range_lookup = TRUE, tik skaitinės vertės) pirmoje lentelės_masyvo eilutėje. Tada ji grąžina atitinkamą reikšmę, n eilučių skaičių žemiau atitikties.
Jei norite rasti tikslią atitiktį naudodami VLOOKUP, pirmiausia nustatykite identifikavimo vertę, kurios norite ieškoti kaip lookup_value. Ši identifikavimo vertė gali būti SSN, darbuotojo ID, vardas ar kitas unikalus identifikatorius.
Toliau apibrėžiate diapazoną (vadinamą table_array), kuriame yra identifikatoriai viršutinėje eilutėje ir visos reikšmės, kurių galiausiai norite ieškoti žemiau esančiose eilutėse. SVARBU: unikalūs identifikatoriai turi būti viršutinėje eilutėje. Jei jų nėra, turite arba perkelti eilutę į viršų, arba vietoj VLOOKUP naudoti MATCH / INDEX.
Trečia, apibrėžkite eilutės numerį (eilutės_indeksas) iš table_array kad nori sugrįžti. Atminkite, kad pirmoji eilutė, kurioje yra unikalūs identifikatoriai, yra 1 eilutė. Antroji eilutė yra 2 eilutė ir t. T.
Galiausiai turite nurodyti, ar ieškoti tikslios atitikties (FALSE), ar artimiausios atitikties (TRUE) range_lookup. Jei pasirinkta tikslios atitikties parinktis ir nerasta tiksli atitiktis, grąžinama klaida (#N/A). Jei norite, kad formulė būtų tuščia arba „nerasta“, arba bet kokia kita vertė, o ne klaidos vertė (#N/A), naudokite funkciją IFERROR su VLOOKUP.
Jei norite naudoti apytikslę atitikties rinkinį, naudodami funkciją VLOOKUP: range_lookup = TIESA. Ši parinktis galima tik skaitinėms reikšmėms. Vertės turi būti surūšiuotos didėjančia tvarka.
Norėdami atlikti horizontalią paiešką, naudokite funkciją HLOOKUP.
VLOOKUP VBA pavyzdžiai
Taip pat galite naudoti VLOOKUP funkciją VBA. Tipas:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Funkcijos argumentams („lookup_value“ ir pan.) Galite juos įvesti tiesiai į funkciją arba nustatyti kintamuosius, kuriuos naudoti.
Grįžkite į visų „Excel“ funkcijų sąrašą
