Aggregat mit Summe wenn Bedingung erfüllt

Alesandro · 2016-04-13T19:55:49+00:00

Liebe Forumsgemeinde, wie ermittle ich mit AGGREGAT die Summe wenn eine Bedingung erfüllt sein muss? Im beigefügten file gebe ich in Zelle B1 (Tabellenblatt Projektdetails) die Projektnr ein und ermittle mir mittels AGGREGAT und KKLEINSTE bzw KGRÖSSTE (Tabelle ROH über die Spalten Von und Bis) den START u. das ZIEL. Das funktioniert auch prima. Wie man die Gesamtstunden zum Projekt mittels SUMMEWENNS ermittelt weiß ich. Ich schaffe es leider nicht wie ich die Gesamtstunden des Projekts via AGGREGAT ermittle. Vielleicht kann mir einer von Euch Profis da weiterhelfen. https://www.herber.de/bbs/user/104947.xlsx Vielen Dank im Voraus. Alesandro

…auch eine reguläre AGGREGAT-Lösung gibt, Alesandro,
die aber auch die längste Fml hat. Die kürzeste Fml ist deine SUMMEWENNS-Fml, wobei hier auch schon das klassische SUMMEWENN (s.u.) gereicht hätte (1 Zeichen kürzer).
Dazwischen bewegen sich längenmäßig irreguläre AGGREGAT-Fmln wie du sie in den anderen Zellen angewendet hast, deren ErgebnisVektor dann aber noch extra summiert wdn muss, und Lösungen mit AGGREGAT und einer UDF, die ggf einen unzusammenhängenden Bereich erzeugt (bei dir ist er zusammenhängend), der von AGGREGAT als 3.Argument toleriert wird, sowie einer UDF, die gleich die ganze AGGREGAT-Fkt simuliert (in 2 Varianten). Die meisten dieser Fmln sind 1zellige (singulare) {MatrixFmln}:
65: =SUMMEWENN(ROH[Projektnr];Projektdetails!$B$1;ROH[Wochenstunden])
66: =SUMMEWENNS(ROH[Wochenstunden];ROH[Projektnr];Projektdetails!$B$1)
73:{=AggregateXk(9;6;(ROH[Projektnr]=Projektdetails!$B$1)*ROH[Wochenstunden])}
74: =AggregateXk(-9;6;(ROH[Projektnr]=Projektdetails!$B$1)*ROH[Wochenstunden])
85:{=AGGREGAT(9;6;ChooseIn(ROH[Wochenstunden];ROH[Projektnr]=Projektdetails!$B$1;WAHR;0))}
102:{=SUMME(AGGREGAT(15;6;ROH[Wochenstunden]*(ROH[Projektnr]=Projektdetails!$B$1);ZEILE(ROH[Projektnr])-1))}
110: =SUMMENPRODUKT(AGGREGAT(15;6;ROH[Wochenstunden]*(ROH[Projektnr]=Projektdetails!$B$1);ZEILE(ROH[Projektnr])-1))
180:{=AGGREGAT(9;6;INDEX(ROH[Wochenstunden];VERGLEICH(Projektdetails!$B$1;ROH[Projektnr];0)):
INDEX(ROH[Wochenstunden];MAX((ROH[Projektnr]=Projektdetails!$B$1)*ZEILE(ROH[Projektnr]))-1))}
Die letzte und längste Fml erzeugt/benutzt aber stets einen zusammenhängenden Bereich (die Werte müssen also sortiert sein - bei den anderen Fmln nicht!), denn es gibt mW keine Xl-StandardFkt, die unzusammenhängende Bereiche erzeugen, nur welche, die sie verarbeiten können. AggregateXk ist im Archiv zu finden, ChooseIn nicht, weil bisher unveröffentlicht (Neufassung in Arbeit!).
Feedback nicht unerwünscht! Gruß, Luc :-?
Besser informiert mit …

