Office: Funktion von Summenprodukten aus mehreren Matrizen verkürzen

Helfe beim Thema Funktion von Summenprodukten aus mehreren Matrizen verkürzen in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo liebe Community, ich benötige Hilfe zur Verkürzung einer Funktion. Es handelt sich um mehrere Summenprodukte, die aus 18 Matrizen (Kalender)... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von Roger977, 19. Januar 2023.

  1. Roger977 Neuer User

    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!
     
    Roger977, 19. Januar 2023
    #1
  2. Klaus-Dieter Erfahrener User
    Hallo,

    ganz ehrlich? Das Problem liegt nicht in den Formeln, sondern in deinem Dateiaufbau.
     
    Klaus-Dieter, 19. Januar 2023
    #2
  3. Roger977 Neuer User
    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.
     
    Roger977, 19. Januar 2023
    #3
  4. Klaus-Dieter Erfahrener User

    Funktion von Summenprodukten aus mehreren Matrizen verkürzen

    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.
     
    Klaus-Dieter, 19. Januar 2023
    #4
  5. Roger977 Neuer User
    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.
     
    Zuletzt bearbeitet: 19. Januar 2023
    Roger977, 19. Januar 2023
    #5
  6. Roger977 Neuer User
    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.
     
    Roger977, 19. Januar 2023
    #6
  7. Klaus-Dieter Erfahrener User
    Hallo Roger,

    werde mir das noch mal in Ruhe ansehen, heute komme ich allerdings nicht mehr dazu, weil ich gleich weg muss.
     
    Klaus-Dieter, 19. Januar 2023
    #7
  8. Klaus-Dieter Erfahrener User

    Funktion von Summenprodukten aus mehreren Matrizen verkürzen

    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?
     
    Klaus-Dieter, 19. Januar 2023
    #8
  9. Roger977 Neuer User
    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.
     
    Roger977, 19. Januar 2023
    #9
  10. Roger977 Neuer User
    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 ;)
     
    Zuletzt bearbeitet: 19. Januar 2023
    Roger977, 19. Januar 2023
    #10
  11. Roger977 Neuer User
    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
     
    Roger977, 20. Januar 2023
    #11
  12. Klaus-Dieter Erfahrener User
    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?
     
    Klaus-Dieter, 20. Januar 2023
    #12
  13. Klaus-Dieter Erfahrener User

    Funktion von Summenprodukten aus mehreren Matrizen verkürzen

    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?
     
    Klaus-Dieter, 20. Januar 2023
    #13
  14. Klaus-Dieter Erfahrener User
    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.
     
    Klaus-Dieter, 20. Januar 2023
    #14
  15. Exl121150 Erfahrener User
    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.
     
    Exl121150, 20. Januar 2023
    #15
Thema:

Funktion von Summenprodukten aus mehreren Matrizen verkürzen

Die Seite wird geladen...
  1. Funktion von Summenprodukten aus mehreren Matrizen verkürzen - Similar Threads - Funktion Summenprodukten mehreren

  2. Spezielle Arbeitszeiterfassung für Mitarbeiter

    in Microsoft Excel Hilfe
    Spezielle Arbeitszeiterfassung für Mitarbeiter: Hallo Leute, ich bräuchte spezielle Hilfe. Ich habe versucht die Informationen im blauen Kästchen in Wenn-Funktion umzuwandeln aber am Ende leider ohne Erfolg. Habt ihr Ideen wie ich die Zellen...
  3. Zellbezüge einer Zeile auf unverbundene Zellen erstellen

    in Microsoft Excel Hilfe
    Zellbezüge einer Zeile auf unverbundene Zellen erstellen: Hallo Community, ich habe folgendes Problem: ich möchte die Daten aus einer Spalte in ein anderes Tabellenblatt übertragen (Zellbezüge), nur, dass die Zellen im anderen Tabellenblatt nicht...
  4. Vereinfachung dieser Funktion?

    in Microsoft Excel Hilfe
    Vereinfachung dieser Funktion?: Hallo Zusammen, ich habe zwei Tabellen, wobei in Tabelle X über zwei Spalten und ca.100 Zeilen Artikelnamen + Nummern stehen. In der anderen Tabelle Y stehen in Spalte C die Artikelnamen, dann...
  5. Wenn funktion mit oder Bedingung

    in Microsoft Excel Hilfe
    Wenn funktion mit oder Bedingung: hallo, ich habe folgende Formel: =WENN([@Bezeichnung]="";"";WENN([@Bezeichnung]<>"Großkopie...
  6. Anfänger - Hilfe Excel WENN Funktionen

    in Microsoft Excel Hilfe
    Anfänger - Hilfe Excel WENN Funktionen: Hallo Zusammen, leider bin ich in Excel Funktionen nicht so erfahren und würde euch hierbei um Hilfe bitten, ob folgende Abfragen überhaupt möglich sind. Erstmal zum bisherigen Stand. Ich habe...
  7. SUMMENPRODUKT (Funktion)

    in Microsoft Excel Tutorials
    SUMMENPRODUKT (Funktion): SUMMENPRODUKT (Funktion) Excel für Microsoft 365 Excel für Microsoft 365 für Mac Excel für das Web Excel 2019 Excel 2016 Excel 2019 für Mac...
  8. Summenprodukt mit Teilergebnis ohne volatile Funktionen möglich?

    in Microsoft Excel Hilfe
    Summenprodukt mit Teilergebnis ohne volatile Funktionen möglich?: Guten Tag Zusammen, ich habe vor einiger Zeit eine Excel Datei für eine Auswertung zusammengebaut, eine vereinfachte Form habe ich angehängt. Im Prinzip gibt es folgende Spalten: [A] Lieferant...
  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