So verweisen Sie auf ein anderes Blatt oder eine Arbeitsmappe in Excel (mit Beispielen)

In der Lage zu sein, auf Zellen und Bereiche zu verweisen, macht jedes Tabellenkalkulationstool zum Funktionieren. Und Excel ist das beste und leistungsstärkste auf dem Markt.

In diesem Tutorial werde ich alles behandeln, was Sie wissen müssen, um Zellen und Bereiche in Excel zu referenzieren. Abgesehen von der grundlegenden Referenzierung auf demselben Blatt dreht sich der größte Teil dieses Tutorials um So verweisen Sie auf ein anderes Blatt oder eine Arbeitsmappe in Excel.

Obwohl es keinen großen Unterschied in der Funktionsweise gibt, ändert sich das Format dieser Referenz ein wenig, wenn Sie auf ein anderes Blatt in derselben Datei verweisen oder auf eine völlig separate Excel-Datei verweisen.

Außerdem müssen Sie einige wichtige Dinge beachten, wenn Sie auf ein anderes Blatt oder andere externe Dateien verweisen.

Aber keine Sorge… nichts zu verrücktes!

Wenn Sie mit diesem Tutorial fertig sind, wissen Sie alles, was Sie über das Referenzieren von Zellen und Bereichen in Excel wissen müssen (in derselben Arbeitsmappe oder einer anderen Arbeitsmappe).

Lass uns anfangen!

Auf eine Zelle im selben Blatt verweisen

Dies ist die grundlegendste Referenzierungsebene, bei der Sie auf eine Zelle auf demselben Blatt verweisen.

Wenn ich mich beispielsweise in Zelle B1 befinde und auf Zelle A1 verweisen möchte, lautet das Format:

=A1

Wenn Sie dies tun, ist der Wert in der Zelle, in der Sie diesen Verweis verwenden, derselbe wie in Zelle A1. Und falls Sie in Zelle A1 Änderungen vornehmen, werden diese in der Zelle widergespiegelt, in der Sie diese Referenz verwendet haben.

Auf eine Zelle in einem anderen Blatt verweisen

Wenn Sie auf ein anderes Blatt in derselben Arbeitsmappe verweisen müssen, müssen Sie das folgende Format verwenden:

Tabellenname!Zellenadresse

Zuerst haben Sie den Blattnamen, gefolgt von einem Ausrufezeichen, dem der Zellbezug folgt.

Wenn Sie also auf Zelle A1 in Blatt 1 verweisen müssen, müssen Sie die folgende Referenz verwenden:

=Blatt1!A1

Und wenn Sie auf einen Zellbereich in einem anderen Blatt verweisen möchten, müssen Sie das folgende Format verwenden:

Sheet_name!First_cell:Last_cell

Wenn Sie also auf den Bereich A1: C10 in einem anderen Blatt derselben Arbeitsmappe verweisen möchten, müssen Sie die folgende Referenz verwenden:

=Blatt1!A1:C10

Beachten Sie, dass ich Ihnen nur den Verweis auf die Zelle oder den Bereich gezeigt habe. In Wirklichkeit würden Sie diese in Formeln verwenden. Aber das Format der oben genannten Referenzen wird gleich bleiben

In vielen Fällen enthält das Arbeitsblatt, auf das Sie sich beziehen, mehrere Wörter im Namen. Es könnte zum Beispiel sein Projektdaten oder Verkaufsdaten.

