Die INDIREKT-Funktion: Ich kenne einen, der jemanden kennt… 8

Die INDIREKT-Funktion kann sehr hilfreich sein, wenn man ein wenig um die Ecke denkt...
 

Excel ist über die Jahre hinweg immer komfortabler und benutzerfreundlicher geworden. Allerdings gibt es viele Tabellenfunktionen, deren Sinn sich einem nicht immer sofort erschließt. Dazu gehört beispielsweise auch die INDIREKT-Funktion.

Dabei passt diese Funktion ganz hervorragend in unsere heutige vernetzte Zeit, in der Beziehungen so wichtig sind. Es heißt ja immer, man muss nicht alles selber können, es reicht, wenn man jemanden kennt, der weiß wie etwas geht.

Und so ähnlich ist das auch mit der INDIREKT-Funktion…

Was macht die Funktion?

Beginnen wir mit etwas trockener Theorie: Sie gibt den Bezug eines Textwertes zurück, der in einer bestimmten Zelle angegeben ist.
Das heißt, man gibt eine Zelle an, die wiederum einen Text enthält, der auf eine andere Zelle verweist. Das kann eine Zelladresse sein (A1, B5, Z1S10) oder ein definierter Name.

So funktioniert INDIREKT

So funktioniert INDIREKT

Klingt jetzt vielleicht etwas verwirrend und alles andere als spannend. Daher nachfolgend ein paar interessantere Beispiele, um diese Funktion besser zu verstehen.

Beispiel 1: Dynamisch Bereiche auswerten

Es ist erschließt sich einem sicherlich nicht sofort der Sinn, warum ich in einer Zelle die Adresse einer anderen Zelle eintragen soll, damit ich über die INDEX-Funktion auf diese letzte Zelle zugreifen kann. Das ist ein bisschen wie „Ich kenne einen, der jemanden kennt, der jemanden kennt“.

Interessant wird das Ganze vor allem im Zusammenhang mit benannten Bereichen. Das heißt, wenn man für bestimmte Zellbereiche einen Namen definiert hat. Dann lassen sich diese Bereiche nämlich über INDIREKT beispielsweise für eine Summenberechnung sehr dynamisch ansprechen. Dazu habe ich folgende kleine Tabelle vorbereitet:

Die Summe für einen Monat

Die Summe für einen Monat

Ich möchte nun in Zelle B17 immer die Summe des Monats erhalten, dessen Name ich in die Formel eingebe. Dazu muss für jeden Monatsbereich ein entsprechender Name festgelegt werden. Das heißt, für den Bereich B4:B13 der Name „Januar“, für C4:C13 der Name „Februar“ und für D4:D13 eben „März“. Am Schnellsten geht das folgendermaßen:

Markiere den kompletten Wertebereich einschließlich der Monatsüberschriften, also den Bereich B3:D13, und wähle dann aus dem Register „Formeln“ die Schaltfläche „Aus Auswahl erstellen“:

Namen aus Auswahl erstellen

Namen aus Auswahl erstellen

Excel erkennt, dass die Markierung anscheinend schon Überschriften enthält und hat daher bereits die Option „Oberster Zeile“ angekreuzt. Sollte das bei dir nicht der Fall sein oder sollten mehrere Kästchen angekreuzt sein, dann überprüfe bitte nochmal die Markierung und ändere gegebenenfalls die Optionen.

Nach einem Klick auf „OK“ wurden die drei Namen Januar, Februar und März erstellt, die auf die jeweiligen Werte verweisen. Das kann man leicht im Namensmanager kontrollieren:

Kontrolle im Namensmanager

Kontrolle im Namensmanager

Nun gebe ich in Zelle B17 die folgende Formel ein und erhalte das Ergebnis für diesen Monat:
=SUMME(Januar)

SUMME mit Namensbezug

SUMME mit Namensbezug

Da ich zuvor die jeweiligen Spalten nach den Monaten benannt habe, weiß die SUMME-Funktion, auf welchen Bereich sie zugreifen muss. Für einen anderen Monat müsste ich jetzt den gewünschten Namen in der SUMME-Funktion eintragen, was nicht sehr schön ist. Besser ist es, den Monatsnamen in eine eigene Zelle zu schreiben und – du ahnst es bereits – über die INDIREKT-Funktion darauf zuzugreifen.

Schreiben wir also den Monatsnamen in Zelle B16. Für mehr Komfort und um Eingabefehler zu vermeiden, kannst du stattdessen auch ein Dropdownfeld anlegen, in dem man die Namen auswählen kann (Menü Daten | Datenprüfung | Zulassen: Liste | Quelle = $B$3:$D$3)

Und ändern als nächstes die Formel in Zelle B17 wie folgt:
=SUMME(INDIREKT(B16))

Summenberechnung mit Hilfe von INDIREKT

Summenberechnung mit Hilfe von INDIREKT

Damit holt sich SUMME sozusagen über den Umweg aus Zelle B16 den Namen für den richtigen Zellenbereich und liefert das gewünschte Ergebnis nach dem Motto:
„Ich kenne einen (B16), der jemanden kennt (März), der jemanden kennt (D4:D13)“.

Sobald man den Monatsnamen in Zelle B16 ändert, wird sofort das entsprechende Ergebnis berechnet. Ziemlich praktisch, oder?

Beispiel 2: Den Wert an einem Schnittpunkt ausgeben

Wie wir im vorherigen Beispiel gesehen haben, liegt der Schlüssel in der Vergabe von Namen für Zellbereiche. Dieses Prinzip wende ich auch in diesem Beispiel an. Jetzt möchte ich zusätzlich zum Monat auch noch ein bestimmtes Produkt auswählen und Excel soll mir dann in Zelle B17 den entsprechenden Umsatz dazu ausgeben.

Beispiel 2 mit zwei Variablen

Beispiel 2 mit zwei Variablen

Auch hier legen wir erst wieder die Namen an. Und da kommt schon der erste potenzielle Stolperstein. Wenn du dir mein Beispiel genau angesehen hast, wird dir vielleicht die Schreibweise der Produkte aufgefallen sein:
Produkt_1, Produkt_2 und so weiter. Also immer mit einem Unterstrich vor der Zahl und nicht mit einem Leerzeichen. Der Grund dafür ist, dass ich die Namen mit minimalem Aufwand anlegen möchte. Und es gibt ein paar Namenskonventionen in Excel, an denen man nicht vorbei kommt:

Ein Name

  • darf maximal 255 Zeichen lang sein
  • muss mit einem Buchstaben oder einem Unterstrich beginnen
  • darf Buchstaben, Zahlen, einen Unterstrich oder einen Punkt enthalten
  • darf kein Leerzeichen und keinen Bindestrich enthalten
  • darf nicht mit einem Zellbezug (z.B. A1 oder B5) oder einem Funktionsnamen übereinstimmen (z.B. SUMME)

Wichtig für uns ist hier das verbotene Leerzeichen im Namen. Wenn man die Namen automatisch aus Zellen generieren möchte, deren Inhalte Leerzeichen enthalten, werden diese im Namen automatisch durch einen Unterstrich ersetzt. Das sähe dann so aus:

Namen aus Auswahl ertellen

Namen aus Auswahl ertellen

Die Produkte enthalten Leerzeichen. Im Namensmanager werden jedoch Unterstriche eingesetzt:

Leerzeichen wurden durch Unterstrich ersetzt

Leerzeichen wurden durch Unterstrich ersetzt

Das ist grundsätzlich auch nicht weiter schlimm. Da ich aber faul bin und in Zelle B15 wieder mit minimalem Aufwand ein Dropdown-Feld mit allen Produkten einrichten möchte, müssen diese Produktnamen mit denen im Namensmanager übereinstimmen. Ansonsten funktioniert nachher die Formel nicht. Alternativ könntest du natürlich die Produktliste mit Leerzeichen belassen und stattdessen eine separate Datenquelle für das Dropdownfeld angeben, in dem die Produkte einen Unterstrich enthalten. Mach, wie du willst 🙂

Ich habe mich also für die Variante mit den Unterstrichen entschieden und kann somit in meiner Datenquelle für das Dropdown in Zelle B15 direkt auf die Spalte A verweisen:

Quelle für die Datenüberprüfung

Quelle für die Datenüberprüfung

Wie muss aber jetzt die Formel in B17 aussehen, um den gewünschten Werte am Schnittpunkt von Produkt und Monat auszugeben?

So:
=INDIREKT(B15) INDIREKT(B16)

INDIREKT und Schnittmengenoperator

INDIREKT und Schnittmengenoperator


Das Leerzeichen zwischen den beiden INDIREKT-Funktionen ist der sehr selten verwendete Schnittmengenoperator. Damit wird die Schnittmenge aus dem ersten und dem zweiten Zellbereich bestimmt und in unserem Fall der Inhalt dieses Schnittpunkts ausgegeben.

Unter folgendem Link findest du bei Microsoft eine Übersicht über alle Berechnungsoperatoren in Excel:
https://support.office.com/de-de/article/Verwenden-von-Berechnungsoperatoren-in-Excel-Formeln-78be92ad-563c-4d62-b081-ae6da5c2ca69

Beispiel 3: Immer den letzten Wert einer Zeile (Spalte) anzeigen

In den vorangegangene beiden Beispielen haben wir die INDIREKT-Funktion immer mit zuvor definierten Namen verwendet. Wie eingangs im Artikel erwähnt, kann man natürlich auch Zelladressen angeben. Das Besonderes ist, dass man dabei nicht nur die übliche Schreibweise A1, B5 und so weiter verwenden kann, sondern auch die weniger gebräuchliche Z1S1-Schreibweise.

Der Zellenbezug wird dabei durch die Zeilen- und Spaltennummer festgelegt. A1 entspricht in dieser Schreibweise Z1S1 (Zeile 1, Spalte 1). Die Zelle D15 wäre somit Z15S4.

Diese Schreibweise ermöglicht im Zusammenhang mit der INDIREKT-Funktion eine höhere Flexibilität. Denn man muss nicht zwingend feste Zeilen- oder Spaltennummern angeben. Vielmehr können diese selbst das Ergebnis einer Berechnung sein, wie das folgende Beispiel zeigen soll. Wir bleiben wieder bei unser Umsatztabelle, die ich um eine Summenzeile erweitert habe. Diesmal möchte ich jedoch immer den allerletzt Wert der Tabelle zurückliefern, also die Summe des jeweils letzten Monats.

Wie bestimme ich den letzten Wert

Wie bestimme ich den letzten Wert

Die Summenwerte befinden sich immer in Zeile 14. In oben gezeigten Beispiel ist der Wert in Zelle D14 für März der aktuell letzte Eintrag. Die INDIREKT-Funktion würde daher so aussehen:
=INDIREKT("Z14S4";FALSCH)

INDIREKT mit Z1S1-Schreibweise

INDIREKT mit Z1S1-Schreibweise

Der zweite Parameter FALSCH besagt, dass der verwendete Zellbezug in der Z1S1-Schreibweise erfolgt. Und den Bezug muss man dann in Anführungszeichen eingeben.

Käme jetzt ein weiterer Monat dazu, wäre der letzte Wert somit in Spalte E (= Spaltennummer 5). Damit man die INDIREKT-Funktion jetzt flexibel verwenden kann, berechnen wir diese letzte Spalte mit Hilfe der ANZAHL2-Funktion.
ANZAHL2(14:14)

Damit wird die Anzahl der Einträge in Zeile 14 berechnet. Bei 3 Monatsspalten und der ersten Spalte mit den Bezeichnungen liefert diese Formel also den Wert 4. Da meine Tabelle in Spalte A beginnt, kann ich das Ergebnis auch direkt so übernehmen.

Die angepasste INDIREKT-Funktion sieht nun so aus:
=INDIREKT("Z14S"&ANZAHL2(14:14);FALSCH)

Das heißt, ich gebe keine fixe Spaltennummer an, sondern hänge die ANZAHL2-Funktion über das &-Zeichen an den Zellenbezug an und erhalte somit das gewünschte Ergebnis:

Flexible Berechnung der Spaltennummer

Flexible Berechnung der Spaltennummer

Wenn eine weitere Spalte dazukommt, ermittelt die ANZAHL2-Funktion eben den Wert 5 und damit die 5. Spalte in meiner INDIREKT-Funktion.
Und natürlich würde das genauso funktionieren, wenn ich stattdessen die Zeilennummer flexibel berechnen möchte.

Ich hoffe, diese Beispiele helfen dir ein wenig, den Sinn der INDIREKT-Funktion zu verstehen. Vielleicht hast du ja weitere praktische Einsatzmöglichkeiten gefunden, dann lass es uns unten in den Kommentaren wissen!

 

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

8 Gedanken zu “Die INDIREKT-Funktion: Ich kenne einen, der jemanden kennt…

  • Thomas Verheyen

    Ich nehme die INDIREKT Funktion, wenn ich Werte aus mehreren Blättern in eine Zusammenfassung übernehmen will:

    =INDIREKT(„‚“&B24&“‚!C19″) gibt den Wert der Zelle C19 in dem Tabellenblatt zurück, dessen Name in Zelle B2 steht.
    Der direkte Bezug ist ja: ‚ ‚!
    (wenn im Names des Blattes keine Leerzeichen sind, kann das Hochkomme entfallen) Zur Sicherheit bastele ich mit die Hochkommata mit dazu; das Ausrufezeichen natürlich auch. Die ‚ muss ich mit Anführungszeichen maskieren, also “ und ‚ und “ bzw. “ und ‚ und ! und wieder „

    • Martin Weiß Autor des Beitrags

      Hallo Thomas,

      danke für dieses Beispiel. Hier sieht man schön, dass man nicht nur mit einfachen Zelladressen, sondern auch mit Blattnamen arbeiten kann.

      Schöne Grüße,
      Martin

  • Tim

    Leerzeichen als Schnittmengenoperator… Ich glaube ich könnte jahrelang mit Excel arbeiten und niemals werde ich alle Funktionen kennen.
    Danke für den Artikel.

  • Rico

    So richtig hat sich mir die INDIREKT-Funktion bisher nie erschlossen. Dank dieses Beitrages kann ich jetzt etwas damit anfangen. Sehr schön erklärt! Außerdem habe ich noch etwas über Leerzeichen als Schnittmengenoperator gelernt. Vielen Dank für diesen wieder einmal sehr interessanten Artikel!

  • Werner Backes

    Sehr geehrte Damen und Herren

    Ich lese sehr intensiv immer Ihre neuesten Veröffentlichungen und versuche diese dann auch nachzuvollziehen.

    in diesem Beispiel jedoch beibe ich bei der Formel. =indirekt (B15) indirekt (B16) hängen und komme leider nicht weiter, da mir diese Formel eine Fehlermeldung aus gibt.

    Leerzeichen als Schnittmengenoperator………………funktioniert leider nicht.

    Wie oben von Ihnen beschrieben müsste die Formel funktionieren.
    Ich habe Ihre oben aufgeführte Tabelle kpl. bei mir erfasst und auch alle Zellen genauso, wie von ihnen oben beschrieben ansgesprungen.

    ich wäre Ihnen sehr dankbar wenn sie mir hier weiterhelfen könnten da ich leider nicht weiter komme.

    vielen DANK für Ihre Antwort

    • Martin Weiß Autor des Beitrags

      Hallo Herr Backes,

      ich vermute, dass in Ihrer Datei irgendetwas mit den Namen nicht stimmt. Prüfen Sie doch mal im Namensmanager, ob es für die Monate und Produkte korrekte Einträge gibt, die auf die jeweilige Zeile bzw. Spalte verweisen.

      Schöne Grüße,
      Martin

      • Werner Backes

        hallo H. Weiß

        vielen lieben Dank für Ihre Hilfe. Mein Problem war genau dort, wie von Ihnen beschrieben.

        Getreu nach dem Motto von Ihnen: Das Problem sitzt vor dem Computer…. 🙂

        Funktioniert alles jetzt bestens.
        nochmals DANKE für Ihre Hilfe und Antwort

        beste Grüße
        Werner Backes