Viele Funktionen in einer: AGGREGAT 33

Artikelbild-286
Mit dem Multitalent AGGREGAT lassen sich in Excel viele andere Funktionen in einer verbesserten Variante ersetzen.
 

(überarbeitet am 05.01.2022)

Heute stelle ich eine unglaublich mächtige, aber mindestens im gleichen Maße unbekannte/ignorierte Excel-Funktion vor.

Es geht dabei um eine Funktion, die viele einzelne (genau genommen 19!) eigenständige Excel-Funktionen in sich vereinigt. Und die dazu noch besser ist, als jede dieser einzelnen Funktionen für sich. Hört sich ziemlich gut an, oder?

Die Rede ist von der Funktion AGGREGAT.

Sehen wir uns diese „Über-Funktion“ einmal etwas näher an…

Noch ein in kleiner Hinweis zu Beginn:
Die Funktion gibt es seit Excel 2010. Solltest bei dir tatsächlich noch Excel 2007 im Einsatz sein, musst du leider darauf verzichten.

Was macht diese Funktion?

Die AGGREGAT-Funktion wurde geschaffen, um bestimmte Beschränkungen zu umgehen, die z.B. bei Funktionen wie KKLEINSTE und KGRÖSSTE oder auch in TEILERGEBNIS nicht zu dem gewünschten Resultat führen.

