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-Features der neue­ren Excel Ver­sio­nen vor­be­rei­tet sein. In die­sem 5. Teil des Work­shops ler­nen Sie wie Sie Tabel­len­blät­ter ver­ste­cken und den Kalen­der für Excel 97–2003 aufbereiten.

Sie könn­ten die ers­ten vier Teile der Workshop-Reihe:

• Teil 1 — Grund­la­gen,
• Teil 2 – Pro­gram­mie­rung,
• Teil 3 – Die Bedingte For­ma­tie­rung meis­tern oder
• Teil 4 — Auf­hüb­schen

nach­ar­bei­ten und Ihre eigene Kalen­der­da­tei erstel­len. Oder Sie laden sich eine der Dateien 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”,
• “Kalen­der Teil 3 für Excel 2010” oder 
• “Kalen­der Teil 4 für Excel 2010”.

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

Tabelle Berech­nung verstecken

Ich ver­sehe Excel Arbeits­map­pen, die ich für ein Pro­blem erar­bei­tet habe, häu­fig mit einer Kom­men­tar­seite, für den Fall, dass ich die Tabelle ggf. noch­mals anpas­sen muss. Meist wer­den die Men­schen, für die ich die Excel Tabelle erar­bei­tet habe, durch die Kom­men­tare und Berech­nun­gen nur ver­wirrt. Also muss ich diese verstecken.

Öffnen Sie Ihr Excel Tabelle und rufen Sie mit <ALT><F11> den Visual Basic Edi­tor auf, funk­tio­niert mit jeder Excel Ver­sion. Sicher kön­nen Sie mit die­sem Edi­tor noch mehr anstel­len, wir wol­len aber nur eine Tabelle verbergen.

Sollte Ihr Visual Basic Edi­tor nur ein lee­res graues Fens­ter zei­gen und nicht wie abge­bil­det aus­se­hen, so akti­vie­ren Sie mit Menü – Ansicht – Projekt-Explorer oder >STRG>R das Fens­ter “Pro­jekt – VBA­Pro­jekt”. Das Eigen­schaf­ten Fens­ter rufen Sie mit <F4> oder Menü – Ansicht – Eigen­schaf­ten­fens­ter auf.

Wäh­len Sie im Pro­jekt­fens­ter Ihre Tabelle 2 (Berech­nung) aus, ändern Sie dann im Eigen­schaf­ten Fens­ter die Eigen­schaft “Visi­ble” (Sicht­bar­keit) von “-1 –xlS­heet­Vi­si­ble” auf “0 – xlS­heetHid­den”. Das war’s schon. Ach­ten Sie dar­auf, dass Sie nicht alle Blät­ter einer Arbeits­mappe ver­ste­cken können.

Schlie­ßen Sie den VB Edi­tor durch Klick auf das rote Kreuz in der rech­ten obe­ren Ecke, Sie müs­sen nichts spei­chern, das erfolgt auto­ma­tisch. Die Tabelle “Berech­nung” sollte ver­schwun­den sein.

Kalen­der für Excel 97 bis 2003 abspeichern

Nun spei­chern Sie die Kalen­der Datei kom­pa­ti­bel für Excel 97 bis 2003, dazu kli­cken Sie auf Datei und “Spei­chern unter”. Wäh­len Sie “Excel-97–2003-Arbeitsmappe” und geben Sie als Datei­na­men bspw. “kalender2003.xls” ein. Kli­cken Sie dann auf “Speichern”.

Lei­der fin­det die Kom­pa­ti­bi­li­täts­prü­fung nicht alle Feh­ler. Gefun­den werden:

1. Bedingte For­ma­tie­rung für den Kalen­der, hier wird erheb­li­cher Funk­ti­ons­ver­lust pro­gnos­ti­ziert, der so jedoch nicht ein­tre­ten wird.
2. For­ma­tie­run­gen ein­zel­ner Zel­len sol­len einen gering­fü­gi­gen Genau­ig­keits­ver­lust haben, ver­mut­lich meint Excel hier die Umset­zung der Desi­gnfar­ben, die es in den alten Ver­sio­nen so nicht gibt.

Nicht gefun­den wird die Funk­tion NET­TO­AR­BEITS­TAGE, diese Funk­tion gibt es nicht in Excel 97–2003. Sie erset­zen die Funk­tion mit einer VBA-Funktion, die das Laden eines Add-Ins erfordert.

Wäh­len Sie im Fens­ter für die Kom­pa­ti­bi­li­täts­prü­fung “Korrigieren”.

Öffnen Sie die kon­ver­tierte Excel Datei in der älte­ren Excel Ver­sion, hier vor­ge­führt mit Excel 2003.

Solange Sie eine der Zel­len mit der inkom­pa­ti­blen For­mel für die NET­TO­AR­BEITS­TAGE nicht anfas­sen, sieht alles per­fekt aus. Sobald Sie eine For­mel ankli­cken und mit <ENTER> bestä­ti­gen wer­den Feh­ler ange­zeigt. Das ältere Excel kennt die Funk­tion NET­TO­AR­BEITS­TAGE nicht.

Akti­vie­ren Sie über Menü “Extras” – “Add-Ins…” die “Analyse-Funktionen-VBA”. Jetzt erset­zen Sie die Funk­tion “NET­TO­AR­BEITS­TAGE ” durch “Net­Work­Days” und bestä­ti­gen die Ein­gabe mit der <ENTER>-Taste. In Zelle A34 sollte fol­gende For­mel stehen:

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

Mar­kie­ren Sie Zeile 34 und wäh­len Sie Menü “Bear­bei­ten” – “Erset­zen”. Geben Sie für “Suche nach” NET­TO­AR­BEITS­TAGE und für “Erset­zen durch” Net­Work­Days ein. Nach Klick auf “Alle erset­zen” erscheint ein Fens­ter, dass 11 Erset­zun­gen bestä­tigt. Jetzt berech­net die ältere Excel Ver­sion auch die Arbeitstage.

Wir sind fer­tig. Der Kalen­der funk­tio­niert auch mit der älte­ren Excel Ver­sion. Aber Ihnen gefällt, wie mir übri­gens auch, die “kon­ver­tierte” Desi­gnfarbe abso­lut nicht. Das Cyan ver­ur­sacht doch Augenkrebs.

Kon­ver­tierte Design-Farbe gefällt nicht – was tun?

Ich nehme für die schnelle Lösung bewusst in Kauf, dass die For­meln noch­mals ein­ge­ge­ben wer­den müs­sen. Sie kön­nen die bedingte For­ma­tie­rung für Tage, Wochen­tage und Feiertage/KW auch für jeden Monat ein­zeln vor­neh­mem, das verzwölf­facht jedoch den Arbeitsaufwand.

