Schichtfolge berechnen

A B C D E F G 1   A B C D Arrayfeld   2 01.01.2006 N S - F 22   3 02.01.2006 - N F S 23   4 03.01.2006 - N F S 24   5 04.01.2006 F - S N 25   6 05.01.2006 F - S N 26   7 06.01.2006 S F N - 27   8 07.01.2006 S F N - 0   9 08.01.2006 S F N - 1   10 09.01.2006 N S - F 2   11 10.01.2006 N S - F 3   12 11.01.2006 - N F S 4   13 12.01.2006 - N F S 5   14 13.01.2006 F - S N 6   15 14.01.2006 F - S N 7   16 15.01.2006 F - S N 8   17 16.01.2006 S F N - 9   18 17.01.2006 S F N - 10   19 18.01.2006 N S - F 11   20 19.01.2006 N S - F 12   21 20.01.2006 - N F S 13   22 21.01.2006 - N F S 14   23 22.01.2006 - N F S 15   24 23.01.2006 F - S N 16   25 24.01.2006 F - S N 17   26 25.01.2006 S F N - 18   27 26.01.2006 S F N - 19   28 27.01.2006 N S - F 20   29 28.01.2006 N S - F 21   30 29.01.2006 N S - F   Ab hier wiederholt sich die Schichtfolge 31 30.01.2006 - N F S     32 31.01.2006 - N F S

Formeln der Tabelle Zelle Formel F2 =REST(A2;28) F3 =REST(A3;28) F4 =REST(A4;28) F5 =REST(A5;28) F6 =REST(A6;28) F7 =REST(A7;28) F8 =REST(A8;28) F9 =REST(A9;28) F10 =REST(A10;28) F11 =REST(A11;28) F12 =REST(A12;28) F13 =REST(A13;28) F14 =REST(A14;28) F15 =REST(A15;28) F16 =REST(A16;28) F17 =REST(A17;28) F18 =REST(A18;28) F19 =REST(A19;28) F20 =REST(A20;28) F21 =REST(A21;28) F22 =REST(A22;28) F23 =REST(A23;28) F24 =REST(A24;28) F25 =REST(A25;28) F26 =REST(A26;28) F27 =REST(A27;28) F28 =REST(A28;28) F29 =REST(A29;28) Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

In Spalte B sind die Schichten der Schicht 1 (A) so eingetragen wie sie auf bestimmte Tage gefallen sind. Das zugrunde gelegte Datum ist dabei beliebig auswählbar, es muß nur die eingetragene Schicht dazu passen. Im vorliegenden Beispiel haben wir eine Schichtfolge, die sich alle 28 Tage wiederholt. Deshalb wird in der Spalte F das Datum des jeweiligen Tages durch 28 geteilt, in diesem Fall wird nur das Ganzzahlige Ergebnis der Rechenoperation benötigt. Das liefert uns die verwendete Formel. Da sich hinter dem Datum eine serielle Zahl verbirgt bekommen wir für jeden Tag ein anderes Ergebnis. Wegen der Division durch 28 sind das die Zahlen von 0 bis 27.

Diese Berechnungen sind erforderlich, damit man weiß, in welcher Reihenfolge die Schichtkürzel im Makro eingebunden werden. Wichtig ist dabei die Zeile mit dem Ergebnis Null. Das Schichtkürzel aus dieser Zeile ist das erste, das im Makro eingetragen wird. Die anderen folgen in aufsteigender Reihenfolge. Zum besseren Verständnis sehen wir uns das im Makro an:

sk = Array("S", "S", "N", "N", "-", "-", "F", "F", "F", "S", "S", "N", "N", "-", "-", "-", "F", "F", "S", "S", "N", "N", "N", "-", "-", "F", "F", "S") ' Schichtbezeichnungen

Es sind hier in einem Array, das ist eine spezielle Variable mit vielen Feldern, alle Schichtkürzel in der Reihenfolge von 0 bis 27 eingetragen. die Feldadressen des Array sind fortlaufend nummeriert. Die Nummerierung beginnt ebenfalls mit der Null. Das machen wir uns im weiteren zu Nutze.

Das sind die entscheidenden Zeilen im Makro:

we = Sheets("Kalender").Cells(s + iSta, sp - 1) Mod 28 ' Feldwert für Variable berechnen

In dieser Zeile wird passend zum Datum der Wert berechnet, den wir schon aus der Beschreibung weiter oben kennen. Es handelt sich bei der Formel um die VBA-Umsetzung der Formel(n) =REST(A2;28). Die Variablen innhalb der Cells-Anweisung sorgen dafür, dass immer das richtige Datum berechnet wird. Die anfallenden Ergebnisse sind, wie schon oben, die Werte von 0 bis 27. (Das gilt natürlich nur für dieses Beispiel. Bei einem anderen Divisor kommen auch andere Werte heraus. Bei 36 wären das die Werte 0 bis 35).

Die nächste Programmzeile können wir zunächst außen vor lassen, sie berechnet einen Korrekturwert, der erst interessant wird, wenn es um die anderen Schichten geht. Das werde ich noch genauer darstellen.

iSchiKorr = we - vKoArr(Schi, we + 1) ' Arrayfeld aus Datum und Schicht berechnen

In der dritten Zeile wird das berechnete Schichtkürzel an ein Feld eines weiteren Array übergeben. Ich gebe zu, das ist nicht leicht zu verstehen. Leider auch nicht leicht zu beschreiben. Im Grunde reicht es aber, wenn man nachvollziehen kann, wie mit der Tabellenformel die Reihenfolge der Schichtkürzel berechnet wird. Die werden dann in der richtigen Reihenfolge in die zuerst beschriebene Makrozeile eingetragen. Außerdem muß in der Zeile, die ich als zweite beschrieben habe, statt der 28 der richtige Werte geschrieben werden, wenn die Schichtfolge eine andere Anzahl von Tagen hat.

arr(s, 0) = sk(iSchiKorr) ' Neue Inhalte in Array einlesen

Jetzt muss ich noch eins draufsetzen, es geht um die Beschreibung, wie die anderen Schichten berechnet werden. Ich habe das alles in ein Makro eingebaut. Was zwar vom Programm her elegant ist, (glaube ich wenigstens), aber das Verständnis der Vorgänge nicht erleichtert. Zunächst eine Überlegung die für das Grundverständnis wichtig ist, und für den geneigten Leser auch noch leicht nachvollziehbar sein sollte. Wir sind von einem Schichtsystem ausgegangen, das vier Schichten beinhaltet. Die Schichtfolge wechselt alle 28 Tage. 28 dividiert (geteilt) durch 4 ergibt 7. Das heißt, die Schichten untereinander sind, was die Schichtfolge betrifft, jeweils um sieben Tage versetzt. Das habe ich im nächsten Bild dargestellt.

	AD	AE	AF	AG	AH
1	Arrayfeld	Abwicklung der Schichten
2	22	N
3	23	-
4	24	-
5	25	F
6	26	F
7	27	S
8	0	S
9	1	S	S
10	2	N	N
11	3	N	N
12	4	-	-
13	5	-	-
14	6	F	F
15	7	F	F
16	8	F	F	F
17	9	S	S	S
18	10	S	S	S
19	11	N	N	N
20	12	N	N	N
21	13	-	-	-
22	14	-	-	-
23	15	-	-	-	-
24	16	F	F	F	F
25	17	F	F	F	F
26	18	S	S	S	S
27	19	S	S	S	S
28	20	N	N	N	N
29	21	N	N	N	N
30	22	N	N	N	N
31	23	-	-	-	-
32	24	-	-	-	-
33	25		F	F	F
34	26		F	F	F
35	27		S	S	S
36	0		S	S	S
37	1		S	S	S
38	2		N	N	N
39	3		N	N	N
40	4			-	-
41	5			-	-
42	6			F	F
43	7			F	F
44	8			F	F
45	9			S	S
46	10			S	S
47	11				N
48	12				N
49	13				-
50	14				-
51	15				-
52	16				F
53	17				F

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Daraus folgt, das die Schichten 2 bis 4 einen anderen Startwert für die Schichtfolge benötigen, als die Schicht 1. Das wird im nächsten Bild noch deutlicher:

	A	B	C	D	E	F
