Das grosse Archiv mit 640
VBA-freien Formellösungen und Tips
 
   
 
 ÜBER UNS   NEWS   FORMELN   TIPPS   KONTAKT   FORUM   LINKS 
   
Übersicht   Zauberbuch   Rätselbuch   Sparbuch   Bücher-Downloads / Errata  
 
Sonstiges (11) ->

AGGREGAT - die Unvollendete (517)
 
 
Aufgabe
Von den meisten als Superfunktion (seit xl2010) gehyped, hat sie tatsächlich Stärken – teils auch ungeahnte – allerdings auch eine gravierende Unlogik.

Lösung
Zum eigentlichen Einsatz:
Gedacht für Bereichsberechnungen mit vielen Ausschlüssen, die man vormals nur aufwändiger ausschließen konnte, hat AGGREGAT seine volle Daseinsberechtigung.
Auch wenn sie sich nicht flach legen lässt (in Bezug auf ausgeblendete Spalten), macht sie vertikal einen guten Job, indem sie je nach Bedarf Fehlerwerte, leere Zellen, ausgeblendete oder ausgefilterte Zeilen(werte) sowie verschachtelte TEILERGEBNISse sowie weitere AGGREGATs ignoriert – und das von MIN bis MAX, KKLEINSTE bis KGRÖSSTE, SUMME, ANZAHL etc.

Nun wird sie offiziell in 2 Varianten angepriesen (analog der Funktion INDEX):

=AGGREGAT(Funktion;Optionen;Array;[k])
sowie
=AGGREGAT(Funktion;Optionen;Bezug1;…)

Also nicht nur auf Bereiche bezogen, sondern eben auch auf Arrays!

Will man zum Beispiel den KKLEINSTEn Wert aus einem Array auslesen, dann funktioniert das einwandfrei mit:
=AGGREGAT(15;6;{12.#NV.5.#WERT!.5};1) - das Ergebnis ist 5!
Und den KGRÖSSTEn Wert erhält man ebenfalls mit
=AGGREGAT(14;6;{12.#NV.5.#WERT!.5};1) - Ergebnis ist 12!

Da müsste SUMME ja einfach sein:
=AGGREGAT(9;6;{12.#NV.5.#WERT!.5})
Warum kommt da nicht 22 raus sondern ein humorloses #WERT! ?

Das selbe mit MINimum:
=AGGREGAT(5;6;{12.#NV.5.#WERT!.5}) – ergibt #WERT!
Und auch bei MAX, ANZAHL, ANZAHL2, MITTELWERT usw.: #WERT!

Jetzt wollen wir es aber genau wissen – und stellen mit Erstaunen fest:
Eine {Array}-Bearbeitung erlauben nur alle AGGREGAT-Funktionen ab 14:

14: KGRÖSSTE
15: KKLEINSTE
16: QUANTIL.INKL
17: QUARTILE.INKL
18: QUANTIL.EXKL
19: QUARTILE.EXKL

Im Gegenzug funktionieren alle Funktionen von 1 bis 13 nur mit Bereichen und nicht mit {Arrays}.

Wo ist hier bitteschön die Logik? Eine Funktionslüge sondergleichen!
Wir können uns nur vorstellen, dass der MS-Funktionsentwickler entweder vorzeitig abgehauen oder gestorben ist.

Die Funktion ist also in der Arrayvariante äußerst begrenzt.

Erläuterung
Die jetzt möglichen, praxisnahen Aggregat-Arrays sind die Funktionen 14 und 15.
16 bis 19 sind Statistikfunktionen, die man so gut wie nie sieht.

Das Prinzip ist die Frank-Kabel-Lösung, durch Division Fehlerwerte zu erzeugen, die dann nicht berücksichtigt werden.
Da wird die Funktion VERWEIS verwendet, die von Haus aus Fehlerwerte ignoriert.
Bei Aggregat ist dafür die Option 6 erforderlich.

Der (derzeitige) AGGREGAT-Anwendungsklassiker bedient sich dieser Logik.

Mit
=INDEX(A:A;AGGREGAT(15;6;ZEILE($1:$100)/(B$1:B$100="x");ZEILE(X1)))
und runterkopiert werden alle Einträge aus Spalte A (bis Zeile 100) aufgelistet, die in Spalte B mit einem "x" gekennzeichnet sind.
Ein Matrixformelabschluss mit Strg+Shift+Enter ist (wie bei Frank Kabel) nicht nötig.

...ZEILE($1:$100)/(B$1:B$100="x")
produziert überall dort Fehlerwerte (#DIV/0!), wo in Spalte B kein "x" steht. Ignoriert durch die Option 6 werden aus der Gesamtmatrix die in diesem Formelteil kleinste, zweitkleinste etc. Zeilenzahl ausgelesen.
 
 
    zurück


 
powered by telltarget