Office: (Office 2019) monatsübergreifende Kalenderwochen

Hallo Zusammen,

sitze gerade an einem Problem in Excel und komme nicht weiter.

In Tabellenblatt (Tabelle 2) sollen z.B. unter den Monaten Jan bis Dez und für die entsprechende Anlage die korrekten Monatssollzahlen summiert werden können. Dazu habe ich in Tabelle 1 erfasste Daten (Jahr in B3), KW B6 - B57, sowie die Zahlen pro KW in Spalte F. Leider gibt es übergreifende Kalenderwochen im Jahr, die teils noch verschiedene Monate ansprechen(oder dazu gehören). Die Berechnung wäre also nicht ganz korrekt. Ich möchte entweder mit einer Hilfsspalte (Tabelle 1 Spalte P) die betreffenden Kalenderwochen in 2 Teile aufspalten, die Schichten, die in Spalte D eingegeben werden auch noch darauf aufteilen und die korrekte Schichtleistung berechnen, damit ich sie nach zuordnung zum korrekten Monat summiert in Tabelle 2 darstellen kann. Wenn diese Sache auch einfacher geht, wäre ich für einen Vorschlag sehr dankbar. LG Jan

:)

 

Hallo, kann mir jemand bei der o.g. Problematik behilflich sein?

Vielen Dank.

 

Hallo Jan,

erkläre mal wie du zu der Schichteneinteilung (Spalte R) in KW 17 für April und Mai kommst.

 

Die Spalte R soll die KW Anteile darstellen, die für die z.B. ursprüngliche Schichteinteilung für die GesamtKW, z.B. 21 Schichten für die Monatsübergreifende KW aufzuteilen sind. Beispiel KW 13 2021, zählen von den 21 Schichten noch 9 in den März und 12 Schichten bereits in den Monat April.
Das ist gerade mein Problem, dass ich zwar eine direkte Zuordnung der Schichten zu der KW habe, aber nicht richtig zum zugehörigen Monat.
Komme da irgendwie nicht weiter, daher hatte ich versucht über eine Art Kalender zu berechnen, aber das stellt sich auch als schwierig heraus...

aber Du hast recht, da habe ich einen Fehler gemacht, vorne in Spalte D stehen 11 Schichten drin, durch Osterfeiertage sind weniger Schichten drin.
Aber selbst wenn dort weniger Schichten als 21 eingetragen sind, soll die Formel diese dann nach den Monatstagen aufteilen, würde bedeuten 9 volle Schichten in den März und 2 Schichten in den April...

 

Ich gehe mal von aus, das deine Schichteneinteilung aus dem Tabellenblatt "zur Erklärung" kommt, oder wozu ist diese Tabelle da?

 

Die Scheineinteilung kommt ursprünglich aus der Tabelle 1 Spalte D und in Tabelle 2 sollen die Sollzahlen Tabelle 1 Spalte F für den korrekten Monat summiert werden.
Die Tabelle zur Erklärung habe ich nur erstellt, um zu zeigen, um welche besonderen übergreifenden Kalenderwochen es mir geht, die aufgeteilt werden sollen.

 

So kommen wir nicht weiter.

Nehmen wir KW 17. Deine Aufteilung in Spalte R, 3 Schichten für den April und 15 für den Mai. Schauen wir mal auf den Kalender. Da hat die KW 17 im Jahr 2021 im April 5 Tage und im Mai noch 2 Tage. Wie kommt hier jetzt deine Schichtenzusammenstellung zusammen?

Aus meinem Verständnis im Tabellenblatt "zur Erklärung" ergibt sich, wenn ich alle Werte in den Spalten H bis I addiere, für die KW 17 in Spalte G und Apr in Spalte F steht, ein Wert von 45. Für den Mai ergibt das 8. Das würde, wenn wir beide Werte durch 3 teilen, ungefähr 15 und 3 ergeben, halt nur anders herum als du es hast.

 

ja, da hast Du recht...

ich habe, um nur ein Beispiel zu nennen, das für die KW 13 gemacht...
die Sache ist ja auch nicht ganz ohne..

hat man z.B. nur 10 Schichten geplant, sind das nur 5 volle Tage, und würde dann vielleicht noch nicht mal mehr ein z.B. monatsübergreifendes Wochenende der KW betreffen...

Was wäre denn laut deinen Gedanken, die beste Variante?

Einen Kalender dazunehmen, als Hilfsobjekt oder eher mit Formel aus KW und Jahr, die entsprechend identifizierten übergreifenden KW durch die Schichten teilen?

 

Wenn du erklärst, wie viele Schichten Pro Tag sein können, für jeden Tag der Woche, was passiert an Feiertagen usw. und welche möglichen anderen Fälle kann es geben. Wenn es da einen Plan gibt, dann können wir weiter sehen.

 

Anbei eine taggenaue Aufteilung der KW-Mengen auf die richtigen Monate.

Um aber im Januar 2021 korrekte Ergebnisse zu erhalten, ist es notwendig, die Planung mit KW 53/20 zu beginnen.

Die Berechnungen könnten vermutlich auch recht gut in einer Power Query Abfrage durchgeführt werden.

 

Klasse WS-53!!!
Das ist nahezu perfekt :-)
Ist also abhängig vom Kalenderjahr, mit welcher KW ich starten kann oder muss es immer die 52 bzw. KW 53 zu Anfang sein?

 

Hallo, noch eine Frage, in Tabelle 2 der 2. Januar in den Monatssummenauswertung, würde dieser als Januar 2022 erkannt werden?
Oder müsste ich die Formel dahingehend ändern, dass sie wie in Tabelle 1, die Bezeichnungen 01/21 und 01/22 bekommen?

 

Wenn die letzte Woche des Vorjahres bis in das aktuelle Jahr reicht, dann musst du mit der letzten Woche des Vorjahres beginnen, damit die Januar-Anteile korrekt berechnet werden können.

Du kannst aber auch generell mit der letzten Woche des Vorjahres beginnen, denn wenn das aktuelle Jahr von der letzten Woche des Vorjahres nicht betroffen ist, dann hat dies ja auch keinen negativen Einfluß auf den Januar.

Du kannst auf Tabelle1 fortlaufend führen, denn in Tabelle2 gibt du ja das Jahr vor, für das die du Monatssummen sehen willst.

Und wenn du in Tabelle1 Spalte "L" löschst, kannst du ab "B7" bis Ende makrieren und das Ganze als Tabelle formatieren. Danach kannst du dann die Spalten L bis W ausblenden, da die Formeln dieser Salten in einer formatierten Tabelle automatisch fortgeschrieben werden.

 

Ich habe doch die Monatsermittlung in Spalte "B" bereits so angepasst, damit SUMMEWENNS ind Spalte "D" korrekte Ergebnisse ermittelt.

 

Was fehlt noch, um "nahezu" streichen zu können?

Sehe gerade, dass in B16 von Tabelle2 "Gesamt" oder "Summe" stehen sollte. Und im übrigen habe ich mir angewöhnt, Summen fast immer direkt über der Überschriftszeile zu zeigen, denn dann sehe ich diese, auch bei längeren Tabellen, immer sofort.