Kalender mit Schichtplaner

Die Schlüsselzahl wird mit der Formel: =REST(A2;10) berechnet. Der Wert 10 ist abhängig von der Anzahl der verschiedenen Schichtkürzel. Im Beispiel sind das eben 10. Gibt es eine andere Anzahl, muss der Wert angepasst werden. Der Tabellenausschnitt verdeutlicht die Vorgehensweise.

	A	B	C	D
1	Datum	Serielle Zahl des Datums	Wert	Zeichen
2	01.01.04	37987	7	S
3	02.01.04	37988	8	N
4	03.01.04	37989	9	N
5	04.01.04	37990	0	T
6	05.01.04	37991	1	T
7	06.01.04	37992	2	-
8	07.01.04	37993	3	-
9	08.01.04	37994	4	F
10	09.01.04	37995	5	F
11	10.01.04	37996	6	S

Formeln der Tabelle

Zelle Formel C2 =REST(A2;10)

Die Schlüsselzahlen die aus dem Datum errechnet werden, sind immer ganzzahlig, der kleinste Wert ist immer 0. Das nutzen wir, um über ein Array die Kürzel mit dem Datum zu verknüpfen. Es muss also auf einem Tabellenblatt eine datumreihe erstellt werden, in der alle Schichten, die vorkommen können, dem richtigen Tag zugeordnet werden. Dabei kommt dem Nullwert und dem dazu gehörigen Kürzel die Schlüsselrolle zu. Deshalb habe ich beide rot markiert.

Normalerweise ist es so, dass die Felder eines Array immer mit dem Wert 0 anfangen zu zähelen. (Es gibt da zwar Ausnahmen, die möchte ich hier aber ausklammern, da es nur verwirren würde). Im Quelltext werden die Kürzel im Array beginnend mit dem neben dem Nullwert stehenden Buchstaben eingetragen, Es folgt dann der dem Wert 1 zugeordnete und so weiter, in unserem Beispiel bis zum 10. Wert, das ist die 9.

Das sieht so aus: sk = Array("T", "T", "-", "-", "F", "F", "S", "S", "N", "N")

Das Makro:

Option Explicit

Sub schichten()
' trägt vorgegebene Schichtkürzel in einen Kalender ein
' geschrieben von Klaus-Dieter Oppermann
' am 16.03.2004
' Änderung 28.11.2004           ' Korrektur für Schaltjahr zugefügt
' letzte Änderung 28.10.2006    ' Quelltext optimiert
' Variablen deklarieren
Dim sk As Variant               ' Schichtkürzel
Dim s As Integer                ' Schleifenzähler für Tabellenzeilen
Dim we As Integer               ' Zuweisungsschlüssel
Dim sp As Integer               ' Schleifenzähler für Spalten
Dim arr(50, 0)                  ' Array zum Eintragen der Kürzel
Dim ziel As Integer             ' letzte Zelle für Array
' Kürzel in Feldvariable einlesen
sk = Array("T", "T", "-", "-", "F", "F", "S", "S", "N", "N")
' Kürzel in Kalender schreiben
For sp = 2 To 35 Step 3                                         ' Schleife für Spaltenzuweisung
    ziel = Cells(3, sp - 1).End(xlDown).Row                     ' Zielwert für Schleife
    If sp = 5 And Cells(1, 1) Mod 4 <> 0 Then ziel = ziel - 1   ' Korrektur für Schaltjahr
    For s = 0 To ziel - 3                                       ' Schleife für Einträge in Zeilen
        we = Cells(s + 3, sp - 1) Mod 10                        ' Feldwert für Variable berechnen (24.07.05)
        arr(s, 0) = sk(we)                                      ' Neue Inhalte in Array einlesen
    Next s                                                      ' Schleifenzähler (Zeile) plus 1
Range(Cells(3, sp), Cells(ziel, sp)) = arr                      ' Array in Tabelle schreiben
Erase arr                                                       ' Array löschen
Next sp                                                         ' Schleifenzähler (Spalte) plus 1
If Cells(1, 1) Mod 4 <> 0 Then Cells(31, 6) = ""                ' Korrektur für Schaltjahr
End Sub

Code eingefügt mit: Excel Code Jeanie

Mit der geschachtelten Schleife, werden die Werte in den Kalender übertragen. Dabei ruft die äußere Schleife (For sp = 2 To 35 Step) die Spalten auf, in die die Kürzel geschrieben werden. For s = 0 To ziel - 3 berechnet die Feldwerte für die Variablen. Im Abschnitt über das Rechnen mit Datum und Uhrzeit habe ich beschrieben, dass hinter jedem Datum eine Zahl steht. Da machen wir uns zu Nutze. Im vorliegenden Beispiel gibt es 10 Schichtkürzel. Entsprechend wird die Zahl hinter dem Datum mit der Formel: we = Cells(s + 3, sp - 1) Mod 10 durch zehn geteilt. Wobei uns die Formel nur den ganzzahligen Rest dieser Berechnung liefert, es ist die VBA-Entsprechung der Formel: =REST(A2;10) die zu Anfang im Tabellenblatt zur Ermittlung der Werte benutzt wurde. Also Zahlen von 0 bis 9, somit ordnet dieses Ergebnis der Variable sk über den errechneten Wert (we), ein Schichtkürzel zu. Also 0="T", 1="T", 2="-" usw.

Mit dieser Zeile: arr(s, 0) = sk(we) werden die die so "gefüllten" Variablen in ein Array geschrieben. Das ermöglicht es, die Kürzel für jeweils einen Monat auf einen Schlag in den Kalender einzutragen. Wie schon versprochen, werde ich das Thema Array noch ausführlicher behandeln.

Den fertigen Schichtkalender gibt es unter Beispieltabellen zum Download.