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
  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 Super-Moderator
    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. Tabelle mit Pos.-Anzahlen aufblähen zu Tabelle mit Pos.-LfdNr

    in Microsoft Excel Tutorials
    Tabelle mit Pos.-Anzahlen aufblähen zu Tabelle mit Pos.-LfdNr: Inputtabelle: Nr | Maximalwert 1000 | 1 2000 | 1 3000 | 3 4000 | 2 5000 | 6 erzeugt mit =LET( a;A2:B6; x;SCAN(0;SPALTENWAHL(a;2);LAMBDA(a;c;a+c)); y;XVERGLEICH(SEQUENZ(MAX(x));x;-1);...
  3. Eindeutige Anzahl von Werten mit Bedingungen

    in Microsoft Excel Hilfe
    Eindeutige Anzahl von Werten mit Bedingungen: Hallo, ich habe schon alles versucht und viele Beiträge in diesem und anderen Foren gelesen, bin aber leider bisher zu keinem Ergebnis gekommen ;-(. Vielleicht kann mir hier jemand...
  4. Zählenwenns (Anzahl Gewinner innerhalb eines definierten Zeitraums)

    in Microsoft Excel Hilfe
    Zählenwenns (Anzahl Gewinner innerhalb eines definierten Zeitraums): Hallo Zusammen. Bin seit einigen Tagen völlig am verzweifeln. Ich möchte für meine Aktientrades die Anzahl der Gewinner in einem bestimmten Zeitraum (z.B. Oktober 2022)mir anzeigen lassen. Dazu...
  5. Seitenzahlen

    in Microsoft Word Hilfe
    Seitenzahlen: Mein Word-Dokument umfasst ca. 60 Seiten. Gehe ich aber auf "Eigenschaften" steht dort Seitenzahl = 1. Wie kann das sein? Danke für eure Antworten Uwe
  6. Summenprodukt - Teilergebnis bei Filter

    in Microsoft Excel Hilfe
    Summenprodukt - Teilergebnis bei Filter: Hallo, ich schaffe es leider nicht die Summenproduktformel so anzupassen, dass bei gesetztem Filter zB nach Lagertag/Monat) das Ergebnis sich anpasst bzw. die Duplikate nicht mitgezählt werden....
  7. Anzahl gleicher Namen aus Tabelle

    in Microsoft Excel Hilfe
    Anzahl gleicher Namen aus Tabelle: Hallo, ich habe jetzt lang gesucht, aber leider keine Lösung gefunden. Ein abgespacktes Bsp.: Ich habe Eine Tabelle mit dem Namen Zimmerbelegung Siehe Bsp. Ich habe in 4 Jahren auf 4...
  8. 3 Fach Werte eine Spalte zählen

    in Microsoft Excel Hilfe
    3 Fach Werte eine Spalte zählen: Hallo liebe Leute, wie ist der Code wenn ich in einer Spalte alle 3 Fach vorhandenen Werte zähle: [ATTACH] Es müsste 2 rauskommen, da die eins und die zwei 3 Fach vorkommen. Freue mich auf eure...
  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