Ziel dieses Workshops ist es einen Kalender zu erstellen, der nur durch Eingabe der Jahreszahl automatisch ein Kalenderblatt erzeugt, in dem die Wochenenden und die gesetzlichen Feiertage markiert und die Anzahl der Arbeitstage angezeigt werden. Der Kalender soll für die Nutzung der Design-Features der neueren Excel Versionen vorbereitet sein. In diesem 4. Teil des Workshops legen Sie die restlichen Monate und Tage an, ermitteln die Arbeitstage und stellen den Druckbereich ein.
Sie könnten die ersten Teile der Workshops:
nacharbeiten und Ihre eigene Kalenderdatei erstellen. Oder Sie laden sich eine der Dateien herunter und starten mit dem Workshop Ihrer Wahl:
- “Kalender Teil 1 für Excel 2010”,
- “Kalender Teil 2 für Excel 2010” oder
- “Kalender Teil 3 für Excel 2010”.
Die Dateien zum Workshop finden Sie wie immer am Ende dieses Blog Post. Für die vergrößerte Darstellung eines Bildes klicken Sie auf das Bild.
Überschriften und Monate mit Schriftfarbe versehen
Markieren Sie die Zellen A1, B2 und D2, also die Zellen mit dem Jahr und den Monaten Januar und Februar. Wählen Sie dann eine Designfarbe für die Schrift aus, hier habe ich wieder das mittlere Design-Blau gewählt.
Markieren Sie das Feld D2, das mit dem Februar, und gehen Sie auf die rechte untere Ecke, klicken Sie mit der linken Maus darauf und ziehen die Maus nach rechts, so füllen Sie die Monate bis Dezember aus. Wahrscheinlich werden Sie am Bildrand absetzen, den Fensterinhalt nach rechts scrollen und dann weiter ausfüllen. Enden Sie mit dem Dezember, es sollte die Zelle AJ2 sein.
Markieren Sie im Monat Februar die Tage vom 1. bis zum 7. Februar (D3:D9). Halten Sie die <STRG>-Taste gedrückt und markieren Sie die Tage für die Monate März bis Dezember. sollten Sie abrutschen, dann müssen Sei weder von vorn beginnen. Klicken Sie dann auf dem Menüband “Start” die Gruppe “Bearbeiten” und dann “Füllbereich” und “Rechts”. Alternativ hätten Sie auch <STRG>R benutzen können. Verfahren Sie für die Wochentage und die Feiertage/KW analog. Am Ende sollte die erste Woche von Januar bis Dezember eingetragen sein.
Markieren Sie nun in Zeile 9 den Bereich A9:AJ9, Sie könnten auch im Namenfeld “A9:AJ9” ohne Anführungszeichen eingeben und mit der <ENTER>-Taste bestätigen. Klicken Sie auf die rechte untere Ecke, Mauszeiger muss zuerst zu dünnem schwarzen Plus werden, und ziehen Sie die Maus bis zur Zeile 33. Sie müssen ggf. absetzen. Wenn Sie alles korrekt ausgefüllt haben, dann erscheint in der rechten unteren Zelle “Silvester”.
Sie wissen, dass einige Monate 28/29, manche 30 und andere 31 Tage haben. Das müssen Sie nun korrigieren. Markieren Sie dazu die Felder, bei denen es mit dem 1. des Nachfolgemonats weiter geht. Das müssten der 2. und 3. März, der 1. Mai, der 1. Juli, der 1. Oktober und der 1. Dezember sein.
Nach dem Markieren drücken Sie bitte die <ENTF>-Taste, die Formeln in den Zellen werden gelöscht. Nun müssen Sie noch die bedingte Formatierung entfernen. Wählen Sie auf dem Menüband “Start” in der Gruppe “Formatvorlagen” die “Bedingte Formatierung”, dann “Regeln löschen” und “Regeln in ausgewählten Zellen löschen”.
Markieren Sie alle Tage-Spalten und alle Wochentag-Spalten, sie werden auf eine Breite von 7 Excel-Einheiten eingestellt. Klicken Sie mit der rechtem Maus auf den Spaltenkopf und wählen Sie “Spaltenbreite…”. Geben Sie hier den Wert 7 ein. Verfahren Sie analog für die Feiertag/KW-Spalten und stellen Sie die Breite auf 25 ein.
Nun müssen Sie sich um den 29. Februar kümmern. Dazu verwenden wir einen Trick, denn Excel kennt Schaltjahre und wendet Sie auch an. Wenn Sie in einem Schaltjahr zum 28. Februar einen Tag hinzuaddieren, dann erhalten Sie den 29. Februar. Vollziehen Sie das in einem Nichtschaltjahr, dann erhalten Sie den 1. März. Wir müssen also den Monat checken.
Wir betten die in den Zellen D31 und F31 stehenden Formeln einfach in eine Wenn-Anweisung ein.
=Wenn(Monat(D30+1)=2; ursprüngliche Formel ;””)
Um die Zelle E31 brauchen Sie sich nicht kümmern, die ist schon geschickt programmiert. 
Nun formatieren Sie die Zeile in der die Anzahl der Arbeitstage stehen sollen. Markieren Sie dazu Zeile 2 (hier stehen die Monate) und Zeile 34 (hier sollen die Arbeitstage stehen). Füllen Sie die Inhalte nach unten aus, bspw. <STRG>U oder über das Menüband.
Jetzt ermitteln Sie die Arbeitstage, dazu gibt es die Funktion NETTOARBEITSTAGE. Klicken Sie bitte in Zelle A34, dort steht z.Z. Januar und geben folgende Formel ein:
=NETTOARBEITSTAGE(MIN(A3:A33);MAX(A3:A33);Feiertage)
Das Startdatum ist immer das kleinste Datum in der Spalte (MIN), das Enddatum dann das größte Datum (MAX). Um ggf. Feiertage abzuziehen geben Sie den Bereichsnamen “Feiertage” ein. Zählen Sie bspw. die Arbeitstage im Dezember, es funktioniert. Benutzen Sie die benutzerdefinierte Formatierung für die Zelle A34 und geben Sie [0 “Arbeitstage] ohne eckige Klammern ein.
![Arbeitstage benutzerdefiniert formatiert - [0 "Arbeitstage"] ohne eckige Klammern](http://kuettner.it/wp-content/uploads/2011/03/kal414.jpg "Arbeitstage benutzerdefiniert formatiert - [0 \"Arbeitstage\"] ohne eckige Klammern")
Füllen Sie den Bereich A34:AJ34 nach rechts aus, benutzen Sie den Befehl aus dem Menüband oder <STRG>R. Nun sollten alle Arbeitstage von Januar bis Dezember berechnet werden.
Markieren Sie den Bereich A1:AJ34 und wählen Sie auf dem Menüband “Seitenlayout” die Gruppe “Seite einrichten”, dann “Druckbereich” und “Druckbereich festlegen”.
Jetzt müssen Sie Excel so einstellen, dass es alles auf eine Seite druckt. Dazu Klicken Sie auf dem Menüband “Seitenlayout” innerhalb der Gruppe “Seite einrichten” auf das kleine Kästchen in der rechten unteren Ecke, wählen dann als Ausrichtung das Querformat und Klicken im Bereich Skalierung auf Anpassen und wählen 1 Seite breit und 1 Seite hoch.
Die Druckvorschau über Datei – Drucken zeigt wie der Kalender im Ausdruck ausschaut.
Das war es mit Teil 4 des Workshops. Im 5. Teil werden Sie das Tabellenblatt “Berechnung” verbergen und die Datei in einem Format für ältere Excel Versionen abspeichern. Dazu müssen Sie die Funktion NETTOARBEITSTAGE ersetzen, denn diese gab es damals noch nicht.
Viel Erfolg und Spaß beim Nachmachen.
Kommentare
7 Kommentare zu „Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 4 – Aufhübschen“