Das Schweizer Taschenmesser in Excel: SVERWEIS! 11

Listen abgleichen und Daten anreichern mit SVERWEIS ganz einfach.
 

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

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

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

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

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

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

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!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

11 Gedanken zu “Das Schweizer Taschenmesser in Excel: SVERWEIS!

  • Joe

    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

    • Martin Weiß Beitragsautor

      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

  • Joe

    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

    • Martin Weiß Beitragsautor

      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

  • Diana

    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

    • Martin Weiß Beitragsautor

      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:
      Beispiel flexibler SVERWEIS

      Grüße,
      Martin

  • Cisina

    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