Office: Funktion von Summenprodukten aus mehreren Matrizen verkürzen

Hallo liebe Community,
ich benötige Hilfe zur Verkürzung einer Funktion. Es handelt sich um mehrere Summenprodukte, die aus 18 Matrizen (Kalender) gesammelt werden. Die Funktion funktioniert zwar, aber sie ist eindeutig zu lang. Ich könnte mir gut vorstellen, dass man die einzelnen Summenprodukte verkürzt zusammenführen kann. Da Excel die Bezüge bereits nicht mehr anzeigt und nur noch die Klammern farbig dargestellt werden, wird die Funktion kurz vor der maximalen Zeichenlänge sein. Dabei handelt es sich um die Zellen, wo die Stunden zusammengerechnet werden. Die Matrizen umfassen jeweils die Zyklusangaben und Datumsfolgen des jeweiligen Monats. Ich habe Excel 360, geschweifte Klammern sind, so glaube ich, nicht mehr notwendig. Es gibt sicher einen Weg, diese Funktion eleganter zu bewerkstelligen. Vielen Dank schon mal!

 

Hallo,

ganz ehrlich? Das Problem liegt nicht in den Formeln, sondern in deinem Dateiaufbau.

 

Du hast damit nicht unrecht, wäre der Kalender anders angeordnet, würde es dieses Problem nicht geben. Aber er soll so angeordnet sein und somit hilft mir das leider nicht.

 

Hallo,

hatte ich mir schon fast gedacht, Design geht vor Funktionalität, kennt man schon.
Dann beschreibe doch bitte mal, was es mit dem Zyklus auf sich hat, meiner Ansicht nach kann man da ansetzen.

 

Erst einmal danke für die rasche Antwort. Ok, mach ich. Auch wenn mir noch nicht klar ist, was die Zyklus-Angabe damit zu hat.
Die Zyklusangabe zeigt einen Zyklus an, der an einem bestimmten Stichtag beginnt. Sie ist aus verschiedenen Gründen Personel individuell, daher kann man ihn auswählen (siehe Datei). Der Zyklus verläuft, je nach Arbeitsmodell, ebenfalls mit unterschiedlicher Dauer. Daher kann man auch die Zyklusdauer auswählen. Sie zeigt also an, wie oft der Zyklus bereits durchlaufen wurde und an welcher Stelle an welchem Tag der Zyklus aktuell ist. Nach Ablauf eines Zyklus werden die Stunden verrechnet und genullt. In der nächsten Spalte rechts davon würden nach einem ähnlichen Prinzip die tatsächlichen Arbeitsstunden berechnet. Diese ist aber noch nicht vorhanden. Die Zeiten werden anschließend gegengerechnet. Das alles hat damit zu tun, dass das Programm "PuZman", um das es hier geht, entgegen des eigentlichen Arbeitszeitmodells mit einer 5-Tage-Woche rechnet. Ich arbeite aber in einem ganz anderem Rhytmus. Daher die anschließende Gegenrechnung. Ich hoffe, dass ich das verständlich erklärt habe. Zudem - da die Funktion prinzipiel ja funktioniert, geht es mir aber in erster Linie darum, generell das Problem zu lösen, mehrere Matrizen in dieser Art von der Funktion her geschmälert zusammen führen zu können, da ich mit Sicherheit des Öfteren auf diese Problematik treffen werde.
Bevor ich es vergesse: Bitte keine VBA-Lösungen, wäre schade um die Mühe. Zudem ebenfalls keine Hilfszeilen oder versteckte Spalten. Das wäre zwar ebenfalls eine Lösung, ändert aber dennoch nichts daran, für die eigentliche Zielsetzung eine Lösung zu haben.

 

Vielleicht hilft es auch der Übesicht, wenn man sich die Formel "zurecht schneidet" und beispielsweise nur bis Ende der 2. Matrix und den letzten Teil rauskopiert. Dann wären die Bezüge deutlicher zuzuordnen. Die einzelnen Summenprodukte unterliegen immer dem gleichen Schema. Es ändern sich lediglich, je nach Monat, die Bezugsreihen.

 

