Über Themen mit T Testbericht

Einmal mehr ein 0,00 Cent - Bericht, der hoffentlich vielen helfen kann …

Wie die fleißigen Leserinnen und Leser meiner MS Office Berichte ja wissen, ist die Veröffentlichung üblicherweise Folge eines „Hilferuf“, einer Bitte bzw. einer Anfrage, wie man dies und das realisieren kann oder wie dies und das denn so geht.

So war es auch in diesem Fall. Ein Freund, der sich selbstständig gemacht hat, hatte die Idee, möglichst kostengünstig seiner Kundschaft eine kleine Freude zu bereiten.

Er wollte, neben einigen kleinen Präsenten einen selbst gestalteten Kalender „basteln“ in dem die wichtigsten Infos (Samstage, Sonntage, Feiertage und Ferienzeiten) enthalten sind.

Gleichermaßen - wie sollte es auch anders sein - wollte der diese „Vorlage“ auch für künftige Jahre und für seine internen Planungen etc. nutzen.
Ein automatischer Kalender musste also her. Tja, da ich so verschiedene selbst im Einsatz hatte, war ich natürlich wieder einmal „fällig“ * lach *.

Anmerkung:
Dies ist ein umfangreicherer Bericht. Er ist besonders für MS-Excel-Interessierte gedacht, die auch gerne etwas experimentieren. Einige grundlegende Excel-Kenntnisse sind eigentlich Voraussetzung dafür, zu verstehen, was sich bei diesem Kalender abspielt.

Gliederung:

1. Aufgabenstellung = automatischer Kalender mit allem Drum und Dran

2. Lösungsmöglichkeiten mit Beschreibung zum Vorgehen ...

a) kurze, allgemeine Vorüberlegungen
b) Einstellungen abhängig von der verwendeten MS Excel-Version
c) bewegliche Feiertage und sonstige Tage berechnen
d) Kalenderwochen ermitteln
e) Realisierung (zu beachtende Infos)

3. Ergebnis

1. Aufgabenstellung

Die Aufgabenstellung ist ja schon im Vorfelde ziemlich deutlich geworden. Es soll ein Kalender mit farblich unterschiedlichen Tagen für die Wochenenden (Samstage, Sonntage) sowie die Feiertage erstellt werden.

Daneben sollen auch die Kalenderwochen angezeigt werden.

Alles soll automatisch erfolgen, damit der Kalender auch:
- für andere Zwecke genutzt werden kann
- für die Folgejahre verwandt werden kann.

2. Lösungsmöglichkeiten mit Beschreibung zum Vorgehen ...

a) kurze, allgemeine Vorüberlegungen

Folgende grundsätzlichen Überlegungen sind vorher anzustellen:

1. Monate
die Monate haben eine unterschiedliche Anzahl von festen Tagen (Monate mit 30 Tagen, Monate mit 31 Tagen).
Das größte „Problem“ ist der Februar, der ja bekanntermaßen alle 4 Jahre 29 Tage besitzt.

2. Feiertage
Es gibt feste Feiertage (Weihnachten, 1. Mai, Neujahr und - soweit man sie als Feiertage definiert - z.B. Silvester oder Heiligabend) und auch variable Feiertage, die sich neben den Osterfeiertagen selbst an diesem Ostertagen anlehnen (Ostern selbst, Pfingsten, Christi Himmelfahrt, Fronleichnam. Und auch hier - soweit man sie dazu zählt, die Faschingstage / Karnevalstage)

3. Kalenderwochen
Die jeweiligen Kalenderwochen sollen ebenfalls angezeigt werden. Nun hat das lfd. Jahr ja schon einmal 53 Wochen.

b) Einstellungen abhängig von der verwendeten MS Excel-Version

Für diese zuvor genannten Optionen sind verschiedene Funktionen bzw. Formeln erforderlich, die MS Excel von Hause aus nicht mit an Bord hat.
Es muss also kurz geprüft werden, ob diese erweiterten Funktionen bereits aktiv sind, oder ob sie erst noch freigeschaltet werden müssen.
Dies lässt sich ganz einfach feststellen, und zwar wie folgt:
Z. B. wird in die Zelle A1 ein gültiges Datum (20.11.2008) eingetragen. In Zelle B1 wird folgende Formel / Funktion erfasst: =EDATUM(A1;1)

