Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
Formel für: bis ein Wert erreicht ist
Gehe zu Seite 1, 2, 3  Weiter
zurück: Erfassen-Auswertung verschiedener Zellen weiter: Werte nach Datum sortieren Unbeantwortete Beiträge anzeigen
Neues Thema eröffnen   Neue Antwort erstellen     Status: Antwort Facebook-Likes Diese Seite Freunden empfehlen
Zu Browser-Favoriten hinzufügen
Autor Nachricht
Natalie232
Gast


Verfasst am:
22. Jan 2014, 13:18
Rufname:

Formel für: bis ein Wert erreicht ist - Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Hallo Zusammen,

ich benötige eure hilfe bei einen Problem.

wie kann ich Excel folgendes sagen "mindere die Zahlen solange bis ein wert (zum Beispiel 5) erreicht ist.

Anschaulich: In einer Tabelle habe ich in der Spalte D verschiedene Werte, in der ersten Zelle steht eine Zahl die nicht übersteigert werden soll, wird diese überschritten, so soll in der spalte nebenan (E) die zahlen solange abgezogen werden bis die Zahl wieder erreicht ist.

Bsp
D
5
-1
-2
-3
-2

Das Ergibt -8 (die summe der negativen Zahlen), dass heisst insgesamt hätten wie ein Wert von -3 (-8+5). Das wollen wir nicht. Wir wollen diese -3 eliminieren.

In E sollte zum beispiel stehen
E

-1
-2
-2 (Hier wird eine 1 dazugerechnet, um ein gesamtergebnis von 5 zu erreichen)
0 (Hier wird eine +2 dazu gerechnet, da die 5 schon längst wieder erreicht ist)

wäre super wenn jemand helfen kann
Danke euch
Jensolator
Formeln, aber kein VBA


Verfasst am:
22. Jan 2014, 14:16
Rufname: Jens
Wohnort: Rhön


AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Hallo,

tut mir leid, aber ich verstehe nicht einmal ansatzweise, worauf Du hinauswillst... Rolling Eyes

_________________
Viele Grüße,
Jens
lupo1
Tüftler und {}-Sympathisant


Verfasst am:
22. Jan 2014, 14:17
Rufname:

Möglicherweise noch abkürzbar - Möglicherweise noch abkürzbar

Nach oben
       Version: Office 2010

 DE
15 
2  
3-1-1
4-2-2
5-3-2
6-20
7-20

ZelleFormel
E3=MIN(;MAX(-$D$1;SUMME(D$3:D3))-SUMME(D$2:D2))

... hier einmal gegen meine Charles-Williams-Predigt, dass man statt SUMME(D$2:D2) eine Hilfsspalte verwendet!

_________________
MfG Lupo - 11.10.2012-> {MSO 2000 SP3;Win 7} - 28.02.2013-> {MSO 2010;Win 8.1} (beide vorhanden)
Natalie232
Gast


Verfasst am:
22. Jan 2014, 16:02
Rufname:

AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

genau das ist es. Vielen dank.

Aber was kann ich machen wenn ich zusatz bedingungen habe. Also links (in Spalte C) Eine Reihenfolge habe die ich beachten soll.

Zum Beispiel (D und E bleiben wie in dein Beispiel), es kommt eine Spalte C hinzu mit
(ab C3)

    1
    0
    2
    5
    4


Und die Werte sollen nach dem gleichen Prinzip geändert werden, nur das nun die Reihenfolge beachtet werden soll + Steht in C eine 0 darf nichts mit dem Wert von E passiere, d.h es bleibt gleich.

Das heißt: zuerst soll vom größten Wert aus C gekürzt werden, also nach der Reihenfolge: 5,4,2,1. (Vom größten zur kleinsten Zahl) 0 darf nicht gekürzt [Das ist der Joker] werden, egal was passiert. oder(je nachdem was einfacher ist) es soll solande vom oben [also nach der Reihenfolge 1,2,3,5] der Wert aus Spalte D übernommen werden, bis die 5 (D1) erreicht ist, ab dann gekürzt und zwar in abhängigkeit von Spalte C.

Als ergebnis hätte ich dann in E genau das hier (die 5 darf immernoch nicht überstiegen werden, wie bisher):

(ab e3)

    -1 (2. Schritt: In c steht 1, bis zu 5 kann die eins behalten werden)
    -2 (1. Schritt: da 0 nicht geändert werden darf, bleibt die -2 stehen)
    0 (4. Schritt: in c steht 4 (egal ob die 3 fehlt), alles ist voll, also 0)
    0 (5. schritt: in c steht 5 alles ist voll, also 0)
    -2 (3. Schritt: da in c 2 steht, und ich bis zur 5 noch 2 stehen lassen kann)


Ich hoffe ich konnte es einigermaßen verständlich formulieren...
Dankeschön
lupo1
Tüftler und {}-Sympathisant


Verfasst am:
22. Jan 2014, 18:05
Rufname:


AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Das wird komplexer, vermutlich nur mittels RANG oder ähnlichen Funktionen lösbar. Es wäre gut, wenn Du C:D nach C sortieren kannst, damit sich das erübrigt.
_________________
MfG Lupo - 11.10.2012-> {MSO 2000 SP3;Win 7} - 28.02.2013-> {MSO 2010;Win 8.1} (beide vorhanden)
Natalie232
Gast


Verfasst am:
22. Jan 2014, 20:45
Rufname:

AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Hallo,

danke erstmal.

Ja ich habe mir tatsächlich die Rangfunktion kurz angeschaut. Aber nehmen wir an ich kann die Daten sortieren.

Damit hätte ich ein Problem weniger.

Aber ich habe noch ein Problem, die Formel in E bezieht sich auf die obere Zelle. Was nun wenn diese nicht leer ist? Tatsächlich habe ich eine größere Auflistung die von Summenzeilen unterbrochen wird.
Nach "Summe" soll das andere Päckchen (also nun P2 statt P1) betrachtet werden, danach das andere (Summe ist immer der Schluss eines Päckchens) Ich füge eine Tabelle bei zur Veranschaulichung.

Frage: wie kann ich die Formel in E so modifizieren, dass eine Leerzeile nicht notwendig ist. Bzw. diese so ändern, dass die Summenzeile die Formel nicht stört?

Danke dir!







 BCDE
1  5 
2 RangWerteOptimierung
3P10-1-1
4P11-2-2
5P12-3-2
6P12-20
7P13-20
8Summe P1-5  
9P21-5 
10P22-1 
11P23-2 
12Summe P2-3  

ZelleFormel
E3=MIN(;MAX(-$D$1;SUMME(D$3:D3))-SUMME(D$2:D2))
E4=MIN(;MAX(-$D$1;SUMME(D$3:D4))-SUMME(D$2:D3))
E5=MIN(;MAX(-$D$1;SUMME(D$3:D5))-SUMME(D$2:D4))
E6=MIN(;MAX(-$D$1;SUMME(D$3:D6))-SUMME(D$2:D5))
E7=MIN(;MAX(-$D$1;SUMME(D$3:D7))-SUMME(D$2:D6))
C8=WENN(LINKS(B8;3)="Sum";SUMMEWENN(B$3:B8;B7;D$3:D7)+$D$1;"")
C12=WENN(LINKS(B12;3)="Sum";SUMMEWENN(B$3:B12;B11;D$3:D11)+$D$1;"")
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Heinz Schweigert
.


Verfasst am:
22. Jan 2014, 20:50
Rufname: Heinz

AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Hallo Zusammen

Natalie, wie du auf dieses Ergebnis kommst ist mir ein Rätsel. Sollte es nicht so aussehen?

 ABCDEF
1   5  
2  RangWerte nach "Rang"
3  1-1 -1
4  0-2 -2
5  2-3 -1
6  5-2 0
7  4-2 0

ZelleFormel
F3=D3+WENN($D$1+ABS(D3)-SUMMENPRODUKT(($C$3:$C$7>C3)*($D$1+($D$3:$D$7)))<0;0;MIN(ABS(D3);$D$1+ABS(D3)-SUMMENPRODUKT(($C$3:$C$7>C3)*($D$1+($D$3:$D$7)))))


Formel aus F3 nach unten ziehen um die dargestellten Ergebnisse zu erhalten.

Die Formel hat mit Sicherheit noch Optimierungspotential Razz - wobei nicht enthalten ist, dass eine 0 in der "Rang"-Liste keine Änderung bedeutet!

Grüße Heinz
Natalie232
Gast


Verfasst am:
22. Jan 2014, 21:13
Rufname:

AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Hallo Heinz,

Tatsächlich müsste die letzte Änderung -2 sein (in F5), denn bis zur 5 "ist noch eine Einheit möglich".

Die Summen Zeilen stellen nun ein Problem da, ich kann die Formel, die Lupo1 nannte nicht ohne weiteres runterziehen.

Kannst du mir da weiterhelfen?

Vielen Dank
Natalie232
Gast


Verfasst am:
22. Jan 2014, 21:17
Rufname:

AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Die Formel ist Super, (die eigentliche Tabelle ist viel größer)

=MIN(;MAX(-$D$1;SUMME(D$3Very Happy3))-SUMME(D$2Very Happy2))

aber wie man sieht greift sie bei der 2ten Summe auf die Zeile oberhalb der ersten Summe. Das ist kein Problem solange diese Leer ist, das wird sie aber leider nicht sein. Gibt es eine Möglichkeit dieses Problem zu beheben um die genannte Formel trotzdem zu nutzen?

Danke Euch
Heinz Schweigert
.


Verfasst am:
22. Jan 2014, 21:29
Rufname: Heinz

AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Hallo Natalie

Ja, der Fehler ist mir dann auch aufgefallen - nachdem ich den post abgeschickt hatte Rolling Eyes

Jetzt hab ich mich mehr mit der Behebung dieses Fehlers und Verkürzung der Formel beschäftigt. Das kam jetzt dabei raus. Der Vorteil ist halt du musst nicht jedesmal vorher sortieren:

 ABCDEF
1   5  
2  RangWerte nach "Rang"
3  1-1 -1
4  0-2 -2
5  2-3 -2
6  5-2 0
7  4-2 0

ZelleFormel
F3=MAX(D3;D3+MIN(ABS(D3);SUMMENPRODUKT(($C$3:$C$7<=C3)*ABS($D$3:$D$7))-$D$1))


Grüße Heinz
Heinz Schweigert
.


Verfasst am:
22. Jan 2014, 21:39
Rufname: Heinz

AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

... nochmal ich

Wenn dir Lupos Vorschlag genügt, weil sortieren kein Problem darstellt, dann kannst du das ganz einfach lösen, indem du in der ersten Zeile den roten Teil in der Formel wegläßt:
=MIN(;MAX(-$D$1;SUMME(D$3:D3))-SUMME(D$2:D2))
und erst ab der zweiten Zeile die Subtraktion vornimmst (und die Formel nach unten ziehst)
=MIN(;MAX(-$D$1;SUMME(D$3:D4))-SUMME(D$3:D3))

Grüße Heinz
Natalie232
Gast


Verfasst am:
22. Jan 2014, 21:43
Rufname:

AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Hallo Heinz,

die Formel ist echt toll, aber leider habe ich eine große Tabelle und es muss erkennen, dass nach jede Summe, dass Spiel von vorne beginnt. (schau die Tabelle ich schickte bitte an) Leider weiß ich nicht wie ich es realisieren kann...

Kennst du da eine Lösung?

Danke dir
Natalie232
Gast


Verfasst am:
22. Jan 2014, 21:55
Rufname:

AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Zitat:
und erst ab der zweiten Zeile die Subtraktion vornimmst (und die Formel nach unten ziehst)
=MIN(;MAX(-$D$1;SUMME(D$3Very Happy4))-SUMME(D$3Very Happy3)


Das klappt leider nicht, dann erhalte ich in E3 -2, statt -1...
Da ist leider ein Fehler drin
Heinz Schweigert
.


Verfasst am:
22. Jan 2014, 22:09
Rufname: Heinz

AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Hallo Natalie

Das sollte eigentlich schon funktionieren. Du musst das natürlich für jeden Block immer wieder angleichen. Erste Zeile ohne Subtraktion, die nächste und ff mit Subtraktion und natürlich auf den entsprechenden Bereich anpassen.

Wie groß ist denn deine Tabelle? Wenns nicht zu groß ist, dann kannst du im SUMMENPRODUKT die Bedingung aus Spalte B hinzufügen, den Bereich über den gesamten Wertebereich deiner Tabelle ausdehnen, dann funktioniert sie "gruppenspezifisch" Wink

 BCDE
1  5 
2 RangWerte 
3P11-1-1
4P10-2-2
5P12-3-2
6P15-20
7P14-20
8Summe P1-5 -5
9P21-5-5
10P22-10
11P23-20
12Summe P2-3  

ZelleFormel
E3=MAX(D3;D3+MIN(ABS(D3);SUMMENPRODUKT(($B$3:$B$11=B3)*($C$3:$C$11<=C3)*ABS($D$3:$D$11))-$D$1))


Die kannst du nun von der ersten bis zur letzten Zeile ziehen, zeigt natürlich manchmal Unsinn, so wie in der orangen Zelle Laughing

Grüße Heinz
Natalie232
Gast


Verfasst am:
22. Jan 2014, 22:29
Rufname:


AW: Formel für: bis ein Wert erreicht ist - AW: Formel für: bis ein Wert erreicht ist

Nach oben
       Version: Office 2010

Ich habe über 50 Gruppen... Über 2000 Zeilen... Ich finde das groß... Confused Ist das noch "klein" genug?
Neues Thema eröffnen   Neue Antwort erstellen Alle Zeiten sind
GMT + 1 Stunde

Gehe zu Seite 1, 2, 3  Weiter
Diese Seite Freunden empfehlen

Seite 1 von 3
Gehe zu:  
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen

Verwandte Themen
Forum / Themen   Antworten   Autor   Aufrufe   Letzter Beitrag 
Keine neuen Beiträge Excel Formeln: Suche Formel für meinen Urlaubsplaner 1 carlos 2875 11. Apr 2004, 22:23
Klaus-Dieter Suche Formel für meinen Urlaubsplaner
Keine neuen Beiträge Excel Formeln: Formel für Leerzeilen 2 Ursula 2167 24. März 2004, 12:31
steve1da Formel für Leerzeilen
Keine neuen Beiträge Excel Formeln: Formel 1 posti 636 20. März 2004, 15:51
Dick Formel
Keine neuen Beiträge Excel Formeln: SVERWEIS - Hilfe die Formel klappt nicht !!! 3 sabine99 2359 15. März 2004, 16:19
sabine99 SVERWEIS - Hilfe die Formel klappt nicht !!!
Keine neuen Beiträge Excel Formeln: Zahlenvergleichen nud grössten Wert mit Namen anzeigen 3 [WN]Ultima 1537 15. März 2004, 14:26
xpecidy Zahlenvergleichen nud grössten Wert  mit Namen anzeigen
Keine neuen Beiträge Excel Formeln: Brauche Hilfe: SVerweis + Wenn Formel Beispiel 10 Gast 7507 08. März 2004, 18:25
Gast Brauche Hilfe: SVerweis + Wenn Formel Beispiel
Keine neuen Beiträge Excel Formeln: Mathematische Formel gesucht! 2 Gast 1367 05. März 2004, 01:18
lala Mathematische Formel gesucht!
Keine neuen Beiträge Excel Formeln: Platzhalter in deiner SUMMEPRODUKT formel 5 seraph 3464 03. März 2004, 18:26
seraph Platzhalter in deiner SUMMEPRODUKT formel
Keine neuen Beiträge Excel Formeln: Suche ein Formel 4 Unwissender 2147 20. Feb 2004, 09:45
xpecidy Suche ein Formel
Keine neuen Beiträge Excel Formeln: formel für 2 Arbeitsmappen 2 Diana 1135 18. Feb 2004, 18:17
peb formel für 2 Arbeitsmappen
Keine neuen Beiträge Excel Formeln: WENN bzw. SUMMEWENN Formel (Provisionsberechnung) 4 Uli 6094 17. Feb 2004, 09:58
Manfred Bartak WENN bzw. SUMMEWENN Formel (Provisionsberechnung)
Keine neuen Beiträge Excel Formeln: Wenn;Oder, Nichtgleich Formel verschachtelt? 2 nebbio 18743 02. Feb 2004, 17:21
nebbio Wenn;Oder, Nichtgleich Formel verschachtelt?
 

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: Microsoft Project