Verwenden Sie die Funktion INDIRECT in Excel-Formeln, um den Bereich der in einer Formel verwendeten Zellreferenzen zu ändern, ohne die Formel selbst bearbeiten zu müssen. Dadurch wird sichergestellt, dass die gleichen Zellen verwendet werden, auch wenn sich Ihre Kalkulationstabelle ändert.
Hinweis: Die Anweisungen in diesem Artikel beziehen sich auf Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel für Mac und Excel Online.
Verwenden Sie einen Dynamikbereich mit der COUNTIF - INDIRECT-Formel
Die INDIRECT-Funktion kann mit einer Reihe von Funktionen verwendet werden, die eine Zellenreferenz als Argument akzeptieren, z. B. die Funktionen SUM und COUNTIF.
Wenn Sie INDIRECT als Argument für COUNTIF verwenden, wird ein dynamischer Bereich von Zellverweisen erstellt, der von der Funktion gezählt werden kann, wenn die Zellwerte ein Kriterium erfüllen. Dies geschieht, indem Textdaten, die manchmal als Textzeichenfolge bezeichnet werden, in eine Zellenreferenz umgewandelt werden.
Dieses Beispiel basiert auf den im obigen Bild gezeigten Daten. Die im Tutorial erstellte COUNTIF - INDIRECT-Formel lautet:
= COUNTIF (INDIREKT (E1 & ":" & E2), "> 10")
In dieser Formel enthält das Argument für die INDIRECT-Funktion:
- Die Zelle verweist auf E1 und E2, die die Textdaten D1 und D6 enthalten.
- Der Bereichsoperator, der Doppelpunkt (:) umgeben von doppelten Anführungszeichen (' ') das den Doppelpunkt in eine Zeichenfolge umwandelt.
- Zwei Et-Zeichen (&), die verwendet werden, um den Doppelpunkt mit den Zellbezügen E1 und E2 zu verketten oder zu verbinden.
Das Ergebnis ist, dass INDIRECT die Textzeichenfolge D1: D6 in eine Zellenreferenz konvertiert und an die COUNTIF-Funktion weitergibt, um gezählt zu werden, wenn die referenzierten Zellen größer als 10 sind.
Die Funktion INDIRECT akzeptiert alle Texteingaben. Dies können Zellen im Arbeitsblatt sein, die Text- oder Textzellenreferenzen enthalten, die direkt in die Funktion eingegeben werden.
Ändern Sie den Bereich der Formel dynamisch
Denken Sie daran, dass es das Ziel ist, eine Formel mit einem dynamischen Bereich zu erstellen. Ein dynamischer Bereich kann geändert werden, ohne die Formel selbst zu bearbeiten.
Durch Ändern der in den Zellen E1 und E2 befindlichen Textdaten von D1 und D6 zu D3 und D7 kann der durch die Funktion summierte Bereich leicht von D1: D6 zu D3: D7 geändert werden. Dadurch müssen Sie die Formel in Zelle G1 nicht direkt bearbeiten.
Die COUNTIF-Funktion in diesem Beispiel zählt nur Zellen mit Zahlen, wenn diese größer als 10 sind. Obwohl vier der fünf Zellen im Bereich von D1: D6 Daten enthalten, enthalten nur drei Zellen Zahlen. Zellen, die leer sind oder Textdaten enthalten, werden von der Funktion ignoriert.
Zählen von Text mit COUNTIF
Die COUNTIF-Funktion ist nicht auf das Zählen numerischer Daten beschränkt. Es zählt auch Zellen mit Text, indem geprüft wird, ob sie mit einem bestimmten Text übereinstimmen.
Dazu wird die folgende Formel in Zelle G2 eingetragen:
= COUNTIF (INDIREKT (E1 & ":" & E2), "zwei")
In dieser Formel referenziert die INDIRECT-Funktion die Zellen B1 bis B6. Die COUNTIF-Funktion summiert die Anzahl der Zellen mit dem Textwert zwei in ihnen.
In diesem Fall ist das Ergebnis 1.
COUNTA, COUNTBLANK und INDIRECT
Zwei weitere Excel-Zählfunktionen sind COUNTA, die Zellen zählt, die beliebige Datentypen enthalten, wobei nur leere oder leere Zellen ignoriert werden, und COUNTBLANK, die nur leere oder leere Zellen in einem Bereich zählt.
Da beide Funktionen eine ähnliche Syntax wie die Funktion COUNTIF aufweisen, können sie mit INDIRECT in das obige Beispiel eingefügt werden, um die folgenden Formeln zu erstellen:
= COUNTA (INDIREKT (E1 & ":" & E2))= COUNTBLANK (INDIREKT (E1 & ":" & E2)
Für den Bereich D1: D6 gibt COUNTA eine Antwort von 4 zurück, da vier der fünf Zellen Daten enthalten. COUNTBLANK gibt eine Antwort von 1 zurück, da sich nur eine leere Zelle im Bereich befindet. Der Vorteil der Verwendung der INDIRECT-Funktion in all diesen Formeln besteht darin, dass neue Zellen an beliebiger Stelle im Bereich eingefügt werden können. Der Bereich verschiebt sich dynamisch innerhalb der verschiedenen Funktionen und die Ergebnisse werden entsprechend aktualisiert. Ohne die Funktion INDIRECT muss jede Funktion so bearbeitet werden, dass sie alle 7 Zellen einschließlich der neuen enthält. Die Vorteile der INDIRECT-Funktion bestehen darin, dass Textwerte als Zellenreferenzen eingefügt werden können und dass Bereiche dynamisch aktualisiert werden, wenn sich Ihre Kalkulationstabelle ändert. Dies erleichtert die allgemeine Wartung der Tabellenkalkulation, insbesondere bei sehr großen Tabellenkalkulationen. Warum eine INDIREKTE Funktion verwenden?
