Funkcja dopasowania Excel VBA
Funkcja dopasowania VBA szuka pozycji lub numeru wiersza wartości wyszukiwania w tablicy tabel, tj. W głównej tabeli programu Excel. Na przykład WYSZUKAJ.PIONOWO, WYSZUKAJ.PIONOWO, PODAJNIK, INDEKS itp. Są to funkcje wyszukiwania, które są ważniejsze niż inne. Niestety, nie mamy takich samych funkcji dostępnych w VBA, aby ułatwić sobie życie. Możemy jednak użyć tych funkcji jako funkcji arkusza w skrypcie VBA, aby ułatwić nam życie.
Dzisiaj mamy zamiar nauczyć się funkcji MATCH, która może być używana jako funkcja arkusza roboczego w VBA.
Dopasowanie VBA ma takie samo zastosowanie jak formuła dopasowania w programie Excel. Ta funkcja w MS Excel VBA znajduje dopasowanie w tablicy w odniesieniu do wartości wyszukiwania i drukuje jej pozycję. Ta funkcja staje się przydatna, gdy trzeba oszacować dane na podstawie określonych wartości. Na przykład VBA MATCH jest pomocny, jeśli masz dane o wynagrodzeniach pracowników i musisz znaleźć w danych liczbowych pozycję pracownika, który ma wynagrodzenie mniejsze niż / większe niż / równe określonej wartości. Jest to bardzo pomocne w analizie danych, a także jeden wiersz kodu może zautomatyzować to za Ciebie.
Składnia funkcji dopasowania w Excel VBA
VBA Match ma następującą składnię:
Gdzie,
- Arg1 - Lookup_value - Wartość, którą należy wyszukać w danej tablicy.
- Arg2 - Tablica_wyszukiwania - tablica wierszy i kolumn, które mogą zawierać wartość_wyszukiwania.
- Arg3 - Typ dopasowania - Typ dopasowania, który przyjmuje wartość -1, 0 lub 1.
Jeśli match_type = -1 oznacza, że funkcja MATCH znajdzie najmniejszą wartość, która jest większa lub równa wartości lookup_value. Aby tak się stało, tablica_wyszukiwania musi być posortowana w kolejności malejącej.
Jeśli match_type = 0 oznacza, że funkcja MATCH znajdzie wartość, która jest dokładnie taka sama jak wartość parametru lookup_value.
Jeśli match_type = +1 oznacza, że funkcja PODAJNIK odkryje największą wartość, która jest mniejsza lub równa wartości wyszukiwania. Aby tak się stało, tablica_wyszukiwania musi być posortowana w porządku rosnącym. Domyślna wartość dla typu dopasowania to +1.
Jak korzystać z funkcji dopasowania Excel VBA?
Nauczymy się korzystać z funkcji VBA Match Excel z kilkoma przykładami.
Możesz pobrać ten szablon Excel VBA Match tutaj - Szablon Excel VBA MatchFunkcja dopasowania VBA - przykład nr 1
Załóżmy, że mamy dane, jak pokazano poniżej:
Musimy dowiedzieć się, kto z tej listy ma wynagrodzenie w wysokości 30 000 EUR wraz ze stanowiskiem w programie Excel.
Chociaż w tym zestawie danych możemy to ręcznie skonfigurować, proszę pomyśleć o szerszym obrazie, a jeśli masz miliony wierszy i kolumn?
Wykonaj poniższe kroki, aby użyć funkcji MATCH w VBA.
Krok 1: Zdefiniuj podprocedurę, nadając nazwę makrze.
Kod:
Sub exmatch1 () End Sub
Krok 2: Teraz chcemy, aby nasze dane wyjściowe były przechowywane w komórce E2. Dlatego zacznij pisać kod jako Range („E2”). Wartość =
Określa zakres wyjściowy dla naszego wyniku.
Kod:
Sub exmatch1 () Zakres („E2”). Wartość = End Sub
Krok 3: Użyj funkcji arkusza roboczego, aby móc korzystać z funkcji VBA.
Kod:
Sub exmatch1 () Zakres („E2”). Wartość = Arkusz roboczy Funkcja Koniec Sub
Krok 4: Arkusz roboczy ma wiele funkcji, do których można uzyskać dostęp i których można używać w języku VBA. Po „WorksheetFunction” umieść kropkę (.), Aby uzyskać dostęp do funkcji. Wybierz funkcję MATCH z rozwijanej listy.
Kod:
Sub exmatch1 () Zakres („E2”). Wartość = WorksheetFunction.Match End Sub
Krok 5: Teraz podaj argumenty funkcji PODAJ.POZYCJĘ. Jak wartość_wyszukiwania. Nasza wartość wyszukiwania jest przechowywana w komórce D2, jak pokazano na poniższym zrzucie ekranu. Możesz uzyskać do niego dostęp w funkcji MATCH za pomocą funkcji Range.
Kod:
Sub exmatch1 () Range („E2”). Value = WorksheetFunction.Match (Range („D2”). Value, End Sub
Krok 6: Drugi argument to tablica_wyszukiwania. Jest to zakres tabeli, w którym chcesz znaleźć pozycję Lookup_value. W naszym przypadku jest to (B1: B11). Podaj tę tablicę za pomocą funkcji Range.
Kod:
Sub exmatch1 () Range („E2”). Value = WorksheetFunction.Match (Range („D2”). Value, Range („B1: B11”), End Sub
Krok 7: Ostatnim argumentem do wypracowania tego kodu jest Match_type. Chcieliśmy mieć dokładne dopasowanie dla wartości Lookup_value w danym zakresie> Dlatego podaj zero (0) jako dopasowany argument.
Kod:
Sub exmatch1 () Range („E2”). Value = WorksheetFunction.Match (Range („D2”). Value, Range („B1: B11”), 0) End Sub
Krok 8: Uruchom ten kod, naciskając F5 lub przycisk Uruchom i zobacz wynik.
W komórce E2 widać wartość liczbową (6), która pokazuje pozycję wartości od komórki D2 do zakresu B1: B11.
Przykład # 2 - Funkcja dopasowania VBA z pętlami
Jest to łatwe, gdy masz tylko jedną wartość do wyszukania w całym zakresie. Ale co, jeśli musisz sprawdzić pozycję dla wielu komórek? Byłoby to trudne dla osoby, która dodaje, aby poprosić go o napisanie osobnych kodów dla każdej komórki.
W takich przypadkach funkcja MATCH może być używana z pętlą (szczególnie w naszym przypadku dla pętli). Zobacz poniższe kroki, aby dowiedzieć się, w jaki sposób korzystamy z funkcji MATCH z pętlą.
Krok 1: Zdefiniuj podprocedurę, nadając nazwę makrze.
Kod:
Sub Przykład 2 () End Sub
Krok 2: Zdefiniuj liczbę całkowitą, która może przechowywać wartość dla wielu komórek w pętli.
Kod:
Sub Przykład 2 () Dim i As Integer End Sub
Krok 3: Użyj pętli For na liczbie całkowitej, aby użyć różnych wartości wyszukiwania, których pozycję można zapisać w kolumnie E.
Kod:
Sub Przykład 2 () Dim i As Integer For i = 2 To 6 End Sub
Krok 4: Teraz skorzystaj z tej samej metody, którą zastosowaliśmy w przykładzie 1, tylko zamiast Range użyjemy funkcji Komórki i będziemy używać tablicy dwuwymiarowej (wiersze i kolumny) w przeciwieństwie do pierwszego przykładu.
Kod:
Podprzykład2 () Dim i jako liczba całkowita dla i = 2 do 6 komórek (i, 5). Wartość = Funkcja arkusza roboczego. Dopasowanie (komórki (i, 4). Wartość, Zakres („B2: B11”), 0) Następny i koniec Pod
Tutaj Komórki (i, 5). Wartość = przechowuje wartość wynikowych pozycji w każdym rzędzie od 2 do 6 (rząd i) w kolumnie E (numer kolumny 5). W funkcji Dopasuj komórki (i, 4). Wartości sprawdzają każdą wartość odnośnika obecną w wierszach od 2 do 6 w czwartej kolumnie. Ta wartość wyszukiwania jest następnie przeszukiwana w szyku B2: B11 w arkuszu programu Excel, w którym znajdują się dane, a pozycje względne można zapisać w każdym wierszu kolumny 5 (kolumna E).
Krok 5: Uruchom ten kod, naciskając jednocześnie klawisz F5 lub Run i zobacz wynik. Prawie wyciągnie magię z kawałka kodu za pomocą jednej linii.
W tym artykule dowiedzieliśmy się, jak używać funkcji PODAJNIK pod VBA jako specjalnego przypadku funkcji WorksheetFunction.
Rzeczy do zapamiętania
- Wartość_wyszukiwania może być liczbą / tekstem / wartością logiczną lub może być odwołaniem komórki do liczby, tekstu lub wartości logicznej.
- Domyślnie typ_typu można uznać za 1, jeśli zostanie pominięty / nie wspomniany.
- Podobnie jak w przypadku funkcji Excel MATCH, VBA MATCH podaje również względną pozycję wartości Lookup w tablicy Lookup_array, a nie samą wartość.
- Jeśli dopasowanie nie zostanie znalezione, względna komórka programu Excel zostanie wypełniona # N / A.
- Jeśli w wartościach tekstowych użyto funkcji PODAJNIK, nie można rozróżnić małych i wielkich liter. Na przykład Lalit i lalit są takie same. Podobnie jak LALIT i lalit.
- Znaków wieloznacznych można użyć, jeśli znajdujesz dokładne dopasowanie (tzn. Typ dopasowania wynosi zero). Znaku wieloznacznego (*) można użyć do znalezienia serii znaków. Podczas gdy znak zapytania (?) Może być użyty do znalezienia jednego znaku.
Polecane artykuły
Jest to przewodnik po funkcji dopasowania VBA. Tutaj omawiamy VBA Match i jak korzystać z Excela VBA Match Function wraz z praktycznymi przykładami i szablonem Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -
- Kompletny przewodnik po VBA o błędzie
- Jak korzystać z formatu liczb VBA?
- Funkcja VBA VLOOKUP z przykładami
- Tworzenie funkcji VBA w Excelu
- Funkcja dopasowania Excela (przykłady)