Czy istnieje sposób na ocenę tablic z innych arkuszy o różnych rozmiarach za pomocą prostej funkcji?
Mam funkcję, która tworzy niestandardową funkcję i tablicę na podstawie zmieniających się okoliczności w danych na jednym arkuszu. Jeśli spróbuję oszacować to w moim głównym arkuszu, zwraca #value, ale jeśli oszacuję to na arkuszu, tablica pochodzi z tego, zwraca prawidłowe dane wyjściowe. Masz jakiś pomysł, jak to obejść, jeśli zmienia się rozmiar tablicy 3-wymiarowej?
Tablica jest zwracana, ponieważ pracuję ze skomplikowaną funkcją sumproduct. Funkcja sumaryczna jest jedyną funkcją zagnieżdżoną w formule obliczeniowej.
Moja ocena VBA wygląda obecnie tak:
Function Makeformula(Ref As String)
Application.Volatile
Makeformula = Evaluate(Ref)
End Function
Cześć Ben,
Podsumowanie: (1a) i (2a) poniżej wyciągną właściwy wynik z arkusza 1, jeśli skopiuję i wkleję dane wyjściowe (1) i (2) do nowych komórek i uruchomię tekst jako funkcję (dodając znak równości) . (1) działa z każdym przedstawionym rozwiązaniem, a (2) nie jest takie, jakie jest. (2) jest tablicą, ponieważ iloczyn sumy pobiera dwie wartości z iloczynu sumarycznego.
Wypróbowałem obie odpowiedzi, które dałeś, bezskutecznie. Załączę kod poniżej, aby pokazać i pogrubić formułę Long, aby ułatwić oczy.
Podobnie jak wcześniej, Twoje odpowiedzi działają, gdy mój iloczyn sumaryczny ostatecznie generuje wartość tekstową widoczną w (1), ale nie (2). Jedyna różnica polega na tym, że (1) nie jest tablicą wielowymiarową, a (2) jest. Warto również zauważyć, że kiedy kopiuję i wklejam (1) i (2) niezależnie jako wartości tekstowe siebie do pustych komórek i umieszczam wiodący znak równości, pobierają właściwą wartość. Jednak (2) nie jest poprawnie obliczany za pomocą żadnej z podanych funkcji Makeformula, mimo że można go ocenić jako czysty tekst wstawiony do nowej komórki po wygenerowaniu formuły Sumproduct. (2) prowadzi do #Value! błąd, jak jest.
Poniżej znajduje się sposób, w jaki są wprowadzane do komórki za pomocą funkcji MakeFormula. Użyłem „Month End Input Frontend” jako drugiego argumentu w twojej funkcji, ponieważ do tego ostatecznie odwołuje się sumproduct, jeśli jest uruchamiany niezależnie, jak widać w (1a) i (2a). Zmiana drugiego argumentu pierwszego rozwiązania na „Backend na koniec miesiąca” również nie działa.
(1) = MakeFormula2 ( ("SUMPRODUCT ((" & WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO ('Inc Stmnt' ''20 vs' '19'!$C11,'Month End Input Backend'!$b$4:$re$78,3,FALSE),'Month End Input Backend'!$H.$4:$K.$27,3,FALSE)&"="&CHAR(34)&U$7 & ZNAK (34) & ") (" & LEWO (myvlookup ($C11,'Month End Input Backend'!$b$4:$mi$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11, „Backend wprowadzania danych na koniec miesiąca”!$B$4:$D$78,3, FALSE), „zaplecze wejściowe na koniec miesiąca”!$H$4:$K$27,4; FAŁSZ))), LEN (myvlookup ($C11,'Month End Input Backend'!$b$4:$mi$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11, „Backend wprowadzania danych na koniec miesiąca”!$B$4:$D$78,3, FALSE), „zaplecze wejściowe na koniec miesiąca”!$H$4:$K$27,4, FAŁSZ)))) - 1) & ") (" & WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO ('Inc Stmnt' '20 vs' '19'!$C11,'Month End Input Backend'!$b$4:$re$78,3,FALSE),'Month End Input Backend'!$H.$4:$27,2 K $, FALSE) & "))") , "Interfejs wprowadzania danych na koniec miesiąca")
^ Ten działa
(2) = MakeFormula2 ( ("= SUMPRODUCT ((" & WYSZUKAJ.PIONOWO (WYSZUKAJ.$B$4:$D$78,3, FALSE), „zaplecze wejściowe na koniec miesiąca”!$H$4:$K$27,3, FALSE) & "=" & CHAR (34) & U $ 7 & CHAR (34) & ") (" & LEFT (myvlookup ($ C35, 'zaplecze wejściowe na koniec miesiąca')!$B$4:$E$78,2, POŚREDNIE (WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO („Inc Stmnt”) „20 vs„ 19 ”!$C35,'Month End Input Backend'!$b$4:$re$78,3,FALSE),'Month End Input Backend'!$H.$4:$K.$27,4,FALSE))),LEN(myvlookup($C35, „Backend wprowadzania danych na koniec miesiąca”!$B$4:$E$78,2, POŚREDNIE (WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO („Inc Stmnt”) „20 vs„ 19 ”!$C35,'Month End Input Backend'!$b$4:$re$78,3,FALSE),'Month End Input Backend'!$H.$4:$K $ 27,4, FALSE)))) - 1) & ") (" & WYSZUKAJ.PIONOWO (WYSZUKAJ.$B$4:$D$78,3, FALSE), „zaplecze wejściowe na koniec miesiąca”!$H$4:$K$27,2, FALSE) & „))”) , „Interfejs wprowadzania danych na koniec miesiąca”)
^^ Ten nie.
Poniżej znajdują się wyniki części sumproduktu formuł, które mogą poprawnie pobierać wartości w obu przypadkach, jeśli zostaną skopiowane i wklejone jako wartości tekstowe do nowych komórek.
(1a) SUMPRODUCT(('Month End Input Frontend'!$C$12:$Q$12="Jan 2020")*(('Month End Input Frontend'!B13:B89=" Sales of Product Income"))*('Month End Input Frontend'!$C$13:$Q$89))
(2a) SUMPRODUCT(('Month End Input Frontend'!$C$12:$Q$12="Jan 2020")*(('Month End Input Frontend'!B13:B89=" Billing Fee Expense")+('Month End Input Frontend'!B13:B89=" Medical Billing and Collection"))*('Month End Input Frontend'!$C$13:$Q$89))
Odpowiedzi
Evaluate
(lub Application.Evaluate
) działa w kontekście aktywnego arkusza. Chcesz użyć Worksheet.Evaluate
, więc musisz przekazać funkcji odniesienie do danego Worksheet
pytania. Twoje opcje obejmują:
- Podaj nazwę arkusza jako
String
:
Public Function MakeFormula(ByVal Ref As String, ByVal SheetName As String) As Variant
Application.Volatile
Makeformula = ThisWorkbook.Worksheets(SheetName).Evaluate(Ref)
End Function
- Podaj
Range
referencje i użyjRange.Parent
. Twoja formuła będzie wyglądać=MakeFormula("yourlongformula", 'Sheet 2'!A1)
Public Function MakeFormula(ByVal Ref As String, ByVal Cell As Range) As Variant
Application.Volatile
Makeformula = Cell.Parent.Evaluate(Ref)
End Function