Live-Forum - Die aktuellen Beiträge
Datum
Titel
15.04.2024 23:32:21
Darstellung im IMAGE sehr schlecht
15.04.2024 21:08:34
PDF mit Makro erstellen mehrere Sheets
15.04.2024 18:38:39
Arbeitszeiten Vorlage ergänzen
15.04.2024 17:34:30
Fehlende Zeilen automatisch nach Zeiten ergänzen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
960to964
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
960to964
960to964
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Variable Summenformel

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
Anzeige
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:
 EFG
287.708.190,007.708.190,007.708.190,00
29   
30   
31   
326500  
3310830  
34385000  
355040  
36   
37407.370,00407.370,00407.370,00

Formeln der Tabelle
ZelleFormel
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

Anzeige
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

Anzeige
AW: Variable Summenformel
17.03.2008 23:27:10
Erich
Hi Peter,
so könnte ich mir eine solche Funktion vorstellen:
 GHI
287.708.190,007.708.190,007.708.190,00
29   
36   
37407.370,00407.370,00407.370,00

Formeln der Tabelle
ZelleFormel
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

Anzeige
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

Anzeige
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

Anzeige
AW: Danke! owT
18.03.2008 13:33:08
Peter

167 Forumthreads zu ähnlichen Themen

Summenformel Zellbezüge markieren
Christian

Hi Leute,
heute möchte ich eine Frage stellen die mich schon eine Zeit lang beschäftigt, für die ich aber leider selber noch keine Antwort gefunden habe.
Ich habe ein Registerblatt in dem einige "Summen-Formeln" stehen, die auf Zellbeüge in anderen Registerblättern (alle in der selben A...
08.09.2008 08:38:00
Thread-ID: 1008042
6 Beiträge
Anzeige
Summenformel funktioniert nicht
Mathias

Hallo guten Abend,
ich habe folgendes Problem. Ich habe eine Stundenübersicht. In der Stundenübersicht habe ich wie ihr in der Formel seht im Format 00:00 Die Stunden zusammen gezählt. Wenn ich jetzt aber die Wochenstunden zusammenzählen möchte für Monatsabrechung kommt iergent ein wirres Z...
18.11.2008 23:06:14
Thread-ID: 1025814
7 Beiträge
Summenformel ändert sich beim Löschen
Manu

Hallo,
ich will von Zeile 10 die ganze Spalte aufsummieren. bis Zeile 9 sind die Zeilen gesperrt.
Meine Formel lautet: =Summe(A10:A65000)
Problem nun, wenn ich in diesem Bereich eine Zeile lösche wird aus 65000 - 64999. Gibts da was, wie man das verhindern kann und es bei 65000 ble...
06.01.2009 15:26:00
Thread-ID: 1037256
9 Beiträge
Anzeige
Makro mit Ordnerfunktion und Summenformel
joop

Hallo Liebe VBA Profis,
Ich hätte hier ein Excel Problem, welches wahrscheinlich auch schon öfter besprochen wurde, leider finde ich dazu nichts. Ich bin also auch für links super dankbar.!!!
Sachverhalt:
Gegeben ist ein Datensatz welcher durch eine Abteilungskennziffer in der erste...
10.06.2009 10:27:21
Thread-ID: 1080368
6 Beiträge
Dynamsiche Summenformel
wolfman

Hallo und einen schönen guten Abend!
Habe folgendes Problem:
Ich möchte in einer Zeile Zellen addieren, z.B. Summe(a10:a15). Dabei kenne ich den Namen der letzen Zelle (A15), die 1.Zelle (a10) soll errechnet werden durch eine Variable ( also hier 5 Spalten zurück)
Gibt es dafür ein...
05.10.2009 20:45:13
Thread-ID: 1107343
7 Beiträge
Summenformel auf VBA umschreiben
Martin

Hallo,
folgende Formel möchte ich gerne für VBA schreiben:
=SUMMENPRODUKT((Geschlecht=A2)*(Endzeit<B2))+1
So geht es mit VBA:
MsgBox [SumProduct((Geschlecht = A2) * (Endzeit MsgBox Evaluate("SumProduct((Geschlecht = A2) * (Endzeit ("Geschlecht" und "Endzeit" sind benannte...
07.10.2009 19:33:12
Thread-ID: 1107979
11 Beiträge
SUMMENFORMEL mit VBA einfügen
Peter

Guten Tag
Ich möchte mit VBA eine Summenformel einfügen.
Selektiert ist irgend eine Zelle in der Spalte H (z.B. H35).
Nun möchte ich in Zelle H35 eine Summenformel einfügen, die den Bereich von H2 bis H34 berücksichtigt.
Wenn H100 selektiert ist, soll die Summenformel H2 bis H99...
30.12.2009 10:14:36
Thread-ID: 1126356
6 Beiträge
bei Zeile einfügen per VBA Summenformel anpassen
Klaus

wie kann ich erreichen, dass eine Summenformel im Tabellenblatt z.B. Zelle A1: '=Summe(A2:A10)' nach dem einfügen einer Zeile nach der 10ten Zeile sich in '=Summe(A2:A11)' ändert!
Für den Fall, dass die Summe unter der Liste steht hab ich hier im Forum mal eine geniale Lösung gelesen:=SUMME(...
12.04.2002 17:43:41
Thread-ID: 112908
11 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
5
4
6
8
5
5
6
10
5
10
2
4
1
5
4
2
8
14
5
2
1
3
2
6
5