In Microsoft Excel ist es üblich, auf Zellen in anderen Arbeitsblättern oder sogar in anderen Excel-Dateien zu verweisen. Auf den ersten Blick kann dies ein wenig entmutigend und verwirrend erscheinen, aber wenn Sie erst einmal verstanden haben, wie es funktioniert, ist es nicht so schwer.
In diesem Artikel sehen wir uns an, wie Sie auf ein anderes Blatt in derselben Excel-Datei verweisen und wie Sie auf eine andere Excel-Datei verweisen. Wir werden auch Dinge behandeln, wie zum Beispiel, wie man einen Zellbereich in einer Funktion referenziert, wie man die Dinge mit definierten Namen vereinfacht und wie man SVERWEIS für dynamische Referenzen verwendet.
Inhaltsverzeichnis
So verweisen Sie auf ein anderes Blatt in derselben Excel-Datei
Ein grundlegender Zellbezug wird als Spaltenbuchstabe gefolgt von der Zeilennummer geschrieben.
Der Zellbezug B3 bezieht sich also auf die Zelle am Schnittpunkt von Spalte B und Zeile 3.
Wenn auf Zellen in anderen Blättern verwiesen wird, wird dieser Zellreferenz der Name des anderen Blatts vorangestellt. Unten ist beispielsweise ein Verweis auf Zelle B3 in einem Blattnamen „Januar“.
=January!B3
Das Ausrufezeichen (!) trennt den Blattnamen von der Zellenadresse.
Wenn der Blattname Leerzeichen enthält, müssen Sie den Namen in der Referenz in einfache Anführungszeichen einschließen.
='January Sales'!B3
Um diese Referenzen zu erstellen, können Sie sie direkt in die Zelle eingeben. Es ist jedoch einfacher und zuverlässiger, Excel die Referenz für Sie schreiben zu lassen.
Geben Sie ein Gleichheitszeichen (=) in eine Zelle ein, klicken Sie auf die Registerkarte Blatt, und klicken Sie dann auf die Zelle, auf die Sie einen Querverweis erstellen möchten.
Dabei schreibt Excel die Referenz für Sie in die Formelleiste.
Drücken Sie die Eingabetaste, um die Formel zu vervollständigen.
So verweisen Sie auf eine andere Excel-Datei
Sie können mit derselben Methode auf Zellen einer anderen Arbeitsmappe verweisen. Stellen Sie einfach sicher, dass Sie die andere Excel-Datei geöffnet haben, bevor Sie mit der Eingabe der Formel beginnen.
Geben Sie ein Gleichheitszeichen (=) ein, wechseln Sie zu der anderen Datei und klicken Sie dann auf die Zelle in dieser Datei, auf die Sie verweisen möchten. Drücken Sie die Eingabetaste, wenn Sie fertig sind.
Der ausgefüllte Querverweis enthält den anderen Arbeitsmappennamen in eckigen Klammern, gefolgt vom Blattnamen und der Zellennummer.
=[Chicago.xlsx]January!B3
Wenn der Datei- oder Blattname Leerzeichen enthält, müssen Sie den Dateiverweis (einschließlich der eckigen Klammern) in einfache Anführungszeichen setzen.
='[New York.xlsx]January'!B3
In diesem Beispiel sehen Sie Dollarzeichen ($) unter der Zellenadresse. Dies ist ein absoluter Zellbezug (Erfahren Sie mehr über absolute Zellbezüge).
Beim Verweisen auf Zellen und Bereiche in verschiedenen Excel-Dateien werden die Verweise standardmäßig absolut gemacht. Sie können dies bei Bedarf in eine relative Referenz ändern.
Wenn Sie sich die Formel ansehen, wenn die referenzierte Arbeitsmappe geschlossen ist, enthält sie den gesamten Pfad zu dieser Datei.
Obwohl das Erstellen von Verweisen auf andere Arbeitsmappen einfach ist, sind sie anfälliger für Probleme. Benutzer, die Ordner erstellen oder umbenennen und Dateien verschieben, können diese Verweise beschädigen und Fehler verursachen.
Die Speicherung von Daten in einer Arbeitsmappe, wenn möglich, ist zuverlässiger.
So verweisen Sie auf einen Zellbereich in einer Funktion
Es ist nützlich genug, auf eine einzelne Zelle zu verweisen. Möglicherweise möchten Sie jedoch eine Funktion (z. B. SUM) schreiben, die auf einen Zellbereich in einem anderen Arbeitsblatt oder einer anderen Arbeitsmappe verweist.
Starten Sie die Funktion wie gewohnt und klicken Sie dann auf das Blatt und den Zellbereich – genauso wie in den vorherigen Beispielen.
Im folgenden Beispiel summiert eine SUM-Funktion die Werte aus dem Bereich B2:B6 in einem Arbeitsblatt namens Sales.
=SUM(Sales!B2:B6)
So verwenden Sie definierte Namen für einfache Querverweise
In Excel können Sie einer Zelle oder einem Zellbereich einen Namen zuweisen. Dies ist im Rückblick aussagekräftiger als eine Zellen- oder Bereichsadresse. Wenn Sie in Ihrer Tabelle viele Verweise verwenden, können Sie durch Benennen dieser Verweise viel einfacher sehen, was Sie getan haben.
Noch besser, dieser Name ist für alle Arbeitsblätter in dieser Excel-Datei eindeutig.
Zum Beispiel könnten wir eine Zelle „ChicagoTotal“ nennen und dann würde der Querverweis lauten:
=ChicagoTotal
Dies ist eine sinnvollere Alternative zu einer Standardreferenz wie dieser:
=Sales!B2
Es ist einfach, einen definierten Namen zu erstellen. Wählen Sie zunächst die Zelle oder den Zellbereich aus, die Sie benennen möchten.
Klicken Sie in das Namensfeld in der oberen linken Ecke, geben Sie den Namen ein, den Sie zuweisen möchten, und drücken Sie dann die Eingabetaste.
Beim Erstellen definierter Namen dürfen Sie keine Leerzeichen verwenden. Daher wurden in diesem Beispiel die Wörter im Namen verbunden und durch einen Großbuchstaben getrennt. Sie können Wörter auch mit Zeichen wie einem Bindestrich (-) oder Unterstrich (_) trennen.
Excel verfügt auch über einen Namensmanager, der die zukünftige Überwachung dieser Namen erleichtert. Klicken Sie auf Formeln > Namensmanager. Im Fenster Name Manager sehen Sie eine Liste aller definierten Namen in der Arbeitsmappe, wo sie sich befinden und welche Werte sie derzeit speichern.
Mit den Schaltflächen am oberen Rand können Sie diese definierten Namen dann bearbeiten und löschen.
Daten als Tabelle formatieren
Wenn Sie mit einer umfangreichen Liste verwandter Daten arbeiten, kann die Verwendung der Funktion „Als Tabelle formatieren“ von Excel die Art und Weise vereinfachen, wie Sie Daten darin referenzieren.
Nehmen Sie die folgende einfache Tabelle.
Dies könnte als Tabelle formatiert werden.
Klicken Sie auf eine Zelle in der Liste, wechseln Sie zur Registerkarte „Start“, klicken Sie auf die Schaltfläche „Als Tabelle formatieren“ und wählen Sie dann einen Stil aus.
Vergewissern Sie sich, dass der Zellbereich korrekt ist und Ihre Tabelle Überschriften hat.
Im Reiter „Design“ können Sie dann Ihrer Tabelle einen aussagekräftigen Namen zuweisen.
Wenn wir dann die Verkäufe von Chicago summieren müssten, könnten wir auf die Tabelle mit ihrem Namen (von einem beliebigen Blatt) verweisen, gefolgt von einer eckigen Klammer ([) to see a list of the table’s columns.
Select the column by double-clicking it in the list and enter a closing square bracket. The resulting formula would look something like this:
=SUM(Sales[Chicago])
Sie können sehen, wie Tabellen die Referenzierung von Daten für Aggregationsfunktionen wie SUM und AVERAGE einfacher machen können als Standardblattreferenzen.
Diese Tabelle ist zu Demonstrationszwecken klein. Je größer die Tabelle und je mehr Blätter Sie in einer Arbeitsmappe haben, desto mehr Vorteile werden Sie sehen.
So verwenden Sie die SVERWEIS-Funktion für dynamische Referenzen
Die bisher in den Beispielen verwendeten Referenzen wurden alle auf eine bestimmte Zelle oder einen Zellbereich festgelegt. Das ist toll und reicht oft für Ihre Bedürfnisse aus.
Was ist jedoch, wenn sich die Zelle, auf die Sie verweisen, möglicherweise ändert, wenn neue Zeilen eingefügt werden, oder einige?