Excel – AGGREGAT(15)

AGGREGAT ist ein wahrerer Alleskönner. Die Funktion steht seit Excel 2010 zur Verfügung und leistet mehr als TEILERGEBNIS und manche Matrixfunktionen – vor allem benötigt man nicht die {}-Schreibweise über STRG + SHIFT + ENTER.
In mehreren Videos wurde die Funktion bereits beschrieben. Hier nutze ich AGGREGAT(14) und AGGREGAT(15) zur Ermittlung der bedingten größten und kleinsten Werte einer Liste.

=AGGREGAT(15;6;[Zeit]/(([@Name]=[Name])*(DATUM(JAHR([Zeit]);MONAT([Zeit]);TAG([Zeit]))=DATUM(JAHR([@Zeit]);MONAT([@Zeit]);TAG([@Zeit]))));1)

Erläuterung:
AGGREGAT(15;….;1) steht für die Ermittlung des kleinsten Wertes aus einem Array. Das Array ist die Spalte [Zeit] aus einer intelligenten Tabelle. Dort hätte auch $A$1:$A$1000 stehen können.

Der Parameter 6 an zweiter Stelle ist hier sehr wichtig, da Fehlerwerte ignoriert werden sollen. Bei der Division von [Zeit]/((….)) wird häufig durch Null geteilt. Dies ist Absicht, um unpassende Werte auszugrenzen. Diese Werte sollen nicht berücksichtigt werden.

([@Name]=[Name]) prüft, ob der aktuelle Name derselben Zeile mit einem Namen aus dem Array [Name] identisch ist. Falls ja, kommt hier ein WAHR heraus. Bei der Multiplikation ergibt dies eine 1. Bei FALSCH wäre der Wert Null und es würde durch eine Null geteilt werden. Somit entsteht ein Fehler und der Wert aus [Zeit] ist nicht interessant.

Eine Prüfung wie bei [Name] wird auch bei [Zeit] durchgeführt. Hier wird geprüft, ob der Tag derselben Zeile identisch mit dem entsprechenden Wert des Arrays ist. Falls ja, kommt auch hier ein WAHR heraus. Stimmen Name und Zeit, kommt der Wert in Betracht und es wird aus diesen „Restwerten“ nach dem kleinsten Wert geschaut.

Als Ergebnis erhalten wir pro Person, pro Tag den jeweils kleinsten Wert und mit AGGREGAT(14) den entsprechend größten Wert. Aus diesen Werten kann nun die Differenz gebildet werden, womit man die tägliche Arbeitszeit erhält.