Nun gut, das ist aber Empirie, nicht Kenntnis ...
23.01.2016 04:11:09
Luc:-?
…(bzw Vorstellung von) interner(/n) Abläufe(n), Werner,
wie du ja selbst immer wieder betonst! Und was heißt „nicht tragfähig“? Es ist doch ganz leicht feststellbar und damit auch „einzusehen“, dass eine einzelne ZellFml in jeder Zelle, in der sie steht, komplett neu berechnet wdn muss. Das ist bei mehrzelligen Matrix- und wohl auch benannten Fmln eben nicht so - sie wdn nur 1-2mal komplett berechnet und die Ergebniswerte auf alle zugehörigen, also markierten Zellen verteilt. Wurden zuwenig markiert, fehlen Werte, sind es zuviel, erscheint in diesen Zellen der Fwert #NV. Meine Bspp mit benannten FmlTeilen wirken quasi wie INDEX - die benannte Fml wurde extern berechnet und die ZellFml holt nur den jeweiligen Ergebniswert ab. Das ist allemal rationeller als die Fml immer wieder komplett neu zu berechnen. Bei wenigen Daten macht das ja nichts aus, aber bei sehr vielen schon. Du versuchst doch selber stets volatile Fktt zu vermeiden, hier aber verursachst du rechnerischen Mehraufwand, wenn auch nicht andauernd wie bei INDIREKT & Co.
Ich habe deine AW gelesen, aber mir fiel die von dir erwähnte Eindeutigkeit nicht auf, da du auf den HptPkt meiner Ergänzung ja gar nicht eingegangen bist, weshalb ich alle evtl Bezüge nur auf meinen Erst-BT bezogen hatte, worauf mir auch dein Betreff hinzudeuten schien. Es ist nämlich eindeutig und nachweislich so, dass Xl grundsätzlich alle Werte eines Datenfeldes ermittelt, nur nicht immer alle weiterverwendet. Die Fktt, die das vermögen, greifen wohl direkt auf diese Schnittstelle zu, wobei es dabei Grenzen gibt. Anders sieht es bei skalar erwarteten Argumenten aus. Da ist idR nicht unbedingt zu erwarten, dass ein Datenfeld über ggf hier übergebene Datenfelder bzw Bereiche aufgebaut wird. Das kann man schon am Fml-Assi ablesen. Hierfür ist idR MxFml-Form zwingend erforderlich, ja, es sieht beinahe so aus, als ob das dann auch für benannte Fmln gelten würde. INDEX ist exemplarisch dafür (keine Überlistung möglich).
So, jetzt habe ich es aber doch noch getan, obwohl ich es eigentlich für überflüssig halte. Die neueste Version meiner Bsp-UDF enthält nun auch eine interne Selbstauswertung, was in deinem Sinne quasi Matrix(formel)funktionalität bedeutet. Da die vbFkt Evaluate genauso wie die XLM-Fkt AUSWERTEN interne Xl-Mechanismen in Anspruch nimmt, also so etwas wie eine verborgene 2.Xl-Instanz, kann man einen FmlText nicht auswerten, wenn er eine Fkt enthält, die dabei selber aktiv auf diesen Mechanismus zugreift. Aber meine diesbzgl Erweiterung ist optional, weshalb das für FmlTexte mit dieser Fkt nur fallweise zutrifft. Das bedeutet aber auch, dass Xl tatsächlich alle Werte berechnet und die Fkt dann daraus den gewünschten auswählt, denn hierbei ist dann auch keine MatrixFmlForm erforderlich wie im Folgenden zu sehen ist:
| I | J | K | L | M | N | O | P |
---|
1 | AGGREGAT 1zellig | {KKLEINSTE 1zellig} | {AggregateXk 1zellig} | {AggregateXk m-zellig} | {AGGREGAT m-zellig} | KKLEINSTE 1zellig | AggregateXk 1zellig | AggregateXk 1zellig* |
---|
2 | User1@xyz.de | User1@xyz.de | User1@xyz.de | User1@xyz.de | User1@xyz.de | User1@xyz.de | User1@xyz.de | User1@xyz.de |
---|
3 | User3@xyz.de | User3@xyz.de | User3@xyz.de | User3@xyz.de | User3@xyz.de | User3@xyz.de | User3@xyz.de | User3@xyz.de |
---|
4 | User8@xyz.de | User8@xyz.de | User8@xyz.de | User8@xyz.de | User8@xyz.de | User8@xyz.de | User8@xyz.de | User8@xyz.de |
---|
5 | User13@xyz.de | User13@xyz.de | User13@xyz.de | User13@xyz.de | User13@xyz.de | User13@xyz.de | User13@xyz.de | User13@xyz.de |
---|
6 | User16@xyz.de | User16@xyz.de | User16@xyz.de | User16@xyz.de | User16@xyz.de | User16@xyz.de | User16@xyz.de | User16@xyz.de |
---|
7 | User18@xyz.de | User18@xyz.de | User18@xyz.de | User18@xyz.de | User18@xyz.de | User18@xyz.de | User18@xyz.de | User18@xyz.de |
---|
8 | User22@xyz.de | User22@xyz.de | User22@xyz.de | User22@xyz.de | User22@xyz.de | User22@xyz.de | User22@xyz.de | User22@xyz.de |
---|
9 | User23@xyz.de | User23@xyz.de | User23@xyz.de | User23@xyz.de | User23@xyz.de | User23@xyz.de | User23@xyz.de | User23@xyz.de |
---|
10 | User24@xyz.de | User24@xyz.de | User24@xyz.de | User24@xyz.de | User24@xyz.de | User24@xyz.de | User24@xyz.de | User24@xyz.de |
---|
11 | User26@xyz.de | User26@xyz.de | User26@xyz.de | User26@xyz.de | User26@xyz.de | User26@xyz.de | User26@xyz.de | User26@xyz.de |
---|
12 | xxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxxxx | xxxxxxxxxxxxxxxxxx |
---|
13 | I2[:I11]:=WENNFEHLER(INDEX(Tab!G:G;AGGREGAT(15;6;ZEILE(A$1:A$99)/(INDEX(Tab!$B$1:$F$99;;VERGLEICH("x";Tab!$B$1:$F$1;))="ja"); ZEILE(A1))); "") |
---|
14 | J2[:J11]: {=WENNFEHLER(INDEX(Tab!G:G;KKLEINSTE(WENNFEHLER(ZEILE(A$1:A$99)/(INDEX(Tab!$B$1:$F$99;;VERGLEICH("x";Tab!$B$1:$F$1;))="ja");999);ZEILE(A1))); "")} |
---|
15 | K2[:K11]: {=WENNFEHLER(INDEX(Tab!G:G;AggregateXk(15;6;ZEILE(A$1:A$99)/(INDEX(Tab!$B$1:$F$99;;VERGLEICH("x";Tab!$B$1:$F$1;))="ja");ZEILE(A1)));"")} |
---|
16 | L2:L11: {=WENNFEHLER(INDEX(Tab!G:G;AggregateXk(15;6;ZEILE(A$1:A$99)/(INDEX(Tab!$B$1:$F$99;;VERGLEICH("x";Tab!$B$1:$F$1;))="ja");ZEILE(A1:A10)));"")} |
---|
17 | M2:M11: {=WENNFEHLER(INDEX(Tab!G:G;AGGREGAT(15;6;ZEILE(A$1:A$99)/(INDEX(Tab!$B$1:$F$99;;VERGLEICH("x";Tab!$B$1:$F$1;))="ja"); ZEILE(A1:A10))); "")} |
---|
18 | N2[:N11]:=WENNFEHLER(INDEX(Tab!G:G;KKLEINSTE(DFeldoF;ZEILE(A1))); "") | DFeldoF | ⇒WENNFEHLER(DFeld;999) |
---|
19 | O2[:O11]:=WENNFEHLER(INDEX(Tab!G:G;AggregateXk(15;6;DFeld;ZEILE(A1)));"") | DFeld | ⇒ZEILE($A$1:$A$99)/(INDEX($B$1:$F$99;;VERGLEICH("x";$B$1:$F$1;))="ja") |
---|
20 | P2[:P11]:=WENNFEHLER(INDEX(Tab!G:G;AggregateXk(-15;6;ZEILE(A$1:A$99)/(INDEX(Tab!$B$1:$F$99;;VERGLEICH("x";Tab!$B$1:$F$1;))="ja");ZEILE(A1)));"") | * intern evaluiert |
---|
Die neue Version der UDF sieht dann so aus:
Rem Aggregat f.1 Datenfeld aus Ausdruck (eXpression)
' als Arg3 u.ggf eines als Arg4 f.Arg1>13; Umwandl
' 1es Bereichs in DatFeld; nur Input-FWert-Ignorg!
' Arg1: 0|"" bzw -Ersatz durch ""/1 (8/9);
' Arg3: ZBer/Ausdr->DFeld; Arg4: ohne/Kl13.
' Achtung! Neg Arg1 vhindrt ext FmlText-Evaluierg!
' Vs1.2 -LSr -cd:20160120 -1pub:20160120herber -lupd:20160122n
Function AggregateXk(ByVal Funktion As Integer, ByVal Optionen As Integer, _
ByVal DatenfeldAusdruck, Optional ByVal k)
Const myName$ = "aggregatexk("
Dim p As Long, sx As Long, sz As Long, zx As Long, zz As Long, _
q(1) As Long, FTxt As String, erg, kw, xw, zwErg As Variant
On Error GoTo fx
If Funktion Len(FTxt)))
AggregateXk = ActiveSheet.Evaluate(FTxt): Exit Function
End If
With WorksheetFunction
If TypeName(DatenfeldAusdruck) = "Range" Then
If DatenfeldAusdruck.Cells.Count > 1 Then
DatenfeldAusdruck = .Transpose(.Transpose(DatenfeldAusdruck))
Else: DatenfeldAusdruck = Array(DatenfeldAusdruck)
End If
End If
If Not IsMissing(k) Then
If TypeName(k) = "Range" Then
If k.Cells.Count > 1 Then k = .Transpose(.Transpose(k))
End If
End If
End With
On Error Resume Next
sz = UBound(DatenfeldAusdruck, 2) + 1 - LBound(DatenfeldAusdruck, 2)
zz = UBound(DatenfeldAusdruck, 1) + 1 - LBound(DatenfeldAusdruck, 1)
On Error GoTo fx: ReDim zwErg(zz - 1, sz - 1)
Select Case Optionen
Case 0, 1, 4, 5 'entfällt hier f.DFeld!
zwErg = DatenfeldAusdruck
Case 2, 3, 6, 7, 8, 9 'dito, nur FWertErsatz!
For Each xw In DatenfeldAusdruck
If Not IsError(xw) Then
zwErg(zx, sx) = xw
ElseIf Optionen > 7 Then
zwErg(zx, sx) = Array("", 1)(Optionen - 8)
End If
zx = (zx + 1) Mod zz: sx = sx - CInt(zx = 0)
Next xw
Case Else: Err.Raise xlErrNA
End Select
With Application
If Funktion > 13 And IsArray(k) Then
On Error Resume Next
sz = UBound(k, 2) + 1 - LBound(k, 2)
zz = UBound(k, 1) + 1 - LBound(k, 1)
On Error GoTo fx
kw = k: zx = 0: sx = 0: ReDim erg(zz - 1, sz - 1)
For Each k In kw
On Error Resume Next: GoSub kf: On Error GoTo fx
erg(zx, sx) = AggregateXk: AggregateXk = Empty
zx = (zx + 1) Mod zz: sx = sx - CInt(zx = 0)
Next k
AggregateXk = erg: Exit Function
End If
kf: Select Case Funktion
Case 0: AggregateXk = zwErg
Case 1: AggregateXk = .Average(zwErg)
Case 2: AggregateXk = .Count(zwErg)
Case 3: AggregateXk = .CountA(zwErg)
Case 4: AggregateXk = .Max(zwErg)
Case 5: AggregateXk = .Min(zwErg)
Case 6: AggregateXk = .Product(zwErg)
Case 7: AggregateXk = .StDev_S(zwErg)
Case 8: AggregateXk = .StDev_P(zwErg)
Case 9: AggregateXk = .Sum(zwErg)
Case 10: AggregateXk = .Var_S(zwErg)
Case 11: AggregateXk = .Var_P(zwErg)
Case 12: AggregateXk = .Median(zwErg)
Case 13: AggregateXk = .Mode_Sngl(zwErg)
Case 14: AggregateXk = .Large(zwErg, k)
Case 15: AggregateXk = .Small(zwErg, k)
Case 16: AggregateXk = .Percentile_Inc(zwErg, k)
Case 17: AggregateXk = .Quartile_Inc(zwErg, k)
Case 18: AggregateXk = .Percentile_Exc(zwErg, k)
Case 19: AggregateXk = .Quartile_Exc(zwErg, k)
Case Else: Err.Raise xlErrNA
End Select
If Not IsEmpty(kw) Then Return
End With
fx: If CBool(Err.Number) Then AggregateXk = CVErr(Err.Number)
End Function
Um das zu erreichen, muss Arg1 negativ angegeben wdn, denn ich wollte das aus dem o.g. Grund nicht generell vornehmen, sondern zur Wahl stellen ohne dabei wesentlich von der ArgumenteZahl und -Form von AGGREGAT abzuweichen. Außerdem habe ich noch die Funktion 0 spendiert, bei deren Angabe nur Arg3 in aufbereiteter Form wiedergegeben wird, und außerdem die zusätzlichen Optionen 8 und 9. 2/3/6/7 tragen nichts in das aufbereitete Datenfeld ein, wenn im Original ein F-Wert steht. Dadurch wird hier idR eine 0 erscheinen, bei reinen TextDatenfeldern ggf ein "". Mit Option 8 wird dann ein LeerText eingetragen, mit 9 eine 1.
Gruß, Luc :-?