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 Super-Moderator
    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 Super-Moderator

    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 Super-Moderator
    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 Super-Moderator

    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
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 aus Text auslesen

    in Microsoft Excel Hilfe
    Anzahl aus Text auslesen: Hallo zusammen, ich wäre für Hilfe sehr dankbar. Ich suche eine Möglichkeit mit Formeln eine Aufzählung in Textform auszulesen und die Anzahl der Elemente zu zählen. Zur Verdeutlichung hier zwei...
  3. Anzahl der Optionen pro ID

    in Microsoft Excel Hilfe
    Anzahl der Optionen pro ID: Hallo zusammen Ich habe zwei Spalten Kategorie und ID Kategorie kann 1 bis 5 verschiedene Ausprägungen haben pro ID. Ich würde jetzt gerne herausfinden, welche ID nur 1, 2, 3 oder 4 Auspräungen...
  4. Anzahl AutoVervollständigen Liste erhöhen

    in Microsoft Outlook Hilfe
    Anzahl AutoVervollständigen Liste erhöhen: Mir persönlich gefällt die alte Version mit bis zu 50 Einträgen in der AutoVervollständigen Liste besser, als nur die 5 Einträge mit Bild gemäß der Standardeinstellung. Für die Umsetzung habe ich...
  5. Wenn und Formel

    in Microsoft Excel Hilfe
    Wenn und Formel: Hallo Zusammen, könnte mir jemand bei folgenden Problem helfen: Ich benötige eine Excelformel für folgende Situation --> [ATTACH]
  6. Variable Anzahl von Werten in eine Zelle schreiben

    in Microsoft Excel Hilfe
    Variable Anzahl von Werten in eine Zelle schreiben: Hallo, Ich möchte Werte aus verschiedenen Zellen in ein und dieselbe schreiben und dies abhängig davon. ob die Quellzellen gefüllt sind oder nicht. ich habe einen kleinen Code geschrieben, der...
  7. Anzahl Einträge aus einem Listenfeld in einem ungebundenen Textfeld anzeigen

    in Microsoft Access Hilfe
    Anzahl Einträge aus einem Listenfeld in einem ungebundenen Textfeld anzeigen: Hallo zusammen, gibt es eine Möglichkeit die Anzahl an Einträgen aus einem Listenfeld in einem ungebundenen Textfeld anzuzeigen? Vielen Dank im Voraus. Gruß Eisfuchs
  8. Zählen von Anzahl unterschiedlicher Einträge mit 2 Vorbedingungen

    in Microsoft Excel Hilfe
    Zählen von Anzahl unterschiedlicher Einträge mit 2 Vorbedingungen: Hi, ich scheitere im Moment an einer Lösung für folgende Aufgabenstellung. Es sollen die Anzahl unterschiedlicher Einträge einer Spalte ausgegeben werden, wenn zuvor 2 verschiedene Werte in 2...
  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