Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 12:46:38
Nutzereingaben Speichern und Laden
26.04.2024 11:49:48
VAB und Bedingte Formatierung nimmt die Farbe im Makro nicht
26.04.2024 11:38:46
Acrobat PDF Formularfelder mit Excel ausfüllen
26.04.2024 11:23:04
zählen wenn - aber nur die selektierten Zeilen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
356to360
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
356to360
356to360
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Dynamische Summenberechnung

Dynamische Summenberechnung
27.12.2003 14:47:57
Bongo
Werte Community!

Folgendes Problem:

Ich habe zwei Spalten (A,B) im Excel mit jeweils etwa 4000 Werten.
Ich möchte nun unter folgenden Voraussetzungen eine Summe von Werten errechnen:

Beginnend in einer Spalte soll die Summe der Spalte errechnet werden so lange bis die Summe größer als 5% der vergleichbaren Summe der anderen Spalte ist. Dann soll zur anderen Spalte gewechselt werden.
Das Verfahren beginnt bei A1. Bei A21 wäre die Summe (A1:A21) um 5% größer als (B1:B21). Es wird zu Spalte B gewechselt.Nun wird weitergerechnet solange bis die Gesamtsumme wieder 5% größer als die Summe der Spalte A bis zu einem gewissen Zeitpunkt ist.
Es sei nun bei (B21:B39) um 5% größer als (A21:A39). es wird wieder zu Spalte A gewechselt. Zum Schluß nach oftmaligen Hin und her Springen soll die Gesamtsumme ausgewiesen werden.

Vielen Dank im Voraus

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Dynamische Summenberechnung
27.12.2003 15:02:08
Ramses
Hallo

wo sollen denn die Ergebnisse/Zwischenwerte stehen ?

