Dynamische Excel-Arbeitsblatt- oder Arbeitsmappenreferenz
Angenommen, Sie haben Daten mit demselben Format über mehrere Arbeitsblätter oder Arbeitsmappen hinweg und müssen Daten aus diesen Arbeitsblättern oder Arbeitsmappen dynamisch in ein anderes Blatt übertragen. Mit der INDIRECT-Funktion können Sie dies schnell erledigen.
Referenzzellen in einem anderen Arbeitsblatt dynamisch
Referenzzellen in einer anderen Arbeitsmappe dynamisch
Referenzzellen in einem anderen Arbeitsblatt dynamisch
Angenommen, es gibt vier Arbeitsblätter mit unterschiedlichen Verkäufen in Quartalen für vier Verkäufer, und Sie möchten ein zusammenfassendes Arbeitsblatt erstellen, um die Quartalsverkäufe basierend auf dem entsprechenden Verkäufer dynamisch abzurufen. Damit es funktioniert, kann die folgende Formel helfen.
Generische Formel
=INDIRECT("'"&sheet_name&"'!Cell to return data from")
1. Wie im folgenden Screenshot gezeigt, müssen Sie zunächst das zusammenfassende Arbeitsblatt erstellen, indem Sie die Blattnamen separat in verschiedene Zellen eingeben. Wählen Sie dann eine leere Zelle aus, kopieren Sie die folgende Formel hinein und drücken Sie die Taste Enter Key.
=INDIRECT("'"&B3&"'!C3")
Notizen: Im Code:
- B3 ist die Zelle mit dem Blattnamen, aus dem Sie Daten abrufen möchten;
- C3 ist die Zellenadresse in dem spezifischen Arbeitsblatt, aus dem Sie die Daten abrufen.
- Um zu verhindern, dass der Fehlerwert zurückgegeben wird, wenn entweder B5 (die Blattnamenszelle) oder C3 (die Zelle, aus der Sie die Daten abrufen) leer ist, fügen Sie der INDIRECT-Formel eine IF-Funktion bei, die wie folgt angezeigt wird:
= IF (OR (B3 = "", C3 = ""), "", INDIRECT ($ B $ 3 & "! C3")) - Wenn Ihre Blattnamen keine Leerzeichen enthalten, können Sie diese Formel direkt verwenden
= INDIREKT (B3 & "! C3")
2. Ziehen Sie dann seine Griff füllen nach unten, um die Formel auf andere Zellen anzuwenden. Jetzt haben Sie alle Verkäufe des ersten Quartals aus den spezifischen Arbeitsblättern zurückgegeben.
3. Ziehen Sie alle Verkäufe anderer Quartale nach Bedarf ab. Und vergessen Sie nicht, die Zellreferenz in der Formel zu ändern.
Referenzzellen in einer anderen Arbeitsmappe dynamisch
In diesem Abschnitt wird über das dynamische Verweisen von Zellen in einer anderen Arbeitsmappe in Excel gesprochen.
Generische Formel
=INDIRECT("'[" & Book name & "]" & Sheet name & "'!" & Cell address)
Wie der folgende Screenshot zeigt, befinden sich die Daten, die Sie zurückgeben möchten, in Spalte E des Arbeitsblatts "Gesamtumsatz" in einer separaten Arbeitsmappe mit dem Namen "SalesFile". Bitte gehen Sie Schritt für Schritt wie folgt vor, um dies zu erreichen.
1. Geben Sie zunächst die Arbeitsmappeninformationen (einschließlich des Arbeitsmappennamens, des Arbeitsblattnamens und der Referenzzellen) ein, die Sie basierend auf diesen Informationen in die aktuelle Arbeitsmappe ziehen.
2. Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel hinein und drücken Sie die Taste Enter Key.
=INDIRECT("'["&$B$3&"]"&$C$3&"'!"&D3)
Notizen:
- B3 enthält den Arbeitsmappennamen, aus dem Sie die Daten extrahieren möchten;
- C3 ist der Blattname;
- D3 ist die Zelle, aus der Sie Daten abrufen werden;
- Das BEZUG! Der Fehlerwert wird zurückgegeben, wenn die referenzierte Arbeitsmappe geschlossen wird.
- Um das #REF zu vermeiden! Fehlerwert, bitte fügen Sie die INDIRECT-Formel wie folgt der IFERROR-Funktion bei:
= IFERROR (INDIREKT ("'[" & $ B $ 3 & "]" & $ C $ 3 & "'!" & D3), "")
3. Ziehen Sie dann den Füllgriff nach unten, um die Formel auf andere Zellen anzuwenden.
TIPP: Wenn Sie nicht möchten, dass der Rückgabewert nach dem Schließen der referenzierten Arbeitsmappe ein Fehler wird, können Sie den Arbeitsmappennamen, den Arbeitsblattnamen und die Zellenadresse in der Formel wie folgt direkt angeben:
=INDIRECT('[SalesFile.xlxs]Total sales'!E3,"")
Verwandte Funktion
Die INDIRECT-Funktion
Die Microsoft Excel INDIRECT-Funktion konvertiert eine Textzeichenfolge in eine gültige Referenz.
Die besten Tools für die Office-Produktivität
Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...
Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).
- Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
- Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
- Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.
