Auf dieser Seite findest Du kostenlose Downloads mit ausführlichen Erklärungen und Beispielen zur Excel Suchfunktion SVERWEIS. Ergänzt wird das Paket mit einen Flussdiagramm zur korrekten Anwendung und Musterfälle für typischen Fehlern, die oft auch Excel-Profis passieren. Die Vorlagen sind so aufgebaut, dass Du selbst daran weiter experimentieren kannst, bis Dir diese Funktion in Fleisch und Blut übergegangen ist!
Download der Mustervorlagen
Nichts lernst Du leichter als durch probieren! Dies gilt zumindest für den ganzen digitalen Kram!
Wir haben hier für Dich sechs verschiedene Muster-Tabellen mit unterschiedlichen Schwierigkeitsgrad vorbereitet. Jede Excel Tabelle ist so konzipiert, dass die relevanten Daten leicht erkannt und geändert werden können. So lernst Du die Funktionsweise und Tücken dieser mächtigen Excel-Funktion an praktischen Beispielen.
- Hier erhälst Du vorab ein Flussdiagramm, welches Dir zeigt, welche Parameter wie korrekt verwendete werden können: Download Flussdiagrammes
- Anhand zwei einfacher Anwendungen werden dir Tipps zur korrekten und fehlerhaften Verwendung von SVERWEIS gegeben: Download Mini-Beispiele
- Ein weiterführendes simples Beispiel, in dem vor allem die Grenzen der Excelfunktion kannst Du hier herunterladen: Download simples Beispiel
- Speziell für sortierte Matrizen wird sinnvoller Weise bei der Suchfunktion der Bereich_Verweis=WAHR gesetzt: Download Beispiel für WAHR Option
- Ist die erste Spalte der Matrix nicht nicht aufsteigend sortiert, so muss der Bereich_Verweis=FALSCH gesetzt werde: Download Beispiel für FALSCH Option
- Du beherrschst nur das, dessen Grenzen Du auch kennst! Diese Excelfunktion beinhaltet einige versteckte Möglichkeiten, grobe Fehler zu machen. Fallweise sind die falschen Ergebnisse nur sehr schwer zu finden sind. Hier erhälst Du eine Beispiel-Fehler-Sammlung der betrachteten Funktion: Viel Spaß nach dem Download der Fehlersammlung
Wie Excel SVERWEIS berechnet
Diese Funktion durchsucht die erste Spalte einer Matrix nach dem Suchkriterium. Sie durchläuft bei Übereinstimmung die gefundene Zeile nach rechts und gibt den Wert aus einer gewählten anderen Spalte zurück.
Die korrekte Syntax der Suchfunktion lautet:
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
Und schaut korrekt ausgefüllt in der Praxis zum Beispiel so aus:
=SVERWEIS(H5;B6:E21;2;FALSCH)
Suchkriterium
Dies kann ein Wert (zum Beispiel: 3,14) oder ein Bezug auf eine andere Zelle (zum Beispiel: H5) sein. Das Suchkriterium darf nie kleiner sein, als der kleinste Wert der Spalte 1 der Matrix!
Matrix
Die Matrix (zum Beispiel B6:E21) muss aus mindestens zwei Spalten bestehen. Das Suchkriterium wird immer in der ersten Spalte gesucht. Die Werte der ersten Spalte können Zahlen, Texte (keine Unterscheidung zwischen Groß- und Kleinschreibung!) oder Wahrheitswerte sein.
Spaltenindex
Dies ist die Spaltennummer (zum Beispiel: 2), aus der der entsprechende Wert zurückgegeben wird. Er darf natürlich nicht kleiner als 1 oder größer als die Anzahl der Spalten der Matrix sein.
Bereich_Verweis
Der Bereich_Verweis (zum Beispiel WAHR) ist ein Wahrheitswert, der angibt ob nach einer exakten oder ungefähren Entsprechung des Suchkriteriums gesucht werden soll.
- WAHR steht für eine ungefähre Entsprechung und funktioniert nur dann, wenn die Werte der ersten Spalte der Matrix in aufsteigender Reihenfolge sortiert sind!
- FALSCH steht für eine exakte Übereinstimmung mit dem Suchkriterium. Die Werte der serten Spalte der Matrix können durcheinender gewürfelt sein.
- Falls Du hier nichts angibst, also die Option leer lässt, entspricht dies dem Wert WAHR.
Voraussetzungen für die Anwendung
Grundsätzlich müssen die Datensätze zeilenweise vorliegen und spaltenweise gelistet sein: SVERWEIS kann nur senkrecht suchen: das “S” steht für “senkrecht”.
Liegen die Datensätze spaltenweise vor, und soll die erste Zeile nach dem Suchkriterium durchforstet werden, ist die Funktion WVERWEIS zu verwenden: das “W” steht hier für “waagrecht”.
Die weiteren Voraussetzungen sind auf den ersten Blick etwas komplex aber, einmal Verstanden, im Grunde ganz easy:
Im Flussdiagramm, welches Du oben auch gratis als XLSX Datei downloaden kannst, sind die Voraussetzungen in Interaktion miteinander übersichtlich zusammenstellt. Grundsätzlich sind folgende Kriterien zu beachten:
- Wird kein Bereich_Verweise angegeben, oder ist dieser auf WAHR gesetzt, müssen die Werte der ersten Spalte in aufsteigender Reihenfolge sortiert sein.
- Ist der Bereich_Verweis auf FALSCH gesetzt, muss das Suchkriterium exakt einem Wert der ersten Spalte der Matrix entsprechen.
Typische Fehler
Das alles klingt schwierig, ist es aber nicht! Trotzdem unterlaufen gerade bei dieser Funktion auch dem Excel-Freak mitunter hier so mache Peinlichkeiten. Um die Fehler auch gut zu Verdeutlichen, gibt es oben eine Excel Tabelle zum Herunterladen, in der so ziemlich alles falsch gemacht wurde: viel Spaß beim Ausprobieren!
Eine Liste drei häufigsten Fehler:
- =SVERWEIS(H5;B6:E21;2;WAHR): Die Werte der ersten Spalte der Matrix sind aber nicht aufsteigend sortiert.
- =SVERWEIS(H5;B6:E21;2;FALSCH): Das Suchkriterium entspricht aber nicht exakt einem Wert der Spalte H5:H21.
- =SVERWEIS(H5;B6:E21;2;WAHR): Der Wert in H5 ist kleiner als das der kleinste Wert der Spalte H5:H21.
Anwendungensbeispiele
Die Funktion eignet sich im Grunde hervorragend zur Verwaltung kleinerer und mittlerer Datenbanken, welche in einem Excel Sheet vorliegen. Anwendungsbeispiele finden sich im beruflichen und privaten Umfeld gleichermaßen:
Produktverwaltung eine kleinen Betriebs- Bestands- und Inventurlisten
- Mitarbeiter- oder Projektverwaltung
- Verwaltung von privaten Sammlungen (Briefmarken, Münzen, …)
- Kochrezepte, Videothek, …
- und vieles vieles mehr 🙂
Alternativen zu SVERWEIS
Die Funktion SVERWEIS ist sehr mächtig, die Grenzen sind aber bereits klar ersichtlich: Die Suchfunktion ist auf die erste Zeile der Matrix beschränkt; das kann oft sehr lästig sein. In einem eigenen Beitrag zeige ich Dir, wie Du diese Schranke mit einer einfachen VBA Makro Funktion durchbrechen kannst. Wenige Codezeilen werden Dich zu neuen Erweiterungsmöglichkeiten dieser Verweis-Funktion führen.