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

Vor einer Woche habe ich in Teil 1 des Artikels zur INDEX-Bezugsversion Möglichkeiten aufgezeigt, Werte im gleichen Arbeitsblatt abzufragen.

Im heutigen Teil 2 will ich Formelvarianten bei der Suche in anderen Arbeitsblättern vorstellen.

Suche in einem anderen Arbeitsblatt, direkte Bezugsnennung

Wie schon in Teil 1 erwähnt, habe ich dazu in Tabelle2 meiner Arbeitsmappe die Tabellen eingerichtet und mit Namen versehen.

Als Erstes suche ich die Werte für Leisten in Werk5 im Februar, also den Wert einer einzelnen Zelle.

Ohne namentliche Benennung der Teilbereiche lautet die Formel:

Formel 12: =INDEX((Tabelle2!B5:D7;Tabelle2!B10:D12;Tabelle2!B15:D17);1;2;2) =196

Verwende ich die vergebenen Namen, schreibe ich so:

Formel 13: =INDEX((Werk4;Werk5;Werk6);1;2;2) =196

Jetzt will ich die Summe für das Produkt Bretter im Werk5 für Februar bis März abfragen. Ich Suche also die Summe zweier Zellen.

Formel 14: =SUMME(INDEX((Tabelle2!B5:D7;Tabelle2!B10:D12;Tabelle2!B15:D17);2;2;2):INDEX((Tabelle2!B5:D7;Tabelle2!B10:D12;Tabelle2!B15:D17);2;3;2)) =238

Verwende ich die vergebenen Namen, lautet die Formel so:

Formel 15: =SUMME(INDEX((Werk4;Werk5;Werk6);2;2;2):INDEX((Werk4;Werk5;Werk6);2;3;2)) =238

Gesucht wird die Summe der Zeile Kanthölzer im Werk6. Es müssen daher alle Werte in der entsprechenden Zeile addiert werden.

Formel 16: =SUMME(INDEX((Tabelle2!B5:D7;Tabelle2!B10:D12;Tabelle2!B15:D17);3;;3)) =125

Bei Verwendung der Namen der Teilbereiche schreibe ich diese Formel:

Formel 17: =SUMME(INDEX((Werk4;Werk5;Werk6);3;;3)) =125

Suche ich die Summe der Spalte Januar im Werk4 (was im Beispiel bei unterschiedlichen Produkten nur bedingt Sinn macht), nehme ich diese Formel:

Formel 18: =SUMME(INDEX((Tabelle2!B5:D7;Tabelle2!B10:D12;Tabelle2!B15:D17);;1;1)) =460

Unter namentlicher Aufzählung der Bezugsbereiche lautet die Formel:

Formel 19: =SUMME(INDEX((Werk4;Werk5;Werk6);;1;1)) =460

Ich will auch hier einen Mittelwert abfragen, den der Zeile Bretter im Werk4 und schreibe:

Formel 20: =MITTELWERT(INDEX((Tabelle2!B5:D7;Tabelle2!B10:D12;Tabelle2!B15:D17);2;;1)) =236,6667

Formel 21: =MITTELWERT(INDEX((Werk4;Werk5;Werk6);2;;1)) =236,6667

Ein Mittelwert lässt sich ebenso für eine Spalte errechnen.

Suche in einem anderen Arbeitsblatt, indirekte Bezugsnennung

Gesucht wird der Märzwert für das Produkt Leisten in Werk5.

Dazu richte ich mir eine Liste mit den Elementen Werk4, Werk5, Werk6 ein und benenne sie mit “WerkeB”. Für die Zelle C121 lasse ich über die Datenüberprüfung die Liste “WerkeB” zu und treffe dort meine Vorauswahl, hier Werk5.

Zusätzlich verwende ich nun die Funktion INDIREKT und schreibe:

Formel 22: =INDEX(INDIREKT(C152);1;3;1) =119

Um bei der Bezugsversion zu bleiben, wurde der durch INDIREKT bereits bestimmte Bereich nochmals durch die 1 bestätigt.

Suche im gleichen Arbeitsblatt, Zeilen und Spalten mit VERGLEICH

Hier suche ich den Februarwert Bretter in Werk3.

Für Zelle B134 lasse ich die Liste “WerkeA2 zu und wähle Werk3. In B136 schreibe ich die 2 für den Monat Februar.

Die Formel lautet nun unter zusätzlicher Verwendung der Funktion VERGLEICH:

Formel 23: =INDEX((B6:D8:B11:D13;B16:D18);2;B136;VERGLEICH(B134;WerkeA;0)) =133

Setze ich die namentlichen Bezugsbereiche ein, lautet die Formel:

Formel 24: =INDEX((Werk1;Werk2;Werk3);2;B136;VERGLEICH(B134;WerkeA;0)) =133

Suche in einem anderen Arbeitsblatt, Zeilen und Spalten mit VERGLEICH

Gesucht wird der Januarwert Kanthölzer in Werk5.

Für B146 lasse ich die Liste “WerkeB” zu und wähle Werk5.

In B148 schreibe ich die 1 für Januar. Das Produkt Kanthölzer wähle ich einfach mit der Zeilenangabe 3.

Formel 25: =INDEX((Tabelle2!B5:D7;Tabelle2!B10:D12;Tabelle2!B15:D17);3;B148;VERGLEICH(B146;WerkeB;0)) =11

Formel 26: =INDEX((Werk4;Werk5;Werk6);3;B148;VERGLEICH(B146;WerkeB;0)) =11

Suche im gleichen Arbeitsblatt, Bezug mit einem Drehfeld bestimmen

Ausgangspunkt sind die Tabellen Werk1, Werk2 und Werk3.

Gesucht wird der Februarwert Leisten in Werk2

Über ein Drehfeld soll die Auswahl des Werkes erfolgen (ich mag Drehfelder).

In den Eigenschaften nehme ich folgende Einstellungen vor:

Minimalwert: 1
Maximalwert: 3
Schrittweite: 1

Für die Zellverknüpfung trage ich B160 ein. In E16 kann ich den Monat Februar mit 2 wählen.

Durch Betätigen der Pfeile im Drehfeld kann ich den Wert in B160 von 1 bis 3 erhöhen bzw. von 3 bis 1 senken.

Nun kann die Abfrage formuliert werden:

Formel 27: =INDEX((Werk1;Werk2;Werk3);1;E160;B160) =206

Damit beende ich den Teil 2 meines Beitrages zur Bezugsversion der INDEX-Funktion. Probiere es einfach mal aus, vielleicht gefällt dir diese Version besser als die Matrixversion.