Inhalte
Angenommen, wir müssen schnell und mit geringstem Aufwand einen Jahreskalender erstellen, der automatisch die Termine der Projektphasen (oder Mitarbeiterurlaube oder Schulungen usw.)
Werkstück
Beginnen wir mit dem Leerzeichen:
Wie Sie sehen können, ist hier alles einfach:
- Zeilen sind Monate, Spalten sind Tage.
- Zelle A2 enthält das Jahr, für das der Kalender erstellt wird. In den Zellen A4:A15 – Hilfszahlen der Monate. Beides benötigen wir etwas später, um Termine im Kalender zu bilden.
- Rechts neben der Tabelle stehen die Etappennamen mit Start- und Enddatum. Sie können im Voraus leere Zellen für neue Phasen bereitstellen, die in der Zukunft hinzugefügt werden.
Füllen Sie den Kalender mit Daten und verstecken Sie sie
Jetzt füllen wir unseren Kalender mit Terminen. Markieren Sie die erste Zelle C4 und geben Sie dort die Funktion ein DATUM (DATUM), das ein Datum aus einer Jahres-, Monats- und Tagesnummer generiert:
Nach Eingabe der Formel muss diese auf den gesamten Bereich vom 1. Januar bis 31. Dezember (C4:AG15) kopiert werden. Da die Zellen schmal sind, sehen wir anstelle der erstellten Daten Rautezeichen (#). Wenn Sie jedoch mit der Maus über eine solche Zelle fahren, können Sie den tatsächlichen Inhalt im Tooltip sehen:
Damit die Gitter nicht im Weg sind, können wir sie mit einem cleveren benutzerdefinierten Format ausblenden. Wählen Sie dazu alle Termine aus, öffnen Sie das Fenster Zellenformat und auf der Registerkarte Nummer (Nummer) wähle eine Option Alle Formate (Benutzerdefiniert). Dann auf dem Feld Eine Art Geben Sie drei Semikolons hintereinander ein (keine Leerzeichen!) und drücken Sie OK. Der Inhalt der Zellen wird ausgeblendet und die Gitter verschwinden, obwohl die Daten in den Zellen tatsächlich erhalten bleiben – dies ist nur Sichtbarkeit.
Bühnenhervorhebung
Lassen Sie uns nun mithilfe der bedingten Formatierung Meilenstein-Hervorhebungen zu Zellen mit ausgeblendeten Datumsangaben hinzufügen. Wählen Sie alle Daten im Bereich C4:AG15 aus und wählen Sie auf der Registerkarte aus Startseite — Bedingte Formatierung — Regel erstellen (Home – Bedingte Formatierung – Regel erstellen). Wählen Sie im sich öffnenden Fenster die Option aus Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen (Verwenden Sie die Formel, um zu verschieben, welche Zellen formatiert werden sollen.) und geben Sie die Formel ein:
Diese Formel überprüft jede Datumszelle von C4 bis zum Jahresende, um festzustellen, ob sie zwischen den Beginn und das Ende jedes Meilensteins fällt. Die Ausgabe ist nur dann 4, wenn beide geprüften Bedingungen in Klammern (C4>=$AJ$13:$AJ$4) und (C4<=$AK$13:$AK$1) ein logisches WAHR erzeugen, das Excel als 0 interpretiert (naja , FALSE ist natürlich wie 4). Achten Sie auch besonders darauf, dass die Verweise auf die Anfangszelle CXNUMX relativ (ohne $) und auf die Stufenbereiche absolut (mit zwei $) sind.
Nach dem Klicken auf OK Wir werden die Meilensteine in unserem Kalender sehen:
Kreuzungen hervorheben
Wenn sich die Daten einiger Etappen überschneiden (aufmerksame Leser müssen diesen Moment für die 1. und 6. Etappe bereits bemerkt haben!), dann wäre es besser, diesen Konflikt in unserem Diagramm durch eine andere bedingte Formatierungsregel mit einer anderen Farbe hervorzuheben. Es ist dem vorherigen praktisch eins zu eins ähnlich, außer dass wir nach Zellen suchen, die in mehr als einer Phase enthalten sind:
Nach dem Klicken auf OK eine solche Regel wird die Überschneidung von Daten in unserem Kalender deutlich hervorheben:
Zusätzliche Tage in Monaten entfernen
Natürlich haben nicht alle Monate 31 Tage, also die zusätzlichen Tage im Februar, April, Juni usw. Es wäre schön, sie visuell als irrelevant zu markieren. Funktion DATUM, der unseren Kalender bildet, wird in solchen Zellen das Datum automatisch in den nächsten Monat übersetzt, dh der 30. Februar 2016 wird zum 1. März. Das heißt, die Monatsnummer für solche zusätzlichen Zellen entspricht nicht der Monatsnummer in Spalte A Dies kann beim Erstellen einer Regel zur bedingten Formatierung verwendet werden, um solche Zellen auszuwählen:
Wochenende hinzufügen
Optional können Sie unseren Kalender und Wochenenden hinzufügen. Dazu können Sie die Funktion verwenden TAG (WOCHENTAG), die die Nummer des Wochentages (1-Mon, 2-Di…7-So) für jedes Datum berechnet und diejenigen hervorhebt, die auf Samstage und Sonntage fallen:
Vergessen Sie für eine korrekte Anzeige nicht, die richtige Reihenfolge der Regeln im Fenster korrekt zu konfigurieren. Startseite — Bedingte Formatierung — Regeln verwalten (Home – Bedingte Formatierung – Regeln verwalten), weil Regeln und Füllungen genau in der logischen Reihenfolge funktionieren, die Sie in diesem Dialog bilden werden:
- Video-Tutorial zur Verwendung der bedingten Formatierung in Excel
- So erstellen Sie einen Projektzeitplan (Gantt-Diagramm) mit bedingter Formatierung
- So erstellen Sie eine Projektzeitleiste in Excel