Laden Sie die Beispielarbeitsmappe herunter
Dieses Tutorial zeigt, wie Sie das Excel INDIRECT-Funktion in Excel, um einen Zellbezug aus Text zu erstellen.
Übersicht über die Funktion INDIRECT
Die INDIRECT-Funktion Erstellt einen Zellbezug aus einer Textzeichenfolge.
(Beachten Sie, wie die Formeleingaben angezeigt werden)
INDIRECT-Funktion Syntax und Eingaben:
| 1 | =INDIREKT(ref_text,C1) |
ref_text - Eine Zeichenfolge, die eine Zellreferenz oder eine Bereichsreferenz darstellt. Die Zeichenfolge kann im R1C1- oder A1-Format vorliegen oder ein benannter Bereich sein.
a1 - OPTIONAL: Gibt an, ob die Referenz im Format R1C1 oder A1 vorliegt. FALSE für R1C1 oder TRUE / Ausgelassen für A1.
Was ist die INDIREKT-Funktion?
Mit der Funktion INDIREKT können Sie eine Textzeichenfolge angeben und den Computer diese Zeichenfolge als tatsächliche Referenz interpretieren lassen. Dies kann verwendet werden, um auf einen Bereich auf demselben Blatt, einem anderen Blatt oder sogar einer anderen Arbeitsmappe zu verweisen.
ACHTUNG: Die INDIRECT-Funktion ist eine der flüchtigen Funktionen. Wenn Sie in Ihrer Tabellenkalkulation arbeiten, berechnet der Computer meistens nur dann eine Formel neu, wenn die Eingaben ihre Werte geändert haben. Eine flüchtige Funktion berechnet jedoch neu jeden wenn Sie eine beliebige Zelle ändern. Es ist Vorsicht geboten, um sicherzustellen, dass Sie keine lange Neuberechnungszeit aufgrund übermäßiger Verwendung der flüchtigen Funktion verursachen oder viele Zellen vom Ergebnis einer flüchtigen Funktion abhängen.
Erstellen Sie einen Zellbezug
Angenommen, Sie möchten den Wert von A2 abrufen, möchten jedoch sicherstellen, dass Ihre Formel bleibt auf A2, unabhängig davon, ob neue Zeilen eingefügt/entfernt werden. Du könntest eine Formel von schreiben
| 1 | =INDIREKT("A2") |
Beachten Sie, dass das Argument in unserer Funktion die Textzeichenfolge „A2“ und kein Zellbezug ist. Da es sich um eine Textzeichenfolge handelt, ist es außerdem nicht erforderlich, eine absolute Referenz wie $ 2 $ anzugeben. Der Text wird sich nie ändern, und daher zeigt diese Formel immer auf A2, egal wohin sie verschoben wird.
INDIREKTE Zeilennummer
Sie können Textzeichenfolgen und Werte aus Zellen miteinander verketten. Anstatt wie zuvor „A2“ zu schreiben, können wir einen numerischen Wert aus Zelle B2 holen und diesen in unserer Formel verwenden. Wir würden Formeln schreiben wie
| 1 | =INDIREKT("A" & B2) |
Das Symbol „&“ wird hier verwendet, um die Textzeichenfolge „A“ mit dem Wert aus Zelle B2 zu verketten. Wenn also der Wert von B2 derzeit 10 wäre, würde unsere Formel dies lauten als
| 123 | =INDIREKT("A" & 10)=INDIREKT("A10")=A10 |
Spaltenwert INDIREKT
Sie können auch in der Spaltenreferenz verketten. Nehmen wir dieses Mal an, dass wir wissen, dass wir einen Wert aus Zeile 10 abrufen möchten, aber wir möchten in der Lage sein, zu ändern, aus welcher Spalte gezogen werden soll. Wir setzen den gewünschten Spaltenbuchstaben in Zelle B2. So könnte unsere Formel aussehen
| 1 | =INDIREKT(B2 & "10") |
Wenn der Wert von B2 „G“ ist, wird unsere Formel wie folgt ausgewertet
| 123 | =INDIREKT("G" & 10)=INDIREKT("G10")=G10 |
INDIREKTER r1c1-Stil
In unserem vorherigen Beispiel mussten wir einen Buchstaben verwenden, um die Spaltenreferenz anzugeben. Dies liegt daran, dass wir die sogenannte Referenzierung im A1-Stil verwendet haben. Im A1-Stil werden Spalten durch einen Buchstaben und Zeilen durch Zahlen angegeben. Absolute Referenzen werden mit dem „$“ vor dem absoluten Element angegeben.
In r1c1 werden sowohl Zeilen als auch Spalten mit einer Zahl gestartet. Der absolute Bezug zu a1 würde geschrieben als
| 1 | =R1C1 |
Sie können dies als „Zeile 1, Spalte 1“ lesen. Relative Bezüge werden durch Klammern angegeben, aber die Zahl gibt die Position an relativ zur Zelle mit Formel. Wenn wir also eine Formel in Zelle A10 schreiben und auf A1 verweisen müssen, würden wir die Formel schreiben
| 1 | =R[-9]C |
Sie können dies lesen als „Die Zelle 9 richtet sich nach oben, aber in derselben Spalte.
Dies könnte hilfreich sein, weil INDIRECT die Verwendung der r1c1-Notation unterstützen kann. Betrachten Sie das vorherige Beispiel, in dem wir einen Wert aus Zeile 10 abgerufen haben, aber die Spalte ändern wollten. Anstatt einen Buchstaben anzugeben, geben wir eine Zahl in Zelle B2 ein. Unsere Formel könnte dann so aussehen
| 1 | =INDIREKT("R10C" & B2, FALSCH) |
Wir haben die 2 weggelassennd Streit bis jetzt. Wenn dieses Argument weggelassen wird oder True ist, wird die Funktion im A1-Stil ausgewertet. Da es False ist, wird es in r1c1 ausgewertet. Nehmen wir an, der Wert von B2 ist 5. Unsere Formel wird dies wie folgt auswerten
| 12 | =INDIREKT("R10C5", FALSCH)=$E$10 |
INDIREKTE Unterschiede mit A1 vs. r1c1
Denken Sie daran, dass wir zuvor gezeigt haben, dass der Inhalt dieser Formel eine Textzeichenfolge war und sich nie geändert hat?
| 1 | =INDIREKT("A2") |
Diese Formel betrachtet immer Zelle A2, egal wohin Sie die Formel verschieben. In r1c1 bleibt diese Regel nicht konsistent, da Sie die relative Position mit Klammern angeben können. Wenn Sie diese Formel in Zelle B2 platzieren
| 1 | =INDIREKT("RC[-1]") |
Es wird Zelle A2 betrachtet (da Spalte A links von Spalte B ist). Wenn Sie diese Formel in Zelle B3 kopieren, bleibt der Text darin gleich, aber der INDIREKTE schaut jetzt auf Zelle A3.
INDIREKT mit Blattname
Sie können auch einen Blattnamen in Ihre INDIREKTEN Referenzen kombinieren. Eine wichtige Regel, die Sie sich merken sollten, ist, dass Sie die Namen in einfache Anführungszeichen setzen und den Blattnamen durch ein Ausrufezeichen vom Zellbezug trennen müssen.
Nehmen wir an, wir hatten dieses Setup, in dem wir unseren Blattnamen, die Zeile und die Spalte angeben.
Unsere Formel, um all dies zu einer Referenz zu kombinieren, würde so aussehen:
| 1 | =INDIREKT("'" & A2 & "'!" & B2 & C2) |
Unsere Formel wird dann wie folgt ausgewertet:
| 123 | =INDIREKT("'" & "Blatt2" & "'!" & "B" & "5")=INDIREKT("'"Blatt2'!B5")='Blatt2'!B5 |
Da das Wort "Sheet2" keine Leerzeichen enthält, haben wir technisch gesehen keine brauchen die einfachen Anführungszeichen. Es ist absolut gültig, so etwas zu schreiben wie
| 1 | =Blatt2!A2 |
Es schadet jedoch nicht, die Anführungszeichen zu setzen, wenn Sie sie nicht benötigen. Es empfiehlt sich, sie einzuschließen, damit Ihre Formel Instanzen verarbeiten kann, in denen sie möglicherweise benötigt werden.
INDIREKT zu einer anderen Arbeitsmappe
Wir werden auch erwähnen, dass INDIRECT einen Verweis auf eine andere Arbeitsmappe erstellen kann. Die Einschränkung besteht darin, dass INDIRECT keine Werte aus einer geschlossenen Arbeitsmappe abruft, sodass diese spezielle Verwendung nur begrenzt praktikabel ist. Wenn die Arbeitsmappe, auf die INDIRECT verweist, ungeöffnet ist, gibt die Funktion ein "#REF!" Error.
Die Syntax beim Schreiben des Arbeitsmappennamens ist, dass er in eckigen Klammern stehen muss. Lassen Sie uns dieses Setup verwenden und versuchen, einen Wert aus Zelle C7 abzurufen.
Unsere Formel wäre
| 1 | =INDIREKT("'[" & A2 & "]" & B2 & "'!C7") |
Achten Sie auch hier auf die Platzierung der einfachen Anführungszeichen, Klammern und Ausrufezeichen. Unsere Formel wird dann wie folgt ausgewertet:
| 123 | =INDIREKT("'[" & "Beispiel.xlsx" & "]" & "Zusammenfassung" & "'!C7")=INDIREKT("'[Beispiel.xslx]Zusammenfassung'!C7")='[Sample.xlsx]Zusammenfassung'!C7 |
INDIREKT zum Aufbau eines dynamischen Bereichs
Wenn Sie über einen großen Datensatz verfügen, ist es wichtig, die Formeln zu optimieren, damit sie nicht mehr Arbeit als nötig machen. Anstatt beispielsweise auf die gesamte Spalte A zu verweisen, möchten wir möglicherweise nur auf die genaue Anzahl der Zellen in unserer Liste verweisen. Betrachten Sie das folgende Layout:
In Zelle B2 haben wir die Formel platziert
| 1 | =ANZAHL(A:A) |
Die COUNTA-Funktion ist für den Computer sehr einfach zu berechnen, da sie einfach prüft, wie viele Zellen in Spalte A einen Wert haben, anstatt logische Prüfungen oder mathematische Operationen durchführen zu müssen.
Lassen Sie uns nun unsere Formel erstellen, die die Werte in Spalte A summiert, aber wir möchten sicherstellen, dass nur der genaue Bereich mit Werten (A2: A5) berücksichtigt wird. Wir schreiben unsere Formel als
| 1 | =SUMME(INDIREKT("A2:A" & B2)) |
Unser INDIREKTER greift die Zahl 5 aus Zelle B2 und erstellt einen Verweis auf den Bereich A2:A5. Die SUM kann dann diesen Bereich für ihre Berechnung verwenden. Wenn wir in Zelle A6 einen weiteren Wert hinzufügen, wird die Zahl in B2 aktualisiert, und unsere SUMMEN-Formel wird ebenfalls automatisch aktualisiert, um diesen neuen Wert einzuschließen.
ACHTUNG: Mit der Einführung von Tabellen in Office 2007 ist es aufgrund der Flüchtigkeit von INDIREKT viel effizienter, Ihre Daten in einer Tabelle zu speichern und eine strukturelle Referenz zu verwenden, als die in diesem Beispiel verwendete Formel zu erstellen. Dies können jedoch Fälle sein, in denen Sie eine Liste von Elementen erstellen müssen und keine Tabelle verwenden können.
Dynamisches Charting mit INDIRECT
Nehmen wir das vorherige Beispiel und gehen noch einen Schritt weiter. Anstatt eine Formel zu schreiben, die uns die Summe der Werte liefert, erstellen wir einen benannten Bereich. Wir könnten diesen Bereich „MyData“ nennen und darauf verweisen
| 1 | =INDIREKT("A2:A" & ANZAHLA($A:$A)) |
Beachten Sie, dass wir die Referenz auf B2 vertauscht und stattdessen die COUNTA-Funktion direkt dort eingefügt haben, da wir dies in einen benannten Bereich einfügen.
Da wir nun diesen benannten Bereich haben, können wir ihn in einem Diagramm verwenden. Wir erstellen ein leeres Liniendiagramm und fügen dann eine Datenreihe hinzu. Für die Reihenwerte könntest du so etwas schreiben wie
| 1 | =Tabelle1!MeineDaten |
Das Diagramm verwendet nun diese Referenz auf Plotwerte. Wenn der Spalte A mehr Werte hinzugefügt werden, bezieht sich der INDIREKTE auf einen immer größeren Bereich, und unser Diagramm wird weiterhin mit allen neu hinzugefügten Werten aktualisiert.
Dynamische Datenvalidierung mit INDIRECT
Beim Sammeln von Eingaben von Benutzern besteht manchmal die Notwendigkeit, die Auswahlmöglichkeiten einer Wahl von einer vorherigen Wahl abhängig zu machen. Betrachten Sie dieses Layout, bei dem unsere erste Spalte dem Benutzer die Auswahl zwischen Obst, Gemüse und Fleisch ermöglicht.
Im 2nd Spalte möchten wir keine große Liste mit allen möglichen Optionen haben, da wir die Dinge bereits ein wenig eingegrenzt haben. Also haben wir 3 weitere Listen erstellt, die so aussehen:
Als Nächstes weisen wir jedem von diese Listen zu einem benannten Bereich. D.h., alle Früchte werden in einem Bereich namens "Früchte" und Gemüse in "Gemüse" usw.
Zurück in unserer Tabelle sind wir bereit, die Datenvalidierung im 2 einzurichtennd Säule. Wir erstellen eine Validierung des Listentyps mit einer Eingabe von:
| 1 | =INDIREKT(A2) |
Der INDIREKTE liest die in Spalte A getroffene Auswahl ein und sieht den Namen einer Kategorie. Wir haben Bereiche mit diesen Namen definiert, also nimmt der INDIRECT diesen Namen und erstellt eine Referenz auf den gewünschten Bereich.
Zusätzliche Bemerkungen
Verwenden Sie die INDIRECT-Funktion, um einen Zellbezug aus Text zu erstellen.
Erstellen Sie zuerst die Textzeichenfolge, die einen Zellbezug darstellt. Der String muss entweder im üblichen A1-Style Spaltenbuchstaben & Zeilennummer (M37) oder im R1C1-Style (R37C13) sein. Sie können die Referenz direkt eingeben, aber normalerweise verweisen Sie auf Zellen, die die Zeilen und Spalten definieren. Geben Sie zuletzt das gewünschte Zellbezugsformat ein. TRUE oder Omitted für A1-Style-Referenz oder FALSE für R1C1-Style.
Bei der Arbeit mit INDIREKTEN Formeln möchten Sie vielleicht die ROW-Funktion um die Zeilennummer einer Referenz zu erhalten oder die COLUMN-Funktion um die Spaltennummer (nicht den Buchstaben) einer Referenz zu erhalten.
Zurück zur Liste aller Funktionen in Excel
INDIREKT in Google Tabellen
Die INDIREKTE Funktion funktioniert in Google Sheets genauso wie in Excel:
