Dopasowanie indeksu w VBA
Funkcja INDEKS I DOPASUJ w kombinacji VBA jest alternatywą dla funkcji WYSZUKAJ.PIONOWO w programie Excel. W VBA nie mamy luksusu bezpośredniego korzystania z funkcji INDEKS I PODAJ.POZYCJĘ, 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)
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żyć 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, 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 tablica, z której kolumny potrzebujemy wyniku. W tym przypadku potrzebujemy wyniku od 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: Funkcja MATCH pierwszym argumentem jest wartością WYSZUKAJ; tutaj naszą wartością wyszukiwania są nazwy działów; jest tam 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 wspomnieć o zakresie 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 jako 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.
