Summe unterschiedlicher Werte in Spalte

[neopa]

ich habe mit der Formel von Werner auch ein Problem, denn diese Forderung

Zusätzlich sollte die Summenbildung auch funktionieren, wenn ich auf die ID einen Filter setze.

funktioniert bei mir mit dieser Formel nicht.

Hallo Peter, hallo Jan,

... ich hatte leider nicht den ganzen thread gelesen und mich in der Formel dazu auch noch 2x verschrieben.
Mein Schreibfehler mit D2:D99 anstelle B2:B99 in der Formel ist ja schon von Günter B korrigiert. Der zweite Schreibfehler in der Formel ist etwas "versteckt", spielt aber bei  Einsatz von nur AGGREGAT() keine Rolle.

Für eine zusätzliche Filterung war meine bisherige Formel noch nicht ausgelegt. Anders geschrieben, die Funktion AGGREGAT() ist in ihrer Matrixversion (so wie ich sie einsetze) dafür nicht nicht ausgelegt!


Ich hab dieses Manko (um es vorsichtig auszudrücken) seitens der Programmierer, der ab Excel 2010 neuen Funktion, in anderen Foren schon ein paar Mal thematisiert.

Es ist leider so, obwohl AGGREGAT() vordergründig für ausgeblendete Zeilenwerte geschaffen ist, ignoriert es diese Funktion bei Anwendung in ihrer Matrixversion. Man muss sich da wieder auf das gute alte TEILERGEBNIS() besinnen und damit leider, leider auch auf die volatile Funktion INDIREKT().

Geplant meinerseits ist übrigens, dass ich irgendwann die nächsten Monate (hoffentlich) mehr zum Thema AGGREGAT() in der Matrixversion zusammenfassend in Online Excel zusammenfassend wiedergeben werde.

Die Funktionen lassen sich aber natürlich entsprechend kombinieren, wie zu sehen ist (ausgeblendet ist hier ID 5 und 54):

	A	B	C	D	E
1	ID	Value			40000
2	3	0
3	3	0
4	3	0
5	4	-10000
6	4	-10000
7	4	-10000
8	4	-10000
11	15	-15000
12	53	15000
14	55	50000
15	56	0
16	57	0
17

Formeln der Tabelle

Zelle Formel E1 =SUMMENPRODUKT(AGGREGAT(15;6;(A2:A99<>A1:A98)*B2:B99*TEILERGEBNIS(2;INDIREKT("B"&ZEILE(A2:A99)));ZEILE(A1:A98)))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[Jan]

Guten Morgen Werner,

das sieht schon sehr gut aus. Danke!
Leider habe ich wieder ein kleines aber...



Die Summe sollte in der Ergebniszeile der Tabelle stehen. Wen ich sie in diesem Feld bilden lasse, führt das zu einem Ergebnis von Null.
Ich vermute, dass die Formel die Summe selbst noch mit verarbeitet, oder?


Gruß
Jan

[neopa]

Die Summe sollte in der Ergebniszeile der Tabelle stehen. Wen ich sie in diesem Feld bilden lasse, führt das zu einem Ergebnis von Null.
Ich vermute, dass die Formel die Summe selbst noch mit verarbeitet, oder?

Hallo Jan,

... lade doch mal eine kleine Beispielarbeitsmappe (keine Bilddatei) hoch und zeige daran auf, wo Du Dein Ergebnis ermitteln willst.
Im Laufe des Tages schau ich es  mir dann noch mal an.

[Jan]

Hallo Werner,

die Datei ist im Anhang.
Tabelle1 zeigt Deinen Lösungsvorschlag, der grundsätzlich funktioniert
Tabelle2 ist identisch, bis auf die Tatsache, dass die Summe in der Ergebniszeile der Spalte B steht.

Danke und Gruß
Jan

[neopa]

Hallo Jan,

... dank entsprechendem zusätzlichen Einsatzes von INDEX()-Funktionen, ist das nun realisiert.
 
Ich hab die Formel so erweitert, dass Sie jetzt in Deiner "Ergebniszeile" eingesetzt ist und diese völlig unabhängig davon arbeitet, wie viele Datensätze Du wirklich auszuwerten hast.

Die Formel  habe ich  auch gleich so erweitert, dass sie sich völlig selbstständig an nachträgliche Strukturänderungen Deiner Tabelle anpasst und oder auch an evtl. nachträgliche Lage-Änderungen dieser Tabelle bezogen auf das Tabellenblatt.

Für letzteres musste ich zusätzlich ADRESSE() einsetzen, um die ansonsten starre INDIREKT() - Definition flexibel zu gestalten. 

Du kannst jetzt z.B. vor Zeile 1 Zeilen einfügen und oder auch links von Spalte A neue Spalten. Spalten können auch zwischen die beiden Tabellenspalten eingefügt werden. Im Tabellenbereich selbst  können ebenso Zeilen gelöscht und oder eingefügt werden.

Das ganze ist natürlich nur zu Lasten der Verlängerung der Formel möglich gewesen. Aber diese könntest Du "verstecken", indem Du sie als Bereichsnamen definierst und diesen als Ergebnisformel einsetzt.

