Cum se automatizează Vlookup folosind Excel VBA

Vlookup este o funcție esențială în Excel și a devenit o parte importantă a procesării datelor. Acesta oferă o serie de funcții pe care le puteți combina cu o bază de date cuprinzătoare.

Cu toate acestea, dacă doriți să automatizați această funcție, puteți, mai ales dacă știți să utilizați limbajul de automatizare disponibil în Excel și VBA. Iată cum puteți automatiza funcția vlookup în Excel VBA .

Formula funcției de căutare în Excel

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

Sensul argumentelor din formula:

• lookup_value: valoarea de bază pe care doriți să o căutați în matrice.
• lookup_array: Datele sursă, lookup_value vor fi folosite ca referință.
• column_index: Coloana returnează valoarea.
• exact_match/partial_match: Folosiți 0 sau 1 pentru a specifica tipul de combinație.

Funcția VBA Vlookup în Excel

Utilizarea funcției Vlookup nu este mult diferită atunci când o utilizați direct în Excel sau prin VBA. Indiferent dacă un analist de date procesează rânduri de informații sau un manager de depozit lucrează constant cu produse noi, funcția vlookup este utilă.

Când lucrați cu un interval de căutare dinamică, automatizarea formulei este cea mai bună, pentru a evita manipularea aceleiași formule de mai multe ori în Excel. Prin automatizarea funcției Vlookup în VBA, puteți efectua calcule pe mai multe coloane cu un singur clic.

Ilustrare

Acest set de date are două părți: un tabel de căutare și puncte de date țintă. Tabelul de căutare conține 3 coloane, cu date în cele două câmpuri Subcategorie și Nume produs:

Tabelul țintă are coloanele corespunzătoare, fără subdosare sau date despre numele produsului. Rețineți că valorile din coloana ID comandă apar și în aceeași coloană în tabelul de căutare:

1. Efectuați setările necesare

Începeți prin a deschide editorul de cod (apăsați Alt + F11 sau navigați la fila Dezvoltator ) într-un nou registru de lucru Excel și adăugați modulul pentru a începe codarea. În editorul de cod, adăugați următoarele linii în partea de sus a ferestrei de programare pentru a crea o subrutină:

Sub vlookup_fn1()End Sub

subrutina este un container pentru codul VBA și este necesară pentru a o rula cu succes. O altă alternativă este crearea unui formular de utilizator VBA pentru a face interfața de utilizare mai interactivă.

2. Declarați variabile și creați domenii de referință

Mai întâi, trebuie să declarați tipurile de date ale variabilelor folosind comanda Dim:

Dim i as integer, lastrow as long

Apoi, creați o variabilă lastrow , pentru a stoca valoarea ultimului rând populat din intervalul de căutare. Funcția lastrow folosește funcția end(xldown) pentru a calcula ultima referință de rând dintr-un anumit interval.

În acest exemplu, variabila lastrow conține valoarea ultimului rând completat din intervalul de căutare, în special 17.

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

În exemplul de mai sus, tabelul destinație se extinde de la A2:C17, iar tabelul sursă se extinde de la F2:H17. Formula vlookup va parcurge fiecare valoare din coloana A, o va căuta în tabelul sursă și va lipi intrările combinate în coloanele B și C.

Cu toate acestea, înainte de a intra în buclă, trebuie să stocați valorile intervalului într-o nouă variabilă ( my_range ) după cum urmează:

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

Trebuie să specificați în mod explicit referința de pornire a celulei (F3) și referința de coloană de sfârșit (H). VBA adaugă automat valori de rând pentru a finaliza matricea de căutare.

3. Scrieți o buclă pentru a parcurge fiecare valoare de căutare

Înainte de a scrie bucla, definiți valoarea rândului de pornire, 2. Definirea unei valori de pornire pentru buclă este necesară atunci când aveți de-a face cu componente dinamice. În tabelul de destinație, rândul 2 este primul rând de date. Modificați această valoare dacă datele încep pe un rând diferit.

i = 2

Puteți utiliza o buclă do-while pentru a procesa fiecare rând, începând cu rândul 2 și terminând cu rândul 17. Ca și restul codului, acest aspect este flexibil; Bucla va rula până când condiția este falsă. Utilizați condiția pentru a testa o valoare care nu este goală în prima celulă a rândului curent.

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

În interiorul buclei, puteți apela funcția Vlookup pentru a umple celulele:

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)

Aceste comenzi setează valorile celulelor din rândul curent, în coloanele 2 și, respectiv, 3. Ele folosesc obiectul WorksheetFunction pentru a apela funcția Vlookup, trecând valoarea corespunzătoare din coloana 1 la căutare. De asemenea, trec intervalul pe care l-ați definit inițial și indexul coloanei relevante pentru a prelua valoarea finală.

Codul de căutare este gata, dar trebuie totuși să incrementați variabila rând de fiecare dată în buclă:

i = i + 1

De fiecare dată când bucla rulează, aceasta crește valoarea i cu 1 . Amintiți-vă, valoarea rândului de pornire este 2, creșterea are loc de fiecare dată când rulează bucla. Utilizați cuvântul cheie loop pentru a seta sfârșitul acestui bloc de cod.

Când întregul cod este rulat, acesta va popula ambele coloane cu rezultate, pe baza valorilor din tabelul sursă.

Mai jos este întregul cod de referință:

 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. Creați un buton pentru a rula codul

Puteți crea un buton într-un tabel Excel pentru a-l rula cu un singur clic. Introduceți forma dorită pe foaia Excel goală, faceți clic dreapta pe ea și faceți clic pe opțiunea Atribuire macro .

În caseta de dialog, selectați numele sub-rutinei și faceți clic pe OK .

Când doriți să rulați codul, faceți clic pe butonul pentru a vedea cum datele sunt completate imediat.

Mai sus este cum să utilizați Excel VBA pentru a automatiza funcțiile de căutare . Sper că articolul vă este de folos.