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.
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)) |
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
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.
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)) |
Hi
Noch eine Möglichkeit.
Gruß Charly
Hallo
Eine weitere Alternative mit PQ könnte so lauten.
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
Hier mal eine überarbeitete Version meines Vorschlags.
(02.01.2024, 03:06)Charly CNX schrieb: [ -> ]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