Funkcja tablicy UDF z argumentami w programie Excel
Jak sugeruje tytuł, nauczymy się tworzyć w Excelu funkcję zdefiniowaną przez użytkownika, która zwraca tablicę. Nauczyliśmy się już, jak stworzyć funkcję zdefiniowaną przez użytkownika w VBA. Więc bez marnowania czasu zacznijmy od samouczka.
Co to jest funkcja tablicy?
Funkcje tablicowe to funkcje zwracające tablicę, gdy są używane. Te funkcje są używane z kombinacjami klawiszy CTRL+SHIFT+ENTER i dlatego wolimy wywoływanie funkcji tablicowych lub formuł jako funkcji i formuł CSE.
Funkcja tablicowa programu Excel to często wielokomórkowe formuły tablicowe. Jednym z przykładów jest funkcja TRANSPONUJ.
Tworzenie funkcji tablicowej UDF w VBA
Scenariusz jest więc taki, że chcę tylko zwrócić pierwsze 3 liczby parzyste za pomocą funkcji ThreeEven().
Kod będzie wyglądał tak.
Funkcja ThreeEven() As Integer() 'definiuj numery tablicy Dim(2) As Integer 'Przypisz wartości do numerów tablicy (0) = 0 liczb(1) = 2 liczby(2) = 4 'zwróć wartości ThreeEven = liczby Koniec funkcji
Wykorzystajmy tę funkcję w arkuszu.
Widać, że najpierw wybieramy trzy komórki (poziomo, dla pionu musimy użyć dwuwymiarowej tablicy. Omówiliśmy to poniżej.). Następnie zaczynamy pisać naszą formułę. Następnie wciskamy CTRL+SHIFT+ENTER. Wypełnia to zaznaczone komórki wartościami tablicy.
Notatka:
- W praktyce nie będziesz wiedział ile komórek będziesz potrzebować. W takim przypadku zawsze wybieraj więcej komórek niż oczekiwana długość tablicy. Funkcja wypełni komórki tablicą, a dodatkowe komórki pokażą błąd #N/A.
- Domyślnie ta funkcja tablicowa zwraca wartości w tablicy poziomej. Jeśli spróbujesz wybrać komórki pionowe, wszystkie komórki pokażą tylko pierwszą wartość tablicy.
Jak to działa?
Aby utworzyć funkcję tablicową, musisz postępować zgodnie z tą składnią.
Function functionName(variables) As returnType() dim resultArray(length) as dataType 'Przypisz tutaj wartości do tablicy functionName =resultArray Zakończ funkcję
Deklaracja funkcji musi być taka, jak zdefiniowano powyżej. To zadeklarowało, że jest to funkcja tablicowa.
Używając go w arkuszu, musisz użyć kombinacji klawiszy CTRL + SHIFT + ENTER. W przeciwnym razie zwróci tylko pierwszą wartość tablicy.
Funkcja VBA Array, aby zwrócić tablicę pionową
Aby funkcja tablicowa UDF działała w pionie, nie musisz wiele robić. Po prostu zadeklaruj tablice jako tablicę dwuwymiarową. Następnie w pierwszym wymiarze dodaj wartości, a drugi wymiar pozostaw pusty. Tak to się robi:
Funkcja ThreeEven() As Integer() 'zdefiniuj liczby Dim tablicy (2,0) As Integer 'Przypisz wartości do numerów tablicy(0,0) = 0 liczb (1,0) = 2 liczb (2,0) = 4 ' zwracane wartości TrzyParzyste = liczby Koniec funkcji
W ten sposób używasz go w arkuszu.
Funkcja tablicy UDF z argumentami w programie Excel
W powyższych przykładach po prostu wydrukowaliśmy na arkuszu sumaryczne wartości statyczne. Powiedzmy, że chcemy, aby nasza funkcja przyjęła argument zakresu, wykonała na nim pewne operacje i zwróciła wynikową tablicę.
Przykład Dodaj "-done" do każdej wartości w zakresie
Teraz wiem, że można to zrobić łatwo, ale tylko po to, aby pokazać, jak używać zdefiniowanych przez użytkownika funkcji tablicowych VBA do rozwiązywania problemów.
Więc tutaj chcę funkcji tablicowej, która przyjmuje zakres jako argument i dodaje "-done" do każdej wartości w zakresie. Można to łatwo zrobić za pomocą funkcji konkatenacji, ale tutaj użyjemy funkcji tablicowej.
Function CONCATDone(rng As Range) As Variant() Dim resulArr() As Variant 'Utwórz kolekcję Dim col As New Collection 'Dodawanie wartości do kolekcji po błędzie Resume Next For Each v In rng col.Add v Next po błędzie Przejdź do 0 ' zakończenie operacji na każdej wartości i dodanie ich do tablicy ReDim resulArr(col.Count - 1, 0) For i = 0 To col.Count - 1 resulArr(i, 0) = col(i + 1) & "-done" Dalej CONCATDone = resulArr End Function
Wyjaśnienie:
Powyższa funkcja zaakceptuje zakres jako argument i doda "-done" do każdej wartości w zakresie.
Widać, że użyliśmy tutaj kolekcji VBA do przechowywania wartości tablicy, a następnie wykonaliśmy operację na każdej wartości i dodaliśmy je z powrotem do tablicy dwuwymiarowej.
Więc tak, w ten sposób można utworzyć niestandardową funkcję tablicową VBA, która może zwrócić tablicę. Mam nadzieję, że było to wystarczająco wyjaśniające. Jeśli masz jakiekolwiek pytania dotyczące tego artykułu, umieść je w sekcji komentarzy poniżej.
Kliknij poniższy link, aby pobrać działający plik:
Utwórz funkcję VBA, aby zwrócić tablicęTablice w programie Excel Formuła|Dowiedz się, jakie tablice znajdują się w programie Excel.
Jak utworzyć funkcję zdefiniowaną przez użytkownika za pomocą VBA | Dowiedz się, jak tworzyć funkcje zdefiniowane przez użytkownika w programie Excel
Korzystanie z funkcji zdefiniowanej przez użytkownika (UDF) z innego skoroszytu przy użyciu VBA w programie Microsoft Excel | Użyj funkcji zdefiniowanej przez użytkownika w innym skoroszycie programu Excel
Zwróć wartości błędów z funkcji zdefiniowanych przez użytkownika za pomocą VBA w programie Microsoft Excel | Dowiedz się, jak zwrócić wartości błędów z funkcji zdefiniowanej przez użytkownika
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność
Funkcja WYSZUKAJ.PIONOWO w programie Excel
LICZ.JEŻELI w Excelu 2016
Jak korzystać z funkcji SUMIF w programie Excel?