Excel Indirekt() - Dynamische Zellen- und Bereichsbezüge gestalten

Bei der Bearbeitung von umfangreichen Excel-Dateien mit einer größeren Anzahl an Tabellenblättern und Verknüpfungen innerhalb der Datei kann die Formel Excel Indirekt() sehr nützlich sein und manuelle Arbeit abnehmen. Auch weitere Tabellenblätter zu erstellen erfordert keine umfangreichen Anpassungen in den Formeln.


Durch Verwendung der Excel Indirekt()-Formel lassen sich Zellen- und Bereichsbezüge innerhalb des Tabellenblatts oder auf weiteren Tabellenblättern anlegen. Die Funktionsweise und Einsatzmöglichkeiten lassen sich am besten durch drei kleine Beispiele verdeutlichen:  

 


Beispiel für Excel Indirekt() jetzt hier rechts oben anfordern!


 

Excel - Werte aus weiteren Tabellenblättern abbilden

Im ersten Beispiel nutzen wir die Formel, um Werte aus unterschiedlichen Tabellenblättern zu übernehmen und in einer Übersicht abzubilden. Sie finden neben einer Übersicht im ersten Tabellenblatt „Süßigkeitenbranche“ ein Tabellenblatt je Segment. In Zelle C7 finden Sie folgende Formel:
 
=INDIREKT($B7&"!"&ADRESSE(6;SPALTE();4))

 

Excel Indirekt() - Dynamische Zellen- und Bereichsbezüge gestalten

 

Wenn wir die Formel genauer betrachten, finden wir zwei notwendige Angaben:

=INDIREKT (Bezug;A1)

Bezug: Ist der Bezug auf eine Zelle, die je nach Angabe in A1 die Verknüpfung herstellt und den Inhalt wiedergibt.

A1: Wahr = A1 Bezüge; Falsch = Z1S1 Bezüge

Natürlich kann man die Werte auch mit einer einfachen Verknüpfung „=Schokolade!C6“ abbilden, diese muss aber für jedes neue Segment ergänzt und bei Änderungen angepasst werden. Mit Hilfe der Formel Indirekt() greifen wir auf die Bezeichnung in Spalte B zu und bilden die Werte aus dem entsprechenden Tabellenblatt ab. Wenn Sie die Beschriftung „Eis“ in Zelle B11 in „Schokolade“ ändern, so wird der Wert 9.114.898 aus dem Tabellenblatt „Schokolade“ abgebildet.


 

Excel - Werte aus einem benannten Bereich abbilden

Im zweiten Beispiel nutzen wir die Formel Indirekt() in Verbindung mit benannten Bereichen, um einen Mittelwert abzubilden. In Zelle J7 finden Sie die Formel
 
=MITTELWERT(INDIREKT(I7))

Auch in diesem Beispiel greifen wir auf die Bezeichnung in Spalte I zu und bilden den Mittelwert des benannten Bereiches. Voraussetzung ist, dass zuvor die benannten Bereiche in den einzelnen Tabellenblättern erstellt worden sind, z.B. der Bereich C6:E6 mit der Bezeichnung „Gummibärchen“ im gleichlautenden Tabellenblatt.

 


 

Gültigkeiten miteinander verknüpfen

Im letzten Beispiel wird die Formel Indirekt() genutzt, um die Auswahlmöglichkeiten einer Gültigkeitsliste abhängig von der Selektion einer zweiten Liste darzustellen. Hierfür wurde die Liste im Bereich B23:G28 erstellt und die einzelnen Segmente als benannte Bereiche angelegt.

Für die erste Datenüberprüfung greifen wir auf die Inhalte des Bereichs „Segmente“ zu, den wir als Kriterium hinterlegt haben.

 

 Als zweites Gültigkeitskriterium hinterlegen wir die Formeln Indirekt() mit dem Bezug auf die Auswahl der ersten Gültigkeitsliste. In diesem Beispiel wird auf die Zelle L23 verwiesen, in der wir den Namen des benannten Bereiches bilden.


 

Basierend auf der Auswahl in Zelle J23 werden die entsprechenden Kategorien des Segmentes als Auswahlmöglichkeiten in Zelle J24 abgebildet. Im ersten Screenshot finden Sie nach Auswahl des Segments „Gummibärchen“ die entsprechenden Kategorien, bei Selektion eines anderen Segments wird die Auswahlmöglichkeit der Kategorie automatisch angepasst.

Mit dem Einsatz der Formel Indirekt() als Gültigkeitskriterium kann eine beliebige Anzahl von verknüpften Gültigkeiten erstellt werden. Diese drei Beispiele bieten Ihnen einen ersten Einblick in die Einsatzmöglichkeiten der Formel Indirekt().

Eventuell haben Sie bereits die ersten Berichte im Kopf, die sich für den Einsatz der Formel eignen. Doch Vorsicht – auch die Formel Indirekt() hat eine Schattenseite. Indirekt() gehört zu den volatilen Formeln von Excel und wird nach jeder Eingabe neu berechnet –auch wenn die Formel in einer anderen geöffneten Arbeitsmappe verwendet wird. Dadurch sollte die Formel nur sparsam in Verbindung mit rechenintensiven Formeln (z.B. Summewenn) eingesetzt werden.

Webinare Lösungen Kontakt