Inhalte
Herzliche Glückwünsche! Du hast es bis zum letzten Tag des Marathons geschafft 30 Excel-Funktionen in 30 Tagen. Es war eine lange und interessante Reise, auf der Sie viele nützliche Dinge über Excel-Funktionen gelernt haben.
Am 30. Tag des Marathons widmen wir uns dem Studium der Funktion INDIREKT (INDIREKT), die den durch die Textzeichenfolge angegebenen Link zurückgibt. Mit dieser Funktion können Sie abhängige Dropdown-Listen erstellen. Wenn Sie beispielsweise ein Land aus einer Dropdown-Liste auswählen, bestimmt dies, welche Optionen in der Dropdown-Liste für Städte angezeigt werden.
Schauen wir uns also den theoretischen Teil der Funktion genauer an INDIREKT (INDIREKT) und untersuchen Sie praktische Anwendungsbeispiele. Wenn Sie zusätzliche Informationen oder Beispiele haben, teilen Sie diese bitte in den Kommentaren mit.
Funktion 30: INDIREKT
Funktion INDIREKT (INDIREKT) gibt den durch die Textzeichenfolge angegebenen Link zurück.
Wie können Sie die INDIREKT-Funktion verwenden?
Da die Funktion INDIREKT (INDIREKT) gibt einen Link zurück, der durch eine Textzeichenfolge angegeben wird. Sie können ihn verwenden, um:
- Erstellen Sie einen unveränderlichen Anfangslink.
- Erstellen Sie einen Verweis auf einen statischen benannten Bereich.
- Erstellen Sie einen Link mit Blatt-, Zeilen- und Spalteninformationen.
- Erstellen Sie ein sich nicht verschiebendes Array von Zahlen.
Syntax INDIREKT (INDIREKT)
Funktion INDIREKT (INDIREKT) hat die folgende Syntax:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) ist der Text des Links.
- a1 – wenn gleich TRUE (TRUE) oder nicht angegeben, dann wird der Stil des Links verwendet A1; und wenn FALSE (FALSE), dann der Stil R1C1.
Fallen INDIREKT (INDIREKT)
- Funktion INDIREKT (INDIREKT) wird immer dann neu berechnet, wenn sich die Werte im Excel-Arbeitsblatt ändern. Dies kann Ihre Arbeitsmappe stark verlangsamen, wenn die Funktion in vielen Formeln verwendet wird.
- Ist die Funktion INDIREKT (INDIREKT) erstellt einen Link zu einer anderen Excel-Arbeitsmappe, diese Arbeitsmappe muss geöffnet sein oder die Formel meldet einen Fehler BEZUG! (#VERKNÜPFUNG!).
- Ist die Funktion INDIREKT (INDIREKT) auf einen Bereich verweist, der die Zeilen- und Spaltenbegrenzung überschreitet, meldet die Formel einen Fehler BEZUG! (#VERKNÜPFUNG!).
- Funktion INDIREKT (INDIREKT) kann nicht auf einen dynamischen benannten Bereich verweisen.
Beispiel 1: Erstellen Sie einen nicht verschiebenden Anfangslink
Im ersten Beispiel enthalten die Spalten C und E die gleichen Zahlen, deren Summen mit der Funktion berechnet werden SUM (SUMME) sind ebenfalls gleich. Allerdings sind die Formeln etwas anders. In Zelle C8 lautet die Formel:
=SUM(C2:C7)
=СУММ(C2:C7)
In Zelle E8 die Funktion INDIREKT (INDIREKT) stellt eine Verbindung zur Ausgangszelle E2 her:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Wenn Sie oben im Blatt eine Zeile einfügen und den Wert für Januar (Jan) hinzufügen, ändert sich der Betrag in Spalte C nicht. Die Formel ändert sich und reagiert auf das Hinzufügen einer Zeile:
=SUM(C3:C8)
=СУММ(C3:C8)
Allerdings ist die Funktion INDIREKT (INDIREKT) legt E2 als Startzelle fest, sodass der Januar automatisch in die Berechnung der Summen in Spalte E einbezogen wird. Die Endzelle hat sich geändert, aber die Startzelle wurde nicht beeinflusst.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Beispiel 2: Link zu einem statischen benannten Bereich
Funktion INDIREKT (INDIREKT) kann einen Verweis auf einen benannten Bereich erstellen. In diesem Beispiel bilden die blauen Zellen den Bereich NumListe. Zusätzlich wird aus den Werten in Spalte B auch ein dynamischer Bereich erstellt NumListDyn, abhängig von der Anzahl der Zahlen in dieser Spalte.
Die Summe für beide Bereiche kann berechnet werden, indem der Funktion einfach deren Name als Argument übergeben wird SUM (SUMME), wie Sie in den Zellen E3 und E4 sehen können.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Anstatt einen Bereichsnamen in eine Funktion einzugeben SUM (SUMME), Sie können sich auf den Namen beziehen, der in eine der Zellen des Arbeitsblatts geschrieben ist. Wenn zum Beispiel der Name NumListe in Zelle D7 geschrieben wird, dann sieht die Formel in Zelle E7 so aus:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Leider die Funktion INDIREKT (INDIREKT) kann keine dynamische Bereichsreferenz erstellen. Wenn Sie diese Formel also nach unten in Zelle E8 kopieren, erhalten Sie eine Fehlermeldung BEZUG! (#VERKNÜPFUNG!).
Beispiel 3: Erstellen Sie einen Link mit Blatt-, Zeilen- und Spalteninformationen
Sie können auf einfache Weise eine Verknüpfung basierend auf den Zeilen- und Spaltennummern erstellen sowie den Wert FALSE (FALSE) für das zweite Funktionsargument verwenden INDIREKT (INDIREKT). So entsteht der Style-Link R1C1. In diesem Beispiel haben wir dem Link zusätzlich den Blattnamen hinzugefügt – „MyLinks“!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Beispiel 4: Erstellen Sie ein sich nicht verschiebendes Array von Zahlen
Manchmal müssen Sie ein Array von Zahlen in Excel-Formeln verwenden. Im folgenden Beispiel wollen wir die 3 größten Zahlen in Spalte B mitteln. Die Zahlen können wie in Zelle D4 in eine Formel eingegeben werden:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Wenn Sie ein größeres Array benötigen, möchten Sie wahrscheinlich nicht alle Zahlen in die Formel eingeben. Die zweite Möglichkeit besteht darin, die Funktion zu verwenden REIHE (ROW), wie in der in Zelle D5 eingegebenen Matrixformel:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Die dritte Möglichkeit besteht darin, die Funktion zu verwenden REIHE (STRING) zusammen mit INDIREKT (INDIREKT), wie bei der Matrixformel in Zelle D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Das Ergebnis für alle 3 Formeln ist dasselbe:
Wenn jedoch Zeilen am oberen Rand des Blatts eingefügt werden, gibt die zweite Formel ein falsches Ergebnis zurück, da sich die Verweise in der Formel zusammen mit der Zeilenverschiebung ändern. Jetzt gibt die Formel anstelle des Durchschnitts der drei größten Zahlen den Durchschnitt der dritt-, 3- und 4-größten Zahl zurück.
Funktionen verwenden INDIREKT (INDIREKT) behält die dritte Formel die richtigen Zeilenbezüge bei und zeigt weiterhin das richtige Ergebnis an.