Wir geben Voll­gas und ändern:

1. die bedingte For­ma­tie­rung für die Tage,
2. die bedingte For­ma­tie­rung für die Wochentage,
3. die bedingte For­ma­tie­rung für die Feiertage/KW,
4. die Tabel­len­li­nien waa­ge­recht und senk­recht und
5. die Far­ben für das Kalen­der­jahr, die Monate und die Nettoarbeitstage.

1. Bedingte For­ma­tie­rung der Tage im gesam­ten Kalender

Dazu geben Sie im Namen­feld fol­gende Berei­che ein und bestä­ti­gen die Ein­gabe mit <ENTER>, danach hal­ten Sie die <STRG>-Taste gedrückt und kli­cken auf Zelle A3!

A3:A33;D3:D31;G3:G33;J3:J32;M3:M33;P3:P32;S3:S33;V3:V33;Y3:Y32;AB3:AB33;AE3:AE32;AH3:AH33

Die For­mel für die bedingte For­ma­tie­rung lau­tet wie folgt, wäh­len Sie eine Formatierung.

=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)

2. Bedingte For­ma­tie­rung der Wochen­tage im gesam­ten Kalender

Fol­gende Berei­che aus­wäh­len. Dann bei gedrück­ter <STRG>-Taste auf die Zelle B3 klicken.

B3:B33;E3:E31;H3:H33;K3:K32;N3:N33;Q3:Q32;T3:T33;W3:W33;Z3:Z32;AC3:AC33;AF3:AF32;AI3:AI33

Die For­mel für die bedingte For­ma­tie­rung lau­tet wie folgt, wäh­len Sie eine For­ma­tie­rung wie bei den Tagen.

=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)

3. Bedingte For­ma­tie­rung der Feiertage/KW im gesam­ten Kalender

Den Bereich wie folgt wäh­len und dann bei bei gedrück­ter <STRG>-Taste auf die Zelle C3 klicken.

C3:C33;F3:F31;I3:I33;L3:L32;O3:O33;R3:R32;U3:U33;X3:X33;AA3:AA32;AD3:AD33;AG3:AG32;AJ3:AJ33

Für die bedingte For­ma­tie­rung sind zwei For­meln einzugeben.

=UND(ISTFEHLER(FINDEN(A3;Feiertagsliste));WOCHENTAG(A3;2)=1)
=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)

Die erste For­mel gilt für die Kalen­der­wo­che, ändern Sie hier nur die Schrift­farbe. For­mel 2 gilt für die Fei­er­tage, wäh­len Sie eine For­ma­tie­rung. Hal­ten Sie die Rei­hen­folge der ein­zel­nen For­meln für die bedingte For­ma­tie­rung ein. 

4. Tabel­len­li­nien waa­ge­recht und senkrecht

Nun müs­sen Sie die hori­zon­ta­len Linien for­ma­tie­ren, wäh­len Sie dazu fol­gen­den Bereich.

A2:AH34

Wäh­len Sie im Menü “For­mat” – “Zel­len” und dann “Rah­men”. ACH­TUNG! For­ma­tie­ren Sie die mitt­lere senk­rechte Linie nicht. For­ma­tie­ren Sie nur die äuße­ren senk­rech­ten Linien und alle waa­ge­rech­ten Linien.

Für das For­ma­tie­ren der senk­rech­ten Linien dür­fen wir nur jeden zwei­ten Monat mar­kie­ren. Wäh­len Sie fol­gen­den Bereich. For­ma­tie­ren Sie nur die äußere linke und äußerste rechte senk­rechte Linie.

D2:F34;J2:L34;P2:R34;V2:X34;AB2:AD34;AH2:AJ34

Jetzt müs­sen Sie nur noch die Zel­len mit dem Jahr, die Monate und die Arbeits­tage mar­kie­ren. Wäh­len Sie eine For­ma­tie­rung für die Schriftfarbe.

Das war’s. Der Kalen­der für Excel 97 bis 2003 sieht nun auch ange­nehm aus.

Viel Erfolg und Spaß beim Nachmachen.

Down­loads

1. Kalen­der Teil 1 für Excel 2010,
2. Kalen­der Teil 2 für Excel 2010,
3. Kalen­der Teil 3 für Excel 2010,
4. Kalen­der Teil 4 für Excel 2010,
5. fina­ler Kalen­der für Excel 2010,
6. kon­ver­tier­ter Kalen­der für Excel 97–2003 und
7. fina­ler Kalen­der für Excel 97–2003.

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-Features 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 Monate und Tage an, ermit­teln die Arbeits­tage und stel­len den Druck­be­reich ein.

Sie könn­ten die ers­ten Teile der Workshops:

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

nach­ar­bei­ten und Ihre eigene Kalen­der­da­tei erstel­len. Oder Sie laden sich eine der Dateien 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 Dateien zum Work­shop fin­den Sie wie immer am Ende die­ses Blog Post. Für die ver­grö­ßerte Dar­stel­lung eines Bil­des kli­cken Sie auf das Bild.

Über­schrif­ten und Monate mit Schrift­farbe versehen

Mar­kie­ren Sie die Zel­len A1, B2 und D2, also die Zel­len mit dem Jahr und den Mona­ten Januar und Februar. Wäh­len Sie dann eine Desi­gnfarbe für die Schrift aus, hier habe ich wie­der das mitt­lere Design-Blau gewählt.

Mar­kie­ren Sie das Feld D2, das mit dem Februar, und gehen Sie auf die rechte untere Ecke, kli­cken Sie mit der lin­ken Maus dar­auf und zie­hen die Maus nach rechts, so fül­len Sie die Monate 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 sollte die Zelle AJ2 sein.

Mar­kie­ren Sie im Monat Februar die Tage vom 1. bis zum 7. Februar (D3:D9). Hal­ten Sie die <STRG>-Taste gedrückt und mar­kie­ren Sie die Tage für die Monate 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 Gruppe “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­tage und die Feiertage/KW ana­log. Am Ende sollte die erste Woche von Januar bis Dezem­ber ein­ge­tra­gen sein.

Mar­kie­ren Sie nun in Zeile 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 rechte untere Ecke, Maus­zei­ger muss zuerst zu dün­nem schwar­zen Plus wer­den, und zie­hen Sie die Maus bis zur Zeile 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 Zelle “Silvester”.

