Come automatizzare Vlookup utilizzando Excel VBA

Come automatizzare Vlookup utilizzando Excel VBA

Vlookup è una funzione essenziale in Excel ed è diventata una parte importante dell'elaborazione dei dati. Fornisce una serie di funzioni che è possibile combinare con un database completo.

Tuttavia, se desideri automatizzare questa funzione, puoi farlo, soprattutto se sai come utilizzare il linguaggio di automazione disponibile in Excel e VBA. Ecco come automatizzare la funzione vlookup in Excel VBA .

Formula della funzione Vlookup in Excel

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

Significato degli argomenti nella formula:

  • lookup_value: il valore sottostante che desideri cercare nell'array.
  • lookup_array: dati di origine, lookup_value verrà utilizzato come riferimento.
  • column_index: la colonna restituisce il valore.
  • exact_match/partial_match: utilizzare 0 o 1 per specificare il tipo di combinazione.

Funzione VBA Vlookup in Excel

L'utilizzo della funzione Vlookup non è molto diverso quando la si utilizza direttamente in Excel o tramite VBA. Sia che un analista di dati elabori righe di informazioni o che un responsabile del magazzino lavori costantemente con nuovi prodotti, la funzione vlookup è utile.

Quando si lavora con un intervallo di ricerca dinamico, è preferibile automatizzare la formula per evitare di dover manipolare la stessa formula più volte in Excel. Automatizzando la funzione Vlookup in VBA, puoi eseguire calcoli su più colonne con un clic.

Illustrazione

Questo set di dati è composto da due parti: una tabella di ricerca e punti dati di destinazione. La tabella di ricerca contiene 3 colonne, con i dati nei due campi Sottocategoria e Nome prodotto:

Come automatizzare Vlookup utilizzando Excel VBA

La tabella di destinazione ha le colonne appropriate, nessuna sottocartella o dati sul nome del prodotto. Tieni presente che i valori nella colonna ID ordine compaiono anche nella stessa colonna nella tabella di ricerca:

Come automatizzare Vlookup utilizzando Excel VBA

1. Effettuare le impostazioni necessarie

Inizia aprendo l'editor di codice (premi Alt + F11 o vai alla scheda Sviluppatore ) in una nuova cartella di lavoro di Excel e aggiungi il modulo per avviare la codifica. Nell'editor del codice, aggiungi le seguenti righe nella parte superiore della finestra di programmazione per creare una subroutine:

Sub vlookup_fn1()End Sub

la subroutine è un contenitore per il codice VBA ed è necessaria per eseguirla correttamente. Un'altra alternativa è creare un modulo utente VBA per rendere l'interfaccia utente più interattiva.

2. Dichiarare variabili e creare ambiti di riferimento

Innanzitutto, devi dichiarare i tipi di dati delle variabili utilizzando il comando Dim:

Dim i as integer, lastrow as long

Successivamente, crea una variabile lastrow per memorizzare il valore dell'ultima riga popolata nell'intervallo di ricerca. La funzione lastrow utilizza la funzione end(xldown) per calcolare l'ultimo riferimento di riga in un intervallo specifico.

In questo esempio, la variabile lastrow contiene il valore dell'ultima riga riempita nell'intervallo di ricerca, in particolare 17.

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

Nell'esempio precedente, la tabella di destinazione si estende da A2:C17 e la tabella di origine si estende da F2:H17. La formula vlookup scorrerà ciascun valore nella colonna A, lo cercherà nella tabella di origine e incollerà le voci combinate nelle colonne B e C.

Tuttavia, prima di entrare nel ciclo, è necessario memorizzare i valori dell'intervallo in una nuova variabile ( my_range ) come segue:

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

È necessario specificare in modo esplicito il riferimento alla cella iniziale (F3) e il riferimento alla colonna finale (H). VBA aggiunge automaticamente i valori delle righe per completare l'array di ricerca.

Come automatizzare Vlookup utilizzando Excel VBA

3. Scrivere un ciclo per scorrere ciascun valore di ricerca

Prima di scrivere il ciclo, definire il valore della riga iniziale, 2. La definizione di un valore iniziale per il ciclo è necessaria quando si ha a che fare con componenti dinamici. Nella tabella di destinazione, la riga 2 è la prima riga di dati. Modificare questo valore se i dati iniziano in una riga diversa.

i = 2

Puoi utilizzare un ciclo do- while per elaborare ogni riga, iniziando dalla riga 2 e terminando con la riga 17. Come il resto del codice, questo aspetto è flessibile; Il ciclo verrà eseguito finché la condizione non sarà falsa. Utilizza la condizione per verificare la presenza di un valore non vuoto nella prima cella della riga corrente.

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