Gruss Rainer
AW: Dynamische Summenberechnung
27.12.2003 15:07:54
Bongo
Die Zwischenwerte sind nicht so wichtig - können ausgewisen werden oder nicht. Wichig ist schließlich nur ein einziges Ergebnis: Es ist jener Pfad der den einzlnen Summen mit der beschriebenen Eigenschaft folgt.
Noch Offen. Das ist mir zu hoch. :-(
27.12.2003 15:16:49
Ramses
Hallo

Abgesehen vom Crossposting bei Berti und was weiss ich wo sonst noch, habe ich keine Ahnung für was das gut sein soll und was du eigentlich für ein Ergebnis genau erwartest:

"...Beginnend in einer Spalte soll die Summe der Spalte errechnet werden so lange bis die Summe größer als 5%...

"...Zum Schluß nach oftmaligen Hin und her Springen soll die Gesamtsumme ausgewiesen werden..."

"...Die Zwischenwerte sind nicht so wichtig ..."

"...Wichig ist schließlich nur ein einziges Ergebnis: Es ist jener Pfad der den einzlnen Summen mit der beschriebenen Eigenschaft folgt..."

Bei aller Liebe:
Aber da hört mein Abstratktionsvermögen ganz einfach auf.
Verstehst du eigentlich selber was du da haben willst ?

Für alle anderen wäre wahrscheinlich eine Beispieldatei mehr als hilfreich.

Gruss Rainer
Anzeige
AW: Dynamische Summenberechnung
27.12.2003 16:08:34
Reinhard
Hi Bongo,
ich hoff ich hab die 5% richtig berechnet, evtl. überprüfen.
Ansonsten ergibt sich folgende Tabelle in der Wertansicht: 

12	12
2	2
123	1	137
4	4
5	5
6	123	132
7	7
8	8
9	9
10	4	34
303
und in der Formelansicht:
12	12
2	2
123	1	=SUMME(A1:A3)
4	4
5	5
6	123	=SUMME(B4:B6)
7	7
8	8
9	9
10	4	=SUMME(A7:A10)
=SUMME(C1:C10)
bei Benutzng das nachfolgenden Makros.
Gruß
Reinhard
Sub wechselsumme()
letzte = Range("A65536").End(xlUp).Row
Range(Cells(1, 3), Cells(letzte, 3)).ClearContents
anf = 1
ende = 1
spa = 1
While ende <= letzte
sum1 = Application.WorksheetFunction.Sum(Range(Cells(anf, 1), Cells(ende, 1)))
sum2 = Application.WorksheetFunction.Sum(Range(Cells(anf, 2), Cells(ende, 2)))
If spa = 1 Then
Diff = (sum1 - sum2) / sum2
If Diff > 0.05 * sum2 Then
Cells(ende, 3).FormulaLocal = "=Summe(A" & anf & ":A" & ende & ")"
anf = ende + 1
spa = 2
End If
End If
If spa = 2 Then
Diff = (sum2 - sum1) / sum1
If Diff > 0.05 * sum1 Then
Cells(ende, 3).FormulaLocal = "=Summe(B" & anf & ":B" & ende & ")"
anf = ende + 1
spa = 1
End If
End If
ende = ende + 1
Wend
If Cells(ende - 1, 3) = "" Then
Cells(ende - 1, 3).FormulaLocal = "=Summe(" & Chr(spa + 64) & anf & ":" & Chr(spa + 64) & ende - 1 & ")"
End If
Cells(ende, 3).FormulaLocal = "=Summe(C1:C" & letzte & ")"
End Sub

Anzeige
AW: Dynamische Summenberechnung
27.12.2003 16:47:26
Bongo
Hallo Reinhard!

Vielen Dank dafür, daß Du Dir für mein Problem Zeit genommen hast. Aus dem Ausdruck glaube ich zu erkennen, daß Du das Problem richtig erkannt hast.
Mit einem Marko habe ich alelrdings nicht gerechnet, denn ich dachte man modifiziert das ganze im Menü Funktion.
Aber bitte wie impementiere ich das ganze nun in mein Arbeitsblatt? Im Visual Basis Editor?

Wie auch immer mit dem Code ist mir schon sehr geholfen.

Danke nochmals
AW: Dynamische Summenberechnung
27.12.2003 18:30:20
Reinhard
Hi Bongo,
ja, im Editor, in den kommst du mit Alt+F11.
Dort in ein Modul. Es gibt viele Möglichkeiten, ich schildere mal eine die für einen VBA-Anfänger vielleicht die eleganteste ist.

In Excel, über Extrax---Makro---Aufzeichnen zeichnest du ein Makro auf. Einfach OK wenn nach Namen gefragt wird, dann startet die Aufzeichnung.
Du markierst irgendeine Zelle, kopierst sie und fügst sie woanders wieder ein.
Dann auf das Extras---Makro---Aufzeichnung beenden.
Nun auf Extras---Makro---Makros, dort dann links das eben erstellte Makro auswählen und auf 'Bearbeiten' klicken.
Dort das Makro löschen und an dessen Stelle den Makrocode von mir einfügen.
Jetzt kannst du das Edito-Fenster schließen.
Jetzt kann man das Makro immer über Extras---Makro---Makros---Makroname starten.
Oder einem CommandButton das Makro zuweisen...
Gruß
Reinhard


Anzeige
AW: Dynamische Summenberechnung
27.12.2003 23:41:36
Bongo
@reinhard Vvvvvvielen Dank es funktioniert. Mußte zwar die if bedingung abändern aber egal. Könntest Du mir bitte noch sagen, wie ich Code umändern muß um mit mehr als 2 Spalten operieren zu können?
AW: Dynamische Summenberechnung
27.12.2003 23:16:46
Bongo
@reinhard Die Diff definition bzw. die Differenzbedingung passt nicht ganz. Egal, daß bekomm ich hin. wie müsste ich das ganze denn erweitern, wenn ich 3 oder mehrere Spalten habe?

ich könnte dir ja das excel file schicken wenn du es brauchen kannst.

danke lg
AW: Dynamische Summenberechnung
27.12.2003 23:35:35
Bongo
@reinhard Die Diff definition bzw. die Differenzbedingung passt nicht ganz. Egal, daß bekomm ich hin. wie müsste ich das ganze denn erweitern, wenn ich 3 oder mehrere Spalten habe?

ich könnte dir ja das excel file schicken wenn du es brauchen kannst.

danke lg
Anzeige
AW: Dynamische Summenberechnung
28.12.2003 15:20:58
Reinhard 

Hi Bongo,
wie gehabt, überprüf die Prozentrechnung :-)
Gruß
Reinhard
Es ergibt sich
1	1	1	1
2	2	2	2
7	3	3	3	10
4	4	4	4
5	5	5	5
6	16	6	6	25
7	7	7	7
8	8	8	8
9	9	9	9
10	10	10	10
11	11	17	11	51
12	12	12	12
13	13	13	13
14	14	14	14
15	15	15	25	64
16	16	16	16
17	17	17	17
18	18	18	18	51
201
und als Formelansicht:
1	1	1	1
2	2	2	2
7	3	3	3	=SUMME(A1:A3)
4	4	4	4
5	5	5	5
6	16	6	6	=SUMME(B4:B6)
7	7	7	7
8	8	8	8
9	9	9	9
10	10	10	10
11	11	17	11	=SUMME(C7:C11)
12	12	12	12
13	13	13	13
14	14	14	14
15	15	15	25	=SUMME(D12:D15)
16	16	16	16
17	17	17	17
18	18	18	18	=SUMME(A16:A18)
=SUMME(E1:E18)
bei dem Makro:
Sub wechselsumme()
letzte = Range("A65536").End(xlUp).Row
anf = 1
ende = 1
spaA = 1
spaB = 2
spaAnz = 4 'Anzahl der Spalten
Range(Cells(1, spaAnz + 1), Cells(letzte, spaAnz + 1)).ClearContents
While ende <= letzte
sumA = Application.WorksheetFunction.Sum(Range(Cells(anf, spaA), Cells(ende, spaA)))
sumB = Application.WorksheetFunction.Sum(Range(Cells(anf, spaB), Cells(ende, spaB)))
If sumA / sumB > 1.05 Then
Cells(ende, spaAnz + 1).FormulaLocal = _
"=Summe(" & Chr(spaA + 64) & anf & ":" & Chr(spaA + 64) & ende & ")"
anf = ende + 1
spaA = spaA + 1
If spaA > spaAnz Then spaA = 1
spaB = spaB + 1
If spaB > spaAnz Then spaB = 1
End If
ende = ende + 1
Wend
If Cells(ende - 1, spaAnz + 1) = "" Then
Cells(ende - 1, spaAnz + 1).FormulaLocal = _
"=Summe(" & Chr(spaA + 64) & anf & ":" & Chr(spaA + 64) & ende - 1 & ")"
End If
Cells(ende, spaAnz + 1).FormulaLocal = _
"=Summe(" & Chr(spaAnz + 65) & 1 & ":" & Chr(spaAnz + 65) & letzte & ")"
End Sub

Anzeige
AW: Dynamische Summenberechnung
28.12.2003 19:29:02
Bongo
Hallo!

Das ganze funktioniert fast perfekt.
Zwei Kleinigkeiten:
1.) Der Pfad sollte auch nach einmaliger Summe springen (also dem blauen Pfad folgen und nicht dem roten)
2.) Ich würde gerne die Bedingungen verändern. Beispielsweise soll erst gesprungen werden, wenn beide Alternativen also sum1 und sum2 < 1+sum3 springe zu sum1 falls sum1 Daher frage ich mich ob Du ein OnlineTutorial kennst welches mir da weiterhelfen könnte.

Vielen dank nochmals, ich hab Dir meine Datei zur besseren Illustration angehängt.

Gruß Bongo
Anzeige
AW: Dynamische Summenberechnung
29.12.2003 00:20:12
Reinhard
Hi Bongo,
bin jetzt verwirrt, da ich die 'blaue' Logik der Spalten E F G nicht verstehe.
In Zeile 6 ist wohl Bedingung erfüllt also Summe nach H??, wechsel auf F, okay.
In Zeile 7 ist wohl Bedingung erfüllt also Summe nach H??, wechsel auf E, okay.
In Zeile 8 müsste es doch neu losgehen, wieso fliesst da Zeile 7 mit ein?
Genauso der Wechsel zwischen Zweile 12 und 13 , warum?

Ich gehe weiter davon aus:
1- die Daten stehen in A,B,C
2- es wird die Summe in A gebildet, wenn Kriterium erreicht, Summe nach D und Wechsel zu B als Pfad. Dort desgleichen, bei Kriteriumerreichung, Summe nach D und Rückwdchsel zu A als Pfad.
2- C dient nur als Kriterum, in C läuft der Pfad nicht durch!!??

Mir wärs lieb, du färbst mal 20-40 Datenzeilen in A und B blau so ein, dass ich den Pfad erkennen kann und schreibst in D die Summenformeln, nur die bei einem Spaltenwechsel in A oder B, also so wie es mein Makro tat.

:: Beispielsweise soll erst gesprungen werden, wenn beide Alternativen also sum1 und sum2 < 1+sum3 springe zu sum1 falls sum1 ??
Die Sprungbedingungen musst du mir bitte nochmal genauer erklären, meinst du:
wenn sum1 < 1+sum3 AND sum2 < 1+sum3 then

Ich kenne kein Onlinetutorial aber hier bei Herber gibt es genügend zu lernen
Gruß
Reinmhard
Anzeige
Modifikation
29.12.2003 17:46:53
Bongo
Hallo Reinhard!

Ich habe die Datei modifiziert und hochgeladen.

Die Datei https://www.herber.de/bbs/user/2635.xls wurde aus Datenschutzgründen gelöscht


In Spalte A,B und C stehen die relevanten Daten. C ist kein Kriterium - der Pfad soll (kann) auch durch C laufen. Der rot unterlegte Pfad ist derjenige, den Dein Marko geht, wobei das Makro leicht abgeändert wurde, und zwar in: ...if SumA > 1 + sumB... then
In Spalte D-G steht die jeweilige Summe bis zum Punkt an dem gesprungen wird.
Deine Logik ist soweit in Ordnung allerdings springt in Zeile 8 nicht von B nach A zurück, da (B>1+A)(2,77>1+0,2).

Ich will aber, daß der rote Pfad wie der blaue in Spalte H,I und J aussieht, vorausgesetzt das Verfahren beginnt in Spalte A. Dieser besteht aus den einzelnen Summen die vom jeweiligen Sprungpunkt berechnet wurden welche zur besseren Veranschaulichung dienen.
(Vorher sah er ein bisschen anders aus, da ich mich in der Summenberechnung in Zeile 8 vertan habe).

Beachte: Wenn die Summe einer Spalte größer als 1+Summe andere Spalte, so wird in diejenige Spalte mit der kleineren Summe gesprungen. (siehe h9).

Zusammenfassend: Nehme Wert mit geringster Rendite (sum1), tausche gegen billigeren (sum3)falls sum1> 1+sum2 ODER sum1> 1+sum3. Sum3 < sum2

Folgende Varianten sollen weiters berechnet werden:
1.) Nehme Wert mit geringster Rendite (sum1), tausche gegen billigeren (sum3)falls sum1> 1+sum2 UND sum1> 1+sum3.
sum3 < sum2 z.B.:(-43<-23)

2.) Nehme Wert mit geringster Rendite (sum1), tausche gegen billigeren (sum3)falls sum1> 1+sum2 oder sum1> 1+sum3 wobei SUM3>SUM2.

3.) Nehme Wert mit geringster Gesamtrendite (sum1), tausche gegen billigeren (sum3)falls sum1> 1+sum2 oder sum1> 1+sum3 wobei sum3 < sum2.
Die Gesamtrendite errechnet sich immer von (a6:??) und nicht vom jeweiligen Sprungpunkt weg.

Beginnt das Verfahren immer bei Spalte A und kann ich mit dem Makro auch 5 Spalten berechnen?
Ich hoffe Du kriegst das hin, wenn ja, ist Dir eine lobende Erwähnung in meiner Arbeit gewiss.

lg Bongo
Anzeige
AW: Modifikation
29.12.2003 21:46:27
Reinhard
Hi Bongo,
das nachstehende Makro ist auch in https://www.herber.de/bbs/user/2636.xls eingebaut.
Man muss es nur anpassen das läuft es auch über 5 Spalten und es muss nicht A:E sein.
1) hab ich glaub ich verstanden, 3) auch so einigermassen aber 2) noch nicht so ganz.
Gruß
Reinhard


Sub wechselsumme()
Dim su(3) As Integer
Dim sumKL As Single
Dim posKL As Integer
Dim anf As Long
Dim ende As Long
Dim spa As Byte
Application.ScreenUpdating = False
letzte = Range("A65536").End(xlUp).Row
Range(Cells(1, 1), Cells(letzte, 4)).Font.ColorIndex = 0
anf = 6
ende = 6
spa = 1
'spaAnz = 3 'Anzahl der Spalten
Range(Cells(1, 4), Cells(letzte, 4)).ClearContents
While ende <= letzte
su(1) = Application.WorksheetFunction.sum(Range(Cells(anf, 1), Cells(ende, 1)))
su(2) = Application.WorksheetFunction.sum(Range(Cells(anf, 2), Cells(ende, 2)))
su(3) = Application.WorksheetFunction.sum(Range(Cells(anf, 3), Cells(ende, 3)))
sumKL = WorksheetFunction.Min(su(1), su(2), su(3))
posKL = WorksheetFunction.Match(sumKL, Array(su(1), su(2), su(3)), 0)
If su(spa) > 1 + sumKL Then
Cells(ende, 4).FormulaLocal = _
"=Summe(" & Chr(spa + 64) & anf & ":" & Chr(spa + 64) & ende & ")"
Range(Cells(anf, spa), Cells(ende, spa)).Font.ColorIndex = 5
spa = posKL
anf = ende + 1
End If
ende = ende + 1
Wend
If Cells(ende - 1, 4) = "" Then
Cells(ende - 1, 4).FormulaLocal = _
"=Summe(" & Chr(spa + 64) & anf & ":" & Chr(spa + 64) & ende - 1 & ")"
Range(Cells(anf, spa), Cells(ende - 1, spa)).Font.ColorIndex = 5
End If
Cells(ende, 4).FormulaLocal = _
"=Summe(D6:D" & letzte & ")"
Application.ScreenUpdating = True
End Sub

Anzeige
AW: Modifikation
30.12.2003 01:37:41
Bongo
Hi Reinhard!
Dein Makro funktioniert perfekt.
Als Unterstützung habe ich die 3 Varianten unter https://www.herber.de/bbs/user/2637.xls abgelegt.

Beginnt das Verfahren immer in Spalte A?
Wie muß ich das Makro bei 4 oder mehr Spalten modifizieren?

lg Bongo
AW: Modifikation
30.12.2003 20:46:57
Reinhard
Hallo Bongo,
ich habe es jetzt umgestellt, sodass es auch in allen Spalten funktioniert, getestet im Spaltenberich BC:BE.
In https://www.herber.de/bbs/user/2643.xls musst du dann nur für den Spaltenbereich ErsteSpa und LetzteSpa anpassen, siehst ja wo da "BC" bzw "BE" im Makro steht.
anfges ist Zeile wo es anfängt, bisher 6, ggfs auch anpassen.
Das makro ermittelt die Anzahl der Zeilen indem es von unten kommen schaut, wo die letzte benutze Zeile in "ErsteSpa" ist. Deshalb darf da nichts stehen, auch keine Summenformeln oder so.Du hast da Summe(A6:A3999) oder so stehen.
Lass die Summenformel weg oder ändere ErsteSpa in ErsteSpa+1 in
letzte = Cells(65536, ErsteSpa).End(xlUp).Row
ab.
Schau jetzt bitte mal ob das Makro korrekt läuft. Wenn ja , schau ich mal nach 1) 2) 3)
Gruß
Reinhard
AW: Modifikation
31.12.2003 04:55:11
Bongo
Hallo Reinhard!

Das Makro hat einwandfrei funktioniert, und ich habe es auch modifizieren können.:)
Aufgrund dessen hat sich herausgestellt, daß Variante 2 und 3 unsinnig sind. Es verbleibt allerdings noch Variante 1.

Allerdings muß ich noch eine kühne Bitte stellen:
Bitte schreib mir noch ein Makro. Ich weis es ist etwas unverschämt, aber es wäre sehr wichtig und es ist garantiert das letzte Mal!

Variante 4 siehe https://www.herber.de/bbs/user/2651.xls :

Es gibt 2 Spalten: H ist die Datenspalte, I ist eine Kriteriumsspalte.
Es gilt:
I...Wert in der Kriteriumsspalte
I-1...Wert bevor I in der Kriteriumsspalte.
1:'endlosschleife
Wenn Spaltenwert I > -20 and (I-1 < I) dann beginne in H mit der Summenberechnung.
Wenn Spaltenwert I > +10 and (I-1 < I) dann beende in H die Summenberechnung und weise Summe in Spalte J aus.(siehe zeile 135) goto 2.
Wenn Spaltenwert I < -20 and I-1>I dann beende in H die Summenberechnung und weise Summe in Spalte K aus.(zeile 390) Beginne Summenberechnung an Stelle nachdem zuvor aufgehört wurde und beende Summenberechnung wenn (I > -20) and (I-1 < I) weise Summe in Spalte L aus.(zeile402) Goto 1.
2:'endlosschleife
Wenn Spaltenwert I < -20 and I-1 > I beginne Summenbeechnung
wenn Spaltenwert I > -20 and I-1 < I beende Summenberechnung weise Summe Spalte L zu.
Goto1.

Es gibt also 2 Schranken. 1: Die Summenberechnung startet, wenn die untere Schranke (-20) nach oben hin durchbrochen wird (I-1 < I).
Die Summenberechnung endet wenn die obere Schranke (+10) von unten erreicht wird (I-1 < I)(siehe Zeile 135) oder die untere Schranke von oben ereicht wird(I-1 > I).
Wird die untere Schranke von oben erreicht I < -20 und (I-1 > I)wird die Summe bis I > -20 und (i-1 < I) berechnet (die untere Schranke wird nach oben hin durchbrochen. Goto 1:

Reinhard, ich hoffe Du kapierst das.
Auf jedenfall wünsch ich Dir einen GUTEN RUTSCH und FROHES NEUES JAHR!
AW: Dynamische Summenberechnung
01.01.2004 14:52:27
Bongo
Hallo Reinhard!

Der Beitrag wurde ins Archiv gestellt. Meine Mailadresse lautet: hotmail@kabsi.at

Danke

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige