(ü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 |
|---|---|
| 1 | MITTELWERT |
| 2 | ANZAHL |
| 3 | ANZAHL2 |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUKT |
| 7 | STABW.S |
| 8 | STABW.N |
| 9 | SUMME |
| 10 | VAR.S |
| 11 | VAR.P |
| 12 | MEDIAN |
| 13 | MODUS.EINF |
| 14 | KGRÖSSTE |
| 15 | KKLEINSTE |
| 16 | QUANTIL.INKL |
| 17 | QUARTILE.INKL |
| 18 | QUANTIL.EXKL |
| 19 | QUARTILE.EXKL |
Für den zweiten Parameter „Option“ kann eine der folgenden Nummern verwendet werden:
| Option | Verhalten |
|---|---|
| 0 (Null) | Geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren |
| 1 | Ausgeblendete Zeilen, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren |
| 2 | Fehlerwerte, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren |
| 3 | Ausgeblendete Zeilen, Fehlerwerte, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren |
| 4 | Nichts ignorieren |
| 5 | Ausgeblendete Zeilen ignorieren |
| 6 | Fehlerwerte ignorieren |
| 7 | Ausgeblendete 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
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
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
=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.
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.
Hallo Martin,
danke für diese hilfreiche Info.
Viele Grüße aus der RuhrStadt
Andreas
Gern geschehen!
Schöne Grüße,
Martin
Danke Martin, es war mehr als hilfreich!
🙂
Pingback: AGGREGAT und TEILERGEBNIS im Zusammenspiel | Der Tabellen-Experte
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])
Hallo,
danke für den Hinweis, ist jetzt korrigiert 🙂
Schöne Grüße,
Martin
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
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
super erklärt, vielen Dank!
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!
???
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…
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
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
Hallo Martin,
vielen Dank für die übersichtliche Erklärung, das hat mir sehr geholfen, Fehlerwerte auszublenden.
Konrad
Hallo Konrad,
danke für das Feedback, es freut mich sehr, wenn dir der Artikel weitergeholfen hat.
Schöne Grüße,
Martin
Super erklärt. Bin auf weiteres gespannt. Danke
Hi Kevin,
danke, gern geschehen!
Schöne Grüße,
Martin
Vielen Dank für die erste Info. Bin auf die weiteren gespannt, da ich die Funktion bestimmt x-mal verwenden kann!
Hallo Erich,
danke für dein Feedback und Interesse!
Schöne Grüße,
Martin
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. ?
Vielen Dank, Uwe, das wünsche ich Dir auch!
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
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
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)““)).
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.
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. 🙁
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.
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
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?
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
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
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