In den ersten beiden Teilen dieses Beitrags haben Sie erfahren, wie Sie mit Hilfe der Bedingten Formatierung Projektstatusberichte visuell ansprechend durch Ampeln und Symbole aufbereiten und welche Techniken Sie dabei nutzen können, um die von Excel vorgegebenen Möglichkeiten zu erweitern und den Bedienkomfort zu erhöhen.Dieser dritte und letzte Teil stellt schließlich Lösungen vor, in denen Sie die bereits vorgestellten Techniken sinnvoll miteinander kombinieren, um z.B. einen Trend aufzuzeigen (Bild 1) oder um ein Warnsystem aufzubauen, das Ihnen auf einen Blick zeigt, bei welchen Teilprojekten bzw. Arbeitspaketen die Kosten und Termine aus dem Ruder laufen (Bild 2). Sie können dabei die zulässigen Grenzwerte für Kosten und Dauer per Drehfeld ändern, um so bequem unterschiedliche Szenarien zu simulieren.
Bild 1: Mit Pfeilen einen Abwärtstrend verdeutlichen.
Bild 2: Mit Ampeln die Kosten überwachen, mit Harvey Balls die verstrichene Projektzeit anzeigen, mit roten Fähnchen Warnsignale setzen und über die Drehfelder in den Spaltenüberschriften die Grenzwerte für die Kosten- und Terminüberwachung komfortabel ändern.
Bild vergrößern
Egal, ob Sie eingesetzte Arbeitskräfte, angefallene Projektstunden oder ‑kosten überwachen möchten – um auf einen Blick zu erkennen, wie sich der Status seit dem vorangegangenen Kontrollintervall verändert hat, sind Trendpfeile eine gute Lösung. Sie ersparen Ihnen das zeitraubende Studium der Einzeldaten. Bild 1 stellt eine mögliche Variante dar, bei der in der Spalte "Trend" automatisch rote Abwärtspfeile erscheinen, wenn der aktuelle Wert niedriger ist, als der der Vorwoche. Das Zahlenmaterial ist in dem Fall bewusst neutral gehalten, denn die vorgestellte Lösung lässt sich vielfältig verwenden. Anwender von Excel 2010 können sich überdies dank der neuen Funktion "Sparklines" die Entwicklung der Daten in einer Zeile oder einer Spalte kompakt mit einem Minidiagramm anzeigen lassen. Die Sparklines erzeugen Linien- oder Säulendiagramme in einer Zelle und ermöglichen so einen schnellen Vergleich der Entwicklung zwischen den Daten mehrerer Zeilen (Bild 1, Spalte "Entwicklung").
Sparklines gibt es in Excel 2007 noch nicht, Trendpfeile sind dagegen sowohl in Excel 2007 wie auch in 2010 verfügbar. Im vorliegenden Beispiel sollen sie erscheinen, wenn die Differenz zwischen dem aktuellen Wert und dem der Vorwoche kleiner als 0 ist. Die Differenz berechnen Sie dazu in einer eigenen Spalte. Hier die Schritte im Detail:
Bild 3: In Spalte N die Differenz zwischen dem aktuellen Wert und dem der vorangegangenen Kalenderwoche berechnen.
Bild 4: Damit Excel 2010 nur rote Pfeile für einen Abwärtstrend anzeigt, lassen sich die grünen und gelben abwählen.
Natürlich können Sie die eben gezeigte Lösung auch so anpassen, dass Sie die Differenzen hervorheben, bei denen in der aktuellen Kalenderwoche der Wert nach oben gegangen ist. Bild 5 zeigt eine Vorschau auf diese Variante sowie die dazu erforderlichen Einstellungen in Excel 2010.
Bild 5: Einstellungen in Excel 2010, um nur grüne Pfeile für einen Aufwärtstrend anzuzeigen.
Bild vergrößern
Zur Projektsteuerung ist es wichtig, Termine und Kosten im Blick zu behalten, um Verzögerungen oder Budgetüberschreitungen frühzeitig zu erkennen. Da liegt es nahe, in Excel ein Frühwarnsystem aufzubauen, das die beiden wichtigen Felder "Kosten" und "Termine" unter die Lupe nimmt.
Das nachfolgende Beispiel dient lediglich als Anregung dafür, wie sich die im zweiten Teil des Beitrags vorgestellten Techniken einsetzen lassen. Sie können aber auch Ihr ganz persönliches Kontrollcockpit aufbauen, indem Sie die Tabelle und die Formeln entsprechend Ihren individuellen Bedürfnissen anpassen.
Das folgende Beispiel für ein Frühwarnsystem verwendet drei unterschiedliche Indikatoren:
Die ersten beiden Symbole lassen also jeweils getrennte Aussagen über den Status von Projektkosten und Terminen zu. Das letzte betrachtet beide Größen im Zusammenhang.
Wechseln Sie in der Beispieldatei in das Arbeitsblatt "7 Frühwarnsystem". In Spalte G werden die tatsächlichen Kosten mit den Plan-Kosten verglichen. Dividiert man erstere durch letztere, erhält man einen Wert dafür, wie weit das Budget bereits ausgeschöpft ist. Liegt der Wert über 100%, sind die Plankosten überschritten, liegt er darunter, sind noch finanzielle Mittel vorhanden.
In Spalte H wird der Status der Kosten mit drei Ampelfarben signalisiert. Rot bedeutet, dass das Budget bereits überschritten ist, Grün signalisiert, dass die Ausschöpfung im Plan liegt. "Im Plan" heißt hier, dass kein größerer Anteil des Budgets verbraucht wurde, als dem Fertigstellungsgrad entspricht. Ist ein Arbeitspaket z.B. zu 60% fertiggestellt, sollten die dafür angefallenen Kosten nicht mehr als 60% des Gesamtbudgets betragen. Liegen sie darüber – z.B. um mehr als 10% – erscheint als Warnung ein gelbes Symbol. Die Prozentangabe lässt sich dabei frei über ein Drehfeld einstellen.
Gehen Sie zum Nachvollziehen dieser Teillösung wie folgt vor:
Bild 6: Den Typ von Prozent auf Zahl umstellen, die passenden Vorzeichen und die Grenzwerte festlegen.
Das Ergebnis sollte nun so wie in Bild 7 aussehen.
Bild 7: Blick auf die fertige Teillösung in Spalte H.
Die Zelle G3 spielt bei der Kostenbetrachtung eine besondere Rolle. In der Beispieldatei ist sie so eingestellt, dass die gelbe Ampel erscheint, wenn die Budget-Ausschöpfung um mehr als 10% von dem Fertigstellungsgrad abweicht.
Der Prozentwert kann durch Eingabe direkt in H3 geändert werden – eleganter geht das jedoch über ein Drehfeld. Wie Sie dieses erstellen und steuern, wurde bereits im zweiten Teil des Beitrags beschrieben.
Um den Text mit Vergleichsoperator "Abw. von Fertigst. > " vor der Zahl einzufügen, markieren Sie Zelle H3 und rufen Sie mit der Tastenkombination Strg+1 das Dialogfeld "Zellen formatieren" auf. Wählen Sie dort links die Kategorie "Benutzerdefiniert". Tippen Sie dann rechts unter "Typ" den in Bild 8 gezeigten Formatcode ein:
"Abw. v. Fertigst. >" 0%. Schließen Sie mit einem Klick auf "OK" ab (Bild 8).
Bild 8: Ein benutzerdefiniertes Zahlenformat, das dem Prozentwert einen Text inkl. Vergleichsoperator voranstellt.
Bild 9 zeigt den detaillierten Aufbau der Teillösung.
Bild 9: Die für die Kontrolle der Projektdauer relevanten Spalten.
Der Status der Projektdauer wird in Spalte N mit passenden Symbolen aus der Bedingten Formatierung verdeutlicht.
Es kommen also zwei unterschiedliche Regeln der Bedingten Formatierung zum Einsatz: eine mit Symbolen und eine "klassische" mit farbiger Füllung der Zellen.
Bild 10: Zwei verschiedene Regeln sorgen für eine übersichtliche Statusanzeige in Spalte N.
Legen Sie zuerst die Bedingte Formatierung mit Hilfe der Symbole fest. Die entsprechenden Einstellungen zeigt Bild 11. In Excel 2010 können Sie den schwarzen Vollkreis gegen einen roten austauschen.
Bild 11: Den Symbolsatz mit den Harvey Balls auswählen und "Nur Symbol anzeigen" aktivieren.
Schließen Sie anschließend das Dialogfeld, um das Ergebnis zu prüfen und fügen Sie dann die zweite Regel hinzu:
Das Ergebnis sollte nun so wie in Bild 9 aussehen.
Bild 12: Die Formel eingeben und die Mustereinstellungen für die Zellschraffur festlegen.
Bild vergrößern
In der Spalte P soll eine Bewertung für Projektkosten und -dauer nach folgender Logik erfolgen:
Auf Basis dieser drei Zahlen wird per Bedingter Formatierung ein rotes Fähnchen, ein grüner Haken oder ein grauer Kreis zugewiesen.
Die Formel, zur Bewertung von Projektkosten und -dauer
In der Formel, welche die oben genannten drei Vorgaben prüft, werden die Funktionen ISTLEER sowie UND in einer verschachtelten WENN-Funktion verwendet.
Hier die komplette Formel für Zelle O4, die Sie dann nach unten kopieren können:
=WENN(ISTLEER(K4);WENN(UND(G4-D4>$H$3;N4-D4>$O$3);1;-1);0)
Markieren Sie den Zellbereich P4:P14, rufen Sie auf der Registerkarte Start in der Befehlsgruppe Formatvorlagen die Befehlsfolge Bedingte Formatierung / Symbolsätze / Weitere Regeln auf und geben Sie die in Bild 13 gezeigten Einstellungen ein.
Bild 13: Die Einstellungen für die Anzeige von Symbolen per Bedingter Formatierung in Spalte P.
In diesem dreiteiligen Beitrag haben Sie unterschiedliche Techniken kennengelernt, wie Sie mit Hilfe der Bedingten Formatierung und Symbolen leicht verständliche Statusberichte aufbauen. Sie konnten sich davon überzeugen, dass sich durch den Einsatz zum Teil recht komplexer Formeln die Symbole noch flexibler nutzen lassen. Der "Blick unter die Motorhaube" der Bedingten Formatierung ist hoffentlich Inspiration zum Aufbau eigener Lösungen für Ihre Projekte.
