Microsoft Excel – Kosten und Termine im Blick mit Ampeln & Co.

Teil 2:
Formeln zur flexiblen Anzeige von Symbolen einsetzen

Im ersten Teil haben Sie erfahren, wie Sie Ihre Statusberichte mit Hilfe von Ampeln oder Trendpfeilen visuell ansprechend aufbereiten. Die Bedingte Formatierung kann allerdings noch mehr, wenn Sie deren vorgefertigte Regeln mit Formeln und Funktionen kombinieren. In diesem zweiten Teil stellen Hildegard Hügemann und Dieter Schiecke anhand von Beispielen entsprechende Techniken vor. Sie zeigen z.B., wie Sie die Budgetauslastung durch Ampelsymbole darstellen und dabei per Drehfeld eigene Grenzwerte setzen können. Außerdem erfahren Sie, wie Sie Datumsangaben kennzeichnen, die auf Wochenenden oder Feiertage fallen, oder wie Sie Termine markieren, die in regelmäßiger Abfolge stattfinden.

Materialien
templates

Microsoft Excel – Kosten und Termine im Blick mit Ampeln & Co.

Teil 2:
Formeln zur flexiblen Anzeige von Symbolen einsetzen

Im ersten Teil haben Sie erfahren, wie Sie Ihre Statusberichte mit Hilfe von Ampeln oder Trendpfeilen visuell ansprechend aufbereiten. Die Bedingte Formatierung kann allerdings noch mehr, wenn Sie deren vorgefertigte Regeln mit Formeln und Funktionen kombinieren. In diesem zweiten Teil stellen Hildegard Hügemann und Dieter Schiecke anhand von Beispielen entsprechende Techniken vor. Sie zeigen z.B., wie Sie die Budgetauslastung durch Ampelsymbole darstellen und dabei per Drehfeld eigene Grenzwerte setzen können. Außerdem erfahren Sie, wie Sie Datumsangaben kennzeichnen, die auf Wochenenden oder Feiertage fallen, oder wie Sie Termine markieren, die in regelmäßiger Abfolge stattfinden.

Materialien
templates

Im ersten Teil des Beitrags haben Sie erfahren, wie Sie Ihre Statusberichte zu Kosten und Terminen in Excel mit Ampeln, farbigen Symbolen oder Trendpfeilen visuell ansprechend aufbereiten. Dieser zweite Teil zeigt anhand von Beispielen, mit welchen Techniken Sie die Einsatzmöglichkeiten der Bedingten Formatierung erweitern und die vorgefertigten Regeln mit Formeln und Funktionen kombinieren. Sie können so beispielsweise die Budgetauslastung durch Ampelsymbole darstellen und dabei per Drehfeld eigene Grenzwerte setzen, Datumsangaben kennzeichnen, die auf Wochenenden oder Feiertage fallen oder Termine markieren, die in regelmäßiger Abfolge stattfinden (Bild 1).

Drei Beispiele für den Einsatz von Formeln bei der Bedingten Formatierung. V.l.n.r.: Budget überwachen und per Drehfeld eigene Grenzwerte setzen; Wochenenden anzeigen; periodisch wiederkehrende Termine in einer Datumsliste hervorheben.
Bild 1: Drei Beispiele für den Einsatz von Formeln bei der Bedingten Formatierung. V.l.n.r.: Budget überwachen und per Drehfeld eigene Grenzwerte setzen; Wochenenden anzeigen; periodisch wiederkehrende Termine in einer Datumsliste hervorheben.

Wochenenden mit farbigen Indikatoren hervorheben

Das farbige Kennzeichnen von Wochenenden in Datumslisten gehört zu den Aufgaben, für die die Bedingte Formatierung schon in früheren Excel-Versionen oft verwendet wurde. Bis Excel 2003 lässt sich allerdings nur die Zellfarbe ändern, um Datumswerte zu markieren, die auf ein Wochenende fallen (Bild 2). Den entsprechenden Datumswerten ein Symbol zuzuweisen, ist erst ab Excel 2007 möglich.

Bis Excel 2003 lässt sich per Formel nur die Farbe einer Zelle ändern, wenn deren Datum auf ein Wochenende fällt.
Bild 2: Bis Excel 2003 lässt sich per Formel nur die Farbe einer Zelle ändern, wenn deren Datum auf ein Wochenende fällt.

Allerdings setzt Excel Grenzen, wenn bei der Bedingten Formatierung Formeln eingesetzt werden. Öffnet man z.B. mit der Befehlsfolge Bedingte Formatierung / Symbolsätze / Weitere Regeln das Dialogfeld "Neue Formatierungsregel" und versucht dort als Regel zur Kennzeichnung der Wochenenden die Formel

=WOCHENTAG(Zelle;2)>5

einzugeben, quittiert Excel das mit einer Fehlermeldung (Bild 3).

Das Verwenden von Symbolsätzen lässt sich in Excel 2007 und 2010 zwar mit Formeln kombinieren, aber es sind nur absolute Bezüge möglich. Relative Bezüge akzeptiert das Programm nicht.

Zwar lassen sich Formeln für das Definieren einer Symbolsatz-Regel verwenden, relative Bezüge werden dabei aber nicht akzeptiert.
Bild 3: Zwar lassen sich Formeln für das Definieren einer Symbolsatz-Regel verwenden, relative Bezüge werden dabei aber nicht akzeptiert.

Mit dieser Technik umgehen Sie die Einschränkung für Symbolsätze

Wenn die direkte Eingabe einer Formel im Dialogfeld zum Definieren einer neuen Formatierungsregel nicht möglich ist, dann hilft ein Umweg über eine zusätzlich eingefügte Spalte, in die Sie die entsprechende Formel eintragen. In unserem Beispiel würden Sie die WOCHENTAG-Formel z.B. in einer neuen Spalte rechts neben jedem Datumswert einfügen. Die Formel liefert dann, je nach Wochentag, einen Wert zwischen 1 und 7 (Montag bis Sonntag). Mit diesen Werten lässt sich eine entsprechende Formatierungsregel definieren, um die Wochenenden mit einem Symbol zu kennzeichnen. Die eingangs gezeigte Einschränkung haben Sie so umgangen.

Sie können dies anhand der mitgelieferten Beispieldatei ausprobieren. Gehen Sie dazu wie folgt vor:

  1. Markieren Sie im Arbeitsblatt "1 Basis" der Beispieldatei die Zellen D3 bis D9 (Spaltenname "WE").
  2. Geben Sie dann die Formel =WOCHENTAG(B3;2) ein. Schließen Sie die Aktion jedoch nicht wie üblich mit "Enter" ab, sondern mit "Strg+Enter", damit Excel die Formel in alle markierten Zellen einfügt und nicht nur in die oberste.
  3. Sie sehen in Spalte D jetzt untereinander die Zahlen 1 bis 7, da der 26.03.2012 ein Montag, der 01.04.2012 ein Sonntag ist. Das Argument "2" in der WOCHENTAG-Funktion sorgt dafür, dass die Woche am Montag beginnt. Ohne dieses zweite Argument "Typ" nimmt Excel den Sonntag als ersten Tag der Woche an.
  4. Lassen Sie den Zellbereich D3:D9 markiert und weisen Sie ein Bedingtes Format zu, das in den Zellen mit den Werten 6 und 7 ein Symbol anzeigt. Öffnen Sie dazu das Dialogfeld "Neue Formatierungsregel" mit der Befehlsfolge Formatvorlagen / Bedingte Formatierung / Symbolsätze / Weitere Regeln. Definieren Sie jetzt, bei welchem Wert Symbole erscheinen sollen und welches Symbol die Wochenendtage kennzeichnen soll. Dazu können Sie die in Bild 4 gezeigten Einstellungen übernehmen. Ihre Tabelle sollte jetzt so aussehen, wie in Bild 1 in der Mitte gezeigt.
Ein Symbol für die Tage, die auf ein Wochenende fallen – statt eines roten Kreises ist auch ein gelbes Dreieck möglich.
Bild 4: Ein Symbol für die Tage, die auf ein Wochenende fallen – statt eines roten Kreises ist auch ein gelbes Dreieck möglich.

Im Gegensatz zu Excel 2010 bietet Excel 2007 keine Möglichkeit, Zellsymbole ganz auszublenden, wie in diesem Beispiel. Sie können sich jedoch behelfen, indem Sie auf den Symbolsatz "Rot/Schwarz" mit den Farben Rot, Hellrot, Grau und Schwarz ausweichen. Geben Sie beim roten und beim hellroten Symbol jeweils ">=6" ein, beim grauen ">=0". Excel markiert daraufhin alle Wochenenden mit einem roten und alle anderen Tage mit einem unauffälligen grauen Symbol. Diese Lösung ist zwar nicht so elegant, wie die oben gezeigte, erfüllt aber oft ebenfalls den gewünschten Zweck.

Feiertage kennzeichnen mit Hilfe der Funktion VERGLEICH

Nicht nur Wochenenden, auch Feiertage spielen bei der Terminplanung eine Rolle. Die eben vorgestellte Technik mit der WOCHENTAG-Funktion lässt sich hier nicht verwenden, da Feiertage nicht nur auf Wochenenden fallen. Folgende Elemente sollten Sie beachten, um eine passende Lösung aufzubauen:

  • Da je nach Bundesland die Anzahl der Feiertage pro Jahr recht deutlich variiert, ist zunächst das Anlegen einer Liste mit den Feiertagen erforderlich. Damit der Verweis auf diese Liste in der Formel leichter ist, erhält sie einen Bereichsnamen.
  • Die Datumsspalte wird dann mit der Feiertagsliste abgeglichen. Dazu eignet sich die Funktion VERGLEICH.
  • Diese Funktion liefert Zahlen als Ergebnis, wenn ein Datum auf einen Feiertag fällt. Dieser Umstand lässt sich beim Definieren einer Symbolsatz-Regel gut verwenden.
  • Fällt ein Datum nicht auf einen Feiertag, wird #NV…

Bewertungen und Kommentare

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung. Jetzt einloggen
0 Kommentare anzeigen & selbst mitreden!
Gesamt
Bewertungen 0
Kommentare 0

Fortsetzungen des Fachartikels

Teil 1:
Voreingestellte Regeln individuell anpassen
Damit die Aussage eines Statusberichts leicht erfassbar ist, sollte dieser nicht nur aus trockenen Zahlen bestehen, sondern visuell ansprechend aufbereitet sein. Dafür eignen sich z.B.
Teil 3:
Trends deutlich machen und ein Warnsystem aufbauen
In den ersten beiden Teilen haben Sie erfahren, wie Sie Projektstatusberichte in Excel ab Version 2007 mit Hilfe von Ampeln und Symbolen aufbereiten und wie Sie dabei die vorgefertigten Regeln mit Formeln und Funktionen kombinieren.