Jeigu yra būdas padaryti geriau, atrask jį. T. A. Edison

MS Excel: funkcija VLOOKUP duomenų tikrinimui ir pritraukimui iš kelių lentelių

 

      Funkcija VLOOKUP leidžia greitai rasti reikiamus duomenis kitose lentelėse pagal tam tikrą kriterijų (duomenų raktą). Naudojamos duomenų suderinimui, tikrinimui ir pritraukimui iš kelių duomenų šaltinių – skirtingų lentelių.

      VLOOKUP funkcija atlieka paprastą veiksmą, kuris rankiniu būdų būtų atliekamas pačio vartotojo, tačiau esant nemažam kiekiui duomenų labai paspartina darbą. Pavyzdžiui, jūs turite dvi ataskaitas - excel lenteles, vienoje jų nurodytos produktų kainos, o kitoje parduoti kiekiai. Jūsų uždavinys - sudaryti sąrašą pagal prekes, kuriame būtų nurodyta produkto kaina, parduoti kiekiai ir viso suma. Gerai, jei abiejuose sąrašuose prekės būtų surūšiuotos ta pačia tvarka ir nebūtų niekada praleistos dėl to, jog nebuvo pardavimų ar panašiai, kas realiame gyvenime pasitaiko itin retai. Taigi atliekant šį uždavinį, jūs greičiausiai pasiimtumėte pirmąją prekę, nusikopijuotumėte jos kodą, ieškotumėte jo kainų lentelėje, o suradę šią prekę  ir kartu kainą, tą kainą perkopijuotumėte į pirmąją lentelę. Tas pats su prekės pardavimai ir vėliau taip su visomis prekėmis... laukia nemažai darbo, taip? Būtent šioje vietoje į pagalbą ateina minėta funkija VLOOKUP, kuri ieško mūsų nurodytos reikšmės (pagal pavyzdį produkto pavadinimo) nurodytame langelių bloke (kainų lentelėje) ir suradusi ieškomą reikšmę, grąžina tos reikšmės eilutėje kitame stulpelyje esančią reikšmę (pavyzdžiui, kainą).

      Taigi, funkcija VLOOKUP turi keturis argumentus:

=VLOOKUP(ieškos_reikšmė;lentelė_masyvas;stulp_indekso_num;diapaz_ieškoti)

Pirmasis – nurodoma reikšmė, kurios ieškosime kitoje lentelėje ar duomenų masyve;

Antrasis – nurodoma lentelė, ar duomenų masyvas, kuriame ieškosime pirmąjame argumente nurodytos reikšmės – būtina, kad ieškoma reikšmė būtų pirmame pažymėtos lentelės ar langelių bloko stulpelyje;

Trečiasis – nurodome, kurio stulpelio duomenis grąžinti, radus ieškomą reikšmę, eilutė bus grąžinama ta pati, kurioje buvo rasta pirmąjame argumente nurodyta reikšmė.

Ketvirtasis – nurodome TRUE, kai reikalingas apytikslis atitikimas, ir FALSE arba 0 kai tikslus.

      Pavyzdys, kai prie produkto pritraukiama jo kaina:

vlookup funkcija

      Trumpai paaiškinsiu, ką matome funkcijos argumentuose (argumentus skiria kabliataškiai), langelyje E3:

Pirmasis argumentas A3- čia nurodoma, kokios reikšmės ieškosime kitoje lentelėje, šiuo atveju kainų lentelėje ieškosime A3 langelyje esančios reikšmės "Kriaušės". Kaip matote tai yra nuorodą į langelį, kadangi vėliau kopijuojant funkciją žemyn, ji keičiasi, ir prisitaiko prie pakitusių prekių pavadinimų.

Antrasis argumentas $G$2:$H$6 - nurodomas langelių diapazonas, kita lentelė, kurioje ieškosime pirmąjame argumente nurodytos reikšmės (Kriaušės). Svarbu, kad reikšmė Kriaušės bus ieškoma tik pirmąjame šio blo stulpelyje, t.y. G stulpelis, todėl visuomet žymėdami langelių bloką ar lentelę, žymėkite taip, kad pirmasis stulpelis būtų tas, kuriame norite ieškoti pirmąjame argumente nurodytos reikšmės.

Trečiasis argumentas 2- čia nurodoma, iš kelinto stulpelio reikia grąžinti duomenis, kai tam tikroje eilutėje randama pirmojo argumento reikšmė. Stulpelio numeris skaičiuojamas nuo atrąjame argumente nurodyto diapazono. Pavyzdžio atveju, 1 stulpelis yra G (kuriame yra produktų pavadinimai), o antrasis stulpelis yra H, kuriame yra produktų kainos. Kadangi suradus produktą Kriaušės G stulpelye (rezultatas 4 eilutė), reikalinga grąžinti kainą H stulpelis, kurio numeris šiuo atveju 2, taip ir nurodome. Taigi funkcija VLOOKUP grąžins rezultatą lygų H4 langelio reikšmei - Kriaušių kaina 4,99. Turbūt pastebėjote, kad koordinatės šiuo atveju yra absoliučiosios su $ ženklu, tai yra tam, kad paieškos langelių blokas nesikeistų vėliau kopijuojant funkciją į apačią ieškant kitų produktų kainų. Šiuo atveju prieš langelių diapazoną nėra nei excel darbo knygos (failo), nei lapo (sheet) pavadinimo, tai reiškia, kad kainų lentelė yra tame pačiame lape kaip ir suvestinė lentelė, tačiau funkciją VLOOKUP galima naudoti pritraukiant duomenis ir iš kito excel lapo ar net ir iš kito excel failo.

Ketvirtasis argumentas 0- nurodome, kad ieškosime tikslaus atitikimo.

      Norėdami paeksperimentuoti, pakeiskite trečiojo argumento reikšmę į 1, ir pamatysite kaip keičiasi grąžinami duomenys priklausomai nuo stulpelio numerio (bus grąžinti G stulpelio duomenys - produktų pavadinimai). Antrajame argumente, langelių bloką žymėjau su stulpelių pavadinimais, tačiau galite žymėti ir be jų, tai beveik nesudaro skirtumo, tik paieškoje tikrinama viena reikšme daugiau arba mažiau. Tą patį toliau reikėtų atlikti C stulpelyje - iš pardavimų lentelės sukelti pardavimų duomenis, o tuomet G stulpelyje tiesiog įrašyti daugybos formulę: kaina x pardavimai.  

      Svarbu naudojant VLOOKUP funkciją:

   - Antajame argumente nurodytos lentelės pirmuoju stulpeliu turi būti tas, kuriame norime ieškoti pirmajame argumente nurodytos reikšmės – galima žymėti dalį duomenų;

   - Grąžnama bus tik pirmoje eilutėje esanti reikšė kurioje bus rasta iešoma reikšė t.y. toliau nebebus iešoma reikšių net jei jų yra kelios vienodos. Todėl svarbu prieš naudojant šią funkciją lentelę kurioje bus iešoma reikšmių atitinkamai surūšuoti ar pašlinti joje nereikalingus dublikatus;

   - Kad gautumėte teisingus rezultatus, pirmame argumente nurodoma reikšmė turėtų būi unikali, t.y. nesikartoti, tai gali būti, pavyzdžui, asmens kodas ar prekės SAP numeris.

Šią funkciją rekomenduoju išmokti bet kuriam MS Excel vartotojui, tai viena populiariausių funkcijų buhalterių, analitikų, pardavėjų ir kitų profesijų atstovų darbuose, kurie nori dirbti efektyviai ir greitai :)

Pridėti komentarą


Saugos kodas
Atnaujinti