SVerweis einfach erklärt
Eine Funktion, die mich lange Zeit den letzten Nerv gekostet hat, ist der SVerweis.
Jedes Mal wenn es darum ging, den SVerweis zu schreiben, war ich unsicher. Ich hatte Angst, die Formel nicht richtig zu schreiben und einen Fehler angezeigt zu bekommen. Darauf folgten direkt die Zweifel, ob das Ergebnis richtig sein würde.
Auch nach vielen hundert Anwendungen gehört die Funktion nicht unbedingt zu meinen Lieblingsfunktionen in Excel. Ich sag es mal so: wir haben uns arrangiert. Aus diesem Grund habe ich mich entschieden, eine umfassende Anleitung für die häufig benötigte und gleichzeitig gefürchtete Excelfunktion zu schreiben:
=SVERWEIS(Suchkriterium ; Matrix ; SpaltenIndex ; Bereich_Verweis)
Der SVerweis hat die Fähigkeit passende Informationen in einer zweiten Tabelle zu suchen und in deiner Haupttabelle anzuzeigen. Erfahre in diesem Beitrag:
- Wie du die Funktion aufbaust.
- Wie du Probleme mit der Verweis-Funktion behebst.
- Wie du deinen SVERWEIS selber kontrollieren kannst.
- Warum der Verweis zu der Weiterentwicklung deiner Datenkompetenz beiträgt.
Viel Spaß!
SVerweis ein Beispiel
Wozu kann ich den SVERWEIS einsetzen?
Das Beispiel zeigt den klassischen Einsatz des SVERWEIS: Du holst zusätzliche Informationen aus einer anderen Tabelle.
Anders ausgedrückt kombinierst du mit dem SVERWEIS Daten aus zwei Tabellen in einer.
Die SVerweis “Formel” kannst du außerdem dazu nutzen, (Spalten aus) zwei Excel-Tabellen miteinander zu vergleichen. Für den Vergleich von Spalten aus zwei Dateien ist der SVerweis nur unter bestimmten Voraussetzungen geeignet.
Im Allgemeinen empfehle ich dir, den Verweis sparsam einzusetzen. Die Anzahl der Datensätze sollte überschaubar sein. Ich persönlich nutze die SVERWEIS Funktion nur bis ca. 15.000 Datensätze. Und schon hier kann es passieren, dass Excel bei der Berechnung schwächelt.
Wie funktioniert ein SVerweis?
- Du benötigst immer zwei Tabellen:
- Tabelle Nummer 1 soll mit weiteren Infos erweitert werden = Haupttabelle
- Tabelle Nummer 2 enthält die fehlenden Inhalte = Lookup-Tabelle
- In beiden Tabellen benötigst du ein gemeinsames Kriterium. Darüber werden beide Tabellen miteinander verbunden.
- Idealerweise ist das gemeinsame Kriterium in der Lookup-Tabelle nur einmal (eineindeutig) enthalten.
- Das gemeinsame Kriterium hat in beiden Tabellen das gleichen Format. → Läge unsere Kundennummer einmal als Zahl und einmal als Text vor, würde der Verweis nicht funktionieren.
- Habe den Mut den Verweis auszuprobieren. Los geht’s!
Die Funktion in meinen Worten
Ich habe die Formel schon unendlich oft getippt, trotzdem muss ich mich bei der Eingabe konzentrieren.
Mit diesem langen Satz, baue ich die Funktion Schritt für Schritt auf:
Ich denke laut und tippe (z.B. in die erste freie Spalte hinter deiner Haupttabelle und dort in die erste Datenzelle)
- ist gleich → SVERWEIS → Klammer auf → “Suche dieses Kriterium” → (Klick) → Semikolon
- “in diesem Bereich der Lookup-Tabelle” (markieren) → (Fixiere mit F4) → Semikolon
- gib mir den eins, zwei, x-ten Wert zurück (Zahl schreiben) → Semikolon
- und zwar FALSCH (tippen oder auswählen) zurück → Klammer zu → ENTER
Tadaaaaa – hier ist die fertige Haupttabelle aus dem obigen Beispiel mit der ergänzten Kundennummer und Stadt.
(Für den Fall, dass dir das zu schnell ging: weiter unten wird’s ausführlicher.)
Häufige Fragen
Was ist das Suchkriterium?
Das Suchkriterium ist das erste Funktionselement des SVERWEIS(). Du klickst die Zelle in deiner Haupttabelle an, in der das Suchkriterium das erste Mal vorkommt. (In der gleichen Zeile, in der du gerade die Formel schreibst.)
Was ist die Matrix?
Die Matrix ist der Bereich in deiner Lookup-Tabelle, in dem du die zusätzlichen Informationen finden kannst.
Die erste Spalte deiner Matrix muss das gemeinsame Kriterium enthalten. Du erweiterst den Bereich bis mindestens zu der Spalte und Zeile, in denen deine Wiedergabewerte gefunden werden können.
Was ist der Spaltenindex?
Den Spaltenindex ermittelst du in der Lookup-Tabelle die Position deines Rückgabewerts.
Du fängst bei der ersten Spalte der markierten Matrix an zu zählen: 1, 2, …
Möchtest du als Rückgabewert den Kundennamen in deiner Haupttabelle haben, lautet der Index in unserem Beispiel 2.
Möchtest du als Rückgabewert die Stadt aus der Lookup-Tabelle holen, ist der Spaltenindex 3 .
Soll ich WAHR oder FALSCH im Bereich_Verweis angeben?
Meine klare Empfehlung lautet: Gib immer FALSCH an!
Im letzten Element “Bereich_Verweis” hast du die Möglichkeit zwischen einer ungefähren Übereinstimmung (“WAHR”) und einer exakten Übereinstimmung (“FALSCH”) zu wählen. Ich habe in meiner langjährigen SVerweis Erfahrung immer nur eine exakte Übereinstimmung des Suchkriterium, sprich FALSCH, ausgewählt bzw. eingetippt.
Gibt es in Excel einen Funktions-Assistenten?
Für (fast) jede Excel-Funktion kannst du den Funktionsassistenten über das fx Symbol in der Formelleiste aufrufen.
Für den SVERWEIS sieht der Funktionsassistent so aus:
Du kannst den Funktionsassistenten nutzen, wenn du dich nicht um das Semikolon ( ; ) zwischen den Funktionselementen kümmern möchtest. Außerdem erhältst du für jedes Funktionsargument eine Beschreibung.
VLOOKUP
Im Englischen nennt sich die Funktion VLOOKUP. Vertical Lookup bedeutet so viel wie senkrechter Verweis.
Die Definition aus der englischen Microsoft Excel Dokumentation ist noch einmal eine gute Zusammenfassung in anderen Worten:
“=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).”
Der SVerweis im Überblick
Mit der NewDataWork SVERWEIS-Infografik enthältst du eine Anleitung zum SVerweis , damit du die „Formel“ ab sofort richtig und sicher verwenden kannst.
Probleme mit der Funktion
Der SVerweis gibt #NV als Ergebnis zurück
Bei diesem Fehler findet sich in der Lookup-Tabelle keine Übereinstimmung.
Überprüfe, ob das Suchkriterium in beiden Tabellen genau gleich geschrieben ist und das gleiche Format hat.
Wenn es stimmt, dass dieser Wert nicht gefunden werden kann, kannst du den Fehlerwert #NV mit einer Formelerweiterung überschreiben.
Deshalb nutze die WennFehler-Funktion und lass Excel #NV mit einem Wert deiner Wahl ersetzen:
=WENNFEHLER(SVERWEIS(….);0)
=WENNFEHLER(SVERWEIS(….);”unbekannt”)
Der Rückgabewert steht links (und nicht rechts) vom Suchkriterium.
Alte Excel-Profis würden sagen: “Nutze die Kombination aus Index() und Vergleich()”
Ich muss gestehen, ich habe das nie verwendet. Die pragmatischste Lösung wäre, die Suchkriterium Spalte an den Anfang zu verschieben oder eine Kopie der Spalte an den Anfang zu setzen.
Falls das nicht möglich ist, empfehle ich dir den direkten Umstieg auf den XVERWEIS.
Der VERWEIS lähmt meine Datei
Diese Funktion leistet ganze Arbeit, macht aber aus einer süßen kleinen Datei schnell ein mittleres oder großes Excel-Monster. Lässt sich der Verweis nicht vermeiden, versuche es damit.
- Benötigst du die Informationen nur einmal initial, überschreibe den SVerweis danach mit einer Wertkopie der Ergebnisse ( → Spalte mit den Ergebnissen markieren → rechte Maustaste → Wert einfügen).
- Markiere bei der Auswahl deiner Matrix wirklich nur den betreffenden Bereich und nicht die gesamten Spalten. Ich empfehle dir hier den Einsatz einer intelligenten Tabelle
- Nutze Alternativen zum SVerweis
- Wenn du den SVerweis nicht immer bei jedem Enter neu berechnen lassen möchtest, schalte die Berechnung auf manuell um. (Achtung: Ergebnisse können bis zur nächsten Berechnung falsch sein!)
Der SVERWEIS zeigt kein Ergebnis, obwohl ich eins erwarte
- Stelle sicher, dass das Suchkriterium in der Lookup-Tabelle enthalten ist.
- Manchmal passiert es, dass Suchkriterium in der Haupttabelle und in der Lookup-Tabelle nicht identisch geschrieben sind (z.B. Leerzeichen am Ende). Oder sie haben unterschiedliche Formate (z.B. Zahl vs. Text)
- Prüfe, ob die Suchmatrix richtig fixiert ist. Wenn du sie nicht mit der Funktionstaste F4 fixiert hast ( z.B. $A$1:$C$20), verrutscht der Suchbereich mit jeder SVerweis Zeile weiter nach unten ins Leere. Dann kann kein Wiedergabewert mehr gefunden werden.
- Schau dir an, ob die Suchmatrix in der SVerweis Formel alle relevanten Zeilen enthält. Wenn die Datei wächst, muss der SVerweis mitwachsen.
Checkliste: Prüfe in 10 Schritten, ob du den SVerweis in deiner Datei richtig verwendest
Um auszuschließen, dass beim SVerweis Fehler entstehen, habe ich dir eine Checkliste zusammengestellt. Auch nach vielen hundert Anwendungen des SVerweises, schaue ich mir die Funktion lieber einmal mehr an:
- Ist der SVerweis unbedingt notwendig? Oft ist beispielsweise der Export deiner Daten über ein zusätzliches Feld aus der dahinter stehenden Datenbank erweiterbar.
- Was ist meine Haupttabelle? Welche Informationen möchte ich in meiner Haupttabelle ergänzen?
- Welche Lookup-Tabelle verwende ich? Enthält die Lookup-Tabelle die passende Info (nur einmal!)?
- Ist meine Lookup-Tabelle richtig aufgebaut? → Die Spalte mit dem Suchkriterium befindet sich vor den Spalten mit den Wiedergabe-werten. Es steht also am besten in der ersten Spalte ganz links.
- Habe ich für die Matrix nur den relevanten Bereich ausgewählt? Falls du die Spalten komplett markiert hast (z.B. A:D), kann die Performance deiner Datei darunter leiden. Denn eine xlsx-Arbeitsmappe hat mehr als eine Million Zeilen.
- Habe ich die Bereiche in der Matrix richtig fixiert? ($ Dollarzeichen in der Matrix: z.B. $A$1:$D$20)
- Deckt die Matrix alle Zeilen und Spalten der Lookup Tabelle ab? Prüfe das vor allem, nachdem du Datensätze ergänzt hast. Für länger werdende Lookup-Tabellen eignet sich hervorragend eine Intelligente Tabelle.
- Habe ich am Ende der Funktion FALSCH im SVerweis angegeben? Lässt du dieses Element der Formel weg, bekommst du nur eine ungefähre und keine genaue Übereinstimmung.
- Habe ich Formel-Stichproben im mittleren Bereich und am Ende des Zahlenbereiches gemacht? Sind meine Ergebnisse plausibel und vollständig?
- Ist der Verweis eine einmalige Angelegenheit? Dann macht es Sinn, die Ergebnisse wertzukopieren.
Kannst du all diese Punkte mit Ja beantworten, bist du auf dem besten Weg zum perfekten SVerweis. Herzlichen Glückwunsch!
Was hat der SVerweis mit Datenkompetenz zu tun?
Im Arbeitsalltag mit Excel werden wir den SVerweis immer wieder verwenden.
Beherrschst du den SVerweis, wird es dir leichter fallen, die verschiedenen Arten der Tabellen-Joins zu verstehen. Das ist ein wichtiger Schritt zur Verbesserung deiner Datenkompetenz.
In der Abfragesprache SQL (Structured Query Language) ist das Joinen, also das Zusammenführen von Tabellen, eine wichtige Disziplin. Der SVerweis hat Ähnlichkeit mit dem Left Join. Dabei können an die linke Tabelle (Haupttabelle) Spalten aus einer anderen Tabelle angefügt werden.
Ich hoffe, dass ich dir mit diesem Artikel die Angst vor dem SVerweis nehmen konnte.
Teile gern deine Erfahrungen und Fragen mit mir!
Schreib mir gern
Ich freue mich auf deine Mail! andrea@newdatawork.de
SVerweis - Allgemein
SVerweis - Die Tabellen
SVerweis - Die Funktion