1		1	2	3	4	Arrayfeld
2	01.01.2006	N	S	-	F	22
3	02.01.2006	-	N	F	S	23
4	03.01.2006	-	N	F	S	24
5	04.01.2006	F	-	S	N	25
6	05.01.2006	F	-	S	N	26
7	06.01.2006	S	F	N	-	27
8	07.01.2006	S	F	N	-	0
9	08.01.2006	S	F	N	-	1
10	09.01.2006	N	S	-	F	2
11	10.01.2006	N	S	-	F	3
12	11.01.2006	-	N	F	S	4
13	12.01.2006	-	N	F	S	5
14	13.01.2006	F	-	S	N	6
15	14.01.2006	F	-	S	N	7
16	15.01.2006	F	-	S	N	8
17	16.01.2006	S	F	N	-	9
18	17.01.2006	S	F	N	-	10
19	18.01.2006	N	S	-	F	11
20	19.01.2006	N	S	-	F	12
21	20.01.2006	-	N	F	S	13
22	21.01.2006	-	N	F	S	14
23	22.01.2006	-	N	F	S	15
24	23.01.2006	F	-	S	N	16
25	24.01.2006	F	-	S	N	17
26	25.01.2006	S	F	N	-	18
27	26.01.2006	S	F	N	-	19
28	27.01.2006	N	S	-	F	20
29	28.01.2006	N	S	-	F	21

Jetzt hätte ich also einfach vier Arrays definieren können, die zwar letzlich die gleiche Schichtfolge enthalten, aber immer einen anderen Startwert haben. Das wollte ich vermeiden, deshalb habe ich mich entschlossen, das mit Korrekturwerten zu lösen. Die findet man hier:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
34	Korrekturwerte für Schicht. Nicht Löschen!
35	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
36	2	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	21	21	21	21	21	21	21
37	3	-21	-21	-21	-21	-21	-21	-21	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7
38	4	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	14	14	14	14	14	14	14	14	14	14	14	14	14	14

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Wer den Kurs von Anfang an durchgearbeitet hat, kann sich daran erinnern. Bei Schicht 1 stehen diese Werte auf Null, weil das die Berechnungsgrundlage ist. Bei den anderen Schichten wird damit der Wert modifiziert, den diese Programmzeile berechnet hat:

we = Sheets("Kalender").Cells(s + iSta, sp - 1) Mod 28 ' Feldwert für Variable berechnen

Alles nächstes betrachten wir noch mal die Zeile, die ich zu Anfang nur am Rand erwähnt hatte:

iSchiKorr = we - vKoArr(Schi, we + 1) ' Arrayfeld aus Datum und Schicht berechnen

Das ist die Zeile, die die Korrekturwerte für die anderen Schichten berechnet. Für diesen Zweck wurden die Inhalte der Zellen A35 bis AC 38 in ein zweidimensionales Array eingelesen. Das geschah in dieser Zeile:

vKoArr = Sheets("Schichtfolge").Range("B35:AC38").Value2 ' Korrekturwerte einlesen

Jetzt muß noch eine Besonderheit beachtet werden: weiter oben hatte ich geschieben, dass Arrayfelder mit der Null anfangen zu zählen. Leider stimmt das nicht immer. In diesem Fall, also wenn der Bereich einer Range eingelesen wird, so wie ich es hier gemacht habe, dann beginnen die Adressen der Arrayfelder mit 1 zu zählen. Warum das so ist weiß ich nicht, es ist aber wichtig, das für das Verständnis der weiteren Ausführungen zu wissen.

Mit diesem Wissen betrachten wir noch einmal diese Zeile:

iSchiKorr = we - vKoArr(Schi, we + 1) ' Arrayfeld aus Datum und Schicht berechnen

Die Variable iSchiKorr soll mit einem Wert gefüllt werden, der das richtige Schichtkürzel dem richtigen Datum zuweist. Im ersten Teil der Formel iSchiKorr = we ist das, wie ich denke, noch klar. Dann geht es aber weiter, - vKoArr(Schi, we + 1). Was bedeutet das? Nun, Schi ist die Variable für die Schicht, nimmt also den Wert 1, 2, 3 oder 4 ein, je nach dem, welche Schicht angezeit werden soll. we ist ein Wert von 0 bis 27, das hängt, wie wir schon erfahren haben, vom jeweiligen Datum ab. Aber warum + 1? Das hängt mit der Zählweise unserer Arrays zusammen. Der Wert we geht von 0 bis 27, das Array iSchiKorr erwartet aber Werte von 1 bis 28, alles klar?

Gehen wir also einfach mal davon aus, es wurde die Schicht 2 gewählt und das datum gibt den Wert 18 vor. Dann sieht die Adresse der Arrayfeldes, das für die aktuelle Berechnung verwendet werden soll so aus: vKoArr(2, 19), 19 wegen we + 1!

Im nächsten Bild habe ich versucht, das Array iKoArr mit seinen Feldern darzustellen. Die oben genannte Adresse habe ich rot markiert.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
2	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	2	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	21	21	21	21	21	21	21
4	3	-21	-21	-21	-21	-21	-21	-21	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7
5	4	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	14	14	14	14	14	14	14	14	14	14	14	14	14	14

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Heißt unsere Rechnung also im Klartext we - (-7). Nehmen wir also mal an, we hat den Wert 8, dann bedeutet das 8 - (-7) = 15. Donnerwetter! wie kommt denn das? Nun die eine oder der andere wird sich noch dunkel erinnern, da war doch was in Mathe? Stimmt, wenn man von einer Zahl eine neagtive Zahl subthraiert (abzieht), dann wird daraus eine Addition. Auf dieser Basis wird man dann das nächste Bild verstehen:

Schichtkürzel

	A	B	C	D	E	F	G	H	I	J	K	L
1	Datum	1	2	3	4	Arrayfeld			1	2	3	4
2	07.01.2006	S	F	N	-	0			0	7	21	14
3	08.01.2006	S	F	N	-	1			1	8	22	15
4	09.01.2006	N	S	-	F	2			2	9	23	16
5	10.01.2006	N	S	-	F	3			3	10	24	17
6	11.01.2006	-	N	F	S	4			4	11	25	18
7	12.01.2006	-	N	F	S	5			5	12	26	19
8	13.01.2006	F	-	S	N	6			6	13	27	20
9	14.01.2006	F	-	S	N	7			7	14	0	21
10	15.01.2006	F	-	S	N	8			8	15	1	22
11	16.01.2006	S	F	N	-	9			9	16	2	23
12	17.01.2006	S	F	N	-	10			10	17	3	24
13	18.01.2006	N	S	-	F	11			11	18	4	25
14	19.01.2006	N	S	-	F	12			12	19	5	26
15	20.01.2006	-	N	F	S	13			13	20	6	27
16	21.01.2006	-	N	F	S	14			14	21	7	0
17	22.01.2006	-	N	F	S	15			15	22	8	1
18	23.01.2006	F	-	S	N	16			16	23	9	2
19	24.01.2006	F	-	S	N	17			17	24	10	3
20	25.01.2006	S	F	N	-	18			18	25	11	4
21	26.01.2006	S	F	N	-	19			19	26	12	5
22	27.01.2006	N	S	-	F	20			20	27	13	6
23	28.01.2006	N	S	-	F	21			21	0	14	7
24	01.01.2006	N	S	-	F	22			22	1	15	8
25	02.01.2006	-	N	F	S	23			23	2	16	9
26	03.01.2006	-	N	F	S	24			24	3	17	10
27	04.01.2006	F	-	S	N	25			25	4	18	11
28	05.01.2006	F	-	S	N	26			26	5	19	12
29	06.01.2006	S	F	N	-	27			27	6	20	13

Formeln der Tabelle

Zelle Formel I2 =$F2-$B$32 J2 =$F2-$B$33 K2 =$F2-$B$34 L2 =$F2-$B$35

Array vKoArr (Inhalte)

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
31	Korrekturwerte für Schicht. Nicht Löschen!
32	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
33	2	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	-7	21	21	21	21	21	21	21
34	3	-21	-21	-21	-21	-21	-21	-21	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7	7
35	4	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	-14	14	14	14	14	14	14	14	14	14	14	14	14	14	14

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Ich habe also im grünen Bereich die Werte dargestellt, die sich bei den oben beschriebenen Berechnungen ergeben können. Aus Platzgründen habe ich, in der Hoffnung, das das auch so verständlich ist, nicht alle Formeln dargestellt. Es ändern sich immer die Spaltenbezeichnungen von B auf C usw.