Dopasowanie indeksu VBA

Dopasowanie indeksu w VBA

Funkcja INDEX & MATCH w kombinacji VBA jest alternatywą dla funkcji WYSZUKAJ.PIONOWO w programie Excel. W VBA nie mamy luksusu bezpośredniego korzystania z funkcji INDEKS I DOPASUJ, ponieważ te dwie funkcje nie są częścią wbudowanych funkcji VBA. Jednak nadal możemy ich używać jako części klasy funkcji arkusza.

Jak korzystać z dopasowania indeksów w VBA? (Krok po kroku)

Możesz pobrać ten szablon VBA Index Match Excel tutaj - VBA Index Match szablon Excel

Na przykład spójrz na poniższe dane.

W powyższych danych wartością wyszukiwania jest nazwa działu i na podstawie tej nazwy działu musimy wyodrębnić kwotę wynagrodzenia.

Ale problem polega na tym, że kolumna wyników znajduje się w pierwszej, a kolumna wartości wyszukiwania znajduje się później w kolumnie wyników. W takim przypadku WYSZUKAJ.PIONOWO nie może pobrać kwoty wynagrodzenia, ponieważ WYSZUKAJ.PIONOWO działa tylko od prawej do lewej, a nie od lewej do prawej.

W takich przypadkach musimy użyć formuły kombinacji funkcji VBA INDEX & MATCH. Wykonajmy zadanie znalezienia wysokości wynagrodzenia każdego działu w kodzie VBA.

Krok 1: Rozpocznij rutynę opalania.

Krok 2: Zadeklaruj zmienną VBA Integer.

Kod:

 Sub INDEX_MATCH_Example1 () Dim k As Integer End Sub 

Krok 3: Teraz otwórz dla następnej pętli w VBA.

Kod:

 Sub INDEX_MATCH_Example1 () Dim k As Integer For k = 2 To 5 Next k End Sub 

Krok 4: Wewnątrz pętli VBA wykonaj formułę. W piątej kolumnie musimy zastosować formułę, więc kod to CELLS (k, 5) .Value =

Kod:

 Sub INDEX_MATCH_Example1 () Dim k As Integer For k = 2 to 5 Cells (k, 5) .Value = Next k End Sub 

Krok 5: W tej komórce musimy zastosować formułę VBA INDEX & MATCH. Jak powiedziałem, musimy używać tych funkcji jako funkcji arkusza roboczego w klasie vba, więc otwórz klasę funkcji arkusza.

Kod:

Sub INDEX_MATCH_Example1 () Dim k As Integer For k = 2 to 5 Cells (k, 5) .Value = WorksheetFunction. Dalej k End Sub

Krok 6: Po wejściu do klasy funkcji arkusza roboczego możemy zobaczyć wszystkie dostępne funkcje arkusza roboczego, więc wybierz funkcję INDEKS.

Kod:

 Sub INDEX_MATCH_Example1 () Dim k As Integer For k = 2 to 5 Cells (k, 5) .Value = WorksheetFunction.Index (Next k End Sub 

Krok 7: Korzystając z funkcji arkusza w VBA, musisz mieć absolutną pewność co do argumentów formuły. Pierwszym argumentem jest tablica czyli z jakiej kolumny potrzebujemy wyniku, w tym przypadku potrzebujemy wyniku z A2 do A5.

Kod:

 Sub INDEX_MATCH_Example1 () Dim k As Integer For k = 2 to 5 Cells (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), Next k End Sub 

Krok 8: Następny numer wiersza to wynik. Jak widzieliśmy we wcześniejszym przykładzie, nie możemy za każdym razem ręcznie podać numeru wiersza. Więc użyj funkcji MATCH.

Aby ponownie użyć funkcji MATCH, musimy otworzyć klasę Worksheet Function.

Kod:

 Sub INDEX_MATCH_Example1 () Dim k As Integer For k = 2 to 5 Cells (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Next k End Sub 

Krok 9: Pierwszym argumentem funkcji PODAJ.POZYCJĘ jest wartość WYSZUKAJ, tutaj naszą wartością wyszukiwania są nazwy działów, znajduje się ona w komórkach (2, 4).

Ponieważ za każdym razem, gdy numer wiersza musi się zmienić, możemy podać zmienną „k” zamiast ręcznego numeru wiersza 2. Komórki (k, 4).

Kod:

 Sub INDEX_MATCH_Example1 () Dim k As Integer For k = 2 to 5 Cells (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Cells (k, 5) .Value, Next k End Sub 

Krok 10: Następnie musimy podać zakres wartości działu, czyli Range („B2: B5”).

Kod:

 Sub INDEX_MATCH_Example1 () Dim k As Integer For k = 2 to 5 Cells (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Cells (k, 5) .Value, Range („B2: B5”), 

Dalej k

Napis końcowy

Krok 11: Następnie ustaw argument na 0, ponieważ potrzebujemy dokładnego dopasowania i zamknij nawiasy.

Kod:

 Sub INDEX_MATCH_Example1 () Dim k As Integer For k = 2 to 5 Cells (k, 5) .Value = WorksheetFunction.Index (Range ("A2: A5"), WorksheetFunction.Match (Cells (k, 4) .Value, Range („B2: B5”), 0)) 

Dalej k

Napis końcowy

Ok, skończyliśmy z kodowaniem. Uruchommy kod, aby uzyskać wynik w kolumnie 5.

Tak więc otrzymaliśmy wynik.

Możemy użyć tej formuły jako alternatywy dla funkcji WYSZUKAJ.PIONOWO.