Hallo Roger,

werde mir das noch mal in Ruhe ansehen, heute komme ich allerdings nicht mehr dazu, weil ich gleich weg muss.

 

Hallo Roger,

eine erste Erkenntnis habe ich, offenbar werden in der Spalte mit den Gesamtzeiten immer pro Arbeitstag 7:42 Stunden zugerechnet, warum braucht man dafür ein so komplizierte Formel? Was habe ich da übersehen?

 

Grüß Dich Klaus-Dieter,
das ist richtig. Es werden pro Werktag 7:42 addiert. Allerdings wird dies nur so lange gemacht, bis ein neuer Zyklus beginnt (bereits weiter oben erwähnt). Die Zählerleiste dient also nicht nur zur Zierde, sondern ebenfalls als Prüfzahl. Da dier Zyklus individuell beginnen kann und je nach Modell auch lang ist, ist es zwingend, dass bei jedem Datum eine Abfrage stattfindet.

 

Wenn ich mal kurz die Formel übersetzte, passiert Folgendes:
1. Zeile: prüft ob Datum vor der Stichtag liegt, wenn ja dann fängt der Zähler mit 1 an, wenn nein, dann mit -1 Ich wollte einen 0-Durchlauf des Zyklus vermeiden.
2. Zeile: prüft ob es dass Datum überhaupt gibt. Ich ziehe die Matrix generell bis zum 31. des Monats & prüft ob der Tag Mo-Fr ist.
Das ist erstmal die Hauptprüfung, um tätig zu werden. Wenn alles passt, dann zählt er die Summenprodukte aller 18 Monate zusammen. Die Monate werden immer nach dem gleichen Schema berechnet.
Ein Summenprodukt entsteht wie folgt:
TEXT(SUMME(
dann füge Arbeitszeit hinzu, wenn < Sa --> an dieser Stelle fällt mir auf, dass der Wochentag zum 2. Mal geprüft wird. Muss noch mal später schauen. Erstmal weiter..
Dann folgt eine Doppelzeile mit Aussage: Prüfe die Zykluszahl für das hiesige Datum und vergleiche sie mit der Zahl, die vor dem "_" in der Zyklusspalte steht. --> auch hier fällt mir auf. Die Spalte wohl doch nur Zierde. Es wird ja auch hier im Summenprodukt geprüft (Die Tabelle ist über mehrere Tage entstanden, da weiß man manchmal nicht mehr, was man da alles gemacht hat.) Gut das ich das noch mal durchgegangen bin.
Zurück zur Doppelzeile: Da dies im Summenprodukt geschiet, werden alle Datumsangaben, die nicht im gleichen Zyklus sind gefiltert.
4. Zeile (Letzte): filtere alle Summen, dessen Datum größer als das hiesige ist heraus.

Somit entsteht Folgendes:

Summiere alle Werktage mit der angegebenden Arbeitszeit, die sich im gleichen Zyklus befinden
und <= dem anliegendem Datum liegen.

Allerdings steht die eigentliche Frage, wie oder ob man mehrere Matrizen überhaupt zusammen fassen kann, immer noch als Kernanliegen im Raum ;)

 

Mit meinem Satz:"
Summiere alle Werktage mit der angegebenden Arbeitszeit, die sich im gleichen Zyklus befinden
und <= dem anliegendem Datum liegen."
bin ich eigentlich selbst auf die Lösung gestoßen: Anstatt alle 18 Monate zu summieren, summiere ich den aktuellen Monat nur mit dem Summenprodukt des vorherigen. Und zwar der höhste summierte Zeitwert, welcher sich im Vormonat und im gleichen Zyklus befindet, wie für das Datum der angewählen Zelle. Fehlt immer noch die Erleuchtung, ob unabhängig von dieser Tabelle, Summenprodukte aus mehreren Matrizen zusammengefasst werden können. Komme mir vor, als führe ich hier eine Allein-Unterhaltung. HaHaHa

 

Hallo Roger,

habe noch mal ein paar Fragen:
- zunächst einmal erscheinen die Zeitangaben in einem, sagen wir mal ungewöhnlichen Format ( 23 h : 12 m ) warum ist das so? Das macht die Zeiten zu Texten, damit rechnet es sich nicht so gut.
- die Formeln sind so ziemlich das längste, was ich in dieser Art bisher gesehen habe, ( in rund 30 Jahren Forenarbeit ). Sind die von dir?

 

Hallo Roger,

noch ein Hinweis zum Zellformat: das lässt sich mit diesem benutzerdefinierten Format: hh " h" " : " mm " m" erzeugen. Vorteil: in den Zellen stehen weiterhin Uhrzeiten mit denen man direkt rechnen kann. Das würde die Länge deiner Formeln vermutlich erheblich reduzieren.
Zusatzfrage: die Zeiten stehen in verbundenen Zellen, warum das?

 

Hallo Roger,

noch eine Frage: in Spalte BI steht in der Zeile des Datums 01.12.2021 die Kennzahl 01, beim Beginn des nächsten Zyklus am 28.12.2022 02 usw.. Warum steht vor dem Datum 01.12.2021 -01? Ist der 01.12.2021 der Startpunkt der Berechnungen?
Warum gibt es innerhalb des Kalenders verbundene Zellen? Das ist Murks.

 

Hallo,

ich habe mich ebenfalls an deinen Formelmonstern versucht. Da du Excel365 als Version angegeben hast, habe ich deine Monsterformel aufgespaltet in Monatsspaltenberechnungen mit Hilfe einer LAMBDA-Funktion, die ich im Namensmanager gespeichert habe (Name: Monatswerte):
=LAMBDA(dt;zyk;wtg;me;pg;
SUMMENPRODUKT((WENNFEHLER(WENN(WOCHENTAG(dt;2)<6; wtg;0);0))* N(WENNFEHLER(WERT(TEXTVOR(zyk;"_"));0)=(pg+(pg>=0)))*(N(dt<=me))))
Sie besitzt 5 Parameter:
dt=Array der Datumsangaben für 1 Monat (Stets für 31 Tage);
zyk=Array der Zyklusangaben für 1 Monat (für 31 Tage);
wtg=7,7 (wie in Zellen AI3:AJ3)
me=Datumsangabe für den lfd.Tag;
pg=Differenz des lfd.Tages zum PuZ-Stichtag, wobei diese mit 56*7 dividiert wird und das ganzzahlige Ergebnis als pg-Wert übergeben wird (siehe dazu auch die folgende Formel).

Die eigentliche Formel in den PuZman-Spalten "reduziert" sich dann wie folgt (zB. Zellen AO6:AP6):
=LET(ht;AN6; pd;ht-$R$3; pz;$L$3*7; pg;GANZZAHL(pd/pz); pr;REST(pd;pz*2)+1; wt;WOCHENTAG(ht;2);
WENN(ODER(ISTFEHLER(pg+(pg>=0)); ISTFEHLER(wt); WENNFEHLER(wt>5;WAHR));
"";
SUMME(
Monatswerte($AN$6:$AN$36;$AL$6:$AL$36;$AI$3; ht; pg); Monatswerte($AZ$6:$AZ$36; $AX$6:$AX$36;$AI$3; ht;pg); Monatswerte($BL$6:$BL$36; $BJ$6:$BJ$36; $AI$3; ht;pg);
Monatswerte($D$42:$D$72; $B$42:$B$72;$AI$3;ht;pg); Monatswerte($P$42:$P$72; $N$42:$N$72;$AI$3;ht;pg); Monatswerte($AB$42:$AB$72;$Z$42:$Z$72;$AI$3;ht;pg);
Monatswerte($AN$42:$AN$72;$AL$42:$AL$72;$AI$3;ht;pg); Monatswerte($AZ$42:$AZ$72;$AX$42:$AX$72;$AI$3;ht;pg); Monatswerte($BL$42:$BL$72;$BJ$42:$BJ$72;$AI$3;ht;pg);
Monatswerte($D$78:$D$108;$B$78:$B$108;$AI$3;ht;pg); Monatswerte($P$78:$P$108;$N$78:$N$108;$AI$3;ht;pg); Monatswerte($AB$78:$AB$108;$Z$78:$Z$108;$AI$3;ht;pg);
Monatswerte($AN$78:$AN$108;$AL$78:$AL$108;$AI$3;ht;pg); Monatswerte($AZ$78:$AZ$108;$AX$78:$AX$108;$AI$3;ht;pg); Monatswerte($BL$78:$BL$108;$BJ$78:$BJ$108;$AI$3;ht;pg);
Monatswerte($D$114:$D$144;$B$114:$B$144;$AI$3;ht;pg); Monatswerte($P$114:$P$144;$N$114:$N$144;$AI$3;ht;pg); Monatswerte($AB$114:$AB$144;$Z$114:$Z$144;$AI$3;ht;pg)
)/24))
Die ursprüngliche TEXT-Formatierung des Resultates habe ich entfernt und ein DATUM/ZEIT-konformes Zahlenresultat erzeugt, das dann mit Zellformatierung [hh]"h "mm" m";; angezeigt wird.

Die Formel zur Berechnung der "Zykluszahl" habe ich ebenfalls vereinfacht (zB. Zelle AL6):
=LET(ht;AN6;MoAnf;AM$5; pd;ht-$R$3; pz;$L$3*7; pg;GANZZAHL(pd/pz); pr;REST(pd;pz*2)+1;
WENNFEHLER(TEXT(pg+(pg>=0);"00") &
WENN((MoAnf+ZEILE(A1)-1)<=MONATSENDE(MoAnf;0);"_";"") &
TEXT(pr-(pr>pz)*pz;"000");""))

Die Formel für die Monatsüberschriften für 2022 sieht jetzt so aus (zB. Zellen C39:L39):
=LET(dt;D42:D72; dz;C41:L41;
f;LAMBDA(i;INDEX(dz;i));
g;LAMBDA(j;WENNFEHLER(REST(dt-f(j);9)=0;0));
TEXT(f(1);"MMMM JJJJ - ")&SUMME(g(3)+g(5)+g(7)+g(9))&" Schichten")
Was es dabei mit den Januar-Datumsangaben für 2016 auf sich hat zur Berechnung der Schichten-Anzahl in der Monatsüberschrift, entzieht sich meiner Kenntnis (zB. Zellbereich E41:L41).

Die Formel für die Tagesnummer sieht jetzt so aus (zB. Zelle C42):
=LET(MoAnf;C$41; dt;MoAnf-1+ZEILE($A1); WENN(MONAT(MoAnf)=MONAT(dt);dt;""))
Die Formel für die Wochentagsangabe (zB. Zelle D42):
=WENN(C42="";"";C42)
Beide vorgenannten Spalten werden durch Excel-Zellformate angezeigt.

Die Formel, die jeweils vor der Zyklus-Spalte enthalten war, habe ich so vereinfacht (zB. Zelle A42):
=LET(ht;D42;pd;ht-$R$3; pz;63; pg;GANZZAHL(pd/pz);
WENNFEHLER(WENN(REST(pd;pz)=0; pg+(pg>=0);"");""))

Ich bin jeweils von den vorliegenden Formeln ausgegangen ohne tieferes Verständnis, wozu die Formeln wirklich gut sind, und habe diese dann zu vereinfachen versucht.
Modifizierte Datei liegt bei.