Grundlagen externe Referenzen
In diesem Artikel gehen wir auf die Grundlagen einer externen Referenz in Excel ein und zeigen, wie Sie sie in Formeln verwenden, um eine Verknüpfung zu einer anderen Seite oder Datei herzustellen.
Sie müssen häufig Daten aus einem anderen Arbeitsblatt oder sogar aus einer anderen Excel-Datei extrahieren, wenn Sie Berechnungen in Excel durchführen. Können Sie das bewerkstelligen? Verwenden Sie einfach einen externen Zellverweis, oft als Link bezeichnet, um die Arbeitsblätter zu verbinden (unabhängig davon, ob sie sich in derselben Arbeitsmappe oder in anderen Arbeitsmappen befinden).
In Excel ist ein externer Verweis ein Verweis auf eine Zelle oder einen Zellbereich, die nicht Teil des aktiven Arbeitsblatts sind. Der Hauptvorteil der Verwendung einer externen Excel-Referenz besteht darin, dass der von der externen Zellreferenz zurückgegebene Wert sofort aktualisiert wird, wenn sich die referenzierte(n) Zelle(n) in einem anderen Arbeitsblatt ändert.
Die externen Referenzen und Zellreferenzen von Excel sind sich sehr ähnlich, es gibt jedoch einige wichtige Unterschiede. Wir beginnen in dieser Lektion mit den Grundlagen und arbeiten uns nach oben, wobei wir Schritt-für-Schritt-Anleitungen, Screenshots und Formelbeispiele bereitstellen.
Wie verwende ich ein anderes Blatt als Referenz in Excel?
Setzen Sie den Arbeitsblattnamen gefolgt von einem Ausrufezeichen (!) vor die Zellenadresse, um auf eine Zelle oder einen Zellbereich in einem anderen Arbeitsblatt in derselben Arbeitsmappe zu verweisen.
Mit anderen Worten, Sie verwenden das unten gezeigte Format, wenn Sie auf ein Arbeitsblatt in Excel verweisen.
Referenz auf eine bestimmte Zelle:
Sie geben beispielsweise Sheet2 ein, um auf Zelle A1 in Sheet2 zu verweisen! A1.
Referenz auf mehrere Zellen:
Sie geben beispielsweise Sheet2 ein, um auf die Zellen A1 bis A10 in Sheet2 zu verweisen! A1:A10.
Hinweis: Sie müssen den Arbeitsblattnamen in einfache Anführungszeichen setzen, wenn er Leerzeichen oder andere nicht alphabetische Zeichen enthält. Wenn beispielsweise auf Zelle A1 in einer Tabelle mit dem Namen „Projektmeilensteine“ extern verwiesen wird, sollte dort „Projektmeilensteine“ stehen! A1.
Rechnen mit externer Referenz
Eine Excel-Tabellenreferenz in einer realen Formel, die die Zahl in Zelle A1 der Tabelle „Projektmeilensteine“ mit zehn multipliziert, sieht folgendermaßen aus:
Hinzufügen eines Verweises auf eine andere Excel-Tabelle
Externe Referenz mit einem Klick
Natürlich können Sie den Namen des anderen Arbeitsblatts, ein Ausrufezeichen und den Zellbezug manuell eingeben, wenn Sie eine Formel entwerfen, die auf Zellen in einem anderen Arbeitsblatt verweist, aber dies wäre eine träge und fehleranfällige Methode.
Das Klicken auf die Zelle(n) in einem anderen Blatt, auf die sich die Formel beziehen soll, ist ein bevorzugter Ansatz. Excel kümmert sich um die richtige Syntax für Ihre Tabellenreferenz. Nehmen Sie die folgenden Änderungen an Ihrer Formel vor, damit Excel einen Verweis auf ein anderes Blatt hinzufügt:
1. Beginnen Sie entweder in der Bearbeitungsleiste oder einer Zielzelle mit der Eingabe einer Formel.
2. Um auf ein anderes Arbeitsblatt zu verweisen, blättern Sie einfach zu dieser Seite und wählen Sie die Zelle oder Zellen aus, auf die Sie verweisen möchten.
3. Wenn die Formel vollständig ist, beenden Sie sie mit der Eingabetaste.
Beispiel externe Referenz
Wenn Sie beispielsweise die Mehrwertsteuer (19 Prozent) für jedes Produkt in einem anderen Blatt mit dem Namen Mehrwertsteuer ermitteln möchten und Sie eine Liste mit Verkaufsdaten im Blatt Verkauf haben, gehen Sie wie folgt vor:
• Beginnen Sie mit der Eingabe der Formel =19%* in Zelle B2 auf dem Blatt Mehrwertsteuer.
• Wechseln Sie in das Blatt Sales und klicken Sie dort auf die Zelle B2. Excel fügt sofort einen externen Verweis auf diese Zelle ein, wie im folgenden Screenshot gezeigt:
• Drücken Sie die Eingabetaste, um die Formel abzuschließen.
Relativer Verweis
Notiz. Unter Verwendung der oben genannten Technik fügt Microsoft Excel automatisch einen relativen Verweis (ohne das Dollarzeichen) hinzu, wenn ein Excel-Verweis zu einem anderen Blatt hinzugefügt wird. Da sich die Zellbezüge für jede Zeile ändern, können Sie die Formel aus dem obigen Beispiel einfach für andere Zellen in Spalte B auf dem Blatt Mehrwertsteuer wiederholen, um die Mehrwertsteuer für jedes Produkt genau zu berechnen.
Sie können auf ähnliche Weise auf eine Reihe von Zellen in einem anderen Blatt verweisen. Die Mehrfachzellenauswahl des Quellarbeitsblatts ist die einzige Änderung. Sie könnten beispielsweise die folgende Formel eingeben, um die Summe der Verkäufe in den Zellen B2 bis B5 auf dem Blatt Sales zu berechnen:
In Excel können Sie auf diese Weise auf ein anderes Blatt verweisen. Schauen wir uns nun an, wie Sie auf Zellen in einem anderen Arbeitsblatt verweisen können.
Wie verweise ich auf eine andere Arbeitsmappe in Excel?
Abhängig davon, ob die Quellarbeitsmappe geöffnet oder geschlossen ist, werden externe Verweise auf andere Arbeitsmappen in Microsoft Excel-Formeln auf eine von zwei Arten angezeigt.
Ein externer Excel-Verweis beginnt mit dem Namen der Quellarbeitsmappe in eckigen Klammern (einschließlich der Dateierweiterung), gefolgt vom Namen des Blatts, einem Ausrufezeichen (! ) und der Zelle oder dem Zellbereich, auf die verwiesen wird. Um auf ein geöffnetes Arbeitsblatt zu verweisen, sollten Sie das unten beschriebene Referenzformat verwenden:
Hier ist beispielsweise ein externer Verweis auf die Zellen B2:B5 auf Blatt Jan in der Arbeitsmappe namens Sales.xlsx:
Wenn Sie beispielsweise die Summe dieser Zellen berechnen möchten, würde die Formel mit der Arbeitsmappenreferenz wie folgt aussehen:
Externer Verweis auf eine geschlossene Arbeitsmappe
In Excel muss die andere Arbeitsmappe nicht immer geöffnet sein, um darauf zu verweisen. Sie müssen die gesamte Route zu Ihrer externen Referenz hinzufügen, wenn das Quellarbeitsblatt geschlossen ist.
Beispielsweise könnten Sie die folgende Formel verwenden, um die Werte in den Zellen B2 bis B5 des Jan-Blatts aus der Sales.xlsx-Arbeitsmappe zu summieren, die sich im Ordner „Berichte“ auf Laufwerk D befindet:
Aufbau der Referenzen
• Dateipfad – Gibt das Laufwerk und den Ort an, an dem sich Ihre Excel-Datei befindet (in diesem Beispiel D:\\Berichte\\).
• Name der Arbeitsmappe – Er erscheint immer in eckigen Klammern und enthält die Dateierweiterung (.xlsx,.xls oder.xslm), wie in der obigen Formel mit [Sales.xlsx].
Blattname – Dieser Abschnitt der externen Excel-Referenz enthält den Blattnamen, ein Ausrufezeichen und die Zelle(n), auf die verwiesen wird (in diesem Fall Jan!).
• Zellreferenz – Leitet den Benutzer zu der angegebenen Zelle oder einer Gruppe von Zellen, auf die sich Ihre Formel bezieht.
Wenn Sie einen Verweis auf eine geöffnete Arbeitsmappe generierten und dann das Quellarbeitsblatt schlossen, wurde der Verweis auf die externe Arbeitsmappe sofort aktualisiert und enthielt den vollständigen Pfad.
Notiz. Wenn entweder der Arbeitsmappenname oder der Blattname oder beide Leerzeichen oder andere nicht alphabetische Zeichen enthalten, müssen Sie die Route in einfache Anführungszeichen setzen. Zur Veranschaulichung:
Erstellen eines Verweises auf eine andere Arbeitsmappe in Excel
Sie müssen keinen Verweis auf eine andere Arbeitsmappe manuell eingeben, genau wie beim Erstellen einer Excel-Formel, die auf eine andere Seite verweist. Öffnen Sie beim Einfügen Ihrer Formel einfach das andere Arbeitsblatt und wählen Sie die Zelle oder Zellen aus, auf die Sie verweisen möchten. Den Rest erledigt Microsoft Excel:
Anmerkungen:
• Excel fügt immer absolute Zellreferenzen hinzu, wenn es auf eine Arbeitsmappe verweist, indem es die Zelle(n) darin auswählt. Achten Sie darauf, das Dollarzeichen ($) aus den Zellbezügen zu entfernen, bevor Sie die neu konstruierte Formel in andere Zellen kopieren, damit sie je nach Bedarf für relative oder gemischte Bezüge verwendet werden kann.
• Die beiden Dateien sind wahrscheinlich in unterschiedlichen Instanzen von Excel geöffnet, wenn die Auswahl einer Zelle oder eines Bereichs aus der Arbeitsmappe, auf die verwiesen wird, nicht sofort einen Bezug in der Formel herstellt. Öffnen Sie den Task-Manager und sehen Sie sich die Anzahl der aktiven Microsoft Excel-Instanzen an, um dies zu überprüfen. Erweitern Sie jede Instanz, falls mehrere vorhanden sind, um die dort verschachtelten Dateien anzuzeigen. Schließen Sie die problematische Datei (und Instanz) und öffnen Sie sie aus der anderen Datei, um das Problem zu beheben.
Die Verwendung von Daten aus anderen Arbeitsblättern und Arbeitsmappen in Ihren Berechnungen ist jetzt dank Ihres Wissens über das Erstellen einer externen Referenz in Excel möglich. Ich weiß es zu schätzen, dass Sie lesen, und ich hoffe, Sie werden unsere anderen Artikel lesen.
Benötigen Sie einen VBA Programmierer?
Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).
Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular
