Indexmatchning i VBA
INDEX & MATCH-funktion i VBA-kombination är alternativet till VLOOKUP-funktionen i Excel. I VBA har vi inte lyxen att använda INDEX & MATCH-funktionen direkt eftersom dessa två funktioner inte ingår i VBA-inbyggda funktioner. Vi kan dock fortfarande använda dem som en del av kalkylbladets funktionsklass.
Hur använder man Index Match i VBA? (Steg för steg)
Titta till exempel på nedanstående data.
I ovanstående data är uppslagsvärdet avdelningsnamnet, och baserat på detta avdelningsnamn måste vi extrahera lönebeloppet.
Men problemet här är att resultatkolumnen är där i den första, och uppslagsvärdekolumnen är därefter resultatkolumnen. I det här fallet kan VLOOKUP inte hämta lönebeloppet eftersom VLOOKUP bara fungerar från höger till vänster, inte från vänster till höger.
I dessa fall måste vi använda kombinationsformeln för VBA INDEX & MATCH-funktionen. Låt oss utföra uppgiften att hitta lönebeloppet för varje avdelning i VBA-koden.
Steg 1: Starta solrutinen.
Steg 2: Förklara variabeln VBA Integer.
Koda:
Sub INDEX_MATCH_Example1 () Dim k Som heltal End Sub
Steg 3: Öppna nu för nästa slinga i VBA.
Koda:
Sub INDEX_MATCH_Example1 () Dim k Som heltal För k = 2 till 5 Nästa k Avslut Sub
Steg 4: Utför formeln inuti VBA-slingan. I den femte kolumnen måste vi tillämpa formeln, så koden är CELLS (k, 5) .Värde =
Koda:
Sub INDEX_MATCH_Example1 () Dim k som heltal För k = 2 till 5 celler (k, 5). Värde = Nästa k Avsluta sub
Steg 5: I den cellen måste vi tillämpa VBA INDEX & MATCH-formeln. Som jag sa, måste vi använda dessa funktioner som kalkylbladfunktion i VBA-klass, så öppna kalkylarkets funktionsklass.
Koda:
Sub INDEX_MATCH_Example1 () Dim k som heltal för k = 2 till 5 celler (k, 5) .Value = WorksheetFunction. Nästa k Avsluta Sub
Steg 6: Efter att ha gått in i kalkylbladets funktionsklass kan vi se alla tillgängliga kalkylarkfunktioner, så välj INDEX-funktionen.
Koda:
Sub INDEX_MATCH_Example1 () Dim k som heltal För k = 2 till 5 celler (k, 5) .Value = WorksheetFunction.Index (Next k End Sub
Steg 7: När du använder kalkylfunktionen i VBA måste du vara helt säker på argumenten med formeln. Det första argumentet är array dvs från vilken kolumn vi behöver resultatet. I det här fallet behöver vi resultatet från A2 till A5.
Koda:
Sub INDEX_MATCH_Example1 () Dim k som heltal För k = 2 till 5 celler (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), Next k End Sub
Steg 8: Nästa är från vilket radnummer vi behöver resultatet. Som vi har sett i det tidigare exemplet kan vi inte manuellt ange radnumret varje gång. Så använd MATCH-funktionen.
För att använda MATCH-funktionen en gång till måste vi öppna klassen Arbetsbladets funktion.
Koda:
Sub INDEX_MATCH_Example1 () Dim k som heltal För k = 2 till 5 celler (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Next k End Sub
Steg 9: MATCH-funktioner första argumentet är LOOKUP-värdet; här är vårt sökvärde avdelningsnamn; den finns där i cellerna (2, 4).
Eftersom varje gång radnumret måste ändras kan vi leverera variabeln "k" istället för manuell rad nummer 2. Celler (k, 4) .Värde
Koda:
Sub INDEX_MATCH_Example1 () Dim k som heltal För k = 2 till 5 celler (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Cells (k, 5) .Value, Next k Avsluta sub
Steg 10: Därefter måste vi nämna avdelningens värdeområde, dvs. Range (“B2: B5”).
Koda:
Sub INDEX_MATCH_Example1 () Dim k som heltal För k = 2 till 5 celler (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Cells (k, 5) .Value, Range) ("B2: B5"),
Nästa k
Avsluta sub
Steg 11: Sätt sedan argumentet som 0 eftersom vi behöver en exakt matchning och stäng parenteserna.
Koda:
Sub INDEX_MATCH_Example1 () Dim k som heltal För k = 2 till 5 celler (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Cells (k, 4) .Value, Range) ("B2: B5"), 0))
Nästa k
Avsluta sub
Okej, vi är klara med kodningsdelen. Låt oss köra koden för att få resultatet i kolumn 5.
Så vi fick resultatet.
Vi kan använda denna formel som ett alternativ till VLOOKUP-funktionen.
