Excel Dienstplan 2024

[Gina]

Hallo Ihr Lieben,

schönen zweiten Weihnachtstag und vorab schon lieben Dank für Eure Hilfe. 

Dienstplan 2024, bei welchem täglich ein einziger Mitarbeiter (von 19 Mitarbeitern) von Mo - Fr (Feiertage mit berücksichtigt) eingesetzt wird. Mein Wunsch ist, dass ich alle Wochentage gleichmäßig verteile. Beispielsweise gibt es in 2024 den Montag 50x, den Dienstag 51 etc...

Ich habe die Mitarbeiter-Liste einfach mal vom 2.1. - 30.12. runterkopiert. Na ja, dass Ergebnis ist bspw., dass ein MA insgesamt 5x am Freitag eingesetzt wurde und andere MA nur 1-2x   . Mit Zufallsbereich, Zufallszahl oder bedingte Formatierung bin ich nicht weiter gekommen.

Wie bewerkstellige ich eine gleichmäßige Verteilung mit einer Formel? 


Spalte B - Wochentag
Spalte C - Datum
Spalte D - Feiertage
Spalte E - Mitarbeiter
Spalte F - Name des eingeteilten Mitarbeiters pro Wochentag

Spalte H - M: Formel "Zählenwenns", um das Ergebnis pro Mitarbeiter aus Spalte F zu gegen zu prüfen.

Nochmals herzlichen Dank für Eure Hilfe / Vorschläge und schönen Tag.
Liebe Grüße 
Gina 

 

Excel-Version 2019 & Kenntnisse OK, aber kein VBA.

[RPP63]

Moin!
Du brauchst doch nur eine Liste der Arbeitstage (ohne Wochenenden und Feiertagen, die an einem Arbeitstag liegen).
Ich komme für NRW auf 251 Arbeitstage.
Jetzt einfach die 19 MA nach unten ziehen.
Die Reihenfolge kannst Du ja einmalig (per Zettel oder Excel) auslosen.
Zu berücksichtigen ist jedoch, dass so etwas nie "gerecht" sein kann, weil Urlaub und Krankheit nicht berücksichtigt sind.

Gruß Ralf

---

Sooo ungerecht empfinde ich meinen Zufallsplan jetzt nicht!
Formeln funktionieren nur in Excel 365 und online.
Aber das bekommst Du ja auch manuell hin.
Ab G1 Auswertung per Pivot-Table.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	2024	WT	Mitarbeiter		FT_WT		Anzahl von Mitarbeiter	WT
2	Di_02.01.	Di	d		Mo_01.01.		Mitarbeiter	Mo	Di	Mi	Do	Fr	Gesamtergebnis
3	Mi_03.01.	Mi	j		Fr_29.03.		a	3	2	3	2	3	13
4	Do_04.01.	Do	i		Mo_01.04.		b	2	2	3	2	4	13
5	Fr_05.01.	Fr	f		Mi_01.05.		c	3	3	3	2	2	13
6	Mo_08.01.	Mo	e		Do_09.05.		d	2	4	3	2	3	14
7	Di_09.01.	Di	s		Mo_20.05.		e	3	1	4	3	2	13
8	Mi_10.01.	Mi	k		Do_30.05.		f	1	5	3	2	3	14
9	Do_11.01.	Do	c		Do_03.10.		g	2	3	2	4	2	13
10	Fr_12.01.	Fr	g		Fr_01.11.		h	3	2	3	3	2	13
11	Mo_15.01.	Mo	b		Mi_25.12.		i	5	3	2	3	1	14
12	Di_16.01.	Di	r		Do_26.12.		j	3	2	3	2	4	14
13	Mi_17.01.	Mi	m				k	3	3	3	1	3	13
14	Do_18.01.	Do	p				l	2	3	2	3	3	13
15	Fr_19.01.	Fr	q				m	2	4	2	2	3	13
16	Mo_22.01.	Mo	a				n	2	3	3	2	3	13
17	Di_23.01.	Di	o				o	3	3	2	3	2	13
18	Mi_24.01.	Mi	n				p	3	2	4	2	2	13
19	Do_25.01.	Do	h				q	2	3	2	3	3	13
20	Fr_26.01.	Fr	l				r	4	2	2	3	2	13
21	Mo_29.01.	Mo	d				s	2	3	1	4	3	13
22	Di_30.01.	Di	j				Gesamtergebnis	50	53	50	48	50	251
23	Mi_31.01.	Mi	i
24	Do_01.02.	Do	f

Zelle	Formel
A2	=LET(jahr;DATUM(A1;1;SEQUENZ(366)); FILTER(jahr;(WOCHENTAG(jahr;2)<6)*(ISTFEHLER(VERGLEICH(jahr;E2#;)))))
B2	=TEXT(A2#;"TTT")
E2	=LET(j;A1;     FFT;DATUM(j;1;{1;121;276;305;359;360})+WENN(MONAT(DATUM(j;2;29))=2;{0;1;1;1;1;1});     A;GANZZAHL(j/100);     B;15+GANZZAHL((3*A+3)/4)-GANZZAHL((8*A+13)/25);     C;2-GANZZAHL((3*A+3)/4);     D;REST(j;19);     E;REST(19*D+B;30);     F;GANZZAHL((E+GANZZAHL(D/11))/29);     G;21+E-F;     H;7-REST(j+GANZZAHL(j/4)+C;7);     I;7-REST(G-H;7);     OS;G+I;     K; DATUM(j;3;OS);     OFT;K+{-2;0;1;39;49;50;60};     x;ZEILEN(FFT);     y;ZEILEN(OFT);     z;SEQUENZ(x+y);     Liste;SORTIEREN(EINDEUTIG(WENN(z<x+1;INDEX(FFT;z);INDEX(OFT;z-x))));     FILTER(Liste;WOCHENTAG(Liste;2)<6))

[Gina]

Hallo Ralf,

super, Danke für die schnelle Rückmeldung. 

So hatte ich es gemacht, d.h., Liste ohne Feiertage & Wochenende angelegt und Mitarbeitername runter kopiert. Im Jahresergebnis wird - abgesehen bei zwei - jeder Mitarbeiter 13x eingesetzt. Ich dachte per Excel geht es noch etwas genauer/einfacher mit der Verteilung an den Wochentagen. 

Allerdings sieht Deine Liste mit den Feiertags-Formeln und der Jahresvorgabe komfortabler  aus. Prima & Danke, dass probiere ich, wenn ich wieder am anderen Laptop mit Excel 365 sitze.

Herzlichen Dank nochmals. LG Gina

[Gina]

Hallo zusammen,
 
plötzlich fiel es mir wie Schuppen vor den Augen – eine ganz simple Lösung nach stundenlangen Hirnen . 


Tabelle nach Wochentagen sortieren, so dass zuerst alle Montage, Dienstage etc. angezeigt werden. Bei dieser Sortierung die 19 Mitarbeiter eingetragen nach unten kopieren. Voila, nun hat jeder Mitarbeiter pro Wochentag max. bis zu 3 Dienste. Anschließend die Tabelle wieder nach Datum sortieren.

Formel nur, wenn man die Gegenrechnung machen möchte. Siehe auf dem Bild die markierten Stellen - meine Ergebnisse mit vorher und nachher.
 

[RPP63]

Exakt dies wollte ich Dir gerade nach ausgiebigem Sahnetortengenuss auch vorschlagen.

Die Reihenfolge der Wochentage bekommst Du (Excel 365) mittels
=LET(jahr;DATUM(A1;1;SEQUENZ(366));
f;FILTER(jahr;(WOCHENTAG(jahr;2)<6)*(ISTFEHLER(VERGLEICH(jahr;E2#;))));
SORTIERENNACH(f;WOCHENTAG(f;2)))

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	2024	WT	Mitarbeiter		FT_WT		Anzahl_MA	WT
2	Mo_08.01.	Mo	d		Mo_01.01.		MA	Mo	Di	Mi	Do	Fr	Gesamt
3	Mo_15.01.	Mo	j		Fr_29.03.		a	2	3	3	2	3	13
4	Mo_22.01.	Mo	i		Mo_01.04.		b	3	2	3	3	2	13
5	Mo_29.01.	Mo	f		Mi_01.05.		c	3	3	2	3	2	13
6	Mo_05.02.	Mo	e		Do_09.05.		d	3	3	3	2	3	14
7	Mo_12.02.	Mo	s		Mo_20.05.		e	3	3	2	3	2	13
8	Mo_19.02.	Mo	k		Do_30.05.		f	3	3	2	3	3	14
9	Mo_26.02.	Mo	c		Do_03.10.		g	3	2	3	3	2	13
10	Mo_04.03.	Mo	g		Fr_01.11.		h	2	3	3	2	3	13
11	Mo_11.03.	Mo	b		Mi_25.12.		i	3	3	2	3	3	14
12	Mo_18.03.	Mo	r		Do_26.12.		j	3	3	2	3	3	14
13	Mo_25.03.	Mo	m				k	3	3	2	3	2	13
14	Mo_08.04.	Mo	p				l	2	3	3	2	3	13
15	Mo_15.04.	Mo	q				m	3	2	3	2	3	13
16	Mo_22.04.	Mo	a				n	2	3	3	2	3	13
17	Mo_29.04.	Mo	o				o	2	3	3	2	3	13
18	Mo_06.05.	Mo	n				p	2	3	3	2	3	13
19	Mo_13.05.	Mo	h				q	2	3	3	2	3	13
20	Mo_27.05.	Mo	l				r	3	2	3	3	2	13
21	Mo_03.06.	Mo	d				s	3	3	2	3	2	13
22	Mo_10.06.	Mo	j				Gesamt	50	53	50	48	50	251
23	Mo_17.06.	Mo	i

Zelle	Formel
A2	=LET(jahr;DATUM(A1;1;SEQUENZ(366)); f;FILTER(jahr;(WOCHENTAG(jahr;2)<6)*(ISTFEHLER(VERGLEICH(jahr;E2#;)))); SORTIERENNACH(f;WOCHENTAG(f;2)))
B2	=TEXT(A2#;"TTT")
E2	=LET(j;A1;     FFT;DATUM(j;1;{1;121;276;305;359;360})+WENN(MONAT(DATUM(j;2;29))=2;{0;1;1;1;1;1});     A;GANZZAHL(j/100);     B;15+GANZZAHL((3*A+3)/4)-GANZZAHL((8*A+13)/25);     C;2-GANZZAHL((3*A+3)/4);     D;REST(j;19);     E;REST(19*D+B;30);     F;GANZZAHL((E+GANZZAHL(D/11))/29);     G;21+E-F;     H;7-REST(j+GANZZAHL(j/4)+C;7);     I;7-REST(G-H;7);     OS;G+I;     K; DATUM(j;3;OS);     OFT;K+{-2;0;1;39;49;50;60};     x;ZEILEN(FFT);     y;ZEILEN(OFT);     z;SEQUENZ(x+y);     Liste;SORTIEREN(EINDEUTIG(WENN(z<x+1;INDEX(FFT;z);INDEX(OFT;z-x))));     FILTER(Liste;WOCHENTAG(Liste;2)<6))

[Charly CNX]

Hi
Noch eine Möglichkeit.
Gruß Charly

[PIVPQ]

Hallo

Eine weitere Alternative mit PQ könnte so lauten.

[Gina]

Hallo Charly,
hallo PIVPQ,

danke, beide Eurer Vorschläge sehen sehr gut aus. 

Mit PQ habe ich länger nicht mehr gearbeitet, beste Gelegenheit sich damit wieder etwas auseinander zu setzen.

Wenn ich es richtig sehe, dass sind beide Vorschläge für Excel 365 ...das muss ich mir gesondert anschauen, sobald ich auf Excel 365 wieder Zugriff habe.

Danke für Eure Mühe. 
Schönen Abend und LG 
Gina

[Charly CNX]

Hier mal eine überarbeitete Version meines Vorschlags.

[Gina]

Hier mal eine überarbeitete Version meines Vorschlags.

Hallo  Charly CNX,

klasse, die echt super. Aber ich bekomme das mit den Excel-Formeln 365 nicht gebacken. Formel mit LET etc. sagen mir nichts.
Lösche ich auf dem Registerblatt "FTG" einen nicht benötigten Feiertag, wird im Registerblatt "Kalender" in der Spalte C 
die Jahreszahl mit 01.01.00 ... angezeigt & unter B2 wird die KW mit #Überlauf angezeigt, was dazu führt, dass alle KW`s
verschwinden. 

Hättest Du die Tabelle mit Excel-Formeln 2019 für mich? 

VG Gina