Getagged: INDEX

Excel – Bedingten Bereich über INDEX erzeugen – Namen und Matrixformeln

SUMMENPRODUKT, SUMMEWENN, SUMMEWENNS, ZÄHLENWENN und ZÄHLENWENNS errechnen eigentlich genau das, was ich ermitteln möchte. Die bilden nur dann bedingt eine Summe oder zählen die Werte eines Bereichs, wenn ein bestimmtes Kriterium mit den Werten des Bereichs übereinstimmt.

Zusätzlich kann man natürlich noch Matrixformeln vom Typ {=SUMME(WENN(Prüfungen;Bereich;““))} nutzen.

Aber der Bereich an sich lässt sich auch vorab dynamisch auf die gewünschten Kriterien reduzieren und dann darüber die einfache Summe bilden. Also bitte nicht schimpfen, dass es 1000-fach einfacher geht. Hier zeige ich auf, wie man INDEX auch noch nutzen könnte.

Download der Datei:
E387_Index_Thehos

Hier geht es zum YouTube-Video:

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 – Bereich für Datenüberprüfung mit INDEX identifizieren

INDEX ermittelt normalerweise einen Wert einer Matrix, der aus einer Spalte und einer Zeile identifiziert werden kann. Als Resultat von INDEX kann jedoch auch ein Bereich für die Datenüberprüfung genutzt werden.

So wird hier im Beispiel erst eine Sprache aus den Zellen D1:G1 ausgewählt und anschließend stehen die Werte der ausgewählten Sprache zur Auswahl zur Verfügung.

E384-1

Über Daten – Datenüberprüfung wird festgelegt, welche Werte zur Auswahl stehen sollen. Wichtig: Hier muss Liste ausgewählt werden.

Die Formel aus Zelle B1, welche in der Datenüberprüfung steckt.:

E384-2

Die Formel der Datenüberprüfung aus Zelle B2 lüftet das Geheimnis:

E384-3

Der Wert 0 an der Stelle, wo eigentlich die Zeilennummer stehen sollte führt dazu, dass alle Werte aller Zeilen der entsprechenden Spalte genommen werden.

Hier geht es zum Video: