Office: (Office 365) Excel - Statistik aus Schichtplan erstellen

Hallo zusammen,

ich versuche gerade den bisher händisch erstellten Schichtplan in dem Kleinbetrieb meiner Frau mit Hilfe von Excel etwas zu automatisieren und zu vereinfachen. Bin jedoch lediglich mehr seeehr oberflächlichen Kenntnissen in Excel gesegnet.

Zunächst benötigte ich eine Automatisierung der jeweiligen Daten eines Monats,
dazu habe ich in Zelle A3 und B3 die Möglichkeit geschaffen Monat (1-12) und Jahreszahl einzugeben.
Über diese beiden Werte generiert Excel dann in Spalte A ab Zelle A6 mit Hilfe der Formeln:
A6: =DATUM(B3;A3;1) Ergebnis: 01.12.22
A7: =DATUM(B3;A3;2) Ergebnis: 02.12.22
A8: =DATUM(B3;A3;3) Ergebnis: 03.12.22
usw...

Als nächsten Schritt habe ich dann den Daten ihren jeweiligen Wochentag zugeordnet (=TEXT(A6;"tttt")... Das ganze findet sich in Spalte B ab Zelle B6.

Jetzt reicht es, dass ich in den Zellen A3 (Monat) und B3 (Jahr) die Daten ändere, so bekomme ich Datum + Wochentag in den Spalten A und B automatisiert.

In den Spalten C+D werden nun Vormittagsschichten und den den Spalten E und F Nachmittagsschichten aufgeführt. Mittels DropDown Auswahllliste können die Mitarbeiter:innen einfach zugeordnet werden. Der Schichtplan ist so erstellt.

Um einen Überblick über die geleisteten Stunden zu bekommen, muss ich das ganze jedoch nun auch "Live" auswerten und in eine kleine tabellarische Übersicht packen.

Ich habe also neben den Schichtplan eine weitere Tabelle angelegt, in der jeder Mitarbeiter eine Zeile bekommt.

In der Spalte H stehen die Namen
In der Spalte I die Soll Stunden
in der Spalte J die Vormittage
in der Spalte K die Nachmittage
in der Spalte L die Samstage

Wie bekomme ich es nun hin, dass er automatisch zählt, wie viele Vormittage, Nachmittage und Samstage die jeweilige Person gearbeitet hat?

Ich habe es mit ZählenWenn, ZählenWenns, Summenprodukt versucht, bekomme aber entweder einen #Wert fehler oder er wendet die Formel erst gar nicht an, sondern meckert wegen Formatierungsfehlern...

Ich bastel nun schon einige Stunden dran herum und komm einfach nicht weiter.

Über Hilfe würde ich mich sehr freuen.

LG

PS: Sollte es zusätzlich noch eine Möglichkeit geben die Zellen der Spalten C bis F zu schwärzen, sofern in Spalte B in der jeweiligen Zeile ein Sonntag steht, wäre das auch grandios.

 

Sorry für den Doppelpost... das automatische Einfärben von Zeilen die ein Sonntag sind, habe ich mittlerweile hinbekommen. Die Auswertung krieg ich leider immer noch nicht hin.

 

Hola,
lade bitte ein Exceldatei hoch.
Gruß,
steve1da

 

Moin!
Wenn Du schon ein aktuelles Excel hast, solltest Du auch dessen Möglichkeiten nutzen.
Die Liste der Monatstage/Wochentage erschlägt man dann mit einer einzigen Formelzelle.
Excel spillt dann die Liste automatisch:

	A	B
3	2	2024
4	01.02.2024	Do
5	02.02.2024	Fr
6	03.02.2024	Sa
7	04.02.2024	So
8	05.02.2024	Mo
9	06.02.2024	Di
10	07.02.2024	Mi
11	08.02.2024	Do
12	09.02.2024	Fr
13	10.02.2024	Sa
14	11.02.2024	So
15	12.02.2024	Mo
16	13.02.2024	Di
17	14.02.2024	Mi
18	15.02.2024	Do
19	16.02.2024	Fr
20	17.02.2024	Sa
21	18.02.2024	So
22	19.02.2024	Mo
23	20.02.2024	Di
24	21.02.2024	Mi
25	22.02.2024	Do
26	23.02.2024	Fr
27	24.02.2024	Sa
28	25.02.2024	So
29	26.02.2024	Mo
30	27.02.2024	Di
31	28.02.2024	Mi
32	29.02.2024	Do
33
34

Zelle	Formel
A4	=LET(Monats_Tage;DATUM(B3;A3;SEQUENZ(TAG(DATUM(B3;A3+1;0)))); HSTAPELN(Monats_Tage;TEXT(Monats_Tage;"TTT")))

Gruß Ralf

 

Und als kleine Spielerei:
Auflistung der Feiertage im entsprechenden Monat per Formel.
Der Arbeitnehmer kann sich schon mal auf Mai 24 freuen.
5 Feiertage, davon 4 in der Arbeitswoche:

	A	B	C
3	5	2024
4	01.05.2024	Mi	Mi,01.05.24
5	02.05.2024	Do	Do,09.05.24
6	03.05.2024	Fr	So,19.05.24
7	04.05.2024	Sa	Mo,20.05.24
8	05.05.2024	So	Do,30.05.24
9	06.05.2024	Mo

Zelle	Formel
A4	=LET(Monats_Tage;DATUM(B3;A3;SEQUENZ(TAG(DATUM(B3;A3+1;0)))); HSTAPELN(Monats_Tage;TEXT(Monats_Tage;"TTT")))
C4	=LET( FFT;DATUM(B3;1;{1;121;276;305;359;360})+WENN(MONAT(DATUM(B3;2;29))=2;{0;1;1;1;1;1}); OFT;RUNDEN((TAG(MINUTE(B3/38)/2+55)&".4."&B3)/7;)*7-6+{-2;0;1;39;49;50;60}; FT;SORTIEREN(VSTAPELN(FFT;OFT)); FILTER(FT;MONAT(FT)=A3;"leider keiner"))

 

Oh wow, vielen Dank für die Hilfe bisher. Die Formel hab ich mal eingebaut, macht das ganze noch einfacher.

Ich lad die Excel mal in Ihrem aktuellen Stand hoch :)...

