INDIREKTE Funktion In Excel
Die indirekte Funktion in Excel ist eine integrierte Funktion, mit der Zellwerte aus einer Textzeichenfolge referenziert und abgerufen werden. Diese Formel bezieht sich auf die referenzierte Zelle. Sie enthält zwei Argumente. Das erste Argument ist nicht optional, während das zweite Argument optional ist Aufgrund der Art der Übereinstimmung kann diese Funktion auch mit einer anderen Formel verwendet werden.
Die INDIRECT-Funktion in Excel wird mehrfach verwendet. Es gibt die durch eine Textzeichenfolge angegebene Zellreferenz zurück. Wenn beispielsweise Zelle B1 eine Zeichenfolge enthält, haben beispielsweise der Bereich c4 und c4 den Wert "Jessica". Wenn wir nun die INDIRECT-Funktion verwenden und den Referenztext als B1 übergeben, wird der im Zellbereich c4 enthaltene Wert "Jessica" zurückgegeben.
Mit der INDIRECT-Funktion von Excel können Sie INDIRECTly eine Zellenadresse angeben. Wie Sie im obigen INDIRECT Excel-Beispiel sehen können, gibt diese indirekte Excel-Formel den Inhalt der Zelle C4 ( Jessica ) zurück, wenn Zelle B1 den Text C4 enthält . Diese Funktion ist eine äußerst nützliche Funktion. Sie können die INDIRECT-Funktion verwenden, wenn Sie den Verweis auf eine Zelle innerhalb einer indirekten Excel-Formel ändern möchten, ohne die Formel selbst zu ändern.
INDIREKTE Excel-Formel
Erläuterung
Ref_text ist eine Referenz auf eine Zelle, die eine Referenz im A1-Stil, eine Referenz im R1C1-Stil, einen als Referenz definierten Namen oder eine Referenz auf eine Zelle als Textzeichenfolge enthält.
Das Argument a1 ist optional
| a1 | False oder 0, wenn ref_text im R1C1-Stil vorliegt |
| a1 | Ausgelassen oder True (1), wenn ref_text im A1-Stil vorliegt |
Wie verwende ich die INDIRECT-Funktion in Excel?
Nehmen wir ein INDIREKTES Excel-Beispiel, bevor wir die Adressfunktion in der Excel-Arbeitsmappe verwenden:
Angenommen, für eine Website www.xyz.com haben wir die Google-Analysedaten für Besucher der Website aus verschiedenen Zielgruppenkanälen für fünf verschiedene Länder, wie in der folgenden Tabelle gezeigt:
Wir können die INDIRECT-Funktion in Excel verwenden, um eine Textzeichenfolge in eine Referenz zu konvertieren. Verweisen Sie also auf Zelle H4 und verwenden Sie diesen Namen als Referenz. Die Verwendung der INDIRECT-Funktion in Excel besteht in diesem Fall darin, Namensbereiche unter Verwendung der Werte einer Zelle zu referenzieren. In Zelle I4 können wir also einfach die Summenfunktion verwenden, um die Gesamtzahl der Besucher aus einem Land Kanada zu berechnen.
Jetzt können wir die Summe der Besucher aus jedem Land direkt mit der Summenfunktion berechnen, aber mit der INDIRECT-Funktion in Excel können wir die Summe der Besucher aus verschiedenen Ländern berechnen, indem wir die Namensreferenz ändern, anstatt die indirekte Excel-Formel selbst zu ändern.
Wir haben den Namensbereich für jeden Länderbesucher erstellt. Dies kann einfach durch Eingabe des Namensbereichs in das Feld direkt unter der Zwischenablage erfolgen (siehe Abbildung unten).
Eine andere Methode ist, dass Sie zu Formeln-> Namensmanager gehen -> den Bereich auswählen, den Sie benennen möchten -> auf Namensmanager klicken -> den Namensbereich eingeben.
In ähnlicher Weise wurde auch für Besucher aus anderen Ländern ein benanntes Excel-Sortiment erstellt.
Wenn wir nun in I4 die Summe der Besucher aus dem Land Kanada mithilfe der INDIRECT-Funktion und des Namensbereichs in H4 (Kanada, ein Name oben für F3: F7) berechnen, erhalten wir die Gesamtsumme eines Besuchers.
Wenn wir mithilfe der Datenvalidierung für die Länder eine Datenliste erstellen und diese Liste auf H4 anwenden
Wenn Sie die verschiedenen Ländernamen von Kanada in Indien, die USA, Australien oder Singapur ändern, erhalten Sie die Gesamtsumme des Besuchers in I4 mithilfe der INDIRECT-Funktion in Excel.
Wir ändern also nicht für jeden Fall die indirekte Excel-Formel, die sich in Zelle I4 befindet. Wir ändern die Namensreferenz in H4 und berechnen die Summe der Besucher für jedes Land.
Wir können noch einen Schritt weiter gehen und Namen auf Arbeitsblattebene referenzieren, auch mit der INDIRECT-Funktion in Excel. Zum Beispiel werden wir jetzt die INDIRECT-Funktion in Bezug auf verschiedene Blätter in der Arbeitsmappe verwenden.
Wir haben verschiedene Blätter mit den Ländernamen für die Gesamtzahl der Verkäufe erstellt, die mit jedem Suchkanal für verschiedene Länder getätigt wurden.
Für Indien
Für die USA
Ähnliches gilt für andere Länder.
Wiederum berechnen wir den Gesamtumsatz, der über jeden Kanal mit der INDIRECT-Funktion in Excel für verschiedene Blätter mit Ländernamen als Referenz erzielt wird.
In diesem Fall müssen wir, da wir Verkaufsdaten in verschiedenen Blättern haben, verschiedene Elemente verketten, die den Datenbereich darstellen. Der ref_text beginnt also mit dem Namen des Blattes, in diesem Fall Indien in B4, und wir werden es dann tun Verwenden Sie den Operator & mit dem Namen kaufmännisches Und, der für die Verkettung verwendet wird.
Wir erhalten also die Gesamtsumme der Verkäufe für Indien; Wenn wir jetzt den Namen des Blattlandes in B4 von Indien in andere Länder wie die USA, Singapur oder Kanada ändern, erhalten wir den Gesamtverkaufswert dieser Länder.
Auch in diesem Fall haben wir die ref_text- Werte in Zelle B4 einfach geändert, ohne die indirekte Haupt-Excel-Formel in C4 zu ändern, um mithilfe der INDIRECT-Funktion die Summe der Verkäufe für verschiedene Länder zu erhalten, die sich in verschiedenen Blättern befinden.
Jetzt verwenden wir die INDIRECT-Funktion unter Verwendung der R1C1-Referenz, um den Verkaufswert des letzten Monats zu erhalten. Wir haben Verkäufe für verschiedene Monate in den Spalten B, C und D für drei Monate Januar, Februar und März, die auf weitere Monate wie April, Mai, Juni usw. anwachsen können. Die Verkaufstabelle wird also immer größer, abhängig von der Hinzufügung des bevorstehenden Monatsverkaufs. In diesem INDIRECT Excel-Beispiel möchten wir also den Verkauf des letzten Monats berechnen.
Hier ist also der letzte Monat März, und wir möchten den Gesamtverkauf des März-Monats, der 249.344 USD beträgt, mithilfe der INDIRECT-Funktion berechnen.
In diesem INDIRECT Excel-Beispiel finden wir die Verwendung des zweiten Arguments der INDIRECT-Funktion
INDIRECT (ref_text, a1)
a1: Dies ist ein optionales Argument. Es ist ein logischer Wert (ein Boolescher Wert), der angibt, welcher Referenztyp (ref_text) in der Zelle enthalten ist
Wenn a1 wahr (1) ist oder weggelassen wird, wird ref_text als Referenz im A1-Stil betrachtet
Wenn a1 false (0) ist, wird ref_text als R1C1-Stil interpretiert
Wie unterscheidet sich der A1-Stil vom R1C1-Stil?
Normalerweise verwendet die INDIRECT-Funktion in Excel die A1-Notation. Jede Zellenadresse besteht aus einem Spaltenbuchstaben und einer Zeilennummer. Die INDIRECT-Funktion in Excel unterstützt jedoch auch die R1C1-Notation. In diesem System wird Zelle A1 als Zelle R1C1, Zelle A2 als R2C1 usw. bezeichnet.
Um zur R1C1-Notation zu wechseln, wählen Sie Datei-> Option, um das Dialogfeld Excel-Optionen zu öffnen, klicken Sie auf die Registerkarte Formel und aktivieren Sie die Option R1C1-Referenzstil. Jetzt werden Sie feststellen, dass sich die Spaltenbuchstaben alle in Zahlen ändern. Und alle Zellreferenzen in Ihren Formeln werden ebenfalls angepasst.
Wenn wir also den R1C1-Stil in der INDIRECT-Funktion in Excel verwenden, übergeben wir den Wert a1 als false, und wenn wir den A1-Stil verwenden, übergeben wir den Wert true an a1.
In diesem INDIRECT Excel-Beispiel verwenden wir jetzt den R1C1-Stil aufgrund der Anforderung, da die Spalten mit dem Monat wachsen würden.
Bei Verwendung des R1C1-Stils befindet sich der Gesamtverkaufswert in Zeile 10, und der letzte Spaltenwert in der Spalte D wird mithilfe der COUNTA- Funktion berechnet , die die letzte Spaltennummer mit einem Wert angibt . Wenn Sie also in diesem Fall (10:10) zählen, erhalten Sie 4, die Spaltennummer im R1C1-Stil. Wir haben diesen Wert kontaktiert, um R10C4 zu erhalten, das den gewünschten Wert enthält, der dem Gesamtverkaufswert des letzten Monats entspricht (249.344 USD).
Und da wir hier den R1C1-Stil verwenden, übergeben wir einen falschen Wert für das Argument a1.
Fügen wir nun eine weitere Spalte mit dem Monat April Sale hinzu.
Erhalten Sie also neue Verkaufswerte für den letzten Monat, die denen des letzten Monats im April entsprechen. Wenn wir weitere Monate hinzufügen, erhalten wir immer den neuen Verkaufswert des letzten Monats, ohne die indirekte Excel-Formel für jeden Monat zu ändern. Dies ist die Spezialität der INDIRECT-Funktion in Excel.
Wie kann ich das Tabellenarray in der Funktion INDIRECT Excel VLOOKUP anpassen?
Wir haben fünf Länder und einen Suchkanal und den Verkauf, den die Website über diese verschiedenen Kanäle aus verschiedenen Ländern getätigt hat. Jetzt wollen wir in Zelle B3 den Verkauf erhalten, der durch organische Suchen aus dem Land USA erfolgte.
Für jede Tabelle für verschiedene Länder haben wir jede Tabelle mit einem Kurznamen für das Land, Ind für Indien, Staaten für die USA, Can für Kanada usw. benannt .
Wenn wir die INDIRECT Excel vlookup-Funktion direkt verwenden, um den Wert zu erhalten, wie unten beschrieben
Wir erhalten eine Fehlermeldung, weil die INDIRECT Excel-Lookup-Funktion das übergebene Tabellenarray nicht als Wert der B2-Zelle erkennen kann. Daher verwenden wir die INDIRECT-Funktion in Excel, damit das Lookup das table_array als gültiges Argument erkennt.
Wenn wir die Werte von B1 und B2 ändern, erhalten wir je nach Bedarf unterschiedliche Verkaufswerte in B3.
Hinweis: Bei Namensbereichen wird nicht zwischen Groß- und Kleinschreibung unterschieden. Daher sind Ind, IND und InD für Excel alle gleich.
Im nächsten INDIRECT Excel-Beispiel verwenden wir die INDIRECT-Funktion in Excel, um eine abhängige Liste zu erstellen. Wir haben eine Liste der Position eines Mitarbeiters in einem Unternehmen; dann haben wir für jede Position die Namen der Mitarbeiter.
In Zelle I2 erstellen wir nun eine Liste für verschiedene Mitarbeiterränge als deren Positionen, und in I3 zeigen wir eine Liste an, die vom Wert in I2 abhängt. Wenn I2 ein Regisseur ist, sollte I3 die Namen der Direktoren widerspiegeln, die Andrew und Micheal haben. Wenn I2 Manager ist, sollte I3 die Namen des Managers widerspiegeln, der Camille, David, Davis und William ist, und auf die gleiche Weise für den Supervisor.
In diesem Fall verwenden wir erneut die INDIRECT-Funktion in Excel, um die abhängige Liste zu erstellen. Datenvalidierung In Einstellungen, in denen Validierungskriterien als Liste und im Quellfeld ausgewählt werden, wird die INDIRECT-Funktion in Excel wie folgt verwendet.
= INDIREKT ($ I $ 2)
Wenn wir in I3 Direktorennamen auswählen, die als Liste mit den Namen Andrew und Micheal angezeigt werden, wenn I2 Manager ist, spiegelt I3 die Namen von Manager Camille, David, Davis und William und in ähnlicher Weise für den Supervisor wider, wie unten in der Abbildung gezeigt.
Dinge, an die man sich erinnern sollte
- Das Argument ref_text muss eine gültige Zellreferenz sein, sonst gibt INDIRECT das #REF zurück! Error. Das Argument ref_text kann auf eine andere Arbeitsmappe verweisen.
- Wenn sich ref_text auf eine andere Arbeitsmappe bezieht (eine externe Referenz), muss die andere Arbeitsmappe geöffnet sein. Wenn die Quellarbeitsmappe nicht geöffnet ist, gibt INDIRECT das #REF zurück! Fehlerwert.
- Wenn sich ref_text auf einen Zellbereich außerhalb des Zeilenlimits von 1.048.576 oder des Spaltenlimits von 16.384 (XFD) bezieht, gibt INDIRECT ein #REF zurück! Error.
