Office: Anzahl von Schichten im Plan basierend auf Suchkriterien ausgeben

Hey Leute,

während ich noch darauf warte, dass mein Arbeitgeber endlich unsere Excel 2019 Version auf die neue updated, habe ich mal weiter an dem dynamischen Schichtplan gebastelt.

Um die Schichten für die Mitarbeiter aus dem "Pool" (flexible Arbeitskräfte) so gerecht wie nur möglich zu verteilen, gibt es im Plan einen Reiter "Statistik" der erfassen soll, wieviele Schichten (Früh, Mittag, Spät, Feiertag, Wochenende etc.) Mitarbeiter X in einem bestimmten Monat gearbeitet hat und diese Anzahl als Zahl zurückgeben.

Die Kombi aus "Mitarbeiter im Jahresplan finden - bestimmten Monat - Zusammenzählen der entsprechenden Schicht" macht mir Kopfzerbrechen und ich komm da nicht weiter.

Zudem wurde für den Betriebsrat auf dem Tabellenblatt "Einzelnachweis" auch noch ein Statistik Block hinterlegt, wo er sich innerhalb eines Zeitraumes die Anzahl gewisser Schichten anzeigen lassen kann.

Würde mich über Hilfe freuen und dass ich in Excel wieder was dazu lernen kann.

Grüße
Heiko

 

Hola,
nimm Summenprodukt().
Das FS, MS, NS... so im Jahresplan nicht vorkommen ist gewollt? Wie soll man denn dann die Zahlen ermitteln? Leider fehlt ein Wunschergebnis an dem man sich hätte orientieren können.
Gruß,
steve1da

 

Stimmt, FS sollte F sein, MS M und NS S. Wichtig ist halt, dass entsprechend alle Frühschichten, Mittagschichten, Nachtschichten etc. der Mitarbeiter in dem Monat gearbeitet hat.

Hab mich mal an der von Dir vorgeschlagenen Formel ausprobiert, bin mir aber nicht sicher, was ich bei den Arrays auswählen soll.

 

=summenprodukt((text(Jahresplan!$E$5:$PK$5;"MMMM")="januar")*(Jahresplan!$B$8:$B$55=$B9)*(links(Jahresplan!$E$8:$PK$55;1)=C$5))

Ist allerdings nur geraten weil a) immer noch kein Wunschergebnis b) im Jahresplan bei den Buchstaben oft noch Zahlen stehen, in der Statistik aber nicht.

 

Das mit den Buchstaben bei den Zahlen liegt daran, dass es unterschiedliche Schichten gibt. Zum einen die normale 8 Stunden Frühschicht F8 und die Schichten der flexiblen Kollegen 1F8, 2F8.... mit unterschiedlichen Startzeiten.

Könntest Du mir vielleicht noch kurz erklären, wie diese Formel genau funktioniert, damit ich auch noch was dazu lerne ;-).

Für die einzelnen Schichten, Krank- und Urlaubstage scheint das perfekt zu funktionieren. Doch wie kann ich jetzt Excel abfragen, an wievielen Wochenenden und Feiertagen der Kollege gearbeitet hat und wieviel freie Tage er hatte?

 

Die Formel wandelt die Datumswerte in E5:PK5 in Monatsnamen um und vergleicht das mit "januar".
Gleichzeitig schaut sie, ob die Namen in B8:B55 den Namen aus B9 ff. entsprechen.
Überall wo das der Fall ist, wird im Bereich E8:PK55 der erste Wert von Links mit dem Wert in C5 ff. verglichen. Wenn alle 3 Bedingungen passen, wird 1 gezählt.
Wochenenden würden in der Formel als weitere Bedingung definiert:
.....*(wochentag($E$5:$KP$5;2)>5)*....
Für die freien Tage muss die letzte Bedingung abgeändert werden:
..($E$8:$KP$55=""))
Die Feiertage können berücksichtigt werden, allerdings müsste man dazu etwas umbauen.
In E6:
=WENN(ZÄHLENWENN(tbFeiertage[Feiertag];E5);"x";"")
Die Formel ganz nach rechts ziehen und dann als weitere Bedingung in der Formel:
...*($E$6:$KP$6="x")

 

Wenn ich erweiterte Formel für die Wochenenden bei Samstag und Sonntag einfüge, erhalte ich leider einen Wert-Fehler oder hab ich da jetzt einfach nur nen Denkfehler drin.

=SUMMENPRODUKT((TEXT(Jahresplan!$E$5:$PK$5;"MMMM")="januar")*(Jahresplan!$B$8:$B$55=$B7)*(WOCHENTAG(Jahresplan!$E$5:$PK$5;2)>5)*(LINKS(Jahresplan!$E$8:$PK$55;1)=C$5))

