Šios „Excel“ funkcijos taikymas ir apibrėžimas
VLOOKUP yra „Excel“ funkcija, su kuria vartotojas gali ieškoti ir įvertinti lentelės turinį. Ši funkcija pasiekiama „Excel 2007“ versijose, skirtose „Windows“ ir „Mac“.
Kas yra VLOOKUP?
Galimi „VLOOKUP“ naudojimo būdai turi būti paaiškinti čia, naudojant pavyzdį: Šiame skyriuje esate didelis literatūros gerbėjas, todėl sukūrėte savo „Excel“ skaičiuoklę, kurioje galite kruopščiai surūšiuoti surinktas knygas. Kiekvienas darbas įvedamas su informacija apie šias kategorijas:
-
autorius
-
titulas
-
Puslapio numeris
-
Leidybos metai
Dabar norėtumėte duoti draugui knygos patarimą, kurį galėtumėte pasiimti su savimi kitame susitikime. Deja, galite galvoti tik apie autorių, o ne apie knygos pavadinimą. Čia atsiranda VLOOKUP, nes ji gali naudoti šią įvesties vertę, kad vienu metu išmestų ieškomą informaciją.
Kaip naudojamas VLOOKUP?
Prieš net galvojant apie formulių formulavimą, reikėtų nustatyti, kur vėliau bus įvesties laukas ir įvairūs išvesties laukai. Norėdami tai padaryti, prasminga sukurti atskirą lentelę, kuri iš pradžių yra tuščia ir taip suteikia vietos minėtai informacijai. Jei kuriate šią naują lentelę pagal esamos lentelės pavyzdį, vėliau turėsite laiko taupymo pranašumą.
Šiuo pagrindu formulę VLOOKUP galima sukurti rankiniu būdu arba automatiškai sugeneruoti naudojant „Excel“. Pradedantiesiems verta naudoti pastarąjį metodą, kad palaipsniui pažintumėte formulės struktūrą ir poveikį. Norėdami tai padaryti, skirtuke „Formulės“ pasirenkamas mygtukas „Įterpti funkciją“. VLOOKUP yra paslėptas atidarytame lange. Po patvirtinimo vėl atsidaro langas, kuriame galima užpildyti keturis formulės parametrus. Šitie yra:
-
Paieškos kriterijus
-
matrica
-
Stulpelio indeksas
-
Area_reference
Todėl neapdorotas formulės projektas atrodo taip:
= VLOOKUP (paieškos kriterijus, matrica, stulpelių indeksas, diapazono nuoroda)
ir vienoje iš galimų programų, tokių kaip:
= VLOOKUP (H3; A3: E40; 5)
Paieškos kriterijus
Kad funkcija žinotų, kokia vertė turėtų būti naudojama kaip atskaitos taškas, eilutė, kuri buvo pasirinkta kaip įvesties laukas dviem veiksmais anksčiau, pažymima lauke „Paieškos kriterijus“. Mūsų pavyzdyje ten įrašytas knygos autoriaus vardas „Phillip Pulmann“. Tai daro formulę lanksčią ir jos nereikia dar kartą koreguoti, kai tik pasikeičia įvesta vertė.
matrica
Įvesties lauke „Matrica“ aprašoma lentelė, kurioje galima rasti išvestinę informaciją. Šioje specialioje matricoje taip pat yra knygos pavadinimo, puslapio numerio ir išleidimo metų stulpeliai.
Matrica yra visiškai pasirinkta vieną kartą be antraščių iš viršaus kairės į apatinę dešinę paraštę. Tokiu būdu „Excel“ žino, į kokį turinį reikia atsižvelgti vertinant.
Stulpelio indeksas
„Stulpelio indekso“ įvesties laukas raginamas vartotoją apibrėžti matricos stulpelį, kuriame išvardyta tik norima vertė. Stulpelių priskyrimas chronologiškai sunumeruotas. Tai reiškia, kad pirmasis lentelės stulpelis gauna 1 reikšmę, antrasis - 2 ir tt. Mūsų pavyzdyje tai atitinka 1 stulpelio indeksą autoriui, 2 stulpelio indeksą pavadinimui, 3 stulpelio indeksą puslapio numeriui ir paskelbimo metų 4 skilties rodyklę.
Kad lentelė būtų kuo lankstesnė, stulpelio antraštę galima susieti, o ne skaičių. Tai turi pranašumą, nes formulę taip pat galima be problemų perkelti į kitas eilutes, nes stulpelio antraštę galima lanksčiai pritaikyti kiekvieną kartą.
Dėmesio: VLOOKUP skaito matricą iš kairės į dešinę, todėl stulpelių rodyklė turi būti dedama dešinėje paieškos kriterijaus stulpelio pusėje, kad funkcija galėtų į ją atsižvelgti!
Area_reference
Parametras „Range_Lookup“ užbaigia VLOOKUP formulę, nurodydamas lentelės vertinimo tikslumą. Tačiau jis skiriasi nuo anksčiau minėtų formulės komponentų, nes yra neprivalomas. Jei „neteisinga“ įvesta vertė 0, „Excel“ ieško tik tos vertės, kuri buvo nurodyta kaip paieškos kriterijus. Tačiau naudojant 1 reikšmę „true“, toliau ieškoma akivaizdžių verčių, jei nepavyko rasti tikslios vertės.
Šio parametro nurodyti negalima, nes reikšmė 1 nustatyta pagal numatytuosius nustatymus. Šis nustatymas bus naudingas vėliau išplėstiniame VLOOKUP su keliais paieškos kriterijais.
Sujungimas
Kai tik bus nustatyti visi reikalingi parametrai, bus galima naudoti VLOOKUP. Įvedus paieškos kriterijų ir patvirtinus funkciją, ieškoma vertė rodoma eilutėje, kuri buvo apibrėžta kaip išvesties laukas.
Mūsų pavyzdyje dabar rodomas knygos pavadinimas „Auksinis kompasas“, atitinkantis įvestą autorių. Norint greitai sužinoti puslapio numerį ir paskelbimo metus, nieko daugiau nereikia padaryti, kaip tik vilkti esamą VLOOKUP formulę į vėlesnius langelius. Tai labai paprasta, nes VLOOKUP stulpelių indeksas buvo susietas su pirmosios lentelės stulpelio antrašte, o antroji lentelė taip pat sudaryta ta pačia tvarka.
Jei lentelės turėtų skirtis viena nuo kitos arba nepaisant visko įvyktų klaida, VLOOKUP formulę taip pat galima pakeisti rankiniu būdu. Norėdami tai padaryti, priešpaskutinis stulpelio indekso skaitmuo turi būti suderintas su naujos išvesties vertės stulpeliu.
VLOOKUP su keliais paieškos kriterijais
Dažnai atsitinka taip, kad vieno paieškos kriterijaus nepakanka tiksliai įvertinti didelę „Excel“ lentelę. Tada prasminga paleisti VLOOKUP pagal kelis paieškos kriterijus. Norėdami tai padaryti, esama formulė turi būti papildyta papildoma IF funkcija. Tokiu būdu paraiškos metu galima atsižvelgti į iki aštuonių skirtingų paieškos kriterijų.
VLOOKUP keliose „Excel“ skaičiuoklėse
Jei paieškos kriterijų galima rasti ne tik vienoje, bet galbūt ir kitoje lentelėje, VLOOKUP formulę galima atitinkamai koreguoti. Norėdami tai padaryti, prieš esamą formulę reikia įdėti ir funkciją, ir funkciją ISERROR. Tam reikalingi penki parametrai:
-
Paieškos kriterijus
-
Matrica 1 ir Matrica 2
-
Stulpelio indeksas1 ir stulpelio indeksas2
Rezultatas atrodo taip:
= IF (ISERROR (VLOOKUP (paieškos kriterijus, matrica1, stulpelio indeksas1, 0));
VLOOKUP (paieškos kriterijus; matrica2; stulpelio indeksas2,0); VLOOKUP (paieškos kriterijus; matrica1; stulpelio indeksas1;))
ir vienoje iš galimų programų, tokių kaip:
= IF (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Paieškos kriterijus naudojamas įterpti vertę, kurios reikia ieškoti dviejose lentelėse. „Matrix1“ ir „Matrix2“ apibrėžia atitinkamas dviejų lentelių ląstelių sritis. Stulpelių indeksas1 ir stulpelio indeksas2 naudojami išsamiau apibrėžti, kurių atitinkamų lentelių stulpelių reikia ieškoti.
Jei jūsų ieškoma vertė yra abiejose lentelėse, „Excel“ išves rezultatą iš pirmosios lentelės. Tačiau, jei vertė nerasta nė vienoje iš dviejų lentelių, rodomas klaidos pranešimas. Formulės pranašumas yra tas, kad abu sąrašai neturi būti vienodos struktūros arba vienodo dydžio.
Naudodami VLOOKUP priskirkite reikšmes kategorijoms
Papildoma VLOOKUP funkcija leidžia išvardytas vertes automatiškai padalyti į jūsų pasirinktas raides ir predikatus. Ankstesniame pavyzdyje knygos tipui turėtų būti įterptas papildomas lentelės stulpelis. Knygos, kurių ilgis iki 50 puslapių, turėtų patekti į novelės žanrą, o knygos nuo 51 iki 150 puslapių priskiriamos romanui, o nuo 151 puslapio - romanui. Kad tai būtų įmanoma, VLOOKUP nereikia jokios papildomos formulės, tereikia naudoti garbanotus skliaustus „{}“. Paruošta formulė atrodo taip:
= VLOOKUP (B1; {1. "Novelė"; 51. "Novella"; 151. "Romanas"}; 2)
Garbanotų skliaustų turinys nurodo matricą, apibrėžiančią atitinkamo tipo knygos plotą. Todėl šoninio ilgio priskyrimas atitinkamai genčiai yra garbanotuose skliausteliuose. Formulėje naudojamos verčių poros, kiekviena atskirta tašku. Matrica {1. „Novelė“; 51. „Novella“; 151. „Novelė“} skaitoma taip:
„Iš 1 parodykite novelę, iš 51 - romaną, iš 151 - romaną“.
Šią matricą galima lengvai pritaikyti įvairioms užduotims. Viena vertus, tai susiję su matricų dydžiu ir skaičiumi bei jų pavadinimu. Taigi vietoj atskirų raidžių galima išvesti eilutes ar skaičius. Tereikia pakoreguoti formulės raides.
VLOOKUP keliuose darbalapiuose
Kita „VLOOKUP“ funkcija leidžia vartotojams susieti turinį, esantį skirtingose skaičiuoklėse. Mūsų pavyzdyje ši parinktis gali būti naudinga, kai informacija pirmiausia surūšiuojama į skirtingus darbalapius, o po to atnaujinama suvestinėje lentelėje.
Įsivaizduokite, kad, be knygų, „Excel“ skaičiuoklėje taip pat nurodote surinktus filmus. Tada abi kolekcijas sujungiate į vieną didelę lentelę.
Šios procedūros privalumas yra ne tik padidėjusi tvarka, bet ir galimų klaidų išvengimas. Jei norite sukurti naują įrašą arba atnaujinti esamą, jums nereikia ieškoti didelės lentelės, bet galite pasiekti mažesnes. Tuomet vertės automatiškai perkeliamos į apibendrinančią „Excel“ lentelę. Dėl to perrašymas didelėje lentelėje yra nereikalingas, o geriausiu atveju išvengiama nesėkmingo žingsnio ir vėlesnių klaidų pranešimų grandinės.
Kaip atrodo formulė?
Ši funkcija vėl įmanoma įterpiant kitą formulę. Nors ieškant pagal kelis kriterijus reikėjo papildomos IF formulės, dirbant su keliais darbalapiais reikalinga NETIESIOGINĖ formulė. Taip VLOOKUP matricai galima nurodyti diapazoną iš kitos skaičiuoklės.
= VLOOKUP (paieškos kriterijus; NETIESIOGINĖ (matrica); stulpelio indeksas; diapazono nuoroda)
Dėmesio: Ši formulė veiks tik tuo atveju, jei atskiros lentelės įvairiuose lapuose turi tuos pačius pavadinimus kaip bendrosios lentelės stulpelių antraštės. Visas lenteles galima pavadinti „Pavadinimo lauke“ viršutiniame kairiajame kampe virš langelių tinklelio. Jau pavadintas lenteles galima peržiūrėti naudojant klavišų kombinaciją Ctrl + F3.
Spręsti atsirandančius klaidų pranešimus
Darbas su susietomis „Excel“ lentelėmis gali sukelti nepageidaujamų problemų. Tai visų pirma apima neteisingų verčių išvestį. Jei išvedama neteisinga vertė 0, „Excel“ nustatymuose yra nedidelė problema, kurią galima greitai išspręsti.
Kita vertus, bendras klaidos pranešimas #NV yra sąmoninga VLOOKUP funkcija, kuri vartotojui nurodo, kad reikiamos vertės nėra. Ši pastaba gali būti sukurta kitaip naudojant formulę.
VLOOKUP - apžvalga
VLOOKUP yra naudinga „Excel“ funkcija, kurią galima naudoti ieškant ir vertinant lenteles. Jo pranašumai akivaizdūs dėl patogaus ir lankstaus pritaikymo. Tokiu būdu ši funkcija gali būti naudinga visiems, kurie reguliariai dirba su „Excel“ lentelėmis. Ar tai būtų privatus kolekcionierius, kuris sukuria savo mažas lenteles, ar didelė įmonė, kuri apdoroja žymiai svarbesnius duomenų rinkinius.
Kita vertus, jei vis dar turite neatsakytų užklausų, kurių VLOOKUP negalėjo patenkinti, galite laukti papildomos „Excel“ parinkties: „Microsoft“ siūlo „Excel 365“ vartotojams naują XLOOKUP nuo 2022-2023–2022 m. Pradžios. Tai remiasi VLOOKUP kompetencijomis ir papildo jas papildomomis, kartais net paprastesnėmis, funkcijomis. Todėl šiuo metu taip pat atsiveria nauja duomenų vertinimo tvarka.
