Office-Blog

AGGREGAT(): Neue, mächtige Funktion in Excel 2010

07.09.2010   Frank Arendt-Theilen

Kürzlich konnte ich mich vom Nutzen der neuen Funktion AGGREGAT() überzeugen. Welchen Vorteil sie im Vergleich zu KKLEINSTE(), MAX() und weiteren siebzehn Funktionen bietet, zeigt das folgende Beispiel.

In einem Tabellenblatt hatte ich KKLEINSTE() benutzt, um den zweitkleinsten Wert zu finden – leider zunächst erfolglos. Der Grund lag an einem der Werte in der Matrix, der zum Fehler #DIV/0! führte. Daraufhin hatte KKLEINSTE() nichts Eiligeres zu tun, als ebenfalls #DIV/0! auszugeben. Das kann ich nicht gebrauchen!

Eigentlich kein Problem, stehen doch zahlreiche Funktionen zur Verfügung, um Fehlerwerte abzufangen –  ISTFEHLER(). WENNFEHLER(), WENN(), usw.

Doch dadurch blähen sich die Formeln ziemlich auf. Es funktioniert zwar, aber perfekt ist es nicht.

In dieser Situation half mir nun die Funktion AGGREGAT() weiter.

Die Arbeitsweise der Funktion AGGREGAT()

Das Besondere an dieser Funktion liegt darin, dass ich wie mit der Funktion KKLEINSTE() arbeiten kann, auch wenn sich in der Matrix Fehlerwerte tummeln. Genau das, was ich in meinen Formeln brauche.

Die Syntax dieser Funktion lautet entweder

AGGREGAT(Funktion;Optionen;Array;[k])
oder
=AGGREGAT(Funktion;Optionen;Bezug1;Bezug2;[Bezug3]…)

Für den Einsatz der Funktion AGGREGAT() gehe ich wie folgt vor:

  • Nach Eintippen des Gleichheitszeichens und des Funktionsnamens lege ich im ersten Argument eine Aggregierungsfunktion fest. Für die Funktion KKLEINSTE() gebe ich die Zahl 15 ein. Excel unterstützt mich während der Formelerstellung bei der richtigen Auswahl mit seiner QuickInfo (Abbildung 1).
  • Das zweite Argument ist für mich entscheidend. Hier bestimme ich die Arbeitsweise der Funktion. Die Option Fehlerwert ignorieren trifft auf meine Situation genau zu, also trage ich die Zahl 6 als zweites Argument ein (Abbildung 2).
  • Im dritten Argument gebe ich wie in der Funktion KKLEINSTE() die Matrix an, aus dem der kleinste Wert ermittelt werden soll.
  • Zuletzt nimmt das vierte Argument den Rang des Wertes auf nach dem gesucht wird. Bei mir setze ich die Zahl 2 für den zweiten Rang ein.

Die komplette Formel lautet also =AGGREGAT(15;6;Matrix;2).

Excel 2010: Die AGGREGAT()-Funktion sowie die Optionen

Fazit

Diese Funktion hat einiges Potential, das es zu entdecken gilt. Daher werde ich mich dieser Funktion noch weiter annehmen. Mein Problem hat sich auf einfachste Weise mit AGGREGAT() aufgelöst.

Über den Autor

Frank Arendt-Theilen

- Spezialist für dynamische Excel-Diagramme
- Langjähriger Microsoft-MVP für Excel
- Experte für Matrixformeln
- Zauberer beim Thema »Bedingte Formatierung«
- Autor bei Microsoft Press und beim dpunkt.verlag
- Bekannt durch seine Excel-Videos bei video2brain

5 Comments so far

Claude PelletierPosted on  9:34 am - Mrz 27, 2014

Aggregat() ist ein sehr hilfreich Funktion. Somit könnte ich Boxplot-Diagramme basiert auf dynamische Datenquelle erstellen.

Es funktioniert eiwandfrei für alle Max, Min, Median und Quartile funktionen.

Nur mit Standardabweichung Funktionen bekomme ich Unterschied in die Ergebnisse,

hat jemand einer Idee wie ich Standardabweichung mit Aggregat() nutzen kann?

Danke

    Claude PelletierPosted on  9:40 am - Mrz 27, 2014

    Dazu wollte ich noch erklären; in mein dynamische Datenquelle befinden sich „ausgeblandete Zeilen“ UND „Leerewerte“.

    Bei der Optíonen der Aggregat Funktion kann ich entwieder eine oder die andere auswählen.

    Hat jemand ein Idee ob es gibt es ein Weg herum?

    Danke

RaketenschneckePosted on  4:18 pm - Nov 12, 2013

Wow, coole Seite!
hab die heute das erste Mal besucht und mir schon einige Beträge durchgelesen. Sehr hilfreich, sehr gut erklärt!

Ich würde mich schon als Excel-Nerd bezeichnen, aber in den letzten Jahren ist mir immer mehr die Neugier auf neue Funktionen, Muße, danach zu suchen und Zeit, diese experimentell kennen zu lernen – abhanden gekommen. Dieser Blog kompensiert das ganz gut.
Weiter so!

Mit gebücktem Gruß
Raketenschnecke

Bernhard KrugPosted on  6:52 pm - Sep 29, 2013

Starke Funktion mit viel Potential
Danke für die Hinweise