Wenn du mit mehreren Tabellenblättern arbeitest und diese unter einander verlinkst, wirst du früher oder später an den Punkt kommen, wo du diese automatisch wählen möchtest. Dabei kann dich die INDIREKT Funktion unterstützen.

Die INDIREKT Funktion wandelt einen Text in eine Zellenreferenz um. Oder anders gesagt, kann man damit indirekt auf eine Zelle referenzieren.

Damit kannst du z.B.: Bezüge in einer Formel ändern, ohne die Formel selbst zu ändern.

In diesem Artikel zeige ich dir, wie die INDIREKT Funktion aufgebaut ist und wie du sie einsetzen kannst, um die Bearbeitungszeit deiner Dateien zu verringern.

Wenn du einmal die Funktionsweise verstanden hast, wird der Einsatz ein Kinderspiel.

Syntax der Funktion INDIREKT

Die Funktion INDIREKT besteht aus 2 Argumenten

=INDIREKT(Bezug;[A1])

Das Argument Bezug und Argument a1.

Der Bezug ist der Text, der in eine Zellenreferenz umgewandelt wird. Mit a1 kannst du definieren, um welche Art von Bezug es sich handelt.

Das Ergebnis der Funktion ist immer der Inhalt der Zelle, die mit Bezug definiert ist.

Argumente der Funktion INDIREKT

Wie oben beschrieben, besteht die Funktion aus zwei Argumenten.

  • Bezug

Ist erforderlich. Dieses Argument kann ein fest eingegebener Wert sein, ein Bezug auf eine Zelle, einen Namen oder eine Kombination aus beiden. Der Bezug kann dabei in der Z1S1-Schreibweise oder in der A1-Schreibweise vorhanden sein.

  • A1

Ist optional. Definiert, in welcher Art der Bezug vorliegt. Wenn das Argument nicht angegeben wird, entspricht es WAHR und somit wird Bezug in der A1-Schreibweise interpretiert. Ist das Argument FALSCH, wird Bezug in der Z1S1-Schreibweise interpretiert.

Kurz zur Erklärung die A1-Schreibweise ist die weithin bekannteste Schreibweise eines Zellenbezuges z.B.: D7. Die Z1S1-Schreibweise definiert mit der Zahl hinter dem Z die Zeile und mit der Zahl hinter dem S die Spalte. Wenn du mehr zu den unterschiedlichen Bezugsarten erfahren möchtest, melde dich doch gerne bei mir mit Hilfe des Kontakt Formulars, dass du hier findest: kontaktiere mich

Du kannst dir aber auch gerne diesen Artikel durchlesen: Adresse

So kannst du die Funktion INDIREKT einsetzen

Nun gut, mit der Funktion kann man über Text eine Zelle ansteuern, aber hat das wirklich einen Nutzen.

Dazu von mir eine ganz klares JA.

Wie oben beschrieben, kommst du irgendwann an den Punkt, wo du mit mehreren Tabellenblättern arbeiten wirst. Auch der Einsatz von mehreren Tabellen und Namensbereichen ist keine Seltenheit.

Du kannst dann ohne großen Aufwand Daten aus verschieden Bereichen zugänglich machen, ohne jedes Mal die Zellreferenz in der Formel zu ändern.

Du musst nur den Aufbau eines Zellbezugs oder einer Referenz kennen. Dann kannst du mit der Funktion INDIREKT Daten von überallher abrufen.

Hier ein paar Beispiel für Bezüge in Excel und wie diese aufgebaut sind.

Relativer Bezug auf eine Zelle: F27

Absoluter Bezug auf eine Zelle: $F$27

Zellbezug mit absoluter Spalte: $F27

Zellbezug mit absoluter Zeile: F$27

Bezug auf eine ganze Spalte relativ und absolut: F:F und $F:$F

Bezug auf eine ganze Zeile relativ und absolut: 27:27 und $27:$27

Bezug auf das gesamte Tabellenblatt: 1:1048576

Relativer Bezug auf eine Zelle im Arbeitsblatt „Einführung“: Einführung!A1

Mit einem Namen definierter Bereich: cel_Vorname

Ist abhängig von der Benennung des Bereiches.

Die gesamte Tabelle „Jahresauswertung“: tbl_Jahresauswertung