Für die freien Tage hab ich dann, wie du es oben gesagt hattest, die letzte Bedingung abgeändert:
=SUMMENPRODUKT((TEXT(Jahresplan!$E$5:$PK$5;"MMMM")="januar")*(Jahresplan!$B$8:$B$55=$B6)*(LINKS(Jahresplan!$E$8:$PK$55;1)=""))

Jedoch bekommt Excel hier 41 freie Tage für den Monat Januar pro Arbeitskollege heraus, was ja nicht stimmen kann ;-).

Mit den Feiertagen klappt es auch nicht so ganz.

Hab dir mal die veränderte Excel Datei dran gehängt.

 

Sorry, hatte übersehen dass du zwischen den Datumswerten am Monatsende noch TEXT stehen hast. Macht man zwar so nicht, aber was solls:)
=SUMMENPRODUKT((TEXT(Jahresplan!$E$5:$PK$5;"MMMM")="januar")*(TEXT(Jahresplan!$E$5:$PK$5;"TTT")=G$5)*(Jahresplan!$B$8:$B$55=$B6)*(LINKS(Jahresplan!$E$8:$PK$55;1)=C$5))
Das mit den Feiertagen kann auch überhaupt nicht klappen, weil du etwas völlig anderes gemacht hast, als ich vorgeschlagen habe. Ich schrieb: zusätzliche Bedingung *($E$6:$KP$6="x"). Du hast daraus B8:B55="x" gemacht. Warum?

Bei den freien Tagen werden leere Zellen als 1 gewertet, also Januar. Leere Zellen im Datumsbereich hast du durch die schon angesprochenen Zwischen-Überschriften. Außerdem hast du am Ende schon den Januar 2023 in der Tabelle, und die Formel fragt erst mal stur nach "Januar" ab. Hätte auffallen können.
=SUMMENPRODUKT((TEXT(Jahresplan!$E$5:$PK$5;"MMMM")="januar")*(TEXT(Jahresplan!$E$5:$PK$5;"JJJJ")="2022")*(Jahresplan!$B$8:$B$55=$B6)*(Jahresplan!$E$8:$PK$55=""))

Du wirst aber häufiger trotzdem falsche Wert bekommen, weil einige Namen in B8:B55 doppelt vorkommen. Die werden dann natürlich immer addiert. Ist halt dem Aufbau geschuldet.

 

Ok, erstmal Danke für Dein Feedback und Deine Hilfe.

Bin immer über konstruktives Feedback dankbar. Wäre es einfach besser, auch was die weitere "Programmierung" angeht, wenn ich den Text zwischen den Monaten einfach weg lasse, also in den Datumszeilen 4 und 5? Hab den Plan nach einem Video-Tutorial erstellt, was ich eigentlich ganz gut fand, welches aber natürlich die weiteren Funktionen, die ich gerne eingebaut hätte, nicht mit berücksichtigt.

Sollte ich Januar 2023 einfach komplett rausmachen?

Wochenenden
Die Formel =SUMMENPRODUKT((TEXT(Jahresplan!$E$5:$PK$5;"MMMM")="januar")*(TEXT(Jahresplan!$E$5:$PK$5;"TTT")=G$5)*(Jahresplan!$B$8:$B$55=$B6)*(LINKS(Jahresplan!$E$8:$PK$55;1)=C$5)) würde mir jetzt für den Kollegen für den Samstag eine 1 anzeigen. Gearbeitet hat er aber 3

Feiertage

Bedenke bitte, dass ich noch Excel Anfänger bin und mich solche komplexen Formeln erstmal komplett überfordern, wie so eine Art Reizüberflutung ;-).
Wie sollte dann die folgende Formel ausschauen?
=SUMMENPRODUKT((TEXT(Jahresplan!$E$5:$PK$5;"MMMM")="januar")*(Jahresplan!$B$8:$B$55="X")*(LINKS(Jahresplan!$E$8:$PK$55;1)=""))

Freie Tage

Für nen erfahrenen Excel Benutzer bestimmt, für nen Frischling wie mich eher nicht ;-). Also wenn ich die Formel so im Statistik Reiter eingebe, scheint es bei manchen Kollegen zu passen, bei anderen (Borg, Klaus) dann wieder nicht.

 

Das bei Klaus Borg falsche Werte rauskommen hatte ich erwähnt:

Musst du nicht, ich frag ja einfachhalber auch aufs Jahr 2022 ab.
Ich denke, das wird mit dem Aufbau und den Doppelnennungen nicht wirklich was werden. Evtl fällt mir etwas anderes ein, bin aber momentan nicht so flexibel etwas neues zu entwerfen.

 

Beziehen sich jetzt die Doppelnennungen nur auf die Namen der Arbeitskollegen?

Wo genau siehst du das Problem mit dem Aufbau? Aufbau des Jahresplans oder des Reiters Statistik? Bin für Vorschläge immer offen.