Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

 

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

In Microsoft Excel ist es eine häufige Aufgabe, auf Zellen in anderen Arbeitsblättern oder sogar in anderen Excel-Dateien zu verweisen. Dies kann zunächst ein wenig entmutigend und verwirrend wirken, aber wenn Sie erst einmal verstanden haben, wie es funktioniert, ist es nicht mehr so ​​schwer.

In diesem Artikel wird erläutert, wie auf ein anderes Blatt in derselben Excel-Datei und auf eine andere Excel-Datei verwiesen wird. Wir werden uns auch mit Themen wie dem Verweisen auf einen Zellbereich in einer Funktion, dem Vereinfachen von Aufgaben mit definierten Namen und der Verwendung von VLOOKUP für dynamische Verweise befassen.

Verweisen auf ein anderes Blatt in derselben Excel-Datei

Ein grundlegender Zellverweis wird als Spaltenbuchstabe gefolgt von der Zeilennummer geschrieben.

Die Zellreferenz 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 das andere Blatt vorangestellt

=January!B3

Das Ausrufezeichen (!) ist beispielsweise unten ein Verweis auf Zelle B3 in einem Blattnamen.

=January!B3

trennt den Blattnamen von der Zellenadresse.

Wenn der Blattname Leerzeichen enthält, müssen Sie den Namen im Verweis in einfache Anführungszeichen setzen.

='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 dann auf die Zelle, auf die Sie verweisen möchten.

Dabei schreibt Excel die Referenz für Sie in die Formelleiste.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Drücken Sie die Eingabetaste, um die Formel zu vervollständigen.

Verweisen auf eine andere Excel-Datei

Mit derselben Methode können Sie auf Zellen einer anderen Arbeitsmappe verweisen. Stellen Sie lediglich sicher, dass die andere Excel-Datei geöffnet ist, bevor Sie mit der Eingabe der Formel beginnen.

Geben Sie ein Gleichheitszeichen (=) ein, wechseln Sie zur anderen Datei und klicken Sie dann auf die Zelle in der Datei, auf die Sie verweisen möchten. Drücken Sie die Eingabetaste, wenn Sie fertig sind.

Der ausgefüllte Querverweis enthält den Namen der anderen Arbeitsmappe 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 einzelne Zeichen setzen Anführungszeichen.

='[New York.xlsx]January'!B3

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

In diesem Beispiel sehen Sie Dollarzeichen ($) unter der Zellenadresse. Dies ist eine absolute Zellreferenz (Erfahren Sie mehr über absolute Zellreferenzen).

Wenn Sie auf Zellen und Bereiche in verschiedenen Excel-Dateien verweisen, werden die Verweise standardmäßig als absolut festgelegt. Sie können dies bei Bedarf in einen relativen Verweis ändern.

Wenn Sie sich die Formel ansehen, während die Arbeitsmappe, auf die verwiesen wird, geschlossen ist, enthält sie den gesamten Pfad zu dieser Datei.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Das Erstellen von Verweisen auf andere Arbeitsmappen ist zwar unkompliziert, jedoch anfälliger für Probleme. Benutzer, die Ordner erstellen oder umbenennen und Dateien verschieben, können diese Verweise beschädigen und Fehler verursachen.

Wenn möglich, ist es zuverlässiger, Daten in einer Arbeitsmappe zu speichern.

Querverweisen auf einen Zellbereich in einer Funktion

Das Referenzieren einer einzelnen Zelle ist ausreichend nützlich. Möglicherweise möchten Sie jedoch eine Funktion (z. B. SUMME) 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 Zellenbereich — wie in den vorherigen Beispielen.

Im folgenden Beispiel summiert eine SUM-Funktion die Werte aus dem Bereich B2: B6 in einem Arbeitsblatt mit dem Namen Sales.

=SUM(Sales!B2:B6)

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Verwenden definierter Namen für einfache Querverweise

In Excel können Sie einer Zelle oder einem Zellbereich einen Namen zuweisen. Dies ist sinnvoller als eine Zellen- oder Bereichsadresse, wenn Sie sich diese ansehen. Wenn Sie in Ihrer Tabelle viele Verweise verwenden, können Sie durch Benennen dieser Verweise leichter erkennen, was Sie getan haben.

Dieser Name ist sogar für alle Arbeitsblätter in dieser Excel-Datei eindeutig.

Zum Beispiel könnten wir eine Zelle ‘ ChicagoTotal ’ 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. Beginnen Sie mit der Auswahl der Zelle oderZellenbereich, den 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 die Eingabetaste.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Beim Erstellen definierter Namen dürfen keine Leerzeichen verwendet werden. Daher wurden in diesem Beispiel die Wörter im Namen zusammengefügt und durch einen Großbuchstaben getrennt. Sie können Wörter auch durch Zeichen wie einen Bindestrich (-) oder einen Unterstrich (_) trennen.

Excel verfügt auch über einen Namensmanager, mit dem Sie diese Namen in Zukunft einfach überwachen können.   Klicken Sie auf Formeln > Name Manager.   Im Fenster Name Manager sehen Sie eine Liste aller definierten Namen in der Arbeitsmappe, wo sie sich befinden und welche Werte sie aktuell speichern.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Mit den Schaltflächen oben können Sie diese definierten Namen bearbeiten und löschen.

Formatieren von Daten als Tabelle

Wenn Sie mit einer umfangreichen Liste verwandter Daten arbeiten, können Sie mit der Funktion Als Tabelle formatieren in Excel die Art und Weise vereinfachen, in der Sie auf Daten verweisen.

Nehmen Sie die folgende einfache Tabelle.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Dies kann als Tabelle formatiert werden.

Klicken Sie auf eine Zelle in der Liste und wechseln Sie zum “ Start ” Klicken Sie auf die Registerkarte “ Als Tabelle formatieren ” Klicken Sie auf die Schaltfläche und wählen Sie dann einen Stil aus.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Vergewissern Sie sich, dass der Zellenbereich korrekt ist und Ihre Tabelle Überschriften enthält.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Anschließend können Sie Ihrer Tabelle im “ Design ” Tab.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Wenn wir dann die Verkäufe von Chicago summieren müssten, könnten wir uns auf die Tabelle mit ihrem Namen beziehen (von jedem Blatt), gefolgt Durch eine eckige Klammer ([) wird eine Liste der Tabellenspalten angezeigt.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Wählen Sie die Spalte durch Doppelklicken in der Liste aus und geben Sie eine schließende eckige Klammer ein. Die resultierende Formel würde ungefähr so ​​aussehen:

=SUM(Sales[Chicago])

Sie können sehen, wie Tabellen das Referenzieren von Daten für Aggregationsfunktionen wie SUMME und DURCHSCHNITT einfacher machen 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 sehen Sie.

Verwenden der VLOOKUP-Funktion für dynamische Verweise

Die bisher in den Beispielen verwendeten Verweise wurden alle auf eine bestimmte Zelle oder einen bestimmten Zellbereich festgelegt und ist oft genug für Ihre Bedürfnisse.

Was ist jedoch, wenn sich die Zelle, auf die Sie verweisen, möglicherweise ändert, wenn neue Zeilen eingefügt werden, oder wenn jemand die Liste sortiert?

In diesen Szenarien können Sie den Wert nicht garantieren Sie möchten, befinden sich weiterhin in derselben Zelle, auf die Sie ursprünglich verwiesen haben.

Eine Alternative in diesen Szenarien ist die Verwendung einer Suchfunktion in Excel, um nach dem Wert in einer Liste zu suchen. Dies macht es haltbarer gegen Änderungen an der Platte.

Im folgenden Beispiel verwenden wir die VLOOKUP-Funktion, um einen Mitarbeiter anhand seiner Mitarbeiter-ID auf einem anderen Blatt nachzuschlagen und dann sein Startdatum zurückzugeben.

Nachfolgend finden Sie eine beispielhafte Liste der Mitarbeiter.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Die Funktion VLOOKUP durchsucht die erste Spalte einer Tabelle und gibt dann Informationen aus einer angegebenen Spalte nach rechts zurück.

Die folgende VLOOKUP-Funktion sucht nach der in Zelle A2 in der oben gezeigten Liste eingegebenen Mitarbeiter-ID und gibt das von Spalte 4 (vierte Spalte der Tabelle) verbundene Datum zurück.

=VLOOKUP(A2,Employees!A:E,4,FALSE)

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Im Folgenden wird veranschaulicht, wie Diese Formel durchsucht die Liste und gibt die richtigen Informationen zurück.

Querverweis von Zellen zwischen Microsoft Excel-Arbeitsblättern

Das Tolle an diesem VLOOKUP gegenüber den vorherigen Beispielen ist, dass der Mitarbeiter auch dann gefunden wird, wenn sich die Liste in der Reihenfolge ändert.

Hinweis: VLOOKUP ist eine unglaublich nützliche Formel, und wir haben in diesem Artikel nur die Oberfläche seines Werts zerkratzt. Weitere Informationen zur Verwendung von VLOOKUP finden Sie in unserem Artikel zu diesem Thema. In diesem Artikel über Computergaga (ebenfalls von Alan Murray geschrieben) erfahren Sie noch mehr Tricks für die Verwendung.

In diesem Artikel haben wir uns mit verschiedenen Querverweisen zwischen Excel-Arbeitsblättern und Arbeitsmappen befasst. Wählen Sie den Ansatz, der für Ihre jeweilige Aufgabe geeignet ist und mit dem Sie problemlos arbeiten können.

body #primary .entry-content ul # nextuplist {Listentyp: keine; Rand-links: 0px; Abstand-links: 0px;} body #primary .entry-content ul # nextuplist li a{Textdekoration: keine; Farbe: # 1d55a9;} WEITERLESEN

  • › So sichern Sie Ihr Linux-System
  • › Warum verwendet Windows weiterhin Buchstaben für Laufwerke?
  • › Zoom Ermöglicht es Websites, Sie ohne Ihre Zustimmung zu filmen, auch unter Windows
  • › So überprüfen Sie, ob Zoom einen geheimen Webserver auf Ihrem Mac ausführt (und entfernen Sie ihn)
  • › Herunterladen einer Windows 10-ISO-Datei ohne das Media Creation Tool

q , quelle

Zusammenhängende Posts:

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.