Summe einer Spalte - variabel | HERBERS Excel Forum

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

Hallo Boris,
hier nochmal eine verbesserte Version der Matrixformel, die Werte müssen dafür ab A2 stehen, A1 kann leer sein oder muss eine Überschrift haben, die keine Zahl ist (wenn es eine Zahl ist, dann muss ein ' davor).
{=SUMME(BEREICH.VERSCHIEBEN($A$1;KKLEINSTE(WENN(ISTZAHL($A$1:$A$20);"";ZEILE($A$1:$A$20));ZEILE($A1)) ;0;KKLEINSTE(WENN(ISTZAHL($A$1:$A$20);"";ZEILE($A$1:$A$20));ZEILE($A2)) -KKLEINSTE(WENN(ISTZAHL($A$1:$A$20);"";ZEILE($A$1:$A$20));ZEILE($A1))-1)) }
Nun zur Erklärung der Formel. Entscheiden ist dieser Teil hier, der wiederholt sich ja mehrfach:
KKLEINSTE(WENN(ISTZAHL($A$1:$A$20);"";ZEILE($A$1:$A$20));ZEILE($A1))
Durch die WENN Funktion wird eine Matrix aufgebaut, in der "" steht, wenn es eine Zahl ist und die Zeilennummer, wenn es keine Zahl ist - also im Falle Deiner Leerzeichen. Für die erste Summe benötige ich nun den Bereich zwischen dem ersten Leerzeichen (bzw. der Überschrift) und dem zweiten Leerzeichen, also der kleinsten und zweitkleinsten Zahl in dieser Matrix. Da die Formel natürlich für das runterkopieren geeignet sein soll, kann ich nicht einfach KKLEINSTE(...;1) verwenden, sondern mache das über einen Bezug, hier $A1 für 1 und §A2 für 2.
Nun liefert mir also
{=KKLEINSTE(WENN(ISTZAHL($A$1:$A$20);"";ZEILE($A$1:$A$20));ZEILE($A1)) } die Zeile des ersten Leerzeichens
{=KKLEINSTE(WENN(ISTZAHL($A$1:$A$20);"";ZEILE($A$1:$A$20));ZEILE($A2)) } die Zeile des zweiten Leerzeichens
Und nun muss ich nur noch mit BEREICH.VERSCHIEBEN den korrekten Bereich festlegen. Als Bezugspunkt setzte ich $A$1, also die erste Zeile. Die Summierung soll in der Zeile nach dem ersten Leerzeichen beginnen, also muss ich ausgehend von $A$1 genau um die Zeilennummer des ersten Leerzeichens verschieben.
Die Größe des Bereiches sind genau die Zeilen zwischen dem ersten und zweiten Leerzeichen, also die Differenz der beiden Zeilen - 1. Um das ganze nun noch die Summe und schon war es das.
Das ganze kann man nun auch noch fehlerredundant auslegen, so dass man auch in mehr Zeilen die Formel eintragen kann, als Blöcke vorhanden sind. Das ging z.B. so (Summen stehen in Spalte J):
=WENN((SUMME(J$1:J1)>=SUMME($A$1:$A$20))+ISTFEHLER(KKLEINSTE(WENN(ISTZAHL($A$1:$A$20);""; ZEILE($A$1:$A$20));ZEILE($A2)));"";SUMMEWENN(BEREICH.VERSCHIEBEN($A$1; KKLEINSTE(WENN(ISTZAHL($A$1:$A$20);"";ZEILE($A$1:$A$20));ZEILE($A1));0; KKLEINSTE(WENN(ISTZAHL($A$1:$A$20);"";ZEILE($A$1:$A$20));ZEILE($A2)) -KKLEINSTE(WENN(ISTZAHL($A$1:$A$20);"";ZEILE($A$1:$A$20));ZEILE($A1))-1);"0"))
Das summiert dann einfach zuerst mal alle Zahlen im relevanten Bereich und vergleicht das mit der bisherigen Summe. Zudem wird noch überprüft, ob das zweite Leerzeichen gefunden werden kann. Hier wird nun SUMMEWENN statt SUMME verwendet, da die Verwendung von SUMME in einer anderen Funktion (hier WENN) bei Matrixfunktionen nur noch Unsinn ergibt. Da in dieser SUMMEWENN der Suchbereich auch aufsummiert wird, hat man mit "0" natürlich eine wunderbare Bedingung (ob man Nullen mit aufsummiert oder nicht ist ja egal). Hätte man den Fall, dass ein anderer Bereich aufsummiert werden müsste, dann muss man sich die Bedingung natürlich überlegen, aber dann geht sowas wie " Gruß, Jogy

Hallo Edgar,
... dann sieh dir mal diese nette kleine Formel an:
{=WENN(MAX(SUMME(A1:INDEX(A:A;VERGLEICH(1;--(A1:A99="");0)-1));TEILERGEBNIS(9; BEREICH.VERSCHIEBEN(A$1;;;KKLEINSTE((A$1:A$99"")*999+ZEILE($1:$99);ZEILE(C$2:C$9)))) -TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(A$1;;;KKLEINSTE((A$1:A$99"")*999+ZEILE($1:$99);ZEILE(C$1:C$8))) ))=SUMME(A1:INDEX(A:A;VERGLEICH(1;--(A1:A99="");0)-1));1;VERGLEICH(MAX(SUMME(A1:INDEX(A:A; VERGLEICH(1;--(A1:A99="");0)-1));TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(A$1;;;KKLEINSTE((A$1:A$99"") *999+ZEILE($1:$99);ZEILE(C$2:C$9))))-TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(A$1;;; KKLEINSTE((A$1:A$99"")*999+ZEILE($1:$99);ZEILE(C$1:C$8)))));TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(A$1; ;;KKLEINSTE((A$1:A$99"")*999+ZEILE($1:$99);ZEILE(C$2:C$9))))-TEILERGEBNIS(9; BEREICH.VERSCHIEBEN(A$1;;;KKLEINSTE((A$1:A$99"")*999+ZEILE($1:$99);ZEILE(C$1:C$8))));0)+1) }
was die macht? Sie sucht welcher Zahlenblock das Maximum ergibt.
Beispiel:

