Der heutige Beitrag behandelt eine mächtige und universell einsetzbare Funktion in Excel, die auch zu meinen absoluten Favoriten gehört: Den SVERWEIS!
Was ist der SVERWEIS? Ganz allgemein gesprochen kann man mit dieser Funktion einen bestimmten Wert in einer Liste nachschlagen. Damit lassen sich z.B. folgende Aufgabenstellungen lösen:
- Prüfen, ob ein bestimmter einzelner Wert in einer Liste enthalten ist
- Abgleichen zweier kompletter Listen gegeneinander
- Anreichern von Daten in einer Liste mit den Inhalten aus einer anderen Liste
Hat man erst einmal die Funktionsweise verstanden, sind die Anwendungsmöglichkeiten fast so universell wie ein Schweizer Taschenmesser.
Der SVERWEIS durchsucht die erste Spalte einer Liste senkrecht von oben nach unten (daher das „S“ in SVERWEIS) und gibt bei einem Treffer den Wert aus einer Spalte rechts davon wieder.
Das war noch ein wenig zu abstrakt? Die folgenden Erklärungen und praktischen Beispiele verdeutlichen die Anwendung ganz bestimmt.
Anwendung
Bevor wir uns aber die praktische Umsetzung ansehen, ist noch etwas Theorie notwendig.
Die Syntax für den SVERWEIS lautet wie folgt:
=SVERWEIS(Kriterium; Liste; Spaltenindex; Übereinstimmung)
Kriterium:
Was/welchen Wert suche ich? Hier gebe ich entweder direkt den Wert an (z.B. die Zahl 345 oder einen in Anführungszeichen gesetzten Text), oder ich verweise auf eine Zelle, in der mein Suchkriterium steht, z.B. C5
Liste:
Wo (in welcher Liste) suche ich? Hier gebe ich einen Adressenbereich an, z.B. F10:H5000
Spaltenindex: Gesucht wird das Suchkriterium immer in der ersten Spalte der zuvor angegebenen Liste. Aber aus welcher Spalte möchte ich mir mein Ergebnis holen?
Übereinstimmung:
Suche ich nur nach einer ungefähren Übereinstimmung mit meinem Suchkriterium, dann gebe ich hier als Parameter den Wert WAHR an oder lasse den Parameter ganz weg. Excel liefert dann auch den nächstgrößeren Wert, der kleiner ist als mein Suchkriterium. Wichtig: Die Liste muss dazu sortiert sein.
Möchte ich jedoch nur eine exakte Übereinstimmung finden, dann muss ich den Parameter FALSCH angeben. Wenn Excel keine exakte Übereinstimmung findet, wird der Fehler „#NV“ zurückgeliefert.
So, nach dem theoretischen Geschwafel kommen jetzt endlich die Beispiele!
Beispiel 1: Staffelpreise
Mit einer Staffelpreisliste wird abhängig von der gekauften Menge ein günstigerer Preis gewährt. Ich habe dazu eine kleine Preisliste vorbereitet und möchte mir von Excel immer den richtigen Preis zu meiner angegebenen Menge ermitteln lassen.
Staffelpreise
Excel liefert also für die Menge 10 den korrekten (günstigeren) Preis von 98 Euro zurück.
Im nächsten Bild suchen wir den Preis für 23 Stück, eine Menge, die so nicht explizit in der Preisliste aufgeführt ist.
Staffelpreise Beispiel 2
Wie man sieht, findet Excel trotzdem den korrekten Preis. Voraussetzung dafür waren zwei Dinge:
– Die Preisliste ist aufsteigend nach Menge sortiert
– Wir haben den vierten Parameter („Übereinstimmung“) in der SVERWEIS-Funktion weggelassen
Anders sähe das Ergebnis hingegen aus, wenn wir diesen vierten Parameter auf FALSCH gesetzt und Excel somit bei der Suche zu einer genauen Übereinstimmung gezwungen hätten:
Exakte Übereinstimmung
Somit findet Excel keinen Preis, da die gesuchte Menge 23 in der Preisliste nicht explizit aufgeführt ist.
Warum also sollte ich dann überhaupt diesen Parameter auf „FALSCH“ setzen?
Beispiel 2: Geburtsdatum
Es gibt viele Fälle, bei denen es sehr wichtig ist, dass Excel nur exakt übereinstimmende Treffer anzeigt, und nicht einfach nur den nächsten ähnlichen Wert nimmt.
Wir wollen im folgenden Beispiel zu einem Namen aus einer Liste das dazugehörige Geburtsdatum per SVERWEIS heraussuchen lassen.
Geburtsdatum
Die Namensliste ist alphabetisch sortiert und Excel liefert für den Namen „Müller“ das korrekte Geburtsdatum zurück.
Was passiert aber, wenn es den gesuchten Namen in der Liste gar nicht gibt?
Falsches Geburtsdatum
Da wir nicht auf einer exakte Übereinstimmung bestanden haben, liefert der SVERWEIS fatalerweise hier ein völlig falsches Ergebnis!
Mit der Angabe des vierten Parameters „FALSCH“ können wir das jedoch vermeiden:
Geburtsdatum nicht gefunden
Der gesuchte Name ist nicht in der Liste und die SVERWEIS-Funktion liefert jetzt korrekterweise einen Fehler „#NV“ zurück.
Merke:
Es kommt beim SVERWEIS immer auf die genauen Anforderungen an, ob ich den vierten Parameter zwingend setzen muss oder nicht.
Ungefähre Übereinstimmung reicht aus („Passt schon“):
→ Parameter kann weggelassen werden
Exakte Übereinstimmung ist erforderlich („Passt schon“ reicht also nicht):
→ Parameter muss auf „FALSCH“ gesetzt werden
Dann kann auch nichts Schiefgehen!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Pingback: SVERWEIS, wie du ihn garantiert noch nicht kennst! | Der Tabellen-Experte
Hallo und Respekt, sehr gut erklärt. Ich verwende dieses Tool ab sofort für den Abgleich mit Embargo-/Sanktionslisten.
Hallo Martin,
nun habe ich so langsam den SVERWEIS verstanden. Nun habe ich eine große fertige Tabelle mit Zahlen, diese sollen sich prozentual ändern. Das ist mir gelungen, nun kopiere ich in jede Zelle (stehen ja Werte drin) er das = dann folgt die Zahl und nun kopiere ich den Rest hinein, sehr sehr zeitaufwendig. Gibt es eine Möglichkeit dies zu vereinfachen? die Formel sieht dann immer so aus:=55*(1-$C$41)*(1-F41)*(1+I41)
Gruss Joachim
Hallo Joachim,
ich schlage vor, die setzt die Formel in eine leere Spalte neben die Werte und verwendest statt der Zahl einen Verweis auf die Zelle mit der Zahl.
Bezogen auf Dein Beispiel:
Wenn die Zahl 55 in Zelle A41 steht, wäre die Formel (in einer eigenen leeren Zelle):
=A41*(1-$C$41)*(1-F41)*(1+I41)
Und dann kannst Du die Formel einfach in alle anderen Zeilen kopieren.
Schöne Grüße,
Martin
Hallo Martin, danke für deine Antwort aber das funktioniert nicht. Wahrscheinlich habe ich mißverständlich geschrieben. In jeder dieser vielen Zellen steht eine andere Zahl, die ich um den prozentualen Wert erhöhe oder vermindere (siehe Formel) dieses soll und muss aber in der gleichen Zelle erfolgen. Wenn ich deinen Weg gehe erscheint aber der Wert in der Zelle die du mir beschrieben hast.
Gruß Joachim
Hallo Joachim,
wenn die genannten Zahlen feste Werte sind und wenn die Formeln unbedingt auch in die gleiche Zelle müssen, dann wird dir vermutlich nichts anderes übrig bleiben, als Zelle für Zelle die Formel so einzugeben, wie Du es eingangs beschrieben hast. Ich kenne jetzt Deine Tabelle und die konkreten Anforderungen nicht, aber vielleicht wäre es auch eine Option, die von mir vorgeschlagene Formel in einer freien Spalte zu verwenden und dann die Ergebnisse über Kopieren – Inhalte Einfügen – Werte in die Zellen mit den ursprünglichen Zahlen zu kopieren. Damit hast Du zumindest weniger Eingabearbeit, allerdings sind die Ergebnisse dann auch wieder statisch.
Schöne Grüße,
Martin
Hallo Martin,
danke für den tollen Artikel. Ich nutze selbst den SVERWEIS regelmäßig, bin jetzt aber auf ein Problem gestoßen für das ich bisher keine Lösung gefunden habe. Bei dem SVERWEIS gebe ich ja den Spaltenindex meiner Matrix an, der wieder gegeben werden soll. Wenn ich allerdings nachträglich in meine Matrix Spalten einfügen, die dazu führen, dass sich der Spaltenindex für meine SVERWEIS Funktion ändert, aktualisiert sich natürlich die SVERWEIS nicht automatisch mit (SVERWEIS Funktion gibt dann den „falschen“ Spaltenindex wieder). Kann man den Spaltenindex irgendwie anders (als numerisch) definieren, so dass das Einfügen von Spalten in meine Matrix keinen Einfluss auf SVERWEIS Funktionen hat? Für ein kurzes Feedback wäre ich sehr dankbar.
Viele Grüße,Diana
Hallo Diana,
ja, das Problem kenne ich. Eine mögliche Lösung wäre, die gesuchte Spalte nicht über einen fixen Index anzugeben, sondern über eine VERGLEICH-Funktion auszuwählen. Zum Beispiel so:
Grüße,
Martin
Hallo Martin,
zunächst einmal, ich bin ganz begeistert von Deiner Seite, hab mir auch schon das eBook bezüglich der Verweise zugelegt und verschlungen.
Ich bin hier im Beitrag vllt. nicht ganz richtig, aber ich hoffe, Du kannst mir einen Tipp für mein Problem geben.
Nehmen wir ein Bsp. aus Deinem Buch, die Tabelle vom Kapitel 2 VERWEIS mit Verkäufer/Region/Umsatz.
Ich möchte nicht nur wissen, in welcher Region Müller verkauft, ich möchte, dass Excel B5:C16 prüft und mit eine Auflistung gibt welche Verkäufer alles in der Region West / Ost / Nord/ Süd arbeitet.
Also eine Spalte mit der Überschrift West und dann darunter alle Namen der Verkäufer von West, etc.
Meine eigene Tabelle ist sehr viel umfangreicher als diese Beispieltabelle, aber das Prinzip ist das gleiche. Würde mich wahnsinnig über eine Lösungsidee von Dir freuen. Bis dahin tüftele ich selber weiter.
Vielen Dank vorab
Cisina
Hallo Cisina,
erst einmal Danke für das nette Feedback und dafür, dass Du mein E-Book gekauft hast.
Das was Du beschreibst, würde ich – vor allem bei umfangreichen Tabellen – über eine Pivot-Tabelle lösen. Die ist extrem schnell und entgegen aller Vorurteile ruckzuck erstellt. Wenn Du bisher noch nicht mit Pivot-Tabellen gearbeitet hast, empfehle ich die beiden Einführungsartikel hier auf dem Blog:
Der beste Freund des Excel-Analysten: Pivot-Tabellen
Der Horror geht weiter: Pivot-Tabellen Teil 2
Das hilft hoffentlich weiter.
Schöne Grüße,
Martin
Das probiere ich aus, vielen lieben Dank 😀
Hallo Martin,
du nennst den vierten Parameter kurz „Übereinstimmung“. Und wenn man eine genaue Übereinstimmung haben will, setzt man ihn auf „falsch“. Klingt das nicht etwas schräg? Ich erkläre diesen Parameter (der ja in der Hilfe echt seltsam beschrieben wird) immer wie folgt: Darf der SVerweis davon ausgehen, dass die Liste sortiert ist?
falsch:
Die Liste ist nicht sortiert, suche bis zum bitteren Ende. Findest du nichts, dann sag’s halt (#NV).
wahr:
Die Liste ist sortiert. Sobald du laut Sortierung nichts mehr finden kannst, gib mir halt den letzten Treffer.
Hast du Erfahrungen mit sehr langen Listen? Kann man da mit diesem Parameter und einer Sortierung Zeit retten?
Unabhängig von meiner Wichtigtuerei, danke für deine Beschreibungen! Ich empfehle den Tabellenexperten sogar, wenn ich nicht gefragt werde 🙂
Hannes
Hallo Hannes,
keine Angst, so etwas empfinde ich nicht als Wichtigtuerei 🙂
Ich bin selbst nicht glücklich mit der Formulierung und die offizielle Bezeichnung für diesen Parameter („Bereich_Verweis“) ist auch nicht gerade erleuchtend. Deine Erklärung mit der Sortierung finde ich dagegen ziemlich gelungen, die werde ich mir merken.
Was die Geschwindigkeit angeht:
Eine Sortierung kann tatsächlich eine merkliche Verbesserung bringen. Dazu gibt es übrigens einen schönen Artikel bei Andreas Thehos:
https://thehosblog.com/2014/05/18/excel-sverweis-fur-sortierte-listen-sehr-schnell/
Ich persönlich vermeide bei wirklich langen Listen (mehrere 10.000 Zeilen) mittlerweile den SVERWEIS und greife stattdessen häufig auf PowerQuery („Daten abrufen und transformieren“) zurück. Das ist flexibler und um Welten schneller.
Schöne Grüße (und danke für die Empfehlungen!)
Martin
Hallo Martin,
der SVERWEIS ohne den 4.Parameter zu setzen hat mir schon sehr geholfen. Allerdings bräuchte ich das ganze nun so:
An Hand deines Bsp. suche ich den Preis für 23 Stck. Nun soll der Preis aber in meinem Beispiel nicht 95 sondern 90 EUR ergeben, also der Preis staffelt sich ab 21 Stück schon zur nächst höheren Kategorie. Gibt es da auch eine Möglichkeit? Lieben Dank Andra
Hallo Andra,
wenn ich dich richtig verstehe, dann sind das bei dir keine „ab…“-Preise, sondern „bis…“-Preise.
Eine tatsächlich kniffelige Angelegenheit, für die mir mit dem SVERWEIS spontan auch keine Lösung eingefallen ist.
Aber es geht mit einer Matrixformel bestehend aus MAX und WENN. Bezogen auf mein Beispiel wäre das:
{=MAX(WENN(A5:A9>=B1;B5:B9))}
Wichtig: Die Eingabe muss mit der Tastenkombination Strg+Umschalt+Enter abgeschlossen werden.
Und wenn du Excel 2019 oder Office 365 im Einsatz hast, geht es noch einfacher mit der MAXWENNS-Funktion.
=MAXWENNS(B5:B9;A5:A9;“>=“&B1)
Die braucht man dann nicht als Matrixfunktion einzugeben.
Schöne Grüße,
Martin
Lieber Martin,
vielen vielen Dank für die tollen Tipps hier auf Deiner Seite die mir schon vieles erelichtert haben
Leider habe ich ein sehr kniffliges Problem und werde nicht fündig – während die Funktion SVERWEIS mit EINEM Suchkriterium hervorragend funktioniert, finde ich keine Lösung für ZWEI ODER MEHRERE Suchkriterien über Komma getrennt in einer Zelle der Spalte A die mir die zugehörigen Werte in Spalte B mit Kommas getrennt ausgibt..
Am Beispiel:
Wenn der passende Wert zu „xn“ „yn“ ist und die Zelle für die Suchkriterien in Spalte A enthält „xa, xb, xc“ dann soll in der Ausgabezelle der Spalte B „ya, yb, yc“ enthalten sein.
Gibt es dafür eine Möglichkeit?
Gruß, Veronika
Hallo Veronika,
ich bin mir nicht sicher, ob ich dich richtig verstanden habe:
Die Spalte A enthält also mehrere durch Komma getrennte Werte (also z.B. xa, xb, xc). Und jeder dieser Werte wird einzeln in einer Referenztabelle aufgelistet, in der dann zu jedem Wert ein passender zusätzlicher Wert steht.
Wenn ich das richtig verstanden habe, könnte eine Lösung so aussehen:
Voraussetzung dafür ist aber Excel aus Microsoft 365, denn die Funktion TEXTTEILEN gibt es nur dort. Ansonsten sieht es schlecht aus.
Schöne Grüße,
Martin
Lieber Martin,
ja, genau das habe ich gemeint.
Die Formel wird zwar angenommen (würde vermutlich funktionieren) aber ich bekomme ein „#NAME?“ zurück – was vermutlich an der EXCEL Version 16.75.2 und an dem Umstand liegt, dass die Fubktion TEXTTEILEN in dieser Version nicht funktioniert.
Vielen herzlichen Dank dennoch.
Ich muss leider eine andere Lösung finden.