Falls Sie Leerzeichen oder nicht-alphabetische Zeichen (wie @, !, #, - usw.) verwenden, müssen Sie den Namen in einfache Anführungszeichen setzen.

Wenn Sie beispielsweise auf Zelle A1 im Blatt mit dem Namen Verkaufsdaten verweisen möchten, verwenden Sie die folgende Referenz:

='Verkaufsdaten'!A1

Und falls der Name des Blattes Sales-Data ist, müssen Sie die folgende Referenz verwenden, um auf Zelle A1 in diesem Blatt zu verweisen:

='Verkaufsdaten'!A1

Wenn Sie auf ein Blatt in derselben Arbeitsmappe verweisen und später den Namen des Arbeitsblatts ändern, müssen Sie sich keine Sorgen machen, dass die Referenz aufbricht. Excel aktualisiert diese Referenzen automatisch für Sie.

Obwohl es gut ist, das Format dieser Verweise zu kennen, ist es in der Praxis keine gute Idee, diese jedes Mal manuell einzugeben. Das wäre zeitaufwendig und sehr fehleranfällig.

Lassen Sie mich Ihnen eine bessere Möglichkeit zeigen, Zellbezüge in Excel zu erstellen.

Automatisches Erstellen eines Verweises auf ein anderes Blatt in derselben Arbeitsmappe

Eine viel bessere Möglichkeit, einen Zellbezug auf ein anderes Blatt zu erstellen, besteht darin, Excel einfach auf die Zelle / den Bereich zu verweisen, auf die Sie den Bezug erstellen möchten, und Excel selbst erstellen zu lassen.

Dies stellt sicher, dass Sie sich keine Sorgen machen müssen, dass das Ausrufezeichen oder Anführungszeichen fehlen oder andere Formatprobleme auftauchen. Excel erstellt automatisch die richtige Referenz für Sie.

Im Folgenden sind die Schritte aufgeführt, um automatisch einen Verweis auf ein anderes Blatt zu erstellen:

1. Wählen Sie die Zelle in der aktuellen Arbeitsmappe aus, in der Sie die Referenz benötigen
2. Geben Sie die Formel ein, bis Sie die Referenz benötigen (oder ein Gleichheitszeichen, wenn Sie nur die Referenz wünschen)
3. Wählen Sie das Blatt aus, auf das Sie sich beziehen möchten
4. Wählen Sie die Zelle/den Bereich aus, auf den Sie verweisen möchten
5. Drücken Sie die Eingabetaste, um das Ergebnis der Formel zu erhalten (oder arbeiten Sie weiter an der Formel)

Die obigen Schritte würden automatisch einen Verweis auf die Zelle/den Bereich in einem anderen Blatt erstellen. Sie können diese Referenzen auch in der Bearbeitungsleiste sehen. Sobald Sie fertig sind, können Sie einfach die Eingabetaste drücken und Sie erhalten das Ergebnis.

Wenn Sie beispielsweise einige Daten in Zelle A1:A10 in einem Blatt mit dem Namen Verkaufsdaten haben und die Summe dieser Werte im aktuellen Blatt abrufen möchten, gehen Sie wie folgt vor:

1. Geben Sie die folgende Formel in das aktuelle Blatt ein (wo Sie das Ergebnis benötigen): =Sum(
2. Wählen Sie das Blatt „Verkaufsdaten“ aus.
3. Wählen Sie den Bereich aus, den Sie hinzufügen möchten (A1:A10). Sobald Sie dies tun, erstellt Excel automatisch einen Verweis auf diesen Bereich (Sie können das in der Bearbeitungsleiste sehen)
4. Drücken Sie die Eingabetaste.

Wenn Sie eine lange Formel erstellen, müssen Sie möglicherweise auf eine Zelle oder einen Bereich in einem anderen Blatt verweisen und müssen dann zum Ursprungsblatt zurückkehren und dort auf eine Zelle/einen Bereich verweisen.

Wenn Sie dies tun, werden Sie feststellen, dass Excel automatisch einen Blattverweis auf das Blatt einfügt, auf dem sich die Formel befindet. Das ist zwar in Ordnung und schadet nicht, aber es ist nicht nötig. In einem solchen Fall können Sie die Referenz beibehalten oder manuell entfernen.

Eine andere Sache, die Sie wissen müssen, wenn Sie Referenzen erstellen, indem Sie das Blatt und dann die Zelle / den Bereich auswählen, ist, dass Excel immer ein erstellt relativer Bezug (d. h. Referenzen mit n0 $-Zeichen). Dies bedeutet, dass, wenn ich die Formel (eine mit Bezug auf ein anderes Blatt) in eine andere Zelle kopiere und einfüge, die Referenz automatisch angepasst wird.

Hier ist ein Beispiel, das relative Verweise erklärt.

Angenommen, ich verwende die folgende Formel in Zelle A1 im aktuellen Blatt (um auf Zelle A1 in einem Blattnamen SalesData zu verweisen)

=Verkaufsdaten!A1

Wenn ich nun diese Formel kopiere und in Zelle A2 einfüge, ändert sich die Formel in:

=Verkaufsdaten!A1

Dies geschieht, weil die Formel relativ ist und wenn ich sie kopiere und einfüge, werden die Referenzen automatisch angepasst.

Falls ich möchte, dass sich dieser Verweis immer auf die Zelle A1 im SalesData-Blatt bezieht, muss ich die folgende Formel verwenden:

=Verkaufsdaten!$A$1

Das Dollarzeichen vor der Zeilen- und Spaltennummer sperrt diese Referenzen, damit sie sich nicht ändern.

Hier ist ein detailliertes Tutorial, in dem Sie mehr über absolute, gemischte und relative Referenzen erfahren können.

Nachdem wir nun behandelt haben, wie Sie auf ein anderes Blatt in derselben Arbeitsmappe verweisen, sehen wir uns an, wie wir auf eine andere Arbeitsmappe verweisen können.

So verweisen Sie auf eine andere Arbeitsmappe in Excel

Wenn Sie auf eine Zelle oder einen Bereich auf eine andere Excel-Arbeitsmappe verweisen, hängt das Format dieses Verweises davon ab, ob diese Arbeitsmappe geöffnet oder geschlossen ist.

Und natürlich spielen auch der Name der Arbeitsmappe und der Arbeitsblätter eine Rolle beim Bestimmen des Formats (je nachdem, ob Sie Leerzeichen oder nicht-alphabetische Zeichen im Namen haben oder nicht).

Sehen wir uns also die unterschiedlichen Formate externer Verweise auf eine andere Arbeitsmappe in verschiedenen Szenarien an.

Externer Verweis auf eine geöffnete Arbeitsmappe

Wenn Sie auf eine externe geöffnete Arbeitsmappe verweisen, müssen Sie den Arbeitsmappennamen, den Arbeitsblattnamen und die Zellen-/Bereichsadresse angeben.

Im Folgenden finden Sie das Format, das Sie verwenden müssen, wenn Sie auf eine externe geöffnete Arbeitsmappe verweisen

='[Dateiname]Tabellenname!Zellenadresse

Angenommen, Sie haben eine Arbeitsmappe „ExampleFile.xlsx“ und möchten auf Zelle A1 in Sheet1 dieser Arbeitsmappe verweisen.

Nachfolgend die Referenz dazu:

=[BeispielDatei.xlsx]Verkaufsdaten!A1

Falls der Name der externen Arbeitsmappe oder der Blattname (oder beides) Leerzeichen enthält, müssen Sie den Dateinamen (in eckigen Klammern) und den Blattnamen in einfache Anführungszeichen setzen.

Unten sind die Beispiele, bei denen Sie die Namen in einfachen Anführungszeichen setzen müssen:

='[Beispieldatei.xlsx]SalesData'!A1 ='[Beispieldatei.xlsx]Umsatzdaten'!A1 ='[Beispieldatei.xlsx]Umsatzdaten'!A1

So erstellen Sie einen Verweis auf eine andere Arbeitsmappe (automatisch)

Auch wenn es gut ist, das Format zu kennen, ist es am besten, es nicht manuell einzugeben.

Zeigen Sie Excel stattdessen einfach in die richtige Richtung und es erstellt diese Referenzen für Sie. Dies ist viel schneller mit viel weniger Fehlermöglichkeiten.

Wenn Sie beispielsweise einige Daten in Zelle A1: A10 in einer Arbeitsmappe mit dem Namen "Beispieldatei" im Blatt mit dem Namen "Verkaufsdaten" haben und die Summe dieser Werte im aktuellen Blatt abrufen möchten, folgen Sie den folgenden Schritten :

1. Geben Sie die folgende Formel in das aktuelle Blatt ein (wo Sie das Ergebnis benötigen): =Sum(
2. Gehen Sie zur Arbeitsmappe „Beispieldatei“
3. Wählen Sie das Blatt „Verkaufsdaten“ aus.
4. Wählen Sie den Bereich aus, den Sie hinzufügen möchten (A1:A10). Sobald Sie dies tun, erstellt Excel automatisch einen Verweis auf diesen Bereich (Sie können das in der Bearbeitungsleiste sehen)
5. Drücken Sie die Eingabetaste.

Dies würde sofort die Formel mit den richtigen Referenzen erstellen.

Beim Erstellen eines Verweises auf eine externe Arbeitsmappe würden Sie feststellen, dass immer absolute Verweise erstellt werden. Das bedeutet, dass vor den Zeilen- und Spaltennummern ein $-Zeichen steht. Dies bedeutet, dass beim Kopieren und Einfügen dieser Formel in andere Zellen aufgrund des absoluten Bezugs weiterhin auf denselben Bereich verwiesen wird.

Falls Sie dies ändern möchten, müssen Sie die Referenzen manuell ändern.

Externer Verweis auf eine geschlossene Arbeitsmappe

Wenn eine externe Arbeitsmappe geöffnet ist und Sie auf diese Arbeitsmappe verweisen, müssen Sie nur den Dateinamen, den Blattnamen und die Zellen-/Bereichsadresse angeben.

Aber wenn dies geschlossen ist, hat Excel keine Ahnung, wo Sie nach den Zellen/Bereichen suchen, auf die Sie sich bezogen haben.

Aus diesem Grund müssen Sie beim Erstellen eines Verweises auf eine geschlossene Arbeitsmappe auch den Dateipfad angeben.

Unten ist ein Verweis, der auf Zelle A1 im Arbeitsblatt Sheet1 in der Arbeitsmappe Beispieldatei verweist. Da diese Datei nicht geöffnet ist, verweist sie auch auf den Speicherort der Datei.

='C:\Benutzer\sumit\Desktop\[Beispieldatei.xlsx]Sheet1'!$A$1

Die obige Referenz hat die folgenden Teile:

• Dateipfad – der Speicherort auf Ihrem System oder Netzwerk, an dem sich die externe Datei befindet
• Dateiname - der Name der externen Arbeitsmappe. Dazu gehört auch die Dateierweiterung.
• Blattname - der Name des Blatts, in dem Sie sich auf die Zellen/Bereiche beziehen
• Zellen-/Bereichsadresse – die genaue Zellen-/Bereichsadresse, auf die Sie sich beziehen

Wenn Sie einen externen Verweis auf eine geöffnete Arbeitsmappe erstellen und die Arbeitsmappe dann schließen, werden Sie feststellen, dass sich der Verweis automatisch ändert. Nachdem die externe Arbeitsmappe geschlossen wurde, fügt Excel automatisch auch einen Verweis auf den Dateipfad ein.

Auswirkungen der Änderung des Dateispeicherorts auf Referenzen

Wenn Sie eine Referenz auf eine Zelle/einen Bereich in einer externen Excel-Datei erstellen und diese dann schließen, verwendet die Referenz jetzt auch den Dateipfad.

Wenn Sie dann jedoch den Dateispeicherort ändern, ändert sich nichts in Ihrer Arbeitsmappe (in der Sie die Referenz erstellen). Aber da Sie den Standort geändert haben, ist der Link jetzt unterbrochen.

Wenn Sie also diese Arbeitsmappe schließen und öffnen, wird Ihnen mitgeteilt, dass der Link unterbrochen ist und Sie den Link entweder aktualisieren oder vollständig unterbrechen müssen. Es zeigt Ihnen eine Eingabeaufforderung wie unten gezeigt:

Wenn Sie auf Aktualisieren klicken, wird eine weitere Eingabeaufforderung angezeigt, in der Sie die Optionen zum Bearbeiten der Links auswählen können (die Ihnen das folgende Dialogfeld anzeigen).

Wenn Sie diese Dateien verknüpft halten müssen, können Sie den neuen Speicherort der Datei angeben, indem Sie auf Werte aktualisieren klicken. Excel öffnet ein Dialogfeld für Sie, in dem Sie den neuen Dateispeicherort angeben können, indem Sie dorthin navigieren und ihn auswählen.

Verweis auf einen definierten Namen (in derselben oder einer externen Arbeitsmappe)

Wenn Sie auf Zellen und Bereiche verweisen müssen, ist es besser, definierte Namen für die Bereiche zu erstellen.

Dies ist hilfreich, da Sie auf diese Bereiche einfach mit einem Namen anstelle einer langen und komplizierten Referenzadresse verweisen können.

Beispielsweise ist es einfacher, =SalesData statt =[Example File.xlsx]Sheet1′!$A$1:$A$10 . zu verwenden

Und falls Sie diesen definierten Namen in mehreren Formeln verwendet haben und die Referenz ändern müssen, müssen Sie dies nur einmal tun.

Hier sind die Schritte zum Erstellen eines benannten Bereichs für einen Zellbereich:

1. Wählen Sie alle Zellen aus, die Sie in den benannten Bereich einschließen möchten
2. Klicken Sie auf die Registerkarte Formeln
3. Klicken Sie auf die Option Namen definieren (sie befindet sich in der Gruppe Definierte Namen).
4. Geben Sie diesem Bereich im Dialogfeld Neuer Name einen Namen (in diesem Beispiel verwende ich den Namen SalesData). Denken Sie daran, dass der Name keine Leerzeichen enthalten darf
5. Behalten Sie den Umfang als Arbeitsmappe bei (es sei denn, Sie haben einen triftigen Grund, ihn auf Blattebene zu erstellen)
6. Stellen Sie sicher, dass der Verweis auf den Bereich korrekt ist.
7. OK klicken.

Jetzt wurde Ihr benannter Bereich erstellt und Sie können ihn anstelle der Zellbezüge mit Zelladressen verwenden.

Wenn ich beispielsweise die Summe all dieser Zellen im SalesData-Bereich erhalten möchte, können Sie die folgende Formel verwenden:

=SUMME(Verkaufsdaten)

Und was ist, wenn Sie diesen benannten Bereich für andere Arbeitsblätter oder sogar andere Arbeitsmappen verwenden möchten?

Du kannst!

Sie müssen nur das gleiche Format befolgen, das wir im obigen Abschnitt besprochen haben.

Sie müssen nicht zum Anfang dieses Artikels zurückkehren. Lassen Sie mich Ihnen alle Beispiele hier selbst geben, damit Sie die Idee bekommen.

Benannte Bereiche auf Arbeitsmappe und Arbeitsblattebene: Wenn Sie einen benannten Bereich auf Arbeitsmappenebene erstellen, kann es nur einen Bereich mit diesem Namen geben. Wenn Sie also von externen Arbeitsmappen oder Arbeitsblättern in derselben Arbeitsmappe darauf verweisen, müssen Sie den Arbeitsblattnamen nicht angeben (da er in der gesamten Arbeitsmappe verwendet werden kann). Falls Sie den benannten Bereich auf Arbeitsblattebene verwenden, können Sie ihn nur im Arbeitsblatt verwenden, und während Sie darauf verweisen, müssen Sie auch den Arbeitsblattnamen angeben.

Verweisen auf den definierten Namen in demselben Arbeitsblatt oder derselben Arbeitsmappe

Wenn Sie den definierten Namen für die Arbeitsmappenebene erstellt haben, können Sie ihn überall in der Arbeitsmappe verwenden, indem Sie einfach den definierten Namen selbst verwenden.

Wenn ich beispielsweise die Summe aller Zellen in dem von uns erstellten benannten Bereich (SaledData) erhalten möchte, kann ich die folgende Formel verwenden:

=SUMME(SaledData)

Falls Sie einen benannten Bereich auf Arbeitsblattebene erstellt haben, können Sie diese Formel nur verwenden, wenn der benannte Bereich in demselben Blatt erstellt wird, in dem Sie die Formel verwenden.

Falls Sie es auf einem anderen Blatt verwenden möchten (z. B. Blatt2), müssen Sie die folgende Formel verwenden:

=SUMME(Blatt1!$A$1:$A$10)

Und falls der Blattname Leerzeichen oder alphanumerische Zeichen enthält, müssen Sie den Blattnamen in einfache Anführungszeichen setzen.

=SUMME('Blatt 1'!$A$1:$A$10)

Verweisen auf den definierten Namen in einer anderen Arbeitsmappe (offen oder geschlossen)

Wenn Sie auf einen benannten Bereich in einer anderen Arbeitsmappe verweisen möchten, müssen Sie den Namen der Arbeitsmappe und dann den Namen des Bereichs angeben.

Wenn Sie beispielsweise eine Excel-Arbeitsmappe mit dem Namen ExampleFile.xlsx und einen benannten Bereich mit dem Namen SalesData haben, können Sie die folgende Formel verwenden, um die Summe dieses Bereichs aus einer anderen Arbeitsmappe abzurufen:

=SUMME(BeispielDatei.xlsx!SalesData)

Falls der Dateiname Leerzeichen enthält, müssen Sie diese in einfache Anführungszeichen setzen.

=SUMME('Beispieldatei.xlsx'!SalesData)

Falls Sie benannte Bereiche auf Blattebene haben, müssen Sie den Namen der Arbeitsmappe sowie des Arbeitsblatts angeben, wenn Sie von einer externen Arbeitsmappe darauf verweisen.

Unten sehen Sie ein Beispiel für den Verweis auf einen benannten Bereich auf Blattebene:

=SUMME('[Beispieldatei.xlsx]Sheet1'!SalesData)

Wie bereits erwähnt, ist es immer am besten, benannte Bereiche auf Arbeitsmappenebene zu erstellen, es sei denn, Sie haben einen triftigen Grund, einen auf Arbeitsblattebene zu erstellen.

Falls Sie in einer geschlossenen Arbeitsmappe auf einen benannten Bereich verweisen, müssen Sie auch den Dateipfad angeben. Unten ist ein Beispiel dafür:

=SUMME('C:\Benutzer\sumit\Desktop\Beispieldatei.xlsx'!SalesData)

Wenn Sie in einer geöffneten Arbeitsmappe einen Verweis auf einen benannten Bereich erstellen und dann die Arbeitsmappe schließen, ändert Excel automatisch den Verweis und fügt den Dateipfad hinzu.

So erstellen Sie eine Referenz auf einen benannten Bereich

Wenn Sie viele benannte Bereiche erstellen und damit arbeiten, können Sie sich nicht jeden Namen merken.

Excel hilft Ihnen, indem es Ihnen eine Liste aller von Ihnen erstellten benannten Bereiche anzeigt und Sie diese mit einem einzigen Klick in Formeln einfügen können.

Angenommen, Sie haben einen benannten Bereich SalesData erstellt, den Sie in einer Formel verwenden möchten, um alle Werte im benannten Bereich zu summieren.

Hier sind die Schritte, um dies zu tun:

1. Wählen Sie die Zelle aus, in die Sie die Formel eingeben möchten.
2. Geben Sie die Formel bis zu der Stelle ein, an der der benannte Bereich eingefügt werden soll
3. Drücken Sie die F3-Taste auf Ihrer Tastatur. Dadurch wird das Dialogfeld Name einfügen mit der Liste aller von Ihnen erstellten Namen geöffnet
4. Doppelklicken Sie auf den Namen, den Sie einfügen möchten.

Die obigen Schritte würden den Namen in die Formel einfügen und Sie können weiter an der Formel arbeiten.

Hinweis: Diese Technik funktioniert nur für die Namensbereiche, die sich in derselben Arbeitsmappe befinden. Wenn Sie die Namen in einer externen Arbeitsmappe abrufen möchten, müssen Sie dorthin gehen, den Namen im Dialogfeld „Namen einfügen“ überprüfen und dann zurückkehren und ihn in Ihrer Formel verwenden (im richtigen Format mit dem Dateinamen).

Dies ist alles, was Sie wissen müssen, um auf andere Blätter oder Arbeitsmappen zu verweisen und eine externe Referenz in Excel zu erstellen.

Ich hoffe, Sie fanden dieses Tutorial nützlich.