Größtmögliche Summe einer Spalte

Moderator: ModerationP

Größtmögliche Summe einer Spalte

Beitragvon SJPX » 15. Mai 2021, 17:14

Hallo ihr Lieben,
ich muss für die Uni eine Excel-Tabelle zu Energieüberschüssen und -Mangeln auswerten. Hierbei muss ich aus einer Spalte mit positiven und negativen Zahlen alle Zahlen aufsummieren, sodass die größtmögliche Summe entsteht. Ich habe keine Ahnung wie ich eine Art "Stop-Bedingung" erstellen soll oder wie man sonst auf die Lösung kommt

Vielen Dank!
SJPX
 

Re: Größtmögliche Summe einer Spalte

Beitragvon steve1da » 15. Mai 2021, 17:23

Hola,

also alle positiven Zahlen addieren?
Dann nimm Summewenn().
Gruß,
steve1da
Benutzeravatar
steve1da
Moderator
 
Beiträge: 31386
Registriert: 24. Sep 2003, 10:54

Re: Größtmögliche Summe einer Spalte

Beitragvon SJPX » 15. Mai 2021, 17:36

Hallo Steve,
nein das reicht für mich nicht, es sollen auch die negativen Werte mitgenommen werden. Beginnend bei dem 1. Wert wird immer der folgende aufaddiert, sowohl positiv als auch negativ und so muss ich die größte Summe bilden die möglich ist, also bis zu dem Punkt ab dem der übrige Rest der Liste als Summe negativ ist.
Ich finds schwierig das auszudrücken hoffe man verstehts

Grüße SJPX
SJPX
 

Beitragvon lupo1 » 15. Mai 2021, 20:29

herber.de/excelformeln/pages/summieren_von_Einzelposten_bis_ein_Betrag_ueberschritten_wird.html

Bei TEILERGEBNIS 9 (=SUMME) vermutlich durch 4 (=MAX) ersetzen. Oder um die kürzere SUMMEWENN(S) Variante möglicherweise mit MAX arbeiten. Oder, falls vorhanden, gleich MAXWENN(S).
lupo1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 10062
Registriert: 25. Okt 2012, 13:38

Re: Größtmögliche Summe einer Spalte

Beitragvon RPP63 » 16. Mai 2021, 08:51

Moin!
Wie wäre es mit 2 bis 3 Beispielen mit Wunschergebnis?
Bitte als .xlsx, dafür musst Du Dich hier kurz registrieren.
Außerdem: Welche Excel-Version wird verwendet?

Gruß Ralf
Benutzeravatar
RPP63
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 11388
Registriert: 19. Okt 2012, 17:41
Wohnort: mitten im Pott

Re: Größtmögliche Summe einer Spalte

Beitragvon SJPX » 16. Mai 2021, 15:49

Moin Ralf,
Angegeben wird Version 2002 von MS Office 365. Habe hier zwei kurze Beispiele für dich, die Original Tabelle besitzt 70000 Zeilen, daher fällt es mir schwer das Ergebnis zu überprüfen selbst wenn ich auf eine vermeintliche Lösung gestoßen bin. Daher hoffe ich auf eine Formel, bei der ich den Lösungsweg nachvollziehen kann

Grüße SJPX
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
SJPX
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6
Registriert: 16. Mai 2021, 15:24

Re: Größtmögliche Summe einer Spalte

Beitragvon GMG-CC » 16. Mai 2021, 16:49

SJPX hat geschrieben:Moin Ralf,
… Habe hier zwei kurze Beispiele für dich, …

Danke für DIESEN Satz, denn ich bin ja nicht Ralf. Sonst hätte ich mir doch glatt den Kopf zerbrechen müssen, wieso, warum, weshalt die jeweilige Formel zum Ergebnis führt.
Gruß
Günther (bekennender Power Query-Enthusiast) :!:

