Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 2 – Programmierung

Kalender

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 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 Design­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 eige­ne 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 wei­ter.

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

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 bit­te von der manu­el­len Wahl von Stan­dard­far­ben oder Stan­dard­schrif­ten.

Sie müs­sen sich bei der Farb- bzw. Font­wahl auf die Design­schrif­ten bzw. Design­far­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 akti­vie­ren.

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

Design “Larissa” und Design “Austin”

Bei “Laris­sa” 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­tu­ry Gothic” gesetzt.

Wol­len Sie desi­gnori­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 neu­es Design, dann wer­den die zuge­hö­ri­gen Design­schrif­ten akti­viert.

Design-Schriften für Design “Larissa” und “Austin” Design-Farben für Design “Larissa” und “Austin”

Wäh­len Sie bei Far­ben auch nur die Design­far­ben, die­se sind je nach Design har­mo­nisch um eine Grund­far­be her­um grup­piert. Prü­fen Sie ggf. ob das gewähl­te Design hin­sicht­lich sei­ner Grau­um­set­zung auf Schwarz/Weiß Dru­ckern erkenn­bar ist. Die neu­en Designs ori­en­tie­ren auf Aus­dru­cke mit Farb­dru­ckern.

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­te­re 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-Anwen­dun­gen ein­setz­bar.

Pro­bie­ren Sie ruhig mal ein neu­es 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.

Kalender programmieren

Nun geht es mit unse­rem Kalen­der in die zwei­te Run­de. 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 Janu­ar und Febru­ar.

Als ers­tes For­ma­tie­ren wir die Über­schrift in Zel­le 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 Zel­le A1 bezieht.

Sie for­ma­tie­ren die Zel­le benut­zer­de­fi­niert. Dazu kli­cken Sie mit der rech­ten Maus­tas­te auf die Zel­le 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­gen­de Leer­zei­chen ist für den abstand zustän­dig und die Null ist ein Platz­hal­ter für eine gan­ze Zahl ohne Nach­kom­ma­stel­le. 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­ga­be im Feld Bei­spiel erken­nen. Soll­te 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­ga­be auf Ok.

Benutzerdefinierte Formatierung: “Kalender für das Jahr” 0

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

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

  1. Spal­te: Der Tag 1 .. 31
  2. Spal­te: Der Wochen­tag Mo .. So
  3. Spal­te: Der Fei­er­tag oder ggf. die Kalen­der­wo­che

Über die­sen 3 Spal­ten befin­det sich der Monat: Janu­ar .. Dezem­ber.

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 Zel­le 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. Rech­te Maus auf Zel­le kli­cken und “Zel­len for­ma­tie­ren” wäh­len, Regis­ter­kar­te “Zah­len”, Kate­go­rie “Benut­zer­de­fi­niert”, Typ MMMM.

A1 =Datum(Jahr;1;1) Registerkarte “Zahlen”, Kategorie “Benutzerdefiniert”, Typ MMMM

Für die For­ma­tie­rung des Mona­tes gibt es 4 Mög­lich­kei­ten:

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

Schrei­ben Sie nun in die Zel­le A3 ein Gleich­heits­zei­chen und kli­cken Sie die Zel­le 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önn­te zu uner­wünsch­ten Ergeb­nis­sen füh­ren. For­ma­tie­ren Sie die Zel­le A3 benut­zer­de­fi­niert mit der Zei­chen­fol­ge TT.

A3=A2 Formatieren Sie die Zelle A3 benutzerdefiniert mit der Zeichenfolge TT

Für die For­ma­tie­rung des Tages gibt es eben­falls 4 Mög­lich­kei­ten:

  • T: stellt den Tag ohne füh­ren­de Null dar,
  • TT: an Tagen von 01. bis 09. gibt es eine füh­ren­de Null,
  • TTT: der Tag wird in Text­schreib­wei­se 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, Mitt­woch.

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

B3=A3 Wählen Sie die benutzerdefinierte Formatierung mit TTT.

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

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

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)
)
"Code"-Eingabe in Zelle C3
Was macht nun diese Code-Zeile?

Es wird abge­fragt ob der Tag in Zel­le 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 Rei­he 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” aus­ge­ge­ben.

FINDEN(A3;Feiertagsliste)

Die­se Funk­ti­on sucht das Datum in Zel­le A3 in der Fei­er­tags­lis­te, wenn es nicht in der Lis­te steht, gibt es eine Feh­ler­mel­dung, die Sie mit der nächs­ten Funk­ti­on abfan­gen kön­nen.

ISTFEHLER(Wert)

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

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 Zel­le A3 ein Mon­tag, dann wird die Kalen­der­wo­che als Zahl ein­ge­tra­gen, sonst wird nichts, also “”, eine lee­re Zei­chen­ket­te ein­ge­tra­gen.

WOCHENTAG(A3;2)=1

lie­fert WAHR, wenn der Tag in Zel­le 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­an­te, die meist einen ande­ren Wert lie­fert als die KW nach DIN. Das ist die kor­ri­gier­te Berech­nung nach DIN.

SVERWEIS(A3;Berechnen_der_Feiertage;2;WAHR))

Gibt aus der Lis­te “Berechnen_der_Feiertage” den Namen des Fei­er­ta­ges aus Spal­te 2 zurück. Feh­ler brau­chen nicht abge­fan­gen wer­den, da die Funk­ti­on nur auf­ge­ru­fen wird, wenn ein Fei­er­tag vor­liegt.

Wenn ich der­ar­ti­ge For­meln auf­baue, dann mache ich das schritt­wei­se und baue sie aus den Ein­zel­for­meln zusam­men.

Bei Ihnen soll­te für den ers­ten Janu­ar der Fei­er­tag Neu­jahr erschei­nen.

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

Sie addieren zum 1. Januar einen Tag hinzu und erhalten 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 rech­te unte­re 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 lin­ke Maus­tas­te gedrückt und zie­hen den Cur­sor bis zur Zei­le 4. In der Zel­le B4 soll­te “So” erschei­nen, die Zel­le C scheint leer. Ein sich zei­gen­der Smart­tag wür­de ihnen eine Aus­wahl bie­ten hin­sicht­lich “Zel­len Kopie­ren”, “Nur For­ma­te 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­ti­ge Wahl.

Nach Unten Asufüllen Smarttag: Der Standardwert “Zellen Kopieren” ist für diesen Fall die richtige Wahl.

Als Schrift für die gesam­te Tabel­le 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ürz­te Wochen­tag ist mit 22pt for­ma­tiert und die Fei­er­ta­ge haben eine Schrift­grö­ße von 11pt. Die Brei­te der Spal­te A und B habe ich an den Inhalt ange­passt, hier 50pt und die Spal­te C habe ich so breit gemacht, dass der Fei­er­tag “Tag der deut­schen Ein­heit” hin­ein­pas­sen wür­de. Wird spä­ter noch­mals ange­passt.

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 Design­far­be außen und innen nur waa­ge­recht for­ma­tiert. Sonst wür­de 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öch­te ich aber nicht.

Schriftart und Schriftgrößen Rahmengestaltung

Nun legen wir das Feld für den Febru­ar in der Über­schrift an. Dazu mar­kie­ren Sie den Janu­ar bege­ben sich mit dem Cur­sor an den rech­ten unte­ren Rand der Zel­le, 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­tas­te um drei Spal­ten nach rechts. Dabei wird auto­ma­tisch die For­mel aus Zel­le A2 in die Zel­le D2 über­tra­gen, die­se 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 soll­te nach Bestä­ti­gen mit der <ENTER>-Taste dort Febru­ar ste­hen. Sie berech­nen aus dem Datum im Feld Janu­ar ein neu­es Datum in dem Sie zum Monat einen Monat hin­zu­fü­gen.

Nach Rechts ausfüllen Formel “=DATUM(Jahr;1;1)” die Sie durch “=DATUM(Jahr;MONAT(A2)+1;1)” ersetzen.

Nun wer­den Sie die For­meln in den Fel­dern A3 bis C4 in die ent­spre­chen­den Fel­der des Monats Febru­ar 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, Grup­pe Bear­bei­ten auf Füll­be­reich und wäh­len “Rechts”. Nun soll­ten die For­meln und For­ma­te in die Zel­len D3 bis D4 über­nom­men wor­den sein. Ver­fah­ren Sie bit­te genau­so für die Berei­che

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

Sie erhal­ten eine Tabel­le wie auf dem letz­ten Bild dar­ge­stellt.

Bereiche markieren und nach Rechts aufüllen Alle Felder für Tag, Wochentag und Feiertag/KW ausgefüllt

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

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­si­on für Excel 2003
  3. Kalen­der Teil 1 für Excel 2010 und
  4. Kalen­der Teil 2 für Excel 2010.

Kommentare sind geschlossen.