Microsoft Excel stellt eine Fülle von Funktionen zur Datenanalyse bereit. Eine der bekanntesten ist die SVerweis-Funktion, die dazu dient, Daten in Tabellen zu identifizieren. Dieser Artikel erläutert den Einsatz der SVerweis-Funktion in Excel zur Suche von Daten in Tabellen, und stellt darüber hinaus Alternativen vor.
Was ist die SVerweis-Funktion in Excel?
Der SVerweis, auch als VLookup bekannt, steht für “Vertikaler Verweis” (oder “Senkrechter Verweis”) und ist einer der häufigsten und nützlichsten Befehle in Excel für den Umgang mit großen Datenmengen. Dieser Befehl ermöglicht es Benutzern, bestimmte Werte in einer Tabelle oder Spalte zu lokalisieren und zu extrahieren, indem eine Bezugszelle und eine Spalte zum Suchen angegeben werden. Wenn man etwa eine Tabelle hat, die aus zwei Spalten besteht (Name und E-Mail-Adresse), kann man mit dem SVerweis-Befehl nach einem bestimmten Namen suchen und die zugehörige E-Mail-Adresse ausgeben lassen.
Aber Vorsicht! Der SVerweis sucht ausschließlich in einer Richtung: von links nach rechts.
SVerweis Funktion und Syntax
Die Syntax für die SVerweis-Funktion lautet:
=SVERWEIS(Suchkriterium; Matrix; Spaltenindex;[Bereich_Verweis])
Argumente
Suchkriterium: Der Wert, der in der ersten Spalte der Matrix gesucht werden soll. Der Wert kann ein Text, ein logischer Wert oder eine Zahl sein.
Matrix: Der Bereich, in dem die Suchkriterien und die zurückzugebenden Werte gespeichert sind. Der Bereich muss mindestens zwei Spalten enthalten.
Spaltenindex: Die Spalte in der Matrix, in der der Wert zurückgegeben werden soll. Die erste Spalte in der Matrix hat den Spaltenindex 1.
Bereich_Verweis: Ein logischer Wert, der angibt, ob man eine genaue oder eine ungefähre Übereinstimmung wünscht. Bei WAHR oder 1 wird eine ungefähre Übereinstimmung durchgeführt. Bei FALSCH oder 0 wird eine genaue Übereinstimmung durchgeführt. Wenn das Argument Bereich_Verweis weggelassen wird, wird die ungefähre Übereinstimmung durchgeführt.
SVerweis Beispiele
Im Folgenden werden einige Beispiele für den Einsatz der SVerweis-Funktion in Excel dargestellt.
Einfache SVerweis-Funktion
Als Ausgangspunkt habe ich eine Tabelle mit Namen und E-Mail-Adressen.
Nun möchte ich die E-Mail-Adresse von “Cooper Schinacher” finden. Hierzu nutze ich die SVerweis-Funktion.
=SVERWEIS("Cooper Schinacher";A2:B6;2;0)
Ich kann auch den Bereich A1:B6 als Tabelle formatieren (STRG+T) und die Tabelle als Matrix verwenden.
=SVERWEIS("Cooper Schinacher";Tabelle1;2;0)
Dynamischer Spaltenindex
Nachfolgend habe ich eine Tabelle zu einem Lagerbestand, mit Produkt, Preis, Anzahl und Kategorie. Ich kann herausfinden, wie teuer ein Apfel ist, indem ich den SVerweis verwende.
=SVERWEIS("Apfel";A2:D6;2;0)
Was aber, wenn ich nicht genau weiß, in welcher Spalte der Preis steht? Ich kann den Spaltenindex dynamisch gestalten, indem ich die Funktion VERGLEICH verwende.
=SVERWEIS("Apfel";A2:D6;VERGLEICH("Preis (EUR)";A1:D1;0);0)bzw.=SVERWEIS("Apfel";Tabelle2;VERGLEICH("Preis (EUR)";Tabelle2[#Kopfzeilen];0);0)
Nicht vorhandener Suchbegriff
Wenn der Suchbegriff nicht vorhanden ist, gibt die SVerweis-Funktion den Fehler #NV zurück.
Dies kann ich mit der Funktion WENNNV abfangen.
=WENNNV(SVERWEIS("Birne";A2:D6;2;0);"Nicht vorhanden")
Unpraktisch ist allerdings, dass jetzt nicht erkennbar ist, ob das Produkt oder der Spaltenname nicht existiert. Dies kann ich jedoch mit den Funktionen WENN und ISTNV abfangen.
=WENNNV(SVERWEIS($F$2;Tabelle2;VERGLEICH($F$3;Tabelle2[#Kopfzeilen];0);0);WENN(ISTNV(VERGLEICH($F$3;Tabelle2[#Kopfzeilen];0));"Spalte nicht vorhanden";"Produkt nicht vorhanden"))
Noch einfacher ist es, wenn Teile der Funktion mit LET definiert werden.
=LET(Suchbegriff;$F$2;Tabelle;Tabelle2;Kopfzeilen;Tabelle2[#Kopfzeilen];Spaltenname;$F$3;Spaltenindex;VERGLEICH(Spaltenname;Kopfzeilen;0);WENNNV(SVERWEIS(Suchbegriff;Tabelle;Spaltenindex;0);WENN(ISTNV(Spaltenindex);"Spalte nicht vorhanden";"Produkt nicht vorhanden")))
Suche über mehrere Arbeitsblätter
Angenommen, ich habe eine Excel-Datei mit mehreren Arbeitsblättern. Auf jedem Arbeitsblatt habe ich eine Tabelle mit Produkten und Preisen.
Ich möchte nun die Preise für Januar und Februar in einer Tabelle zusammenfassen. Hierzu verwende ich die Funktion SVerweis zusammen mit der Funktion INDIREKT.
=SVERWEIS($A2;INDIREKT("'"&B$1&"'!$A$2:$B$6");2;0)
Auf diese Weise wechsle ich bei der SVerweis-Funktion dynamisch das Arbeitsblatt.
Ungefähre Übereinstimmung
In manchen Fällen benötigt man auch eine ungefähre Übereinstimmung.
Ich habe eine Tabelle mit Produkten und Preisen (tbl_Produkte) und indessen auch eine zweite Tabelle mit einem Mengenrabatt (tbl_Mengenrabatt).
=LET(Suchbegriff;$F$2;Tabelle;tbl_Produkte;Menge;$F$3;WENNNV(SVERWEIS(Suchbegriff;Tabelle;2;0)*Menge;"Produkt nicht vorhanden"))
Wenn die Menge jedoch größer als 10 ist, soll der Preis aber mit den Daten aus der Tabelle tbl_Mengenrabatt rabattiert werden.
Hier benötige ich die ungefähre Übereinstimmung, weil die Menge nicht nur 10, sondern auch beispielsweise 11 sein könnte.
=LET(Suchbegriff;$F$2;Menge;$F$3;Rabatt;1-SVERWEIS(Menge;tbl_Mengenrabatt;2;1);WENNNV(SVERWEIS(Suchbegriff;tbl_Produkte;2;0)*Menge*Rabatt;"Produkt nicht vorhanden"))
Alternativen zu SVerweis
Suchrichtung nach unten
Die Funktion SVerweis sucht immer nach rechts. Wenn ich waagerecht (nach unten) suchen möchte, kann ich die Funktion WVERWEIS verwenden.
=WVERWEIS(Suchkriterium; Matrix; Zeilenindex;[Bereich_Verweis])
Index und Vergleich (Index & Match)
Um mehr Flexibilität bezüglich der Suchrichtung und der Spaltenauswahl zu haben, kann ich die Funktionen INDEX und VERGLEICH verwenden.
=INDEX(Matrix;VERGLEICH(Suchkriterium;Matrix;[Vergleichstyp]);[Spaltenindex])
XVerweis
Seit Excel 2019 gibt es die Funktion XVerweis mit Suchfunktion in alle Richtungen und integrierter Fehlerbehandlung. Diese Funktion kann SVerweis, WVerweis, Index und Vergleich ersetzen. Allerdings sollte man Kompatibilitätsprobleme mit älteren Excel-Versionen und die Geschwindigkeit bei größeren Dateimengen beachten.
=XVERWEIS(Suchkriterium;Suchmatrix;Rückgabematrix;wenn_nicht_gefunden;Vergleichsmodus;Suchmodus)
Langfristig wird die Funktion SVerweis vermutlich durch XVerweis abgelöst werden, da sie mehr Möglichkeiten bietet und einfacher zu handhaben ist.
SummeWenns
Ein mögliches Problem bei SVerweis ist, dass es nur den ersten Treffer zurückgibt. Wenn ich etwa die Summe der Obstartikel im Lager berechnen möchte, kann SUMMEWENNS alle Treffer berücksichtigen und summieren.
=SUMMEWENNS(tbl_Produkte[Anzahl auf Lager];tbl_Produkte[Kategorie];$F$2)
In Kombination mit der Funktion ZÄHLENWENNS kann ich auch die Anzahl der Treffer ermitteln.
=TEXTVERKETTEN(": ";0;ZÄHLENWENNS(tbl_Produkte[Kategorie];$F2);SUMMEWENNS(tbl_Produkte[Anzahl auf Lager];tbl_Produkte[Kategorie];$F2))
Filter
Möchte ich alle Treffer sehen, kann ich auch die neue Funktion FILTER verwenden. In meinem Fall kombiniere ich sie mit der MTRANS Funktion, um die Ergebnisse in einer Reihe anzuzeigen.
=MTRANS(FILTER(tbl_Produkte[Produktname];tbl_Produkte[Kategorie]=$F2))
Fazit
Obwohl SVerweis eine sehr populäre Funktion in Excel ist, gibt es viele alternative Methoden, die je nach Situation besser geeignet sein können. Die Wahl der richtigen Methode hängt von der Art der Daten und den speziellen Anforderungen der Auswertung ab. Es lohnt sich daher, mit verschiedenen Ansätzen zu experimentieren, um herauszufinden, welche für die eigenen Bedürfnisse am besten geeignet ist.