Mit den Feiertagen ist auch interessant, richtig interessant wäre es, wenn man diese automatisch im Schichtplan "schwärzen" könnte :O, wie ich es momentan bei Sonntagen und an Samstagen für 2 der 4 Schichten gemacht habe.

LG

 

Moin!
Ich habe Dir mal meine Übungsdatei hochgeladen.
Wem meine bedingten Formatierungen und Formeln reichen, hier mit dem Tool:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1											Mi,01.05.24	2020	1
2											Do,09.05.24	2021	2
3	5	2024									So,19.05.24	2022	3
4	Mi	01.05.2024									Mo,20.05.24	2023	4
5	Do	02.05.2024									Do,30.05.24	2024	5
6	Fr	03.05.2024										2025	6
7	Sa	04.05.2024										2026	7
8	So	05.05.2024										2027	8
9	Mo	06.05.2024										2028	9
10	Di	07.05.2024										2029	10
11	Mi	08.05.2024										2030	11
12	Do	09.05.2024											12
13	Fr	10.05.2024
14	Sa	11.05.2024
15	So	12.05.2024
16	Mo	13.05.2024

Zelle	Formel
K1	=LET( FFT;DATUM(B3;1;{1;121;276;305;359;360})+WENN(MONAT(DATUM(B3;2;29))=2;{0;1;1;1;1;1}); OFT;RUNDEN((TAG(MINUTE(B3/38)/2+55)&".4."&B3)/7;)*7-6+{-2;0;1;39;49;50;60}; FT;SORTIEREN(VSTAPELN(FFT;OFT)); FILTER(FT;MONAT(FT)=A3;"leider keiner"))
L1	=SEQUENZ(11;;2020)
M1	=SEQUENZ(12)
A4	=LET(Monats_Tage;DATUM(B3;A3;SEQUENZ(TAG(DATUM(B3;A3+1;0)))); HSTAPELN(TEXT(Monats_Tage;"TTT");Monats_Tage))

Zelle	bedingte Formatierung...	Format
A4	1: ZÄHLENWENNS($K:$K;$B4)	abc
A4	2: (WOCHENTAG($B4;2)>5)*($B4<>"")	abc
C4	1: ZÄHLENWENNS($K:$K;$B4)	abc
C4	2: (WOCHENTAG($B4)=1)*($B4<>"")	abc

Zelle	Gültigkeitstyp	Operator	Wert1	Wert2
A3	Liste		=M1#
B3	Liste		=L1#

Gruß Ralf

 

Hallo Ralf,
vielen Dank dafür, ich verstehe aber nicht so ganz, wie ich damit die Vormittage/Nachmittage und Samstage zählen kann?
Wenn du in die von mir hochgeladene Tabelle schaust, siehst du ja, dass ich rechts vom Schichtplan eine kleine Statistik brauche, auch nach Tagen des Probierens komme ich da immer noch nicht weiter.
Ich kriege es hin Vormittage und Nachmittage zu zählen, jedoch bekomme ich es nicht hin die Samstage da "rauszurechnen" und gesondert zu zählen.

 

So schaut die Datei mittlerweile aus, habe deine Monatsliste als Ausgangspunkt genommen.