Excel SVerweis (Suchverweis), WVerweis und Verweis -  einfach erklärt

in diesem Beitrag wird der SVerweis (Suchverweis), WVerweis, Verweis, Adresse, Zeile und Spalte einfach erklärt. Sie gehören zu der Gruppe der Verweise. Verweise liefern anhand von Informationen Ergebnisse aus anderen Tabellen oder geben Zeilen oder Spalteninformationen zurück. Die Clips für diese Ausdrücke sind etwas größer. Deshalb bitte ich die längere Ladezeit zu entschuldigen.

Hilfe anfordern für 25 € Festpreis

Excel SVerweis (Suchverweis)

Mit dem Excel SVerweis (Suchverweis) können Information aus anderen Datenbereichen gelesen werden, wodurch Daten durchaus einfacher zu lesen und zu verstehen sind. Man kann das auch so beschreiben, das mehrere Tabellen zu einer großen, gemeinsamen Tabelle zusammengeführt werden. Das ist eine Möglichkeit, die auch professionelle Datenbanken liefern. Im Ergebnis ist das sicher auch so, aber Excel ist nicht mit Datenbanken zu vergleichen. Diese Möglichkeit in Excel über den SVerweis zu haben, ist aber klasse.

Hier eine (hoffentlich) verständliche Beschreibung des SVerweis. Man stelle sich eine Adresstabelle bestehend aus 5 Spalten vor. Die 1. Spalte enthält die Kundennummer, Spalte 2 den Namen, Spalte 3 die Straße, Spalte 4 die Postleitzahl und Spalte 5 den Ort. Über den SVerweis möchten Sie anhand des Suchbegriffe "Kundennummer" den Namen wissen.  Manuell würden wir diese Zeile einfach finden, obwohl es schon darauf ankommt, wie groß diese Adresstabelle tatsächlich ist.

Argumente des Excel SVerweis

Damit SVerweis (Suchverweis) ordentlich arbeitet, benötigt sie mindestens 3 von 4 Informationen oder Argumente.

1. Argument: der Suchbegriff, nach dem in der Adresstabelle gesucht werden soll. In unserem Beispiel ist das eine Kundennummer.

2. Argument: Der Bereich der Adresstabelle, so wie Bereiche in Excel eben beschrieben werden, Anfangszelle, :, Endzelle (z.B. B2:B3). Wichtig ist, das in dieser Tabelle ausschließlich die 1. Spalte senkrecht (daher kommt das "S" im SVerweis) durchsucht wird, um den Suchbegriff zu finden. Sollte diese Tabelle Überschriften haben, dann werden diese nicht benötigt und müssen im Bereich nicht enthalten sein. Es ist von Vorteil und reduziert Fehler, wenn nach der 1. Spalte sortiert ist.

4. Argument: Die Spalte, in der sich die gewünschte Information befindet. Gezählt wird ab der 1. Spalte der Adresstabelle, die nicht unbedingt die Spalte A sein muss. In unserem Beispiel befindet sich der Name in der 2. Spalte, das Argument müsste also 2 sein.

4. Argument (optional): Optionale Argumente müssen nicht gesetzt werden. Wird dieses Argument nicht gesetzt, dann wird von Excel im SVerweis "Wahr" (True) gesetzt. Wahr steht dafür, das der Suchbegriff exakt mit dem Wert in der 1. Spalte übereinstimmt (genaue Übereinstimmung). Die Beschreibung für Falsch(False, ungefähre Übereinstimmung) kommt später im Beitrag.

Damit würde der SVerweis so aussehen:
SVerweis(Suchbegriff, Bereich, Spalte mit dem gewünschten Wert, exakte oder ungefähre Übereinstimmung) 

Diese Nutzung des SVerweis lässt sich durchaus auf andere Aufgaben übertragen. Stellen Sie sich eine Tabelle mit Artikeldaten vor, in der nach der Artikelnummer gesucht werden muss. In beiden Aufgaben ist eines gleich: Die Kundennummer und auch eine Artikelnummer dürfen sich in diesen Tabellen nicht wiederholen. Ist das der Fall, dann wird immer der zuerst gefundene Wert zurückgegeben. 

