Dynamische Liste 2 Kriterien

Chris · 2021-04-22T15:26:57+00:00

Hallo Zusammen, ich habe eine Frage zu der Datei im Anhang: https://www.herber.de/bbs/user/145726.xlsx Ziel ist es eine Liste aus einer Datenquelle zu erstellen für die beide Kriterien (Zelle G7 und G8) wahr sind, wenn diese Kriterien erfüllt sind soll jeweils der Name und der Prozentsatz angezeigt werden (in gelb makiert). Geht das mit der Aggregat funktion oder braucht man da Index? Danke für eure Hilfe und viele Grüße Chris

Mir stellt sich die Frage, warum Streit um Kaisers Bart, Werner & Walter?
Mich stört eher, dass beide Lösungen wieder mal auf einer EinzelZellenLösung verharren, wobei bei Walters hinzukommt, dass dafür auch eine singulare MatrixFml verwendet wird. Wenn schon MatrixFml, dann doch bitte für gleich alle Werte, zumal es doch schon Xl-Versionen gibt, die einfachere plurale MatrixFmln automatisch auf den erforderlichen Bereich ausdehnen. Wer solche SingulärMatrixFml-Lösungen dann für größere DatenMengen übernimmt, muss sich nicht wundern, wenn die Berechnungszeiten ggf immer länger wdn. Ansonsten schreibt man Fmln gewöhnlich für den augenblicklichen Bedarf, sofern nicht größerer Bedarf schon ersichtlich ist. Eine Anpassung an die realen und spätere Verhältnisse ist und muss Sache des Fragestellers bleiben, der deshalb die angebotene Fml auch verstehen sollte. Ihn ggf dazu zu befähigen ist wichtiger als eine Fml quasi auf Vorrat zu dimensionieren¹.
Werners Lösung hat ein Fktsaufruf-zu-Fml-Länge-Verhältnis von 5:103. Walters Fmln im Durchschnitt (da 2 unterschiedliche Fmln für 2 Zellen) eines von 4,5:76,5. Hier ist aber nur der Durchschnitt der Fktsaufrufe relevant, denn der einzig sinnvolle Grund für die Ermittlung der Fml-Länge (ZeichenAnzahl) ist der mit ihrer Notation verbundene SchreibAufwand (weshalb auch die äußeren {} nicht mitgezählt wurden, obwohl man das wohl könnte, weil dafür 2 Tasten mehr gedrückt und gehalten wdn müssen). Folglich wäre hier eher die Summe der Längen aus dem GesamtVerhältnis 9:153 anzusetzen.
Mit INDEX und AGGREGAT ist aber auch eine kürzere plurale MatrixFml möglich (mit o.g.Verhältnis von 4:81):
{=INDEX(B8:C13;AGGREGAT(14;6;ZEILE(1:6)/(A8:A13=G7)/(C8:C13>G8);ZEILE(1:6));{1.2})}
Diese entspricht quasi Werners Lösung, berechnet aber alle Werte auf 1×. Dass Xl Fmln in willkürlicher Reihenfolge² berechnet, spielt hierbei keine Rolle, denn das Ganze gilt als nur eine Fml im GgSatz zu ihrer Vereinzelung in lauter EinzellFmln. Das bemerkt man sehr schön, wenn darin zB ein AdressBezug geändert wdn muss.
Eine mögliche UDF-haltige Fml hätte ein ähnliches Verhältnis (4:86):
{=TxEval(WECHSELN(VJoin(WENN((A8:A13=G7)*(C8:C13>G8);B8:C13;"");"";2);";"""",""""";""))}
Eine andere (nur mit UDFs) wäre zwar länger, hätte aber weniger Fktsaufrufe (3:99):
{=DataSet(NoErrRange(B8:B13;;(A8:A13=G7)*(C8:C13>G8));NoErrRange(C8:C13;;(A8:A13=G7)*(C8:C13> G8));2) }
Anmerkung: Die UDF-Standorte im Archiv wurden schon so oft von mir verlinkt, dass sie b.Bed leicht zu finden sein müssten (idR neueste im Archiv vorhandene Version verwenden!).
—————————————————
¹ Anders sieht es bei UDFs aus, die gern auch so konstruiert wdn dürfen, dass sie ähnlich wie viele Xl-Fktt möglichst alle Situationen, die im Rahmen ihrer ZweckBestimmung auftreten können, meistern sollten.
² Selbst die von mir entdeckten quasi-pluralen MatrixFmln, deren angezeigte Werte tatsächlich einzeln berechnet wdn, stört die auch bei ihnen erfolgende willkürliche Berechnungsreihenfolge nicht.
Morhn, Luc :-?
Der beste Beweis für intelligentes Leben im Universum ist, dass noch niemand versucht hat, Kontakt mit uns aufzunehmen. H.Lesch, 2018, Sonneberg

…per UDF, Ralf;
eine mögliche ist oben schon erwähnt …
&bullet; NoErrRange (Vs1.3) ⇒ https://www.herber.de/forum/archiv/1508to1512/1508209_Auflistung_aus_01_Matrix.html#1508215
Die liefert ggf einen diskontinuierlichen Bereich, also ZellBezüge, weshalb ihr Ergebnis nicht direkt, ohne INDEX, abgebildet, aber durchaus weiterverwendet wdn kann, was ja sowohl INDEX als auch das obige Fml-Bsp beweist.
&bullet; Eine einfachere, die ich im Rahmen einer anderen Entwicklung testweise entwickelt habe, folgt hier. Die (plurale Matrix-)Fml würde damit so lauten:
{=VFilter(B8:C13;(A8:A13=G7)*(C8:C13>G2))}
Dazu folgendes UDF-Pgm:


Rem Bereich zeilenweise nach Kriterien filtern
'   Ergebnisrückgabe: nur Werte d.FilterQuelle
'   Vs1.1 -LSr:CyWorXxl -cd:20210423 -1pub:20210427h -lupd:20210425t
Function VFilter(ByVal Ref As Range, ByVal Crit)
Dim isArCrit As Boolean, i As LongPtr, j As LongPtr, erg, x
On Error GoTo fx
isArCrit = IsArray(Crit): ReDim erg(0)
If isArCrit Then
If TypeOf Crit Is Range Then Crit = Crit.Value
If IsError(UBound(Crit, 2)) Then
ReDim Preserve Crit(UBound(Crit) - LBound(Crit))
ElseIf UBound(Crit, 2) = 1 Then
Crit = WorksheetFunction.Transpose(Crit)
ReDim Preserve Crit(UBound(Crit) - LBound(Crit))
Else: Err.Raise xlErrRef
End If
End If
For Each x In Ref.Rows
If isArCrit Then
ReDim Preserve erg(j)
If CBool(Crit(i)) Then erg(j) = x: j = j + 1
ElseIf CBool(Crit) Then
ReDim Preserve erg(j): erg(j) = x: j = j + 1
End If
i = i + 1
Next x
VFilter = erg
fx: If CBool(Err.Number) Then VFilter = CVErr(Err.Number)
End Function

Die UDF kann man auch in VBA-Pgmm einbinden oder bei Standort in einem KlassenModul im Ausführungstext von Get- und Set-UDFs aufführen. Dazu demnächst in einem separaten Beitrag mehr.
Gruß, Luc :-?
Der beste Beweis für intelligentes Leben im Universum ist, dass noch niemand versucht hat, Kontakt mit uns aufzunehmen. H.Lesch, 2018, Sonneberg

Forumthread: Dynamische Liste 2 Kriterien

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Dynamische Liste 2 Kriterien

Forumthread: Dynamische Liste 2 Kriterien

14 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread