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.
Inhalt
- Wochenenden mit farbigen Indikatoren hervorheben
- Feiertage kennzeichnen mit Hilfe der Funktion VERGLEICH
- In einer Terminliste periodisch wiederkehrende Termine kennzeichnen
- Schnelle Plan-Ist-Kontrolle für Kosten mit Fahnen und Ampelsymbolen
- Differenzierte Anzeige von Terminen in einer Datumsliste mit HEUTE()
- Ausblick
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.
Inhalt
- Wochenenden mit farbigen Indikatoren hervorheben
- Feiertage kennzeichnen mit Hilfe der Funktion VERGLEICH
- In einer Terminliste periodisch wiederkehrende Termine kennzeichnen
- Schnelle Plan-Ist-Kontrolle für Kosten mit Fahnen und Ampelsymbolen
- Differenzierte Anzeige von Terminen in einer Datumsliste mit HEUTE()
- Ausblick
Wir empfehlen zum Thema Berichten
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).
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.
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.
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:
- Markieren Sie im Arbeitsblatt "1 Basis" der Beispieldatei die Zellen D3 bis D9 (Spaltenname "WE").
- 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.
- 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.
- 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.
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:
…Jetzt Feedback geben und mitdiskutieren!
Wir würden uns über Ihre Bewertung und/oder einen Kommentar freuen ‒ nur so können wir Ihnen in Zukunft noch bessere Inhalte liefern.
