Office-Blog

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

Die Aufgabe

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

Der Bauplan und eine Vorschau auf seine zwei wichtigsten Elemente

• Die dynamische Datumsleiste in Zeile 4 erzeuge ich mit Hilfe der Funktion SEQUENZ (Bild 3).
• Das Einfärben der Zellen, die ab Zeile 5 Auskunft über Start und Dauer der einzelnen Projekte geben, steuere ich mit einer UND-Formel in der Bedingten Formatierung (Bild 4).

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

Kurz erklärt: Aufbau und Wirkung von SEQUENZ an zwei einfachen Beispielen

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).

• die Argumente Zeilen und Spalten geben vor, über welchen Bereich sich die Funktion ausdehnen soll,
• Anfang ist der Startwert,
• Schritt ist die Angabe, wieviel von Zelle zu Zelle dazu addiert wird.

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:

• 1 = 1 Zeile,
• 6 = 6 Spalten,
• 100 = bei 100 beginnen,
• 50 = jeweils 50 hinzuaddieren.

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 dynamische Datumsleiste mit der Funktion SEQUENZ aufbauen

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)

• Der dritte Parameter MIN(tblProjekte[Beginn]) besagt, dass die Liste mit dem kleinsten Beginn-Datum beginnen soll.
• Der vierte Parameter zählt von diesem Datum aus immer 1 weiter hoch.
• Wie viele Spalten durch die Funktion belegt werden sollen, beschreibt der zweite Parameter, nämlich die Differenz des größten Ende-Datums und des kleinsten Beginn-Datums plus 1.
• Der erste Parameter schließlich gibt vor, dass durch die Formel nur eine Zeile belegt wird.

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.

Balken für die Laufzeiten der Projekte mittels Bedingter Formatierung generieren

Unterhalb der Datumsleiste lege ich über die Höhe der Projekttabelle und eine vorausschauend ausreichende Breite eine bedingte Formatierung an.

• Dazu markiere ich beispielsweise den Bereich von H5 bis AK13.
• Über Start > Bedingte Formatierung > Neue Regel > Formel zur Ermittlung der zu formatierenden Zellen verwenden trage ich folgende Formel ein: =UND(H$4>=$E5;H$4<=$F5).
  Diese Formel prüft, ob das jeweilige Datum oben in der Datumsleiste in Zeile 4 im Zeitraum des Projektes liegt.
• Als Formatierung wähle ich eine helle Füllfarbe.

Bild 8: Diese Formel in der Bedingten Formatierung sorgt für die erforderlichen farbigen Balken

Neugierig auf weitere Beispiele für dynamische Funktionen?

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