Excel-ist-sexy!
Wenn du keine Excel-Version angegeben hast, gehe ich von >=Excel 2016 aus.
Hilfe zur Selbsthilfe, nicht Komplettlösungen für den kommerziellen Einsatz und das "natürlich" gratis!
Benutzeravatar
GMG-CC
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 4842
Registriert: 12. Nov 2011, 23:17
Wohnort: Geesthacht bei Hamburg

Re: Größtmögliche Summe einer Spalte

Beitragvon Der Ratlose » 16. Mai 2021, 18:07

Hallo,

hier ein Vorschlag mit Hilfsspalten.



Der Ratlose
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Der Ratlose
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 182
Registriert: 03. Feb 2021, 23:20

Re: Größtmögliche Summe einer Spalte

Beitragvon HKindler » 17. Mai 2021, 13:39

Hi,

bei 70.000 Zeilen würde ich aber auf ein Konstrukt wie =SUMME(A$1:A1) verzichten. Damit erzeugst du 70.000 Summenformeln mit im Schnitt 35.000 Summanden. Also 70.000 * 35.000 = 2.450.000.000 Additionen. Durch dabei würden 70.000 Additionen ausreichen, wenn du in C1 =A1 schreibst und in C2 =C1+A2 und dieses dann runter ziehst. Heutige Rechner sind zwar schnell, aber einen Zeit-Faktor von 35.000 wird man sicherlich bemerken.
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6037
Registriert: 04. Jul 2013, 09:02
Wohnort: Schwarzwald

Re: Größtmögliche Summe einer Spalte

Beitragvon slowboarder » 17. Mai 2021, 14:54

Hi
darfs auch VBA sein?

die folgende Benutzerdefinierte Funktion ermittelt dir den Maximalen Wert sowie die Stelle, an welcher dieser eintritt:

Code: Alles auswählen
Function KumuliertesMax(Spalte As Range)
Dim arr
Dim max As Double, sum As Double
Dim PosMax As Long
Dim z As Long
arr = Intersect(Spalte, Spalte.Worksheet.UsedRange).Value

For z = 1 To UBound(arr, 1)
    If VarType(arr(z, 1)) = vbDouble Then
        sum = sum + arr(z, 1)
        If sum > max Then
            max = sum
            PosMax = z
        End If
    End If
Next

KumuliertesMax = Array(max, PosMax)
End Function


in die Zelle kommt dann die Formel =KumuliertesMax(A:A)

Die UDF ist so programmiert, dass ganze Spalten ohne große Performance-Einbussen angegeben werden können.

Ich habe hier die Ausgabe beider Werte als Array realisiert, damit man die Suche nicht doppelt ausführen muss wen man beides braucht.

in Älteren Excelversionen muss man, wenn man beide Werte haben will, zwei nebeneinander liegende Zellen selektieren, die Formel eingeben und mit STRG+SHIFT+ENTER abschließen.
in Excel 365 kann man die Formel normal eingeben, die rechte Nachbarzelle muss frei sein.

wenn man nur einen Wert braucht, kann man sich ja die UDF entsprechend anpassen, dh am Ende dann
Code: Alles auswählen
KumuliertesMax = Max

oder
Code: Alles auswählen
KumuliertesMax = MaxPos



oder man wählt über INDEX den Wert aus der Origninal-Formel:
Max-Wert: =Index(KumuliertesMax(A:A);1)
Position: =Index(KumuliertesMax(A:A);2)

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 28190
Registriert: 18. Apr 2009, 13:33

Re: Größtmögliche Summe einer Spalte

Beitragvon SJPX » 31. Mai 2021, 12:53

Vielen Dank ihr habt mir sehr weitergeholfen!
Die VBA funktioniert einwandfrei und ist für mich als Laie sehr charmant. Ich habe mich daraufhin gefragt ob eine solche Lösung auch für die größtmögliche negative Summe aufeinanderfolgender Werte möglich ist, bin jedoch an der Umsetzung gescheitert, das Problem besteht darin , dass ich hier nicht nur den letzten Wert nicht kenne, sondern auch der Anfangswert unbekannt ist.
Wäre eine gute Ergänzung für meine Hausarbeit

Vielen Dank SJPX
SJPX
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6
Registriert: 16. Mai 2021, 15:24

Re: Größtmögliche Summe einer Spalte

Beitragvon Der Ratlose » 31. Mai 2021, 19:20

Hier wieder ein Vorschlag mit Hilfsspalten...




Der Ratlose
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Der Ratlose
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 182
Registriert: 03. Feb 2021, 23:20

Re: Größtmögliche Summe einer Spalte

Beitragvon SJPX » 01. Jun 2021, 09:37

Danke für deine erneut schnelle Antwort,
korrigiere mich wenn ich falsch liege, jedoch stoße ich beim Anwenden deiner Methode auf die Problematik, dass sich Werte doppeln können und die Vergleich-Funktion gibt mir nur die erste Stelle an der der Wert Auftritt an, so erhalte ich nicht das richtige Ergebnis :(

LG SJPX
SJPX
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6
Registriert: 16. Mai 2021, 15:24

Re: Größtmögliche Summe einer Spalte

Beitragvon Der Ratlose » 01. Jun 2021, 10:09

Hallo,

ich erkläre dir die Formeln sehr gern. Entschuldige, wenn es zu ausführlich wird, aber ich weiß ja nicht genau, an welcher Stelle du nicht mehr mitkommst...

Ich nehme als Beispiel die rechte Seite...

In Spalte F habe ich erst einmal die ganzen negativen Werte ermitteln lassen. Nun brauchte ich die Reihenfolge dieser Werte (Spalte G), weil du ja nicht einfach nur die ganzen negativen Werte addiert haben wolltest, sondern den größten Wert. In Zelle H26 habe ich nun die Summe von der kleinsten bis zur größten Zeile berechnen lassen. (sprich: Summe von F9:F20 = -1753,50) In Zelle H27 dann Summe von F10:F20 = -1418,75 usw.
Ich hatte nämlich ursprünglich eine Tabelle mit noch mehreren Hilfsspalten erstellt, weil ich dachte, das die größtmögliche negative Summe von Zeile F13:F16 sein müsste.(weil ja in F17 ein positiver Wert steht...) Das Ergebnis ist aber nur -2592,50.
Bei der Überprüfung ist mir dann aufgefallen, das die größtmögliche negative Summe dann tatsächlich von F13:F20 ist.(-2694)


Der Ratlose
Der Ratlose
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 182
Registriert: 03. Feb 2021, 23:20

Re: Größtmögliche Summe einer Spalte

Beitragvon SJPX » 01. Jun 2021, 12:35

Hallo nochmal,
ich hab mir die Formeln jetzt alle einmal angeguckt mit den Erklärungen die MS liefert und deiner Erklärung und denke ich hab die Vorgehensweise verstanden, jedoch als ich die Formeln auf meine Excel-Tabelle übertragen habe hat mich das Ergebnis stutzig werden lassen und ich habe mich nochmal unserer Beispiel-Tabelle gewidmet um es besser zu verstehen. Ich habe ein paar Änderungen an den Zahlenwerten, nicht an den Hilfstabellen, vorgenommen, bei Tabelle 1 ist das vorher angesprochene Problem aufgetreten, dass "=Vergleich" nur den 1. passenden Wert aus Spalte A wiedergibt und bei der zweiten Tabelle fiel mir auf, dass ich mit der Formel immer bis zum letzten negativen Wert alles addiere, was jedoch hier zu einem kleineren Wert als dem Größtmöglichen führt. Ich brauche also eine zweite Bedingung die sicherstellt, dass der aktuelle Summand negativ ist oder alle Werte bis zum letzten Summanden in Summe kleiner null sind.

Danke für deine Mühen

SJPX
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
SJPX
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6
Registriert: 16. Mai 2021, 15:24


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 20 Gäste