…gefunden, Werner,
auch wenn du nun zum konkreten Daten-/FmlFall und nicht da geantwortet hast, wo ich dich „hinlocken“ wollte… ;-)
Ich bin bei meiner AW ursprünglich davon ausgegangen, dass Alesandro, genau wie du, hiermit nur die Möglichkeiten von AGGREGAT ausloten wollte. Deshalb habe ich auch diesen FmlVgl angefertigt, denn im konkreten Fall ist hier ja sogar eine reguläre AGGREGAT-Lösung möglich, wenn auch unnötig umständlich.
Immerhin konnte ich so erkennen und nachweisen, dass AGGREGAT unzusammenhänge ZellBereiche verarbeiten kann, was nicht unbedingt zu erwarten war (die ~WENNs-Fktt können das nicht!). Das wurde klar, als ich eine ProjektNr innerhalb des Auswahlbereiches geändert habe. In diesem Fall gibt die UDF ChooseIn (ebenso wie ihr in Erarbeitung befindlicher Nachfolger ChooseIf) nämlich einen aus 2 TeilBereichen zusammengesetzten unzusammenhängenden ZellBereich zurück, erkennbar auch daran, dass die UDF, solo angewendet, #WERT! liefert, weil Xl dieses Ergebnis nicht auf einen ZellBereich abbilden kann. Als Argument einer anderen Fkt verwendet, wird aber klar, dass real doch ein Ergebnis geliefert wird, das viele alte XlFktt weiterverarbeiten können.
Im Zusammenhang mit meinen obigen AWen hatte ich erwähnt, dass Xl zwar viele Fktt hat, die unzusammenhängende ZellBereiche verarbeiten können, aber mW keine, die sie erzeugen kann. Das ist zwar iW richtig, doch das liegt nur daran, dass alle XlFktt so pgmmiert wurden, dass sie stets auch ein abbildbares Ergebnis liefern. Solche „HilfsFktt“ wie meine UDF ChooseIn kommen also standardmäßig nicht vor! INDEX, das sowohl Datenfelder als auch beide Arten von ZellBereichen verarbeiten kann, hätte auch dazu das Potenzial, wenn das nicht (bewusst) eingeschränkt worden wäre. Im folgenden Bsp zeige ich das:

	A	B	C	D	E	F	G	H	I
2	1	alpha	beta	gamma	delta	epsilon	vau	zeta	eta
3	2	beta	C2:I2:=INDEX($B$2:$B$12;SPALTE(B2);1)
4	3	gamma		1	18		→	E4⇒18	F4⇒18
5	4	delta	WAHR	3	E4:F4: {=SUMME(INDEX($A$2:$A$10;{1;3;6;8};1)1^ZEILE(A1:A4))}*
6	5	epsilon	FALSCH	6	18		→	E6⇒18	F6⇒18
7	6	vau	FALSCH	8	E6:F6: {=AggregateXk(9;6;INDEX($A$2:$A$10;{1;3;6;8};1)1^ZEILE(A1:A4))}*
8	7	zeta	WAHR	18	D4:D7: {=INDEX($A$2:$A$12;{1;3;6;8};1)}			D8:=SUMME(D4:D7)
9	8	eta	C5:C8: {=IsColor(INDEX($B$2:$B$12;{1;3;6;8};1);TxEval(T(JETZT())&"CellColor("&RAddress(C4)&")"))}

Hier kann man einerseits erkennen, dass INDEX sowohl Datenfelder als auch ZellBereiche liefern kann, aber nur in einer MatrixFml, wenn auch genügend Zellen ausgewählt wurden. Im Bsp ist das der ErgebnisBereich C5:C8, der die Farben der zurückgegebenen 4 Zellen mit Rot vergleicht. Das wäre nicht möglich, wenn nur ZellWerte geliefert würden.
In D4:D7 geschieht dergleichen mit Spalte A. Hier kommt es aber auf die ZellWerte an, die summiert wdn sollen. Wenn man das in einer Fml tun wollte (wie in E4:F4 bzw E6:F6), müsste man diesen unzusammenhängenden Bereich noch in eine auswertbare Form bringen, wodurch er zum Datenfeld wird. Trotzdem wird eine 1zellige (singulare) MatrixFml immer noch kein richtiges Ergebnis liefern. Dafür benötigt sie einen weiteren Anstoß in Form einer 2., mitausgewählten Zelle, so dass eine quasi-1-zellige (duale) MatrixFml entsteht. Diese liefert dann auch das richtige Ergebnis, aber nunmal doppelt, weshalb ich das hier in einer speziellen VerbundZelle untergebracht habe, die die Xl-Warnung, dass MatrixFmln in VerbundZellen nicht möglich sind, ad absurdum führt (bei singularen MxFmln erscheint die Warnung nicht!).
Da die regulären Anwendungen von AGGREGAT (Arg1=1…13) mit Datenfeldern nichts anfangen können und die irregulären das Ganze nur unnötig komplizieren würden, habe ich in E6:F6 die UDF AggregateXk eingesetzt, um diesen INDEX-Effekt an einem 2.Bsp zu demonstrieren.
(Anm: Die in C2 angezeigte Fml ist nur ein Stino-KontrastPgm für eine einfache TransponierungsAufgabe.)
Gruß, Luc :-?

Aggregat mit Summe wenn Bedingung erfüllt

Forumthread: Aggregat mit Summe wenn Bedingung erfüllt

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Aggregatfunktionen in Excel: Summe bei erfüllten Bedingungen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Aggregat mit Summe wenn Bedingung erfüllt

Forumthread: Aggregat mit Summe wenn Bedingung erfüllt

12 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Aggregatfunktionen in Excel: Summe bei erfüllten Bedingungen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread