=INDIREKT(E2) liest die Zelle, deren Adresse als Text in E2 steht. Steht in E2 C4, liefert die Formel den Wert in C4. Die Adresse lässt sich auch aus Teilen zusammensetzen: =INDIREKT("C"&E3) liest Spalte C in der Zeile, deren Nummer in E3 steht. INDIREKT heißt in englischem Excel INDIRECT, und die Tabelle zeigt die englische Schreibweise; du kannst die Formeln dort aber auch deutsch eingeben, mit Semikolons.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Address | Value | |
| 2 | Apple | Fruit | $1.20 | C4 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | 6 | $1.10 | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
=INDIREKT(E2)F2 liest C4, den Preis von Carrot, $0.80. Ändere E2 in C3 oder B5, und F2 zieht mit. F3 verbindet "C" und die 6 aus E3 zur Adresse C6 und liefert $1.10. Ändere E3 in 2 für den Preis von Apple.
Syntax von INDIREKT
=INDIRECT(ref_text, [a1])
ref_text(Bezug): Text, der einen Bezug ausschreibt:"C4","B2:B6","Prices!A2","'Price list'!A2:B9".a1(A1):WAHRoder weggelassen für Adressen im A1-Stil.FALSCHliest den Z1S1-Stil, in dem"Z4S3"(in englischem Excel"R4C3") Zeile 4, Spalte 3 bedeutet, und das passt, wenn Zeile und Spalte beide als Zahlen vorliegen.
Ist der Text keine gültige Adresse, ist das Ergebnis #BEZUG! (englisch #REF!; die Tabellen hier zeigen die englischen Fehlernamen). INDIREKT liefert einen echten Bezug, also funktioniert es in SUMME (englisch SUM), ZÄHLENWENN (COUNTIF), SVERWEIS (VLOOKUP) und jeder Funktion, die einen Bereich nimmt.
Einen Bereich aus Zahlen bauen
Die Adresse kann ein ganzer Bereich sein. Fügst du eine Zahl hinein, bekommst du einen Bereich, dessen Größe aus einer Zelle kommt.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Rows | Total | ||
| 2 | Jan | 4,200 | 3 | 12,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 |
=SUMME(INDIREKT("B2:B"&(1+E2)))Mit 3 in E2 wird der Text zu B2:B4, und F2 addiert Jan bis Mar: 12,900. Setz E2 auf 6 für das Halbjahr, 27,900. Das 1+E2 steht da, weil die Daten in Zeile 2 beginnen. In deutschem Excel lautet die Formel =SUMME(INDIREKT("B2:B"&(1+E2))). Dieselbe Summe lässt sich ohne INDIREKT schreiben, =SUMME(B2:INDEX(B2:B7;E2)), und die ist nicht volatil; die Seite zu BEREICH.VERSCHIEBEN (OFFSET) vergleicht die Möglichkeiten.
Ein Blatt ansprechen, dessen Name in einer Zelle steht
Auch der Blattname kann aus einer Zelle kommen. So wird aus einer einzigen Übersichtsformel eine Suche über mehrere Blätter: Jede Zeile liest das Blatt, das in Spalte A genannt ist. Einfache Anführungszeichen um den Namen sorgen dafür, dass es auch bei Namen mit Leerzeichen funktioniert.
| A | B | |
|---|---|---|
| 1 | Month | Total |
| 2 | Jan | 12,500 |
| 3 | Feb | 12,200 |
| 4 | Mar | 13,700 |
=SUMME(INDIREKT("'"&A2&"'!B2:B4"))B2 baut den Text 'Jan'!B2:B4 und summiert ihn: 12,500. B3 und B4 sind dieselbe Formel, nach unten ausgefüllt, also lesen sie Feb (12,200) und Mar (13,700). Öffne das Register Feb und ändere eine Zahl: Die Übersicht zieht mit. Tippe in A2 Feb über Jan, und B2 summiert jetzt Feb. Das B2:B4 in den Anführungszeichen ist Text, also ändert es sich beim Ausfüllen nicht; nur der Bezug auf A2 wandert.
Abhängige Dropdown-Listen
Ein zweites Dropdown, dessen Einträge vom ersten abhängen, ist die klassische Aufgabe für INDIREKT. In Excel sieht der übliche Aufbau so aus:
- Schreib die Einträge jeder Kategorie in eine Spalte und benenne jeden Bereich nach seiner Kategorie: Markier die Spalten mit ihren Überschriften und nimm Formeln > Aus Auswahl erstellen > Oberster Zeile. Das erzeugt die Namen
Fruit,VegetableundDairy. - Gib A2 eine Liste der Kategorien: Daten > Datenüberprüfung > Zulassen: Liste, Quelle
Fruit;Vegetable;Dairy. - Gib B2 eine Liste mit der Quelle
=INDIREKT(A2). Steht in A2 Fruit, liest die Liste den Bereich namens Fruit.
Die Tabelle unten baut dasselbe mit einem Blatt pro Kategorie statt eines benannten Bereichs. D2 lässt mit INDIREKT die Einträge des Blatts überlaufen, das in A2 genannt ist, und die Liste in B2 liest D2:D4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Item | Items for the category | |
| 2 | Fruit | Apple | Apple | |
| 3 | Pear | |||
| 4 | Plum |
=INDIREKT("'"&A2&"'!A2:A4")Wähle Dairy in A2: D2:D4 wechselt zu Milk, Butter, Cheese, und die Auswahl in B2 auch. B2 behält seinen alten Wert, bis du einen neuen wählst; Excel verhält sich genauso, und deshalb ergänzen Formulare oft neben dem Eintrag eine Prüfung wie =ZÄHLENWENN(D2:D4;B2)>0. In Excel 365 kannst du auf die benannten Bereiche verzichten und die zweite Liste auf eine übergelaufene Formel richten, zum Beispiel =INDIREKT("'"&A2&"'!A2:A4") in einer Hilfszelle und =D2# als Quelle. Die Seite zur Dropdown-Liste zeigt den Rest des Aufbaus.
INDIREKT ist volatil und ignoriert eingefügte Zeilen
Zwei Nebenwirkungen kommen daher, dass INDIREKT Text liest statt eines Bezugs:
- Es wird bei jeder Änderung neu berechnet. Excel kann nicht wissen, auf welche Zellen ein Stück Text zeigen wird, also berechnet es jedes INDIREKT nach jeder Bearbeitung irgendwo in der Arbeitsmappe neu. Ein paar Dutzend schaden nicht; Zehntausende machen jeden Tastendruck langsam. INDEX mit einer Zeilennummer (
=INDEX(C:C;E3)) liefert dasselbe Ergebnis wie=INDIREKT("C"&E3)und wird nur neu berechnet, wenn sich seine Eingaben ändern. - Die Adresse wandert nicht mit. Füg über Zeile 4 eine Zeile ein, und aus
=C4wird=C5, aber=INDIREKT("C4")liest weiter C4, das jetzt eine andere Zeile ist. Manchmal ist genau das gewollt, ein Bezug, der auf einer festen Zelle bleiben muss, egal was mit dem Blatt passiert. Öfter ist es ein Fehler, der nur darauf wartet, dass jemand eine Zeile einfügt.
INDIREKT in eine andere Arbeitsmappe funktioniert nur, solange diese Arbeitsmappe geöffnet ist; geschlossen liefert es #BEZUG!.
Übung: ein Preis aus einer Zeilennummer
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Price | |
| 2 | Apple | Fruit | $1.20 | 5 | ||
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Du bist dran: Gib in F2 mit INDIREKT den Preis aus Spalte C in der Zeile zurück, deren Nummer in E2 steht.
Häufig gestellte Fragen
Was macht INDIREKT in Excel?
Die Funktion macht aus Text einen Bezug. =INDIREKT("C4") liefert den Wert von C4, und =INDIREKT(E2) liefert den Wert der Zelle, deren Adresse in E2 steht. Die Adresse lässt sich mit & zusammensetzen, also liest =INDIREKT("C"&E2) Spalte C in der Zeile, deren Nummer in E2 steht.
Wie beziehe ich mich auf ein anderes Blatt, dessen Name in einer Zelle steht?
Setz die Adresse mit dem Blattnamen in einfachen Anführungszeichen zusammen: =INDIREKT("'"&A2&"'!B2"). Die Anführungszeichen sorgen dafür, dass es auch bei Namen mit Leerzeichen funktioniert. =SUMME(INDIREKT("'"&A2&"'!B2:B4")) summiert einen Bereich auf diesem Blatt.
Warum liefert INDIREKT #BEZUG!?
Der Text ist keine gültige Adresse, er nennt ein Blatt, das es nicht gibt, oder er zeigt in eine andere Arbeitsmappe, die geschlossen ist. Prüf den Text, den die Formel baut, indem du denselben Ausdruck ohne INDIREKT allein in eine Zelle schreibst.
Ist INDIREKT volatil?
Ja. Excel berechnet jedes INDIREKT bei jeder Änderung irgendwo in der Arbeitsmappe neu, weil es nicht im Voraus weiß, auf welche Zellen der Text zeigen wird. Ein paar davon schaden nicht; Tausende machen eine Arbeitsmappe langsam. INDEX kann oft dieselbe Aufgabe erledigen, ohne volatil zu sein.