Die Formel ist keine ("echte") MATRIXformel, sondern ich nenne derartige Formeln seit ein paar Wochen Matrixfunktion(alität)sformel.

In Excelversionen vor Excel2010 waren derartige "einzelligen" Lösungsformeln fast ausschließlich nur als MATRIXformel (also die mit der abschließenden Formeleingabe durch STRG+Shift +Enter) realisierbar.

Dank AGGREGAT() in Kombination mit anderen Funktionen mit Matrixversionen wie z.B. INDEX(), SUMENPRODUKT() etc ist das nun nur selten notwendig. Die interne Formelauswertung ist jedoch mit der "echter" Matrixformeln vergleichbar.


Tabelle2

	A	B
1	ID	Value
2	3	0
3	3	0
4	3	0
5	4	-10000
6	4	-10000
7	4	-10000
8	4	-10000
11	15	-15000
12	53	15000
14	55	50000
15	56	0
16	57	0
17		40000
18

Formeln der Tabelle

Zelle Formel B17 =SUMMENPRODUKT(AGGREGAT(15;6;(INDEX(A:A;ZEILE(A1)+1):INDEX(A:A;ZEILE()-1)<>A1:INDEX(A:A;ZEILE()-2))*INDEX(B:B;ZEILE(A1)+1):INDEX(B:B;ZEILE()-1)*TEILERGEBNIS(2;INDIREKT(LINKS(ADRESSE(ZEILE();SPALTE();2);FINDEN("$";ADRESSE(ZEILE();SPALTE();2))-1)&ZEILE(INDEX(A:A;ZEILE(A1)+1):INDEX(A:A;ZEILE()-1))));ZEILE(INDEX(A:A;1):INDEX(A:A;ZEILE()-1-ZEILE(A1)))))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[Jan]

Hallo Werner,

das funktioniert! Danke!
Verstanden habe ich es aber nicht.
Was machst Du sonst so...Raketen bauen?  :19:
Wahrscheinlich hast Du ein klimatisiertes Büro, denn ich könnte bei der Hitze (aktuelle 32°) derartige gedankliche Höchstleistungen nicht vollbringen.
Hut ab!

Wenn ich Zeit finde, muss ich die Formel mal auseinender nehmen um zu verstehen, was Du da gemacht hast.
Im ersten Moment, als ich die Formel auf meine reale Liste übertragen habe funktionierte sie nicht, weil die Tabelle bei mir in Zeile 2 beginnt.
Ich habe dann einfach die erste Zeile gelöscht, Deine Formel eingesetzt und die Zeile wieder hinzugefügt. Hat geklappt.
Meine Tabelle hat aktuell 7 ID-Spalten und je 3 (also insg. 21) Werte-Spalten. Da bin ich jetzt erst einmal beschäftigt, die Formel anzupassen.

Also, nochmals danke!
Gruß
Andreas

[neopa]

Was machst Du sonst so...Raketen bauen?
...

Gruß
Andreas

Hallo Andreas oder Jan,

...  Raketen baue ich nicht, ich beschäftige mich halt nur zu freien Zeiten öfters mit Excelformeln und das eben schon ein paar Jährchen. Heiß ist es bei uns auch, ich schaue aber lieber nicht nach, welche Temperatur wirklich ansteht, weil alles über 25° mir nicht zusagt.

Vorhin während des Mittagessen war mir noch eingefallen, dass ich wahrscheinlich die Funktion ADRESSE() in meiner Formel nicht brauche. Das habe ich nun so mir eben bestätigen. Da damit auch FINDEN() weg fällt, wird meine Formel schon wieder überschaubarer.

Die Anpassung an mehrere Spalten dürfte auf Grund der von mir eingebauten Flexibilität für Dich nun kein all zu großes Problem sein. Unten stehend nun die aktuelle Formel:

Tabelle2

	H	I	J	K	L	M	N
4	ID	Spalte1	Spalte2	Spalte3	Spalte4	Value	Spalte5
5	3					0	dd
6	3					0
7	3					0
8	4					-10000
9	4					-10000
10	4					-10000
11	4					-10000
14	15					-15000
15	53					15000
17	55					50000
18	56					0
19	57					0
20						40000
21

Formeln der Tabelle

Zelle Formel M20 =SUMMENPRODUKT(AGGREGAT(15;6;(INDEX(H:H;ZEILE(H4)+1):INDEX(H:H;ZEILE()-1)<>H4:INDEX(H:H;ZEILE()-2))*INDEX(M:M;ZEILE(H4)+1):INDEX(M:M;ZEILE()-1)*TEILERGEBNIS(2;INDIREKT("Z"&ZEILE(INDEX(H:H;ZEILE(H4)+1):INDEX(H:H;ZEILE()-1))&"S"&SPALTE();));ZEILE(INDEX(H:H;1):INDEX(H:H;ZEILE()-1-ZEILE(H4)))))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[neopa]

Hallo @ all,

warum mein vorheriger Beitrag bis jetzt doppelt da war, muss irgendwie mit der nachträglichen Bearbeitung meines vorherigen Beitrages zusammenhängen (ich hatte wiedermal die Icon-Anzeige nicht ausgeschalten und hatte sich deshalb wieder eine solche in der Formelanzeige eingeschlichen)