Variation einer Bedingten Summenproduktformel

Bild

Betrifft: Variation einer Bedingten Summenproduktformel
von: Daniel
Geschrieben am: 09.05.2015 13:36:33

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!

Bild

Betrifft: in einer Matrixfunktion(alitäts)formel ...
von: der neopa C
Geschrieben am: 09.05.2015 18:01:16
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
.. , - ...

Bild

Betrifft: AW: in einer Matrixfunktion(alitäts)formel ...
von: Daniel
Geschrieben am: 09.05.2015 18:29:44
Hallo Werner,
erst einmal Danke für deine Antwort :)
Die Datei ist etwas unglücklich geraten, da ich den Inhalt einfach aus einer Datei die ich nicht hochladen kann rauskopiert habe.
Du hast natürlich recht das ich in der Beispielformel die Spalte A vergessen habe. Für die KW Wochen habe ich die Formel beim hin und herkopieren versaut, und nach dem ich es in Ordnung gebracht habe nicht mehr gespeichert vor dem Hochladen :-/
Die roten % in Spalte müssen mit berücksichtigt sein, da es sich um die Summierten Vorjahreswerte handelt.
B und D sind berücksichtigt, da ich diese benutze um den jeweiligen Materialbeschaffungsstatus bzw. Fakturierungsstatus zu berechnen. Wahrscheinlich hätte ich das auch anders bedingen können :)
Gruß Daniel

Bild

Betrifft: noch immer widersprüchlich ...
von: der neopa C
Geschrieben am: 09.05.2015 18:41:44
Hallo Daniel,
... in Deinem Formelansatz sind die Werte der Spalte D nicht berücksichtigt und müssen es mE nach Deiner verbalen Aufgabenbeschreibung genau so wenig wie die Daten aus Spalte C.
Wie auch immer, stelle doch mal eine Tabelle ein, mit Daten und Deinen (von Dir "händisch" ermittelten) Zielergebniswerten. Dann wird es eindeutiger.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: noch immer widersprüchlich ...
von: Daniel
Geschrieben am: 10.05.2015 08:25:07
Hallo Werner,
beim Schreiben der Beschreibung im Blatt ist mir aufgefallen, dass du natürlich recht hast.
Hier noch einmal die Datei:
https://www.herber.de/bbs/user/97559.xlsx
Ich hoffe, dass ich mich diesmal klar ausgedrückt habe :)
Schon mal vielen Dank für deine Geduld.
Gruß Daniel

Bild

Betrifft: nun klar wie "Klusbrie" ...
von: der neopa C
Geschrieben am: 10.05.2015 11:51:43
Hallo Daniel,
... Du musst jetzt "nur" noch die "trübe" Zelle B13 leeren/löschen und kannst dann mit meiner nachfolgenden Formel (ich nenne diese Matrixfunktion(alitäts)formel), die Du einfach nach rechts ziehend kopierst, die gewünschten Ergebnisse erzielen.

 F
161000

Formeln der Tabelle
ZelleFormel
F16=SUMMENPRODUKT((MMULT($E7:F15+0;INDEX(ZEILE(A1:INDEX(A:A;SPALTE(B1))); )^0)=$B7:$B15)*(F7:F15>0)*$A7:$A15)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Bild

Betrifft: oh ... trüb war/ist nicht B13 sondern O13 owT
von: der neopa C
Geschrieben am: 10.05.2015 12:00:46
Gruß Werner
.. , - ...

Bild

Betrifft: AW: oh ... trüb war/ist nicht B13 sondern O13 owT
von: Daniel
Geschrieben am: 10.05.2015 12:56:46
Hallo Werner,
vielen Dank! das funktioniert super in meinem Beispiel. Kannst du mir jetzt auch noch erklären was du da machst so dass ich es auf meine große Tabelle übertragen kann, und hoffentlich etwas lerne??
Gruß Daniel

Bild

Betrifft: AW: oh ... trüb war/ist nicht B13 sondern O13 owT
von: Daniel
Geschrieben am: 10.05.2015 13:33:34
Oh und ab O13 geht die Formel nicht mehr. Ich glaube darauf wolltest du mich aufmerksam machen, aber ich verstehe nicht warum die Formel da nicht mehr geht... :-/
Gruß Daniel

Bild

Betrifft: das "Trübe" in O13 ist ein Leerzeichen ...
von: der neopa C
Geschrieben am: 10.05.2015 15:09:27
Hallo Daniel,
... und Leerzeichen ist Text und Texte können durch MMULT() nicht "verarbeitet" werden.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: das "Trübe" in O13 ist ein Leerzeichen ...
von: Daniel
Geschrieben am: 10.05.2015 16:00:38
Hallo Werner,
Alles klar jetzt hab ich es auch gefunden... :)
Kannst du mir die Formel auch noch erklären? Ich würde dein Hexenwerk gerne durchdringen :)
Gruß Daniel

Bild

Betrifft: wie meine Formel "tickt" ...
von: der neopa C
Geschrieben am: 11.05.2015 09:07:06
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
.. , - ...

Bild

Betrifft: AW: wie meine Formel "tickt" ...
von: Daniel
Geschrieben am: 13.05.2015 19:10:49
Hallo Werner,
Vielen vielen Dank für die ausführliche Erläuterung und das du dir die Zeit genommen hast diese zu verfassen!!!!
Ich werde mich wenn ich wieder zuhause bin ausführlich damit beschäftigen und mir mit Hilfe deiner Erklärung das Angesprochene Verständnis erarbeiten so das ich das gelernte variabel anwenden und auch weitergeben kann.
Nochmals Danke :-)

Bild

Betrifft: AW: wie meine Formel "tickt" ...
von: Daniel
Geschrieben am: 14.05.2015 10:10:35
Hallo Werner,
Vielen vielen Dank für die ausführliche Erläuterung und das du dir die Zeit genommen hast diese zu verfassen!!!!
Ich werde mich wenn ich wieder zuhause bin ausführlich damit beschäftigen und mir mit Hilfe deiner Erklärung das Angesprochene Verständnis erarbeiten so das ich das gelernte variabel anwenden und auch weitergeben kann.
Nochmals Danke :-)

Bild

Betrifft: freut mich ...
von: der neopa C
Geschrieben am: 15.05.2015 07:45:53
Hallo Daniel,
... solltest Du noch Fragen dazu haben, so wisse, ich bin jetzt ab sofort erst einmal für mindestens eine Woche offline.
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Englische Excelversion?"