Excel INDIRECT(): Dynamische Referenzen in deinen Formeln erstellen

Die Funktion INDIRECT() ist eines der mächtigsten, aber oft missverstandenen Werkzeuge von Excel. Im Gegensatz zu Funktionen, die direkt Berechnungen durchführen, wandelt INDIRECT() Textstrings in lebende Zellbezüge um. So kannst du dynamische und flexible Formeln erstellen, die sich an variable Eingaben anpassen. Ganz gleich, ob du ein zusammenfassendes Dashboard erstellst, auf der Grundlage von Benutzerauswahlen auf verschiedene Blätter verweist oder die Zellbezüge dynamisch änderst, INDIRECT() kann eine große Hilfe sein.

In diesem Leitfaden zeige ich dir, wie du die Funktion INDIRECT() für Zellreferenzen verwendest und häufige Probleme behebst, einschließlich der Frage, wann du die Funktion nicht verwenden solltest. Wenn du mit einigen Grundlagen nicht vertraut bist, empfehle ich dir, unseren Kurs Einführung in Excel zu besuchen, um eine solide Grundlage zu schaffen. 

Was die Funktion INDIRECT() bewirkt

Die Funktion INDIRECT() in Excel ruft den Wert einer Zelle oder eines Bereichs auf der Grundlage einer als Textzeichenfolge angegebenen Referenz ab. Anstatt eine Zelladresse wie "A1" in einer Formel fest zu codieren, kannst du mit INDIRECT() diese Adresse dynamisch mit Text definieren, den Excel als Live-Referenz interpretiert.

Die Syntax der Funktion INDIRECT() ist unten dargestellt:

INDIRECT(ref_text, [a1])

Wo:

• ref_text (erforderlich): Eine Zeichenkette, die einen gültigen Zell- oder Bereichsbezug darstellt. Dies kann sich auf eine Zelle wie "A1", einen benannten Bereich oder sogar einen Verweis auf ein anderes Blatt wie "Sheet2!B5" beziehen.

• [a1]: Dies ist ein optionales Argument, das den Typ der Referenz in ref_text angibt. Wenn a1 TRUE ist oder weggelassen wird, wird ref_text als A1-Referenz interpretiert. Wenn a1 FALSE ist, wird ref_text wie ein Verweis im Stil von R1C1 behandelt.

Wie man die Funktion INDIRECT() verwendet

Das folgende Beispiel zeigt, wie die Funktion INDIRECT() dynamisch Text in Zellbezüge umwandelt.

Angenommen, Zelle C2 enthält den Text "A2" und Zelle A2 enthält den Wert 100. Die folgende Funktion gibt ebenfalls 100 zurück, weil Excel den Inhalt von C2 liest und INDIRECT() als Referenz behandelt.

=INDIRECT(C2)

INDIRECT() verwenden, um auf eine andere Zelle in Excel zu verweisen. Bild vom Autor.

Wenn Zelle A5 die Zahl 5 enthält, kannst du mit der folgenden Formel eine dynamische Referenz erstellen:

=INDIRECT("B"&A5)

Diese Formel kombiniert "B" mit dem Wert in A5 (6), wodurch "B6" entsteht. INDIRECT() liefert dann den Wert in Zelle B6, der "Product A" ist.

INDIRECT() mit & für dynamische Verweise in Excel verwenden. Bild vom Autor.

Dynamische Verwendungen von INDIRECT() in Excel

Die wahre Stärke der Funktion INDIRECT() ist ihre Fähigkeit, dynamische Referenzen zu erstellen. Das macht sie ideal für fortgeschrittene Modellierung, Dashboards und benutzergesteuerte Berichte. Im Folgenden findest du drei wichtige Anwendungsfälle, die seine Flexibilität zeigen.

Verweis auf andere Arbeitsblätter

Du kannst INDIRECT() verwenden, um Daten dynamisch aus verschiedenen Blättern zu ziehen, basierend auf der Eingabe in einer Zelle. Wenn du zum Beispiel in der Zelle A1 den Namen eines Blattes hast, wie "Sales". Du willst den Wert aus Zelle B2 auf diesem Blatt ziehen.

Die folgende Formel konstruiert eine Referenz wie 'Sales'!B2 und gibt den Wert aus dieser Zelle zurück.

=INDIRECT("'" & A1 & "'!B2")

Mit INDIRECT() kannst du in Excel indirekt auf andere Arbeitsblätter verweisen. Bild vom Autor.

Schließe die Blattnamen immer in einfache Anführungszeichen ein, indem du ' verwendest, wenn die Möglichkeit besteht, dass sie Leerzeichen oder Sonderzeichen enthalten, wie 'Sales 2023'!A1.

Referenz benannte Bereiche

Du kannst auch die Funktion INDIRECT() verwenden, um dynamisch auf benannte Bereiche zu verweisen, was in Kombination mit Dropdowns oder Benutzereingaben sehr nützlich ist.

Angenommen, du hast benannte Bereiche, Sales_2023 und Sales_2023, in denen Zelle "A1" Folgendes enthält "Sales_2023"

Benennen von Zellbereichen in Excel. Bild vom Autor.

Die folgende Formel summiert die Werte im benannten Bereich Sales_2023, auch wenn sie sich nicht direkt auf diesen Bereich bezieht. Diese Methode eignet sich hervorragend für benutzergesteuerte Dashboards oder Modelle mit mehreren Szenarien.

=SUM(INDIRECT(A1))

INDIRECT() verwenden, um benannte Bereiche in Excel zu referenzieren. Bild vom Autor.

Behobene Referenzen, die sich nicht ändern

Wenn du Zeilen oder Spalten einfügst oder löschst, werden die Zellbezüge in Formeln normalerweise automatisch angepasst. Aber die Funktion INDIRECT() erstellt absolute Referenzen, die sich nicht verschieben.

Wenn zum Beispiel die Zelle "A1" 100 enthält, geben die Formeln =A1 und =INDIRECT("A1") beide 100 zurück. Wenn du jedoch den Wert verschiebst

Direkter Zellbezug in Excel. Bild vom Autor.

INDIRECT() für Zellreferenzen in Excel verwenden. Bild vom Autor.

Wenn du jedoch eine neue Zeile über Zeile 1 einfügst, zeigt D3 (war B2) jetzt =A3, da sich der Wert mit der Zeile verschiebt. Die Zelle E3 mit der Funktion INDIRECT() zeigt immer noch die ursprüngliche A2 an, die jetzt leer sein oder einen anderen Wert haben kann (UID).

INDIRECT() für feste Referenzen in Excel verwenden. Bild vom Autor.

Fortgeschrittene Beispiele und Anwendungsfälle

Sobald du mit den Grundlagen vertraut bist, glänzt die Funktion INDIRECT() in fortgeschritteneren Excel-Szenarien, insbesondere in Verbindung mit Funktionen wie VLOOKUP(), ROW() und dynamischen Arrays. Diese Kombinationen ermöglichen noch dynamischere und flexiblere Modelle.

INDIRECT() mit VLOOKUP() verwenden

Du kannst INDIRECT() mit VLOOKUP() kombinieren, um die Tabelle auf der Grundlage von Benutzereingaben dynamisch zu ändern.