Diese Funktionen haben nämlich einen großen Nachteil: Sie liefern keine Ergebnisse, wenn sich innerhalb der analysierten Daten ein Fehlerwert (z.B. #NV, #WERT, #DIV/0 etc.) befindet. Mit AGGREGAT kann man diesen Nachteil umgehen.

Sehen wir uns zuerst aber einmal an, wie die Syntax von AGGREGAT lautet und welche Funktionen dadurch eigentlich abgedeckt sind (die Beispieldatei kannst du hier herunterladen).

AGGREGAT

Es gibt zwei Versionen der AGGREGAT-Funktion.

Die Bezugs-Version:
=AGGREGAT(Funktion; Option; Bezug1; [Bezug2]...)

Die Matrix-Version:
=AGGREGAT(Funktion; Option; Matrix; [k])

Für den ersten Parameter „Funktion“ wird die Nummer einer der folgenden Funktionen angegeben. Dieses Multitalent kann sage und schreibe 19(!) eigenständige Funktionen ersetzen:

Funktionsnr.Funktion
1MITTELWERT
2ANZAHL
3ANZAHL2
4MAX
5MIN
6PRODUKT
7STABW.S
8STABW.N
9SUMME
10VAR.S
11VAR.P
12MEDIAN
13MODUS.EINF
14KGRÖSSTE
15KKLEINSTE
16QUANTIL.INKL
17QUARTILE.INKL
18QUANTIL.EXKL
19QUARTILE.EXKL

Für den zweiten Parameter „Option“ kann eine der folgenden Nummern verwendet werden:

OptionVerhalten
0 (Null)Geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
1Ausgeblendete Zeilen, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
2Fehlerwerte, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
3Ausgeblendete Zeilen, Fehlerwerte, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
4Nichts ignorieren
5Ausgeblendete Zeilen ignorieren
6Fehlerwerte ignorieren
7Ausgeblendete Zeilen und Fehlerwerte ignorieren

Und genau dieser zweite Parameter ist der Grund, warum AGGREGAT viel besser ist als die jeweilige eigenständige Funktion. Sehen wir uns ein Praxis-Beispiel an.

AGGREGAT vs. KKLEINSTE

Mit Hilfe der KKLEINSTE-Funktion lässt sich bekanntermaßen der k-kleinste (2.-kleinste, 3.-kleinste usw.) Wert in einer Liste finden:

Die KKLEINSTE-Funktion

Die KKLEINSTE-Funktion

Soweit so gut. Was ist aber, wenn die Liste einen Fehlerwert enthält? Zum Beispiel, weil einer der Läufer disqualifiziert wurde:

KKLEINSTE mit Fehler

KKLEINSTE mit Fehler

Ein kleiner Fehler und die ganze Berechnung ist für die Katz‘!

Mit der AGGREGAT-Funktion ist das jedoch kein Problem. Die Matrix-Variante dieser Funktion ist besser als die Original-KKLEINSTE-Funktion:

Die AGGREGAT-Funktion

Die AGGREGAT-Funktion

=AGGREGAT(15;6;C4:C16;3)
Als ersten Parameter haben wir die Funktionsnummer 15 eingegeben, was gemäß der weiter oben dargestellten Übersichtstabelle der Funktion „KKLEINSTE“ entspricht.

Der wichtigste Parameter kommt an zweiter Stelle: Der Wert 6 besagt nämlich, dass Fehlerwerte ignoriert werden sollen.

Der dritte Parameter ist der Bezug, so wie wir ihn auch in der KKLEINSTE-Funktion verwenden würden.

Und an vierter und letzter Stelle steht schließlich noch der Wert für k, den wir für KKLEINSTE auch benötigen würden. Hier im Beispiel also 3 für den drittkleinsten Wert. Und schon erhalten wir ein fehlerfreies Ergebnis!

Das klappt natürlich auch mit allen anderen Funktionen, die der erste Parameter bietet, also z.B. auch bei einer einfachen SUMME, bei MAX oder MIN. Immer dann, wenn du damit rechnen musst, dass eine Tabelle auch Fehlerwerte enthalten kann (z.B. #NV, #DIV/0), dann solltest du dich an die AGGREGAT-Funktion erinnern.

Dieser Artikel hat dir nur einen kleinen Einblick gegeben, wozu AGGREGAT in der Lage ist. Ich werde in weiteren Beiträgen noch tiefer in diese mächtige Funktion einsteigen und hoffe, du bist jetzt schon ein wenig auf den Geschmack gekommen.
 

Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

33 Gedanken zu “Viele Funktionen in einer: AGGREGAT

  • Avatar-Foto
    Anonym

    Hallo Martin,
    in der Bezugs- und Matrix-Version hat sich der Fehlerteufel eingeschlichen 😉

    =AGGREAGT(Funktion; Option; Bezug1; [Bezug2]…)

    Die Matrix-Version:
    =AGGREAGT(Funktion; Option; Matrix; [k])

  • Avatar-Foto
    Ludwig Huber

    Hallo,

    ich bin im Sportverein in der Ski-Abteilung tätig und will nun in Excel 2000 ein Langlauf Programm erstellen.

    Wie ich aus der Startzeit und der Zielzeit die Laufzeit errechne das habe ich kapiert auch wie ich die Rangfolge aus diesem Ergebnis errechne das klappt auch, nur wie ich das ganze nach den einzelnen Altersgruppen gruppiert bringe da bin ich noch nicht dahinter gekommen.

    Ich habe eine Liste mit:

    Name/ Vorname/ Verein/ Geschlecht/ Jahrgang/ Klasse/ Startnummer/ Startzeit/ Zielzeit/ Laufzeit/ Rang
    m/w Textfeld Textfesld Nummernf. Zeitf. Zeitf. Zeitf.
    Bei allen anderen Felder handelt es sich um Textfelder

    Für die Rangberechnung habe ich folgende Formel: =RANG(D3;$D$3:$D$22;1)

    Was muss ich tun damit nach der Klasse gruppiert wird und innerhalb der gruppierten Klasse die Rangvergabe immer wieder bei Rang 1 beginnt?

    Für Ihre Bemühungen bedanke ich mich im Voraus und verbleibe

    mit herzlichen Grüße

    Ludwig Huber

    • Avatar-Foto
      Martin Weiß

      Hallo Herr Huber,

      da könnte evtl. die SUMMENPRODUKT-Funktion weiterhelfen. Angenommen, die Klassen stehen in A2:A10 und die Zeiten in D2:D10, dann erhalten Sie hiermit die aufsteigende Rangfolge innerhalb der Klasse:

      =SUMMENPRODUKT(($A$2:$A$10=A2)*($D$2:$D$10

  • Avatar-Foto
    Sebastian

    Cooler Tipp!

    Kann mir das weiterhelfen?
    Basis ist eine Tabelle mit Namen in der A-Spalte und Lehrgängen in den Spalten B-D. Ich wollte über ein Pulldown alle Namen mit dem gewählten Lehrgang ausgeben lassen. Das Klappt auch mit nur einer Lehrgangsspalte oder mit Hilfsspalten zur Zwischenrechnung, aber das große Ganze mag dann plötzlich nicht.
    Die Rohtabelle wird zu Beginn nach dem gesuchten Lehrgang sortiert, sodass nicht-Treffer am Ende stehen. Anders komm ich garnicht zu Recht.

    Dabei habe ich folgendes Problem:
    =(($B2=$F$17)+($C2=$F$17)+($D2=$F$17))*(ZEILE($A2)-1) funktioniert
    =ZÄHLENWENN($B$2:$D$5;$F$17)+1-ZEILE(A1) funktioniert
    Mit 2 Hilfsspalten damit in KGRÖSSTE: =KGRÖSSTE($L$18:$L$21;N18) funktioniert

    Baue ich es aber alles in eine Formel kommt Mist raus
    =KGRÖSSTE(((($B2=$F$17)+($C2=$F$17)+($D2=$F$17))*(ZEILE($A2)-1));(ZÄHLENWENN($B$2:$D$5;$F$17)+1-ZEILE(A1)))
    Statt 1 : 2 : 3 : 0 kommt nun #zahl! : #zahl! : 3 : #zahl! raus

    =AGGREGAT(14;6;(((($B2=$F$17)+($C2=$F$17)+($D2=$F$17))*(ZEILE($A2)-1)));ZÄHLENWENN($B$2:$D$5;$F$17)+1-ZEILE(A1))
    Liefert das gleiche falsche Ergebnis.

    Einziger Hinweis, der mir aber nicht weiterhilft: mit nur einer Spalte zur Überprüfung liefern nicht-Treffer bei INDEX #WERT, mit mehreren Spalten und Hilfsrechnungen liefern diese #Zahl!
    ???

    • Avatar-Foto
      Sebastian

      Ich denke ich bin auf der Spur.
      Es funktioniert wenn ich in der all-in-one-Formel das erste Argument von KGRÖßTE mit der Hilfstabelle ersetze.
      Es funktioniert nicht wenn ich nur den Zählenwenn-Teil mit der Hilfsformel ersetze.

      Interessant bleibt, warum die Argumentformel von Kgrößte in einer Hilfsspalte funktioniert…

  • Avatar-Foto
    Thomas Föller

    Hallo Martin,

    danke für deine Ausführungen zu dieser Funktion.

    Kannst du dir (und auch mir) erklären, weshalb die Funktion =AGGREGAT(2;6;FINDEN(3;A1:A10)) nicht funktioniert?

    Ich suche in diesem exemplarischen Beispiel die Zahl 3 im Bereich A1:A10, was mittels der Funktion FINDEN entweder eine 1 oder #WERT ergibt.

    Weshalb addiert dann die Funktion AGGREGAT die Einsen nicht, wenn ich im zweiten Argument angebe, dass Fehlerwerte ignoriert werden sollen?

    Grüße

    Thomas

    • Avatar-Foto
      Martin Weiß

      Hallo Thomas,

      der Grund dafür dürfte sein, dass die AGGREGAT-Funktion als drittes Argument einen Bezug erwartet (also Zellreferenzen), die FINDEN-Funktion aber schon Werte zurückliefert.
      Wenn es in deinem Fall nur im das ignorieren von Fehlerwerten geht, kannst du auch die normale ZÄHLENWENN-Funktion nutzen. Ausgeblendete Zeilen werden damit allerdings trotzdem gezählt.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Konrad Nowak

    Hallo Martin,
    vielen Dank für die übersichtliche Erklärung, das hat mir sehr geholfen, Fehlerwerte auszublenden.
    Konrad

    • Avatar-Foto
      Martin Weiß

      Hallo Konrad,

      danke für das Feedback, es freut mich sehr, wenn dir der Artikel weitergeholfen hat.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    erichm

    Vielen Dank für die erste Info. Bin auf die weiteren gespannt, da ich die Funktion bestimmt x-mal verwenden kann!

  • Avatar-Foto
    Uwe

    Vielen Dank für das Mitteilen und Erklären dieser tollen Funktion, aber auch generell für die immer wieder interessanten Artikel. Ach ja und noch nachträglich alles Gute für 2022. ?

  • Avatar-Foto
    Lilli

    Hallo, ich versuche mich auch hin und wieder an der Aggregatfunktion, aber in der Praxis klappt klappt das nie und finde darüber auch keinen Artikel..
    Ich möchte zum Beispiel einen Bereich durchsuchen wo es Leerzeilen gibt und hier und dort mal Text, Zahlen und #NV steht. Das eigentliche Projekt ist etwas umfangreicher, ich habe es deshalb ganz vereinfacht dargestellt und mir reicht es hier deshalb, wenn es mit Leerzeilen funktionieren würde. Ich möchte eigentlich nur zählen wie oft normaler Text in der Spalte vorkommt und alles andere ignorieren. Man denkt es wäre mit der Aggregatfunktion einfach, aber ist es leider nicht.
    Excel Spalteninhalt:
    Leerzeile
    Leerzeile
    beliebiger Text
    Leerzeile
    Leerzeile

    Als Funktion verwende ich:
    =AGGREGAT(9;6;1*((C1:C5)““))

    Leider gibt Excel aus: #WERT!
    Ich verstehe es nicht. Wenn man sich die Zwischenergebnisse anschaut, sieht alles super aus. Er bräuchte nur noch {0;0;1;0;0} zusammen addieren. Falls mir jemand erklären kann woran die Aggregatfunktion bei bereits diesem simplen Beispiel scheitert, würde ich mich freuen. Wie bereits geschrieben habe ich das Problem hier sehr vereinfacht dargestellt, weshalb einem bei dieser Vereinfachung sicherlich schnell Alternativen einfallen. Wenn die Aggregatfunktion die Summe von meinem Beispiel ausrechnen würde, wäre mein eigentliches Problem schon fast gelöst.
    Viele Grüße

    • Avatar-Foto
      Martin Weiß

      Hallo Lilli,

      die Aggregat-Funktion 9 ist die Summe. Das heißt, du versuchst damit die Summe über Texte zu bilden, was zwangsläufig zu einem Fehler führen muss. Versuch stattdessen mal die Funktion 3 (= ANZAHL2) zu verwenden:
      =AGGREGAT(3;6;C1:C5)
      Damit würde aber auch Zellen mitgezählt werden, die eine Zahl enthalten. Wenn das nicht gewünscht ist, würde ich folgende Alternative vorschlagen:
      =SUMMENPRODUKT(–(ISTTEXT(C1:C5)))

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Lilli

        Danke, ich werde es morgen mal ausprobieren. Aber mit der Summe über einen Text stimmt nicht ganz. Wie oben gezeigt steht in dem Array dann ja drin {0;0;1;0;0}, also er hat den Text korrekt in Zahlen umgewandelt und soll diese Zahlen nun addieren.
        Ich hatte bei der oben geschriebenen Formel jedoch einen kleinen Fehler drin, sie muss heißen: =AGGREGAT(9;6;1*((C1:C5)““)).

        • Avatar-Foto
          Lilli

          Ah jetzt weiß ich weshal sie falsch ist. Haha, die spitzen Klammen werden automatisch beim Absenden entfernt. Also vor den „“ steht noch ein Kleiner und Größer Zeichen.

      • Avatar-Foto
        Lilli

        Hab es mal probiert. Die beiden Alternativen funktionieren leider nicht wie ich es gern hätte.
        Es liegt daran, weil in den Zellen Formeln stehen mit SVERWEIS. Die suchen nach einem Schlüsselwort in Zelle $B$3, was ich in einer Zelle eingebe. Entferne ich dieses Schlüsselwort, soll nicht gesucht werden sondern „“ eingetragen werden. Würde ich auf die „“ verzichten, würde ansonsten 0 in der Zelle stehen.
        Also quasi =WENN($B$3=““;““;SVERWEIS…)
        Man kann es auch einfach simulieren, in dem man in einer Zelle die Formel eingibt =““. Sowohl die Alternative mit Aggregat(3;6…) als auch SUMMENPRODUKT mit ISTTEXT zählen dann diese Leerzeile mit.
        Ändere ich die Aggregat(3;6…) dann ab wie meine Formel mit Aggregat(9;6…) mit den spitzen Klammern, erhalte ich das gleiche Lösungsarray mit dem gleichen Fehler.
        Schade. 🙁

      • Avatar-Foto
        Lilli

        Notfalls können Zahlen mitgezählt werden. Aber dann käme man natürlich auf die Idee in den Leerzeilen (wo die SVERWEIS Funktion) nichts gefunden hat, eine 0 zurückzugeben bzw gar nichts. Das sieht dann aber recht unordentlich aus, deshalb wäre eine leere Zelle schon besser. Ich versuche es auf jeden Fall weiter. Wird vermutlich eine riesige Formel werden oder ich muss Abstriche in Kauf nehmen.

        • Avatar-Foto
          Martin Weiß

          Hallo Lilli,

          du könntest mit dem SVERWEIS eine 0 zurückgeben und einfach die Anzeige von Nullwerten unterdrücken. Entweder über eine bedingte Formatierungsregel (wenn es nur für einen bestimmten Bereich unterdrückt werden soll) oder über die Excel-Optionen: Unter der Kategorie „Erweitert“ findest du ziemlich weit unten die Option „In Zellen mit Nullwert eine Null anzeigen“. Hier einfach das Häkchen rausnehmen und alle Nullen im betreffenden Arbeitsblatt verschwinden aus der Ansicht.

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Matt

    Hallo Martin,
    kann ich mit AGGREGAT nicht subtrahieren?
    Die Berechnung =AGGREGAT(9;3;H125;F125*-1) oder =AGGREGAT(9;3;H125;(F125*-1)), etc. führt zu einem #WERT!-Fehler…
    Wenn ich das gleiche in der SUMME-Funktion eingebe, klappt es aber?

    • Avatar-Foto
      Martin Weiß

      Hallo Matt,

      das Problem ist, dass AGGREGAT nur mit reinen Bezügen arbeitet. Das heißt, Bezug + gleichzeitige Berechnung wird offensichtlich nicht akzeptiert. SUMME hingegen akzeptiert sowohl Bezüge, statische Zahlen und Formeln.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Werner

    Hallo Martin,
    danke für diese hilfreiche webseite , ich bin wirklich begeistert

    ich sag mir immer „Fischen gehen“ mit Summenprodukt und Aggregat Formeln

    Viele Grüße aus Kiel
    Werner

    • Avatar-Foto
      Martin Weiß

      Hallo Werner,

      vielen Dank für dein tolles Feedback. Das Fischen habe ich mit Summenprodukt und Aggregat bisher noch nicht probiert, aber alles andere geht damit auf jeden Fall 🙂

      Schöne Grüße,
      Martin