Naučite automatizirati svoje Vlookupove uz Excel VBA

Čitatelji poput vas podržavaju MUO. Kada kupite putem poveznica na našoj stranici, možemo zaraditi partnersku proviziju. Čitaj više.

Vlookup je bitna funkcija unutar Excela i postao je vitalni dio obrade podataka. Pruža neke od funkcija koje obično povezujete s punom bazom podataka.

Ali što ako želite automatizirati ovu funkciju? Da, to je moguće, pogotovo ako znate kako koristiti Excelov izvorni jezik za automatizaciju, VBA. Evo kako možete automatizirati funkciju vlookupa u programu Excel VBA.

MAKEUSEOF VIDEO DANA

Sintaksa Vlookupa

Sintaksa funkcije vlookup je prilično jednostavna, ali ima četiri aspekta na koja se morate usredotočiti, čak i dok je automatizirate u Excel VBA.

 =vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

Argumenti funkcije imaju sljedeće značenje:

tražena_vrijednost: Ovo je primarna vrijednost koju želite potražiti u nizu za pretraživanje.
pretraživanje_niza: Ovo su izvorni podaci koje će lookup_value koristiti kao referencu.
stupac_index: Stupac iz kojeg se vraća vrijednost.
točno_podudaranje/djelomično_podudaranje: Koristite 0 ili 1 za određivanje vrste podudaranja.

Priprema podataka

Ovaj skup podataka ima dva dijela: tablicu pretraživanja i odredišne podatkovne točke.

Pregledna tablica sastoji se od tri stupca, s podacima koji popunjavaju polja Potkategorija i Naziv proizvoda:

Tablica podataka u MS Excelu s unaprijed popunjenim redovima

Odredišna tablica ima podudarne stupce, bez podataka o podkategoriji ili nazivu proizvoda. Imajte na umu da su vrijednosti u njegovom stupcu ID narudžbe također prisutne u istom stupcu u tablici pretraživanja:

Traženje vrijednosti u proračunskoj tablici programa Excel

Korištenje Vlookupa izravno u Excel proračunskoj tablici

Za popunjavanje odredišnih stupaca, B i C, možete koristite Excelovu funkciju vlookup .

U ćeliji B2 , unesite sljedeću formulu:

 =VLOOKUP($A2, $F:$H, 2, 0)

Slično, u ćeliji C2 , unesite ovu formulu:

 =VLOOKUP($A2, $F:$H, 3, 0)

Formulu možete povući prema dolje od ćelije B2 do kraja stupca, B17. Ponovite ovaj postupak i za stupac C. Vrijednosti za svaki sljedeći unos automatski će se ažurirati u oba stupca.

Vrijednosti koje prosljeđujete funkciji vlookup su:

A2 : Vrijednost traženja je u ovoj ćeliji.
F3:H17 : Ovo se sastoji od raspona pretraživanja.
23 : Referentni stupci iz kojih se dohvaća vrijednost.
0 : Označava točno podudaranje.

Excel VBA funkcija Vlookup

Funkcija vlookup ima mnogo sličnosti bez obzira koristite li je izravno u Excelu ili putem VBA. Bilo da ste analitičar podataka koji se bavi redovima podataka ili upravitelj inventara koji se redovito bavi novim proizvodima, možete imati koristi od upotrebe vlookupa.

Kada radite s dinamičkim rasponom pretraživanja, uvijek je najbolje automatizirati svoje formule kako biste izbjegli opetovanu primjenu formula unutar programa Excel. Automatizacijom vaše funkcije Vlookup u VBA-u, možete izvršiti izračune s više stupaca jednim klikom.

1. Izvršite potrebna podešavanja

Započnite otvaranjem uređivača kodiranja (pritisnite Alt + F11 ili idite na Developer karticu) u novoj Excel radnoj knjizi i dodajte modul da biste započeli pisati svoj kod. Unutar uređivača koda dodajte sljedeće retke na vrh prozora za kodiranje kako biste stvorili potprogram:

  Sub vlookup_fn1()End Sub

Potprogram je spremnik za vaš VBA kod i ključan je za njegovo uspješno pokretanje. Alternativa je da se stvoriti VBA korisnički obrazac kako bi vaše korisničko sučelje bilo interaktivnije.

što je bixby na mom telefonu

