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
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
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
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
Nun gebe ich in Zelle B17 die folgende Formel ein und erhalte das Ergebnis für diesen Monat:
=SUMME(Januar)
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
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
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
Die Produkte enthalten Leerzeichen. Im Namensmanager werden jedoch Unterstriche eingesetzt:
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
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
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
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
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
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.
(Hier gibt es die Beispieldatei zum Download)
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!
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.
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 „
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
Leerzeichen als Schnittmengenoperator… Ich glaube ich könnte jahrelang mit Excel arbeiten und niemals werde ich alle Funktionen kennen.
Danke für den Artikel.
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!
Hallo Rico,
freut mich, wenn ich ein bisschen Licht ins Dunkel bringen konnte.
Schöne Grüße,
Martin
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
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
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
Hallo Martin, vielen Dank für die Indirekt-Formeln.
Könntest du evtl. auch die Datei dazu hochladen?
Vielen Dank.
Hallo Margit,
ich habe den Download-Link am Ende des Artikels ergänzt.
Schöne Grüße,
Martin
Hallo Martin,
kannst du was zum „Speichervrbrauch“ bzw. „Berechnungsgeschwindigkeit“ sagen. Es ist schon ein paar Jehre, da hatte ein Excel-Profi mal (weiß nicht mehr wo und wer) erläutert, dass Indirekt-Funktionen merkbar Berechnungszeit benötigen…..
Seit längerem stoße ich des öfteren (auch schon bei Dateien ab 2 MB) an nervige Berechnungszeiten…..deshalb habe ich mir mal angewöhnt so wenig als möglich mit Indirekt-Formeln zu arbeiten, auch wenn sie sehr attraktiv daher kommen.
Kannst du du was zu sagen ?
viele Grüße von Joachim
Hallo Joachim,
ja, die INDIREKT-Funktion kann sich auch auf die Performance auswirken. Es handelt sich um eine sogenannte volatile Funktion, zu dem Thema hatte ich schon mal einen Artikel geschrieben:
Excel im Schneckentempo: Volatile Funktionen
Wenn die Funktion im „normalen“ Rahmen eingesetzt wird, sollte es nicht spürbar sein, bei intensivem Gebrauch ist das aber schon gut möglich. Es hängt halt immer vom Einzelfall ab, sprich: wie oft sie zum Einsatz kommt uns was sonst noch alles in der Arbeitsmappe passiert. Eine pauschale Aussage kann ich daher hier nicht treffen. Wenn die Arbeitsmappe aber langsam wird, könnte die INDIREKT-Funktion dabei eine Rolle spielen.
Schöne Grüße,
Martin
Hallo Martin, vielen Dank für die prompte Antwort und den Link!!
Schönes Wochende
Gruß Joachim
Hallo Martin,
danke für den Beitrag.
Hatte bis dato nie von dieser hilfreichen Funktion gehört.
Zum Beispiel 3 habe ich aber noch eine Frage:
Wie ändere ich am einfachsten die Namenslisten, wenn ich weitere Spalten (Monate) bzw. Zeilen (Produkte) ergänze?
Beste Grüße
Matthias
Und die Zuweisung zu den Dropdown-Feldern muss ich dann auch anpassen…
Gibt es keinen Automatismus?
Hallo Matthias,
für eine dynamische Anpassung muss in der Datenprüfung anstelle der Liste eine Formel verwendet werden und mit der BEREICH.VERSCHIEBEN-Funktion arbeiten.
Für die Produkte würde die Formel in meinem Beispiel so aussehen:
=BEREICH.VERSCHIEBEN(A4;0;0;ANZAHL2($A:$A)-3;1)
Ich muss hier vom Wert von ANZAHL2 noch 3 abziehen, da ich noch 3 andere Zellen in Spalte A gefüllt habe (A5:A17). Das musst du halt auf deine Verhältnisse entsprechend anpassen.
Und für die Monate wäre das die Formel:
=BEREICH.VERSCHIEBEN(B3;0;0;1;ANZAHL2(3:3))
Schöne Grüße,
Martin
=BEREICH.VERSCHIEBEN(B3;0;0;1;ANZAHL2(3:3))
Hallo Matthias,
wenn es nur wenige neue Zeilen und/oder Spalten sind, dann würde ich die Namen manuell vergeben. Wenn es jedoch sehr viele sind, dann ist es vermutlich schneller, alle vorhandenen Namen zu löschen und dann auf dem im Artikel beschriebenen Weg neu anlegen zu lassen.
Schöne Grüße,
Martin
Danke für die hilfreiche Erklärung zum Thema INDIREKT. Ich habe festgestellt, in der Datei fehlt ein Beispiel für die dynamische Anzahl von Zeilen. Würde es dann so aussehen:
=INDIREKT(„Z“&ANZAHL2(1:1)&“S10;FALSCH)
?
Hallo Martin,
ich stehe glaube ich kurz vor der Lösung, komme nur nicht drauf. Und zwar habe ich eine Tabelle mit Tagesumsätzen, die am Ende jeder Kalenderwoche summiert werden, zeitgleich mit einer Entwicklung zum Vorjahr. Jetzt möchte ich diese Werte (Entwicklung der jeweiligen KW zum VJ) jeder einzelnen KW in einer separaten Tabelle darstellen. Natürlich könnte ich jeden einzelnen Wert anklicken. Ich denke, es gibt eine Formel die besagt, dass ich den Wert aus jeder 7. Spalte nehmen kann. Also z.B. A10=A9 und B10=(A9+8 Tage) und C10=(B9+8 Tage). Verstehst Du, was ich meine?
Vielen Dank für eine Rückmeldung.
Schöne Grüße
Christian
Hallo Christian,
momentan kann ich dir noch nicht helfen, da ich keine Ahnung habe, wie deine Tabelle aufgebaut ist. Kannst du das nochmal ein bisschen näher beschreiben?
Schöne Grüße,
Martin
Hallo Martin,
mit ganz viel goo…le und co habe ich mir eine brauchbare adresse(indirekt … Formel erstellt.
=INDIREKT(ADRESSE(160;ZEILE(A10);;;“Urlaub 2020″))
Ich habe eine Urlaubsdatei, wo die Mitarbeiter Zeilenweise erfasst sind und das Datum sich spaltenweise ändert, genau immer um eine Spalten, nur von Freitag zu Montag sind es zwei, weil das Wochenende als eine Spalte existiert.
In meinem Tabellenblatt, wo die oben genannte Formel agiert, habe ich allerdings zwischen den Berechnungen immer zwei freie Zeilen, damit mein Säulen-Diagramm im Enteffekt pro Tag 2 gestapelte Säulen zeigt. Eine Säule mit anwesende Mitarbeiter und Leiharbeiter und eine Säule mit abwesenden Mitarbeitern durch Urlaub oder Krankheit. Also eigentlich ist es nur eine freie Zeile um zwischen den Tagen eine Abgrenzung zu haben und dann ist die eine Formel in der einen Zeile und die andere Formel für Urlaub und Krank in der nächsten Zeile.
Mein Problem ist jetzt ganz einfach das, wenn ich die Formel nach unten kopiere ändert sich meine ….Zeile(A10)… was ja der Spalte entspricht immer um drei Spalten, also es steht dann … Zeile(A13)…, ich brauche aber …Zeile(A11)… oder wenn es ein Sprung über das Wochenende ist …. Zeile(A12)…
Gibt es hierfür irgendeine Möglichkeit? Ich habe schon einiges Ausprobiert, bin aber leider nicht zum Ziel gekommen.
Dann über das ganze Jahr sind es natürlich unmengen an Daten, leider funktioniert das mit den Namen vergeben leider auch nicht.
Über Hilfe wäre ich sehr dankbar.
Schöne Grüße
Linda
Hallo Linda,
das ist eine kniffelige Angelegenheit, wenn man die Datei nicht vor Augen hat…
Ich versuch’s trotzdem mal: Wenn Du also beispielsweise nur jede zweite Spalte benötigst, könntest Du etwas in dieser Art probieren:
=ADRESSE(160;ZEILE(A10)+ZEILE(A1)*2)
Und für jede dritte Spalte eben
=ADRESSE(160;ZEILE(A10)+ZEILE(A1)*3)
Vielleicht hilft das ja bei Deinen Experimenten etwas weiter.
Schöne Grüße,
Martin
Hallo Martin,
ich verwende die INDIREKT-Funktion schon lange und vielfach. An einem scheitert man leider immer wieder: wenn der benannte Bereich nicht direkt/fix zugewiesen ist, sondern über BEREICH.VERSCHIEBEN dynamisch z.B. an sich ändernde Bereichsgrößen angepasst wird. Solche Bereiche werden von INDIREKT mit einer Fehlermeldung quittiert…
Kennst du das Problem und hast du dafür eventuell eine Lösung / einen Workaround?
Hallo Gerald,
das Problem war mir bisher nicht bekannt/bewusst, daher kann ich momentan leider auch keine Lösung anbieten. Vielleicht hat ein anderer Leser hier irgendwelche Erfahrungen?
Schöne Grüße,
Martin
Moin Gerald,
Mir wird nicht ganz klar, wie du INDIREKT und BEREICH.VERSCHIEBEN kombinieren möchtest.
hast du dafür evtl. eine Beispielformel?
Gruß Marcel
Hallo Martin, zunächst vielen Dank für die sehr verständliche Darstellung der INDIREKT Funktion.
Ich hole mir mit INDIREKT &ZEICHEN(10) Informationen aus mehreren Zellen und stelle diese in einer einzigen Zelle gesammelt dar (als s.g. „Karte“):
=WENN(INDIREKT(„Q“&AO8)=“AP13“;INDIREKT(„B“&AO8)&ZEICHEN(10)&“Volume: „&(INDIREKT(„J“&AO8)&“ €“&ZEICHEN(10)&“Vertrieb: „&(INDIREKT(„L“&AO8)));““)
Gibt es eine Möglichkeit auch das Format der referenzierten Zelle mit zu übertragen? So enthält z.B. INDIREKT(„B“&AO8) den Kundennamen, diesen möchte ich in fett und einer anderen Farbe darstellen, so wie in der ursprünglichen Zelle.
Hallo Uwe,
nein, es gibt leider keine Möglichkeit, per Formal das Format aus einer anderen Zelle zu übernehmen. Da müsste man mit VBA drangehen.
Schöne Grüße,
Martin