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 einer bestimmten Spalte wenn innerhalb eines Monats liegen

    in Microsoft Excel Hilfe
    Anzahl einer bestimmten Spalte wenn innerhalb eines Monats liegen: Hallo, ich habe wahrscheinlich für die Profis ein simples Problem. ich möchte die Anzahl der Einträge aus einer bestimmten Spalte auswerfen, wenn diese innerhalb eines Monats liegen. Die Spalte...
  3. 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...
  4. Anfügeabfrage Anzahl Datensätze bestimmte Zahl

    in Microsoft Access Hilfe
    Anfügeabfrage Anzahl Datensätze bestimmte Zahl: Hallo an Alle, ich hoffe, ich schaffe es deutlich zu schildern, wo mein Problem liegt. In einem Formular habe ich ein Textfeld (txt_AnzINTMA2), in dem eine Zahl berechnet wird. Ich möchte nun...
  5. Anzahl geantwortete Mails in Outlook auslesen und in Excel einfügen

    in Microsoft Excel Hilfe
    Anzahl geantwortete Mails in Outlook auslesen und in Excel einfügen: Guten Tag, ich brauche Codebeispiele, die geantwortete Emails in vergleich zu Anzahl alle Mails in Outlook auslesen kann und als eine Diagramme (Anzahl% für eine Woche) in Excel anzeigen.(z.B ich...
  6. Berechnung nur wenn Zahlen vorhanden

    in Microsoft Excel Hilfe
    Berechnung nur wenn Zahlen vorhanden: Hallo zusammen, nachdem mir hier das letzte Mal so großartig und schnell geholfen wurde, versuche ich mein Glück nochmal. Folgendes Problem bekomm ich einfach nicht gelöst: Beispiel: Zeile 34:...
  7. [EILT] Bestimmte Inhalte einer Zelle in eine andere Zelle kopieren und die Anzahl ausrechnen

    in Microsoft Excel Hilfe
    [EILT] Bestimmte Inhalte einer Zelle in eine andere Zelle kopieren und die Anzahl ausrechnen: Hallo zusammen, ich muss dringend bis Montag eine Liste fertigstellen, dass folgendes beinhaltet: Alle Mailadressen, die in der Zeile A2 sind, müssen untersucht werden und die Mailadressen mit...
  8. unterschiedliche Anzahl Spalten kopieren VBA

    in Microsoft Excel Hilfe
    unterschiedliche Anzahl Spalten kopieren VBA: Hallo, ich weiß nicht weiter. Ich möchte das kopieren von Spalten automatisieren, dabei ist mein Problem, dass die Anzahl unterschiedlich ist. Ich habe ein Tabellenblatt "alle", da sind meine...
  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