Thema: Suchen mit der Funktion Index()
Wie bereits gezeigt, können Sie mit der Funktion Index()
den Wert an einer bestimmten Stelle in einem vorgegebenen
Bereich ermitteln.
Sie können die Index-Funktion aber auch so verwenden,
dass nicht der Inhalt der Zelle sondern die Zelle
selbst oder sogar ein ganzer Bereich zurückgeliefert wird.
Dadurch werden variable Bereichsangaben möglich,
was wir in den früheren Lektionen bereits gesehen haben.
Man kann die Index-Funktion sogar in direkt in einer Bereichsangabe
verwenden, insbesondere im Zusammenspiel mit Matrixfunktionen.
Was ist eine Matrixfunktion?
"Normale" Funktionen liefern immer nur einen Wert,
welcher in einer einzelnen Zelle ausgegeben wird.
Matrixfunktionen können jedoch auf mehrere Zellen wirken,
so dass ihre Ausgabe in mehreren Zellen erscheint.
Damit Excel den Unterschied zwischen "normalen" Funktionen
und Matrixfunktionen erkennt, werden Matrixfunktionen nicht mit
ENTER abgeschlossen sondern mit STRG+UMSCHALT+ENTER.
Im Formeleingabefeld wird eine Matrixfunktion dann in
geschweiften Klammern dargestellt.
Sie dürfen die geschweiften Klammern nicht selbst eingeben!
Wir verdeutlichen dies an folgendem Beispiel:
Markieren Sie dazu zunächst den Bereich C4:C10 und geben Sie anschließend folgende Formel ein:
=A4:INDEX(A4:A10;C3;1)
Im Beispiel sehen Sie, dass dann im grau hinterlegten Bereich tatsächlich die ersten 4 Produkte angezeigt werden.
Wie ist die obige Formel nun konkret zu verstehen?
Nun, in der Zelle C3 steht der Wert 4. Das bedeutet das die Funktion INDEX(A4:A10;C3;1) aus dem Bereich A4:A10 den Wert in Zeile 4, Spalte 1 ermitteln sollte. Da die Funktion aber Teil einer Bereichsangabe ist, wird an dieser Stelle nicht der Wert sondern die betreffende Zelle selbst zurückgegeben! Die ursprüngliche Formel =A4:INDEX(A4:A10;C3;1) wird folglich zu =A4:A7, d.h. es werden die Werte aus den Zellen A4 bis A7 ausgegeben. Wohin werden diese Werte ausgegeben? Nun, sie haben vorher den Bereich C4:C10 markiert. Somit wird der Inhalt von A4 in C4 ausgegeben, der Inhalt von A5 in C5 usw. Da in unserem Beispiel nur die ersten 4 Produkte ausgegeben werden, wird in den restlichen Zellen (C8, C9, C10) die Meldung #NV angezeigt.
Im zweiten Beispiel sehen Sie im Bereich F4:F10 eine Zahlenreihe. Es soll wie vorher in der Zelle C3 eine Zahl eingegeben werden können, beispielsweise die Zahl 4. In der Zelle F11 soll anschließend die Summe der ersten 4 Zahlen aus dem Bereich F4:F10 ermittelt werden. Hierfür verwenden wir die Index-Funktion erneut in einer Bereichsangabe. Die Formel sieht zunächst so aus:
=F4:INDEX(F4:F10;C3;1)
Wenn Sie also in C3 den Wert 4 eingegeben haben dann liefert die Index-Funktion die Zelle in der vierten Zeile, ersten Spalte des Bereichs F4:F10, also die Zelle F7. Die Formel =F4:INDEX(F4:F10;C3;1) wird somit ausgewertet zu =F4:F7. Wir müssen also lediglich noch die Funktion Summe() "drumherum" basteln, so dass die entsprechenden Werte aufsummiert werden. Die endgültige Formel in F11 lautet somit:
=Summe(F4:INDEX(F4:F10;C3;1))
Im ersten Beispiel haben Sie die Verwendung der Index-Funktion in einer Matrixformel kennengelernt und im zweiten Beispiel haben Sie die Index-Funktion für die Summierung in einem variablen Bereich verwendet.