SVERWEIS zwischen zwei Arbeitsmappen: Einfache Schritt-für-Schritt-Anleitung

Wenn Sie in Excel mit mehreren Arbeitsmappen arbeiten, müssen Sie möglicherweise die SVERWEIS-Funktion verwenden, um Daten von einer Arbeitsmappe in eine andere abzurufen.

Die VLOOKUP-Funktion sucht nach einem bestimmten Wert in einer Tabelle in einer separaten Arbeitsmappe und gibt einen entsprechenden Wert aus derselben Zeile zurück. Die Syntax muss einen vollständig qualifizierten Verweis auf den zweiten Arbeitsmappen- und Arbeitsblattnamen enthalten.

Wenn Sie die Funktion zum ersten Mal verwenden, kann es schwierig sein, die richtigen Referenzen zu finden. In diesem Artikel werden Beispieltabellen verwendet, um Ihnen genau zu zeigen, wie Sie die Formeln eingeben.

Außerdem lernen Sie einige Verbesserungen von VLOOKUP und verschiedene Alternativen zu der Funktion kennen.

Lass uns anfangen!

Inhaltsverzeichnis

• Was ist SVERWEIS?
• So verwenden Sie SVERWEIS zwischen zwei Arbeitsmappen
  • Beispieldaten
  • Schritt 1: Lassen Sie beide Arbeitsmappen geöffnet
  • Schritt 2: Beginnen Sie mit der Eingabe der SVERWEIS-Formel
  • Schritt 3: So referenzieren Sie die zweite Arbeitsmappe
• 3 häufige VLOOKUP-Fehler und wie man sie behebt
  • 1. #N/A-Fehler
  • 2. #Ref! Fehler bei der Spaltennummer
  • 3. Fehler in Anführungszeichen und eckigen Klammern
• 4 Tipps zum Verwalten verschiedener Arbeitsmappen
• So aktualisieren Sie Daten automatisch
• Erweiterte Verwendungsmöglichkeiten der VLOOKUP-Funktion
• 2 Hauptalternativen zu VLOOKUP
  • 1. INDEX-MATCH
  • 2. XLOOKUP
• So verwenden Sie VLOOKUP über mehrere Blätter hinweg
  • Schritt 1: Identifizieren Sie die Komponenten
  • Schritt 2: Richten Sie Ihre Daten ein
  • Schritt 3: Verwenden Sie die SVERWEIS-Funktion
  • (Optional) Schritt 4: #N/A-Fehler beheben
• Abschließende Gedanken

Was ist SVERWEIS?

Angenommen, Sie haben eine Arbeitsmappe mit Produktverkäufen und eine separate Arbeitsmappe mit den Preisen für jedes Produkt. Sie möchten auf die Preise aus der zweiten Arbeitsmappe zugreifen und diese in Berechnungen in der ersten Arbeitsmappe verwenden.

VLOOKUP ist eine Lösung für diese Aufgabe. Der Begriff steht für „Vertical Lookup“ . „

Es handelt sich um eine integrierte Funktion, mit der Sie nach einem bestimmten Wert in einer Spalte suchen und basierend auf der Position einen entsprechenden Wert in einer anderen Spalte zurückgeben können.

Es kann verwendet werden mit:

• eine einzige Tabelle

• mehrere Blätter in derselben Arbeitsmappe

• zwei Excel-Arbeitsmappen

Wenn Sie nicht über die zusätzliche Komplexität mehrerer Arbeitsmappen verfügen, sehen Sie sich dieses Tutorial zum Vergleichen zweier Spalten in Excel mithilfe von VLOOKUP an .

So verwenden Sie SVERWEIS zwischen zwei Arbeitsmappen

Die SVERWEIS-Funktion ist besonders nützlich, wenn Sie in mehreren Arbeitsblättern oder Arbeitsmappen nach Daten suchen müssen.

Die drei wichtigsten Schritte dazu sind:

1. Öffnen Sie beide Arbeitsmappen.

2. Geben Sie die SVERWEIS-Formel in die erste Excel-Arbeitsmappe ein.

