Die Index-Funktion von Excel gehört zur Kategorie der Nachschlage- und Verweisfunktionen.
In der Excel-Hilfe ist zur Erläuterung zu lesen:
„Gibt den Wert eines Elements in einer Tabelle oder einer Matrix zurück, das innerhalb der Matrix die Position einnimmt, die durch die für die Zeile sowie die Spalte angegebenen Indizes festgelegt ist.“
Interessant an der INDEX-Funktion ist, dass sie in zwei Versionen nutzbar ist, diese sind:
– Matrixversion
– Bezugsversion
In diesem Beitrag geht es nur um die Matrixversion.
Um die Index-Funktion nutzen zu können, müssen klare und eindeutige Tabellen als Quelldateien vorhanden sein. D.h. jeder Zelle in der Tabelle muß eine eindeutige Zeilen- und eine eindeutige Spaltenüberschrift zugeordnet werden können. Dazu eine Beispieltabelle:
Hier sind dem Wert 180 in der Zelle B8 die Zeilenüberschrift „Werk2“ und die Spaltenüberschrift „1“ (Monat 1 = Januar) zuordenbar.
Die Matrixversion arbeitet mit der Syntax
=INDEX(Matrix;Zeile;Spalte)
und dabei gelten gemäß Excel-Hilfe:
Matrix ist ein Zellbereich oder eine Matrixkonstante.
Besteht das Argument Matrix aus nur einer Zeile oder Spalte, ist das entsprechende Argument Zeile bzw. Spalte optional.
Erstreckt sich Matrix über mehrere Zeilen und Spalten und ist nur eines der Argumente Zeile oder Spalte angegeben, liefert INDEX eine Matrix, die der gesamten zugehörigen Zeile oder Spalte von Matrix entspricht.
Zeile markiert die Zeile in der Matrix, aus der ein Wert zurückgegeben werden soll. Wird Zeile nicht angegeben, muss Spalte angegeben werden.
Spalte markiert die Spalte in der Matrix, aus der ein Wert zurückgegeben werden soll. Wird Spalte nicht angegeben, muss Zeile angegeben werden.
Damit soll es genug sein mit der Theorie, die aber unbedingt notwendig war.
Die Beispieldatei
enthält im Blatt „Tabelle1“ eine kleine Tabelle, die ich mit dem Namen „Daten1“ versehen habe (Formeln / Namen definieren).
Das Blatt „Tabell2“ enthält eine ähnliche Tabelle, der ich den Namen „Daten2“ gegeben habe.
Um Dir ansatzweise zeigen zu können, in welchen Varianten die INDEX-Funktion verwendet werden kann, habe ich 27 Formeln herausgesucht, die jeweils auf eine der beiden Datentabellen zugreifen und den gewünschten Wert heraus ziehen.
Die Beispieldatei enthält eine Spalte mit der Überschrift „Formel, Nr.“. In dieser Reihenfolge will ich Dir die Varianten nun vertraut machen.
Fall: Die Tabelle befindet sich im gleichen Arbeitsblatt, die Matrix wird direkt benannt.
Formel 1:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird durch die Adressen von / bis dargestellt.
=INDEX(B7:D9;2;2) =290
Formel 2:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird durch Namensverwendung dargestellt.
=INDEX(Daten1;2;2) =290
Formel 3:
Abrufen der Summe zweier Zellen. Die Matrix wird durch die Adressen von / bis dargestellt.
=SUMME(INDEX(B7:D9;2;2):INDEX(B7:D9;2;3)) =540
Formel 4:
Abrufen der Summe zweier Zellen. Die Matrix wird durch Namensverwendung dargestellt.
=SUMME(INDEX(Daten1;2;2):INDEX(Daten1;2;3)) =540
Formel 5:
Abrufen der Summe einer Zeile. Die Matrix wird durch die Adressen von / bis dargestellt.
=SUMME(INDEX(B7:D9;2;)) =720
Das Spaltenargument bleibt in der Formel frei, jedoch muss das Semikolon hinter dem Zeilenargument gesetzt werden.
Formel 6:
Abrufen der Summe einer Zeile. Die Matrix wird durch Namensverwendung dargestellt.
=SUMME(INDEX(Daten1;2;)) =720
Formel 7:
Abrufen der Summe einer Spalte. Die Matrix wird durch die Adressen von / bis dargestellt.
=SUMME(INDEX(B7:D9;;2)) =950
Das Zeilenargument bleibt in der Formel frei, jedoch muss jeweils das Semikolon gesetzt werden.
Formel 8:
Abrufen der Summe einer Spalte. Die Matrix wird durch Namensverwendung dargestellt.
=SUMME(INDEX(Daten1;;2)) =950
Formel 9:
Abrufen des Mittelwerts einer Zeile. Die Matrix wird durch die Adressen von / bis dargestellt.
=MITTELWERT(INDEX(B7:D9;2;)) =240
Statt MITTELWERT können viele andere Funktionen verwendet werde, z.B. MAX, MIN, STABW usw.
Formel 10:
Abrufen des Mittelwerts einer Zeile. Die Matrix wird durch Namensverwendung dargestellt.
=MITTELWERT(INDEX(Daten1;2;)) =240
Fall: Die Tabelle befindet sich im gleichen Arbeitsblatt, die Matrix wird indirekt benannt.
Formel 11:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird indirekt benannt.
=INDEX(INDIREKT(C43);2;2) =290
Fall: Die Tabelle befindet sich im gleichen Arbeitsblatt, die Matrix wird direkt benannt.
Formel 12:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird durch die Adressen von / bis dargestellt.
=INDEX(Tabelle2!B7:D9;2;2) =310
Formel 13:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird durch Namensverwendung dargestellt.
=INDEX(Daten2;2;2) =310
Formel 14:
Abrufen der Summe zweier Zellen. Die Matrix wird durch die Adressen von / bis dargestellt.
=SUMME(INDEX(Tabelle2!B7:D9;2;2):INDEX(Tabelle2!B7:D9;2;3)) =590
Formel 15:
Abrufen der Summe zweier Zellen. Die Matrix wird durch Namensverwendung dargestellt.
=SUMME(INDEX(Daten2;2;2):INDEX(Daten2;2;3)) =590
Formel 16:
Abrufen der Summe einer Zeile. Die Matrix wird durch die Adressen von / bis dargestellt.
=SUMME(INDEX(Tabelle2!B7:D9;2;)) =780
Formel 17:
Abrufen der Summe einer Zeile. Die Matrix wird durch Namensverwendung dargestellt.
=SUMME(INDEX(Daten2;2;)) =780
Formel 18:
Abrufen der Summe einer Spalte. Die Matrix wird durch die Adressen von / bis dargestellt.
=SUMME(INDEX(Tabelle2!B7:D9;;2)) =940
Formel 19:
Abrufen der Summe einer Spalte. Die Matrix wird durch Namensverwendung dargestellt.
=SUMME(INDEX(Daten2;;2)) =940
Formel 20:
Abrufen des Mittelwerts einer Zeile. Die Matrix wird durch die Adressen von / bis dargestellt.
=MITTELWERT(INDEX(Tabelle2!B7:D9;2;)) =260
Formel 21:
Abrufen des Mittelwerts einer Zeile. Die Matrix wird durch Namensverwendung dargestellt.
=MITTELWERT(INDEX(Daten2;2;)) =260
Fall: Die Tabelle befindet sich in einem anderen Arbeitsblatt, die Matrix wird indirekt benannt.
Formel 22:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird indirekt benannt.
=INDEX(INDIREKT(C77);2;2) =310
Fall: Die Tabelle befindet sich im gleichen Arbeitsblatt. Zeilen und Spalten mit VERGLEICH benannt.
Zur Funktion VERGLEICH findest Du im Blog einen gesonderten Beitrag.
Formel 23:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird durch die Adressen von / bis dargestellt.
Zeilen- und Spaltenargument werden mit VERGLEICH ermittelt und benannt.
=INDEX(B7:D9;VERGLEICH(B84;Werke;0);B86) =290
Formel 24:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird durch Namensverwendung dargestellt.
Zeilen- und Spaltenargument werden mit VERGLEICH ermittelt und benannt.
=INDEX(Daten1;VERGLEICH(B84;Werke;0);B86) =290
Fall: Die Tabelle befindet sich in einem anderen Arbeitsblatt. Zeilen und Spalten mit VERGLEICH benannt.
Formel 25:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird durch die Adressen von / bis dargestellt.
Zeilen- und Spaltenargument werden mit VERGLEICH ermittelt und benannt.
=INDEX(Tabelle2!B7:D9;VERGLEICH(B95;Werke;0);B97)
Formel 26:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird durch Namensverwendung dargestellt.
Zeilen- und Spaltenargument werden mit VERGLEICH ermittelt und benannt.
=INDEX(Daten2;VERGLEICH(B95;Werke;0);B97) =310
Fall: Die Tabelle befindet sich im gleichen Arbeitsblatt. Die Matrix wird mit BEREICH.VERSCHIEBEN bestimmt.
Formel 27:
Abrufen des Wertes einer einzelnen Zelle. Die Matrix wird mit BEREICH.VERSCHIEBEN bestimmt.
=INDEX(BEREICH.VERSCHIEBEN($A$106;1;1;3;3);2;2) =290
Zu BEREICH.VERSCHIEBEN habe ich erst kürzlich einen Artikel veröffentlicht.
Selbst wirst Du ganz sicher auf weitere Kombinationen zwischen gleichem / anderen Arbeitsblatt, direkt / indirekt, ohne / mit VERGLEICH usw. kommen.
Die INDEX-Funktion ist damit sehr variabel. Wichtig zu wissen ist auch, dass einzelne Argumente in der Formel durch andere Funktionen ersetzt werden können. Das geht durchaus nicht bei allen Funktionen.
In einem späteren Beitrag werde ich Dir die Bezugsversion der INDEX-Funktion vorstellen. Auch diese Version ist sehr interessant, obwohl sie nach meinem Empfinden weit weniger genutzt wird.
Welche Erfahrungen hast Du mit der INDEX-Funktion? Zu welchen Zwecken nutzt Du sie? Hast Du Fragen zur Anwendung der Funktion?
Schreib einen Kommentar oder eine E-Mail.
Entdecke mehr von Clevercalcul
Subscribe to get the latest posts sent to your email.
Hallo Gerhard,
ich bin durch Twitter auf deine Seite gelangt und freue mich gerade sehr über eine gute Übersicht zur Indexfunktion. Auch wenn ich derzeit die INDEX-Funktion selbst noch nicht in dieser Weise nutze habe ich doch wenigstens direkt eine mögliche Verwendung dafür und bin ernsthaft am Überlegen eine vorhandene Tabelle (Gesamtbudget und Teilblätter mit einzelnen Budgetparamtern) mit dieser Funktion ein klein wenig zu verschlanken. Dieses würde mir dann tatsächlich die ein oder andere Zwischensumme ersparen… 🙂
Viele Grüße und noch viel Erfolg beim Bloggen und der Umsetzung deines ambitionierten Plan bzgl. der 3 bis 4 Artikel alle zwei Wochen. Ich habe auf jeden Fall dein Blog per RSS aboniert und hoffe, dass dir die Artikelideen nicht so schnell ausgehen. Immerhin hast du mit Excel zumindest ein recht weites Feld bestellt :-).
Viele Grüße
Andreas Unkelbach