27 Formeln zur INDEX-Bezugsversion in Excel (Teil 1)

Vor einiger Zeit habe ich hier die Matrixversion der INDEX-Funktion in Excel anhand von 27 beispielhaften Formeln erläutert.

In diesem Beitrag geht es um die Bezugsversion von INDEX.

Die Funktion

Zuerst die unumgängliche Theorie. Die Excel-Hilfe erläutert die Version so:

“Liefert den Bezug der Zelle, in der sich eine bestimmte Zeile und Spalte schneiden. Verbergen sich hinter dem Argument Bezug nicht zusammenhängende Markierungen, können Sie die Markierung angeben, die ausgewertet werden soll.”

Als Syntax wird

=INDEX(Bezug;Zeile;Spalte;Bereich)

verwendet. Dabei gilt gemäß Excel-Hilfe:

Bezug   ist der Bezug auf einen oder mehrere Zellbereiche. Für den Fall, dass Sie statt Bezug einen nicht zusammenhängenden Bereich eingeben, müssen Sie Bezug in Klammern setzen.

Erstreckt sich jeder in Bezug angegebene Teilbereich über nur eine Zeile oder Spalte, ist das Argument Zeile bzw. Spalte optional. Beispielsweise können Sie INDEX(Bezug;;Spalte) verwenden, wenn ein Bezug nur eine Zeile enthält.

Zeile   ist die Nummer der Zeile im Bereich Bezug, aus dem der Bezug zurückgegeben werden soll.

Spalte   ist die Nummer der Spalte im Bereich Bezug, aus dem der Bezug zurückgegeben werden soll.

Bereich   bestimmt den Zellbereich in Bezug, dessen Schnittpunkt von Zeile und Spalte geliefert werden soll. Der erste markierte oder eingegebene Teilbereich erhält die Nummer 1, der zweite die Nummer 2 usw. Ist Bereich nicht angegeben, verwendet INDEX den Teilbereich 1.

Verbergen sich hinter Bezug zum Beispiel die Zellen (Teilbereiche) A1:B4, D1:E4 und G1:H4, dann entspricht Bereich 1 dem Teilbereich A1:B4, Bereich 2 dem Teilbereich D1:E4 und Bereich 3 dem Teilbereich G1:H4.

Damit eine Formel auch für alle Teilbereiche gleichermaßen funktioniert, müssen die Teilbereiche auch gleichartig dimensioniert sein. Interessant ist sicher auch, dass sich Teilbereiche auch überschneiden dürfen.

Neu ist an dieser Version letztlich nur das Argument “Bereich”.

Wie arbeitet die Funktion?

Zur Erläuterung der Funktion habe ich drei kleine Tabellen erstellt, die z.B. die Mengen von drei Produkten in drei Werken für die Monate Januar bis März (1-3)ausweisen.

Als Bezug sind hier drei Bereiche zu definieren (in der Matrixversion hieß das Argument “Matrix”. Die drei Bereiche befinden sich im gleichen Arbeitsblatt.

Da es sich um keinen zusammenhängenden Bereich handelt, ist die Aufzählung in der Formel in Klammern zu setzen.

Für die Fälle, in denen die Daten sich in einem anderen Arbeitsblatt befinden, habe ich diese Bereiche angelegt und mit Namen versehen.

Die Fallunterscheidungen will ich, soweit es geht, analog dem Beitrag zur Matrixversion vornehmen.

Suche im gleichen Arbeitsblatt, direkte Bezugsnennung

Gesucht wird die Menge Bretter in Werk1 im Februar.

Dazu suche ich den Wert aus einer Zelle:

Formel 1: =INDEX((B6:D8:B11:D13;B16:D18);2;2;1) =290

Dazu wird in der INDEX-Formel zuerst eine Aufzählung der Bezüge vorgenommen (in Klammern), Zeile 2 für das zweite Produkt, Spalte 2 für den Februar und schließlich die Auswahl 1 für den ersten Bezug.

Übersichtlicher wird die Formel, wenn die Bezugsbereiche mit Namen versehen werden:

Formel 2: =INDEX((Werk1;Werk2;Werk3);2;2;1) =290

Gesucht ist die Summe der Mengen an Leisten für die Monate Januar-Februar im Werk2.

Ich suche die Summe zweier Zellen:

Formel 3: =SUMME(INDEX((B6:D8:B11:D13;B16:D18);1;1;2):INDEX((B6:D8:B11:D13;B16:D18);1;2;2)) =400

Bei namentlicher Benennung der Bezugsbereiche lautet die Formel:

Formel 4: =SUMME(INDEX((Werk1;Werk2;Werk3);1;1;2):INDEX((Werk1;Werk2;Werk3);1;2;2)) =400

Gesucht wird die Summe der Zeile Kanthölzer im Werk3.

Hier suche ich die Summe aller Zeilenwerte.

Formel5: =SUMME(INDEX((B6:D8:B11:D13;B16:D18);3;;3)) =454

Wieder versehe ich die Bezugsbereiche mit Namen und schreibe dann diese Formel:

Formel 6: =SUMME(INDEX((Werk1;Werk2;Werk3);3;;3)) =454

Als nächstes suche ich die Summe der Spalte März (3) im Werk1.

Ohne namentliche Benennung lautet die Formel:

Formel 7: =SUMME(INDEX((B6:D8:B11:D13;B16:D18);;3;1)) =560

Mit namentlicher Benennung der Bezüge lautet die Formel:

Formel 8: =SUMME(INDEX((Werk1;Werk2;Werk3);;3;1)) =560

Schließlich suche ich den Mittelwert Zeile Bretter in Werk2:

Liste ich die Bezugsbereiche auf, arbeite ich mit dieser Formel:

Formel 9: =MITTELWERT(INDEX((B6:D8:B11:D13;B16:D18);2;;2)) =137

Bei Benutzung von Namen für die Bereiche, schreibe ich die Formel so:

Formel 10: =MITTELWERT(INDEX((Werk1;Werk2;Werk3);2;;2)) =137

Suche im gleichen Arbeitsblatt, indirekte Bezugsnennung

Gesucht wird der Januarwert Kanthölzer in Werk1, also der Wert einer einzelnen Zelle.

Zunächst lege ich mir eine Liste mit den Elementen Werk1, Werk2, Werk3 an und versehe sie mit dem Namen “WerkeA”. In C76 habe ich per Datenüberprüfung die Liste “WerkeA” zugelassen.

Angenommen, ich habe in Zelle C76 die Auswahl “Werk1” getroffen und arbeite mit dieser Formel

Formel 11: =INDEX((B6:D8:B11:D13;B16:D18);3;1;VERGLEICH($C$76;WerkeA;0))

erhalte ich als Ergebnis =60

Verwende ich zusätzlich die Funktion INDIREKT, komme ich mit dieser Formel ans Ziel:

Formel 12: =INDEX(INDIREKT(C76);3;1;1) =60

Hier endet der Teil 1 des Beitrages. In einer Woche folgt Teil 2.