Termin-Überwachung visualisieren per „Bedingte Formatierung” (Maschinen-Wartung)
Gegeben ist folgende Situation: Mehrere Maschinen eines Unternehmens müssen in unterschiedlichen Intervallen (Zeitabständen) gewartet werden. In einer Excel-Tabelle soll nun berechnet und überprüft werden, wann für die jeweilige Maschine die nächste Inspektion fällig ist. Eine Woche vor dem Termin soll das entsprechende Datum gelb hervorgehoben werden, ab dem Fälligkeitstag wird das Datum rot hinterlegt und die Schrift ist dann der besseren Lesbarkeit wegen weiß.
Einleitendes
Für diesen Beitrag habe ich die Datei eines meiner Postings aus einem Forum etwas aufgearbeitet. Sie ist auf den ersten Blick schlicht und einfach gestaltet, enthält aber doch so einige kleine Tricks, die auch in anderen Situationen hilfreich sein könnten. Laden Sie die Datei hier herunter, um alles nachvollziehen zu können. Im Moment (heute, zum Zeitpunkt der Erstellung ist der 23.07.2015) stellt sich die Datei so dar:
Tabelle für die Termin-Überwachung
Sie erkennen eine „Intelligente Tabelle”, wo in Spalte D mehrere Zellen in zwei verschiedenen Farben hervorgehoben sind. Termine in den folgenden 7 Tagen (hier: D3) werden gelb markiert, fällige und überfällige Termine rot. Die Farbmarkierungen überschreiben dabei die tabellarischen Farbvorgaben.
„Intelligente Tabellen”
Das Schlüsselwort „Tabelle” hat in Excel mehrere Bedeutungen. Im normalen Sprachgebrauch werden Tabellenblätter, Arbeitsblätter auch mit diesem Wort bezeichnet. Das war bis zur Version Excel 2003 auch ohne weiteres OK. Beginnend mit Version 2007 wurden die Konstrukte, die vorher als Liste bezeichnet wurden, als Intelligente Tabelle, mitunter auch nur kurz Tabelle bezeichnet. Der Name ist unterschiedlich, die Handhabung praktisch gleich. Es handelt sich in beiden Fällen um einen zusammenhängenden Arbeitsbereich eines Arbeitsblattes, der Überschriften und mindestens 1 Zeile mit Daten hat. Leerzeilen sind in einer solchen Tabelle nicht möglich. In den meisten Fällen reicht die Tastenkombination StrgT (Table) oder StrgL (List) aus, wenn eine Zelle der künftigen Liste/Tabelle ausgewählt ist. Per Default werden automatisch Tabellierlinien (abwechselnd blau / weiß) eingefügt.
Das arbeiten mit diesen Tabellen bzw. Listen gestaltet sich erheblich komfortabler als mit „normalen” Daten-Bereichen. Das beginnt damit, dass sortieren und filtern viel einfacher ist und hört nicht damit auf, dass sich der Bereich der Tabelle dynamisch ohne Ihr Zutun anpasst. – Diese Auflistung der Maschinen hätte auch als ganz normaler Bereich funktioniert, wenn es um die farbliche Hervorhebung geht. Aber falls die Aufstellung beispielsweise nach dem Datum der Fälligkeit sortiert werden soll, ist das so recht angenehm.
Besonderheiten der Spalten
Spalte A
Eigentlich nichts weltbewegendes. Ich habe in A2 den Text Maschine 1 geschrieben und dann bis A11 herunter gezogen. Das hochzählen erledigt Excel von alleine. Ein kleiner „Bug” ist da aber doch noch drin: Sortieren Sie einmal nach Spalte A, dann werden Sie rasch erkennen, was ich meine. Hier bietet sich dann an, von Vornherein die Schreibweise Maschine 01 zu verwenden, um vernünftig sortieren zu können.
Spalte B
Die sieht nun wirklich unspektakulär aus. Verschiedene Wartungsintervalle, die sich teilweise wiederholen. Auf den zweiten Blick werden Sie feststellen, dass die Zellinhalte rechts ausgerichtet sind, obwohl dort scheinbar Text drin steht. Und ich versichere Ihnen, dass da nichts an der Ausrichtung manipuliert worden ist.
Des Rätsels Lösung erkennen Sie in der Editierzeile, der Eingabezeile. In der Zelle steht eigentlich eine Zahl, kein Text. Und das macht auch Sinn, denn das Intervall (immer in Monaten) ist ja auch die Basis für die Berechnung der Fälligkeit.
Was nun wirklich beachtenswert ist: Das Zahlenformat. In der einfachen Form (so auch im Forenbeitrag) werden Sie als benutzerdefiniertes Zahlenformat folgende Formel schreiben: 0 "Monat(e)". In der Mustermappe ist aber auch bei einem einzigen Monat die korrekte Schreibweise in der Zelle sichtbar. Dazu schreiben Sie als Formel beim benutzerdefinierten Zahlenformat: [=1] "1 Monat"; 0 "Monate". Exakt so mit den Leerstellen.
Spalte C
OK, da gibt es keine Besonderheiten. Per Hand wird hier der Tag der letzten Wartung oder Inspektion eingetragen. Dieses Datum ist dann die Grundlage für die Berechnung des Fälligkeitstermins.
Aber ich hätte die Spalte gar nicht erwähnt, wenn Sie keinen Mehrwert daraus schöpfen könnten. Mit drei Ausnahmen sind die kalendarischen Daten nämlich weitgehend zufällig. Das habe ich beispielsweise in Zeile 2 mit folgender Formel erreicht:
=ZUFALLSBEREICH("1.1.2014"; "1.1.2016")
Die Funktion ZUFALLSBEREICH() gibt einen zufälligen numerischen Wert zwischen den beiden Argumenten zurück. Mehr dazu können Sie hier nachlesen. Obwohl als Argument zwei numerische Werte verlangt werden, kann Excel die als Text übergebenen kalendarischen Daten so umwandeln, dass diese als serielle Zahl interpretiert werden. Somit erspare ich mir die „serielle” Darstellung des Datums in einer weiteren Zelle.
Drei dieser Zellen haben jedoch aus nachvollziehbaren Gründen eine andere Formel. Die erste ist C3, wo ein Datum eingetragen wird, welches 5 Tage vor dem heutigen ist. Damit ist gewährleistet, dass innerhalb von 7 Tagen eine Inspektion fällig ist. Und das hat dann zur Folge, dass D3 gelb hinterlegt wird.
C10 ist der nächste „Kandidat”. Per DATUM()-Funktion sowie den Funktionen JAHR(), MONAT(), TAG() und HEUTE() wird ein Datum berechnet, welches 1 Monat vor dem aktuellen Tag liegt. Da für diese Maschine auch 1 Monat als Intervall festgelegt ist, wird D10 rot hinterlegt; es ist der exakte Tag für die Wartung.
Die letzte Datenzeile scheint einfallslos zu sein. Das heutige Datum in Spalte C. Mit anderen Worten: An dem Tag wurde die Inspektion der Maschine durchgeführt. Der eigentliche Hintergrund: So ist gewährleistet, dass in jedem Fall eine Zelle der Spalte D in einem Zeitbereich liegt, wo keine Inspektion fällig ist.
Spalte D
Hier wird mit folgender Formel der Tag der Fälligkeit berechnet:
=DATUM(JAHR(C2); MONAT(C2)+B2; TAG(C2))
Das funktioniert, weil in Spalte B in Wirklichkeit nur eine Zahl steht und kein Text. Die Logik ist die gleiche wie weiter oben diskutierten DATUM()-Funktion. Zum Tag der letzten Wartung werden so viel Monate addiert, wie in Spalte B eingetragen. Es wird ein „echtes” Datum in die Zelle geschrieben. Eventuell ist noch eine Formatierung erforderlich. Und dieses Datum wird bei der bedingten Formatierung ausgewertet.
Die bedingte Formatierung
Der eigentliche Sinn dieser Liste ist ja, alle kurzfristig anstehenden oder (über-) fälligen Termine optisch hervorzuheben. Gelb für demnächst fällig (ab 7 Tage vor dem Termin) und rot beginnend mit dem Tag der Fälligkeit. Das lässt sich mit Bedingter Formatierung lösen. Dabei ist die Reihenfolge wichtig, in welcher die beiden Bedingungen eingetragen werden:
Die Reihenfolge bei der Bedingten Formatierung
Wenn Sie schon etwas Erfahrung mit Bedingter Formatierung haben, werden Sie die Werte vielleicht einfach übernehmen. Sie können aber auch die stichwortartige Kurzanleitung durcharbeiten … 😉
- Markieren Sie den kompletten einzufärbenden Bereich, hier: D2:D11
- Wählen Sie den Menüpunkt für die Bedingte Formatierung aus
- Klicken Sie auf Neue Regel…
- Wählen Sie die Möglichkeit, eine Formel zur Ermittlung … zu verwenden
- Tragen Sie bei der Regelbeschreibung zuerst folgende Formel exakt so ein:
=D2<=HEUTE() - Klicken Sie auf Formatieren… und wählen Sie das Rot als Hintergrundfarbe, als Schriftfarbe ist weiß geeignet.
- Übernehmen Sie die Einstellungen mit einem OK.
- Verfahren Sie mit der gelben Hintergrundfarbe gleichermaßen, nur dass sich hier die Formel folgendermaßen gestaltet:
=D2-7<HEUTE()
wobei -7 die 7 Tage Vorlauf-Frist definiert. - Verschieben Sie nun die Einträge in die richtige Reihenfolge mit den Schaltflächen ▲ bzw.▼ weit oben, direkt neben den drei großen Schaltflächen.
- Prüfen Sie noch einmal, ob der angegebene Bereich korrekt ist, korrigieren Sie ihn erforderlichenfalls.
Jetzt sollten Sie Ihr Ziel erreicht haben. Sollten Sie die alle vier Spalten einer Zeile einfärben wollen, dann sähe die Formel in der Regelbeschreibung etwas anders aus:
=$D2<=HEUTE()
und der Bereich muss natürlich auch angepasst werden, wie hier für rot dargestellt:
=$A$2:$D$11
Sollten Sie noch Fragen haben kontaktieren Sie uns gerne.
[NachObenLetzte Verweis=„ML: Terminüberwachung”]