Jak zautomatyzować przeglądanie pionowe za pomocą Excel VBA

Przeglądanie jest podstawową funkcją programu Excel i stała się ważną częścią przetwarzania danych. Udostępnia szereg funkcji, które można połączyć z rozbudowaną bazą danych.

Jeśli jednak chcesz zautomatyzować tę funkcję, możesz, zwłaszcza jeśli wiesz, jak korzystać z języka automatyzacji dostępnego w Excelu i VBA. Oto jak zautomatyzować funkcję przeglądania pionowego w Excel VBA .

Formuła funkcji Vlookup w Excelu

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

Znaczenie argumentów we wzorze:

• lookup_value: Podstawowa wartość, którą chcesz sprawdzić w tablicy.
• lookup_array: Dane źródłowe, wartość_wyszukiwania zostaną użyte jako odniesienie.
• column_index: Kolumna zwraca wartość.
• exact_match/partial_match: Użyj 0 lub 1, aby określić typ kombinacji.

Funkcja VBA-lookup w Excelu

Użycie funkcji Vlookup nie różni się zbytnio, gdy używasz jej bezpośrednio w Excelu lub poprzez VBA. Niezależnie od tego, czy analityk danych przetwarza wiersze informacji, czy kierownik magazynu stale pracuje nad nowymi produktami, przydatna jest funkcja przeglądania.

Podczas pracy z dynamicznym zakresem wyszukiwania najlepiej jest zautomatyzować formułę, aby uniknąć konieczności wielokrotnego manipulowania tą samą formułą w programie Excel. Automatyzując funkcję Vlookup w VBA, możesz jednym kliknięciem wykonać obliczenia wielokolumnowe.

Ilustracja

Ten zestaw danych składa się z dwóch części: tabeli przeglądowej i docelowych punktów danych. Tabela przeglądowa zawiera 3 kolumny z danymi w dwóch polach Podkategoria i Nazwa produktu:

Tabela docelowa zawiera odpowiednie kolumny, nie zawiera podfolderów ani danych dotyczących nazw produktów. Pamiętaj, że wartości w kolumnie Order ID pojawiają się także w tej samej kolumnie tabeli przeglądowej:

1. Dokonaj niezbędnych ustawień

Zacznij od otwarcia edytora kodu (naciśnij Alt + F11 lub przejdź do zakładki Deweloper ) w nowym skoroszycie programu Excel i dodaj moduł, aby rozpocząć kodowanie. W edytorze kodu dodaj następujące wiersze na górze okna programowania, aby utworzyć podprogram:

Sub vlookup_fn1()End Sub

podprogram jest kontenerem na kod VBA i jest wymagany do jego pomyślnego uruchomienia. Inną alternatywą jest utworzenie formularza użytkownika VBA, aby uczynić interfejs użytkownika bardziej interaktywnym.

2. Zadeklaruj zmienne i utwórz zakresy referencyjne

Najpierw musisz zadeklarować typy danych zmiennych za pomocą polecenia Dim:

Dim i as integer, lastrow as long

Następnie utwórz zmienną lastrow , aby zapisać wartość ostatniego wypełnionego wiersza w zakresie wyszukiwania. Funkcja lastrow wykorzystuje funkcję end(xldown) do obliczenia odwołania do ostatniego wiersza w określonym zakresie.

W tym przykładzie zmienna lastrow zawiera wartość ostatniego wypełnionego wiersza w zakresie wyszukiwania, a konkretnie 17.

lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

W powyższym przykładzie tabela docelowa rozciąga się od A2:C17, a tabela źródłowa rozciąga się od F2:H17. Formuła vlookup przeszuka każdą wartość w kolumnie A, wyszuka ją w tabeli źródłowej i wklei połączone wpisy w kolumnach B i C.

Jednak przed wejściem do pętli należy zapisać wartości zakresu w nowej zmiennej ( my_range ) w następujący sposób:

my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

Należy jawnie określić odwołanie do komórki początkowej (F3) i odwołanie do kolumny końcowej (H). VBA automatycznie dodaje wartości wierszy w celu uzupełnienia tablicy odnośników.

3. Utwórz pętlę przeglądającą każdą sprawdzaną wartość

Przed zapisaniem pętli należy zdefiniować wartość wiersza początkowego, 2. Zdefiniowanie wartości początkowej pętli jest konieczne, gdy mamy do czynienia z komponentami dynamicznymi. W tabeli docelowej wiersz 2 jest pierwszym wierszem danych. Zmień tę wartość, jeśli dane zaczynają się w innym wierszu.

i = 2

Możesz użyć pętli do-while do przetwarzania każdego wiersza, zaczynając od wiersza 2 i kończąc na wierszu 17. Podobnie jak reszta kodu, ten aspekt jest elastyczny; Pętla będzie działać, dopóki warunek nie będzie fałszywy. Użyj warunku, aby sprawdzić, czy w pierwszej komórce bieżącego wiersza nie ma pustej wartości.

Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

Wewnątrz pętli możesz wywołać funkcję Vlookup, aby wypełnić komórki:

Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

Polecenia te ustawiają wartości komórek w bieżącym wierszu, odpowiednio w kolumnach 2 i 3. Wykorzystują obiekt WorksheetFunction do wywołania funkcji Vlookup, przekazując odpowiednią wartość z kolumny 1 do wyszukiwania. Przekazują również początkowo zdefiniowany zakres i indeks odpowiedniej kolumny, aby pobrać wartość końcową.

Kod vlookup jest gotowy, ale nadal musisz zwiększać zmienną wiersza za każdym razem w pętli:

i = i + 1

Przy każdym uruchomieniu pętli zwiększa ona wartość i o 1 . Pamiętaj, że początkowa wartość wiersza wynosi 2, przyrost następuje przy każdym uruchomieniu pętli. Użyj słowa kluczowego pętli , aby ustawić koniec tego bloku kodu.

Gdy cały kod zostanie uruchomiony, zapełni obie kolumny wynikami, na podstawie wartości z tabeli źródłowej.

Poniżej znajduje się cały kod referencyjny:

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. Utwórz przycisk uruchamiający kod

Możesz utworzyć przycisk w tabeli programu Excel, aby uruchomić go jednym kliknięciem. Wstaw żądany kształt do pustego arkusza Excela, kliknij go prawym przyciskiem myszy i kliknij opcję Przypisz makro .

W oknie dialogowym wybierz nazwę podprogramu i kliknij OK .

Gdy chcesz uruchomić kod, kliknij przycisk, aby od razu zobaczyć, jak dane zostaną uzupełnione.

Powyżej opisano sposób użycia programu Excel VBA do automatyzacji funkcji wyszukiwania . Mam nadzieję, że artykuł będzie dla Ciebie przydatny.