Getagged: SUMME

Excel – Dynamischen Bereich für Funktionen mit INDEX ermitteln

Aus einer Matrix heraus soll nicht eine Zelle über INDEX ermittelt werden, sondern ein ganzer Bereich. Diesen Bereich kann man natürlich auch mit BEREICH.VERSCHIEBEN identifizieren. Aber wie wäre es mit einem Spezialfall der INDEX-Funktion?

E385

=SUMME(INDEX(E2:H7;0;VERGLEICH(B1;E1:H1;0)))

Summe ermittelt die Summe über einen Bereich.
E2:H7 ist der Bereich der Matrix, der alle Werte beinhaltet.
Mit Vergleich wird der Wert B1 in den Überschriften oberhalb der Matrix gesucht. Ergebnis ist die Position, dies wird als Spaltennummer für die Matrix genutzt.
Mit dem Parameter 0 in INDEX wird gesagt, dass alle Werte aller Zeilen der gewünschten Spalte genommen werden sollen.

Übungsdatei zum Download:
E385_Index_Thehos

Hier geht es zum Video:

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: