Office: Anzahl von Schichten im Plan basierend auf Suchkriterien ausgeben

Helfe beim Thema Anzahl von Schichten im Plan basierend auf Suchkriterien ausgeben in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; 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... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von excelnoob1979, 23. Juni 2022.

  1. 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
     
    excelnoob1979, 23. Juni 2022
    #1
  2. steve1da Office Guru
    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
     
    steve1da, 24. Juni 2022
    #2
  3. 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.
     
    excelnoob1979, 24. Juni 2022
    #3
  4. steve1da Office Guru

    Anzahl von Schichten im Plan basierend auf Suchkriterien ausgeben

    =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.
     
    steve1da, 24. Juni 2022
    #4
  5. 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?
     
    Zuletzt bearbeitet: 24. Juni 2022
    excelnoob1979, 24. Juni 2022
    #5
  6. steve1da Office Guru
    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")
     
    steve1da, 24. Juni 2022
    #6
  7. 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.
     
    excelnoob1979, 25. Juni 2022
    #7
  8. steve1da Office Guru

    Anzahl von Schichten im Plan basierend auf Suchkriterien ausgeben

    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.
     
    Zuletzt bearbeitet: 25. Juni 2022
    steve1da, 25. Juni 2022
    #8
  9. 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.
     
    excelnoob1979, 5. Juli 2022
    #9
  10. steve1da Office Guru
    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.
     
    steve1da, 5. Juli 2022
    #10
  11. 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.
     
    excelnoob1979, 5. Juli 2022
    #11
Thema:

Anzahl von Schichten im Plan basierend auf Suchkriterien ausgeben

Die Seite wird geladen...
  1. Anzahl von Schichten im Plan basierend auf Suchkriterien ausgeben - Similar Threads - Anzahl Schichten Plan

  2. Anzahl der Zimmerbelegungen zu einem bestimmten Zeitpunkt

    in Microsoft Excel Hilfe
    Anzahl der Zimmerbelegungen zu einem bestimmten Zeitpunkt: Hallo Forum, ich habe eine Tabelle, in der eine Zimmernummer, ein Belegungsanfang (Datum und Uhrzeit) und ein Belegungsende (Datum, Uhrzeit) steht. Nun möchte ich auswerten, wie oft ein Zimmer...
  3. Zellwert Tabelle1 Bestimmt Anzahl Zeilen in Tabelle2

    in Microsoft Excel Hilfe
    Zellwert Tabelle1 Bestimmt Anzahl Zeilen in Tabelle2: Hallo zusammen, ich bräuchte bitte einmal Eure Hilfe. Habe keine schlaue Idee wie ich das automatisiert lösen kann. Folgend mein(e) Problem/Idee. Tabelle 1 ist eine Bestellliste...
  4. Anzahl eindeutiger Werte mit mehreren Bedingungen

    in Microsoft Excel Hilfe
    Anzahl eindeutiger Werte mit mehreren Bedingungen: Hi zusammen Ich habe nur Formeln mit eindeutigen Werten und einer Bedingung gefunden. Nicht mal ChatGPT gibt mir die korrekte Antwort. Aufgabenstellung: Spalte A = Quartal Spalte B = Artikel...
  5. Anzahl Wochentag zwischen zwei Daten

    in Microsoft Access Hilfe
    Anzahl Wochentag zwischen zwei Daten: Hallo zusammen!!! Brauche ich den Anzahl Wochentags zwischen zwei Daten finden z.B. wie viele Montag wischen 01.11.2023 und 10.11.2023 Vielen Dank im voraus
  6. MR

    in Microsoft Access Hilfe
    MR: Hallo, wer kann mir einen Tipp geben ? wie kann ich in einer Abfrage die Anzahl der Datensätze für mehrere Tabellen anzeigen ? vielen Dank !
  7. Excel Anzahl Anhand Obergrenzen errechnen lassen?

    in Microsoft Excel Hilfe
    Excel Anzahl Anhand Obergrenzen errechnen lassen?: Hallo, ich frage mich gerade, ob Excel das auch könnte, ich wüsste allerdings nicht ob und wie das gehen sollte, deswegen sind meine Erwartungen sehr gering. Wenn wir in unserem Betrieb Bargeld...
  8. Dienstplanung. Anzahl der aufeinanderfolgenden Dienste zählen.

    in Microsoft Excel Hilfe
    Dienstplanung. Anzahl der aufeinanderfolgenden Dienste zählen.: Guten Tag in die Runde, ich bin langsam am verzweifeln und hoffe sehr, dass ihr mir helfen könnt. Ich habe einen Dienstplan bereits erstellt. Pro Blatt ist der jeweilige Monat aufgeführt. Die...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden