Office: Summe bis Leerzeile

magicman · 6. September 2010

Hallo Experten,

ich brauche mal etwas Formelunterstützung:

In einer Tabelle muss ich Zwischensummen einfügen:

Von der Zelle über der Summenzeile bis zur nächsten leeren Zelle (nicht 0 das darf vorkommen).

Ist das über eine Formel möglich - sonst muss ich das für 10 Werke x 5 Vertriebler x X-Kunden per hand machen.

Danke im Voraus.

Michael

:)

EarlFred · 8. September 2010

Hallo Michael,

probier mal folgendes kleine Makro:
Code:

Option Explicit
Sub Teilergebnisse_einfügen()
Dim rngStart As Range
Dim rngEnde As Range

Set rngStart = Cells(1, ActiveCell.Column)
Do Until rngStart.End(xlDown).Row = Rows.Count
Set rngEnde = rngStart.End(xlDown)
With rngEnde.Offset(1, 0)
.Formula = "=SUBTOTAL(9," & Range(rngStart, rngEnde).Address & ")"
.Font.Bold = True
End With
Set rngStart = rngEnde.Offset(2, 0)
Loop
With rngEnde.Offset(2, 0)
.Formula = "=SUBTOTAL(9," & Range(Cells(1, ActiveCell.Column), rngEnde.Offset(1, 0)).Address & ")"
.Font.Bold = True
.Font.ColorIndex = 3
End With

Set rngStart = Nothing
Set rngEnde = Nothing
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Grüße
EarlFred

OfficeUser · 12. Januar 2021

Hallo!

Meinst Du es so?

Test

A B
1 nach Leerzeile eine Summe bilden
2
3
4 1
5 2
6 3
7 3
8 4
9 7
10 5
11 6
12 7
13 8
14 9
15 10
16 11
17 56
18 12
19 45
20 4
21 61
22 2
23 2
Zelle Formel
B4 =WENN(A4="";SUMME($A4:A)-SUMME($B3:B);"")
Excel Tabellen im Web darstellen >>

magicman · 8. September 2010

Hallo EarlFred,

danke für das Makro,

aber leider werden die Summen nicht an den Vorgesehenen Stellen eingefügt.

Ich habe mal eine Beispielmappe angehängt, die Zwischensummen müssen in die grau hinterlegten Felder.

@ Wilfried
Danke für die Formel aber die muss wirklich in die gleiche Spalte.

Gruß
Michael

EarlFred · 8. September 2010

Hallo Michael,

OK, bei 2 Leerzeilen geht das so nicht.

Probier mal:
Code:

Option Explicit
Sub Teilergebnisse_einfügen()
Dim rngStart As Range
Dim rngEnde As Range
Dim rngWeiter As Range

Set rngStart = Cells(1, ActiveCell.Column)
Do Until rngStart.End(xlDown).Row = Rows.Count
Set rngEnde = rngStart.End(xlDown)
Set rngWeiter = rngStart.End(xlDown).End(xlDown)
With rngEnde.Offset(1, 0)
.Formula = "=SUBTOTAL(9," & Range(rngStart, rngEnde).Address & ")"
.Font.Bold = True
End With
Set rngStart = rngWeiter
Loop
With rngEnde.Offset(2, 0)
.Formula = "=SUBTOTAL(9," & Range(Cells(1, ActiveCell.Column), rngEnde.Offset(1, 0)).Address & ")"
.Font.Bold = True
.Font.ColorIndex = 3
End With

Set rngStart = Nothing
Set rngEnde = Nothing
Set rngWeiter = Nothing

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Grüße
EarlFred

magicman · 8. September 2010

Guten Morgen EarlFred,

gestern habe ich es nicht mehr testen können.

Heute morgen hab ich das ausprobiert und es läuft wunderbar.

Vielen Dank - das spart eine Menge Arbeit.

Gruß
Michael

magicman · 8. September 2010

Hallo EarlFred,

einen Stolperstein habe ich noch in den echten Daten:

Wenn es nur eine Zeile gibt wird keine Zwischensumme eingefügt und das Makro "verschluckt" sich ab da mit den Zeilen für die Zwischensummen.

In den Ist-Daten kann es sein das bei kleineren Kunden nur eine Produktgruppe aufgelistet ist. Für die Einheitliche Darstellung brauche ich aber auch da eine Zwischensumme.

Kannst Du mir hier noch einmal helfen?

Gruß
Michael

EarlFred · 8. September 2010

Hallo Michael,

kann ich mir nochmal anschauen, im Moment aber schlecht, da wenig Zeit.

Warum nimmst Du eigentlich nicht gleich eine Pivottabelle? *wink.gif*

Grüße
EarlFred

magicman · 8. September 2010

Hallo EarlFred,

das ist quasi rechts von einer Pivot-Tabelle.

In der Pivot zeige ich den Umsatz 2009 sowie die Hochrechung für 2010 und rechts daneben sollen die Vertriebler das Budget planen.

Vorgaben werden über Formeln berechnet und können dann vom Vertriebler geändert werden (hier scheidet dann die Pivot aus).

Wenn Du noch Zeit findest wäre ich dir sehr dankbar aber auch mit dem aktuellen Makro spare ich viel Zeit.

Danke dafür.

Michael

EarlFred · 8. September 2010

Hallo Michael,

doch noch kurz Zeit für eine Kaffeepause gehabt *wink.gif*

Code:

Option Explicit
Sub Teilergebnisse_einfügen()
Dim rngStart As Range
Dim rngEnde As Range
Dim rngWeiter As Range

Set rngStart = Cells(1, ActiveCell.Column)
Do Until rngStart.Row = Rows.Count
If IsEmpty(rngStart.Offset(1, 0)) Then
Set rngEnde = rngStart
Else
Set rngEnde = rngStart.End(xlDown)
End If
Set rngWeiter = rngEnde.End(xlDown)
With rngEnde.Offset(1, 0)
.Formula = "=SUBTOTAL(9," & Range(rngStart, rngEnde).Address & ")"
.Font.Bold = True
End With
Set rngStart = rngWeiter
Loop
With rngEnde.Offset(2, 0)
.Formula = "=SUBTOTAL(9," & Range(Cells(1, ActiveCell.Column), rngEnde.Offset(1, 0)).Address & ")"
.Font.Bold = True
.Font.ColorIndex = 3
End With

Set rngStart = Nothing
Set rngEnde = Nothing
Set rngWeiter = Nothing

End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Grüße
EarlFred

magicman · 8. September 2010

Hallo EarlFred,

danke, jetzt ist es perfekt.

Ich hoffe der Kaffee hat geschmeckt - jetzt werde ich mir auch einen gönnen.
Dank Dir habe ich da ja jetzt Zeit für.

Gruß
Michael

OfficeUser · 12. Januar 2021

von Wilfried07

Hallo!

Meinst Du es so?

Test

[parsehtml]<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-size:11pt; ">nach Leerzeile eine Summe bilden</td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">2</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="color:#0000ff; font-weight:bold; text-align:right; ">3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">3</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">4</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="color:#0000ff; font-weight:bold; text-align:right; ">7</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">5</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">6</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">7</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">8</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">9</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">10</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">11</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td style="color:#0000ff; font-weight:bold; text-align:right; ">56</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">12</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">45</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">4</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td style="color:#0000ff; font-weight:bold; text-align:right; ">61</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">2</td><td style="color:#0000ff; font-weight:bold; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td style="color:#0000ff; font-weight:bold; text-align:right; ">2</td></tr></table>[/parsehtml][parsehtml]<table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B4</td><td >=WENN(A4="";SUMME<span style=' color:008000; '>($A4:A)</span>-SUMME<span style=' color:008000; '>($B3:B)</span>;"")</td></tr></table>[/parsehtml]
Excel Tabellen im Web darstellen >>
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

padre200 · 31. Juli 2015

Jungs , die Formel ist Super....Bin begeistert ...Es gibt aber ein Problem, ich will dass die Teilergebnisse sich in der zweiten leeren Zelle oberhalb der Zahlenreihe bilden. Hab ein bisschen rumgespielt - ist aber nichts daraus gekommen.

Office: Summe bis Leerzeile

Summe bis Leerzeile

Summe bis Leerzeile

Summe bis Leerzeile

Summe bis Leerzeile

Summe bis Leerzeile

Summe bis Leerzeile - Similar Threads - Summe Leerzeile

Zahlenreihe addieren

Tabellensortierung kollidiert mit Formel

VBA Summe bilden

Excel Summe bilden wenn bestimmtes Wort und Zahl in eine Zelle vorhanden

Summen bis bestimmten Punkt zusammenrechnen?

Summe addieren in anderem Format

automatisch Leerzeilen und Summen einfügen

	A	B
1	nach Leerzeile eine Summe bilden
2
3
4	1
5	2
6		3
7	3
8	4
9		7
10	5
11	6
12	7
13	8
14	9
15	10
16	11
17		56
18	12
19	45
20	4
21		61
22	2
23		2