Arbeitsplan automatish erstellen
Adam Richartz
Hallo Excel Profis
Seit geraumer Zeit versuche ich einen Arbeitsplan (Schichtplan)
über VBA zu erstellen und komme einfach nicht weiter. Nach wochenlangem
erfolglosem tüfteln, wende ich mich nun an euch.
In der Spalte "A2" bis "A366 erstelle ich Datumswerte für ein ganzes Jahr,
in der Spalte "B2" bis "B366" den dazugehörigen Tag mit folgendem Code.
dat = InputBox("Datum im Format TT MM JJ eingeben: zB. 01.01.1998")
Range("Tabelle1!A2") = dat
Range("A3").Select
ActiveCell.FormulaR1C1 = "=(R[-1]C)+1"
Selection.AutoFill Destination:=Range("A3:A366"), Type:=xlFillDefault
Range("B2").Select
ActiveCell.FormulaR1C1 = "=WEEKDAY(RC[-1],1)"
Selection.AutoFill Destination:=Range("B2:B366"), Type:=xlFillDefault
Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy"
Columns("B:B").Select
Selection.NumberFormat = "ddd"
In der Spalte "C" nun mein Problem, was mich zur Verzweiflung bringt.
Hier muß nun zu jedem Tag die richtige Schicht zugeordnet werden und das für
5 Schichten unterschiedlich und für ein beliebiges Jahr.
Nachfolgend ein Beichspiel zum besseren Verständnis.
Datum Tag Schicht1 Schicht2 Schicht3 Schicht4
Schicht5
03.02.1997 Mo Nacht Früh Frei Spät
Frei
04.02.1997 Di Nacht Früh Frei Spät
Frei
05.02.1997 Mi Nacht Früh Frei Spät
Frei
06.02.1997 Do Nacht Frei Früh Spät
Frei
07.02.1997 Fr Frei Nacht Früh Spät
Frei
08.02.1997 Sa Frei Nacht Früh Spät
Frei
09.02.1997 So Früh Nacht Frei Frei
Frei
10.02.1997 Mo Früh Frei Spät Frei
Nacht
11.02.1997 Di Früh Frei Spät Frei
Nacht
12.02.1997 Mi Früh Frei Spät Frei
Nacht
13.02.1997 Do Frei Früh Spät Frei
Nacht
14.02.1997 Fr Nacht Früh Spät Frei
Frei
15.02.1997 Sa Nacht Früh Spät Frei
Frei
16.02.1997 So Nacht Frei Frei Frei
Früh
17.02.1997 Mo Frei Spät Frei Nacht
Früh
18.02.1997 Di Frei Spät Frei Nacht
Früh
19.02.1997 Mi Frei Spät Frei Nacht
Früh
20.02.1997 Do Früh Spät Frei Nacht
Frei
21.02.1997 Fr Früh Spät Frei Frei
Nacht
22.02.1997 Sa Früh Spät Frei Frei
Nacht
23.02.1997 So Frei Frei Frei Früh
Nacht
24.02.1997 Mo Spät Frei Nacht Früh
Frei
25.02.1997 Di Spät Frei Nacht Früh
Frei
26.02.1997 Mi Spät Frei Nacht Früh
Frei
27.02.1997 Do Spät Frei Nacht Frei
Früh
28.02.1997 Fr Spät Frei Frei Nacht
Früh
01.03.1997 Sa Spät Frei Frei Nacht
Früh
02.03.1997 So Frei Frei Früh Nacht
Frei
03.03.1997 Mo Frei Nacht Früh Frei
Spät
04.03.1997 Di Frei Nacht Früh Frei
Spät
05.03.1997 Mi Frei Nacht Früh Frei
Spät
06.03.1997 Do Frei Nacht Frei Früh
Spät
07.03.1997 Fr Frei Frei Nacht Früh
Spät
08.03.1997 Sa Frei Frei Nacht Früh
Spät
09.03.1997 So Frei Früh Nacht Frei
Frei
(Alles etwas schief, aber als Beispiel sollte es reichen)
Das ist nun ein Beispiel für 5 Wochen, danach wiederholt sich dann die
Schicht, also ab dem 10.03.1997 würde es auf der Schicht1 mit 4x
Nachtschicht,
2x Frei usw. weitergehen.
Jetzt möchte ich das Ganze für 1998, 1999 usw. automatisch erstellen und
zwar
so, daß ich in der InputBox ein beliebiges Jahr eingebe und dann automatisch
die dazugehörige Schicht erstellt wird.
Das Problem liegt darin, daß die Schicht nach 5 Wochen neu beginnt und
dadurch
ungerade ist.
Ich würde mich sehr freuen wenn mir jemand helfen könnte.
Adam Richartz
Unten jetzt noch die Reihenfolge des Schichtsystems.
Nacht
Nacht
Nacht
Nacht
Frei
Frei
Früh
Früh
Früh
Früh
Frei
Nacht
Nacht
Nacht
Frei
Frei
Frei
Früh
Früh
Früh
Frei
Spät
Spät
Spät
Spät
Spät
Spät
Frei
Frei
Frei
Frei
Frei
Frei
Frei
Frei
Adam Richartz
Die Spalten sind etwas durcheinander geraten.
Die Schicht5 ist unter das datum geraten.
Ich hoffe Ihr könnt das Beispiel auch so verstehen.
Nochmals danke für ewentuelle Antwort
Adam Richartz
Hans Werner Hofmann
On Tue, 9 Dec 1997 18:25:46 +0100, "Adam Richartz"
<Rich...@email.msn.com> wrote:
>Hallo Excel Profis
8<---------
>In der Spalte "C" nun mein Problem, was mich zur Verzweiflung bringt.
>Hier muß nun zu jedem Tag die richtige Schicht zugeordnet werden und das für
>5 Schichten unterschiedlich und für ein beliebiges Jahr.
>Nachfolgend ein Beichspiel zum besseren Verständnis.
8<------------
Es ist etwas schwierig Dein Problem zu durchdenken. Was ich verstanden
habe ist foglendes:
1. Dein Schichtplan gent über 5 Wochen = 5x7 Tage = 35 Tage
2. Jedem Tag im Jahr ist modulo 35 ein Schichtdienst zuzuordnen.
D.H. Du nimmst das Datum als eine fortlaufende Integer-Zahl (Das ist
so in XL aufgebau) und berechnest den Divisionsrest von 35:
Also entspricht der
A B C D
Datumswert fortlau Schicht Divisionsrest
fende dienst
Zahl
--------------+-------+---------+------------------
03.02.97 35464 Nacht 9 =REST(B1;35)
04.02.97 35465 Nacht 10 =REST(B2;35)
05.02.97 35466 Nacht 11 =REST(B3;35)
06.02.97 35467 Nacht 12 =REST(B4;35)
07.02.97 35468 Frei 13 =REST(B5;35)
08.02.97 35469 Frei 14 =REST(B6;35)
09.02.97 35470 Früh 15 =REST(B7;35)
10.02.97 35471 Früh 16 =REST(B8;35)
11.02.97 35472 Früh 17 =REST(B9;35)
12.02.97 35473 Früh 18 =REST(B10;35)
13.02.97 35474 Frei 19 =REST(B11;35)
14.02.97 35475 Nacht 20 =REST(B12;35)
15.02.97 35476 Nacht 21 =REST(B13;35)
16.02.97 35477 Nacht 22 =REST(B14;35)
17.02.97 35478 Frei 23 =REST(B15;35)
18.02.97 35479 Frei 24 =REST(B16;35)
19.02.97 35480 Frei 25 =REST(B17;35)
20.02.97 35481 Früh 26 =REST(B18;35)
21.02.97 35482 Früh 27 =REST(B19;35)
22.02.97 35483 Früh 28 =REST(B20;35)
23.02.97 35484 Frei 29 =REST(B21;35)
24.02.97 35485 Spät 30 =REST(B22;35)
25.02.97 35486 Spät 31 =REST(B23;35)
26.02.97 35487 Spät 32 =REST(B24;35)
27.02.97 35488 Spät 33 =REST(B25;35)
28.02.97 35489 Spät 34 =REST(B26;35)
01.03.97 35490 Spät 0 =REST(A27;35)
02.03.97 35491 Frei 1 =REST(A28;35)
03.03.97 35492 Frei 2 =REST(B29;35)
04.03.97 35493 Frei 3 =REST(B30;35)
05.03.97 35494 Frei 4 =REST(B31;35)
06.03.97 35495 Frei 5 =REST(B32;35)
07.03.97 35496 Frei 6 =REST(B33;35)
08.03.97 35497 Frei 7 =REST(B34;35)
09.03.97 35498 Frei 8 =REST(B35;35)
Dann baust Du einen Array auf
Beginnend mit dem Schichtturnus (REST 0) am
1.3.97->9.3.97 und weiter vom 3.2.97->28.2.97 (REST(34)
mit:
Name festlegen:
SCHICHT1
Zugeordnet zu:
={"Spät";"Frei";"Frei";"Frei";"Frei";"Frei";"Frei";"Frei";"Frei";"Nacht";"Nacht";"Nacht";"Nacht";"Frei";"Frei";"Früh";"Früh";"Früh";"Früh";"Frei";"Nacht";"Nacht";"Nacht";"Frei";"Frei";"Frei";"Früh";"Früh";"Früh";"Frei";"Spät";"Spät";"Spät";"Spät";"Spät"}
Von jedem Datum läßt sich dann die Schicht berechnen zu
=INDEX(SCHICHT1;REST(Datum;35)+1)
Natürlich mußt Du für jede Schicht einen Array anlegen...
Könnte das Dein Problem lösen?
Gruß HW
--
---==## Hans Werner Hofmann ##==---
Fachschule fuer Lebensmitteltechnik
E.C.Baumann-Str.22 95326 Kulmbach
INFO: http://www.tmt.de/~hhofmann
Hans Werner Hofmann
Heiko Rost
Adam Richartz schrieb:
>
> Hallo Excel Profis
> Seit geraumer Zeit versuche ich einen Arbeitsplan (Schichtplan)
> über VBA zu erstellen und komme einfach nicht weiter. Nach wochenlangem
> erfolglosem tüfteln, wende ich mich nun an euch.
Hallo Adam,
ist ein recht interessantes Problem, ich habe mich mal hingesetzt und
eine Lösung gebaut. Allerdings spricht mein Excel 5.0 deutsch mit mir,
Du müßtest den VBA-Quelltext übersetzen. Formatierungen mußt Du selbst
einfügen (Quelltext sollte möglichst klein bleiben) oder das
Tabellenblatt per Hand formatieren. Der Plan wird immer in der Tabelle
"Schichtplan" erstellt.
--Start VBA--
Dim texte(35)
Sub haupt()
StartTag = EingabeDlg("Bitte geben Sie das Startdatum ein"; "Datum";
Format(Jetzt(); "tt.mm.jj"))
Wenn StartTag = "" Dann
MeldungsDlg ("Dann eben nicht")
Sonst
Init
StartTag = ZuDatumZeit(StartTag)
'Woche soll Montag beginnen
Durchlaufe BisWahr (Wochentag(StartTag) = 2)
StartTag = StartTag + 1
Schleife
'Woche soll bis Sonntag gehen
EndeTag = StartTag + 371
'Tabellenkopf anlegen
Bereich("A1") = "Datum"
Bereich("B1") = "Tag"
Bereich("C1") = "Schicht1"
Bereich("D1") = "Schicht2"
Bereich("E1") = "Schicht3"
Bereich("F1") = "Schicht4"
Bereich("G1") = "Schicht5"
Nummer = 0
akttag = StartTag
'Werte für jeden Tag eintragen
Durchlaufe BisWahr akttag = EndeTag
Zeile = Kürze(ZnF(Nummer + 3))
Bereich("A" & Zeile) = akttag
Bereich("B" & Zeile) = Format(akttag; "ttt")
Bereich("C" & Zeile) = HoleText(Nummer)
Bereich("D" & Zeile) = HoleText(Nummer + 7)
Bereich("E" & Zeile) = HoleText(Nummer + 14)
Bereich("F" & Zeile) = HoleText(Nummer + 21)
Bereich("G" & Zeile) = HoleText(Nummer + 28)
Nummer = Nummer + 1
akttag = akttag + 1
Schleife
Ende Wenn
Ende Sub
Sub Init()
'Tabelle mit Schichtzyklus erstellen
texte(1) = "Nacht": texte(2) = "Nacht": texte(3) = "Nacht"
texte(4) = "Nacht": texte(5) = "Frei": texte(6) = "Frei"
texte(7) = "Früh": texte(8) = "Früh": texte(9) = "Früh"
texte(10) = "Früh": texte(11) = "Frei": texte(12) = "Nacht"
texte(13) = "Nacht": texte(14) = "Nacht": texte(15) = "Frei"
texte(16) = "Frei": texte(17) = "Frei": texte(18) = "Früh"
texte(19) = "Früh": texte(20) = "Früh": texte(21) = "Frei"
texte(22) = "Spät": texte(23) = "Spät": texte(24) = "Spät"
texte(25) = "Spät": texte(26) = "Spät": texte(27) = "Spät"
texte(28) = "Frei": texte(29) = "Frei": texte(30) = "Frei"
texte(31) = "Frei": texte(32) = "Frei": texte(33) = "Frei"
texte(34) = "Frei": texte(35) = "Frei"
'Arbeitsblatt suchen, evtl. einfügen
gefunden = Falsch
Für i = 1 Bis TabellenblattListe.Anzahl
tabname = TabellenblattListe(i).Name
Wenn tabname = "Schichtplan" Dann
gefunden = Wahr
Ende Wenn
Nächste
Wenn Nicht gefunden Dann
BlattListe.Hinzufügen
AktivesBlatt.Name = "Schichtplan"
Sonst
BlattListe("Schichtplan").Auswählen
Ende Wenn
ZelleListe.InhalteLöschen
Anwendung.GeheZu Bezug:="Z1S1"
Ende Sub
Funktion HoleText(Nummer)
'die Umwandlung Nummer->Schicht
HoleText = texte((Nummer Mod 35) + 1)
Ende Funktion
--Ende VBA--
Gruß Heiko