Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 1 — Grundlagen

Ziel die­ses Work­shops ist es einen Kalen­der zu erstel­len, der nur durch Ein­ga­be 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­ta­ge mar­kiert und die Anzahl der Arbeits­ta­ge ange­zeigt wer­den. Der­ar­ti­ge Kalen­der lie­gen zwar am Ende eines Jah­res oft der Tages­zei­tung bei, aber ich möch­te ihn als elek­tro­ni­sche Lösung haben. 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. Der vor­ge­stell­te Kalen­der ist ab Excel 2002 lauf­fä­hig, ein­zig für die Berech­nung der Net­to­ar­beits­ta­ge muss eine spe­zi­el­le Funk­ti­on ein­ge­bun­den wer­den, dazu jedoch erst im Teil 3.

Vie­le “ewi­ge” 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. Die­se Lücke möch­te ich schlie­ßen. Natür­lich stel­le 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­wei­se zur “Berech­nung” der Feiertage.

Zur mathe­ma­ti­schen Umset­zung: Es gibt fest­ste­hen­de Fei­er­ta­ge, bspw. Neu­jahr am 1. Janu­ar und glei­ten­de Fei­er­ta­ge 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
• Vier­te Advent.

Mathematik

Zuerst müs­sen Sie eine Lis­te der in Ihrem Bun­des­land gel­ten­den gesetz­li­chen Fei­er­ta­ge 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­ta­ge auf­ge­lis­tet. Soll­ten Sie wei­te­re benö­ti­gen, so emp­feh­le ich von www.oeftg.de die Abhand­lung zur Berech­nung gesetz­li­cher Fei­er­ta­ge. Auf der Sei­te gibt es ein PDF zum Down­load in dem wei­te­re Abhän­gig­kei­ten erklärt sind.

Berechnung der gesetzlichen Feiertage für Thüringen

Nun öff­nen Sie bit­te eine neue Arbeits­map­pe mit zwei Tabel­len, die ers­te nen­nen Sie “Kalen­der”, die zwei­te “Berech­nung”. Die Tabel­le “Berech­nun­gen” wer­den Sie spä­ter verstecken.

In die Zel­le A1 der Tabel­le “Kalen­der” tra­gen Sie bit­te das Jahr 2011 ein. Dann bege­ben Sie sich in das Namen­feld und tra­gen dort anstel­le “A1” den Feld­na­men “Jahr” ein und bestä­ti­gen Ihre Ein­ga­be mit <ENTER>. Sie haben für die Zel­le “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 Zel­le A1, “Berechnen_der_Feiertage” in die Zel­le A3, “Fei­er­ta­ge” in die Zel­le B3 und “Fei­er­tags­lis­te” in die Zel­le 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­ga­be 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­ta­ge ent­hal­ten, die mit Hil­fe 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 kei­ne Bereichs­de­fi­ni­ti­on 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 soll­te, 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 Zel­le B6 den Bereich­sa­men “Oster­sonn­tag”.

Nach dem Bestä­ti­gen mit der <ENTER> Tas­te soll­ten in den For­meln alle Feh­ler­mel­dun­gen ver­schwun­den sein. Soll­ten bei Ihnen in der Spal­te B nicht nur Datums­an­ga­ben ste­hen, son­dern auch Zah­len, dann mar­kie­ren Sie bit­te alle Datums­wer­te 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 bit­te in Zel­le A19 fol­gen­de For­mel ein:

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

Hier­mit erzeu­gen Sie eine Lis­te aller zuvor berech­ne­ten Fei­er­ta­ge, die durch Leer­zei­chen getrennt sind. Ich ver­wen­de dazu die Funk­ti­on Ver­ket­ten. Ach­ten Sie bit­te dar­auf, kei­ne Zel­le dop­pelt anzu­kli­cken und auch kei­ne Zel­le zu ver­ges­sen. Schlie­ßen Sie am Ende die Ver­ket­ten Funk­ti­on 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 die­se, der Schön­heit wegen kön­nen Sie auch einen Rah­men dar­um erstellen.

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

Nun müs­sen Se wei­te­re 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> Tas­te. Fol­gen­de Bereichs­na­men müs­sen Sie definieren:

• Berechnen_der_Feiertage (1), rot
• Fei­er­ta­ge (2), grün
• Fei­er­tags­lis­te (3), blau

Jahr und Oster­sonn­tag haben Sie schon defi­niert. Sie kön­nen gut erken­nen, dass auch über­lap­pen­de Berei­che mög­lich sind. Bit­te kon­trol­lie­ren Sie die Bereichs­na­men mit Hil­fe des Namens-Mana­gers, Menü­band “For­meln” – Grup­pe “defi­nier­te Namen” – “Namens-Mana­ger”.

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

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

Soll­ten Sie Fra­gen haben, dann mel­den Sie sich bit­te 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.

Downloads

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