Workshop: Kalender mit Feiertagen und Kalenderwoche selbst erstellen Teil 5 – Finale

kalender5Ziel 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 lernen Sie wie Sie Tabellenblätter verstecken und den Kalender für Excel 97-2003 aufbereiten.

Sie könnten die ersten vier Teile der Workshop-Reihe:

nacharbeiten und Ihre eigene Kalenderdatei erstellen. Oder Sie laden sich eine der Dateien herunter und starten mit dem Workshop Ihrer Wahl:

Die Dateien zum Workshop finden Sie wie immer am Ende dieses Blog Post. Für die vergrößerte Darstellung eines Bildes klicken Sie bitte auf das Bild.

Tabelle Berechnung verstecken

Ich versehe Excel Arbeitsmappen, die ich für ein Problem erarbeitet habe, häufig mit einer Kommentarseite, für den Fall, dass ich die Tabelle ggf. nochmals anpassen muss. Meist werden die Menschen, für die ich die Excel Tabelle erarbeitet habe, durch die Kommentare und Berechnungen nur verwirrt. Also muss ich diese verstecken.

Öffnen Sie Ihr Excel Tabelle und rufen Sie mit <ALT><F11> den Visual Basic Editor auf, funktioniert mit jeder Excel Version. Sicher können Sie mit diesem Editor noch mehr anstellen, wir wollen aber nur eine Tabelle verbergen.

Sollte Ihr Visual Basic Editor nur ein leeres graues Fenster zeigen und nicht wie abgebildet aussehen, so aktivieren Sie mit Menü – Ansicht – Projekt-Explorer oder >STRG>R das Fenster “Projekt – VBAProjekt”. Das Eigenschaften Fenster rufen Sie mit <F4> oder Menü – Ansicht – Eigenschaftenfenster auf.

Wählen Sie im Projektfenster Ihre Tabelle 2 (Berechnung) aus, ändern Sie dann im Eigenschaften Fenster die Eigenschaft “Visible” (Sichtbarkeit) von “-1 –xlSheetVisible” auf “0 – xlSheetHidden”. Das war’s schon. Achten Sie darauf, dass Sie nicht alle Blätter einer Arbeitsmappe verstecken können.

Schließen Sie den VB Editor durch Klick auf das rote Kreuz in der rechten oberen Ecke, Sie müssen nichts speichern, das erfolgt automatisch. Die Tabelle “Berechnung” sollte verschwunden sein.

Visual Basic Editor mit Projekt- und Eigenschaftenfenster Tabelle "Berechnung" ist verschwunden

Kalender für Excel 97 bis 2003 abspeichern

Nun speichern Sie die Kalender Datei kompatibel für Excel 97 bis 2003, dazu klicken Sie auf Datei und “Speichern unter”. Wählen Sie “Excel-97-2003-Arbeitsmappe” und geben Sie als Dateinamen bspw. “kalender2003.xls” ein. Klicken Sie dann auf “Speichern”.

Leider findet die Kompatibilitätsprüfung nicht alle Fehler. Gefunden werden:

  1. Bedingte Formatierung für den Kalender, hier wird erheblicher Funktionsverlust prognostiziert, der so jedoch nicht eintreten wird.
  2. Formatierungen einzelner Zellen sollen einen geringfügigen Genauigkeitsverlust haben, vermutlich meint Excel hier die Umsetzung der Designfarben, die es in den alten Versionen so nicht gibt.

Nicht gefunden wird die Funktion NETTOARBEITSTAGE, diese Funktion gibt es nicht in Excel 97-2003. Sie ersetzen die Funktion mit einer VBA-Funktion, die das Laden eines Add-Ins erfordert.

Wählen Sie im Fenster für die Kompatibilitätsprüfung “Korrigieren”.

Datei kompatibel für Excel 97 bis 2003 abspeichern Wählen Sie im Fenster für die Kompatibilitätsprüfung “Korrigieren”.

Öffnen Sie die konvertierte Excel Datei in der älteren Excel Version, hier vorgeführt mit Excel 2003.

Solange Sie eine der Zellen mit der inkompatiblen Formel für die NETTOARBEITSTAGE nicht anfassen, sieht alles perfekt aus. Sobald Sie eine Formel anklicken und mit <ENTER> bestätigen werden Fehler angezeigt. Das ältere Excel kennt die Funktion NETTOARBEITSTAGE nicht.

Das ältere Excel kennt die Funktion NETTOARBEITSTAGE nicht.

Aktivieren Sie über Menü “Extras” – “Add-Ins…” die “Analyse-Funktionen-VBA”. Jetzt ersetzen Sie die Funktion “NETTOARBEITSTAGE ” durch “NetWorkDays” und bestätigen die Eingabe mit der <ENTER>-Taste. In Zelle A34 sollte folgende Formel stehen:

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

Aktivieren Sie über Menü “Extras” – “Add-Ins…” die “Analyse-Funktionen-VBA”.  Funktion “NETTOARBEITSTAGE ” durch “NetWorkDays” ersetzen

Markieren Sie Zeile 34 und wählen Sie Menü “Bearbeiten” – “Ersetzen”. Geben Sie für “Suche nach” NETTOARBEITSTAGE und für “Ersetzen durch” NetWorkDays ein. Nach Klick auf “Alle ersetzen” erscheint ein Fenster, dass 11 Ersetzungen bestätigt. Jetzt berechnet die ältere Excel Version auch die Arbeitstage.

Alle  “NETTOARBEITSTAGE ” durch “NetWorkDays” ersetzen  Jetzt berechnet die ältere Excel Version auch die Arbeitstage.

Wir sind fertig. Der Kalender funktioniert auch mit der älteren Excel Version. Aber Ihnen gefällt, wie mir übrigens auch, die “konvertierte” Designfarbe absolut nicht. Das Cyan verursacht doch Augenkrebs.

Konvertierte Design-Farbe gefällt nicht – was tun?

Ich nehme für die schnelle Lösung bewusst in Kauf, dass die Formeln nochmals eingegeben werden müssen. Sie können die bedingte Formatierung für Tage, Wochentage und Feiertage/KW auch für jeden Monat einzeln vornehmem, das verzwölffacht jedoch den Arbeitsaufwand.

Wir geben Vollgas und ändern:

  1. die bedingte Formatierung für die Tage,
  2. die bedingte Formatierung für die Wochentage,
  3. die bedingte Formatierung für die Feiertage/KW,
  4. die Tabellenlinien waagerecht und senkrecht und
  5. die Farben für das Kalenderjahr, die Monate und die Nettoarbeitstage.
1. Bedingte Formatierung der Tage im gesamten Kalender

Dazu geben Sie im Namenfeld folgende Bereiche ein und bestätigen die Eingabe mit <ENTER>, danach halten Sie die <STRG>-Taste gedrückt und klicken 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 Formel für die bedingte Formatierung lautet wie folgt, wählen Sie eine Formatierung.

=ODER(NICHT(ISTFEHLER(FINDEN(A3;Feiertagsliste)));WOCHENTAG(A3;2)>5)
Bedingte Formatierung der Tage im gesamten Kalender ersetzen
2. Bedingte Formatierung der Wochentage im gesamten Kalender

Folgende Bereiche auswählen. Dann bei gedrückter <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 Formel für die bedingte Formatierung lautet wie folgt, wählen Sie eine Formatierung wie bei den Tagen.

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

Bedingte Formatierung der Wochentage im gesamten Kalender ersetzen

3. Bedingte Formatierung der Feiertage/KW im gesamten Kalender

Den Bereich wie folgt wählen und dann bei bei gedrückter <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 Formatierung sind zwei Formeln einzugeben.

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

Die erste Formel gilt für die Kalenderwoche, ändern Sie hier nur die Schriftfarbe. Formel 2 gilt für die Feiertage, wählen Sie eine Formatierung. Halten Sie die Reihenfolge der einzelnen Formeln für die bedingte Formatierung ein. 

Bedingte Formatierung der Feiertage/KW im gesamten Kalender ersetzen

4. Tabellenlinien waagerecht und senkrecht

Nun müssen Sie die horizontalen Linien formatieren, wählen Sie dazu folgenden Bereich.

A2:AH34

Wählen Sie im Menü “Format” – “Zellen” und dann “Rahmen”. ACHTUNG! Formatieren Sie die mittlere senkrechte Linie nicht. Formatieren Sie nur die äußeren senkrechten Linien und alle waagerechten Linien.

Tabellenlinien waagerecht formatieren

Für das Formatieren der senkrechten Linien dürfen wir nur jeden zweiten Monat markieren. Wählen Sie folgenden Bereich. Formatieren Sie nur die äußere linke und äußerste rechte senkrechte Linie.

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

Tabellenlinien senkrecht formatieren

Jetzt müssen Sie nur noch die Zellen mit dem Jahr, die Monate und die Arbeitstage markieren. Wählen Sie eine Formatierung für die Schriftfarbe.

 Jahr, Monate und Arbeitstage formatieren

Das war’s. Der Kalender für Excel 97 bis 2003 sieht nun auch angenehm aus.

Der Kalender für Excel 97 bis 2003 sieht nun auch angenehm aus.

Viel Erfolg und Spaß beim Nachmachen.

Down­loads

  1. Kalender Teil 1 für Excel 2010,
  2. Kalender Teil 2 für Excel 2010,
  3. Kalender Teil 3 für Excel 2010,
  4. Kalender Teil 4 für Excel 2010,
  5. finaler Kalender für Excel 2010,
  6. konvertierter Kalender für Excel 97-2003 und
  7. finaler Kalender für Excel 97-2003.

Kommentare sind geschlossen.