All'interno del ciclo, puoi chiamare la funzione Vlookup per riempire le celle:

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)

Questi comandi impostano i valori delle celle della riga corrente, rispettivamente nelle colonne 2 e 3. Usano l'oggetto WorksheetFunction per chiamare la funzione Vlookup, passando il valore corrispondente dalla colonna 1 alla ricerca. Passano anche l'intervallo definito inizialmente e l'indice della colonna pertinente per recuperare il valore finale.

Il codice vlookup è pronto, ma devi comunque incrementare la variabile di riga ogni volta nel ciclo:

i = i + 1

Ogni volta che il ciclo viene eseguito, incrementa il valore i di 1 . Ricorda, il valore della riga iniziale è 2, l'incremento si verifica ogni volta che viene eseguito il ciclo. Utilizza la parola chiave loop per impostare la fine di questo blocco di codice.

Quando viene eseguito l'intero codice, popolerà entrambe le colonne con i risultati, in base ai valori nella tabella di origine.

Come automatizzare Vlookup utilizzando Excel VBA

Di seguito l'intero codice di riferimento:

 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 un pulsante per eseguire il codice

Puoi creare un pulsante in una tabella Excel per eseguirlo con un clic. Inserisci la forma desiderata sul foglio Excel vuoto, fai clic destro su di essa e fai clic sull'opzione Assegna macro .

Come automatizzare Vlookup utilizzando Excel VBA

Nella finestra di dialogo, selezionare il nome della subroutine e fare clic su OK .

Come automatizzare Vlookup utilizzando Excel VBA

Quando vuoi eseguire il codice, fai clic sul pulsante per vedere immediatamente come vengono compilati i dati.

Sopra è illustrato come utilizzare Excel VBA per automatizzare le funzioni di ricerca . Spero che l'articolo ti sia utile.


Come mostrare o nascondere gli assi del grafico in Excel

Come mostrare o nascondere gli assi del grafico in Excel

Scopri come mostrare o nascondere gli assi del grafico in Excel per migliorare la chiarezza delle presentazioni dei tuoi dati.

Come accedere e aprire un file .xls di Excel 97/2003 in Excel 2016, 2019 e 365?

Come accedere e aprire un file .xls di Excel 97/2003 in Excel 2016, 2019 e 365?

Scopri come aprire vecchi file Excel con Excel 365 (2016/2019) e risolvere i problemi di compatibilità.

Come utilizzare il ciclo do- while in Excel VBA

Come utilizzare il ciclo do- while in Excel VBA

Scopri come utilizzare un ciclo Do-While in Excel VBA per automatizzare le attività ripetitive, migliorando la tua produttività e l

Come rappresentare graficamente le funzioni trigonometriche con Excel

Come rappresentare graficamente le funzioni trigonometriche con Excel

Vuoi rappresentare graficamente le funzioni trigonometriche con Microsoft Excel? Scopri come farlo con questa guida passo passo.

Funzione CONTA.SE, come utilizzare la funzione di conteggio delle celle in base a più condizioni in Excel

Funzione CONTA.SE, come utilizzare la funzione di conteggio delle celle in base a più condizioni in Excel

Scopri come utilizzare la funzione CONTA.SE in Excel per contare celle in base a più condizioni con esempi pratici.

Funzione Match in Excel: come utilizzare la funzione Match con esempi

Funzione Match in Excel: come utilizzare la funzione Match con esempi

La funzione Match in Excel ha molte applicazioni pratiche. Scopri come utilizzare la funzione Corrispondenza di Excel con esempi dettagliati.

Gioca a puzzle su Excel

Gioca a puzzle su Excel

MehmetSalihKoten, un utente Reddit, ha creato una versione completamente funzionale di Tetris in Microsoft Excel.

Come utilizzare la funzione Converti in Excel

Come utilizzare la funzione Converti in Excel

Per convertire le unità di misura in Excel, utilizzeremo la funzione Converti.

Come utilizzare la funzione CERCA.ORIZZ in Excel

Come utilizzare la funzione CERCA.ORIZZ in Excel

Cos'è la funzione CERCA.ORIZZ? Come utilizzare la funzione CERCA.ORIZZ in Excel? Scopriamolo con LuckyTemplates!

Funzione SEERRORE in Excel, formula e utilizzo

Funzione SEERRORE in Excel, formula e utilizzo

La funzione SEERRORE in Excel è abbastanza comunemente utilizzata. Per usarlo bene, è necessario comprendere la formula SEERRORE di Microsoft Excel.