Um Laufzeiten von Projekten, Aufgaben, Verträgen oder auch Wartungsintervallen aufzuzeigen, sind Zeitleisten ein bewährtes Mittel. Sie lassen sich per Gantt-Diagramm oder über die Bedingte Formatierung aufbauen. In Excel 365 reicht jetzt eine einzige Formel, um die erforderlichen Datumsangaben für die Zeitleisten zu erzeugen. Dazu nutze ich die neue Arrayfunktion SEQUENZ.
Bild 1: Mit nur einer Formel wird in Zeile 4 die Datumsleiste dynamisch erzeugt und ab Zeile 5 sorgt eine Formel in der Bedingten Formatierung für die korrekte Position und Länge der farbigen Balken
Aus den Datumsangaben der Spalten Beginn und Ende der folgenden Tabelle entsteht die In Bild 1 gezeigte Lösung:
Die Datumsleiste in Zeile 4 muss abhängig vom kleinsten Beginn-Datum und größten Ende-Datum dynamisch von links nach rechts aufgebaut werden.
Die farbigen Balken ab Zeile 5 müssen vom jeweiligen Projektbeginn bis Projektende reichen und die Dauer des Projektes aufzeigen.
Bild 2: Die Datumsangaben aus den Spalten Beginn und Ende liefern die Angaben für die Startposition sowie die Länge der farbigen Balken
Bild 3: Diese Kombination aus SEQUENZ, MIN und MAX erzeugt die dynamische Datumsleiste oberhalb der farbigen Balken
Bild 4: Diese Formel in der Bedingten Formatierung steuert Position und Länge der farbigen Balken
Mit der Funktion SEQUENZ lässt sich eine Liste von laufenden Nummern generieren. Das kann auch eine dynamische Datumsreihe sein. Die Syntax der Funktion lautet
=SEQUENZ(Zeilen, Spalten, Anfang, Schritt).
Bild 5 und 6 zeigen zwei Beispiele für den Aufbau und die Wirkung von SEQUENZ.
Die Anweisungen der SEQUENZ-Funktion im Beispiel aus Bild 5 bedeuten:
Bild 5: Wertebereich mit 1 Zeile, 6 Spalten, beginnend mit 100 und mit einem Intervall von 50
Das Beispiel aus Bild 6 unterscheidet sich nur im ersten Argument der SEQUENZ-Funktion, denn diesmal soll sich der Wertebereich nicht auf eine, sondern auf zwei Zeilen verteilen.
Bild 6: Wertebereich mit 2 Zeilen, 6 Spalten, beginnend mit 100 und mit einem Intervall von 50
Die in Bild 2 gezeigte Projektübersicht habe ich mit Strg + T in eine »intelligente« Tabelle umgewandelt und dieser den Namen tblProjekte gegeben.
Nach einer Leerspalte trage ich rechts neben den Spaltenüberschriften der Projekttabelle in Zelle H4 die folgende Formel ein:
=SEQUENZ(1;MAX(tblProjekte[Ende])-MIN(tblProjekte[Beginn])+1;MIN(tblProjekte[Beginn]);1)
Bild 7: So wird aus den vier Argumente der SEQUENZ-Funktion die dynamische Datumsleiste aufgebaut
Die eine Formel in H4 genügt, um in Abhängigkeit von den Datumsangaben in der Projekttabelle in Zeile 4 die Datumsleiste nach rechts aufzubauen.
Hinweis: Da die SEQUENZ-Funktion nicht automatisch auch das passende Datumsformat auf die Zellen überträgt, müssen in Zeile 4 ausreichend Zellen nach rechts mit dem benutzerdefinierten Zahlenformat TT.MM. sowie den gewünschten Farben und Schrift und Zelle vorformatiert werden.
Unterhalb der Datumsleiste lege ich über die Höhe der Projekttabelle und eine vorausschauend ausreichende Breite eine bedingte Formatierung an.
Bild 8: Diese Formel in der Bedingten Formatierung sorgt für die erforderlichen farbigen Balken
SEQUENZ ist nur eine von mehreren Funktionen mit dem SPILL-Verhalten, bei dem mit nur einer Formel für mehrere Zellen Ergebnisse erzeugt werden. Weitere Beiträge zum SPILL-Verhalten hier im Blog gibt es über folgende URL: https://www.office-kompetenz.de/?s=spill
Über den Autor