Das Portal zum Thema Excel-Formeln

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.

Update 24.08.2022:
Im Rahmen einer Forendiskussion hat sich eine Erklärung für dieses Verhalten ergeben, die wir durchaus plausibel finden und demnach die „Funktionslüge“ als solche nicht mehr rechtfertigt. In einfachen Worten: Die Funktionen 1 bis 13 sind multibereichsfähig nach der Syntax:

=AGGREGAT(Funktion;Optionen;Bezug1;Bezug2;Bezug3;…)

Den 4. Parameter „k“ gibt es demnach (logischerweise) bei diesen Funktionen nicht. Z.B. ermittelt

=AGGREGAT(5;6;A4:A7;D5:E9;H1:H5)

das Minimum aus den 3 angegebenen (unabhängigen) Bereichen unter Ausschluss von Fehlerwerten. Die normale MIN-Funktion würde mit

=MIN(A4:A7;D5:E9;H1:H5)

einen Fehlerwert liefern, sofern sich in einem der angegebenen Bereiche ein Fehlerwert befindet. Mit solchen Multibereichen können hingegen Array-/Matrixfunktionen nicht umgehen. Bei AGGREGAT sind das die Funktionen 14 bis 19 – und zwar nach folgender Syntax:

=AGGREGAT(Funktion;Optionen;Array;k)

Der 3. Parameter „Array“ verlangt ein zusammenhängendes Array, aus dem mit dem letzten 4. Parameter „k“ das gewünschte Element ausgewählt werden kann. Die zugrunde liegenden Funktionen folgen auch von Haus aus dieser Syntax – z.B. 14 - KGRÖSSTE:

=KGRÖSSTE(Matrix;k)

Multibereiche sind dort nicht vorgesehen - es wird eine zusammenhängende Matrix (Array) verlangt. Das gilt analog für die Funktionen 15 bis 19.
Möglicherweise hat MS hier einfach nur entschieden, dass die Mehrbereichsfähigkeit für den Kunden wichtiger ist als die Matrixformeltauglichkeit und daher die Matrixformeltauglichkeit nur für die Formeln übernommen, die schon aus anderen Gründen (4. Parameter) nur einen Bereich (bzw. eine Matrix) zulassen.

=INDEX(A:A;AGGREGAT(15;6;ZEILE($1:$100)/(B$1:B$100="x");ZEILE(X1)))

=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($1:$100)/(B$1:B$100="x");ZEILE(X1))); "")

=FILTER(A1:A100;B1:B100="x")