Microsoft Excel

Herbers Excel/VBA-Archiv

Formel: Zellen summieren bis Null | Herbers Excel-Forum


Betrifft: Formel: Zellen summieren bis Null von: Chris
Geschrieben am: 18.01.2010 14:03:12

Hallo zusammen,

hoffe ihr könnt mir bei folgendem Problemchen helfen. Ein Beispiel findet ihr anbei.

Ich habe einmal ein paar Ursprungswerte (Zeile 6), die entweder 0 sind oder eine beliebige Zahl annehmen.
Zusätzlich habe ich Delta-Werte (Zeile 5), die diese Ursprungswerte nach einer bestimmten Logik verändern sollen:

- Ist der Ursprungswert = 0, so bleibt er auch 0, egal welchen Wert der Delta-Wert bestitzt
- Ist der Ursprungswert > 0, so werden der Delta-Wert in derselben Spalte sowie alle Delta-Werte in den folgenden Spalten, in denen der Ursprungswert = 0 ist, zum Ursprungswert addiert.

Bsp.:
Zum Ursprungswert in Spalte D (660) wird addiert: +0 + 100 + 50 + 0 = 810
In Spalte H folgt dann wieder ein Ursrpungswert ungleich Null, deshalb wird das dortige Delta nicht zum Wert in D addiert.

Im Bsp-File anbei habe ich das gewünschte Ergebnis hingeschrieben, allerdings suche ich dafür eine Formel, die die Rechenweise automatisiert.
Jemand Ideen?

https://www.herber.de/bbs/user/67283.xls

  

Betrifft: AW: Formel: Zellen summieren bis Null von: Christian
Geschrieben am: 18.01.2010 14:57:15

Hallo Chris,

als Ansatz folgende Arrayformel in B8 und rüberkopieren:
{=(B6<>0)*SUMME(INDIREKT(ADRESSE(5;SPALTE();;;)&":"&ADRESSE(5;MIN(WENN(C5:$N$5=0*C6:$N$6; SPALTE(C5:$N$5)));;;))) +B6}

Abschluss der Formel mit Strg+shift+Enter!
MfG Christian


  

Betrifft: AW: Formel: Zellen summieren bis Null von: Chris
Geschrieben am: 18.01.2010 15:16:38

Coole Formel! Versteh sie zwar nicht zu 100%, aber muss ich mir mal näher anschauen.

Allerdings ist sie nicht ganz korrekt: Wenn ich die Formel aus B8 rüberkopiere, addiert die Formel in Spalte J 200, obwohl eigentlich 0 addiert werden sollte.
Da in K der Ursprungswert ungleich 0 übernimmt dieser schon die 50 +50 +100 = 440

Wo liegt der Fehler?


  

Betrifft: geht bestimmt kürzer von: Christian
Geschrieben am: 18.01.2010 17:08:45

Hallo Chris,

die Formelgurus werden garantiert kürzere Varianten haben:

als Ansatz;
{=(B6<>0)*SUMME(INDIREKT(ADRESSE(5;SPALTE();;;)&":"&ADRESSE(5;MIN(WENN(C5:$N$5=0*C6:$N$6; SPALTE(C5:$N$5)));;;)))+B6-(SUMME(INDIREKT(ADRESSE(5;SPALTE();;;)&":"&ADRESSE(5; MIN(WENN(C5:$N$5=0*C6:$N$6;SPALTE(C5:$N$5)));;;)))*((C6<>0)*(B6<>0)*(B5=0))) }

MfG Christian


  

Betrifft: AW: geht bestimmt kürzer von: Chris
Geschrieben am: 18.01.2010 17:18:15

Lange Formeln sehen beim Chef kompetenter aus ;-)

Vielen Dank, sieh bisher richtig aus!


  

Betrifft: bisschen kürzer von: Christian
Geschrieben am: 18.01.2010 17:19:17

Hallo Chris,

{=(B6<>0)*SUMME(INDIREKT(ADRESSE(5;SPALTE())&":"&ADRESSE(5;MIN(WENN(C5:$N$5=0;SPALTE(C5:$N$5))) )))+B6-(SUMME(INDIREKT(ADRESSE(5;SPALTE())&":"&ADRESSE(5;MIN(WENN(C5:$N$5=0;SPALTE(C5:$N$5)))))) *((C6<>0)*(B6<>0)*(B5=0))) }

MfG Christian


  

Betrifft: AW: bisschen kürzer von: Chris
Geschrieben am: 18.01.2010 17:55:50

Hi Christian,

leider hab ich noch ne kleine Ergänzung:
Und zwar können die Delta-Werte auch negativ sein und sollen deshalb vom Ursprungswert mit der gleichen Logik abgezogen werden.
Wie ginge das?


  

Betrifft: mit der gleichen Formel.. von: Christian
Geschrieben am: 18.01.2010 18:39:42

Hallo Chris,

...sollte es funktionieren, aber vielleicht verstehe ich es auch falsch.
Ansonsten nochmal Bsp.-Mappe mit händischen Ergebnissen und genauer Erklärung!

MfG Christian


  

Betrifft: negative Zahlen mit Bsp. von: Chris
Geschrieben am: 18.01.2010 18:54:05

Beispielfile anbei.
Z.B. errechnet die Array-Formel in Spalte D und J falsche Ergebnisse. Dabei entsteht in D das falsche Ergebnis durch die -50 in G5, während die -50 in E5 richtig in die Berechnung eingeht.


https://www.herber.de/bbs/user/67287.xls


  

Betrifft: unklare Vorgaben von: Christian
Geschrieben am: 18.01.2010 20:25:02

Hallo Chris,

hatte ich schon fast geahnt das sowas langwierig wird, da in der Praxis die Möglichkeiten dann fast unendlich sind. Da ich noch nicht ganz hinter die Logik gestiegen bin warum z.B. in Spalte D 100 abgezogen werden muss, da ja eigentlich die nächste 0 in F steht und bis dahin nur einmal -50 vorkommt, bitte ich Dich, das Du mal alle vorkommenden Möglichkeiten aufzeigst und erklärst.
Bitte nicht falsch verstehen, aber es kam schon oft vor, das mit einer exakten Erklärung die Formel viel einfacher zu gestalten war.
In Deinem Fall gehe ich aber davon aus, das wir ohne Hilfzeilen nicht auskommen werden, da sonst die Verschachtelung der Formel zu groß uns benutzerunfreundlich wird.
Also, mal ausführlich die Regeln beschreiben d.h. was soll gerechnet werden wenn Nachfolger ungleich 0 oder gleich 0 oder wenn Vorgänger den und den Wert hat usw.....

Bis dahin Frage auf offen.

MfG Christian


  

Betrifft: AW: unklare Vorgaben von: Chris
Geschrieben am: 18.01.2010 22:11:37

Estmal habe ich gerade bemerkt, dass ich die Formeln bei den gewünschten Werten nicht angepasst habe. Hier die richtigen hinterlegten Formeln: https://www.herber.de/bbs/user/67292.xls

Ok, ich versuchs mal praxisnaher zu erklären:
Die Ursprungswerte seien aktuelle Produktionsmengen einer Maschine, die Delta-Werte seien Szenario-Werte, die angeben, wie sich die Nachfrage in einem Monat verändern würde.
Nun ist es so, dass die Monate in denen aktuell produziert wird, fix sind. Es wird in Monaten mit Produktionswerten von 0 nicht extra die Maschine angeschmissen, nur weil dort auf einmal ein Sezenario-Wert von ein paar Mengeneinheiten steht.

Deshal soll folgendes berechnet werden:
Ist eine Produktionsmenge (Ursprungswert) gleich 0, so ist auch der gewünschte Wert (entspricht: Szenario-Produktion) gleich 0.
Ist eine Produktionsmenge größer 0, so werden alle Nachfrageveränderungen (Delta-Werte) der nächsten Monate zu dieser Menge addiert (es wird sozusagen vorproduziert), solange in den nächsten Monaten der Produktionswert gleich 0 ist. Es werden also die Delta-Werte bis dahin vorproduziert, wo als nächstes wieder eine Produktionsmenge größer 0 ist.
Entsprechend wird aber auch die Produktionsmenge verringert, wenn die Szenario-Werte negativ sind.

D.h. für deine Frage, warum in D 100 abgezogen wird:
Die Produktionsmenge 660 in Spalte D wird verändert um +0 -50 + 0 -50 = -100, denn die Produktion von 660 ist zu hoch für die Szenario-Nachfrage, es werden 100 Stück weniger gebracht.
Erst in H startet ein neuer Produktionszyklus (770), deshalb werden die Delta aus D, E, F und G berücksichtigt.
Der Produktionszyklus in H dauert 2 Monate: +0 +50 = 770
In I startet ein neuer Zyklus für nur einen Monat: -50 = 910
Und ab K wird vorproduziert bis zum Ende, da ab L alle Ursprungswerte = 0: +50 +50 +100 +0 = 440

Ist das etwas verständlicher? Danke Dir auf jeden Fall für Deine Hilfe!


  

Betrifft: AW: unklare Vorgaben von: Christian
Geschrieben am: 19.01.2010 11:46:35

Hallo Chris,

anbei Datei mit Erläuterungen, hoffe das passt so!

MfG Christian


https://www.herber.de/bbs/user/67313.xls


  

Betrifft: AW: unklare Vorgaben von: Chris
Geschrieben am: 19.01.2010 11:55:11

Sieht super aus, hoffe das jetzt keine Erweiterung meinerseits mehr kommt :-)

Danke für Deine super Hilfe und die Erklärung im File!
Schönen Tag noch


Beiträge aus den Excel-Beispielen zum Thema "Formel: Zellen summieren bis Null"