Variable Summenformel

Forumthread: Variable Summenformel

17.03.2008 16:14:44

Peter

Guten Tag
Angenommen
ich habe verschiedene Zahlenblöcke in Spalte C mit einem anschliessenden Total in Spalte C. Damit die Summe immer alle Zeilen vor dem Total (im Beispiel in Zelle E28) einbezieht, verwende ic folgende Formel:
=SUMME(E8:BEREICH.VERSCHIEBEN(E28;-1;))
Gibt es irgend eine Möglichkeit, dass ich den Beginn des Summenbereichs abfragen kann, wenn dieser immer dort beginnt, wo in Spalte A (von der Summenformel aufwärts gesehen) der letzte Eintrag in Spalte A steht?
Zur Veranschaulichung habe ich eine Tabelle hochgeladen:
https://www.herber.de/bbs/user/50794.xls
Danke, Peter

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Variable Summenformel

17.03.2008 18:10:00

Erich
AW: Variable Summenformel

17.03.2008 21:06:19

Peter
AW: Variable Summenformel

17.03.2008 23:27:10

Erich
AW: Variable Summenformel

18.03.2008 07:32:00

Peter
AW: Variable Summenformel

18.03.2008 09:28:52

Roland
AW: Variable Summenformel

18.03.2008 10:49:00

Peter
AW: Variable Summenformel

18.03.2008 13:13:00

Roland
AW: Danke! owT

18.03.2008 13:33:08

Peter

AW: Variable Summenformel

17.03.2008 18:10:00

Erich

Hallo Peter,
vermutlich gehts auch kürzer, aber die Formeln in Spalte E sollten funzen:

	E	F	G
28	7.708.190,00	7.708.190,00	7.708.190,00
29
30
31
32	6500
33	10830
34	385000
35	5040
36
37	407.370,00	407.370,00	407.370,00

Formeln der Tabelle

Zelle	Formel
E28	{=SUMME(BEREICH.VERSCHIEBEN(E$1;MAX(ZEILE($A$1:A28)($A$1:A28<>""))-1;; ZEILE()-1-MAX(ZEILE($A$1:A28)($A$1:A28<>""))))}
F28	=SUMME(E8:BEREICH.VERSCHIEBEN(F28;-1;))
G28	=SUMME(E8:E27)
E37	{=SUMME(BEREICH.VERSCHIEBEN(E$1;MAX(ZEILE($A$1:A37)($A$1:A37<>""))-1;; ZEILE()-1-MAX(ZEILE($A$1:A37)($A$1:A37<>""))))}
F37	=SUMME(E32:BEREICH.VERSCHIEBEN(F37;-1;))
G37	=SUMME(E32:E36)

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

AW: Variable Summenformel

17.03.2008 21:06:19

Peter

Hallo Erich
Vielen Dank! Finde die Lösung hoch interessant und hochkomplex.
Habe im konkreten Fall noch die Zellen A1 mit StartP und resp. E1 mit StartB benannt und folgende Formel erhalten:
{=SUMME(BEREICH.VERSCHIEBEN(StartB;MAX(ZEILE(StartP:A28)*(StartP:A28""))-1;; ZEILE() -1-MAX(ZEILE(StartP:A28)*(StartP:A28"")))) }
Ich habe mich gefragt, ob sich aus solch einer Formel nicht eine Funktion machen liesse: A28 wäre ja in jedem Fall activecell.address und dann brauche man noch die Bezüge für StartP und StartB zu übergeben. Was denkt da der Profi darüber?
Gruss, Peter

AW: Variable Summenformel

17.03.2008 23:27:10

Erich

Hi Peter,
so könnte ich mir eine solche Funktion vorstellen:

	G	H	I
28	7.708.190,00	7.708.190,00	7.708.190,00
29
36
37	407.370,00	407.370,00	407.370,00

Formeln der Tabelle

Zelle	Formel
G28	=SUMME(E8:E27)
H28	=SumSpezN(1;5)
I28	=SumSpezB("A";"E")
G37	=SUMME(E32:E36)
H37	=SumSpezN(1;5)
I37	=SumSpezB("A";"E")

Und so sind die Funktionen definiert (in ein normales Modul):


Option Explicit
Function SumSpezN(intSpA As Integer, intSpS As Integer)
Dim lngZ As Long, lngV As Long
lngZ = Application.Caller.Row
lngV = Cells(lngZ, intSpA).End(xlUp).Row
SumSpezN = WorksheetFunction.Sum(Range(Cells(lngV, intSpS), Cells(lngZ - 1, intSpS)))
End Function
Function SumSpezB(strA As String, strS As String)
Dim lngZ As Long, lngV As Long
lngZ = Application.Caller.Row
lngV = Cells(lngZ, strA).End(xlUp).Row
SumSpezB = WorksheetFunction.Sum(Range(Cells(lngV, strS), Cells(lngZ - 1, strS)))
End Function

Noch eine Bemerkung zu "A28 wäre ja in jedem Fall activecell.address".
Das stimmt nicht so ganz. Eine beutzerdefinierte Fkt. kann auch berechnet werden,
während irgend eine andere Zelle die aktive ist.
Wichtig ist, aus welcher Zelle heraus die Fkt. aufgerufen wird (wo also die Formel steht).
Das bekommt man mit dem Application.Caller.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

AW: Variable Summenformel

18.03.2008 07:32:00

Peter

Hallo Erich
Vielen Dank. Ich bin begeistert.
Was hältst du davon, wenn man in der Funktion noch application.volatile einfügt, damit die Formel bei jeder Änderung aktualisiert wird? Teilweise wird ja von application.volatile abgeraten, weiss jedoch nicht, ob es allenfalls auch hier besser wäre, anders zum Ziel zu kommen.
Gruss, Peter

AW: Variable Summenformel

18.03.2008 09:28:52

Roland

Halle Peter,
warum so kompliziert, Du brauchst doch "nur" die passende Summenformel?
Mein Simpelansatz (in ein allgemeines Modul):


Sub Summenformel_einfügen()
Dim lngStart As Long
lngStart = Range("A" & ActiveCell.Row).End(xlUp).Row - CLng(ActiveCell.Row)
ActiveCell.FormulaR1C1 = "=SUM(R[" & lngStart & "]C:R[-1]C)"
End Sub

Das ganze auf einen Button gelegt und Du bekommst die Formel dahin, wo Du Sie haben willst und hast keinen Stress mit Parameterübergaben usw.
Gruß aus Berlin
Roland Hochhäuser

AW: Variable Summenformel

18.03.2008 10:49:00

Peter

Hallo Roland
Danke für die Rückmeldung. Du hast recht, in der Regel braucht man das nicht so kompliziert. Im konkreten Fall ergänzt der Sachbearbeiter (ab und zu auch eine "sie") "nicht unwichtige" Tabellen mit zusätzlichen Dateizeilen und vergisst ab und zu die Summenformel anzupassen. Deshalb dieses Vorgehen.
Gruss, Peter

AW: Variable Summenformel

18.03.2008 13:13:00

Roland

Hallo Peter,
wenn das so ist, dann ist es noch einfacher:
1. Schreib händisch die Summenformel; also in E28 steht dann =SUMME(E8:E27). Lasse die Zelle E28 aktiviert.
2. Drücke Strg + F3 (alternativ: Einfügen / Namen / Definieren . . .)
3. In "Bezieht sich auf:" steht jetzt: =Tabelle1!$E$28
4. Ändere dies händisch auf =Tabelle1!E27 (also $-Zeichen weg und eine Zeile höher!)
5. Vergib jetzt noch einen Namen in der obersten Zeile (Namen in der Arbeitsmappe); z.B. Summe1, und bestätige dann mit OK.
6. Wiederhole die Schritte 1-5 für jede Summenformel analog (mit jeweils anderem Namen)
Jetzt können Zeilen gelöscht oder hinzugefügt werden wie die Benutzer(innen) lustig sind, an der Richtigkeit der Summe wird sich nichts ändern. Du brauchst die Formel nie wieder manuell anzupassen und kannst hier auf VBA völlig verzichten.
Gruß aus Berlin
Roland Hochhäuser

AW: Danke! owT

18.03.2008 13:33:08

Peter

Beliebteste Forumthreads (12 Monate)

Forumthread: Variable Summenformel

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread