Office: Wochenweise Gruppieren

Hallo ihr Lieben,

Ich habe folgendes Problem. Ich Sitze an einer Kapazitäten Planung für meine Mitarbeiter.
In Zeile 1 Ab Spalte C befindet sich das Datum aus dem Jahr in A1 und dem Monat in A2.
Jetzt möchte ich in Zeile 41 Wochenweise die Stunden aus Zeile 39 und in Zeile 42 Wochenweise die Stunden aus 40. Natürlich ergibt das für mich nur Sinn wenn sich die jeweilige Formel und oder Formatierung anpasst sobald ich das Jahr oder den Monat ändere.

Mein Problemkind ist im Anhang.

P.S. Falls jemand eine Idee hat wie ich die jeweilige Kalenderwoche als Gruppe darstellen kann wäre ich super Dankbar. (Wie Verbunden und Zentriert)

 

Hi,

C41: =SUMMEWENNS($C39:$AG39;$C$45:$AG$45;C$45)
nach rechts und unten ziehen.

Übrigens solltest du statt KALENDERWOCHE(C1) besser KALENDERWOCHE(C1;21) oder ISOKALENDERWOCHE(C1) verwenden. Sonst erhältst du in manchen Jahren (z.B. letztes Jahr oder 2027) und in fast allen Jahren in KW 1 und der letzten KW falsche Werte.

 

Danke Helmut für die schnelle Hilfe.

Hast du vielleicht noch eine Idee wie ich es hinbekomme das die Zahlengruppen zusammengefasst sind? Also statt Kalenderwoche 1,1,1,1,1,1,1 ich nur eine 1 habe über die breite der Spalten mit 1

 

Hi,

damit die SUMMEWENNS-Formel funktioniert, brauchst du jede 1. Wenn dir das optisch nicht gefällt, dann mach in C45:AG45 eine bed.Form. mit der Formel =C45=B45 und dem Zahlenformat ;;; (3x Strichpunkt). Dann sind die "überzähligen" Wochen nicht sichtbar.

 

Danke, leider funktioniert es nicht in der Zeile 41 oder 42 mit der Anpassung =C41=B45

 

Dann nimm die Formel =C$45=B$45 und als Bereich $C$41:$AG$42;$C$45:$AG$45.

Aber Achtung: Auch hier stehen die Werte dann noch in den Zellen. Wenn du diese also in irgendwelche Rechnungen (z.B. SUMME) einbeziehst, werden sie mitgerechnet. Wenn das nicht passieren soll, dann brauchst du die Formel
C41: =WENN(C$45=B$45;"";SUMMEWENNS($C39:$AG39;$C$45:$AG$45;C$45))
Dafür brauchst du dann die bed.Form. nicht zu ändern.

 

Vielen vielen Dank.
Du bist heute mein Held!

 

Noch mal ein freundliches Hallo in die Runde,

Die Tabelle ist gewachsen und am Ende soll die Summe aus Zeile 42 der jeweiligen Woche im Blatt 'Auswertung' in einen Soll-Ist-Vergleich. Hat da vielleicht nochmal jemand einen klugen Rat?

 

Hallo

Wo liegt das Problem?
Bei mir klappt es prima!
Ich nutze Power Query.

 

Hi,

hier stolperst du über deine vielen Arbeitsblätter. Wieso verwendest du pro Monat ein Blatt? Excel hat genug Spalten um über 40 Jahre zu erfassen.
Verwende nur ein Blatt und du kannst deine Wochenübersicht mit einer simplen SUMMEWENNS-Formel erschlagen.

 

Hallo,

da du nirgends angegeben hast, welche Excel-Version du hast, bin ich von Excel365 ausgegangen.
Falls du diese Version nicht hast, hast du Pech. Ich werde die von mir eingefügten Formeln (im Namensmanager und Arbeitsblatt "Auswertung") sicherlich nicht umschreiben, denn das würde VBA-Programmierung nötig machen.
Falls PowerQuery in Betracht käme, musst du dich an @PIVPQ wenden.

Weiters gibt es 2 Probleme:
1) Im Arbeitsblatt "Auswertung" habe ich auf das Problem hingewiesen, dass es Jahre gibt, in denen zu Beginn und zu Ende des Jahres die gleiche Kalenderwoche enthalten ist.
2) Ferner ist zu beachten, dass ich ein für Österreich lokalisiertes Excel365 habe. Die Besonderheit dabei ist, dass der 1. Monat des Jahres "Jänner" (und nicht "Januar") heißt. Ein für Deutschland lokalisiertes Excel produziert hingegen in einigen Formeln die Monatsbezeichnung "Januar", die dann im Konflikt mit dem Arbeitsblatt "Jänner" steht.

 

Hallo Exl121150,

habe das Wochenende genutzt, Deinen E_365-Formel-Lösung mal ein bißchen näher zu beleuchten, um meine eigenen Kenntnisse zu den neuen Möglichkeiten auf die Sprünge zu helfen. Ich hoffe nicht, daß Du genauso viel Zeit investieren mußtest, um dieses Lösungskonzept zu erstellen, wie ich im Nachgang brauchte, alles
- in die Einzelschritte aufzusplitten
- um Zwischenergebnisse prüfen zu können
- was ja zur Zeit (auch mit den Excel-Labs-Tool) so schwer möglich ist
Mir hat Dein Lösungsansatz gut gefallen und jeder, der sich tiefer mit den neuen Array-Formeln beschäftigen will, solle es mir gleich tun und Zeit investieren.
An der Stelle, wo das jeweilige Monatsende berechnet wird, bist Du nicht E365-konsequent und machst es mit Einzelformeln, obwohl es auch 1 Spillformel gibt:
=MONATSENDE(DATUM(N4;SEQUENZ(13);1);0)
warum allerdings =MONATSENDE(N7#;0) nicht funktioniert, kann ich nicht erklären..
Um die Formel von Auswertung!K7 habe ich 1 zusätzliches Lambda() rum gesetzt und in den Namensmanager verschoben Aufruf sieht jetzt so aus:
=KW_Monate(K4)

Gruß von Luschi
aus klein-Paris

 

Hallo,

Die Funktion MONATSENDE(Datum;OffsetMonate) ist leider - und das ist etwas tückisch - nicht Spill-Range-tauglich. Daher funktioniert das "#" in N7# als erstem Parameter der Funktion nicht. Wird diese Funktion jedoch mit einem Array gefüttert zB. mittels DATUM(N4;SEQUENZ(13);1), so funktioniert sie.

 

Hallo,

gegen das Nicht-Funktionieren von =MONATSENDE(N7#;0) gibt es eine einfache Abhilfe:
=MONATSENDE(--N7#;0)
Die Zahlen im Spill-Bereich N7# werden negativiert und anschließend nochmals negativiert, also handelt es sich insgesamt wieder um die ursprünglich positiven Zahlen - aber diesmal wurde aus dem Spill-Bereich ein Array und siehe da - es funktioniert.
Den gleichen Effekt erzielt man auch mit:
=MONATSENDE(N7#+0;0)
oder
=MONATSENDE(N7#*1;0)

 

Hallo Exl121150,

danke für den Tipp mit --N7# und Schmäh für mich.

Gruß von Luschi
aus klein-Paris