Ist abhängig von der Benennung der Tabelle.

Die Kopfzeile der Tabelle „Jahresauswertung“: tbl_Jahresauswertung[#Kopfzeilen]

Die Spalte „Datum“ der Tabelle „Jahresauswertung“: tbl_Jahresauswertung[Datum]

Eine vollständige Liste aller in Excel möglichen Bezüge findest du in diesem Artikel, da wird auch der Aufbau eines jeden Bezuges im Detail erklärt. Zellbezüge in Excel (Link Artikel Zellbezüge)

Vielleicht ist dir der Vorteil und die Zeitersparnis der INDIREKT Funktion noch immer nicht ganz klar. Dann können die folgenden Beispiele vielleicht die letzten Unklarheiten beseitigen.

Lade dir hier die zugehörige Übungsdatei herunter: Übungsdatei INDIREKT

Geballtes Wissen an einem Ort

In meinem Blog findest du viele hilfreiche Artikel, die dir dabei helfen, dein Wissen aufs nächste Level zu heben. Ganz egal ob du nur nach einem einmaligen Problemlöser suchst oder ob du dauerhaft profitieren möchtest. In meinem Blog wirst du fündig.

Geballtes Wissen in einem Buch

Mit diesen hilfreichen Tipps kannst du in nur 17 Minuten, 90% der anderen Excel Nutzer übertrumpfen.

Einführung

In dem Tabellenblatt „Einführung“ findest du verschiedene Arten wie du mit der INDIREKT Funktion Zellbezüge erstellen kannst.

Überlege immer, wie der Zellbezug lauten soll.

Die INDIREKT Funktion wandelt ja einen Text in eine Zellenreferenz um.

Es gibt die Variante diesen als Text direkt in die Funktion einzugeben. Dann wird der Bezug auf diese Referenz hergestellt.

Wird jedoch der Bezug auf eine Zelle hergestellt, so wird er Inhalt der Zelle als Text für die Erstellung des Zellenbezuges verwendet. Wie du in Zelle B10 erkennen kannst.

Anderes Tabellenblatt mit der Funktion INDIREKT ansteuern

Jede Zelle in einer Arbeitsmappe hat eine eindeutige Adresse. Selbst auf unterschiedlichen Tabellenblättern sind die Adressen einzigartig, da der Name des Tabellenblattes der Zelle vorangestellt ist.

Mit diesem Wissen können wir uns den Bezug einer Zelle in einem anderen Arbeitsblatt einfach erstellen.

Ein Bezug auf eine Zelle in einem anderen Arbeitsblatt ist folgendermaßen aufgebaut:

Blattname!Zellbezug = Einführung!A1

ACHTUNG: Wenn im Blattnamen ein Leerzeichen vorhanden ist, dann must du den Blattnamen in Hochkomma einschließen. Also so:

‚Blattname‘!Zellbezug = ‚Bezug auf Tabellenblatt‘!F29

Am besten du verwendest immer Hochkomma, dann bist du auf der sicheren Seite.

Der Syntax der Formel für den Bezug auf ein anderes Tabellenblatt sieht folgendermaßen aus.

=INDIREKT(„„&Blatt Name&“‚!„&Zellbezug)

Die fixen Werte müssen dabei in eingeschlossen werden um als Text interpretiert werden. Die veränderlichen Werte kann man dann mit den & und dem Text zur gesamten Referenz verbinden.

In den folgenden Beispielen siehst du die verschiedenen Versionen und ihre Anwendung.

Andere Datei mit der INDIREKT Funktion ansteuern

Natürlich funktioniert das dann auch mit dem Bezug auf eine andere Datei.

ACHTUNG: Die Datei muss dabei geöffnet sein, da die Formel ansonsten den Fehler #Bezug ausgibt.

=INDIREKT(‚[„&Dateiname&“]„&Blatt Name&“‚!„&Zellbezug)

Im folgenden Bild wieder einige Beispiele wie du einen Bezug auf eine andere Datei herstellen kannst.

Abhängige Dropdown Liste mit der INDIREKT Funktion

Als praxisnahes Beispiel zeige ich dir, wie du abhängige Dropdown Listen mit der INDIREKT Funktion erzeugen kannst. Dazu findest du in der Übungsdatei in den Arbeitsblättern „Dropdownlisten“, „2016“, „2107“ und „2018“ die zugehörigen Beispiele.

Der Aufbau ist dabei immer derselbe. In der ersten Zelle wird eine einfache Dropdown Liste erstellt, die als Werte entweder Bereichsnamen, Blatt- oder Tabellennamen enthält. Für die zweite Dropdown Liste wird kein Bereich als Quelle definiert, sondern die INDIREKT Formel, die auf die erste Liste verweist.

Die Formel zieht nun abhängig von dem Wert in der ersten Zelle, der über die Dropdown Liste bestimmt wird, die Werte für die Dropdown Liste in der zweiten Zelle. So erhältst du abhängig von der Eingabe in der ersten Zelle eine andere Auswahlmöglichkeit für die zweite Dropdown Liste.

Das ist natürlich nur beispielhaft und kann beliebig geändert werden. Besonders der Einsatz von mit Namen definierten Bereichen machen die Dropdown Listen sehr flexibel und Nutzerfreundlich.

Weitere Beispiele für den Einsatz der INDIREKT Funktion

Jetzt möchte ich dir noch ein paar weiter nützliche Anwendungen der INDIREKT Funktion zeigen.

Die Ideen stammen nicht von mir, sind für dich aber trotzdem besonders wertvoll.

Letzten wert in einer Liste finden

Den Wert an einem Schnittpunkt ausgeben

Darauf solltest du beim Einsatz der INDIREKT Funktion achten

Es gibt auch wieder einige Punkte, auf die du achten musst, beim Einsatz der Funktion INDIREKT.

  • Zellen, auf die du referenzierst, dürfen nicht leer sein.
  • Verweist die Funktion auf eine andere Arbeitsmappe, dann muss diese auch geöffnet sein.
  • Bei Arbeitsblättern mit Leerzeichen oder externen Bezügen (aus einer anderen Arbeitsmappe), musst du Hochkommas verwenden.
  • Die Referenz, die du mit INDRIEKT erzeugst, wird sich nicht ändern, selbst wenn du Zellen einfügst oder löschst. Relative Bezüge auf eine Zelle ändern sich, wenn du Zellen entfernst oder einfügst.
  • Du solltest die INDIREKT Funktion nur sehr sparsam einsetzen, da diese das Arbeitsblatt verlangsamt. INDIREKT hat nämlich den großen Nachtteil, dass sie volatil ist, d.h. sie wird nach jeder Eingabe. Neu berechnet. Selbst dann, wenn sie in einer anderen geöffneten Arbeitsmappe steht!

Fazit

Die INDIREKT Funktion kann dir viel Zeit ersparen, wenn du sie sorgfältig einsetzt.

Verwende sie in anderen Funktionen, um auf Zellen mittels Text zu referenzieren.

Besonders bei einer großen Anzahl an unterschiedlichen Arbeitsblättern kann Sie ihre Vorteile voll ausspielen und dir die größte Zeitersparnis einbringen.

 

Ich verschicke hin und wieder nützliche Tipps und Tricks rund um Excel per E-Mail. Wenn du diese nicht verpassen möchtest, dann trag dich doch in meinen kostenlosen Newsletter ein. Du kannst dich hier eintragen.

Als kleines Dankeschön erhältst du ein eBook mit 17 nützlichen Tipps, mit denen du in kurzer Zeit dein Excel Können auf den nächsten Level bringst.

Den kostenlosen Newsletter und das Geschenk findest du hier.

Du kannst dich jederzeit auch wieder austragen.

Dein Michael, alias Doktor-Excel der smarte Excel Problem Löser

PS.: Vergiss nicht, solltest du spezifische Probleme mit Excel haben, schreib mir eine Nachricht und wir lösen sie gemeinsam. Hier kannst du mich kontaktieren.

Geballtes Wissen an einem Ort

In meinem Blog findest du viele hilfreiche Artikel, die dir dabei helfen, dein Wissen aufs nächste Level zu heben. Ganz egal ob du nur nach einem einmaligen Problemlöser suchst oder ob du dauerhaft profitieren möchtest. In meinem Blog wirst du fündig.

Geballtes Wissen in einem Buch

Mit diesen hilfreichen Tipps kannst du in nur 17 Minuten, 90% der anderen Excel Nutzer übertrumpfen.