Inhalte
Auf den ersten Blick (insbesondere beim Lesen der Hilfe) die Funktion INDIREKT (INDIREKT) sieht einfach und sogar unnötig aus. Seine Essenz besteht darin, Text, der wie ein Link aussieht, in einen vollwertigen Link zu verwandeln. Diese. Wenn wir auf Zelle A1 verweisen müssen, können wir entweder gewöhnlich einen direkten Link erstellen (geben Sie ein Gleichheitszeichen in D1 ein, klicken Sie auf A1 und drücken Sie die Eingabetaste), oder wir können verwenden INDIREKT zum gleichen Zweck:
Bitte beachten Sie, dass das Funktionsargument – ein Verweis auf A1 – in Anführungszeichen gesetzt wird, es sich hier also tatsächlich um Text handelt.
„Na gut“, sagst du. "Und was ist der Vorteil?"
Aber urteilen Sie nicht nach dem ersten Eindruck – er täuscht. Diese Funktion kann Ihnen in vielen Situationen helfen.
Beispiel 1. Transponieren
Ein Klassiker des Genres: Sie müssen den vertikalen Durchmesser drehen
Groove in die Horizontale (transponieren). Natürlich können Sie eine spezielle Einfügung oder Funktion verwenden TRANSP (TRANSPOSIEREN) in einer Matrixformel, aber Sie können mit unserer auskommen INDIREKT:
Die Logik ist einfach: Um die Adresse der nächsten Zelle zu erhalten, kleben wir den Buchstaben „A“ mit dem Sonderzeichen „&“ und der Spaltennummer der aktuellen Zelle zusammen, die uns die Funktion liefert SPALTE (SÄULE).
Der umgekehrte Vorgang wird besser etwas anders durchgeführt. Da wir zu diesem Zeitpunkt eine Verbindung zu den Zellen B2, C2, D2 usw. herstellen müssen, ist es bequemer, den R1C1-Verbindungsmodus anstelle der klassischen „Seeschlacht“ zu verwenden. In diesem Modus unterscheiden sich unsere Zellen nur in der Spaltennummer: B2=R1C2, C2=R1C3, D2=R1C4 usw.
Hier kommt das zweite optionale Funktionsargument ins Spiel. INDIREKT. Wenn es gleich ist LÜGNERISCH (FALSCH), dann können Sie die Verbindungsadresse im R1C1-Modus einstellen. So können wir den horizontalen Bereich leicht zurück in den vertikalen transponieren:
Beispiel 2. Summe nach Intervall
Wir haben bereits eine Möglichkeit analysiert, mit der Funktion über ein Fenster (Bereich) einer bestimmten Größe auf einem Blatt zu summieren VERFÜGUNG (OFFSET). Ein ähnliches Problem kann auch mit gelöst werden INDIREKT. Wenn wir Daten nur aus einem bestimmten Zeitraum zusammenfassen müssen, können wir sie aus Stücken zusammenfügen und dann in einen vollwertigen Link verwandeln, den wir in die Funktion einfügen können SUM (SUMME):
Beispiel 3. Intelligente Tabellen-Dropdown-Liste
Manchmal behandelt Microsoft Excel intelligente Tabellennamen und Spalten nicht als vollständige Links. Wenn Sie also beispielsweise versuchen, eine Dropdown-Liste (Tab Daten – Datenvalidierung) basierend auf Spalte Mitarbeiter:innen von smarttable Personen wir erhalten einen Fehler:
Wenn wir den Link mit unserer Funktion „verpacken“. INDIREKT, dann akzeptiert Excel es einfach und unsere Dropdown-Liste wird dynamisch aktualisiert, wenn neue Mitarbeiter am Ende der intelligenten Tabelle hinzugefügt werden:
Beispiel 4. Unbreakable Links
Wie Sie wissen, korrigiert Excel Referenzadressen in Formeln automatisch, wenn Zeilen-Spalten auf einem Blatt eingefügt oder gelöscht werden. In den meisten Fällen ist dies richtig und bequem, aber nicht immer. Angenommen, wir müssen die Namen aus dem Mitarbeiterverzeichnis in den Bericht übertragen:
Wenn Sie normale Links setzen (geben Sie =B2 in die erste grüne Zelle ein und kopieren Sie es nach unten), wenn Sie beispielsweise Dasha löschen, erhalten wir den #LINK! Fehler in der ihr entsprechenden grünen Zelle. (#REF!). Bei Verwendung der Funktion zum Erstellen von Links INDIREKT es wird kein solches Problem geben.
Beispiel 5: Sammeln von Daten aus mehreren Blättern
Angenommen, wir haben 5 Blätter mit Berichten desselben Typs von verschiedenen Mitarbeitern (Mikhail, Elena, Ivan, Sergey, Dmitry):
Gehen wir davon aus, dass Form, Größe, Lage und Reihenfolge der Waren und Monate in allen Tabellen gleich sind – nur die Zahlen unterscheiden sich.
Mit nur einer Formel können Sie Daten aus allen Blättern sammeln (nicht aufsummieren, sondern in einem „Stapel“ untereinander legen):
Wie Sie sehen können, ist die Idee dieselbe: Wir kleben den Link an die gewünschte Zelle des angegebenen Blatts und INDIREKT macht daraus ein „Live“. Der Einfachheit halber habe ich über der Tabelle die Buchstaben der Spalten (B, C, D) und rechts die Zeilennummern hinzugefügt, die aus jedem Blatt entnommen werden müssen.
Fallstricke
Wenn Sie INDIREKT (INDIREKT) Sie müssen sich an seine Schwächen erinnern:
- Wenn Sie auf eine andere Datei verlinken (durch Einfügen des Dateinamens in eckige Klammern, des Blattnamens und der Zellenadresse), funktioniert dies nur, während die Originaldatei geöffnet ist. Wenn wir es schließen, erhalten wir den Fehler #LINK!
- INDIREKT kann nicht auf einen dynamischen benannten Bereich verweisen. Auf statisch – kein Problem.
- INDIREKT ist eine flüchtige oder „flüchtige“ Funktion, dh sie wird für jede Änderung in einer beliebigen Zelle des Blatts neu berechnet und nicht nur für die Beeinflussung von Zellen, wie bei normalen Funktionen. Dies wirkt sich negativ auf die Performance aus und es ist besser, sich nicht von großen INDIREKT-Tabellen mitreißen zu lassen.
- So erstellen Sie einen Dynamikbereich mit automatischer Größenanpassung
- Summieren über ein Bereichsfenster auf einem Blatt mit der OFFSET-Funktion