2. Deklarirajte svoje varijable i stvorite svoje referentne raspone

Prvo morate deklarirati tipove podataka varijable pomoću Dim izjava:

  Dim i as integer, lastrow as long

Zatim stvorite a lastrow varijabla za pohranjivanje vrijednosti zadnjeg popunjenog retka unutar vašeg raspona pretraživanja. Lastrow funkcija koristi kraj (xldown) funkcija za izračunavanje reference završnog retka unutar navedenog raspona.

U ovom slučaju, varijabla lastrow pohranjuje posljednju popunjenu vrijednost retka raspona pretraživanja, 17.

  lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

U gornjem primjeru, odredišna tablica proteže se od A2:C17 , dok se izvorna tablica proteže od F2:H17 . Vlookup formula proći će kroz svaku vrijednost pohranjenu unutar stupca A, potražiti je unutar izvorne tablice i zalijepiti podudarne unose u stupce B i C.

Međutim, prije nego što skočite na petlju, morate pohraniti vrijednosti raspona unutar nove varijable ( moj_raspon ), kako slijedi:

  my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

Morate eksplicitno definirati početnu referencu ćelije ( F3 ) i referenca krajnjeg stupca ( H ). VBA automatski dodaje vrijednost retka da dovrši polje traženja.

kako započeti niz snapa

3. Napišite petlju za kruženje kroz svaku traženu vrijednost

Prije pisanja petlje definirajte početnu vrijednost retka, 2 . Definiranje početne vrijednosti za vašu petlju bitno je jer imate posla s dinamičkim elementima. U odredišnoj tablici red 2 je prvi red podataka. Promijenite ovu vrijednost ako vaši podaci počinju u drugom retku.

  i = 2

Možete koristiti a učiniti dok petlja za obradu svakog retka, počevši od retka 2 i završavajući u retku 17. Kao i ostatak koda, ovaj aspekt je dinamičan; petlja će se izvoditi sve dok uvjet ne bude lažan. Koristite uvjet za provjeru vrijednosti koja nije prazna u prvoj ćeliji trenutnog retka.

  Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

Unutar petlje možete pozvati funkciju VLookup za popunjavanje ćelija:

  Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

Ove izjave postavljaju vrijednost ćelija u trenutnom retku, u stupcima 2 i 3. Oni koriste Funkcija radnog lista objekt nazvati VLookup funkcija, prosljeđujući odgovarajuću vrijednost iz stupca 1 za traženje. Oni također prosljeđuju raspon koji ste ranije definirali i indeks relevantnog stupca iz kojeg se dohvaća konačna vrijednost.

Vlookup kod je spreman, ali još uvijek trebate povećati varijablu reda svaki put u petlji:

  i = i + 1

Svaki put kada se petlja pokrene, povećava se vrijednost ja za 1. Zapamtite, početna vrijednost reda je 2, a povećanje se događa svaki put kada se petlja pokrene. Koristiti petlja ključna riječ za postavljanje kraja bloka koda petlje.

Kada pokrenete cijeli kod, on će popuniti rezultate u oba stupca, na temelju vrijednosti u izvornoj tablici.

Evo cijelog koda za referencu:

  Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. Napravite gumb za pokretanje koda

Bez koda možete stvoriti gumb u radnoj knjizi programa Excel da biste je pokrenuli jednim klikom. Umetnite željeni oblik na prazan Excel list, desnom tipkom miša kliknite na njega i kliknite na Dodijeli makro opcija.

Pravokutni gumb na proračunskoj tablici programa Excel s popisom opcija

Unutar dijaloškog okvira odaberite naziv svoje podrutine i kliknite na u redu .

Makro okvir otvoren na excel proračunskoj tablici

Kada želite pokrenuti svoj kod, kliknite gumb da vidite kako se podaci odmah popunjavaju.

Korištenje Excel VBA za automatizaciju funkcija pretraživanja

Excel VBA je fleksibilan jezik koji je dobro podešen za lakšu automatizaciju unutar različitih aspekata MS Excela. Mnoge opcije postoje u MS Excel VBA, od automatizacije Excel funkcija do automatske izrade složenih zaokretnih tablica.

Kada radite s različitim Excel segmentima, možete eksperimentirati s raznim opcijama kako biste svoj život učinili lakšim i učinkovitijim.