Įvairioms užduotims spręsti dažnai kyla poreikis sujungti, surasti ir atkelti kelių lentelių duomenis pagal tą pačią ar panašią reikšmę. Šiai užduočiai sukurta Excel VLOOKUP funkcija.
Kas yra VLOOKUP funkcija?
VLOOKUP yra Excel funkcija, skirta ieškoti duomenų vertikaliai lentelėje ir atkelti iš gretimų stulpelių (tik po dešine puse) esančias duomenų reikšmes. Būtent raidė "V" ir nusako, kad ieškomos reikšmės atitikmuo ar jam artimas bus ieškomas būtent vertikaliai žemyn, o likęs žodis "LOOKUP" iš angl. kalbos verčiasi kaip "ieškoti". Excel VLOOKUP funkcija palaiko apytikslį ir tikslų ieškomų reikšmių atitikimą.
VLOOKUP formulė
=VLOOKUP(lookup_value;table_array;column_index_num;[range_lookup])
Excel VLOOKUP funkcijos sėkmingam veikimui formulėje reikia aprašyti kelis kriterijus:
- Nurodyti ieškomą reikmę (lookup_value);
- Nurodyti lentelę, reikšmių lauką ar duomenų masyvą (table_array) kurio pirmame stulpelyje vertikaliai žemyn bus ieškoma (lookup_value) reikšmė;
- Atkeliamos surasto atitikmens greta esančių duomenų stulpelio numerį (column_index_num);
- Ieškomos reikšmės (lookup_value) atitikmenį. Galimi du variantai FALSE/TRUE:
- Surasti lentelėje tikslų atitikmenį (FALSE arba 0)
- Surasti lentelėje artimiausiai panašų atitikmenį (TRUE arba 1)
VLOOKUP užduoties pavyzdys
Užduotis:
Vienoje lentelėje turime darbuotojų duomenis, t.y. unikalų darbuotojo ID numerį bei darbuotojo vardą ir pavardę. Kitoje lentelėje tas pas Unikalus darbuotojo ID numeris bei išmokos ties kiekvienu iš šių numerių. Mums reikia pagal unikalų ID numerį surasti ir užpildyti lentelę papildant vardo ir pavardės reikšmėmis.
=VLOOKUP(F10;$A$10:$C$22;2;FALSE), kur:
- F10 - ieškoma reikšmė (darbuotojo ID numeris);
- $A$10:$C$22 - užfiksuota/ nepaslanki (absoliuti) lentelė kurioje ieškomos ir iš kurios atkeliamos gretimų stulpelių reikšmės;
- 2 - stulpelio numeris, kurio reikšmė turėtų būti atkelta;
- False/True - ieškoma tiksli (False arba 0) ar apytikslė (True arba 1) reikšmė;
Atsisiųsti Excel VLOOKUP funkcijos pavyzdį
VLOOKUP funkcijos privalumai
Remiantis įvairiais šaltiniais, VLOOKUP funkcija priskiriama prie populiariausių Excel funkcijų sąrašo. Tarp Excel vartotojų dažnai šios Excel funkcijos pavadinimas vartojamas kaip bendrinis žodis,reiškiantis apjunk duomenis, surask atitikmenis. Ši funkcija išties lengvai suprantama ir leidžia pakankamai paprastai surasti bei apjungti skirtingų lentelių duomenis.
VLOOKUP funkcijos trūkumai
VLOOKUP funkcija turi ir trūkumų, štai keli iš jų:
- suradus vertikaliai ieškomo atitikmens (lookup_value) reikšmę duomenų masyve (table_array) galime atkelti tik iš dešinės pusės stulpelių;
- suradus pirmą atitikmenį ieškant vertikaliai nuo viršaus žemyn, atkeliama pirmoji reikšmė, t.y. jei ieškomoje lentelėje atitikmenų yra daugiau bus rastas ir atkeltas tik pirmasis;
- norint nurodyti atkeliamų reikšmių stulpelio numerį (column_index_num) iš didesnio duomenų masyvo kartais sunku suskaičiuoti pageidaujamą stulpelį ir galima lengvai suklysti. Ši klaida gali atsirasti ir bandant į duomenų masyvą (table_array) įkelti naują ar ištrynus keletą stulpelių, todėl šioje vietoje rekomenduojama naudoti MATCH funkciją.
Dažniausios VLOOKUP klaidos
Viena iš pagrindinių klaidų su kuria susiduria Excel VLOOKUP funkcijos vartotojai, kad ieškomos reikšmės nepavyksta rasti, tada funkcija grąžina #N/A reikšmę. Norint, kad ši reikšmė nebūtų atkeliama rekomenduojama koreguoti formulę arba papildomai naudoti IFNA funkciją.
Aukščiau pateiktame pvz. klaida atsiranda todėl, kad vlookup funkcijai nurodyta ieškoti ne apytikslės (TRUE), o tikslią reikšmę (FALSE), kurios duomenų masyve (B3:B7) nėra.
Kaip panaikinti #N/A tekstą?
Papildomai naudojant IFNA funkciją pavyks išvengti tokių situacijų kai išmetama #N/A klaidos reikšmė. Panaudosime papildomą IFNA formulę, nurodant, kad #N/A rezultato atveju lauką paliktume tiesiog tuščią ("") arba su mūsų nurodytu tekstu kabutėse:
=IFNA(VLOOKUP(F2;B3:C7;2;FALSE);"tiksli reikšmė nerasta")
VLOOKUP funkcijos alternatyvos
VLOOKUP priklauso angl. "lookup & reference" (paieška ir nuoroda) funkcijų grupei, kuriai taipogi priklauso horizontalios paieškos funkcija HLOOKUP bei kitos geriau žinomos funkcijos kaip INDEX, MATCH ir pan.
Jei yra galimybė rekomenduojame išbandyti naują „Microsoft 365“ prenumeratoriams skirtą XLOOKUP funkciją – patobulintą VLOOKUP versija, kuri veikia bet kuria kryptimi, todėl ją naudoti lengviau ir patogiau nei jos pirmtaką.
Apibendrinimas
VLOOKUP funkcija nors ir turėdama keletą trūkumų dėl savo paprasto naudojimo ir gebėjimo spręsti kasdieninius iššūkius bandant apjungti skirtingus duomenis pelnytai vadinama viena populiariausių Excel funkcijų. Platesnes šios ir kitų Excel funkcijų pritaikymo galimybes visada galime įdomiai, naudingai ir paprastai patobulinti pritaikant kasdieniniuose Jūsų darbuose kartu excel-mokymai-kursai.lt. Susisiekime! Koks Jūsų poreikis?
Microsoft VLOOKUP funkcijos aprašas