Überblick über Projektlaufzeiten mit Ampelsymbolen

Statusanzeigen mit Ampeln gehören definitiv zu den Lieblingsthemen meiner Kursteilnehmer. So auch kürzlich wieder bei einem Excel-Workshop in Berlin.
Die Aufgabe diesmal:

• die Laufzeiten von Projekten ermitteln,
• anhand der berechneten Dauer den Projekten Ampelsymbole zuordnen,
• die Laufzeiten, nach denen gruppiert wird, sollen flexibel auswählbar sein.

Hier die Vorschau auf die fertige Lösung mit der Statusanzeige in Spalte B.

Bild 1: Die Projekte werden mit Hilfe von Ampelsymbolen nach Laufzeiten gruppiert

Kurz gefasst: Mit 3 Schritten die Lösung aufbauen

1. Zuerst werden – wie in Bild 1 zu sehen – im Bereich E2 bis E4 die zu betrachtenden Zeiträume per Dropdown definiert (1).
2. Mit dem in Spalte D stehenden Startdatum für jedes Projekt (2) und dem aktuellen Datum – berechnet mit HEUTE() – wird in Spalte B die Laufzeit  ermittelt.
3. Das so gewonnene Ergebnis wird dann mittels bedingter Formatierung in der gleichen Spalte in ein Ampelsymbol umgewandelt (3).

1) Flexible Eingabe der Laufzeiten, nach denen gruppiert werden soll

Im Bereich E2:E4 soll es möglich sein, drei Grenzen für die Laufzeiten einzustellen. Am komfortabelsten geht das mit den in Bild 2 gezeigten Dropdownfeldern. In jeder der drei Zellen können Werte zwischen 0 und 5 ohne Tipparbeit einfach per Mausklick ausgewählt werden.

Bild 2: Die Laufzeiten bequem in E2, E3 und E4 per Dropdown auswählen

Das Einrichten der Dropdowns, in denen die Zahlen 0 bis 5 ausgewählt werden, geht so:

1. Zelle E2 bis E4 markieren und auf der Registerkarte Daten den Befehl Datenüberprüfung anklicken.
2. Im nun folgenden Dialogfeld (siehe Bild 3) den Eintrag Liste (1) aktivieren und unten in das Eingabefeld die gewünschten Werte – durch Semikolon getrennt – eintippen (2).

Bild 3: Die Einstellungen, die für die drei Zellen E2:E4 die Dropdowns einrichten

Der besseren Lesbarkeit wegen wird nach der Wahl einer Zahl per Dropdown in den Zellen selbst “mehr als 3 Jahre”, “mehr als 2 Jahre”, “mehr als 1 Jahr” oder “weniger als 1 Jahr” angezeigt wird.
Mit welchem Trick dies gelingt, beschreibe ich im nächsten Blogbeitrag
“Zahlenformate in Excel, die mitdenken”.

2) Die Laufzeiten für jedes der Projekte ermitteln

Nachdem die zu beachtenden Laufzeitgrenzen festgelegt wurden, sind nun die Laufzeiten der Projekte zu berechnen. Das erledigt in Zelle B7 die folgende Formel:
=BRTEILJAHRE(D7;HEUTE()).

Bild 4: Die Laufzeit mit den Funktionen BRTEILJAHRE und HEUTE ermitteln

Die Formel kann dann durch Doppelklick auf das Ausfüllkästchen von B7 nach unten kopiert werden.

In der Excel-Hilfe ist zur Funktion BRTEILJAHRE zu lesen:
”Wandelt die Anzahl der ganzen Tage zwischen Ausgangsdatum und Enddatum in Bruchteile von Jahren um. Unter Verwendung der Arbeitsblattfunktion BRTEILJAHRE können z. B. Laufzeiten von Forderungen oder Verbindlichkeiten besser miteinander verglichen werden.”

3) Ermittelte Laufzeiten in Ampelsymbole umwandeln

Die Betrachter der Projektübersicht sollen in Spalte B keine weitere Zahlenkolonne sehen. Statt der errechneten Werte für die Laufzeiten sollen dort unterschiedliche Ampelsymbole dafür sorgen, dass ein Überblick und eine Differenzierung der Projekte möglich wird.
Das erledigt die Bedingte Formatierung – und zwar eine angepasste Ampelregel. So geht’s:

1. Zuerst werden die Werte in Spalte B markiert – hier also B7 bis B14.
2. Über die Befehlsfolge Start – Bedingte Formatierung – Neue Regel wird das in Bild 5 gezeigte Dialogfeld aufgerufen.
3. Dort wird unter Formatstil der Eintrag Symbolsätze gewählt (1) und
4. bei Symbolart die Variante 3 Ampeln (mit Rand) (2).
5. Es folgt ein Klick auf die Schaltfläche Symbolreihenfolge umkehren (3).
6. Da in Spalte B nur noch die Symbole, nicht die errechneten Laufzeiten zu sehen sein sollen, wird Nur Symbole anzeigen mit einem Häkchen versehen (4).
7. Rechts unten bei Typ wird jeweils Zahl eingestellt (5).
8. Nun kommt die wichtigste Einstellung, bei der die Ampelregel an eine Formel gebunden wird. Bei Wert kann keine Zahl eingetragen werden, da die Grenzwerte für die Laufzeiten flexibel veränderbar sein sollen. Es muss also ein Bezug auf die Zellen erfolgen, in denen die Laufzeiten stehen. Das sind E2 und E3. Dazu wird der Inhalt im ersten Eingabefeld unter Wert gelöscht und per Klick auf Zelle E2 durch den Eintrag $E$2 ersetzt. Im Eingabefeld darunter wird auf die gleiche Weise $E$3 eingebaut (6).
9. Ein Klick auf OK wendet diese Einstellungen auf den markierten Zellbereich an.

Bild 5: Die Einstellungen für die angepasste Ampelregel mit Formeln

Fazit

Beim Verwenden von Symbolsätzen aus der bedingten Formatierung sind auch Formeln möglich. Auf diese Weise lassen sich berechnete Werte in besser verständliche und schneller lesbare Symbole umwandeln.

Die Möglichkeit, in der bedingten Formatierung auch Formeln zu benutzen, schafft Raum für zahlreiche kreative Lösungen, um statt der üblichen Zahlenkolonnen mit informativen Statusanzeigen oder Cockpits in der Projektarbeit zu überzeugen.

Mit Hilfe der Dropdowns lassen sich interaktiv und ganz einfach die betrachteten Zeiträume ändern und so schnell unterschiedliche Übersichten zusammenstellen.
Hier zwei Varianten dafür.

Bild 6: Bei der Wahl anderer Zeiträume ändern sich sofort die Ampelsymbole

Ausblick

Im nächsten Blogbeitrag beschreibe ich, wie mitdenkende Zahlenformate angelegt werden und wie sie funktionieren.