Excel SVerweis einfach erklärt

12. April 2019 in Excel

Eine Illustration von Taschenrechner, Stiften, Briefumschlägen und einer Brille

Der SVerweis und große Datenmengen gehören einfach zusammen. Niemand hat schließlich Lust 300 oder 400 Produktposten durchzugehen und den Preis für jedes einzelne per Hand einzutragen. Dabei bezieht sich die Formel nicht nur auf eine Tabelle, sondern legt die Daten aus zwei verschiedenen Tabellen zusammen und ordnet sie richtig zu.

Die Funktion SVerweis wird also immer dann verwendet, wenn mehrere Tabellen miteinander verglichen und / oder zusammengefasst werden sollen.

Das S steht hierbei für Senkrecht, denn Excel durchsucht mit diesem Verweis die erste senkrechte Spalte beider Tabellen nach dem angegebenen Zielwert. Das ist allerdings nur der erste Schritt. Bevor wir jetzt schon zu sehr ins Detail gehen, werfen wir noch einen kurzen Blick auf die drei wichtigsten Anwendungsbereiche, um ein Vorstellungsvermögen für die praktische Verwendung zu erhalten.

Anwendungsbereiche der SVerweis Formel

1. Prüfen, ob ein bestimmter Wert in einer Tabelle auch in einer anderen Tabelle enthalten ist

  • Beispielfrage: Welche Artikel wurden im Vergleich zum Vormonat nicht mehr geliefert?

2. Vergleich zweier Tabellen miteinander

  • Beispielfrage: Wo gibt es Differenzen in meinen Artikellisten im Vergleich zum Vormonat?

3. Erweitern einer Liste mit den Daten einer Tabelle mit den Inhalten einer zweiten Tabelle

  • Beispielfrage: Wie ermittle ich im Vertrieb die Provision für den jeweiligen Umsatz in Quartal 3?

Die Formel und ihre Argumente

Wie jede Funktion in Excel beginnt sie mit dem = Zeichen und dem Namen des Befehls, also =SVERWEIS. Excel weiß jetzt, dass es der markierten Zelle etwas zuweisen muss. Die komplette Formel, die man eingibt sieht folgendermaßen aus:

=SVERWEIS(Suchkriterium;Matrix;Index;Wahr/Falsch)

Die Formel wird also durch vier einzelne Argumente definiert:

Suchkriterium: Das Suchkriterium ist der Wert, der in beiden der zu vergleichen Listen vorkommt. Man gibt mit dem Suchkriterium den Wert in Liste 1 an, der ebenfalls in Liste 2 vorhanden ist.

Matrix: Die Matrix befindet sich in der zu vergleichenden Liste (Liste 2) und ist der Bereich, in der sich die Informationen für die Liste 1 befinden.

Index: Der daraufhin folgende Index gibt an, welche Spalte aus meiner Matrix, also aus meinen verwendeten Daten, für die Suche verwendet werden soll. 

Spaltenindex: Der Spaltenindex gibt an, welche Spalte aus der Matrix, in die Liste 1 übernommen werden soll.

Bereich Verweis: Der Bereich Verweis wird mit Wahr oder Falsch gefüllt. 

  • Wahr steht für: ungefähre Überstimmung des Suchkriteriums aus Liste 1 mit dem Kriterium der Matrix in Liste 2. Gibt man Wahr ein, dann wird in der Formel für das Suchkriterium nicht der exakte Wert dafür verwendet, sondern auch der nächste Näherungswert (z.B. Rabattstaffeln, Preisstaffeln, Mengenstaffeln).
  • Beim Wert Falsch wird nur ein Wert übergeben, wenn das Suchkriterium exakt wiedergegeben wird (z.B. Artikelnummer, Personalnummern).

Beispiel mit Bereichverweis falsch

Bleiben wir doch zur Einführung bei der Produktliste, der die Preise zugeordnet werden müssen. Logischerweise haben wir zwei Tabellen zur Verfügung. Tabelle Blau mit der Produktnummer und dem Namen des Artikels und Tabelle Orange mit der Produktnummer und dem Artikelpreis. Ziel ist es, die Preise für die jeweiligen Artikel in Spalte C automatisch anzeigen zu lassen.

Microsoft-Excel-SVerweis-einfach-erklärt

Sie werden einwenden, dass das Problem durch ein einfaches Copy/Paste-Verfahren viel schneller gelöst ist. Das mag für dieses Beispiel gelten, denn die Produktnummern sind vollständig und sortiert. Bei größeren Datenmengen ist das aber meist nicht der Fall. Oft fehlt die Angabe für eine Produktnummer oder die Produktnummer selbst fehlt im Datenblatt. Vor allem bleiben die Informationen auch dann aktuell, wenn sich beispielsweise der Produktname ändert.

Schritt 1: Markieren der Zelle und Eingabe des Suchkriteriums

Microsoft-Excel-SVerweis-einfach-erklärt

Im ersten Schritt klicken wir auf die Zelle, die den Verweis enthalten soll. Dort geben wir den Formelnamen =Sverweis und das erste Argument – das Suchkriterium – ein. Das Suchkriterium ist in unserem Fall das Feld A2, denn Excel soll in beiden Tabellen nach dieser Produktnummer (hier Nummer 350) suchen.

Schritt 2: Definition der Matrix

Microsoft-Excel-SVerweis-einfach-erklärt

Jetzt geben wir an, wo Excel nach der zugehörigen Information suchen soll. In unserem Fall reicht die Informationsquelle bzw. Matrix von Feld E6 bis hin zu Feld F11. Beachten Sie, dass vor der Feldzahl ein Dollarzeichen gesetzt werden muss, denn sonst funktioniert die Formel nicht. Eingabe: =Sverweis(A2;$E$2:$F$7;

Schritt 3: Den Spaltenindex angeben

In welcher Spalte der Matrix findet Excel nun die Information, also hier den Preis für das Produkt genau? Das ist relativ simpel – in Spalte 2.

Eingabe: =Sverweis(A2;$E$2:$F$7;2)

Bereich Verweis

Soll genau das exakte Suchkriterium verwendet werden oder genügt ein Näherungswert? Wir möchten, dass exakt nach dieser Produktnummer gesucht wird. Sie können entweder Falsch oder für Falsch den Wert 0 eingeben.

Eingabe: Sverweis(A2;$E$2:$F$7;2;Falsch) oder = Sverweis(A2;$E$2:$F$7;2;0)

Microsoft-Excel-SVerweis-einfach-erklärt

Klickt man jetzt auf Enter, dann ist wird der Preis in der Zelle angezeigt. Und es ist auch nicht schwierig nachzuvollziehen, dass sich der Preis immer auf die Produktnummer bezieht. Ändert der Hersteller den Namen des Produkts ist das kein Problem.

Schritt 5: Anwendung der Formel auf alle nachfolgenden Zellen

Natürlich soll die Formel auch auf alle anderen Produkte angewendet werden. Dazu einfach die Zelle mit dem Cursor nach unten ziehen und so die Formel kopieren. Es werden automatisch die richtigen Preise angezeigt.

Beispiel mit BereichVerweis WAHR

In diesem Beispiel haben wir eine Kundenliste mit den dazugehörigen Umsätzen (blaue Tabelle 1).

In Abhängigkeit des erreichten Umsatzes erhält der Kunde einen Rabatt gemäß er Rabattstaffel (rote Tabelle 2).

Microsoft-Excel-SVerweis-einfach-erklärt

Schritt 1: Markieren der Zelle und Eingabe des Suchkriteriums

Wir erweitern unsere blaue Tabelle um ein Spalte Rabatt.

In der Zelle C2 fügen wir die Funktion SVerweis ein. Das Suchkriterium ist dabei der Umsatz. Der Umsatz ist der Wert, der in beiden Listen enthalten ist und an dem in der Liste 2 die entsprechende Rabattkennziffer gebunden ist.

Microsoft-Excel-SVerweis-einfach-erklärt

Schritt 2: Definition der Matrix

Jetzt geben wir an, wo Excel nach der zugehörigen Information suchen soll. In unserem Fall reicht die Informationsquelle bzw. Matrix von Feld E2 bis hin zu Feld F5. Beachten Sie, dass vor der Feldzahl ein Dollarzeichen gesetzt werden muss, denn sonst funktioniert die Formel nicht. Eingabe: =SVERWEIS(B2;$E$2:$F$5

Microsoft-Excel-SVerweis-einfach-erklärt

Schritt 3: Den Spaltenindex angeben

In welcher Spalte der Matrix findet Excel nun die Information, also hier den Rabatt für den Kunden genau? Das ist relativ simpel – in Spalte 2.

Eingabe: =SVERWEIS(B2;$E$2:$F$5;2

Microsoft-Excel-SVerweis-einfach-erklärt

Schritt 4: Bereich Verweis

Der Kunde B hat einen Umsatz von 310.000 €. In der Rabattliste ist allerdings nur ersichtlich, das ein Kunde einen Rabatt von 2% erhält, wenn der Umsatz zwischen  200.000 und 300.000 € liegt. Jetzt muss der SVerweis also auch „zwischen den Zeilen lesen“. Es reicht eine ungefähre Überstimmung aus. Der Bereich Verweis wird nicht gefüllt – oder alternativ WAHR eingegeben.

Eingabe: =SVERWEIS(B2;$E$2:$F$5;2)

Wichtig: Damit die Ergebnisse des SVerweises stimmen, müssen die Umsätze in der roten Tabelle aufsteigend sortiert sein.

Mit Klick auf Enter und das Ausfüllen der Funktion auf alle Datensätze werden die Rabatte für alle Kunden ersichtlich.

Microsoft-Excel-SVerweis-einfach-erklärt

Excel Kurse

Excel Kurse für Anfänger oder Fortgeschrittene.