Angenommen, du hast zwei Tabellen, Prices_2022 und Prices_2023. In getrennten Zellen, G1: Prices_2022 und G2: Apple`. Die folgende Formel sucht dynamisch nach "Apple" in der Tabelle des ausgewählten Jahres, basierend auf dem Wert in A1, und gibt den Preis zurück.

=VLOOKUP(G2, INDIRECT(G1), 2, FALSE)

INDIRECT() mit VLOOKUP() in Excel verwenden. Bild vom Autor.

Schau dir unser VLOOKUP() von einem anderen Blatt an: Ein How-to in Excel Tutorial, um mehr über das Suchen und Abrufen von Daten aus verschiedenen Excel-Blättern zu erfahren.

INDIRECT() mit R1C1-Referenzierung

INDIRECT() kann auch die R1C1-Notation verwenden, was besonders hilfreich ist, wenn du Formeln dynamisch in Makros erstellst oder programmatisch auf Positionen verweist.

Die folgende Formel gibt zum Beispiel den Wert in Zeile 2, Spalte 3 (Zelle C2) zurück. Das zweite Argument (FALSE) sagt Excel, dass du den R1C1-Stil verwendest und nicht den A1-Stil.

=INDIRECT("R2C3", FALSE)

INDIRECT() mit R1C1 in Excel verwenden. Bild vom Autor.

Die folgende Formel beinhaltet die dynamische Nutzung von ROW() und COLUMN(). Die Formel gibt immer den Wert aus Spalte 3 in derselben Zeile zurück, in der die Formel geschrieben wurde.

=INDIRECT("R" & ROW() & "C3", FALSE)

Verwendung von INDIRECT() mit ROW() und COLUMN() in Excel. Bild vom Autor.

INDIRECT() und dynamische Arrays

Bevor Excel dynamische Arrays einführte, war INDIRECT() eine gängige Methode, um arrayähnliches Verhalten zu erzeugen.

Die folgende Formel gibt zum Beispiel ein Array zurück: {1; 2; 3; 4; 5}. Dies simuliert ein Array von 1 bis 5, indem es die Zeilen indirekt referenziert.

=ROW(INDIRECT("1:5"))

INDIRECT() mit ROW() für dynamische Arrays in Excel verwenden. Bild vom Autor.

Die moderne alternative Methode ist die Verwendung der Funktion SEQUENCE().

=SEQUENCE(5)

SEQUENCE() für dynamische Arrays in Excel verwenden. Bild vom Autor.

Die Verwendung von INDIRECT() für dynamische Arrays funktioniert zwar immer noch, aber sie gilt als weniger effizient und weniger lesbar als SEQUENCE(), FILTER() und andere dynamische Array-Funktionen, die in modernem Excel (Office 365 und Excel 2021+) verfügbar sind.

Vorsichtsmaßnahmen und Leistungsaspekte

Die Funktion INDIRECT() hat einige wichtige Einschränkungen, insbesondere wenn sie in großen oder komplexen Excel-Arbeitsmappen verwendet wird. Zu diesen Themen gehören die folgenden:

• Flüchtige Funktion (Überlastung durch Neuberechnung): INDIRECT() ist eine flüchtige Funktion, d. h. sie wird bei jeder Änderung in der Arbeitsmappe neu berechnet, auch wenn die Änderung nichts mit der Formel zu tun hat. Dies kann zu einer langsameren Leistung führen.

• Die Fehlersuche kann eine Herausforderung sein: Da INDIRECT() Verweise aus Text erstellt, kann Excel sie nicht so einfach nachverfolgen wie bei normalen Zellverweisen. Das macht es schwer, Formeln mit Präzedenzfällen/Abhängigkeiten zu prüfen oder fehlerhafte Referenzen zu erkennen, wenn sich etwas ändert.

• Ziehe moderne Alternativen in Betracht: Wenn möglich, ist es oft besser, nichtflüchtige Funktionen zu verwenden, die eine bessere Leistung und Übersichtlichkeit bieten. Zu diesen Methoden gehören INDEX() für dynamische Zellensuche, CHOOSE(), um dynamisch aus vordefinierten Bereichen oder Optionen auszuwählen, und SEQUENCE(), um dynamische numerische Arrays zu erzeugen.

Wann sollte INDIRECT() nicht verwendet werden?

INDIRECT() bietet zwar große Flexibilität, ist aber nicht immer die beste Wahl. Sie führt oft zu unnötiger Komplexität, Leistungsproblemen oder Problemen mit der Wartbarkeit. Im Folgenden findest du einige Situationen, die du vermeiden solltest, und die besten alternativen Methoden.

• Dynamische Zellbezüge innerhalb desselben Blattes: Verwende INDEX() anstelle von INDIRECT() für bessere Leistung und Rückverfolgbarkeit.

• Dynamische Arrays oder Zahlenfolgen: Vermeide =ROW(INDIRECT("1:10")). Verwende stattdessen =SEQUENCE(10), das schneller, sauberer und nicht flüchtig ist.

• Wenn Rückverfolgbarkeit wichtig ist: Formeln, die INDIRECT() verwenden, können mit "Vorgänger verfolgen" oder "Formel auswerten" nicht effektiv nachverfolgt werden.

• Verweis auf eine statische Zelle oder einen Bereich: Verwende nicht INDIRECT("A1"), wenn du einfach =A1 schreiben kannst.

• Starke Nutzung in großen Tabellenkalkulationen: Da INDIRECT() flüchtig ist, wird es ständig neu berechnet und kann die Leistung erheblich verlangsamen.

INDIRECT() Alternativen

In der folgenden Tabelle sind die besten Alternativen zu INDIRECT() zusammengefasst.

Die wichtigsten Erkenntnisse

INDIRECT() verwandelt Textstrings in Live-Referenzen und ist damit ein leistungsstarkes Werkzeug für die Erstellung dynamischer und flexibler Tabellenkalkulationen. Sie ist nützlich, um auf verschiedene Blätter, benannte Bereiche oder feste Zellen zu verweisen, die sich bei Änderungen nicht verschieben sollen. Es ist jedoch eine flüchtige Funktion, die häufig neu berechnet wird und große Arbeitsmappen verlangsamen kann. Die Funktion ist auch schwieriger zu verfolgen und zu debuggen als traditionelle Funktionen. Nutze INDIRECT() gezielt und ziehe sicherere, effizientere Alternativen in Betracht.

Ich empfehle dir unseren Kurs Datenanalyse in Excel, um mehr über Einblicke in B2B-Daten zu erfahren, und den Lernpfad Excel-Grundlagen, um mehr über die Funktionen in den neuesten Excel-Versionen zu lernen. Unser Kurs Excel-Funktionen für Fortgeschrittene ist der perfekte Kurs, wenn du dich mit fortgeschrittenen Referenzierungen und Nachschlagewerken beschäftigen möchtest.