Funkcija "Vlookup" man primena tuos laikus, kai dar nebuvau prisilietęs prie VBA programinio kodo, bet jau niežtėjo rankas šį kodą išbandyti. Galvodamas apie kodą vis atrasdavau kokią nors esamą Excel funkciją, kuri atlikdavo tai, ką būčiau bandęs aprašyti programiniame kode.
Viena tokių atrastų funkcijų buvo "Vlookup". Tarsi programinio kodo sergėtoja, tąkart ir vėl apsaugojusi VBA kodą nuo klaviatūrą mikliai maigančių mano pirštų. Funkcija, leidžianti atlikti operatyvią paiešką didžiulėje duomenų bazėje ir sutaupiusi daugybę valandų mano laiko.
"Vlookup" naudingumą iliustruosiu pavyzdžiu. Tarkime, statistikos padalinys atrinko 1000 asmenų, kuriuos reikėtų apklausti telefonu. Tačiau atrinktų asmenų sąraše nėra pateikti telefono numeriai:
Tarkime, jūsų padalinys yra atsakingas už sąrašo perdavimą apklausėjams, t. y. jūs turite pasirūpinti, kad telefono numeriai atsirastų atrinktų asmenų sąraše. Jūs taip pat turite duomenų bazę, kurioje yra patalpinta informacija (įskaitant telefono numerius) apie maždaug 30 tūkst. asmenų:
Šioje 30 tūkst. asmenų duomenų bazėje turėtų būti ir minėti 1000 atrinktų asmenų. Tad duomenų bazėje jums reikia susirasti atrinktus asmenis ir nukopijuoti jų telefono numerius į atrinktų asmenų sąrašą. Rankiniu būdu surasti kiekvieną iš 1000 asmenų ir nukopijuoti kiekvieno asmens telefono numerį užimtų ilgokai. Darbą apsunkina ir tai, kad duomenų bazėje pirma pateiktas vardas, o po to pavardė (o atrinktų asmenų sąraše - priešingai).
"Vlookup" funkcija tiesiogine prasme jums leis atlikti užduotį per keliolika, o gal net ir kelias minutes. Tačiau pirmiausia reikia sukurti raktą, kuris geriausiai atspindėtų kiekvieną asmenį. Toks raktas galėtų būti viename langelyje pateikti pavardė ir vardas (žmonių su ta pačia pavarde ir tuo pačiu vardu bus santykinai mažai, ir tokius atvejus galima bus nesunkiai išrinkti rankiniu būdu; galima sukurti ir tikslesnį raktą, pavyzdžiui, į tą patį langelį šalia pavardės ir vardo įtraukiant dar ir atstovaujamos institucijos pavadinimą).
Dviejų ar daugiau langelių informaciją perkelti į vieną langelį padeda funkcija "Concatenate" (plačiau apie ją čia). Tad atrinktų asmenų sąraše įterpiame stulpelį "Raktas" ir D2 langelyje įvedame formulę:
+CONCATENATE(A2;" ";B2)
Tai reiškia, kad D2 langelyje atsiras A2 langelio informacija (pavardė), po vardo - tarpas (" "), o po tarpo - vardas (B2 langelio informacija).
Šią formulę turime nukopijuoti į visų dalyvių langelius. Kai turime daug įrašų, tokį kopijavimą palengvins Ctrl+Shift+End kombinacija, kuri pažymės visus žemiau esančius langelius. Kadangi E stulpelyje bus antraštė "Mob. tel.", pažymėjimas apims ir mums nereikalingą E stulpelį. Todėl laikykime Shift nuspaustą ir spustelėkime ant paskutinio mums reikalingo D stulpelio langelio - pažymėjimas susitrauks iki D stulpelio. Ctrl+V įklijuos formulę (kurią prieš tai būsime nukopijavę iš D2 langelio į kompiuterio atmintį) į visus pažymėtus langelius:
Tokį patį raktą turime sukurti ir 30 tūkst. asmenų duomenų bazėje (įterpdami naują E stulpelį ir į E2 langelį įvesdami +CONCATENATE(B2;" ";A2)):
Turėdami paruoštą raktą, galime pritaikyti "Vlookup". Tarkime, duomenų bazę esame perkėlę į tą pačią bylą (kurioje yra atrinktų asmenų sąrašas), į lapą "DB". Atrinktų asmenų sąraše (tai bus kitas lapas nei "DB")), dešiniau pirmo asmens (į E2 langelį) įvedame:
+VLOOKUP(D2;DB!$E$2:$F$30001;2;0)
kur:
pirmasis narys (D2) nurodo, kokios reikšmės bus ieškoma paieškos lauko pirmame stulpelyje, t. y. 30 tūkst. asmenų duomenų bazėje mes ieškosime tokio pat asmens, koks yra nurodytas atrinktų dalyvių sąrašo D2 langelyje (prisiminkime, D stulpelyje yra raktas);
antrasis narys (DB!$E$2:$F$30001) nurodo, koks bus paieškos laukas (kurio pirmame stulpelyje bus ieškoma pirmojo formulės nario nurodytos reikšmės); "DB!" yra nuoroda į lapą "DB"; "$" panaudojimas reiškia, kad atrinktų asmenų sąraše kopijuojant formulę į žemiau esančius langelius informacijos paieškos laukas lape "DB" nesikeis; informacijos ieškome lapo "DB" E-F stulpeliuose, nes lapo "DB" E stulpelyje yra raktas, pagal kurį identifikuosime atrinktą asmenį, o F stulpelyje yra mums reikalingas telefono numeris; informacijos ieškome lapo "DB" 2-30001 eilutėse, nes pirmas asmuo yra 2 eilutėje, o duomenų bazėje yra 30000 asmenų, tad 30000-asis asmuo bus 30001 eilutėje;
trečiasis narys (2) nurodo, iš kurio paieškos lauko stulpelio bus perkelta reikšmė į langelį, kuriame įrašėme formulę; paieškos laukas galėtų apimti daug stulpelių (tas aktualu pažengusiems naudotojams), tačiau mūsų atveju apima tik E ir F stulpelius, tad antras stulpelis yra F, iš jo ir bus imama reikšmė (šiuo atveju - telefono numeris) radus eilutę su ieškomu asmeniu; kaip minėta, asmens bus ieškoma lyginant pirmojo formulės nario nurodytą reikšmę su paieškos lauko pirmajame stulpelyje (E stulpelyje) pateiktomis reikšmėmis;
ketvirtasis narys (0) reiškia, kad ieškome identiškos reikšmės, o ne kaip galima panašesnės reikšmės.
Tereikia formulę pakartoti visuose atrinktų asmenų sąrašo E stulpelio langeliuose (čia vėl padės Ctrl+Shift+End kombinacija), ir, voilà, užduotis įvykdyta:
Kaip užsiminiau, duomenų bazėje gali būti žmonių su ta pačia pavarde ir tuo pačiu vardu, tokiu atveju formulė grąžins reikšmę "#N/A", nes nežinos, kurio asmens telefono numerį perkelti. Tokius atvejus galima nesunkiai išrinkti rankiniu būdu, arba galima naudoti tikslesnį raktą, pavyzdžiui, šalia pavardės ir vardo įtraukiant dar ir atstovaujamos institucijos pavadinimą.
No comments:
Post a Comment