Sverweis – Fast jeder Excelanwender hat diese Funktion bei seiner Tätigkeit gebraucht. Diese Funktion gibt anhand eines Suchbegriffes den Wert einer Spalte zurück. Bei grossen Datenmengen und der Datenüberprüfung ist die Funktion ein richtiger Helfer. Die Funktion «Sverweis» und wichtige Tipps und Tricks lernen Sie in diesem Blog.
Wann macht der Sverweis Sinn?
Sverweis kommt beispielsweise dann zum Einsatz,
- wenn ein Resultat nach einem bestimmten Suchkriterium gesucht wird.
- wenn zwei Tabellen miteinander verglichen werden.
- wenn eine Tabelle um eine zweite Tabelle erweitert wird.
Wie funktioniert der «Sverweis»?
Am Besten starten Sie direkt mit dem Üben. Öffnen Sie dafür Excel und versuchen Sie das Geschriebene direkt umzusetzen. Die Daten finden Sie auf Wikipedia im Kapitel „Lebenserwartung“. Kopieren Sie die Daten und fügen Sie es im Excel ein.
Die Formel für den Sverweis hat folgenden Aufbau:
=Sverweis(Suchkriterium; Matrix; Index; Bereichs-Verweis)
| Suchkriterium | Mit diesem Kriterium wird in der ersten Spalte der Matrix gesucht. |
| Matrix | In diesem Bereich wird in der ersten Spalte nach dem Suchkriterium gesucht. Der Rückgabewert ist die Zahl, welche im Index definiert wird. |
| Index | Hier wird an Excel die Spalte signalisiert, welcher zurückgegeben werden soll. |
| Bereichs-Verweis | Hier kann zwischen Richtig (ungefähre Übereinstimmung) oder Falsch (genaue Überstimmung) ausgewählt werden. |
Beispiel:
Die nächste Abbildung zeigt die angewendete Formel. Die Formel steht in der Zelle «C4». Der Formeltext steht in der Nachbarzelle «D4». In der Zelle «C3» steht der Suchbegriff „Spanien“.
In der Datei wird nach «Spanien» in der Spalte «E» gesucht. Der Rückgabewert ist die zweite Spalte der Matrix („Lebenserwartung (Gesamt)“).
Tipp 1 : Funktion « Wennfehler» in Kombination mit dem Sverweis
«#NV» – dies ist der Rückgabewert, sobald der Suchbegriff nicht in der Suchmatrix vorhanden ist. Mithilfe der Funktion «Wennfehler» kann der Rückgabewert bei einem Fehler überschrieben werden. Der Rückgabewert ist verständlicher und optisch ansprechender.
Die Formel hat folgenden Aufbau:
=Wennfehler(Wert;Wert_Falls_Fehler)
Beispiel:
In der nächsten Abbildung ist die verschachtelte Formel in der Zelle «D9» ersichtlich. Wie zu erkennen ist, kann aufgrund des Suchbegriffs «Spanie» kein Wert in Spalte E gefunden werden.
Tipp 2: Dropdown als Unterstützung
Aufgrund von Tippfehler im Suchbegriff, kann es sein, dass kein Resultat gefunden wird. Um diese Tippfehler zu vermeiden, hilft eine Dropdown-Liste.
Um in der Übungsdatei eine Dropdown-Liste zu erhalten, müssen folgende Schritte unternommen werden.
- Die Zelle auswählen, in der die Dropdown-Liste sein soll (Bsp. Zelle „C13“).
- Klicke nun auf «Daten»
- Bei «Datenüberprüfung» auf «Datenüberprüfung» klicken
- Bei «Zulassen» wählen wir die «Liste» aus.
- Bei Quelle wird der Bereich «E2:E30» auswählt.
- Bestätigen mit «OK».
Nun kann mithilfe des Dropdowns der korrekte Suchbegriff ausgewählt werden.
Tipp 3: Sverweis mit «*» oder «?»
Anstelle eines Dropdowns kann ein Platzhalter zur Hilfe genommen werden. «*» ist gleichzusetzen mit einer beliebigen Anzahl Zeichen (Null oder mehr). Der Platzhalter «?» steht für exakt ein Zeichen. Eines dieser Zeichen kann in Verbindung mit dem Suchbegriff auf den Sverweis verkettet werden.
Die Formel hat folgenden Aufbau:
= Verketten(«*»;Suchbegriff; «*»)
Die erste Formel wird nun in den Sverweis eingefügt.
Somit lautet die neue Formel:
=Sverweis(Verketten(«*»;Suchbegriff;«*»);Matrix;Spalteninden;FALSCH)
Beispiel:
In der nächsten Abbildung ist der Formeltext in der Spalte D ersichtlich. In der Zelle «C19» wird mithilfe der drei Zeichen aus Zelle «C18» gesucht. In der Zelle «C25» wird mithilfe der sechs Zeichen aus Zelle «C24» gesucht. Dies zeigt deutlich den Unterschied zwischen dem Platzhalter «*» und «?» auf.