Tabelle2

	A	B	C	D	E
1	1		15
2	2	0	107		1014
3	3	0	1014		3
4	4	0	10
5	5	0	3
6		15	9
7	3	0	0
8	5	0	0
9	99	0	0
10		107	0
11	2	0
12	5	0
13	8	0
14	999	0
15		1014
16	1	0
17	2	0
18	3	0
19	4	0
20		10
21	1	0
22	2	0
23		3
24	9	0
25		9

Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
Ist natürlich nur eine Spielerei ;-)
Servus aus dem Salzkammergut
Franz

Beliebteste Forumthreads (12 Monate)

Zelle	Format	Wert
B2	Standard;;

Zelle	Formel
B2	=WENN(A3="";SUMME($A$1:$A2)-SUMME($B$1:B1);"")

Zelle	Formel
B1	{=SUMME(BEREICH.VERSCHIEBEN($A$1;;;KKLEINSTE(WENN($A$1:$A$1000="";ZEILE($A$1:$A$1000));ZEILE(A1))))}
C1	{=SUMME(BEREICH.VERSCHIEBEN($A$1;;;KKLEINSTE(WENN($A$1:$A$1000="";ZEILE($A$1:$A$1000));ZEILE(A1))))}
B2	{=SUMME(BEREICH.VERSCHIEBEN($A$1;;;KKLEINSTE(WENN($A$1:$A$1000="";ZEILE($A$1:$A$1000));ZEILE(A2))))-SUMME($B$1:B1)}
C2	{=SUMME(BEREICH.VERSCHIEBEN($A$1;KKLEINSTE(WENN($A$1:$A$1000="";ZEILE($A$1:$A$1000));ZEILE(A1));;KKLEINSTE(WENN($A$1:$A$1000="";ZEILE($A$1:$A$1000));ZEILE(A2))-KKLEINSTE(WENN($A$1:$A$1000="";ZEILE($A$1:$A$1000));ZEILE(A1))))}

Summe einer Spalte - variabel

20
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

310 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
2	3
3	4
4	5	12
5
6	4
7	9	13
8
9	4	4
10
11	6
12	7
13	7
14	3
15	2	25
16
17

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C
1	5	14	14
2	4	18	18
3	3	27	27
4	2	40	40
5
6	7
7	6
8	5
9
10	4
11	3
12	2
13	3
14	4
15	5
16	6
17
18	7
19	8
20	9
21	7
22	5
23	4
24
25
26	4
27	5
28	6
29
30	3
31	4
32

Summe einer Spalte - variabel

20 Beiträge zum Forumthread Beiträge zu diesem Forumthread

310 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

20
Beiträge zum Forumthread

Beiträge zu diesem Forumthread