Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Listen/Datensätze

Beitrag: AGGREGAT - die Unvollendete

Aufgabe
Von den meisten als Superfunktion (seit xl2010) gehyped, hat sie tatsächlich Stärken – teils auch ungeahnte – allerdings auch unübersehbare Schwächen bzw. Unlogiken.
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 für die Ermittlung von Teilergebnissen nicht flach legen lässt, 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 in Teilen also äußerst begrenzt.
Erläuterung
Der (derzeitige) AGGREGAT-Anwendungsklassiker bedient sich des langjährigen Dirty Tricks (Frank-Kabel-Lösung) 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 mit Hilfe der Funktion ZEILE(X1) die in diesem Formelteil kleinste, zweitkleinste etc. Zeilenzahl ausgelesen. ZEILE(X1) wird bekanntlich beim Runterkopieren zu ZEILE(X2), ZEILE(X3) etc. und liefert damit die fortlaufenden Zahlen 1, 2, 3 etc. Weitere Bedingungen können ergänzt werden: Wenn beispielsweise Spalte C noch auf „y“ überprüft werden soll, dann wird durch ein weiteres Wahrheitsarray dividiert ZEILE($1:$100)/(B$1:B$100="x")/(C$1:C$100="y") Um den Fehlerwert #ZAHL! zu vermeiden (er kommt genau dann, wenn es nix mehr zum Auflisten gibt, also bereits alle mit x markierten Werte angezeigt wurden), kann man die Formel mit WENNFEHLER umranden:
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($1:$100)/(B$1:B$100="x");ZEILE(X1))); "")
Seit der Excelversion 365 gibt es mit der Einführung der Funktion FILTER übrigens eine wesentlich einfachere Lösung – bezogen auf die Beispielformel:
=FILTER(A1:A100;B1:B100="x")