Kategorie szkoleń | Egzaminy | Kontakt
  • 3
  • 4
  • 1.5K

Chciałbym stworzyć funkcję, która w argumencie przyjmuje liczbę a, w drugim wartość n (edit: albo szereg wartości n z zakresu kilku komórek) i zwraca szereg (edit albo nawet tablicę) wartości a, a^1, a^2, a^3, a^4, a^5... a^n.

Chodzi o przykład najprostszej funkcji tablicowej UDF (array formula UDF), takiej, którą można zatwierdzić Ctrl+Shift+Enter i która się zachowuje tak, jak funkcje "fabryczne", tj. może przyjąć jako argument kilka wartości (z zakresu albo w nawiasie kwadratowym) i zwrócić szereg wyników, które można zwrócić do kilku komórek lub np. zsumować w tej samej komórce
=sum(funkcja_tablicowa()).

Jacek_Kotowski
  • Zapytał
  • @ Jacek_Kotowski | 01.08.2014
    • lider
    • laureat
    • ekspert
    • 60
    • 54
    • 94

Odpowiedzi (3)

  • 6

Witam.

Jeśli dobrze zrozumiałem pytanie, nie ma potrzeby tworzenia specjalnej nowej funkcji - wystarczy wykorzystać narzędzia już istniejące w Excelu: formułę tablicową albo narzędzie Tabela danych z grupy narzędzi Analiza warunkowa.

Wydaje mi się, że do uzyskania efektu, o jakim Pan pisze, prościej będzie użyć narzędzia Tabela danych (proszę go jednak nie mylić z obiektem Tabela).

Najpierw przygotowuję komórki zawierające zmienne i komórki, przez które będą te zmienne "wchodzić do obliczeń". Dla łatwiejszego zrozumienia komórki z zmiennymi (a i n) zaznaczyłem na poniższym przykładzie żółtym tłem:

 

 

Przy przygotowywaniu szkieletu tabeli należy pilnować, żeby komórka z obliczeniem znajdowała się dokładnie na przecięciu wiersza i kolumny, w których znajdują się dwie serie zmiennych. W tym przypadku jest to B1^B2 (liczba z komórki B1 do potęgi liczby z komórki B2). Komórki B1 i B2 będą przyjmowały zmienne z wiersza i kolumny tabeli i będą je dalej przekazywały do obliczenia w narożniku. A narzędzie Tabela danych umieści wyniki obliczenia  w komórkach na przecięciach komórek wiersza i kolumny.

Teraz czas na finał: Zaznaczam tabelę w taki sposób, żeby komórka z obliczeniem stała się narożną komórką zaznaczenia:

 

 

Po zaznaczeniu komórek wchodzimy na wstążkę Dane i szukamy w sekcji Narzędzia danych przycisku Analiza warunkowa:

 

 

A tam już jest polecenie Tabela danych, które wywołuje następujące okienko:

 

 

Tu pozostaje wskazać, która komórka na przyjmować zmienne z wiersza (wierszowa komórka wejściowa) i przekazywać je do obliczenia - tu wpisuję B1. Jako kolumnową komórkę wejściową wybieram B2. Po wciśnięciu OK otrzymujemy formułę tablicową z dwiema zmiennymi:

 

 

Efekt najprościej jest porównać do tabliczki mnożenia: każda liczba z żółtego wiersza tabeli jest podnoszona do potęgi każdej liczby z żółtej kolumny. Wyniki znajdują się na przecięciach wierszy i kolumn np. liczba 2 z górnego wiersza daje wynik 8 na przecięciu z liczbą 3 z pierwszej kolumny (2 do potęgi 3 daje 8).

 

Excel jest tak rozbudowanym narzędziem, że w ciągu jednego życia już chyba nie da się go ogarnąć w całości.:-) Pański wpis przypomniał mi odpowiedź kolegi (programisty VBA) na pytanie o parametry którejś z rzadziej używanych funkcji, wbudowanych w Excela. Kolega podrapał sie po głowie i powiedział: "Wiesz, ja tej funkcji kompletnie nie kojarzę, ale identycznie działajacą mogę ci napisać w pięć minut." :-) Może za jakiś czas wspomniany kolega podpowie Panu, jak napisać tę pańską funkcję w VBA. :-)

Generalnie - zanim zacznie Pan tworzyć cokolwiek nowego w VBA - warto się rozejrzeć, czy nie ma w Excelu jakiegoś standardowego narzędzia, które jest w stanie wykonać oczekiwaną pracę. Myślę, że VBA raczej powinno być używane do rozszerzania możliwości programu, niż do dublowania już istniejących narzędzi.

Cały czas zakładam, że dobrze zrozumiałem pańskie pytanie. :-)

Pozdrawiam.
Andrzej Słodownik.

 

  • Odpowiedział
  • @ | 05.08.2014
  • TRENER ALTKOM AKADEMII
  • 2

Dziękuję za odpowiedź, wiem, że wiele rozwiązań w Excelu już istnieje, jednak nie zależy mi na stosowaniu tabeli danych tylko na tworzeniu funkcji UDF, która mogłaby zachowywać się jak funkcja tablicowa (array formula, Ctrl+Shift+Enter), by mogła ona przyjmować w miejsce argumentu np. kilka wartości  w nawiasie kwadratowym albo w postaci zakresu z kilkoma wartościami zamiast jednej i zwracać n wartości.

W istocie poszukuję prostego przykładu takiej funkcji, nawet takiego, który łatwo byłoby wykonać za pomocą istniejącego rozwiązania. Taki prosty przykład pomógłby mi tworzyć bardziej zaawansowane rozwiązania.

Wyobrażam sobie, że po prostu należałoby jakoś zadeklarować zmienne wejściowe, argumenty jako tablice i pozwolić tej funkcji zwracać wartości jako tablice.

Dlatego chciałbym zapytać o przykład takiej funkcji UDF.

Jak dotąd udało mi się znaleźć w sieci parę takich przykładów, które usiłuję przeanalizować, jednak szukam jakiegoś prostszego przykładu np.:

http://www.cpearson.com/excel/returningarraysfromvba.aspx

http://fastexcel.wordpress.com/2011/06/20/writing-efiicient-vba-udfs-part5-udf-array-formulas-go-faster/

Chciałem zapytać na szkoleniu EX04, jednak wtedy był akurat szereg innych równie interesujących tematów i po prostu nie zdążyłem.

 

 

Jacek_Kotowski
  • Odpowiedział
  • @ Jacek_Kotowski | 06.08.2014
    • lider
    • laureat
    • ekspert
    • 60
    • 54
    • 94
  • 4

Na początku pozwolę sobie uprościć rozwiązanie pana Andrzeja Słodownika. Wystarczy wpisać w zakresie C5:L19 formułę CSE (tablicową):

{=C4:L4^B5:B19}

Pokazuje to rysunek poniżej:

 

 

A teraz szkic przykładowej funkcji UDF (o nazwie dziwna), równie prosty:

Jak widać, funkcja po prostu musi zwracać tablicę.

 

If został użyty do ewentualnej transpozycji wyniku. Bez tej konstrukcji można by używać tej funkcji UDF w formułach tablicowych tylko w poziomie (w wierszu). Proszę spróbować.

Warto poczytać więcej o Application.Caller. Tylko jeżeli funkcja zostanie wywołana z komórki lub zakresu (a nie z VBA), to Application.Caller będzie zakresem i będzie można zliczyć jego wymiary poprzez Application.Caller.Rows.Count itp.

 

I wreszcie przykład zastosowania w arkuszu:

 

 

Na ilustracji powyżej widać treść formuł z kolorowych zakresów, w komórkach o analogicznym kolorze.Proszę zwrócić uwagę, że możliwe jest zastosowanie tej funkcji w formułach tablicowych jedno- i wielokomórkowych.

 

Mam nadzieję, że dobrze zrozumiałem pytanie i odpowiedź będzie pomocna. W załączeniu plik.

Załączniki

  • xlsm

    dziwna_UDF.xlsm ( 19K )
  • Odpowiedział
  • @ | 07.08.2014
  • TRENER ALTKOM AKADEMII