Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1128to1132
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1128to1132
1128to1132
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel: Zellen summieren bis Null

Formel: Zellen summieren bis Null
Chris
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

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Formel: Zellen summieren bis Null
18.01.2010 14:57:15
Christian
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
AW: Formel: Zellen summieren bis Null
18.01.2010 15:16:38
Chris
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?
Anzeige
geht bestimmt kürzer
18.01.2010 17:08:45
Christian
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
AW: geht bestimmt kürzer
18.01.2010 17:18:15
Chris
Lange Formeln sehen beim Chef kompetenter aus ;-)
Vielen Dank, sieh bisher richtig aus!
bisschen kürzer
18.01.2010 17:19:17
Christian
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
Anzeige
AW: bisschen kürzer
18.01.2010 17:55:50
Chris
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?
mit der gleichen Formel..
18.01.2010 18:39:42
Christian
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
negative Zahlen mit Bsp.
18.01.2010 18:54:05
Chris
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
Anzeige
unklare Vorgaben
18.01.2010 20:25:02
Christian
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
Anzeige
AW: unklare Vorgaben
18.01.2010 22:11:37
Chris
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!
Anzeige
AW: unklare Vorgaben
19.01.2010 11:55:11
Chris
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

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige