Excel Sverweis ganz einfach erklärt!

Sich den Sverweis zu merken und sicher anzuwenden, ist gar nicht so einfach, vor allem bei so vielen komplizierten Anleitungen.

Oder vielleicht nicht?

Mithilfe von Beispielen aus dem Alltag wirst du am Ende des Artikels den Sverweis gemeistert haben und bist einen großen Schritt weiter auf dem Weg zum Excel Hero

Glaubst du nicht?

Dann versuch mir mal zu sagen was der „CARAMEL FRAPPE“ kostet.

Richtig 100!

Wie hast du das bloß gemacht … genau mit dem Sverweis!

Wir haben nach dem „CARAMEL“ Kaffee gesucht und dann in der dritten Spalte geschaut was der Preis für den Kaffee als „FRAPPE“ ist.

Schnellüberblick

1. Wofür steht der Sverweis eigentlich?

Der Sverweis heißt im Englischen „lookup“, was besser mit „Suchen“ oder „Nachschlagen“ hätte übersetzt werden können.

Sverweis = Suchen/Nachschlagen

Das klingt doch schon mal intuitiver als Sverweis.

2. Wo benutzten wir den Sverweis im Alltag?!

Beispiel: Suche im Web

Wo suchen wir bereits jeden Tag?

Z.B. bei Google oder Wikipedia.

Stellen wir uns vor, wir möchten nach der Einwohnerzahl von Deutschland suchen.

1) Wir gehen auf Google und geben unseren Suchbegriff Deutschland und die Information Einwohnerzahl ein. Wir kommen zu einem Wikipedia Artikel, der die Einwohnerzahlen pro Land in einer Tabelle darstellt.

2) Nun suchen wir nach dem Eintrag von Deutschland in der Tabelle, gehen dann in die Spalte, in der die Information für Deutschland steht. Zum Beispiel in der dritten Spalte. Nun haben wir das Ergebnis, welches wir gesucht bzw. nachgeschlagen haben.

Genau das macht auch der Sverweis in Excel, d.h., wenn wir etwas online suchen können, können wir auch den Sverweis sicher anwenden.

Das einzige Problem ist, dass Excel nicht wirklich intuitiv ist und wir uns merken müssen, was Excel von uns möchte, damit die einzelnen Funktionen funktionieren.

3. Sverweis in Excel: Wofür stehen die einzelnen Bestandteile der Formel?

Jetzt schauen wir uns an wofür die einzelnen Formelabschnitte beim Sverweis stehen.

=sverweissenkrechte Suche von oben nach unten(SuchbegriffWonach suchen wir?; MatrixWo suchen wir?; SpaltenindexIn welcher Spalte befindet sich unsere Information?; [Bereich_Verweis])Genaue oder ungenaue Übereinstimmung beim Suchbegriff?

Sverweis = steht für senkrechte Suche von oben nach unten

Es gibt auch einen

Wverweis = steht für waagerechte Suche von links nach rechts

Suchbegriff = steht für den Begriff nach dem wir suchen

Matrix = steht für den Bereich in dem wir nach unserer Information suchen

Spaltenindex = steht für die Spalte in der sich die gesuchte Information befindet

[Bereich_Verweis] = gibt an ob wir mit genauer oder ungenauer Übereinstimmung suchen. Diese Einstellung ist optional.

Optionale Einstellung

Standardmäßig steht der Sverweis auf ungenaue Suche also auf WAHR oder 1

Oft brauchen wir jedoch die genaue Suche, daher ist es wichtig am Ende des Sverweises FALSCH oder 0 einzutragen!

Wahr / 1: ungenaue Suche

Falsch / 0: genaue Suche

4. Anwendung in Excel

Schritt 1) Wo platzieren wir den Sverweis?

Unter einem Feld in dem wir einen Suchbegriff eingeben.

An einer Tabelle, welche wir um eine Spalte erweitern möchten.

Der Vorteil ist, dass man die Formel ganz leicht nach unten erweitern kann.

Schritt 2) Wonach suche ich?

Ich habe die Möglichkeit den Suchbegriff direkt in die Formel zu schreiben oder besser auf eine Zelle zu verknüpfen. In diesem Fall ist oft ratsam, die Verknüpfung mit den $Zeichen zu fixieren. Am besten mit F2 in die Formel springen, den Bezug anwählen und mit dem Shortcut F4 durch die verschiedenen Bezugsarten schalten.

Schritt 3) Wo suchen wir?

Jetzt müssen wir den Bereich auswählen in dem der Sverweis suchen soll. Dabei wird in der ersten Spalte nach dem Suchbegriff gesucht.

Der Bereich muss zudem die Spalten umfassen in denen wir die Informationen nachschlagen.

Oft ist es ratsam den Bereich auf die ganze Tabelle auszuweiten, falls man später nach weiteren Informationen sucht. Dann muss man den Suchbereich nicht extra noch mal anpassen.

Schritt 4) Was will ich wissen?

Jetzt müssen wir schauen in welcher Spalte die Information steht.
Hier haben wir wieder die Möglichkeit direkt in der Formel eine Zahl einzutragen oder mit einer Verknüpfung die Spaltennummer aus einer Zelle zu erhalten.

Schritt 5) Genaue oder ungenaue Übereinstimmung?

Genaue Übereinstimmung
Bei einer genauen Übereinstimmung wird nach dem genauen Suchbegriff gesucht. Dafür müssen wir am Ende des Sverweises FALSCH oder O eingeben. Falls der Suchbegriff nicht gefunden werden kann, wird die Fehlermeldung #NV (Nicht verfügbar) ausgegeben.

Ungenaue Übereinstimmung
Für die ungenaue Übereinstimmung muss WAHR oder 1 am Ende des Sverweises eingegeben werden. In diesem Beispiel möchten wir schauen in welche Gehaltsstufe das Gehalt fällt. Dafür müssen die Gehaltsstufen aufsteigend sortiert sein.

Der Sverweis geht jeden Eintrag von oben nach unten durch und überprüft ob der Eintrag im Suchbereich größer als der gesuchte Wert ist. Wenn das der Fall ist, springt der Sverweis einen Eintrag wieder nach oben.

Z.B. wenn wir nach dem Gehalt von Heinze (42.128€) suchen, geht der Sverweis alle Einträge im Suchbereich durch bis er auf den Wert 44.000€ stößt. Dieser Wert ist größer als 42.128€. Anschließend springt der Sverweis einen Eintrag zurück nach oben auf die 42.000€ und gibt Tarif A2 zurück.

Der Sverweis interpretiert die Einträge im Suchbereich also wie folgt:

40.000€ oder mehr = A1; 42.000€ oder mehr = A2 usw.

5. Was gibt es beim Sverweis zu beachten?

Häufige Fehlermeldungen

#NV
Diese Fehlermeldung erscheint, wenn der Suchbegriff nicht gefunden werden konnte.

Das kann folgende Gründe haben:

1) Der Suchbegriff kommt wirklich nicht vor
2) Man hat sich beim Suchbegriff verschrieben oder im Suchbegriff sind Leerzeichen enthalten, die vorher entfernt werden müssen
3) Der Suchbegriff ist eine Zahl ,die jedoch von Excel als Text interpretiert wird. Dafür müssen wir das Zellformat der Zelle, in der der Suchbegriff steht, auf Zahl umstellen. Alternativ kann man den Suchbegriff mit 1 multiplizieren (*1), dadurch erkennt Excel den Suchbegriff als Zahl.
4) Der Suchbereich ist nicht korrekt eingestellt. Beachte, der Sverweis sucht immer in der ersten Spalte des Suchbereichs.

#BEZUG!
Diese Fehlermeldung tritt auf, wenn man beim Spaltenindex eine Spalte angegeben hat, die sich außerhalb des Suchbereichs befindet. Z.B. wenn man bei einem Suchbereich mit nur zwei Spalten, die Zahl 3 als Spaltenindex angibt.

#WERT!
Diese Fehlermeldung tritt auf, wenn man beim Spaltenindex 0 oder eine negative Zahl einträgt. Der Sverweis schaut immer nur nach rechts, es ist nicht möglich, links von der ersten Spalte zu suchen.

#NAME?
Diese Fehlermeldung tritt auf, wenn man sich bei der Funktion verschrieben hat und anstatt Sverweis z.B. „=serweis“ eingegeben hat.

Sverweis Beispieldatei

Um sich die Formeln anzuschauen, kannst Du entweder rechts unten die Datei herunterladen oder Du lässt dir die Arbeitsmappe in voller Größe anzeigen und gehst dann anschließend auf „Arbeitsmappe bearbeiten“.

Fazit

Jetzt haben wir alles Wichtige zum Excel Sverweis abgedeckt und Du hast eine der meist genutzten Formeln in Excel kennengelernt. Versuche die Formel am besten gleich einmal anzuwenden, denn Übung macht den Meister!

So das war jetzt eine Menge Inhalt. Wenn du schnell jede Menge mit Excel einsparen möchtest, dann habe ich ein kostenloses Webinar-Training zum Thema Pivot-Tabellen für dich.

Klicke hier um zum Pivot-Tabellen Webinar zu kommen

In dem Training zeige ich dir…

  1. Wie du deine Daten aufbereitest und in weniger als 3 Minuten eine Pivot-Tabelle erstellst!
  2. Wie du KPIs: Entwicklung zum Vorjahr/Vormonat, Top 10 Kunden usw. in einer interaktiven Pivot-Tabelle darstellst und somit deine Fähigkeit große Datenmengen auszuwerten auf die nächste Stufe bringst!
  3. Wie du interaktive Dashboards mit Datenschnitten und Pivot-Charts erstellst und somit aussagekräftige Entscheidungsgrundlagen schaffst!

Ich würde mich freuen dich beim Webinar begrüßen zu dürfen!