27 Formeln zur INDEX-Funktion (Matrixversion) in Excel

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:

Index1

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 Die Index-Funktion 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.

Index2

=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.
Index3

=INDEX(INDIREKT(C77);2;2) =310

Fall: Die Tabelle befindet sich im gleichen Arbeitsblatt. Zeilen und Spalten mit VERGLEICH benannt.

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.

Index4

=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.

Download: Die Index-Funktion

Advertisements

4 Kommentare zu „27 Formeln zur INDEX-Funktion (Matrixversion) in Excel“

  1. 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

    Gefällt mir

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s