Impara ad automatizzare i tuoi Vlookup con Excel VBA

I lettori come te aiutano a sostenere MUO. Quando effettui un acquisto utilizzando i link sul nostro sito, potremmo guadagnare una commissione di affiliazione. Per saperne di più.

Vlookup è una funzione essenziale all'interno di Excel ed è diventata una parte vitale dell'elaborazione dei dati. Fornisce alcune delle funzionalità che potresti normalmente associare a un database completo.

Ma cosa succede se si desidera automatizzare questa funzione? Sì, è possibile, soprattutto se sai come utilizzare il linguaggio di automazione nativo di Excel, VBA. Ecco come automatizzare la funzione vlookup in Excel VBA.

MAKEUSEOF VIDEO DEL GIORNO

La sintassi di Vlookup

La sintassi della funzione vlookup è abbastanza semplice, ma ha quattro aspetti su cui devi concentrarti, anche durante l'automazione in Excel VBA.

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

Gli argomenti della funzione hanno il seguente significato:

valore di ricerca: Questo è il valore principale che vuoi cercare dall'array di ricerca.
ricerca_array: Questi sono i dati di origine, che il lookup_value utilizzerà come riferimento.
indice_colonna: Colonna da cui restituire il valore.
corrispondenza_esatta/corrispondenza_parziale: Utilizza 0 o 1 per determinare il tipo di corrispondenza.

Preparazione dei dati

Questo set di dati è composto da due parti: una tabella di ricerca e i punti dati di destinazione.

La tabella di ricerca è composta da tre colonne, con i dati che popolano i campi Sottocategoria e Nome prodotto:

Tabella dati in MS Excel con righe precompilate

La tabella di destinazione ha colonne corrispondenti, senza dati di sottocategoria o nome prodotto. Tieni presente che i valori nella colonna ID ordine sono presenti anche nella stessa colonna nella tabella di ricerca:

Valori di ricerca in un foglio di calcolo Excel

Utilizzo di Vlookup direttamente in un foglio di calcolo Excel

Per riempire le colonne di destinazione, B e C, puoi farlo usa la funzione vlookup di Excel .

In cella B2 , inserisci la seguente formula:

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

Allo stesso modo, in cell C2 , inserisci questa formula:

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

Puoi trascinare la formula verso il basso dalla cella B2 fino alla fine della colonna, B17. Ripeti questo processo anche per la colonna C. I valori per ogni voce successiva verranno aggiornati automaticamente in entrambe le colonne.

I valori che passi alla funzione vlookup sono:

A2 : il valore di ricerca è in questa cella.
F3:H17 : è costituito dall'intervallo di ricerca.
23 : le colonne di riferimento da cui recuperare il valore.
0 : Indica una corrispondenza esatta.

Funzione Vlookup di Excel VBA

La funzione vlookup ha molte somiglianze sia che la utilizzi direttamente in Excel o tramite VBA. Che tu sia un analista di dati che si occupa di righe di dati o un gestore dell'inventario che si occupa regolarmente di nuovi prodotti, puoi trarre vantaggio dall'utilizzo di vlookup.

Quando si lavora con un intervallo di ricerca dinamico, è sempre meglio automatizzare le formule, per evitare di applicare ripetutamente le formule all'interno di Excel. Automatizzando la tua funzione Vlookup in VBA, puoi eseguire calcoli su più colonne con un solo clic.

1. Eseguire la configurazione richiesta

Inizia aprendo l'editor di codifica (premi Alt+F11 o vai al Sviluppatore scheda) in una nuova cartella di lavoro di Excel e aggiungere un modulo per iniziare a scrivere il codice. All'interno dell'editor di codice, aggiungi le seguenti righe nella parte superiore della finestra di codifica per creare una subroutine:

  Sub vlookup_fn1()End Sub

La subroutine è un contenitore per il tuo codice VBA ed è fondamentale per eseguirlo correttamente. Un'alternativa è creare un modulo utente VBA per rendere la tua interfaccia utente più interattiva.

2. Dichiara le tue variabili e crea i tuoi intervalli di riferimento

Innanzitutto, è necessario dichiarare i tipi di dati variabili utilizzando il file Dim dichiarazione:

le migliori app per imparare una lingua

  Dim i as integer, lastrow as long

Quindi, crea un file ultima riga variabile, per memorizzare il valore dell'ultima riga popolata all'interno dell'intervallo di ricerca. La funzione lastrow usa il fine(xlgiù) funzione per calcolare il riferimento della riga finale all'interno dell'intervallo specificato.

In questo caso, la variabile lastrow memorizza l'ultimo valore di riga popolato dell'intervallo di ricerca, 17.

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

Nell'esempio precedente, la tabella di destinazione si estende da LA2:DO17 , mentre la tabella di origine si estende da F2:H17 . La formula di vlookup scorrerà ogni valore memorizzato nella colonna A, lo cercherà all'interno della tabella di origine e incollerà le voci corrispondenti nelle colonne B e C.

Tuttavia, prima di saltare al ciclo, è necessario memorizzare i valori dell'intervallo all'interno di una nuova variabile ( mio_intervallo ), come segue:

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

È necessario definire in modo esplicito il riferimento di cella iniziale ( F3 ) e il riferimento alla colonna finale ( H ). VBA aggiunge automaticamente il valore della riga per completare l'array di ricerca.

Codice VBA su una finestra dell'editor VBA

3. Scrivi un ciclo per scorrere ogni valore di ricerca

Prima di scrivere il ciclo, definisci il valore della riga iniziale, 2 . Definire un valore iniziale per il tuo ciclo è essenziale poiché hai a che fare con elementi dinamici. Nella tabella di destinazione, la riga 2 è la prima riga dei dati. Modifica questo valore se i tuoi dati iniziano in una riga diversa.

  i = 2

Puoi usare un fare durante loop per elaborare ogni riga, iniziando dalla riga 2 e terminando con la riga 17. Come il resto del codice, questo aspetto è dinamico; il ciclo verrà eseguito finché la condizione non sarà falsa. Utilizzare 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 popolare 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)

Queste istruzioni impostano il valore delle celle nella riga corrente, rispettivamente nelle colonne 2 e 3. Usano il Funzione foglio di lavoro oggetto di chiamare il VLookup funzione, passando il valore corrispondente dalla colonna 1 da cercare. Passano anche l'intervallo definito in precedenza e l'indice della colonna pertinente da cui recuperare il valore finale.

Il codice vlookup è pronto, ma devi ancora aumentare la variabile di riga ogni volta che si esegue il ciclo:

programmi di posta elettronica gratuiti per Windows 10

  i = i + 1

Ogni volta che il ciclo viene eseguito, aumenta il valore di io di 1. Ricorda, il valore della riga iniziale è 2 e l'incremento si verifica ogni volta che viene eseguito il ciclo. Usa il ciclo continuo parola chiave per impostare la fine del blocco di codice del ciclo.

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

Output tabulare nel foglio di calcolo Excel

Ecco l'intero codice per 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. Creare un pulsante per eseguire il codice

Con il codice fuori mano, puoi creare un pulsante nella cartella di lavoro di Excel per eseguirlo con un solo clic. Inserisci la tua forma preferita su un foglio Excel vuoto, fai clic destro su di esso e fai clic su Assegna macro opzione.

Pulsante rettangolo su un foglio di calcolo Excel con un elenco di opzioni

All'interno della finestra di dialogo, seleziona il nome della tua subroutine e fai clic su OK .

Casella macro aperta su un foglio di calcolo Excel

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

Utilizzo di Excel VBA per automatizzare le funzioni di ricerca

Excel VBA è un linguaggio flessibile, ben sintonizzato per rendere più semplice l'automazione all'interno delle diverse sfaccettature di MS Excel. Esistono molte opzioni in MS Excel VBA, dall'automazione delle funzioni di Excel alla creazione automatica di elaborate tabelle pivot.

Quando lavori con diversi segmenti di Excel, puoi sperimentare varie opzioni per rendere la tua vita più semplice ed efficiente.