Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 4 – Aufhübschen

Ziel die­ses Work­shops ist es einen Kalen­der zu erstel­len, der nur durch Ein­gabe der Jah­res­zahl auto­ma­tisch ein Kalen­der­blatt erzeugt, in dem die Wochen­en­den und die gesetz­li­chen Fei­er­tage mar­kiert und die Anzahl der Arbeits­tage ange­zeigt wer­den. Der Kalen­der soll für die Nut­zung der Design-Fea­tures der neue­ren Excel Ver­sio­nen vor­be­rei­tet sein. In die­sem 4. Teil des Work­shops legen Sie die rest­li­chen Mona­te und Tage an, ermit­teln die Arbeits­ta­ge und stel­len den Druck­be­reich ein. 

Sie könn­ten die ers­ten Tei­le der Workshops: 

• Teil 1 — Grund­la­gen,
• Teil 2 – Pro­gram­mie­rung oder 
• Teil 3 – Die Beding­te For­ma­tie­rung meis­tern

nach­ar­bei­ten und Ihre eige­ne Kalen­der­da­tei erstel­len. Oder Sie laden sich eine der Datei­en her­un­ter und star­ten mit dem Work­shop Ihrer Wahl: 

• “Kalen­der Teil 1 für Excel 2010”,
• “Kalen­der Teil 2 für Excel 2010” oder
• “Kalen­der Teil 3 für Excel 2010”.

Die Datei­en zum Work­shop fin­den Sie wie immer am Ende die­ses Blog Post. Für die ver­grö­ßer­te Dar­stel­lung eines Bil­des kli­cken Sie auf das Bild. 

Überschriften und Monate mit Schriftfarbe versehen

Mar­kie­ren Sie die Zel­len A1, B2 und D2, also die Zel­len mit dem Jahr und den Mona­ten Janu­ar und Febru­ar. Wäh­len Sie dann eine Design­far­be für die Schrift aus, hier habe ich wie­der das mitt­le­re Design-Blau gewählt.

Mar­kie­ren Sie das Feld D2, das mit dem Febru­ar, und gehen Sie auf die rech­te unte­re Ecke, kli­cken Sie mit der lin­ken Maus dar­auf und zie­hen die Maus nach rechts, so fül­len Sie die Mona­te bis Dezem­ber aus. Wahr­schein­lich wer­den Sie am Bild­rand abset­zen, den Fens­ter­in­halt nach rechts scrol­len und dann wei­ter aus­fül­len. Enden Sie mit dem Dezem­ber, es soll­te die Zel­le AJ2 sein.

Mar­kie­ren Sie im Monat Febru­ar die Tage vom 1. bis zum 7. Febru­ar (D3:D9). Hal­ten Sie die <STRG>-Taste gedrückt und mar­kie­ren Sie die Tage für die Mona­te März bis Dezem­ber. soll­ten Sie abrut­schen, dann müs­sen Sei weder von vorn begin­nen. Kli­cken Sie dann auf dem Menü­band “Start” die Grup­pe “Bear­bei­ten” und dann “Füll­be­reich” und “Rechts”. Alter­na­tiv hät­ten Sie auch <STRG>R benut­zen kön­nen. Ver­fah­ren Sie für die Wochen­ta­ge und die Feiertage/KW ana­log. Am Ende soll­te die ers­te Woche von Janu­ar bis Dezem­ber ein­ge­tra­gen sein.

Mar­kie­ren Sie nun in Zei­le 9 den Bereich A9:AJ9, Sie könn­ten auch im Namen­feld “A9:AJ9” ohne Anfüh­rungs­zei­chen ein­ge­ben und mit der <ENTER>-Taste bestä­ti­gen. Kli­cken Sie auf die rech­te unte­re Ecke, Maus­zei­ger muss zuerst zu dün­nem schwar­zen Plus wer­den, und zie­hen Sie die Maus bis zur Zei­le 33. Sie müs­sen ggf. abset­zen. Wenn Sie alles kor­rekt aus­ge­füllt haben, dann erscheint in der rech­ten unte­ren Zel­le “Sil­ves­ter”.

Sie wis­sen, dass eini­ge Mona­te 28/29, man­che 30 und ande­re 31 Tage haben. Das müs­sen Sie nun kor­ri­gie­ren. Mar­kie­ren Sie dazu die Fel­der, bei denen es mit dem 1. des Nach­fol­ge­mo­nats wei­ter geht. Das müss­ten der 2. und 3. März, der 1. Mai, der 1. Juli, der 1. Okto­ber und der 1. Dezem­ber sein.

Nach dem Mar­kie­ren drü­cken Sie bit­te die <ENTF>-Taste, die For­meln in den Zel­len wer­den gelöscht. Nun müs­sen Sie noch die beding­te For­ma­tie­rung ent­fer­nen. Wäh­len Sie auf dem Menü­band “Start” in der Grup­pe “For­mat­vor­la­gen” die “Beding­te For­ma­tie­rung”, dann “Regeln löschen” und “Regeln in aus­ge­wähl­ten Zel­len löschen”.

Mar­kie­ren Sie alle Tage-Spal­ten und alle Wochen­tag-Spal­ten, sie wer­den auf eine Brei­te von 7 Excel-Ein­hei­ten ein­ge­stellt. Kli­cken Sie mit der rech­tem Maus auf den Spal­ten­kopf und wäh­len Sie “Spal­ten­brei­te…”. Geben Sie hier den Wert 7 ein. Ver­fah­ren Sie ana­log für die Fei­er­ta­g/KW-Spal­ten und stel­len Sie die Brei­te auf 25 ein.

Nun müs­sen Sie sich um den 29. Febru­ar küm­mern. Dazu ver­wen­den wir einen Trick, denn Excel kennt Schalt­jah­re und wen­det Sie auch an. Wenn Sie in einem Schalt­jahr zum 28. Febru­ar einen Tag hin­zu­ad­die­ren, dann erhal­ten Sie den 29. Febru­ar. Voll­zie­hen Sie das in einem Nicht­schalt­jahr, dann erhal­ten Sie den 1. März. Wir müs­sen also den Monat checken.

Wir bet­ten die in den Zel­len D31 und F31 ste­hen­den For­meln ein­fach in eine Wenn-Anwei­sung ein.

=Wenn(Monat(D30+1)=2;     ursprüngliche Formel     ;””)

Um die Zel­le E31 brau­chen Sie sich nicht küm­mern, die ist schon geschickt programmiert. 

Nun for­ma­tie­ren Sie die Zei­le in der die Anzahl der Arbeits­ta­ge ste­hen sol­len. Mar­kie­ren Sie dazu Zei­le 2 (hier ste­hen die Mona­te) und Zei­le 34 (hier sol­len die Arbeits­ta­ge ste­hen). Fül­len Sie die Inhal­te nach unten aus, bspw. <STRG>U oder über das Menüband.

Jetzt ermit­teln Sie die Arbeits­ta­ge, dazu gibt es die Funk­ti­on NET­TO­AR­BEITS­TA­GE. Kli­cken Sie bit­te in Zel­le A34, dort steht z.Z. Janu­ar und geben fol­gen­de For­mel ein:

=NETTOARBEITSTAGE(MIN(A3:A33);MAX(A3:A33);Feiertage)

Das Start­da­tum ist immer das kleins­te Datum in der Spal­te (MIN), das End­da­tum dann das größ­te Datum (MAX). Um ggf. Fei­er­ta­ge abzu­zie­hen geben Sie den Bereichs­na­men “Fei­er­ta­ge” ein. Zäh­len Sie bspw. die Arbeits­ta­ge im Dezem­ber, es funk­tio­niert. Benut­zen Sie die benut­zer­de­fi­nier­te For­ma­tie­rung für die Zel­le A34 und geben Sie [0 “Arbeits­ta­ge] ohne ecki­ge Klam­mern ein.

Fül­len Sie den Bereich A34:AJ34 nach rechts aus, benut­zen Sie den Befehl aus dem Menü­band oder <STRG>R. Nun soll­ten alle Arbeits­ta­ge von Janu­ar bis Dezem­ber berech­net werden.

Mar­kie­ren Sie den Bereich A1:AJ34 und wäh­len Sie auf dem Menü­band “Sei­ten­lay­out” die Grup­pe “Sei­te ein­rich­ten”, dann “Druck­be­reich” und “Druck­be­reich festlegen”. 

Jetzt müs­sen Sie Excel so ein­stel­len, dass es alles auf eine Sei­te druckt. Dazu Kli­cken Sie auf dem Menü­band “Sei­ten­lay­out” inner­halb der Grup­pe “Sei­te ein­rich­ten” auf das klei­ne Käst­chen in der rech­ten unte­ren Ecke, wäh­len dann als Aus­rich­tung das Quer­for­mat und Kli­cken im Bereich Ska­lie­rung auf Anpas­sen und wäh­len 1 Sei­te breit und 1 Sei­te hoch.

Die Druck­vor­schau über Datei – Dru­cken zeigt wie der Kalen­der im Aus­druck ausschaut.

Das war es mit Teil 4 des Work­shops. Im 5. Teil wer­den Sie das Tabel­len­blatt “Berech­nung” ver­ber­gen und die Datei in einem For­mat für älte­re Excel Ver­sio­nen abspei­chern. Dazu müs­sen Sie die Funk­ti­on NET­TO­AR­BEITS­TA­GE erset­zen, denn die­se gab es damals noch nicht.

Viel Erfolg und Spaß beim Nachmachen. 

Down­loads

• Kalen­der Teil 1 für Excel 2010,
• Kalen­der Teil 2 für Excel 2010,
• Kalen­der Teil 3 für Excel 2010 und 
• Kalen­der Teil 4 für Excel 2010.