Variation einer Bedingten Summenproduktformel

Daniel · 2015-05-09T13:36:33+00:00

Hallo zusammen, Ich hoffe Ihr könnt mir hier weiterhelfen. Ich versuche eine bedingte Summenprodukt Formel aufzustellen, bei der die Summe nicht null ist wenn die Summe der Bedingungszeile 1 ergibt. Da ich selbst das was ich geschrieben habe kaum verstehe hier ein Beispiel: https://www.herber.de/bbs/user/97552.xlsx wenn die Summe des Jeweiligen Fertigungsstatus in % (blau) eins ergibt soll der Auftragswert Spalte A summiert werden so dass für jede KW die Summe der in dieser fertiggestellten Aufträge erscheint. kann mir hier jemand weiterhelfen? Vielen Dank!

Hallo Daniel,
... ist das von Dir angestrebte durchaus möglich mit einer reinen SUMMENPRODUKT()-Formel nicht. Allerdings kann diese Funktion den "äußeren" Rahmen für die Ergebnisermittlung sein, wenn ...
... zuvor müssten Deinerseits noch die Rahmenbedingungen eindeutiger als bisher dargelegt werden. Dein in der Datei aufgestellte Formelansatz beinhaltet im Gegensatz zu Deiner Aufgabenbeschreibung die Auftragswerte in Spalte A überhaupt nicht. Auch hast Du nur einen in A13 einen solchen angeben.
Deine KW-Angaben beginnen erst in Spalte S und sind exceltechnisch zumindest nicht für das Jahr 2015 berechnet.
Wieso sind Deine Prozentangaben, die vor der Spalte S stehen, nicht einer KW zugeordnet? Sollen diese nun erst ab Spalte S berücksichtigt werden oder schon zuvor und stehen nur Deine KW-Angaben falsch?
Deine Angaben in Spalte E:F sind rot. Und obwohl Du nur die blauen Angaben berücksichtigen wolltest, sind diese in Deinem ( nicht geeigneten) Formelansatz mit berücksichtigt. Wie ist es richtig?
Ebenso sind in Deiner Formel die Angaben in Spalte in B:D berücksichtigt, obwohl die mE gemäß Deiner verbalen Aufgabenbeschreibung keine Rolle spielen.
Wenn ich die vorgenannten Ungereimtheiten außer Acht lassen kann, hätte ich eine Formellösung anzubieten.
Gruß Werner
.. , - ...

Hallo Daniel,
... eine Matrixfunktion(alität)sformel (so nenne ich diese Art Formel) ermittelt ihr Ergebnis wie eine "echte" Matrixformel (als eine, die durch den spez. Formeleingabeabschluß: STRG+SHIFT+RETURN generiert wird und dann durch ein {} um die Formel gekennzeichnet ist).
Grundlegende Erläuterungen dazu findest Du hier: http://www.online-excel.de/excel/singsel.php?f=26 und den dort folgenden Seiten.
Die in meiner Formel durch SUMMENPRODUKT() auszuwertende Matrix wird einerseits auf Basis des Ergebnisses der Matrixfunktion MMULT() (deren Ergebnis ist kein Wert sondern eine Matrix von Werten) und andererseits aus "normalen" Bereichsbezügen wie ... $B7:$B15)*(F7:F15größer0)*$A7:$A15 gebildet. Auf letzteres gehe ich natürlich nicht weiter ein.
Das zweite Argument der MMULT()-Funktion wird aus der Matrixversion der Funktion INDEX() durch INDEX(ZEILE(A1:INDEX(A:A;SPALTE(B1)));) gebildet. Durch die Potenzierung der so gebildeten Matrix mit 0 ergibt sich eine Wertematrix von genauso vielen Einsen, soviel wie Spalten auszuwerten sind (in F16 also zwei, in G16 drei ... ). Um diese Matrix zu ermitteln, nutze ich die Funktion ZEILE() und werte damit den Bereich A1:INDEX(A:A;SPALTE(B1) aus, was in F16 nichts anderes besagt, als Zeile1:Zeile2, in G16 dann Zeile1:Zeile3 ... Die innere INDEX()-Teilformel in diesem Formelteil wird hier im Gegensatz zur äußeren INDEX(ZEILE(...);) in der Bezugsversion der Funktion INDEX()-angewendet. Sie ermittelt mit der "Spaltenzähler"-Teilformel SPALTE(B1) die auszuwertende Bereichsgröße in einer äquivalenten Zeilenanzahl (dies ist zwingend notwendig, dazu siehe Hinweis in der MSO-Hilfe zum 2. Argument der MMULT()-Funktion). Damit ist also die Ermittlung der Wertematrix des 2. Arguments der MMULT()-Funktion erklärt.
Das 1. Argument der MMULT()-Funktion ist mit $E7:F15 ein (je nach Formelstandort variabler) Zellbezugsbereich, dessen Wertematrix durch die Addition mit +0 wertmäßig nicht geändert wird. Die Addition mit 0 ist aber notwendig, weil MMULT() keine Leerzellen verarbeiten kann. Solche gibt es aber aber in den von Dir auszuwertenden Bereich jede Menge. Mittels +0 werden aus den (internen) "Leerwerten" der Matrix einfach nur echte 0-Werte werden. Nur so kann nun MULLT() seine Auswertung vornehmen ohne das hier die Ergebnisermittlung verfälscht wird und damit seine Ergebnismatrix der SUMMENPRODUKT()-Funktion übergeben. Diese ermittelt nun das jeweilige Endergebnis in Abhängigkeit des Standortes der kopierten Formel.
Wenn Du so willst, ist meine Formel also nichts anderes, als eine geschickte Kombination von Excelfunktionen. Meine "Hexerei" besteht lediglich in der winzigen "Zutat" von +0 im 1. Argument zu maßgeblichen auszuwertenden Bereichsdefinition der MMULT()-Funktion. Dessen Notwendigkeit wie Möglichkeit habe ich auch erst vor kurzen "entdeckt".
oT. Erläuterungen zu einer Formel, wie dieser hier, bedürfen immer ein Vielfaches der Zeit, wie eine solche Formel aufzustellen (wenn man es dann kann). Aber sie bringen (wenn sie verstanden werden) auch einen erheblich höheren Nutzwert, als nur die reine Übernahme einer nicht selbst erstellten Formel. Allerdings oft kann und werde ich das aus Zeitgründen nicht tun können.
Gruß Werner
.. , - ...

Variation einer Bedingten Summenproduktformel

Forumthread: Variation einer Bedingten Summenproduktformel

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

	F
16	1000

Variation einer Bedingten Summenproduktformel

Forumthread: Variation einer Bedingten Summenproduktformel

14 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread