Gibt es eine Möglichkeit, Arrays aus anderen Blättern unterschiedlicher Größe mit einer einfachen Funktion auszuwerten?

Nov 25 2020

Ich habe eine Funktion, die eine benutzerdefinierte Funktion und ein Array basierend auf sich ändernden Umständen in Daten auf einem Blatt erstellt. Wenn ich versuche, es auf meinem Hauptblatt auszuwerten, gibt es #value zurück, aber wenn ich es auf dem Blatt auswerte, gibt das Array, von dem es stammt, die richtige Ausgabe zurück. Haben Sie eine Idee, wie Sie dies umgehen können, wenn sich die Größe des 3D-Arrays ändert?

Das Array wird zurückgegeben, weil ich mit einer komplizierten Summenproduktfunktion arbeite. Die Summenproduktfunktion ist die einzige Funktion, die in der Bewertungsformel verschachtelt ist.

Mein Evaluate VBA sieht derzeit so aus:

Function Makeformula(Ref As String)
Application.Volatile
Makeformula = Evaluate(Ref)
End Function

Hallo Ben,

Zusammenfassung: (1a) und (2a) unten zeigen das richtige Ergebnis in Blatt 1, wenn ich die Ausgabe von (1) und (2) kopiere und in neue Zellen einfüge und den Text als Funktion ausführe (durch Hinzufügen des Gleichheitszeichens). . (1) funktioniert mit beiden vorgestellten Lösungen und (2) funktioniert nicht so wie es ist. (2) ist ein Array, da das Summenprodukt zwei Werte für das Summenprodukt zieht.

Ich habe beide Antworten, die Sie gegeben haben, ohne Erfolg ausprobiert. Ich werde unten einen Code anhängen, um es Ihnen zu zeigen, und die lange Formel fett schreiben, um die Augen leichter zu machen.

Ähnlich wie zuvor funktionieren Ihre Antworten, wenn mein Summenprodukt letztendlich den in (1), aber nicht in (2) gezeigten Textwert generiert. Der einzige Unterschied besteht darin, dass (1) kein mehrdimensionales Array ist, während (2) es ist. Es ist auch erwähnenswert, dass, wenn ich (1) und (2) unabhängig voneinander als Textwerte kopiere und in leere Zellen einfüge und ein führendes Gleichheitszeichen setze, sie den richtigen Wert ziehen. (2) wird jedoch mit keiner der von Ihnen bereitgestellten Makeformula-Funktionen ordnungsgemäß ausgewertet, obwohl es als reiner Text ausgewertet werden kann, der nach dem Generieren der Sumproduct-Formel in eine neue Zelle eingefügt wird. (2) führt zu einem #Wert! Fehler wie er ist.

Im Folgenden wird beschrieben, wie sie mit der MakeFormula-Funktion in die Zelle eingegeben werden. Ich habe "Month End Input Frontend" für das zweite Argument in Ihrer Funktion verwendet, da das Summenprodukt letztendlich darauf verweist, wenn es unabhängig ausgeführt wird (siehe (1a) und (2a)). Das Ändern des zweiten Arguments Ihrer ersten Lösung in "Month End Backend" funktioniert nicht so gut.

(1) = MakeFormula2 ( ("SUMPRODUCT ((" & VLOOKUP) (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '!)$C11,'Month End Input Backend'!$B.$4:$D.$78,3,FALSE),'Month End Input Backend'!$H.$4:$K.$27,3,FALSE)&"="&CHAR(34)&U$7 & CHAR (34) & ") (" & LEFT (myvlookup ($C11,'Month End Input Backend'!$B.$4:$E.$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11, 'Month End Input Backend'!$B$4:$D$78,3, FALSE), 'Month End Input Backend'!$H$4:$K$27,4, FALSE))), LEN (myvlookup ($C11,'Month End Input Backend'!$B.$4:$E.$78,2,INDIRECT(VLOOKUP(VLOOKUP('Inc Stmnt ''20 vs ''19'!$C11, 'Month End Input Backend'!$B$4:$D$78,3, FALSE), 'Month End Input Backend'!$H$4:$K$27,4, FALSE)))) - 1) & ") (" & VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '!$C11,'Month End Input Backend'!$B.$4:$D.$78,3,FALSE),'Month End Input Backend'!$H.$4:$K $ 27,2, FALSE) & "))") , "Month End Input Frontend")

^ Dieser funktioniert

(2) = MakeFormula2 ( ("= SUMPRODUCT ((" & VLOOKUP) (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '! $ C35,' Month End Input Backend '!$B$4:$D$78,3, FALSE), 'Month End Input Backend'!$H$4:$K$27,3, FALSE) & "=" & CHAR (34) & U $ 7 & CHAR (34) & ") (" & LEFT (myvlookup ($ C35, 'Month End Input Backend')!$B$4:$E$78,2, INDIREKT (VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 ')!$C35,'Month End Input Backend'!$B.$4:$D.$78,3,FALSE),'Month End Input Backend'!$H.$4:$K.$27,4,FALSE))),LEN(myvlookup($C35, 'Month End Input Backend'!$B$4:$E$78,2, INDIREKT (VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 ')!$C35,'Month End Input Backend'!$B.$4:$D.$78,3,FALSE),'Month End Input Backend'!$H.$4:$K $ 27,4, FALSE)))) - 1) & ") (" & VLOOKUP (VLOOKUP ('Inc Stmnt' '20 vs '' 19 '! $ C35,' Month End Input Backend '!$B$4:$D$78,3, FALSE), 'Month End Input Backend'!$H$4:$K$27,2, FALSE) & "))") , "Month End Input Frontend")

^^ Das tut man nicht.

Nachfolgend sind die Ergebnisse der Summenproduktteile der Formeln aufgeführt, mit denen die Werte in beiden Fällen korrekt abgerufen werden können, wenn sie kopiert und als Textwerte in neue Zellen eingefügt werden.


(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))

Antworten

BigBen Nov 25 2020 at 22:34

Evaluate(oder Application.Evaluate) arbeitet im Kontext des aktiven Blattes. Wenn Sie verwenden möchten Worksheet.Evaluate, müssen Sie der Funktion einen Verweis auf die Worksheetbetreffende Funktion übergeben. Ihre Optionen umfassen:

  • Übergeben Sie den Blattnamen als String:
Public Function MakeFormula(ByVal Ref As String, ByVal SheetName As String) As Variant
    Application.Volatile
    Makeformula = ThisWorkbook.Worksheets(SheetName).Evaluate(Ref)
End Function
  • Übergeben Sie eine RangeReferenz und verwenden Sie Range.Parent. Ihre Formel würde so aussehen=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