Ziel dieses Workshops ist es einen Kalender zu erstellen, der nur durch Eingabe der Jahreszahl automatisch ein Kalenderblatt erzeugt, in dem die Wochenenden und die gesetzlichen Feiertage markiert und die Anzahl der Arbeitstage angezeigt werden. Der Kalender soll für die Nutzung der Design-Features der neueren Excel Versionen vorbereitet sein. In diesem 5. Teil des Workshops 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:
- Teil 1 — Grundlagen,
- Teil 2 – Programmierung,
- Teil 3 – Die Bedingte Formatierung meistern oder
- Teil 4 — Aufhübschen
nacharbeiten und Ihre eigene Kalenderdatei erstellen. Oder Sie laden sich eine der Dateien herunter und starten mit dem Workshop Ihrer Wahl:
- “Kalender Teil 1 für Excel 2010”,
- “Kalender Teil 2 für Excel 2010”,
- “Kalender Teil 3 für Excel 2010” oder
- “Kalender Teil 4 für Excel 2010”.
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.
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:
- Bedingte Formatierung für den Kalender, hier wird erheblicher Funktionsverlust prognostiziert, der so jedoch nicht eintreten wird.
- 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”.
Ö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.
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)
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.
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:
- die bedingte Formatierung für die Tage,
- die bedingte Formatierung für die Wochentage,
- die bedingte Formatierung für die Feiertage/KW,
- die Tabellenlinien waagerecht und senkrecht und
- 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)
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)
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.
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.
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
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.
Das war’s. Der Kalender für Excel 97 bis 2003 sieht nun auch angenehm aus.
Viel Erfolg und Spaß beim Nachmachen.