Indeks Match i VBA
INDEX & MATCH-funksjon i VBA-kombinasjon er alternativet til VLOOKUP-funksjonen i excel. I VBA har vi ikke den luksusen å bruke INDEX & MATCH-funksjonen direkte fordi disse to funksjonene ikke er en del av de innebygde VBA-funksjonene. Vi kan imidlertid fortsatt bruke dem som en del av regnearkets funksjonsklasse.
Hvordan bruke Index Match i VBA? (Steg for steg)
Se for eksempel på dataene nedenfor.
I dataene ovenfor er oppslagsverdien avdelingsnavnet, og basert på dette avdelingsnavnet må vi trekke ut lønnsbeløpet.
Men problemet her er at resultatkolonnen er der i den første, og oppslagsverdikolonnen er deretter resultatkolonnen. I dette tilfellet kan ikke VLOOKUP hente lønnsbeløpet fordi VLOOKUP bare fungerer fra høyre til venstre, ikke fra venstre til høyre.
I disse tilfellene må vi bruke kombinasjonsformelen til VBA INDEX & MATCH-funksjonen. La oss utføre oppgaven med å finne lønnsbeløpet for hver avdeling i VBA-koden.
Trinn 1: Start solrutinen.
Trinn 2: Deklarer VBA Integer-variabelen.
Kode:
Sub INDEX_MATCH_Example1 () Dim k Som Integer End Sub
Trinn 3: Åpne nå For Next Loop i VBA.
Kode:
Sub INDEX_MATCH_Example1 () Dim k Som Integer For k = 2 Til 5 Neste k End Sub
Trinn 4: Utfør formelen inne i VBA-sløyfen. I den femte kolonnen må vi bruke formelen, så koden er CELLS (k, 5) .Value =
Kode:
Sub INDEX_MATCH_Example1 () Dim k som heltall For k = 2 til 5 celler (k, 5). Verdi = Neste k End Sub
Trinn 5: I den cellen må vi bruke formelen VBA INDEX & MATCH. Som jeg sa, må vi bruke disse funksjonene som regnearkfunksjon i VBA-klasse, så åpne regnearkfunksjonsklassen.
Kode:
Sub INDEX_MATCH_Example1 () Dim k som heltall for k = 2 til 5 celler (k, 5) .Value = WorksheetFunction. Neste k End Sub
Trinn 6: Etter å ha kommet inn i regnearkfunksjonsklassen, kan vi se alle tilgjengelige regnearkfunksjoner, så velg INDEKS-funksjonen.
Kode:
Sub INDEX_MATCH_Example1 () Dim k Som Integer For k = 2 til 5 celler (k, 5) .Value = WorksheetFunction.Index (Next k End Sub
Trinn 7: Mens du bruker regnearkfunksjon i VBA, må du være helt sikker på argumentene til formelen. Det første argumentet er array dvs. fra hvilken kolonne vi trenger resultatet. I dette tilfellet trenger vi resultatet fra A2 til A5.
Kode:
Sub INDEX_MATCH_Example1 () Dim k as Integer For k = 2 til 5 celler (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), Next k End Sub
Trinn 8: Neste er fra hvilket radnummer vi trenger resultatet. Som vi har sett i det tidligere eksemplet, kan vi ikke oppgi radnummeret manuelt hver gang. Så bruk MATCH-funksjonen.
For å bruke MATCH-funksjonen igjen, må vi åpne klassen Funksjonsarkfunksjon.
Kode:
Sub INDEX_MATCH_Example1 () Dim k som heltall For k = 2 til 5 celler (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Next k End Sub
Trinn 9: MATCH-funksjoner første argument er LOOKUP-verdien; her er oppslagsverdien vår avdelingsnavn; den er der i cellene (2, 4).
Siden hver gang radnummeret må endres, kan vi levere variabelen “k” i stedet for manuell rad nummer 2. Celler (k, 4).
Kode:
Sub INDEX_MATCH_Example1 () Dim k som heltall For k = 2 til 5 celler (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Cells (k, 5) .Value, Next k End Sub
Trinn 10: Deretter må vi nevne avdelingsverdiområdet, dvs. Område (“B2: B5”).
Kode:
Sub INDEX_MATCH_Example1 () Dim k som heltal For k = 2 til 5 celler (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Cells (k, 5) .Value, Range) ("B2: B5"),
Neste k
Slutt Sub
Trinn 11: Sett deretter argumentet som 0 fordi vi trenger en nøyaktig samsvar og lukk parentesene.
Kode:
Sub INDEX_MATCH_Example1 () Dim k som heltall For k = 2 til 5 celler (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Cells (k, 4) .Value, Range ("B2: B5"), 0))
Neste k
Slutt Sub
Ok, vi er ferdige med kodingsdelen. La oss kjøre koden for å få resultatet i kolonne 5.
Så vi fikk resultatet.
Vi kan bruke denne formelen som et alternativ til VLOOKUP-funksjonen.