Sie wis­sen, dass einige Monate 28/29, man­che 30 und andere 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 bitte die <ENTF>-Taste, die For­meln in den Zel­len wer­den gelöscht. Nun müs­sen Sie noch die bedingte For­ma­tie­rung ent­fer­nen. Wäh­len Sie auf dem Men­ü­band “Start” in der Gruppe “For­mat­vor­la­gen” die “Bedingte 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-Spalten und alle Wochentag-Spalten, sie wer­den auf eine Breite von 7 Excel-Einheiten ein­ge­stellt. Kli­cken Sie mit der rech­tem Maus auf den Spal­ten­kopf und wäh­len Sie “Spal­ten­breite…”. Geben Sie hier den Wert 7 ein. Ver­fah­ren Sie ana­log für die Feiertag/KW-Spalten und stel­len Sie die Breite auf 25 ein.

Nun müs­sen Sie sich um den 29. Februar küm­mern. Dazu ver­wen­den wir einen Trick, denn Excel kennt Schalt­jahre und wen­det Sie auch an. Wenn Sie in einem Schalt­jahr zum 28. Februar einen Tag hin­zu­ad­die­ren, dann erhal­ten Sie den 29. Februar. 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-Anweisung ein.

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

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

Nun for­ma­tie­ren Sie die Zeile in der die Anzahl der Arbeits­tage ste­hen sol­len. Mar­kie­ren Sie dazu Zeile 2 (hier ste­hen die Monate) und Zeile 34 (hier sol­len die Arbeits­tage ste­hen). Fül­len Sie die Inhalte nach unten aus, bspw. <STRG>U oder über das Menüband.

Jetzt ermit­teln Sie die Arbeits­tage, dazu gibt es die Funk­tion NET­TO­AR­BEITS­TAGE. Kli­cken Sie bitte in Zelle A34, dort steht z.Z. Januar und geben fol­gende For­mel ein:

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

Das Start­da­tum ist immer das kleinste Datum in der Spalte (MIN), das End­da­tum dann das größte Datum (MAX). Um ggf. Fei­er­tage abzu­zie­hen geben Sie den Bereichs­na­men “Fei­er­tage” ein. Zäh­len Sie bspw. die Arbeits­tage im Dezem­ber, es funk­tio­niert. Benut­zen Sie die benut­zer­de­fi­nierte For­ma­tie­rung für die Zelle A34 und geben Sie [0 “Arbeits­tage] ohne eckige 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­tage von Januar 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 Gruppe “Seite 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 Seite druckt. Dazu Kli­cken Sie auf dem Men­ü­band “Sei­ten­lay­out” inner­halb der Gruppe “Seite ein­rich­ten” auf das kleine 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 Seite breit und 1 Seite 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 ältere Excel Ver­sio­nen abspei­chern. Dazu müs­sen Sie die Funk­tion NET­TO­AR­BEITS­TAGE erset­zen, denn diese 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.

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-Features der neue­ren Excel Ver­sio­nen vor­be­rei­tet sein. In die­sem 3. Teil des Work­shops meis­tern Sie die “Bedingte Formatierung”.

Sie könn­ten die bei­den ers­ten Teile der Work­shops “Work­shop: Kalen­der mit Fei­er­ta­gen und Kalen­der­wo­che selbst erstel­len Teil 1 — Grund­la­gen” und “Work­shop: Kalen­der mit Fei­er­ta­gen und Kalen­der­wo­che selbst erstel­len Teil 2 – Pro­gram­mie­rung” nach­ar­bei­ten und Ihre eigene Kalen­der­da­tei erstel­len. Oder Sie laden sich eine der bei­den Dateien her­un­ter und star­ten mit dem Work­shop Ihrer Wahl:

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

Die Dateien des Work­shop Teil 3 fin­den Sie wie immer am Ende die­ses Blog Post. Kli­cken Sie auf ein Bild für eine ver­grö­ßerte Darstellung.

Was noch fehlte

Im Teil 2 die­ser Work­shop Reihe fehlte noch das For­ma­tie­ren der Zel­len in denen ein Fei­er­tag oder mög­li­cher­weise die Kalen­der­wo­che steht. die Berech­nung hat­ten Sie schon pro­gram­miert. Mar­kie­ren Sie bitte die Zel­len A4:F4 und fül­len Sie sie bitte bis zur Zeile 9 nach unten aus. Wenn Sie alles rich­tig gemacht haben, dann soll­ten Sie beim 1. Januar links­bün­dig “Neu­jahr” ste­hen haben und beim 3. Januar und 7. Februar eine 1 bzw. 6, das sind die Kalen­der­wo­chen. Wir möch­ten im Feld aber 1. KW bzw. 6. KW ste­hen haben, also bemü­hen Sie die benut­zer­de­fi­nierte Zell­for­ma­tie­rung. Mar­kie­ren Sie bitte die Zel­len C3:C9 und F3:F9 (<STRG> Taste), kli­cken mit der rech­ten Maus und wäh­len “Zel­len for­ma­tie­ren”. Geben Sie als Typ [0”. KW”] ohne die ecki­gen Klam­mern ein. Also Null Anfüh­rungs­zei­chen, Punkt, Leer­zei­chen, KW und dann Anführungszeichen.

Schön erkenn Sie, wir haben zwar für alle Zel­len eine Zah­len­for­ma­tie­rung defi­niert, diese gilt aber nur für die Zah­len, hier die Kalen­der­wo­chen. Wenn Sie ein­mal die Zel­len mar­kiert haben, ord­nen Sie den Inhalt bitte links­bün­dig an.

Die Bedingte Formatierung

Fol­gende For­ma­tie­run­gen für Fei­er­tage, Wochen­en­den und Mon­tage wol­len wir vornehmen:

• der Tag soll eine mitt­lere Füll­farbe und weiße Schrift bekommen,
• der Wochen­tag bekommt eine mitt­lere Füll­farbe und
• das Feld Feiertag/KW bekommt eben­falls eine mitt­lere Füllfarbe.
• Das Feld Feiertag/KW bekommt eine mitt­lere Schrift­farbe, sofern es ein Mon­tag ist, der kein Fei­er­tag ist.

Wochen­ende: WOCHENTAG(Datum;2)>5

Ein Datum fällt auf ein Wochen­ende, wenn der Wochen­tag grö­ßer als 5 ist, näm­lich Sams­tag oder Sonntag 

NICHT( IST­FEH­LER( FINDEN(Datum;Feiertagsliste) ) )

Ein Fei­er­tag ist, wenn beim Suchen in der Fei­er­tags­liste kein Feh­ler auf­tritt. Datum ist ein Fei­er­tag, dann lie­fern die Funk­tion FIN­DEN kei­nen Feh­ler, damit lie­fert die Funk­tion IST­FEH­LER den Wert FALSCH, die Funk­tion NICHT kehrt den Wert zu WAHR um. Damit hier die Feh­ler der Nicht­fei­er­tage abge­fan­gen wer­den kön­nen, wird diese Kon­struk­tion benötigt.

UND( IST­FEH­LER( FINDEN(Datum;Feiertagsliste));
WOCHENTAG(A3;2)=1)

Die Kalen­der­wo­che wird nur ein­ge­tra­gen, wenn der Mon­tag kein Fei­er­tag ist. Also nur wenn Wochentag=1 (Mon­tag) UND beim Suchen in der Fei­er­tags­liste ein Feh­ler auf­tritt, das Datum also kein Fei­er­tag ist.

Mit den fol­gen­den zwei For­meln lösen Sie die Bedingte For­ma­tie­rung für die oben auf­ge­stell­ten Regeln. ACH­TUNG! Bezugs­zelle ist hier A3.

=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)
=UND(ISTFEHLER(FINDEN(A3;Feiertagsliste));WOCHENTAG(A3;2)=1)

Mar­kie­ren Sie bitte die Zel­len A3:A9, begin­nen Sie unbe­dingt mit Zelle A3, da Sie als Bezugs­zelle für die For­ma­tie­rung der ande­ren Zel­len benutzt wird. Wür­den Sie von unten her mar­kie­ren, dann würde oben im Namen­feld nicht A3 son­dern A9 ste­hen und die For­mel müsste sich anstelle von A3 auf A9 bezie­hen. Also machen Sie es bitte wie vorgeschlagen.

Kli­cken Sie dann im Men­ü­band “Start”, Gruppe “For­mat­vor­la­gen” auf “Bedingte For­ma­tie­rung” und wäh­len “Neue Regel ..”.

Es öffnet sich das Dia­log­fens­ter “Neue For­ma­tie­rungs­re­gel”, wäh­len Sie hier “For­mel zur Ermitt­lung der zu for­ma­tie­ren­den Zel­len ver­wen­den” und geben dann im Feld “Werte for­ma­tie­ren, für die diese For­mel wahr ist:” (rot) die For­mel “=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)” ohne Anfüh­rungs­zei­chen ein. Kli­cken Sie hier­nach auf den Knopf For­ma­tie­ren (gelb).

Im Dia­log­fens­ter Zel­len for­ma­tie­ren wäh­len Sie für die Schrift ein Design-Weiß (grün) und für Aus­fül­len ein mitt­le­res Design-Blau (blau) aus. Nach­dem Sie alle Dia­loge mit OK geschlos­sen haben könnte Ihr Kalen­der wie abge­bil­det aussehen.

Nun for­ma­tie­ren Sie die Wochen­tage, diese sol­len nur mit einem Füll­mus­ter ver­se­hen wer­den, die Schrift­farbe bleibt unver­än­dert. Mar­kie­ren Sie bitte die Zel­len B3:B9, begin­nen Sie unbe­dingt mit Zelle B3!

Kli­cken Sie dann im Men­ü­band “Start”, Gruppe “For­mat­vor­la­gen” auf “Bedingte For­ma­tie­rung” und wäh­len “Neue Regel ..”.

Wäh­len Sie “For­mel zur Ermitt­lung der zu for­ma­tie­ren­den Zel­len ver­wen­den” und geben dann im Feld “Werte for­ma­tie­ren, für die diese For­mel wahr ist:” (rot) die For­mel “=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)” ohne Anfüh­rungs­zei­chen ein. Als Füll­mus­ter wäh­len Sie das glei­che mitt­lere Design-Blau wie bei den Tagen. Wenn alles funk­tio­niert hat, dann sieht Ihr Kalen­der nun so aus.

Nun For­ma­tie­ren Sie die Feiertage/KW. Die For­ma­tie­rungs­re­gel für die Fei­er­tage ken­nen Sie schon, es ist die schon für die Tage und Wochen­tage ver­wen­dete. Mar­kie­ren Sie die Zel­len C3:C9. Erzeu­gen Sie eine neue for­mel­ba­sierte bedingte For­ma­tie­rung und ver­wen­den Sie die fol­gende For­mel und wäh­len ein mitt­le­res Design-Blau als Füllmuster:

=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)

Jetzt müs­sen Sie die For­ma­tie­rung für die Kalen­der­wo­che fest­le­gen, hier kein Füll­mus­ter aber Schrift­farbe in mitt­le­ren Design-Blau.

Nach­dem Sie die erste For­ma­tie­rungs­re­gel defi­niert haben, sind noch immer die Zel­len C3:C9 mar­kiert. Kli­cken Sie auf “Bedingte For­ma­tie­rung” und wäh­len Sie nun “Regeln ver­wal­ten ..”. Im Dia­log­fens­ter “Mana­ger für Regeln zur beding­ten For­ma­tie­rung” wäh­len Sie bitte “Neue Regel ..”.

Der wei­tere Ablauf ist ana­log zu den schon durch­ge­führ­ten beding­ten For­ma­tie­run­gen. Wäh­len Sie eine for­mel­ba­sierte bedingte For­ma­tie­rung, wäh­len Sie als Schrift­farbe ein mitt­le­res Design-Blau. Geben Sie die fol­gende For­mel ein:

=UND(ISTFEHLER(FINDEN(A3;Feiertagsliste));WOCHENTAG(A3;2)=1)

Nach dem Bestä­ti­gen (meh­rere Male), soll­ten Sie die hell­blaue 1. KW sehen können.

Nun haben Sie es fast geschafft, Sie müs­sen nur noch die beding­ten For­ma­tie­run­gen für Tage, Wochen­tage und Feiertag/KW vom Januar auf den Februar über­tra­gen. Das Erle­di­gen Sie kin­der­leicht mit Format-Übertragen (-Pinsel).

Mar­kie­ren Sie hierzu die Zel­len A3:A9, also vom 01. bis 07. Januar, und kli­cken dann auf im Men­ü­band “Start”, Gruppe “Zwi­schen­ab­lage” auf  “Format-Übertragen”. Danach mar­kie­ren Sie den Bereich D3:D9 in einem Zug und die For­ma­tie­rung wird über­tra­gen. Das wie­der­ho­len Sie bitte für die Wochen­tage und die Feiertage/KW. Wenn alles funk­tio­niert hat, dann sollte Ihr Kalen­der wie im fol­gen­den Bild aussehen.

Das war der 3. Teil des Work­shops. Im 4. Teil wer­den Sie die rest­li­chen Monate anle­gen und die Zei­len nach unten aus­fül­len. Dann kommt noch das Ermit­teln der Arbeits­tage und das Ein­stel­len des Druck­be­rei­ches und Sie sind fer­tig mit Ihrem Kalender.

Viel Erfolg und Spaß beim Nachmachen.

Down­loads

1. Kalen­der in einer mög­li­chen fina­len Ver­sion für Excel 2010,
2. Kalen­der in einer mög­li­chen fina­len Ver­sion für Excel 2003,
3. Kalen­der Teil 1 für Excel 2010,
4. Kalen­der Teil 2 für Excel 2010 und
5. Kalen­der Teil 3 für Excel 2010.

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-Features der neue­ren Excel Ver­sio­nen vor­be­rei­tet sein. In die­sem 2. Teil des Work­shops pro­gram­mie­ren Sie die For­meln im Tabel­len­blatt mit dem Jah­res­ka­len­der und berei­ten das Tabel­len­blatt für die Benut­zung der Desi­gn­fea­tures des Office 2010 vor.

Sie kön­nen den Teil 1 des Work­shops “Work­shop: Kalen­der mit Fei­er­ta­gen und Kalen­der­wo­che selbst erstel­len Teil 1 — Grund­la­gen” nach­ar­bei­ten und Ihre eigene Kalen­der­da­tei erstel­len, oder Sie laden sich die Datei “Kalen­der Teil 1 für Excel 2010” her­un­ter und machen gleich mit Teil 2 des Work­shops weiter.

Die Datein des Work­shop Teil 2 fin­den Sie wie immer am Ende die­ses Textes.

Design mit Office 2010/2007

Wenn Sie mit Office ab 2007 die auto­ma­ti­sche Umschal­tung bei Aus­wahl eines ande­ren Design nut­zen möch­ten, dann ver­ab­schie­den Sie sich bitte von der manu­el­len Wahl von Stan­dard­far­ben oder Standardschriften.

Sie müs­sen sich bei der Farb– bzw. Font­wahl auf die Design­schrif­ten bzw. Desi­gnfar­ben beschrän­ken. Dann kann Excel, aber auch die ande­ren Office Anwen­dun­gen, bei Aus­wahl eines ande­ren Designs, die zuge­hö­ri­gen Schrif­ten und Far­ben aktivieren.

Stan­dard­mä­ßig ist in mei­nem Sys­tem das Design “Larissa” vor­ge­wählt, als Bei­spiel habe ich das Design “Aus­tin” gegen­über gestellt. Larissa ist ein blaues Thema, Aus­tin ein eher grü­nes Thema.

Bei “Larissa” wird für Über­schrif­ten die Design­schrift­art “Cam­bria” ein­ge­setzt und für Men­gen– oder Fließ­text die Design­schrift­art “Cali­bri”. Bei “Aus­tin” wer­den Über­schrif­ten und Men­gen­text mit “Cen­tury Gothic” gesetzt.

Wol­len Sie desi­gno­ri­en­tiert die Schrift aus­wäh­len, so ent­schei­den Sie sich nur noch für Über­schrift oder Men­gen­text. Akti­vie­ren Sie ein neues Design, dann wer­den die zuge­hö­ri­gen Design­schrif­ten aktiviert.

Wäh­len Sie bei Far­ben auch nur die Desi­gnfar­ben, diese sind je nach Design har­mo­nisch um eine Grund­farbe herum grup­piert. Prü­fen Sie ggf. ob das gewählte Design hin­sicht­lich sei­ner Grau­um­set­zung auf Schwarz/Weiß Dru­ckern erkenn­bar ist. Die neuen Designs ori­en­tie­ren auf Aus­dru­cke mit Farbdruckern.

Die Designs in Office 2010/2007 gestat­ten Ihnen ein schnel­les Umschal­ten zwi­schen ver­schie­de­nen Vari­an­ten bei der Gestal­tung Ihres Doku­men­tes. Wei­tere Designs zum Her­un­ter­la­den fin­den Sie bei Micro­soft Office.com unter Office-Designs. I.d.R. sind die Designs für alle Office-Anwendungen einsetzbar.

Pro­bie­ren Sie ruhig mal ein neues Design aus, durch über­fah­ren wird das neue Design mit­tels der Live­vor­schau ange­zeigt, aber erst durch bewuss­tes Ankli­cken für Ihr Doku­ment gewählt.

Kalen­der programmieren

Nun geht es mit unse­rem Kalen­der in die zweite Runde. Wir müs­sen nur einen klei­nen Teil hän­disch pro­gram­mie­ren, der Rest wird durch Aus­fül­len über­tra­gen. Wir pro­gram­mie­ren die ers­ten 2 Tage im Januar und Februar.

Als ers­tes For­ma­tie­ren wir die Über­schrift in Zelle A1, dort steht z.Z. nur die Zahl 2011, wir möch­ten dort jedoch “Kalen­der für das Jahr 2011” ste­hen haben und wir kön­nen kei­nen Text hin­ein­schrei­ben, da unser Kalen­der das Kalen­der­jahr aus der Zelle A1 bezieht.

Sie for­ma­tie­ren die Zelle benut­zer­de­fi­niert. Dazu kli­cken Sie mit der rech­ten Maus­taste auf die Zelle A1 und wäh­len “Zel­len for­ma­tie­ren”. Wäh­len Sie nun das Regis­ter­blatt “Zah­len” und dort die Kate­go­rie “Benut­zer­de­fi­niert”. Unter Typ tra­gen Sie inkl. Anfüh­rungs­zei­chen “Kalen­der für das Jahr” 0 ein. Der Text in Anfüh­rungs­zei­chen wird als Text inter­pre­tiert, das fol­gende Leer­zei­chen ist für den abstand zustän­dig und die Null ist ein Platz­hal­ter für eine ganze Zahl ohne Nach­kom­ma­stelle. Möch­ten Sie zwei Kom­ma­stel­len, so wür­den Sie 0,00 ein­ge­ben. Sehr schön kön­nen Sie die Aus­wir­kung Ihrer Ein­gabe im Feld Bei­spiel erken­nen. Sollte dort ggf. Unfug ste­hen, dann haben sie vie­leicht ein Anfüh­rungs­zei­chen ver­ges­sen. Kli­cken sie nach der Ein­gabe auf Ok.

Bei Ihnen steht nicht die gewünschte Über­schrift son­dern #######, kein Pro­blem. Der Platz im Feld A1 ist zu schmal um den gesam­ten Text anzu­zei­gen, Abhilfe könnte sein die Zel­len A1 bis AJ1 zu ver­knüp­fen, so breit wird unser Kalen­der werden.

Für jeden Monat wer­den Sie drei Spal­ten benötigen:

1. Spalte: Der Tag 1 .. 31
2. Spalte: Der Wochen­tag Mo .. So
3. Spalte: Der Fei­er­tag oder ggf. die Kalenderwoche

Über die­sen 3 Spal­ten befin­det sich der Monat: Januar .. Dezember.

Ver­bin­den Sie die Zel­len A1 bis C1 und ord­nen Sie den Inhalt links­bün­dig an. Schrei­ben Sie nun in Zelle A1 =Datum(Jahr;1;1), das bewirkt den 01.01. des Jah­res, hier 2011. Noch schnell das Datum so for­ma­tie­ren, dass nur der Monat dort steht. Rechte Maus auf Zelle kli­cken und “Zel­len for­ma­tie­ren” wäh­len, Regis­ter­karte “Zah­len”, Kate­go­rie “Benut­zer­de­fi­niert”, Typ MMMM.

Für die For­ma­tie­rung des Mona­tes gibt es 4 Möglichkeiten:

• M: in Mona­ten von Januar bis Sep­tem­ber steht nur 1. bis 9., ab Okto­ber steht 10.,
• MM: in Mona­ten von Januar bis Sep­tem­ber gibt es eine füh­rende Null,
• MMM: der Monat wird in Text­schreib­weise mit drei Zei­chen abge­kürzt, also bspw. Jan,
• MMMM: der Monat wird aus­ge­schrie­ben, bspw. Januar.

Schrei­ben Sie nun in die Zelle A3 ein Gleich­heits­zei­chen und kli­cken Sie die Zelle A2 an, alter­na­tiv könn­ten Sie auch hän­disch =A2 hin­ein­schrei­ben, aber die Gefahr des Ver­tip­pen besteht und könnte zu uner­wünsch­ten Ergeb­nis­sen füh­ren. For­ma­tie­ren Sie die Zelle A3 benut­zer­de­fi­niert mit der Zei­chen­folge TT.

Für die For­ma­tie­rung des Tages gibt es eben­falls 4 Möglichkeiten:

• T: stellt den Tag ohne füh­rende Null dar,
• TT: an Tagen von 01. bis 09. gibt es eine füh­rende Null,
• TTT: der Tag wird in Text­schreib­weise mit zwei Zei­chen abge­kürzt, also bspw. Mo, Di, Mi,
• TTTT: der Monat wird aus­ge­schrie­ben, bspw. Mon­tag, Diens­tag, Mittwoch.

In die Zelle B3 schrei­ben Sie wie­derum ein Gleich­heits­zei­chen und kli­cken dann auf die Zelle A3, hier möch­ten wir den Wochen­tag, also Mo .. So ste­hen haben. Wäh­len Sie die benut­zer­de­fi­nierte For­ma­tie­rung mit TTT.

Jetzt wird es inter­es­sant, Sie wol­len in Zelle C3:

• an Fei­er­ta­gen den Fei­er­tag eintragen
• an Mon­ta­gen, die kein Fei­er­tag sind, soll die Kalen­der­wo­che stehen,
• in allen ande­ren Fäl­len soll nichts ein­ge­tra­gen werden.

Tra­gen Sie fol­gen­den “Code” ein.

=WENN(ISTFEHLER(FINDEN(A3;Feiertagsliste));
WENN(WOCHENTAG(A3;2)=1;KÜRZEN((A3-WOCHENTAG(A3;2)-DATUM(JAHR(A3+4-WOCHENTAG(A3;2));1;-10))/7); "" );
SVERWEIS(A3;Berechnen_der_Feiertage;2;WAHR)
)

Was macht nun diese Code-Zeile?

Es wird abge­fragt ob der Tag in Zelle A3 kein Fei­er­tag ist, dann wird sofern der Wochen­tag ein Mon­tag ist die Kalen­der­wo­che aus­ge­ge­ben, ansons­ten wird der Fei­er­tag ein­ge­tra­gen. Ganz ein­fach der Reihe nach.

WENN(Prüfung; [Dann_Wert]; [Sonst_Wert])

Die Wenn Anwei­sung soll­ten Sie ken­nen, WENN(Prüfung; [Dann_Wert]; [Sonst_Wert]), d.h ist die Prü­fung wahr, dann wird der Dann_Wert aus­ge­führt, sonst der Sonst_Wert. Der Sonst_Wert ist optio­nal, wenn Sie ihn nicht fest­le­gen, dann wird ggf. “FALSCH” ausgegeben.

FINDEN(A3;Feiertagsliste)

Diese Funk­tion sucht das Datum in Zelle A3 in der Fei­er­tags­liste, wenn es nicht in der Liste steht, gibt es eine Feh­ler­mel­dung, die Sie mit der nächs­ten Funk­tion abfan­gen können.

ISTFEHLER(Wert)

Wert kann eine Funk­tion sein, die einen Feh­ler erzeugt, bspw. ISTFEHLER(5/0) lie­fert Wahr, da eine Divi­sion durch Null einen Feh­ler her­vor­ruft. Sie benut­zen es um den Feh­ler der Fin­den Funk­tion abzufangen.

WENN(WOCHENTAG(A3;2)=1;KÜRZEN((A3-WOCHENTAG(A3;2)-DATUM(JAHR(A3+4-WOCHENTAG(A3;2));1;-10))/7); “” )

Ist das Datum in Zelle A3 ein Mon­tag, dann wird die Kalen­der­wo­che als Zahl ein­ge­tra­gen, sonst wird nichts, also “”, eine leere Zei­chen­kette eingetragen.

WOCHENTAG(A3;2)=1

lie­fert WAHR, wenn der Tag in Zelle A3 ein Mon­tag ist, also den Wert 1 hat. Mon­tag = 1, Sonn­tag = 7.

KÜRZEN((A3-WOCHENTAG(A3;2)-DATUM(JAHR(A3+4-WOCHENTAG(A3;2));1;-10))/7)

Lei­der ist die For­mel für die Kalen­der­wo­che in Excel die ame­ri­ka­ni­sche Berech­nungs­va­ri­ante, die meist einen ande­ren Wert lie­fert als die KW nach DIN. Das ist die kor­ri­gierte Berech­nung nach DIN.

SVERWEIS(A3;Berechnen_der_Feiertage;2;WAHR))

Gibt aus der Liste “Berechnen_der_Feiertage” den Namen des Fei­er­ta­ges aus Spalte 2 zurück. Feh­ler brau­chen nicht abge­fan­gen wer­den, da die Funk­tion nur auf­ge­ru­fen wird, wenn ein Fei­er­tag vorliegt.

Wenn ich der­ar­tige For­meln auf­baue, dann mache ich das schritt­weise und baue sie aus den Ein­zel­for­meln zusammen.

Bei Ihnen sollte für den ers­ten Januar der Fei­er­tag Neu­jahr erscheinen.

Jetzt wird es wie­der ein wenig ein­fa­cher. Schrei­ben Sie in Zelle A4 ein Gleich­heits­zei­chen, kli­cken auf die Zelle A3 und geben dann noch ein Plus­zei­chen gefolgt von einer 1 ein. D.h. Sie addie­ren zum 1. Januar einen Tag hinzu und erhal­ten den 2. Januar. Hier als 02 erkennbar.

Mar­kie­ren Sei nun die Zel­len B3 bis C3, bewe­gen den Cur­sor an die rechte untere Ecke und war­ten bis aus dem fet­ten wei­ßen Plus ein weni­ger fet­tes schwar­zes Plus wird. Dann hal­ten Sie die linke Maus­taste gedrückt und zie­hen den Cur­sor bis zur Zeile 4. In der Zelle B4 sollte “So” erschei­nen, die Zelle C scheint leer. Ein sich zei­gen­der Smart­tag würde ihnen eine Aus­wahl bie­ten hin­sicht­lich “Zel­len Kopie­ren”, “Nur For­mate aus­fül­len” oder “Ohne For­ma­tie­rung aus­fül­len”. Der Stan­dard­wert “Zel­len Kopie­ren” ist für die­sen Fall die rich­tige Wahl.

Als Schrift für die gesamte Tabelle habe ich die Design­schrift “Cali­bri” ver­wen­det. Die Über­schrift hat eine Größe von 36pt, der Monat, die Tage und der abge­kürzte Wochen­tag ist mit 22pt for­ma­tiert und die Fei­er­tage haben eine Schrift­größe von 11pt. Die Breite der Spalte A und B habe ich an den Inhalt ange­passt, hier 50pt und die Spalte C habe ich so breit gemacht, dass der Fei­er­tag “Tag der deut­schen Ein­heit” hin­ein­pas­sen würde. Wird spä­ter noch­mals angepasst.

Dann habe ich die Zel­len A2 bis C4 mar­kiert und den Rah­men (Zel­len for­ma­tie­ren, Regis­ter Rah­men) mit einer fet­ten Linie in einer hel­len Desi­gnfarbe außen und innen nur waa­ge­recht for­ma­tiert. Sonst würde Excel zwi­schen den Fel­dern für den Tag, den Wochen­tag und den Feiertag/KW eben­falls eine Linie zie­hen, das möchte ich aber nicht.

Nun legen wir das Feld für den Februar in der Über­schrift an. Dazu mar­kie­ren Sie den Januar bege­ben sich mit dem Cur­sor an den rech­ten unte­ren Rand der Zelle, bis aus dem fet­ten wei­ßen Plus ein weni­ger fet­tes Plus wird, und zie­hen bei gedrück­ter lin­ker Maus­taste um drei Spal­ten nach rechts. Dabei wird auto­ma­tisch die For­mel aus Zelle A2 in die Zelle D2 über­tra­gen, diese ent­steht durch ver­bin­den der Zel­len D2 bis F2. Noch steht feh­ler­haft dort die For­mel “=DATUM(Jahr;1;1)” die Sie durch “=DATUM(Jahr;MONAT(A2)+1;1)” erset­zen. Nun sollte nach Bestä­ti­gen mit der <ENTER>-Taste dort Februar ste­hen. Sie berech­nen aus dem Datum im Feld Januar ein neues Datum in dem Sie zum Monat einen Monat hinzufügen.

Nun wer­den Sie die For­meln in den Fel­dern A3 bis C4 in die ent­spre­chen­den Fel­der des Monats Februar kopie­ren. Mar­kie­ren Sie dazu zuerst den Bereich D3 bis D4, hal­ten die <STRG>-Taste gedrückt und wäh­len nun den Bereich A3 bis A4 aus. Hier­nach kli­cken Sie im Men­ü­band Start, Gruppe Bear­bei­ten auf Füll­be­reich und wäh­len “Rechts”. Nun soll­ten die For­meln und For­mate in die Zel­len D3 bis D4 über­nom­men wor­den sein. Ver­fah­ren Sie bitte genauso für die Bereiche

• B3 bis B4 und E3 bis E4 und
• C3 bis C4 und F3 bis F4.

Sie erhal­ten eine Tabelle wie auf dem letz­ten Bild dargestellt.

In die­sem Work­shop haben Sie die For­meln für den Kalen­der pro­gram­mie­ret. Jetzt fehlt nur noch die bedingte For­ma­tie­rung um die Her­vor­he­bun­gen für Fei­er­tage und Wochen­en­den her­vor zuneh­men. Wie das geht, erfah­ren Sie im nächs­ten Teil des Workshops.

Down­loads

1. Kalen­der in einer mög­li­chen fina­len Ver­sion für Excel 2010,
2. Kalen­der in einer mög­li­chen fina­len Ver­sion für Excel 2003
3. Kalen­der Teil 1 für Excel 2010 und
4. Kalen­der Teil 2 für Excel 2010.

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­ar­tige Kalen­der lie­gen zwar am Ende eines Jah­res oft der Tages­zei­tung bei, aber ich möchte ihn als elek­tro­ni­sche Lösung haben. Der Kalen­der soll für die Nut­zung der Design-Features der neue­ren Excel Ver­sio­nen vor­be­rei­tet sein. Der vor­ge­stellte Kalen­der ist ab Excel 2002 lauf­fä­hig, ein­zig für die Berech­nung der Net­to­ar­beits­tage muss eine spe­zi­elle Funk­tion ein­ge­bun­den wer­den, dazu jedoch erst im Teil 3.

Viele “ewige” Kalen­der, die Sie im Netz fin­den, kön­nen Sie frei her­un­ter­la­den, aber sel­ten fin­den Sie eine Anlei­tung wie es gemacht wird. Diese Lücke möchte ich schlie­ßen. Natür­lich stelle ich am Ende den mit Excel erstell­ten Kalen­der zum Down­load zur Verfügung.

In die­sem 1. Teil des Work­shops ler­nen Sie die Vor­ge­hens­weise zur “Berech­nung” der Feiertage.

Zur mathe­ma­ti­schen Umset­zung: Es gibt fest­ste­hende Fei­er­tage, bspw. Neu­jahr am 1. Januar und glei­tende Fei­er­tage wie bspw. Ostern. Alle ver­schieb­li­chen Fei­er­ta­gen sind von fol­gen­den 2 Fei­er­ta­gen abhän­gig bzw. aus die­sen berechenbar:

• Oster­sonn­tag und der
• Vierte Advent.

Mathe­ma­tik

Zuerst müs­sen Sie eine Liste der in Ihrem Bun­des­land gel­ten­den gesetz­li­chen Fei­er­tage zusam­men­stel­len und die For­meln ermitteln.

Ich habe die in allen Regio­nen Thü­rin­gens gül­ti­gen Fei­er­tage auf­ge­lis­tet. Soll­ten Sie wei­tere benö­ti­gen, so emp­fehle ich von www.oeftg.de die Abhand­lung zur Berech­nung gesetz­li­cher Fei­er­tage. Auf der Seite gibt es ein PDF zum Down­load in dem wei­tere Abhän­gig­kei­ten erklärt sind.

Berech­nung der gesetz­li­chen Fei­er­tage für Thüringen

Nun öffnen Sie bitte eine neue Arbeits­mappe mit zwei Tabel­len, die erste nen­nen Sie “Kalen­der”, die zweite “Berech­nung”. Die Tabelle “Berech­nun­gen” wer­den Sie spä­ter verstecken.

In die Zelle A1 der Tabelle “Kalen­der” tra­gen Sie bitte das Jahr 2011 ein. Dann bege­ben Sie sich in das Namen­feld und tra­gen dort anstelle “A1” den Feld­na­men “Jahr” ein und bestä­ti­gen Ihre Ein­gabe mit <ENTER>. Sie haben für die Zelle “A1” den Bereichs­na­men “Jahr” defi­niert und kön­nen die­sen dann in For­meln als Varia­ble benutzen.

Nun fügen Sie im Tabel­len­blatt “Berech­nung” die Über­schrift “Berech­nun­gen für den Kalen­der” in die Zelle A1, “Berechnen_der_Feiertage” in die Zelle A3, “Fei­er­tage” in die Zelle B3 und “Fei­er­tags­liste” in die Zelle A18. Das sind nur Über­schrif­ten die zur Bezeich­nung wei­te­rer zur Berech­nung not­wen­di­ger Berei­che dienen.

Fügen Sie nun vor allen ein­ge­füg­ten For­meln ein “=” ein und bestä­ti­gen Sie Ihre Ein­gabe mit <ENTER>.

Nach­dem Sie alle “=” ein­ge­tra­gen haben, wer­den Sie fest­stel­len, dass in allen Zel­len, die Fei­er­tage ent­hal­ten, die mit Hilfe des Datums für den Oster­sonn­tag berech­net wer­den, Feh­ler­mel­dun­gen “#NAME?” erschei­nen. Das ist soweit kor­rekt, denn Sie haben noch keine Bereichs­de­fi­ni­tion für “Oster­sonn­tag” durchgeführt.

Kli­cken Sie  nun in das Feld in dem das Datum für den Oster­sonn­tag ste­hen sollte, hier steht die Zahl “40657”. Das ist kor­rekt, da Excel noch nicht weis, dass das ein Datum ist. Ver­ge­ben Sie für die Zelle B6 den Bereich­sa­men “Ostersonntag”.

Nach dem Bestä­ti­gen mit der <ENTER> Taste soll­ten in den For­meln alle Feh­ler­mel­dun­gen ver­schwun­den sein. Soll­ten bei Ihnen in der Spalte B nicht nur Datums­an­ga­ben ste­hen, son­dern auch Zah­len, dann mar­kie­ren Sie bitte alle Datums­werte und for­ma­tie­ren Sie die Zel­len mit einem Datumsformat.

Nun kom­men Sie zu einer etwas auf­wen­di­gen For­mel. Geben Sie bitte in Zelle A19 fol­gende For­mel ein:

=VERKETTEN(B4;" ";B5;" ";Ostersonntag;" ";B7;" ";B8;" ";B9;" ";B10;" ";B11;" ";B12;" ";B13;" ";B14;" ";B15;" ";B16)

Hier­mit erzeu­gen Sie eine Liste aller zuvor berech­ne­ten Fei­er­tage, die durch Leer­zei­chen getrennt sind. Ich ver­wende dazu die Funk­tion Ver­ket­ten. Ach­ten Sie bitte dar­auf, keine Zelle dop­pelt anzu­kli­cken und auch keine Zelle zu ver­ges­sen. Schlie­ßen Sie am Ende die Ver­ket­ten Funk­tion mit einer Klam­mer. Ist ein typi­scher Anfän­ger Feh­ler den Excel meist auto­ma­tisch kor­ri­gie­ren kann, aber rummeckert.

Mar­kie­ren Sie die Zel­len A19:F19 und ver­bin­den Sie diese, der Schön­heit wegen kön­nen Sie auch einen Rah­men darum erstellen.

Lei­der müs­sen Sie noch eine kleine Umge­stal­tung der Posi­tion der Daten vor­neh­men. Für die Liste “Berechnen_der_Feiertage” müs­sen die Datums­an­ga­ben zwin­gend in der ers­ten Spalte und die Bezeich­ner der Fei­er­tage in der zwei­ten Spalte ange­ord­net sein. Bitte rea­li­sie­ren Sie das, indem Sie zuerst die Bezeich­ner nach rechts in die Spalte C ver­schie­ben und dann den gesam­ten Bereich wie­der nach links schieben.

Nun müs­sen Se wei­tere Bereichs­de­fi­ni­tio­nen vor­neh­men, geht in einem jung­fräu­li­chen Excel Blatt am ein­fachs­ten durch Mar­kie­ren des Berei­ches, Ein­ge­ben des Bereichs­na­mens im Namen­feld und anschlie­ßen­dem Bestä­ti­gen mit der <ENTER> Taste. Fol­gende Bereichs­na­men müs­sen Sie definieren:

• Berechnen_der_Feiertage (1), rot
• Fei­er­tage (2), grün
• Fei­er­tags­liste (3), blau

Jahr und Oster­sonn­tag haben Sie schon defi­niert. Sie kön­nen gut erken­nen, dass auch über­lap­pende Berei­che mög­lich sind. Bitte kon­trol­lie­ren Sie die Bereichs­na­men mit Hilfe des Namens-Managers, Men­ü­band “For­meln” – Gruppe “defi­nierte Namen” – “Namens-Manager”.

Haben Sie sich ver­tan, dann kön­nen Sie feh­ler­hafte Defi­ni­tio­nen mit dem Namens-Manager löschen oder bearbeiten.

So das war es im 1. Teil die­ses Work­shops. In Teil 2 wer­den Sie den Kalen­der “programmieren”.

Soll­ten Sie Fra­gen haben, dann mel­den Sie sich bitte in einem Kom­men­tar oder per PM. Natür­lich dür­fen Sie auch einen Kom­men­tar schrei­ben, wenn  Sie den Work­shop toll finden.

Viel Erfolg beim Nach­voll­zie­hen des Workshops.

Down­loads

1. Kalen­der in einer mög­li­chen fina­len Ver­sion für Excel 2010,
2. Kalen­der in einer mög­li­chen fina­len Ver­sion für Excel 2003 und
3. Kalen­der Teil 1 für Excel 2010.