Getagged: Bereich

Excel – Summe aus Schnittmenge ermittelter Bereiche – Teil 3

Die Summe oder auch der Mittelwert können aus der Schnittmenge von zwei Bereichen ermittelt werden. In diesem dritten Teil geht es um die Ermittlung der passenden Spalten und Zeilen per Funktionen VERGLEICH und ZÄHLENWENN, sowie die korrekte Ansprache über die Funktion INDIREKT(Z1S1;FALSCH) und INDIREKT(A1;WAHR).

Weiter Text folgt noch… mehr gibt es im Video und in der Übungsdatei.

Hier ist die Übungsdatei:
Excel 454 Summe Schnittmengen 3

Hier ist das Video:

Videolink: http://youtu.be/n_sSAQXm5G4

Excel – SUMME über Bereich – AGGREGAT und INDEX

Es soll die Summe eines Bereichs gebildet werden. Der Bereich wird allerdings über zwei Markierungen neben der Tabelle definiert. Von der Zeile der ersten x-Markierung bis zur Zeile der zweiten x-Markierung soll summiert werden. Der Clou ist also, dass sich der Bereich für die Summenfunktion dynamisch zusammensetzen soll. Statt aber nun zum Beispiel =SUMME(B7:B12) einzugeben, werden die Zeilen für B7, also 7, und B12, also Zeile 12, über die Funktion INDEX ermittelt.

E361

=SUMME(INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1):INDEX(B:B;AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1);1))

Dabei muss die Position, also die Zeile, des oberen x-Wertes ermittelt werden. Dies geschieht durch die Funktion: AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1). Nur in den Zellen, in denen ein x steht, wird auch durch WAHR geteilt – also in diesem Fall durch 1. Es werden also alle Zeilennummern des Bereichs genommen und durch 1 oder 0 geteilt. Von diesen Zeilennummern nimmt AGGREGAT(15;6;Array;1) die kleinste. Im Beispiel resultiert die Zahl 7.

Die Zeile des unteren x-Wertes wird durch AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1) ermittelt. Auch hier wird geprüft, ob ein x in einer der Zellen steht. Falls dies mit WAHR beantwortet werden kann, wird mit WAHR, also mit 1 multipliziert. Das Ergebnis ist eine List von Zeilennummern, die entweder mit 1 oder mit 0 bei FALSCH multipliziert werden. Von diesen Werten nimmt AGGREGAT(14;4;Array;1) den größten Wert. Im Beispiel resultiert die Zahl 12.

Statt nun B7:B12 in die Funktion SUMME als Bereich einzugeben, werden die Koordinaten über INDEX ermittelt. INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1) führt also zu INDEX(B:B;7;1) und dies wiederum zur Zelle B7. Es wird die siebte Zelle aus Spalte B genommen. Das gleiche Prinzip gilt für B12. Die beiden INDEX-Funktionen werden über einen Doppelpunkt verbunden, so dass im Endeffekt B7:B12 resultiert.

Sollte kein x gesetzt sein, resultiert die Fehlermeldung #Zahl!
Hier könnte man die Formel noch einmal um die Funktion WENNFEHLER ergänzen:

=WENNFEHLER(SUMME(INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1):INDEX(B:B;AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1);1));"")

Die Datei zum Download:
E361_SUMME_AGGREGAT_BEREICH

Das Video bei YouTube: