Turinys
- Funkcija VLOOKUP programoje Excel – bendras aprašymas ir sintaksė
- VLOOKUP pavyzdžiai
- Tiksli arba apytikslė atitiktis funkcijai VLOOKUP
- VLOOKUP programoje Excel – turite tai atsiminti!
Šiandien pradedame straipsnių seriją, kurioje aprašoma viena iš naudingiausių „Excel“ funkcijų VPR (VLOOKUP). Ši funkcija kartu yra viena iš sudėtingiausių ir mažiausiai suprantamų.
Šioje pamokoje apie VPR Stengsiuosi kuo paprasčiau išdėstyti pagrindus, kad mokymosi procesas būtų kuo aiškesnis nepatyrusiems vartotojams. Be to, išnagrinėsime kelis pavyzdžius su Excel formulėmis, kurie parodys dažniausiai pasitaikančius funkcijos naudojimo atvejus VPR.
Funkcija VLOOKUP programoje Excel – bendras aprašymas ir sintaksė
Taigi kas tai VPR? Na, visų pirma, tai yra „Excel“ funkcija. Ką ji veikia? Jis suranda jūsų nurodytą reikšmę ir grąžina atitinkamą reikšmę iš kito stulpelio. Techniškai kalbant, VPR suranda reikšmę pirmajame nurodyto diapazono stulpelyje ir pateikia rezultatą iš kito stulpelio toje pačioje eilutėje.
Dažniausiai taikant funkciją VPR duomenų bazėje ieško nurodyto unikalaus identifikatoriaus ir iš duomenų bazės ištraukia tam tikrą su juo susijusią informaciją.
Pirmoji raidė funkcijos pavadinime VPR (VLOOKUP) reiškia Вvertikaliai (Vvertikali). Pagal tai galima atskirti VPR nuo GPR (HLOOKUP), kuri ieško reikšmės viršutinėje diapazono eilutėje − Гhorizontaliai (Hhorizontaliai).
Funkcija VPR galima naudoti „Excel 2013“, „Excel 2010“, „Excel 2007“, „Excel 2003“, „Excel XP“ ir „Excel 2000“.
Funkcijos VLOOKUP sintaksė
Funkcija VPR (VLOOKUP) turi tokią sintaksę:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Kaip matote, funkcija VPR „Microsoft Excel“ turi 4 parinktis (arba argumentus). Pirmieji trys yra privalomi, paskutinis yra neprivalomas.
- lookup_value (lookup_value) – vertė, kurios reikia ieškoti. Tai gali būti reikšmė (skaičius, data, tekstas) arba langelio nuoroda (su peržvalgos verte) arba vertės, kurią grąžina kuri nors kita „Excel“ funkcija. Pavyzdžiui, ši formulė ieškos vertės 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Jei paieškos reikšmė yra mažesnė už mažiausią reikšmę pirmame ieškomo diapazono stulpelyje, funkcija VPR praneš apie klaidą #AT (#N/A).
- table_array (lentelė) – du ar daugiau duomenų stulpelių. Prisiminkite funkciją VPR visada ieško reikšmės pirmame argumente nurodyto diapazono stulpelyje table_array (lentelė). Rodomame diapazone gali būti įvairių duomenų, tokių kaip tekstas, datos, skaičiai, loginiai skaičiavimai. Funkcija neskiria didžiųjų ir mažųjų raidžių, tai reiškia, kad didžiosios ir mažosios raidės laikomos vienoda. Taigi mūsų formulė ieškos vertės 40 ląstelėse iš A2 į A15, nes A yra pirmasis argumente nurodyto diapazono A2:B15 stulpelis table_array (lentelė):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (stulpelio_numeris) yra nurodyto diapazono stulpelio, iš kurio bus grąžinta reikšmė rastoje eilutėje, numeris. Kairiausias nurodyto diapazono stulpelis yra 1, antrasis stulpelis yra 2, trečiasis stulpelis yra 3 ir taip toliau. Dabar galite perskaityti visą formulę:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Formulė ieško vertės 40 diapazone A2: A15 ir grąžina atitinkamą reikšmę iš B stulpelio (nes B yra antras stulpelis diapazone A2:B15).
Jei argumento reikšmė col_index_num (stulpelio_numeris) mažesnis nei 1tada VPR praneš apie klaidą #VALUE! (#VALUE!). Ir jei jis yra didesnis nei diapazono stulpelių skaičius table_array (lentelė), funkcija grąžins klaidą #REF! (#LINK!).
- range_lookup (range_lookup) – nustato, ko ieškoti:
- tiksli atitiktis, argumentas turi būti lygus FALSE (NELAIDAS);
- apytikslis atitikmuo, argumentas lygus TIKRAS KODAS (TRUE) arba visai nenurodyta.
Šis parametras yra neprivalomas, bet labai svarbus. Vėliau šioje pamokoje VPR Parodysiu keletą pavyzdžių, paaiškinančių, kaip parašyti tikslių ir apytikslių atitikmenų nustatymo formules.
VLOOKUP pavyzdžiai
Tikiuosi funkcija VPR taps jums šiek tiek aiškesnis. Dabar pažvelkime į kai kuriuos naudojimo atvejus VPR formulėse su tikrais duomenimis.
Kaip naudoti VLOOKUP ieškant kitame „Excel“ lape
Praktikoje formulės su funkcija VPR retai naudojami ieškant duomenų tame pačiame darbalapyje. Dažniau ieškosite ir iš kito lapo gausite atitinkamas reikšmes.
Norint naudotis VPR, ieškokite kitame Microsoft Excel lape, Argumente privalote table_array (lentelė) nurodykite lapo pavadinimą su šauktuku ir langelių diapazonu. Pavyzdžiui, ši formulė rodo, kad diapazonas A2: B15 yra lape pavadinimu Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Žinoma, lapo pavadinimo nereikia įvesti rankiniu būdu. Tiesiog pradėkite rašyti formulę, o kai kalbama apie argumentą table_array (lentelė), perjunkite į norimą lapą ir pele pasirinkite norimą langelių diapazoną.
Formulė, parodyta toliau esančioje ekrano kopijoje, darbalapyje ieško teksto „Produktas 1“ A stulpelyje (tai yra 1 diapazono A2:B9 stulpelis). kainos.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Atminkite, kad ieškodami teksto reikšmės, turite ją įdėti į kabutes („“), kaip paprastai daroma „Excel“ formulėse.
Už argumentą table_array (lentelė) pageidautina visada naudoti absoliučias nuorodas (su $ ženklu). Tokiu atveju, kopijuojant formulę į kitus langelius, paieškos diapazonas išliks nepakitęs.
Ieškokite kitoje darbaknygėje naudodami VLOOKUP
Veikti VPR dirbo tarp dviejų „Excel“ darbaknygių, prieš lapo pavadinimą turite nurodyti darbaknygės pavadinimą laužtiniuose skliaustuose.
Pavyzdžiui, žemiau yra formulė, kuri ieško vertės 40 ant lapo Sheet2 knygoje Skaičiai.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Čia yra lengviausias būdas sukurti formulę „Excel“ naudojant VPRkuri nukreipia į kitą darbaknygę:
- Atidarykite abi knygas. Tai nėra būtina, bet taip lengviau sukurti formulę. Jūs nenorite įvesti darbaknygės pavadinimo rankiniu būdu, ar ne? Be to, jis apsaugos jus nuo atsitiktinių rašybos klaidų.
- Pradėkite rašyti funkciją VPRo kai kalbama apie ginčą table_array (lentelė), perjunkite į kitą darbaknygę ir joje pasirinkite reikiamą paieškos diapazoną.
Toliau pateiktoje ekrano kopijoje parodyta formulė, kai paieška nustatyta į darbaknygės diapazoną Kainų sąrašas.xlsx ant lapo kainos.
Funkcija VPR veiks net tada, kai uždarysite ieškomą darbaknygę ir visas kelias į darbaknygės failą pasirodys formulės juostoje, kaip parodyta toliau:
Jei darbaknygės ar lapo pavadinime yra tarpų, jis turi būti parašytas apostrofais:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
Kaip naudoti pavadintą diapazoną arba lentelę formulėse su VLOOKUP
Jei planuojate naudoti tą patį paieškos diapazoną kelioms funkcijoms VPR, galite sukurti pavadintą diapazoną ir įvesti jo pavadinimą į formulę kaip argumentą table_array (lentelė).
Norėdami sukurti pavadintą diapazoną, tiesiog pasirinkite langelius ir įveskite atitinkamą pavadinimą lauke Vardas, formulės juostos kairėje.
Dabar galite užrašyti tokią formulę, kaip sužinoti prekės kainą Produktas 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
Dauguma diapazono pavadinimų tinka visai „Excel“ darbaknygei, todėl nereikia nurodyti argumento lapo pavadinimo table_array (lentelė), net jei formulė ir paieškos diapazonas yra skirtinguose darbalapiuose. Jei jie yra skirtingose darbaknygėse, tada prieš diapazono pavadinimą turite nurodyti darbaknygės pavadinimą, pavyzdžiui, taip:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Taigi formulė atrodo daug aiškesnė, sutinkate? Be to, pavadintų diapazonų naudojimas yra gera alternatyva absoliučioms nuorodoms, nes įvardytas diapazonas nepasikeičia, kai nukopijuojate formulę į kitus langelius. Tai reiškia, kad galite būti tikri, kad paieškos diapazonas formulėje visada išliks teisingas.
Jei ląstelių diapazoną konvertuosite į visavertę „Excel“ skaičiuoklę naudodami komandą Lentelė (Lentelė) skirtuką Įterpimas (Įterpti), tada, kai pasirinksite diapazoną pele, Microsoft Excel automatiškai pridės stulpelių pavadinimus (arba lentelės pavadinimą, jei pasirinksite visą lentelę) į formulę.
Paruošta formulė atrodys maždaug taip:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
O gal net taip:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
Kai naudojate pavadintus diapazonus, nuorodos nukreips į tuos pačius langelius, nesvarbu, kur kopijuotumėte funkciją VPR darbo knygelėje.
Pakaitos simbolių naudojimas VLOOKUP formulėse
Kaip ir su daugeliu kitų funkcijų, VPR Galite naudoti šiuos pakaitos simbolius:
- Klaustukas (?) – pakeičia bet kurį vieną simbolį.
- Žvaigždutė (*) – pakeičia bet kokią simbolių seką.
Pakaitos simbolių naudojimas funkcijose VPR gali būti naudinga daugeliu atvejų, pavyzdžiui:
- Kai tiksliai neprisimenate teksto, kurį turite rasti.
- Kai norite rasti žodį, kuris yra langelio turinio dalis. Žinau, kad VPR ieško pagal viso langelio turinį, tarsi parinktis būtų įjungta Suderinti visą langelio turinį (Visas langelis) standartinėje „Excel“ paieškoje.
- Kai langelyje yra papildomų tarpų turinio pradžioje arba pabaigoje. Esant tokiai situacijai, ilgą laiką galite sukti smegenis, bandydami išsiaiškinti, kodėl formulė neveikia.
1 pavyzdys: ieškoma teksto, kuris prasideda arba baigiasi tam tikrais simboliais
Tarkime, kad toliau pateiktoje duomenų bazėje norite ieškoti konkretaus kliento. Jūs neprisimenate jo pavardės, bet žinote, kad ji prasideda raide „ack“. Čia yra formulė, kuri puikiai atliks darbą:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Dabar, kai esate tikri, kad radote tinkamą pavadinimą, galite naudoti tą pačią formulę, kad surastumėte šio kliento sumokėtą sumą. Norėdami tai padaryti, tiesiog pakeiskite trečiąjį funkcijos argumentą VPR į norimą stulpelio numerį. Mūsų atveju tai yra C stulpelis (3 vieta diapazone):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Čia yra daugiau pavyzdžių su pakaitos simboliais:
~ Raskite vardą, kuris baigiasi „vyras“:
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ Raskite vardą, kuris prasideda „ad“ ir baigiasi „sūnus“:
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ Sąraše randame vardą, kurį sudaro 5 simboliai:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Veikti VPR su pakaitos simboliais veikė teisingai, kaip ketvirtąjį argumentą, kurį visada turėtumėte naudoti FALSE (NETEISINGA). Jei paieškos diapazone yra daugiau nei viena reikšmė, atitinkanti paieškos terminus su pakaitos simboliais, bus grąžinta pirmoji rasta reikšmė.
2 pavyzdys: sujunkite pakaitos simbolius ir langelių nuorodas VLOOKUP formulėse
Dabar pažiūrėkime į šiek tiek sudėtingesnį pavyzdį, kaip ieškoti naudojant funkciją VPR pagal vertę langelyje. Įsivaizduokite, kad A stulpelis yra licencijos raktų sąrašas, o B stulpelis yra pavadinimų, kuriems priklauso licencija, sąrašas. Be to, langelyje C1 turite tam tikro licencijos rakto dalį (kelis simbolius) ir norite rasti savininko vardą.
Tai galima padaryti naudojant šią formulę:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Ši formulė ieško reikšmės iš langelio C1 nurodytame diapazone ir grąžina atitinkamą reikšmę iš B stulpelio. Atminkite, kad pirmame argumente prieš ir po langelio nuoroda naudojame ampersandą (&), kad susietume teksto eilutę.
Kaip matote paveikslėlyje žemiau, funkcija VPR grąžina „Jeremy Hill“, nes jo licencijos rakte yra simbolių seka iš langelio C1.
Atkreipkite dėmesį, kad argumentas table_array (lentelė) aukščiau esančioje ekrano kopijoje yra lentelės pavadinimas (7 lentelė), o ne nurodomas langelių diapazonas. Tai mes padarėme ankstesniame pavyzdyje.
Tiksli arba apytikslė atitiktis funkcijai VLOOKUP
Ir galiausiai atidžiau pažvelkime į paskutinį funkcijos argumentą VPR - range_lookup (interval_view). Kaip minėta pamokos pradžioje, šis argumentas yra labai svarbus. Toje pačioje formulėje su jos verte galite gauti visiškai skirtingus rezultatus TIKRAS KODAS (TRUE) arba FALSE (NETEISINGA).
Pirmiausia išsiaiškinkime, ką Microsoft Excel reiškia tiksliomis ir apytikslėmis atitiktimis.
- Jei argumentas range_lookup (range_lookup) yra lygus FALSE (FALSE), formulė ieško tikslios atitikties, ty tiksliai tos pačios reikšmės, kaip nurodyta argumente lookup_value (paieškos_vertė). Jei pirmajame diapazono stulpelyje tpajėgus_masyvas (lentelė) susiduria su dviem ar daugiau verčių, atitinkančių argumentą lookup_value (paieškos_vertė), tada bus pasirinktas pirmasis. Jei atitikmenų nerasta, funkcija praneš apie klaidą #AT (#N/A). Pavyzdžiui, ši formulė praneš apie klaidą #AT (#N/A), jei diapazone A2:A15 nėra reikšmės 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Jei argumentas range_lookup (range_lookup) yra lygus TIKRAS KODAS (TRUE), formulė ieško apytikslės atitikties. Tiksliau, pirmiausia funkcija VPR ieško tikslios atitikties, o jei nerandama, pasirenka apytikslę. Apytikslis atitikimas yra didžiausia reikšmė, kuri neviršija argumente nurodytos vertės. lookup_value (paieškos_vertė).
Jei argumentas range_lookup (range_lookup) yra lygus TIKRAS KODAS (TRUE) arba nenurodyta, tada pirmame diapazono stulpelyje esančios reikšmės turėtų būti rūšiuojamos didėjančia tvarka, ty nuo mažiausios iki didžiausios. Priešingu atveju funkcija VPR gali grąžinti klaidingą rezultatą.
Norėdami geriau suprasti pasirinkimo svarbą TIKRAS KODAS (TIESA) arba FALSE (FALSE), pažvelkime į dar keletą formulių su funkcija VPR ir pažiūrėkite į rezultatus.
1 pavyzdys: tikslios atitikties radimas naudojant VLOOKUP
Kaip prisimenate, norint ieškoti tikslios atitikties, ketvirtasis funkcijos argumentas VPR turėtų reikšmės FALSE (NETEISINGA).
Grįžkime prie lentelės nuo paties pirmojo pavyzdžio ir išsiaiškinkime, kuris gyvūnas gali judėti dideliu greičiu 50 mylios per valandą. Tikiu, kad ši formulė nesukels jums jokių sunkumų:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Atminkite, kad mūsų paieškos diapazone (A stulpelyje) yra dvi reikšmės 50 – ląstelėse A5 и A6. Formulė grąžina vertę iš langelio B5. Kodėl? Nes ieškant tikslios atitikties, funkcija VPR naudoja pirmą rastą reikšmę, atitinkančią ieškomą.
2 pavyzdys: VLOOKUP naudojimas norint rasti apytikslę atitiktį
Kai naudojate funkciją VPR ieškoti apytikslės atitikties, ty kai argumentas range_lookup (range_lookup) yra lygus TIKRAS KODAS (TRUE) arba praleistas, pirmiausia turite surūšiuoti diapazoną pagal pirmąjį stulpelį didėjančia tvarka.
Tai labai svarbu, nes funkcija VPR grąžina kitą didžiausią reikšmę po nurodytos, o tada paieška sustabdoma. Jei nepaisysite tinkamo rūšiavimo, gausite labai keistus rezultatus arba klaidos pranešimą. #AT (#N/A).
Dabar galite naudoti vieną iš šių formulių:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Kaip matote, noriu išsiaiškinti, kuris iš gyvūnų turi artimiausią greitį 69 mylios per valandą. Ir štai rezultatas, kurį funkcija man grąžino VPR:
Kaip matote, formulė grąžino rezultatą Antilopė (Antilopė), kurios greitis 61 mylių per valandą, nors sąraše taip pat yra Gepardas (Gepardas), kuris bėga greičiu 70 mylių per valandą, o 70 yra arčiau 69 nei 61, ar ne? Kodėl tai vyksta? Kadangi funkcija VPR ieškant apytikslės atitikties, pateikia didžiausią reikšmę, kuri nėra didesnė už ieškomą.
Tikiuosi, kad šie pavyzdžiai šiek tiek paaiškins darbą su funkcija VPR programoje „Excel“ ir tu nebežiūri į ją kaip į pašalinį asmenį. Dabar nepakenks trumpai pakartoti pagrindinius mūsų išstudijuotos medžiagos punktus, kad galėtume geriau ją įrašyti atmintyje.
VLOOKUP programoje Excel – turite tai atsiminti!
- Funkcija VPR „Excel“ negali žiūrėti į kairę. Jis visada ieško reikšmės kairiajame argumento pateikto diapazono stulpelyje table_array (lentelė).
- Veikia VPR visose reikšmėse neskiriamos didžiosios ir mažosios raidės, ty mažos ir didelės raidės yra lygiavertės.
- Jei ieškoma reikšmė yra mažesnė už mažiausią reikšmę pirmame ieškomo diapazono stulpelyje, funkcija VPR praneš apie klaidą #AT (#N/A).
- Jei 3 argumentas col_index_num (stulpelio_numeris) mažesnis nei 1funkcija VPR praneš apie klaidą #VALUE! (#VALUE!). Jei jis didesnis nei diapazono stulpelių skaičius table_array (lentelė), funkcija praneš apie klaidą #REF! (#LINK!).
- Argumente naudokite absoliučias langelių nuorodas table_array (lentelė), kad kopijuojant formulę būtų išsaugotas teisingas paieškos diapazonas. Kaip alternatyvą pabandykite naudoti pavadintus diapazonus arba lenteles programoje „Excel“.
- Atlikdami apytikslės atitikties paiešką atminkite, kad pirmasis ieškomo diapazono stulpelis turi būti rūšiuojamas didėjančia tvarka.
- Galiausiai prisiminkite ketvirtojo argumento svarbą. Naudokite vertybes TIKRAS KODAS (TIESA) arba FALSE (NELAIDAS) sąmoningai ir atsikratysite daugelio galvos skausmų.
Kituose mūsų funkcijų mokymo programos straipsniuose VPR programoje Excel sužinosime sudėtingesnių pavyzdžių, pavyzdžiui, atlikti įvairius skaičiavimus naudojant VPR, verčių ištraukimas iš kelių stulpelių ir kt. Dėkojame, kad skaitėte šią pamoką ir tikiuosi, kad susitiksime kitą savaitę!