Hinweise zu "genaue Übereinstimmung" im Excel SVerweis

Wie der Name schon sagt, der Suchbegriff muss in der 1. Spalte der Suchtabelle übereinstimmen. Ist die genaue Übereinstimmung gewählt und kein Wert in der Suchtabelle vorhanden, dann ist das Ergebnis von SVerweis() #NV. In diesem Fall ist das kein Fehler, sondern sogar korrekt und wichtig, das es kenntlich gemacht wird. Ich erwähne das, da bei der ungefähren Übereinstimmung der am ersten gefundene Wert, der ungefähr übereinstimmt zurückgegeben wird, was in diesem Fall falsch wäre und der Benutzer diesen Fehler nicht sehen würde! Mein Tipp: Achten Sie bitte darauf, das bei Suchbegriffen, die in der Suchtabelle eindeutig sind, die genaue Übereinstimmung verwendet wird.

die ungefähre Übereinstimmung im SVerweis

In dem obigem Beispiel wird die exakte Übereinstimmung im SVerweis (Suchverweis) gezeigt.  Mit dem folgendem Beispiel möchte ich eine entsprechende andere Aufgabe darstellen. Für den Vertrieb gibt es ein Provisionsmodell, das je nach erreichtem Umsatz einen Provisionssatz vorsieht. Dazu gibt es eine Tabelle die in der ersten Spalte den Umsatz und in der zweiten den dazugehörigen Provisionssatz abbildet. Nun wird ein Umsatz in einem solchem Szenarium nie exakt erreicht, sondern nur ungefähr. Mit dem SVerweis möchte ich nun den für einen Verkäufer zuteffenden Provisionssatz ermitteln.
In dem Clip zeige ich, wie der SVerweis so erstellt wird, das sie auch kopierbar ist.

SVerweis kopieren

Formeln sind kopierbar, wenn absolute und relative Bezüge optimal eingesetzt werden. In diesem Beispiel mit SVerweis muss der Suchbegriff (1. Argument) als relativer Bezug verwendet werden, der Suchbereich jedoch als absoluter Bezug. Das liegt daran, das der Bereich sich als relativer Bezug durch das kopieren ändern würde, der absolute aber nicht.
Im Clip ist das an der Stelle sichtbar, wenn der Bezug umschaltet. Das wird durch die Taste F4 auch während der Eingabe ausgelöst, wenn der Cursor noch direkt hinter dem Bezug steht, also vor dem Semikolon als Endzeichen für das 2. Argument. 

Weitere Informationen zu relativen und absoluten Bezügen erhalten Sie hier.

Excel WVerweis

Der WVerweis gleicht dem SVerweis (Suchverweis) bis auf einen Unterschied. Während der SVerweis senkrecht in der ersten Spalte der Suchtabelle (oder Matrix) sucht, arbeitet der WVerweis mit der 1. Zeile, eben waagerecht (das "W" im WVerweis). 

Beispiel: Es gibt eine Umsatztabelle, die in den Spalten die Jahre abbildet und in den Zeilen verschiedene Filialen. Dazwischen befinden sich die Umsätze zu den Filialen und den Jahren (siehe Clip). Es wird ein bestimmtes Jahr gesucht. 

Der WVerweis hat die gleichen Argumente wie der SVerweis (Suchverweis), das Suchkriterium, den Suchbereich (oder auch Matrix) und den Zeilenindex anstelle des Spaltenindex und die Art der Übereinstimmung. 

Die ungefähre oder genaue Übereinstimmung verhält sich ebenfalls wie im SVerweis.  

Excel Vergleich 

Mit dem Ausdruck Vergleich wird in einer Matrix (oder Bereich) von Zellen nach einen Kriterium durchsucht. Als Ergebnis gibt Excel Vergleich die Position des Treffers in der Matrix zurück. Für das Beispiel WVerweis im Clip würde das bedeutet: Wenn nach dem Begriff Hannover im Bereich B3:B5 gesucht würde, dann wäre das Ergebnis 2, da sich Hannover in der Zeile 2 der Matrix befindet.

Aufbau der Funktion Vergleich: =Vergleich(Suchkriterium; Matrix; Optionall: Vergleichstyp)

Vergleichstyp

aus der Onlinehilfe bei Microsoft für Vergleich

Die Matrix im WVerweis Vergleich darf nur 1 Spalte haben um den Suchwert zu finden. Das ist aus meiner Sicht auch logisch, da bei mehreren Spalten die Rückgabe der Zeile zu wenig wäre um eine Zelle zu bestimmen.

Excel Vergleich kombiniert mit WVerweis

Vergleich lässt sich dadurch super in Kombination mit unserem WVerweis (oder auch andere Ausdrücke) verwendet. Weil das funktionieren kann habe ich im Clip zusammengestellt. Darin möchte ich nicht nur nach dem Kriterium Jahr suchen, sondern ich möchte auch noch die Filiale mit einbeziehen. Dazu kombiniere ich die Funktionen Vergleich und WVerweis. Mit Vergleich errechne ich die Zeile für die Filiale und gebe diese in einem Feld aus.

Excel Index

Der Index() kann aus einer Matrix einen Wert finden, indem der Zeilen und Spalteninformation übergeben wird. Die Argumente für Index sind:
Index(Matrix;Zeile;Spalte;Bereich)
1. Argument: Die Matrix ist mindestens ein Bereich. Werden mehrere Bereiche angesprochen, dann muss dieses Argumgent in Klammern gesetzt werden und die Bereiche durch ein Semikolon voneinander getrennt werden.
2. Argument: das ist die Zeile dieses gewählten Bereichs oder Matrix
3. Argument: das ist die Spalte dieses gewählten Bereichs oder Matrix
4. Argument (Optional): Das ist nur dann notwendig, wenn mehrere Bereich als 1. Argument gesetzt sind. Hier wird hinterlegt, welcher Bereich gemeint ist. Der Zähler geht dabei von 1 (1. Bereich) bis zum letzten Bereich, so wie sie im 1. Argument gesetzt sind.
In unserem Beispiel geht es lediglich um einen Bereich.

Beispiel: Da die Errechnung der Zeile bereits in der Kombination zwischen WVerweis und Verweis verwendet wurde, zeige ich hier die Formeln ab der Errechnung der Spalte.

Excel Zeile, Spalte und Adresse

Die Zeile() kann die Zeilennummer eines Bezugs ermittelt werden. Zeile ist dabei einer der wenigen Ausrücke, die auch ohne Argument verwendet werden kann (Zeile()). Ist das der Fall, dann gibt Zeile die Zeilennummer der aktuellen Zelle zurück. Als Argument kann aber auch ein Bezug eingetragen werden (Zeile(B3)). Dann gibt Zeile die Zeilennummer des verwendeten Bezugs zurück. In diesem Fall wäre das die 3.

Mit Spalte() kann die Spaltennummer eines Bezugs ermittelt werden. Spalte ist dabei einer der wenigen Funktionen, die auch ohne Argument verwendet werden kann (Spalte()). Ist das der Fall, dann gibt Spalte die Spaltennummer der aktuellen Zelle zurück. Als Argument kann aber auch ein Bezug eingetragen werden (Spalte(B3)). Dann gibt Spalte die Spaltennummer des verwendeten Bezugs zurück. In diesem Fall wäre das die 2. Die Spaltennummer ist in Buchstaben angegeben und nicht in Zahlen. Die Funktion Splate gibt allerdings eine Zahl zurück und keinen Buchstaben. Mit der Funktion Adresse kann das aber in einen Bezug umgewandelt werden. 

Mit Adresse() kann eine Zeilen und eine Spalteninformation in eine Adresse oder einen Bezug umgewandelt werden. Sie hat folgende Argumente:
1. Argument: die Zeilennummer als Zahl
2. Argument: die Spaltennummer als Zahl
3. Argument (optional): Hier wird der Bezugstyp bestimmt. Ohne dieses Argument (1) wird ein absoluter Bezug zurückgegeben. Die 4 erzeugt einen relativen Bezug.  Bei 2 ist die Zeile Absolut gesetzt und bei 3 die Spalte.

die weiteren Argumente vernachlässige ich nun in diesem Beitrag.