[Dakkar]

Mirki pomóżcie. Mam kilka dość skomplikowanych formuł, które trzeba wykonać dla każdego wierszu z danymi. Chciałbym aby policzyło je makro. Z tego co mi się wydaje, powinienem w takim wypadku użyć funkcji, którą zapętle X razy. Przykładowa formuła wygląda w ten sposób:

=IF(OR(RC[-9]=RC[-8],RC[-2]=1),0,IFERROR(IF(AND(RC[-6]=1,RC[-7]<VLOOKUP(RC[-9],R5C42:R45C43,2,0)),1,0),0))

Jak z tego zrobić funkcję? Próbowałem w ten sposób ale to raczej nie jest takie proste (nie działa zresztą - wyskakuje błąd expected expression):

Function Test() As String
test =IF(OR(RC[-9]=RC[-8],RC[-2]=1),0,IFERROR(IF(AND(RC[-6]=1,RC[-7]<VLOOKUP(RC[-9],R5C42:R45C43,2,0)),1,0),0))
End Function

#excel #programowanie

[brak_nicku]

@Dakkar: Jeśli koniecznie chcesz obliczać wartość formuły w funkcji to albo trzeba użyć funkcji Evaluate, albo pozamieniać odpowiednie formuły składowe na ich odpowiedniki z klasy WorksheetFunction. Sytuację pogarsza fakt, że funkcja test nie ma parametrów.

Tylko tu pojawia się pytanie: po co tworzyć funkcję, która oblicza wartość formuły? Chyba warto, żebyś napisał co dokładnie chcesz osiągnąć, bo jeśli chodzi na przykład o zamianę wyniku formuły na wartość, to zdecydowanie nie tędy

[Dakkar]

@brak_nicku: Działam obecnie na arkuszu, który przeciętnie ma około 700 tys. wierszy i 15 kolumn z danymi wejściowymi, na których wykonywane są obliczenia w kolejnych 24 kolumnach (a dochodzą do tego jeszcze obliczenia z innych arkuszy). Nie powinno się tego robić w excelu ale przyjmijmy, że nie mam opcji żeby to zmienić.

Żeby jakoś sobie z tym poradzić, kolumny ułożone są według kolejności działań od lewej, do prawej. W momencie kiedy

[brak_nicku]

@Dakkar: jeśli masz 700 tys. wierszy i będziesz próbował jak mówisz:
- wiersz po wierszu obliczać wynik formuły dla 24 kolumn
- zapisywać wynik w odpowiedniej komórce

To zapewniam, że to rozwiązanie będzie co najmniej o rząd wielkości wolniejsze od takiego:
- obliczyć wynik formuły dla całej kolumny
- zapisać wynik jako wartości

To drugie rozwiązanie wygląda mniej więcej tak, jak opisujesz, że działa to w tej chwili. Powinno to wyglądać

[Dakkar]

@brak_nicku: Żebyśmy się dobrze zrozumieli przygotowałem małą wizualizację ;-) Ogółem dopiero uczę się vba, więc nie wszystko jest dla mnie jasne.

Metoda 1 - tak to obecnie działa
Metoda 2 - tak chciałbym żeby działało
Metoda 3 - tak też jest ok, ale słyszałem, że ten rodzaj pętli jest wolniejszy od tego z modelu 2

A w jaki sposób wykonywany byłby kod Twoją metodą? Domyślam się, że podobnie do metody 1?

[brak_nicku]

@Dakkar: mój kod działa na zasadzie (tłumacząc to na działania użytkownika):
- zaznacz cały zakres wynikowy
- wpisz formułę
- naciśnij Ctrl+Enter
- skopiuj wynik formuły jako wartości
czas wykonania powinien być zbliżony do tego z Metoda 1 (o ile rzeczywiście kod tak działa)

[brak_nicku]

@Dakkar: Metoda numer 2 - czyli wyliczaj wynik formuły i wpisuj wiersz po wierszu - nie ma szans, żeby wynik był choć zbliżony do nr 1 - będzie o rząd wielkości wolniej

[brak_nicku]

@Dakkar: metoda nr 3 - jeśli wczytasz wszystkie dane wejściowe do tablicy, wykonasz wszelkie obliczenia i zapiszesz w tablicy wynikowej, którą następnie zapiszesz w arkuszu - to może działać szybciej niż metoda nr 1 - ale wszystko zależy od specyfiki formuł

[brak_nicku]

Z formuł wyciśnięty jest już maks i dalej raczej nie da się ich zoptymalizować albo z innych względów nie mogę tego zrobić.

Co masz na myśli, pytając o to czy elementy składowe się powtarzają?

@brak_nicku: na przykład jeśli masz w kilku kolumnach wyszukiwanie tej samej wartości za pomocą VLOOKUP, po to by zwrócić różne kolumny z tabeli pomocniczej, to warto to zmienić na jedną dodatkową MATCH i kilka INDEKS. Jednak znacznie

[Dakkar]

@brak_nicku: Jeśli chodzi o formuły, to znam te wszystkie sztuczki i już są zastosowane, tam gdzie mogą.

1. Czy mógłbyś dokładniej wytłumaczyć o co chodzi z tymi tablicami wynikowymi?
2. Co do metody 3 lub 2 czy mógłbyś mi wytłumaczyć jak to zrobić? Mimo wszystko chciałbym przetestować tą metodę. Czy do takich przypadków stosuje się właśnie w vba funkcję, czy funkcje wykorzystuje się do czegoś innego?

[brak_nicku]

@Dakkar: Właśnie mi się przypomniało, że już kiedyś robiłem mały przykład różnych metod wyliczania tego samego:
https://pastebin.com/W05hrHSX

Użyta tam funkcja MicroTimer pochodzi stąd: http://www.vbaexpress.com/kb/getarticle.php?kb_id=1068

Mamy milion wierszy x 2 kolumny i chcemy je skleić (połączone spacją). Daje to taki wynik:

[brak_nicku]

Czasy są w milisekundach. Czemu metoda z wypełnianiem danych wiersz po wierszu jest 10+ razy wolniejsza od 2 pozostałych? Bo każdy pojedynczy zapis/odczyt wartości komórki w VBA jest operacją czasochłonną, zatem Metoda nr 2 nigdy nie jest optymalna - co najwyżej przy małej ilości danych niewiele się traci.

[brak_nicku]

Metoda nr 3 ma sens tylko wtedy, gdy obliczenia wykonywane przez formuły jesteś w stanie wykonać za pomocą samego VBA (bez formuł) w czasie krótszym niż formuła. To jest często możliwe, ale to zależy już tylko i wyłącznie od tego, jakie to konkretnie są formuły.

[brak_nicku]

Czy do takich przypadków stosuje się właśnie w vba funkcję, czy funkcje wykorzystuje się do czegoś innego?

@Dakkar: Zasadniczo funkcje używa w przypadku, gdy takie same obliczenia wykonujesz kilkukrotnie w kodzie w różnych miejscach. Wtedy zamiast powtarzać te same linie kodu kilka razy - tworzysz funkcję, która to oblicza, a następnie zamiast replikować ten sam kod - wywołujesz funkcję. Czyli (tylko i aż) otrzymujesz krótszy, łatwiejszy w późniejszej pielęgnacji kod -

[Dakkar]

@brak_nicku: ok trochę się już rozjaśnia

metoda nr 3 - jeśli wczytasz wszystkie dane wejściowe do tablicy, wykonasz wszelkie obliczenia i zapiszesz w tablicy wynikowej, którą następnie zapiszesz w arkuszu - to może działać szybciej niż metoda nr 1 - ale wszystko zależy od specyfiki formuł

Tego fragmentu nie rozumiem. O jakiej tablicy wynikowej mówimy?

W swoim przykładzie w ostatniej kolumnie także podajesz wyniki czasu obliczeń dla tablicy. O jakiej metodzie

[brak_nicku]

W swoim przykładzie w ostatniej kolumnie także podajesz wyniki czasu obliczeń dla tablicy. O jakiej metodzie mówimy? O tej, czy o czymś innym?

@Dakkar: a patrzyłeś na kod z tego linku:
https://pastebin.com/W05hrHSX

Tam jest podany kod funkcji Tablica: wczytujesz całe dane wejściowe do jednej zmiennej, wynik zapisujesz w drugiej, na koniec zapisujesz wartość tej zmiennej do zakresu

[brak_nicku]

Tam jest podany kod funkcji Tablica

@Dakkar: znaczy procedury

[Dakkar]

@brak_nicku: Przyjrzę się jeszcze, bo w tej chwili nie mam możliwości tego przetestować, a łatwiej mi w ten sposób zrozumieć kod. Dziękuję