Lär dig att automatisera dina Vlookups med Excel VBA

Lär dig att automatisera dina Vlookups med Excel VBA
Läsare som du hjälper till att stödja MUO. När du gör ett köp med hjälp av länkar på vår webbplats kan vi tjäna en affiliate-provision. Läs mer.

Vlookup är en viktig funktion inom Excel och har blivit en viktig del av databehandling. Det ger en del av de funktioner som du vanligtvis kan associera med en komplett databas.





Men vad händer om du vill automatisera den här funktionen? Ja, det är möjligt, särskilt om du vet hur man använder Excels inhemska automationsspråk, VBA. Så här kan du automatisera vlookup-funktionen i Excel VBA.



MAKEUSE AV DAGENS VIDEO

Syntaxen för Vlookup

Vlookup-funktionens syntax är ganska enkel, men den har fyra aspekter du måste fokusera på, även när du automatiserar den i Excel VBA.





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

Funktionens argument har följande betydelse:

  • lookup_value: Detta är det primära värdet du vill slå upp från uppslagsmatrisen.
  • lookup_array: Detta är källdata som lookup_value kommer att använda som referens.
  • kolumnindex: Kolumn att returnera värdet från.
  • exakt_matchning/partiell_matchning: Använd antingen 0 eller 1 för att bestämma matchningstypen.

Dataförberedelse

Denna datauppsättning har två delar: en uppslagstabell och destinationsdatapunkterna.



Uppslagstabellen består av tre kolumner, med data som fyller i fälten underkategori och produktnamn:

Datatabell i MS Excel med förifyllda rader

Måltabellen har matchande kolumner, utan data om underkategorier eller produktnamn. Observera att värdena i dess kolumn Order ID också finns i samma kolumn i uppslagstabellen:



Slå upp värden i ett excel-kalkylblad

Använda Vlookup direkt i ett Excel-kalkylblad

För att fylla målkolumnerna, B och C, kan du använd Excels vlookup-funktion .

I cell B2 , ange följande formel:



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

På samma sätt i cell C2 , ange denna formel:

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

Du kan dra formeln nedåt från cell B2 tills kolumnens slut, B17. Upprepa denna process för kolumn C också. Värdena för varje efterföljande post uppdateras automatiskt i båda kolumnerna.

Värdena du skickar till vlookup-funktionen är:

var är mikrofonen på airpods
  • A2 : Uppslagsvärdet finns i den här cellen.
  • F3:H17 : Detta består av uppslagsintervallet.
  • 23 : Referenskolumnerna att hämta värdet från.
  • 0 : Betecknar en exakt matchning.

Excel VBA:s Vlookup-funktion

Funktionen vlookup har många likheter oavsett om du använder den direkt i Excel eller via VBA. Oavsett om du är en dataanalytiker som hanterar rader med data, eller en lagerförvaltare som regelbundet hanterar nya produkter, kan du dra nytta av att använda vlookup.

När du arbetar med ett dynamiskt uppslagsintervall är det alltid bäst att automatisera dina formler för att undvika att använda formler upprepade gånger i Excel. Genom att automatisera din Vlookup-funktion i VBA kan du utföra flerkolumnsberäkningar med ett enda klick.

1. Utför den nödvändiga installationen

Börja med att öppna kodningsredigeraren (tryck på Alt + F11 eller navigera till Utvecklare flik) i en ny Excel-arbetsbok och lägg till en modul för att börja skriva din kod. I kodredigeraren lägger du till följande rader högst upp i kodningsfönstret för att skapa en underrutin:

  Sub vlookup_fn1()End Sub

Subrutinen är en behållare för din VBA-kod och är avgörande för att köra den framgångsrikt. Ett alternativ är att skapa ett VBA-användarformulär för att göra ditt användargränssnitt mer interaktivt.

2. Deklarera dina variabler och skapa dina referensintervall

Först måste du deklarera variabeldatatyperna med hjälp av Dämpa påstående:

254D23C88C5E06FBC9663FFFFC3C5896BC413B18

Skapa sedan en sista raden variabel, för att lagra värdet på den senast fyllda raden inom ditt uppslagsintervall. Lastrow-funktionen använder end(xldown) funktion för att beräkna slutradreferensen inom det angivna intervallet.

I det här fallet lagrar lastrow-variabeln uppslagsintervallets senast ifyllda radvärde, 17. 

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

I exemplet ovan sträcker sig destinationstabellen från A2:C17 , medan källtabellen sträcker sig från F2:H17 . Formeln vlookup kommer att gå igenom varje värde som är lagrat i kolumn A, slå upp det i källtabellen och klistra in de matchande posterna i kolumnerna B och C.

Men innan du hoppar in i slingan måste du lagra intervallvärdena i en ny variabel ( mitt_område ), som följer:

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

Du måste uttryckligen definiera startcellreferensen ( F3 ) och referensen för slutkolumnen ( H ). VBA lägger automatiskt till radvärdet för att slutföra uppslagsmatrisen.

VBA-kod i ett VBA-redigeringsfönster

3. Skriv en slinga för att gå igenom varje uppslagsvärde

Innan du skriver slingan, definiera startradens värde, 2 . Att definiera ett startvärde för din loop är viktigt eftersom du har att göra med dynamiska element. I destinationstabellen är rad 2 den första raden av data. Ändra detta värde om din data börjar på en annan rad.

  i = 2

Du kan använda en göra medan loop för att bearbeta varje rad, med början på rad 2 och slutar på rad 17. Liksom resten av koden är denna aspekt dynamisk; slingan kommer att köras tills villkoret är falskt. Använd villkoret för att leta efter ett icke-tomt värde i den första cellen i den aktuella raden.

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

Inuti slingan kan du anropa VLookup-funktionen för att fylla i cellerna:

  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)

Dessa uttalanden anger värdet på cellerna i den aktuella raden, i kolumn 2 respektive 3. De använder Arbetsblad Funktion objekt att ringa till VLookup funktion, skickar motsvarande värde från kolumn 1 för att söka efter. De passerar också intervallet du definierade tidigare och indexet för den relevanta kolumnen för att hämta det slutliga värdet från.

Vlookup-koden är klar, men du måste fortfarande öka radvariabeln varje gång runt loopen:

  i = i + 1

Varje gång slingan körs ökar den värdet på i med 1. Kom ihåg att startradens värde är 2, och ökningen sker varje gång loopen körs. Använd slinga nyckelord för att ställa in slutet av slingans kodblock.

När du kör hela koden kommer den att fylla i resultaten i båda kolumnerna, baserat på värdena i källtabellen.

Tabellutdata i Excel-kalkylblad

Här är hela koden som referens:

  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. Skapa en knapp för att köra koden

Med koden ur vägen kan du skapa en knapp i din Excel-arbetsbok för att köra den med ett enda klick. Infoga önskad form på ett tomt Excel-ark, högerklicka på det och klicka på Tilldela makro alternativ.

Rektangelknapp på ett Excel-kalkylblad med en lista med alternativ

I dialogrutan väljer du namnet på din underrutin och klickar på OK .

Makrobox öppen på ett excel-kalkylblad

När du vill köra din kod klickar du på knappen för att se hur data fylls i direkt.

Använda Excel VBA för att automatisera uppslagsfunktioner

Excel VBA är ett flexibelt språk som är välinställt för att göra automatisering enklare inom MS Excels olika aspekter. Det finns många alternativ i MS Excel VBA, från att automatisera Excel-funktioner till att skapa utarbetade pivottabeller automatiskt.

När du arbetar med olika Excel-segment kan du experimentera med olika alternativ för att göra ditt liv enklare och mer effektivt.