Excel SVerweis einfach erklärt

10. Juli 2016 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.

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. Zusammenführung verschiedener Daten aus einem Stammdatenblatt

  • Beispielfrage: Wie schaffe ich es, dass neben der Kundennummer automatisch die zugehörige Adresse in Excel erscheint?

2. Zusammenführung von Informationen zweier Tabellen/Datenblätter mit einem gleichen Kriterium

  • Beispielfrage: Wie führe ich die Mengenangabe aus Sheet 1 mit der Größenangabe aus Sheet 2 für die jeweilige Produktnummer zusammen?

3. Einem Wertebereich müssen verschiedene Zahlenwerte zugeordnet werden

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

Die Formel und ihre Argumente

Ganz ohne Theorie funktionieren Funktionen leider nicht. Deshalb jetzt etwas trocken, wie die Formel SVerweis aufgebaut ist und was sie Excel mitteilt, wenn man sie richtig anwendet. 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 oder auch der Nachschlagewert bezieht sich auf diejenigen Daten, die man kennt. Bei einem Telefonbuch wäre das der Name, nach dem man sucht. Bei einer Warenliste sind es in der Regel die Produktnummern. Man stellt die Frage: Nach welchem Kriterium soll sortiert werden? Bzw. Was suche ich?

Matrix: Das Argument Matrix sagt der Formel woher man seine Daten bezieht. Was ist meine Datenbank? Woher beziehe ich meine Informationen zu diesem Produkt oder zu dem Namen?

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

Wahr/Falsch: Und am Ende der Formel nicht irritieren lassen. Es ist hier nicht das Wahr/Falsch in seinem Wortsinn gemeint. 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. Beim Wert Falsch wird nur ein Wert übergeben, wenn das Suchkriterium exakt wiedergegeben wird.

Für die Praxis bedeutet das, wenn ich beim Vergleich eine exakte Übereinstimmung benötige (z.B. Artikelnummer, Personalnummern, ect.) verwende ich immer Falsch. Will ich aber Bereich abdecken (z.B. Rabattstaffeln, Preisstaffeln, Mengenstaffeln) verwende ich Wahr. Jetzt wird der komplette Bereich zwischen den Werten auch vom SVERWEIS abgedeckt.

Ein konkretes Beispiel für den Einstieg

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.

Workbook Datenblatt mit ersten Werten

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

Workbook Datenblatt auf dem gezeigt wird wie man Zellen markiert und Suchkriterien vergibt

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 364) suchen.

Schritt 2: Definition der Matrix

Workbook Datenblatt, in dem eine Matrix definiert wird

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 – auf der Tastatur bei der Zahl 4 – gesetzt werden muss, denn sonst funktioniert die Formel nicht. Eingabe: =Sverweis(A2;$D$9:$E$15;

Schritt 3: Den Index angeben

SVerweis, den Index 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;$D$9:$E$15;2;

Schritt 4: Wahr/Falsch?

SVerweis Microsoft Excel, wahr/falsch

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;$D$9:$E$15;2;Falsch) oder =SVERWEIS(A2;$D$9:$E$15;2;0)

Schritt 5: Fertig!

SVerweis Microsoft Excel, Fertigstellung

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 6: Anwendung der Formel auf alle nachfolgenden Zellen

SVerweis Microsoft Excel, Anwendung

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.

Empfohlen

Excel Kurse

Excel Kurse
Excel Kurse für Anfänger oder Fortgeschrittene.

Neueste

Monatlich Tipps & Tricks direkt via Mail

Bleiben Sie mit neuen und innovativen Tipps immer einen Schritt voraus.

Excel Kurse

Excel Kurse für Anfänger oder Fortgeschrittene.