3. Geben Sie einen vollqualifizierten Verweis auf das zweite Arbeitsbuch an.

Beispieldaten

In diesem einfachen Tutorial werden Beispiele anhand von Beispieldaten durchgearbeitet. Erstellen Sie dazu eine neue Arbeitsmappe mit dem Namen „Verkäufe“ und geben Sie diese Daten in die Spalten A und B ein:

• Äpfel, 40

• Orangen, 30

• Birnen, 70

• Bananen, 20

Fügen Sie eine dritte Spalte hinzu, um die Preisinformationen aufzunehmen:

Erstellen Sie eine zweite Excel-Arbeitsmappe mit dem Namen „Produkt“ und benennen Sie Tabelle1 in „Preise“ um. Geben Sie diese Daten in Spalte A und Spalte B ein:

• Äpfel, 1,50

• Orangen, 2,30

• Birnen, 3,60

• Bananen, 2.10

Die Daten sehen so aus:

Um den Umsatz für die Verkäufe in der ersten Tabelle zu berechnen, müssen wir den Preis in der zweiten Tabelle nachschlagen. VLOOKUP zur Rettung!

Schritt 1: Lassen Sie beide Arbeitsmappen geöffnet

Bei Verwendung der SVERWEIS-Formel zwischen zwei Arbeitsmappen müssen beide Arbeitsmappen geöffnet sein. Wenn die zweite Arbeitsmappe geschlossen wird, gibt die Funktion einen Fehler zurück.

Sie können sie entweder einzeln öffnen oder den Befehl „Öffnen“ in Excel verwenden, um beide gleichzeitig zu öffnen. Es gibt zwei Möglichkeiten, eine Arbeitsmappe zu öffnen:

• Gehen Sie zur Registerkarte „Datei“ und wählen Sie „Öffnen“.

• Verwenden Sie die Tastenkombination „Strg + O“ .

Sobald Sie beide Arbeitsmappen geöffnet haben, können Sie zwischen ihnen wechseln, indem Sie auf die entsprechende Registerkarte unten im Excel-Fenster klicken.

Schritt 2: Beginnen Sie mit der Eingabe der SVERWEIS-Formel

Hier ist die grundlegende Syntax der VLOOKUP-Funktion:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value : Der Wert, den Sie in der anderen Arbeitsmappe suchen möchten.

• table_array : Der Zellbereich in der anderen Arbeitsmappe, der die Daten enthält, die Sie abrufen möchten.

• col_index_num : Die Spaltennummer im Bereich, der die Daten enthält, die Sie abrufen möchten.

• range_lookup : Dieser letzte Parameter ist optional. Wenn Sie es auf TRUE setzen oder weglassen, sucht VLOOKUP nach einer ungefähren Übereinstimmung. Wenn Sie den Wert auf FALSE setzen, sucht SVERWEIS nur nach einer genauen Übereinstimmung.

Nehmen wir an, Sie möchten den Preis für Äpfel in der zweiten Arbeitsmappe unserer Beispieldaten nachschlagen. So lässt sich die Formel aufschlüsseln:

• Der Lookup-Wert befindet sich in Zelle A2 der ersten Arbeitsmappe.

• Das Tabellenarray liegt im Zellbereich A1:B5 der zweiten Arbeitsmappe.

• Die col_index_num ist die zweite Spalte, die den Preis enthält.

Der komplizierteste Teil der VLOOKUP-Formel besteht darin, korrekt auf die zweite Arbeitsmappe zu verweisen.

Schritt 3: So referenzieren Sie die zweite Arbeitsmappe

Um die VLOOKUP-Formel für zwei Arbeitsmappen zu vervollständigen, müssen Sie eine vollständig qualifizierte Referenz verwenden, die diese Details aus der zweiten Arbeitsmappe enthält:

• Name der Arbeitsmappe

• Blattname

• Zellbereich

Dadurch wird sichergestellt, dass Excel genau weiß, wo sich die gesuchten Daten befinden. Die vollqualifizierte Referenz wird für das Argument table_array der VLOOKUP-Funktion verwendet.

Dies sind die Werte für die Beispieldaten:

• Name der Arbeitsmappe: Product.xlsx

• Blattname: Preise

• Zellbereich: $A1:$B5

Um den Preis für den ersten Artikel zu ermitteln, geben Sie die folgende Formel in Zelle C2 der ersten Arbeitsmappe ein:

=VLOOKUP(A2, [Product.xlsx]Preise!$A$1:$B$5, 2, FALSE)

Dieses Bild zeigt die Funktion in der Bearbeitungsleiste für die erste Zelle:

Beachten Sie, dass der Arbeitsmappenname in eckigen Klammern steht und auf den Blattnamen ein Ausrufezeichen folgt.

Wenn der Excel-Dateiname ein Leerzeichen oder Sonderzeichen enthält, müssen Sie die gesamte Arbeitsmappe und das gesamte Blatt in einfache Anführungszeichen setzen.

Die Formel verwendet absolute Werte für den Zellbereich (das $-Präfix). Dadurch können Sie die Formel in die restlichen Zeilen kopieren, indem Sie auf den Füllpunkt klicken.

Hier sind die SVERWEIS-Ergebnisse:

Und das ist es! Sie wissen jetzt, wie Sie VLOOKUP effektiv nutzen. Um Ihnen zu helfen, falls Sie auf Fehler stoßen, haben wir im nächsten Abschnitt drei der häufigsten SVERWEIS-Fehler zusammengestellt.

3 häufige VLOOKUP-Fehler und wie man sie behebt

Nachdem Sie nun gelernt haben, wie Sie VLOOKUP aus einer anderen Arbeitsmappe verwenden, können einige häufige Fehler auftreten.

Hier sind die drei häufigsten Fehler und wie man sie behebt.

1. #N/A-Fehler

Der #N/A-Fehler tritt auf, wenn der Suchwert nicht im Tabellenarray gefunden wird. Um diesen Fehler zu beheben, können Sie Folgendes versuchen:

• Überprüfen Sie die Schreibweise des Suchwerts.

• Stellen Sie sicher, dass sich der Suchwert in der ersten Spalte des Tabellenarrays befindet.

• Stellen Sie sicher, dass der Suchwert in den beiden Blättern nicht als Text formatiert ist.

• Stellen Sie sicher, dass das Argument „range_lookup“ auf FALSE gesetzt ist, um eine genaue Übereinstimmung zu finden.

Im obigen Beispiel konnte in der zweiten Arbeitsmappe kein passender Wert für Kirschen gefunden werden.

2. #Ref! Fehler bei der Spaltennummer

Der # Ref! Ein Fehler tritt auf, wenn das Argument col_index_num größer als die Anzahl der Spalten im Tabellenarray ist.

Um diesen Fehler zu beheben, überprüfen Sie, ob das Argument col_index_num kleiner oder gleich der Anzahl der Spalten in der Quelltabelle ist.

Im Bild unten wurde col_index_num auf 3 gesetzt, aber es gibt nur zwei Spalten im Referenzbereich.

3. Fehler in Anführungszeichen und eckigen Klammern

Der Fehler „Anführungszeichen und eckige Klammern“ tritt auf, wenn Sie SVERWEIS mit einem Wert ausführen, der Anführungszeichen oder eckige Klammern enthält.

Um diesen Fehler zu beheben, können Sie Folgendes versuchen:

• Verwenden Sie doppelte Anführungszeichen um den Suchwert.

• Verwenden Sie die Funktion CHAR(34) , um doppelte Anführungszeichen einzufügen.

• Verwenden Sie die Funktion IFERROR, um eine leere Zelle zurückzugeben, wenn der Suchwert Anführungszeichen oder eckige Klammern enthält.

Sobald Sie mit diesen Fehlern vertraut sind, ist die Behebung ganz einfach! Um Ihnen dabei zu helfen, ein VLOOKUP-Profi zu werden, haben wir im nächsten Abschnitt vier Tipps für die Verwaltung verschiedener Arbeitsmappen bereitgestellt.