Alle erforderlichen Schritte:
- Gleichheitszeichen an die erste Stelle in der Zelle
- das Wort EDATUM dahinter erfassen
- Runde Klammer öffnend eingeben
- entweder den Zellwert, in dem sich das Datum befindet manuell eingeben oder mit der Maus die Zelle anklicken
- nachdem die Zellkoordinate (in diesem Fall A1) zu lesen ist, direkt dahinter ein Semikolon erfassen.
- Zum Schluss fehlt noch die runde Klammer schließend

Wird jetzt direkt ein gültiges Datum bzw. eine Zahl angezeigt (die man per Zellformatierung zu einem Datum umwandeln sollte), muss nichts veranlasst werden.
Erscheint jedoch ein logischer MS Excel Fehler, nämlich #WERT!, sind die zusätzlichen Informationen noch nicht aktiv.

So werden sie aktiviert ...
- ggf. ist die Installations-CD von MS-Excel bereitzuhalten
- Im Menü EXTRAS befindet sich eine Auswahl / eine Option mit dem Namen „Add-Ins ...“. Diese ist auszuwählen.
- Im nun erscheinenden Fenster ist nach dem Add-In „Analsyse-Funktionen“ zu suchen.
- Ist diese gefunden worden, muss sich mit einem Klick auf das entsprechende Ankreuzekästchen aktiviert werden. (Nicht verwirren lassen. Es gibt auch eine Analsysefunktionen-VBA. Es können auch beide aktiviert werden. Schaden tut es nichts)
- Sobald sie aktiviert ist, wird das Fenster durch Klick auf den OK-Schalter wieder geschlossen.
- Entweder ist nun anstelle des Fehlers direkt schon das korrekte Ergebnis zu lesen, oder es muss die Funktion noch einmal durch Tastendruck auf die F9-Taste oder durch Markieren und Betätigung der Datenfreigabetaste aktualisiert werden.

Funktioniert es wider Erwarten nicht, sind die Schritte nicht korrekt abgearbeitet worden, oder es liegt z.B. eine fehlerhafte Installation vor.

MS Excel 2007 Nutzer brauchen sich hierum nicht mehr zu kümmern; die Funktion ist standardmäßig aktiv. Bei der Version 2003 müsste es auch standardmäßig aktiviert sein, das Add-In.

c) bewegliche Feiertage und sonstige Tage berechnen

Dafür ist die Gauss’sche Osterformel nach dem Gregorianischen Kalender notwendig. Ich werde hier jetzt nicht detailliert auf die Formel eingehen.
Nur soviel:
- sie ist für den Zeitraum 1583 bis 8202 in Deutschland gültig.
- sie fußt auf dem Ostersonntag, der lt. Kirchenkonzil nach dem ersten Vollmond im Frühling ab dem 21. März liegt.

Für die Berechnung / für das Nachvollziehen sollten die Berechnung des Ostertages einschließlich der einzelnen Funktionsschritte in einem separaten Tabellenblatt untergebracht werden.

Für die Berechnung benötigen wir eine gültige 4-stellige Zahl (es wird als Beispiel 2008 genutzt).

Folgende Informationen sind in die einzelnen Zellen einzutragen
(wer will, muss dann darauf achten, dass die Infos genau so, wie sie nach dem jeweiligen Doppelpunkt angegeben sind, übernommen werden, ohne Leerzeichen. IMMER ab dem Gleichheitszeichen

In Zelle A1 des Tabellenblatts, welches „Ostern“ genannt worden ist, wird eintragen: 2008 bzw. das gewünschte Jahr.
Das jeweils aktuelle Jahr lässt sich natürlich auch automatisieren mit folgender Formel / Funktion:
=JAHR(HEUTE())

HINWEIS:
Wegen der Längenbegrenzung wurde bei längeren Formeln ein Unterstrich _ sowie jeweils ein Leerzeichen davor und dahinter eingefügt. Diese dürfen natürlich NICHT mit erfasst werden !!

In Zelle B1 eintragen: =REST(A1;19)+1
In Zelle C1 eintragen: =GANZZAHL(A1/100)+1
In Zelle D1 eintragen: =GANZZAHL((3*C1)/4-12)
In Zelle E1 eintragen: =GANZZAHL((8*C1+5)/25-5)
in Zelle F1 eintragen: =GANZZAHL((5*A1)/4-D1-10)
In Zelle G1 eintragen: =GANZZAHL(REST(11*B1+20+E1-D1;30))
In Zelle H1 eintragen: =WENN(G1 11);G1=24);G1+1;G1))
In Zelle I1 eintragen: =44-H1
In Zelle J1 eintragen: =WENN(I1 31;K1-31;K1)
In Zelle M1 eintragen: =WENN(K1>31;4;3)
In Zelle N1 eintragen: =DATUM(A1;M1;L1)

Das Ergebnis müsste korrekt lauten: 39530 bzw. formatabhängig: Sonntag, 23.03.2008

PS:
Nach der Formel kann man im Internet natürlich auch googlen (Gauss’sche Osterformel Excel).

Die Basis für die beweglichen Feiertage ist nun vorhanden.
Anhand dieses Wertes lässt sich durch Addieren bzw. Subtrahieren der restliche Bereich der beweglichen Tage festlegen.

Da die Auflistung der Feiertage / besonderen Tage später noch eine entsprechende Rolle spielen wird, habe ich die gesamten Tage vom jeweils kleinsten Wert des Jahres zum größten Wert des Jahres aufgelistet. Jeweils darunter befindet sich die entsprechende Formel / Funktion, die auf die Ergebniszelle für den Ostersonntag (N1) bzw. die Basiszahl aus Zelle A1 aufbaut.
Da für jedes Bundesland ja ergänzende Regeln existieren, sind diese chronologisch einzufügen bzw. zu streichen.

Die Erfassung bzw. die Formatierung muss wie folgt erfolgen, da sie später noch benötigt wird:
in die jeweils vorderen Zelle wird das gültige Datum als einfache Zahl ausgegeben. Die Zelle im direkten Anschluss enthält im Klartext die Information um was es sich handelt.

Neujahr, immer der 1.1. eines Jahres, die Formel lautet also: =DATUM(A1;1;1)
Lt. Eingabe würde sie dann z. B. lauten:
in Zelle A3 steht die Zahl, nämlich 39448, Zelle B3 steht im Klartext Neujahr

Weiberfastnacht, 52 Tage früher, die Formel lautet also: = N1-52
Lt. Eingabe würde sie dann z. B. lauten:
in Zelle A4 steht die Zahl, nämlich 39478, Zelle B4 steht im Klartext Weiberfastnacht

Rosenmontag, 48 Tage früher, die Formel lautet also: =N1-48
Aschermittwoch, 46 Tage früher, die Formel lautet also: =N1-46
Karfreitag, 2 Tage früher, die Formel lautet also: =N1-2
Ostersamstag, 1 Tag früher, die Formel lautet also: =N1-1
Ostersonntag, ist die Basis, die Formel lautet also: =N1
Ostermontag, 1 Tag später, die Formel lautet also: =N1+1
1. Mai, immer der 1.5 eines Jahres, die Formel lautet also: =DATUM(A1;5;1)
Christi Himmelfahrt, 39 Tage später, die Formel lautet also: =N1+39
Pfingstsamstag, 48 Tage später, die Formel lautet also: =N1+48
Pfingstsonntag, 49 Tage später, die Formel lautet also: =N1+49
Pfingstmontag, 50 Tage später, die Formel lautet also: =N1+50
Fronleichnam, 60 Tage später, die Formel lautet also: =N1+60
Tag der Deutschen Einheit, immer der 3.10 eines Jahres, die Formel lautet also: =DATUM(A1;10;3)
Allerheiligen, immer der 1.11. eines Jahres, die Formel lautet also: =DATUM(A1;11;1)
Heiligabend, immer der 24.12. eines Jahres, die Formel lautet also: =DATUM(A1;12;24)
1. Weihnachtstag, immer der 25.12. eines Jahres, die Formel lautet also: =DATUM(A1;12;25)
2. Weihnachtstag, immer der 26.12. eines Jahres, die Formel lautet also: =DATUM(A1;12;26)
Silvester, immer der 31.12. eines Jahres, die Formel lautet also: =DATUM(A1;12;31)

Kurze Erklärung der Formel DATUM(Jahr;Monat;Tag). Sie liefert di jeweils gültige Zahl des Datums. Sie besteht aus 3 Parametern, und zwar Jahr als erster, Monat als zweiter und Tag als dritter Parameter.

d) Kalenderwochen ermitteln

Da in Deutschland ja Alles etwas „verrückt“ ist, gilt dies auch für die Kalenderwoche. Die neue oder auch 53. Kalenderwoche wird dadurch bestimmt, dass mindestens 4 Tage, von Donnerstag bis Sonntag in diese neuen Woche fallen müssen.
Daraus ergibt sich dann auch wieder eine zusammengesetzte Formel, die wie folgt lautet:

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

Bitte auch hier wieder auf den Unterstrich und die beiden Leerzeichen achten. Diese müssen entfallen.

e) Realisierung (zu beachtende Infos)

Vor Beginn der eigentlichen Arbeit sollte man sich kurz noch einige Gedanken über mögliche Hilfsspalten bzw. Hilfszeilen machen. Sind noch Eingabefelder je Tag erforderlich, etc., etc., etc.

Im Beispiel wird von folgender Situation ausgegangen:

in einer separaten Spalte werden die Kalenderwochen angegeben, hier Spalte F.
in einer separaten Spalte werden die Tage mit dem jeweiligen Wochentag angegeben, hier Spalte G.
in einer separaten Spalten werden die Feiertage im Klartext angegeben, hier Spalte H.
Ich habe insgesamt zur Verdeutlichung in diesem Beispiel 4 Hilfsspalten genutzt, die später ausgeblendet werden.

In die Zeilen 2 und 3 (Zellen F2, F3, G3, H3) kommen die entsprechenden Überschriften hinein.

Los geht’s ...

Wir haben zuvor ja bereits festgehalten, dass in Zelle A1 der Tabelle „Ostern“ der vierstellige Jahreswert 2008 oder auch das aktuelle Jahr steht. Dies wird als Grundlage für die einzelnen Kalendertage dienen.

In der Hilfszelle A4 wird daher einfach erfasst: =DATUM(A1;1;1) um den jeweiligen 1.1. des Jahres zu erhalten. Per Formatierung wird aus der Zahl ein gültiges Datum mit dem Wert TT.MM.JJJJ.
In jede weitere darunter liegende Zelle (A5, A6, A7 bis A34) wird die Formel =A4+1 bzw. A5+1 usw. eingegeben, und zwar solange, bis der 31. Januar erscheint.

In der Hilfszelle (Spalte) B4 wird die Kalenderwoche erfasst:
(Bitte auch hier wieder an das Weglassen des Unterstrichs und der beiden Leerzeichen denken)

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

Auch diese Formelkombination wird nach Eingabe bis zur Zelle B34 herunterkopiert.

Als nächster Wert muss ein möglicher Feiertag ermittelt werden. Hier wird in der Hilfszelle (Spalte) C4 begonnen. Die Formel lautet dazu:

=WENN(ISTFEHLER(SVERWEIS(A4;Ostern!$A$3:$C$22;2;0)) _ SVERWEIS(A4;Ostern!$A$3:$C$22;2;0))

Erklärung:
Bei der Formel handelt es sich um Verschachtelungen diverser Funktionen. Es beginnt mit der Anweisung Wenn-Dann-Sonst
(=WENN(Argument;Dann;Sonst).
Also quasi Licht an oder Licht aus.

Weiter geht es mit einer Fehlerfunktion
=ISTFEHLER(FEHLERARGUMENT).
Diese unterdrückt im Falle eines logischen Fehlers einfach nur den Ausdruck.

Die Sverweisfunktion
(=SVERWEIS(SUCHE;DATENBANK;AUSZUGEBENDE INFO IN DER SPALTE; KONKRETE ÜBEREINSTIMMUNG)
ist eine Art Datenbankfunktion. Es wird ein Eintrag geprüft, ob er in der Datenbank vorhanden ist. Ist das der Fall, wird auf diesen entsprechenden Eintrag zurückgegriffen; dieser dann als Ergebnis angezeigt. Der zu prüfende Wert ist ja der 01.01.2008 im vorliegenden Beispiel. Als Zahl entspricht er genau dem Wert in der Zelle A3 der Tabelle Ostern. Da er diesem Wert genau entspricht, wird um eine Stelle nach rechts gesprungen. Dort steht ja Neujahr als Text. Dieser Wert wird in der Ergebniszelle C4 ausgegeben. Ist eben keine Übereinstimmung vorhanden, bleibt die Zelle einfach leer.
Aus diesen Gründen ist es auch erforderlich, in der Tabelle „Ostern“ die chronologisch aufgelisteten Werte für die diversen Feiertage / besonderen Tage zu erfassen.

Auch hier wird nach Eingabe bis zur Zelle C34 herunterkopiert.

Da sich der „Datenbankbereich“ aus der Tabelle Ostern natürlich NICHT mit verschieben / verändern / anpassen darf, wurde mit der festen Adressierung, also den Dollarzeichen vor der Spalte und vor der Zeile gearbeitet.

In den Hilfszellen müssten nun eigentlich jedes Datum, jede Kalenderwoche und das Neujahr als Feiertag korrekt angezeigt werden.

In den reinen Ausdruckszellen, also den Zellen, die später sichtbar bleiben, wird jeweils nur auf die Originalzelle verwiesen, also z.B. Zelle D3 entspricht dem Datum aus Zelle A3, E3 entspricht der Kalenderwoche aus B3 und F3 dem besonderen Tag aus C3.

Auch diese werden entsprechend herunterkopiert. Umstellungen sind natürlich ohne weiteres möglich.

Wagen wir uns nun an den Monat Februar heran. Hier muss ja die Besonderheit berücksichtigt werden, dass er üblicherweise 28 Tage, aber alle 4 Jahre 29 Tage hat.

Hier erfolgt wieder der gleiche Aufbau wie zuvor geschildert. Die Spalten G bis I dienen Hilfszwecken und werden später ausgeblendet, die Spalten J bis L sind für das Ergebnis zuständig.

Die Zelle G4 enthält wiederum die Formel =DATUM(A1;2;1) um den ersten Februartag zu ermitteln.
Danach wird bis einschließlich zum 28. Februar herunterkopiert. Die Zelle für den möglichen 29. Februar lassen wir erst einmal frei. Direkt darunter wird die Formel zur Ermittlung des jeweils 1. März ausgegeben. Sie lautet =DATUM(A1;3;1). Später sollte sie mit der Schriftfarbe weiß, oder, besser mit drei Semikola formatiert werden (Menü Format -> Zellen -> benutzerdefiniertes Zahlenformat), damit sie unsichtbar ist.

Nun wird ein wenig getrickst, damit der 29. Februar lediglich bedarfsorientiert erscheint. Es gibt diverse Möglichkeiten; eine recht einfache ist die nachfolgende:

=WENN((C31+1)>=C33C31+1)

Mit diesem kleinen Trick wird einfach erreicht, dass nichts angezeigt wird, wenn das Ergebnis größer oder gleich dem 1.3. des aktuellen Jahres ist.

Alle weiteren Monate haben ja wieder feste Tage. Es kann bei diesen wie bereits für den Januar beschrieben, verfahren werden.

Die größten Hürden sind jetzt bereits genommen worden. Jetzt geht es an Fleißarbeit, nämlich die Wiederholung bis zum Dezembermonat.
Ist das alles geschehen muss noch ein wenig Formatierungsarbeit geleistet werden. Hierzu macht man sich am Einfachsten die bedingte Formatierung nutzbar. Schließlich sollen alle Samstage, alle Sonntage und auch alle Feiertage farblich abgesetzt werden.
Da sich diese Infos ja in zwei unterschiedlichen Zellen abspielen, hier zuerst die bedingte Formatierung für die Samstage und Sonntage. Sie lautet z.B. wie folgt:

Bedingung1 Formel ist =WOCHENTAG(D3)=1 Farbe Grün. Es ist ja ein Samstag.
Bedingung2 Formel ist =WOCHENTAG(D3)=7 Farbe Rot. Es ist ja ein Sonntag.

Logischer Weise werden zwei Bedingungen benötigt, da es sich ja um einen Samstag oder einen Sonntag handeln kann.

Diese bedingte Formatierung wird auf sämtliche (also auch auf die Zelle die ggf. den 29. Februar enthält !!) übertragen. Am Besten eignet sich dafür der Formatübertragungspinsel.

Im zweiten Schritt wird nun ebenfalls mit Hilfe der bedingten Formatierung überprüft, ob ein Feiertag / besonderer Tag vorhanden ist.

Da hier nur die Möglichkeit besteht: Feiertag / besonderer Tag ja oder nein, muss auch nur eine Bedingung ausgefüllt werden. Sie lautet:

Bedingung1 Formel ist =F4 "" Farbe blau, wenn die Zelle nicht leer ist. So werden alle Feiertage direkt abgeprüft.

Wenn weitere Zeilen und / oder Spalten eingefügt werden sollen, müssen alle Funktionen natürlich entsprechend angepasst werden.

Ist nun wirklich Alles ordnungsgemäß erfasst und getestet worden, müsste NUR durch Eingabe einer anderen Jahreszahl in der Basiszelle der komplette Kalender verändert werden.

4. mein abschließendes Fazit:

Auch an diesem Beispiel ist in meinen Augen wieder einmal mehr deutlich erkennbar, dass MS Excel denn so alles Mögliche bietet. Hier lassen sich schließlich schier unendliche Lösungen und Kombinationen erstellen.
Es gilt wieder einmal der bewährte Spruch: „man muss halt nur wissen, wie es geht und wo etwas zu finden ist“.

Das war es ´mal wieder von mir...

Ich hoffe, ich konnte der / dem einen oder anderen ein wenig helfen, und freue mich natürlich über jedes Statement und natürlich über jede andere, gute Idee. Über entsprechendes Feedback (als GB-Eintrag / Nachricht, etc.) freue ich mich natürlich ganz besonders.

Sollten Fragen bestehen, sollten ggf. Probleme existieren; ich versuche zu helfen, wenn ich kann.

So, zum Schluss, wie immer... dient ja auch dem Wiedererkennungs -/- Aha-Effekt

Wenn Sie nun hier angelangt sind (nach dem vielen Lesen, oder nur Scrollen wünsche ich Ihnen ein hoffentlich nicht zu anstrengendes Lesen meines Berichts gehabt zu haben.

Ich hoffe, ich konnte mit der Beschreibung einige eigene Ideen „anstoßen“.

Mir wünsche ich viele Kommentare und natürlich möglichst viele positive Bewertungen.
(Aber nur dann, wenn Ihnen der Bericht wirklich gefallen hat. Frei nach dem Motto: der Bericht hat Ihnen gefallen, dann sagen Sie es weiter. Der Bericht hat Ihnen nicht gefallen, dann sagen Sie es mir!).

Bei Verständigungsfragen bitte eine möglichst konkrete Info an mich. Ich versuche zu helfen (es kann ggf. aber etwas dauern !).

Bis zum nächsten Bericht, man liest sich.

PS: meine Berichte erscheinen auch auf anderen Plattformen …

Wer liest sollte auch bewerten, oder?? In diesem Sinne wünsche ich frohes Lesen, ggf. Kommentieren und Bewerten (ist ja halt mein Standardsprüchlein...)

PPS: m. E. sind auch ältere Infos interessant ...