4 Tipps zum Verwalten verschiedener Arbeitsmappen

Beim Arbeiten mit mehreren Arbeitsmappen ist es wichtig, den Überblick darüber zu behalten, welche Arbeitsmappe welche Daten enthält.

Hier sind unsere vier besten Tipps, um Ihren Verstand zu bewahren:

1. Verwenden Sie Namenskonventionen, um sich zu merken, welche Excel-Tabellen welche Daten enthalten.

2. Verwenden Sie absolute Bezüge in Ihren Formeln, um sicherzustellen, dass der Suchbereich auf die richtigen Zellen verweist, auch wenn Sie die Formel an einen anderen Ort oder in eine andere Excel-Tabelle verschieben oder kopieren.

3. Verwenden Sie die IFERROR-Funktion, um alle #N/A-Fehler zu behandeln, die auftreten können, wenn die Suchwerte nicht in den Quelldaten gefunden werden.

4. Erwägen Sie, die Zellen in der Referenzarbeitsmappe zu sperren , damit sie nicht überschrieben werden können.

So aktualisieren Sie Daten automatisch

Wenn Sie mit mehreren Excel-Dateien arbeiten, kann es zeitaufwändig sein, die Daten manuell zu aktualisieren .

Wenn Sie möchten, dass Ihre VLOOKUP-Formel automatisch aktualisiert wird, wenn Sie Daten zu den anderen Arbeitsmappen hinzufügen oder daraus entfernen, können Sie die Funktion INDIRECT verwenden.

Hier ist wie:

• Ersetzen Sie in der SVERWEIS-Formel das Tabellenarray-Argument durch die Funktion INDIRECT.

• Verwenden Sie in der INDIRECT-Funktion einen Verweis auf die Zelle, die das Tabellenarray in der anderen Arbeitsmappe enthält.

Um dies mit unseren Beispielarbeitsmappen zu erreichen, befolgen Sie diese Schritte.

1. Verwenden Sie eine Zelle in Spalte D, um den Verweis auf das Blatt „Preise“ der Arbeitsmappe „Produkt“ und den Zellbereich A1:B5 wie folgt einzugeben: [Produkt.xlsx]Preise!A:B

2. Verwenden Sie in Zelle C2 der Arbeitsmappe „Verkäufe“ diese Formel: =VLOOKUP(A2, INDIRECT($D$1), 2, FALSE)

3. Kopieren Sie die Formel nach unten (die absoluten Bezüge stellen sicher, dass die Zelle mit der Tabelle erhalten bleibt).

Diese Aktionen stellen sicher, dass Änderungen im Originalblatt automatisch in den verbundenen Zellen aktualisiert werden.

Dieses Bild zeigt die INDIRECT-Funktion kombiniert mit VLOOKUP:

Beachten Sie, dass in diesem Beispiel der Bereich „A:B“ verwendet wird, um auf die beiden Spalten zu verweisen. Das heißt, wenn Sie weitere Produkte und Preise hinzufügen, funktioniert die Formel weiterhin in der aktuellen Arbeitsmappe.

Nachdem Sie nun einen Eindruck von der Leistungsfähigkeit von VLOOKUP bekommen haben, werfen wir in diesem Abschnitt einen Blick auf einige fortgeschrittene Einsatzmöglichkeiten der Funktion!

Erweiterte Verwendungsmöglichkeiten der VLOOKUP-Funktion

Die Beispiele in diesem Artikel geben eine einzelne Übereinstimmung für jeden Suchwert zurück. Wenn Sie mit mehreren Treffern aus einer anderen Arbeitsmappe arbeiten möchten, können Sie stattdessen eine Array-Formel verwenden.

Sie können SVERWEIS auch mit anderen Funktionen kombinieren. Sie können es beispielsweise für eine noch komplexere Analyse in eine IF-Funktion einbetten.

Die komplexe Analyse umfasst Techniken wie Chi-Quadrat-Tests und T-Tests für gepaarte Stichproben. Hier ist ein Beispiel für die Verwendung des Chi-Quadrat-Tests in Excel:

Fortgeschrittene Benutzer nutzen VLOOKUP, um Datensätze zu vergleichen und Nichtübereinstimmungen zu identifizieren. Weitere Möglichkeiten finden Sie in diesem Artikel zum Auffinden von Unstimmigkeiten in Excel .

Aber was ist, wenn Sie VLOOKUP aus irgendeinem Grund nicht verwenden möchten? Du hast Glück! Es gibt einige Alternativen, die wir im nächsten Abschnitt behandeln werden.

2 Hauptalternativen zu VLOOKUP

Wenn es darum geht, Werte in verschiedenen Tabellenblättern nachzuschlagen, ist SVERWEIS oft die erste Wahl. Es gibt jedoch einige Alternativen, die in bestimmten Situationen nützlich sein können.

Die beiden häufigsten sind:

• INDEX-MATCH

• XLOOKUP

1. INDEX-MATCH

INDEX und MATCH ist eine leistungsstarke Kombination aus zwei Funktionen, die zum Nachschlagen von Werten in einer Tabelle verwendet werden können. Im Gegensatz zu VLOOKUP, bei dem sich der Suchwert in der Spalte ganz links in der Tabelle befinden muss, kann INDEX MATCH Werte in jeder Spalte nachschlagen.

So funktioniert das:

1. Die INDEX-Funktion gibt einen Wert aus einem angegebenen Bereich basierend auf einer Zeilen- und Spaltennummer zurück.

2. Die MATCH-Funktion gibt die Position eines Suchwerts in einem angegebenen Bereich zurück.

Um INDEX MATCH zu verwenden, müssen Sie den Bereich angeben, den Sie durchsuchen möchten, die Zeile, in der Sie den Suchwert finden möchten, und die Spalte, in der Sie das Ergebnis zurückgeben möchten.

Hier ist eine Beispielformel:

=INDEX(TableRange,MATCH(LookupValue,LookupRange,0),ColumnIndex)

Um dies mit den Beispieltabellen zu verwenden, ist dies die Formel:

=INDEX([Product.xlsx]Preise!$B$1:$B$5, MATCH(A2, [Product.xlsx]Preise!$A$1:$A$5, 0))

Dies gliedert sich wie folgt:

• Die MATCH-Funktion sucht nach dem Wert in Zelle A2 (Produktname) im Bereich A1:A5 im Blatt „Preise“ der Arbeitsmappe „Produkt“.

• Die INDEX-Funktion gibt einen Wert aus dem Bereich B1:B5 im Blatt „Preise“ der Arbeitsmappe „Produkt“ zurück, basierend auf der von der MATCH-Funktion bereitgestellten Zeilennummer.

Dieses Bild zeigt die Formel in Aktion:

2. XLOOKUP

XLOOKUP ist eine neuere Funktion, die in Excel 365 eingeführt wurde . Es ähnelt VLOOKUP, verfügt jedoch über einige zusätzliche Funktionen.

Einer der größten Vorteile von XLOOKUP besteht darin, dass es Werte links von der Suchspalte nachschlagen kann.

So funktioniert das:

1. Die XLOOKUP-Funktion gibt einen Wert aus einem angegebenen Bereich basierend auf einem Suchwert zurück.

2. Sie können angeben, ob Sie eine exakte oder eine ungefähre Übereinstimmung wünschen.

3. Sie können auch angeben, was zurückgegeben werden soll, wenn keine Übereinstimmung gefunden wird.

Um XLOOKUP zu verwenden, müssen Sie den Suchwert, den Bereich, den Sie durchsuchen möchten, und die Spalte angeben, in der Sie das Ergebnis zurückgeben möchten.

Hier ist eine Beispielformel:

=XLOOKUP(LookupValue,LookupRange,ReturnRange,“Not Found“,0)

Um dies mit den Beispieltabellen zu verwenden, ist dies die Formel:

=XLOOKUP(A2, [Product.xlsx]Preise!$A$1:$A$5, [Product.xlsx]Preise!$B$1:$B$5)

Dies gliedert sich wie folgt:

• A2: Der Wert, den Sie nachschlagen möchten (Produktname).

• [Product.xlsx]Preise!$A$1:$A$5: Der Bereich, in dem Sie nach dem Suchwert suchen möchten (Produktnamen im Blatt „Preise“ der Arbeitsmappe „Produkt“).

• [Product.xlsx]Preise!$B$1:$B$5: Der Bereich, der die Rückgabewerte enthält (Preise im Blatt „Preise“ der Arbeitsmappe „Produkt“).

Dies ist die Formel in Aktion:

So verwenden Sie VLOOKUP über mehrere Blätter hinweg

Wenn Sie einen VLOOKUP zwischen zwei Arbeitsmappen durchführen müssen, müssen Sie VLOOKUP möglicherweise auch über mehrere Blätter hinweg verwenden.

Glücklicherweise ähnelt der Vorgang sehr dem Durchführen eines VLOOKUP zwischen zwei Arbeitsmappen.

Es gibt vier Schritte.

Schritt 1: Identifizieren Sie die Komponenten

Anstatt das Tabellenarray wie bei einem einzelnen Blatt einzuschließen, müssen Sie auch den Blattnamen angeben.

Wenn Sie beispielsweise im Arbeitsblatt „Jan“ in der Arbeitsmappe „Sales_reports.xlsx“ nach einem Wert im Bereich A2:B6 suchen möchten, würde Ihre Formel wie folgt aussehen:

=VLOOKUP(A2,[Sales_reports.xlsx]Jan!$A$2:$B$6,2,FALSE)

Schritt 2: Richten Sie Ihre Daten ein

Stellen Sie sicher, dass Ihre Daten so organisiert sind, dass sie problemlos über mehrere Blätter hinweg referenziert werden können.

Möglicherweise möchten Sie verschiedene Registerkarten für bestimmte Referenzdaten erstellen.

Schritt 3: Verwenden Sie die SVERWEIS-Funktion

Sobald Ihre Daten eingerichtet sind, können Sie mit der VLOOKUP-Funktion nach Daten in anderen Blättern suchen und diese abrufen.

Stellen Sie einfach sicher, dass Sie den Blattnamen in Ihre Formel aufnehmen, wie oben gezeigt.

(Optional) Schritt 4: #N/A-Fehler beheben

Wenn bei der Verwendung von VLOOKUP über mehrere Blätter hinweg #N/A-Fehler auftreten, überprüfen Sie Ihre Formel noch einmal, um sicherzustellen, dass Sie alle erforderlichen Blätter und Bereiche korrekt referenziert haben.

Möglicherweise möchten Sie auch überprüfen, ob Sie eine exakte Übereinstimmung und nicht eine Bereichssuche durchführen.

Abschließende Gedanken

Sie haben gelernt, wie Sie mit VLOOKUP Daten in zwei Tabellenkalkulationen suchen und abrufen. Diese vielseitige Funktion erleichtert das Nachschlagen von Werten in einer Arbeitsmappe anhand entsprechender Werte in einer anderen.

Die Leistungsfähigkeit der VLOOKUP-Funktion geht weit über einzelne Arbeitsblätter hinaus und erstreckt sich über verschiedene Arbeitsmappen, um Daten zu verknüpfen und zu analysieren.

Die Beherrschung dieser Funktion ist ein wichtiger Schritt auf dem Weg zur Excel-Beherrschung, denn sie eröffnet neue Möglichkeiten zur Verwaltung und Interpretation Ihrer Daten.

Durch die Integration von Funktionen wie INDIRECT, INDEX-MATCH oder XLOOKUP können Sie die Flexibilität und Effektivität Ihrer Datensuche weiter steigern.

Üben Sie weiter und erkunden Sie die umfangreichen Möglichkeiten von Excel. das ist nur die Spitze des Eisbergs. Viel Spaß beim Datensuchen!