Die Anleitung wurde mit LibreOffice Calc erstellt, lässt sich aber in Excel genauso anwenden.
Die Syntax vom Sverweis
=SVERWEIS(Suchkriterium;Matrix;Index;Sortierreihenfolge)
Das Suchkriterium ist der Wert, nach dem in der ersten Spalte der Matrix gesucht werden soll, hier steht meist ein Zellbezug z.B. „B4“.
Die Matrix ist der Bereich, in dem in einer Tabelle gesucht wird z.B. „A2:J11“ oder „Tabelle2.A2:J11“. Wenn man vor hat, die Such-Tabelle später zu erweitern oder die Formel in andere Zellen runter zu ziehen, kann man den Wert auch einfach auf „A:J“ oder „Tabelle2.A:J“ reduzieren. Damit erspart man sich auch eine ggf. notwendige absolute Adressierung, da auf diese Weise alle Zeilen eingeschlossen sind.
Der Index gibt die Spalte an, von der der Wert ausgegeben werden soll z.B. „7“. Gezählt wird von links nach rechts ab der ersten Spalte der Matrix. Wenn die Matrix mit „A“ Beginnt, werden die Werte also von Spalte „G“ ausgegeben. Es können lediglich Werte aus Spalten rechts von der Suchspalte angegeben werden.
Die Sortierreihenfolge gibt an, ob die Tabelle nach der ersten Spalte der Matrix aufsteigend sortiert ist. Wird hier eine „0“ oder „FALSE“ eingetragen, bedeutet dies, die Tabelle ist unsortiert. Steht dort eine „1“, „True“ oder nichts, nimmt Excel an, dass die Tabelle sortiert ist.
Zusätzliche Informationen zum Sverweis:
- Beim Sverweis gibt es immer nur ein Suchkriterium
- Der Sverweis gibt immer nur den ersten zutreffenden Wert aus. Gibt es also z. B. Eine Kundennummer doppelt in der Matrix, wird nur die erste Nummer verwendet.
- Der Sverweis unterstützt auch reguläre Ausdrücke (z. B. „A*“ für alles was mit „A“ beginnt)
Beispiel 1: Simples Beispiel der Sverweis Funktion
In diesem Beispiel gibt es ein Tabellendokument mit mehreren Tabellendatenblättern (Reiter). Ebenso kann man aber auch mit mehreren unabhängigen Tabellendokumenten vorgehen.
Im Tabellenblatt „Tabelle2“ befindet sich die folgende Tabelle. In diesem Fall stehen darin frei erfundene Kundendaten.
Im Tabellenblatt 1 soll nun in Zelle „B6“ die Anrede variabel zu einer manuell eingegebenen Kundennummer (gelbe Zelle „B4“) angezeigt werden.
Sverweis Formel erstellen, Schritt für Schritt:
Um genau die Formel aus dem obigen Screenshot zu erstellen gehen Sie wie folgt vor:
1. Zelle „B6“ mit der Maus anwählen
2. „=sverweis(„ tippen
3. Mit den Pfeiltasten oder mit der Maus die Zelle „B4“ auswählen
4. Ein Semikolon „ ; “ setzen (Shift+,)
5. In das 2. Tabellenblatt wechseln (Reiter unten links)
6. Mit der Maus die Matrix auswählen (hier die komplette Tabelle aus Tabellenblatt 2 markieren)
7. Ein weiteres Semikolon setzen
8. Manuell ermitteln, die wievielte Spalte die Spalte „Anrede“ in Ihrer Matrix ist und diese Zahl an diese Stelle schreiben (hier „2“)
9. Wieder ein Semikolon setzten
10. Eine „0“ für die Sortierung tippen.
11. Tippen Sie nun die abschließende Klammer „)“ und drücken die Enter-Taste.
Nun müssten Sie folgende Formel in der Zelle „B6“ stehen haben:
=SVERWEIS(B4;Tabelle2.A2:J11;2;0)
Auf Deutsch würde die Formel folgendes sagen: Suche den Wert aus der Zelle „B4“ in der ersten Spalte der ausgewählten Matrix vom Tabellenblatt „Tabelle2“ und gebe den exakten Wert der 2. Spalte der Matrix aus.
Als Ergebnis wird hier also bei Kundennummer „4“ der Wert „Herr“ ausgegeben.
Ergebnis verfeinern mit Istfehler und Wenn
Wird nun in Zelle „B4“ die Kundennummer geändert, wird von dieser Kundennummer jeweils die Anrede ausgegeben. Wenn allerdings eine unbekannte Kundennummer in die Zelle B4 geschrieben wird, wird ein Fehler (#NV) ausgegeben.
Um dies zu verhindern, empfiehlt es sich, die Istfehler-Funktion zusammen mit der Wenn-Funktion zur Hilfe zu nehmen.
Die mit der Istfehler- und Wenn- Funktion gespickte Formel könnte dann beispielsweise wie folgt aussehen:
=WENN(ISTFEHLER(SVERWEIS(B4;Tabelle2.A2:J11;2;0));"";SVERWEIS(B4;Tabelle2.A2:J11;2;0))
Grob beschrieben sagt man mit der Formel auf Deutsch: Wenn als Ergebnis der Sverweis Funktion ein Fehler ausgegeben wird, gebe nichts aus, ansonsten gebe das Ergebnis der Sverweis Funktion aus.
Die Formel bewirkt lediglich, dass statt eines Fehlers eine leere Zelle ausgegeben wird. Anstatt eine leere Zelle auszugeben, könnte man an der Stelle auch eine 2. Tabelle mit dem Sverweis durchsuchen oder eine alternative Formel ausgeben etc..
Um die Formel richtig zu verstehen, sehen Sie sich bitte die oben verlinkte Istfehler-Funktion und ggf. die Wenn-Funktion an.
Auf diese Weise kann man einen kompletten Datensatz aus dem Tabellenblatt „Tabelle2“ abfragen, das könnte dann so aussehen:
Beispiel 2: Sverweis auf nachstehende Zeilen erweitern
In dem folgenden Praxisbeispiel liegt Ihnen eine Liste vor, in der Kundennummern und ggf. andere Informationen zu Ihren Kunden enthalten sind, für Ihren Zweck benötigen Sie nun auch die passenden Telefonnummern dieser Kunden. Leider stehen diese aber noch nicht in dieser Liste.
Ihre Aufgabe ist nun die Telefonnummern schnellstmöglich in die Liste zu bringen. In diesem Beispiel sind es nur ein Paar Kunden, in der Praxis sind dies aber möglicherweise tausende, da würde es Wochen dauern, die Telefonnummern per Hand in die Liste zu schreiben.
Das Ziel ist es, eine Formel zu generieren, die man auf nachstehende Zeilen erweitern (herunterziehen) kann. Um das Ergebnis zu erreichen, gehen Sie grundlegend genauso vor, wie in Beispiel 1.
In diesem Beispiel schreiben Sie Ihre Formel in die Zelle „B17“. Als Suchkriterium wählen Sie hier die links nebenstehende Kundennummer „A17“.
Bei der Matrix gibt es hier eine Besonderheit, die Sie beachten sollten. Damit es beim Herunterziehen der Formel kein Problem gibt, können Sie entweder die absolute Adressierung anwenden oder alternativ die Zeilenangaben in der Matrix löschen (aus „Tabelle2.A2:J11“ wird Tabelle2.A:J“).
Letzteres hat den Vorteil, dass die Tabelle, auf der die Matrix liegt, ohne Probleme in der Länge erweitert werden kann. Es dürfen also Einträge hinzukommen. Der Nachteil ist, es können für eine problemlose Abfrage nicht 2 oder mehrere verschiedene Tabellen über bzw. untereinander stehen.
Als Spaltenindex tippen Sie eine „10“ für die 10. Spalte also die Telefonnummer ein. Als Sortierung tippen Sie wieder eine „0“ ein.
Nun sollten Sie folgende Formel in der Zelle „B17“ stehen haben:
=SVERWEIS(A17;Tabelle2.A:J;10;0)
Nun können Sie die Formel einfach herunterziehen und somit auf die nächsten Werte automatisch anwenden.
In der Zelle „B18“ würde die Formel dann wie folgt aussehen:
=SVERWEIS(A18;Tabelle2.A:J;10;0)
Hier würde die Formel also automatisch nach dem Wert in Zelle A18 in Ihrer definierten Matrix suchen, und so weiter.
Beispiel 3: Nur einen Teil der Tabelle als Matrix verwenden
In diesem Beispiel liegt Ihnen bezüglich einer Aktion eine Liste mit Faxnummern und evtl. anderen Informationen zu Ihren Kunden oder Geschäftspartnern vor. Da Sie die teilnehmenden Stammkunden anrufen möchten, benötigen Sie die entsprechenden Telefonnummern.
Einziger Unterschied zum 2. Beispiel, es ist eine andere Matrix nötig, um nach den Telefonnummern anhand der Faxnummern suchen zu können. Statt „A:J“ wird hier „I:J“ ausgewählt. Somit wird als Spaltenindex natürlich nicht mehr die „10“ angegeben, sondern die „2“, weil es nun die 2. Spalte der Matrix ist.
Ansonsten wird wie schon in Beispiel 1 gezeigt, die Wenn- und Istfehler Funktion mit in die Formel integriert, damit keine Fehler angezeigt werden..
Die Formel sieht dann wie folgt aus:
=WENN(ISTFEHLER(SVERWEIS(A29;Tabelle2.I:J;2;0));"";SVERWEIS(A29;Tabelle2.I:J;2;0))
Die Formel kann durch das Herunterziehen wieder auf nachstehende Zeilen erweitert werden.
Beispiel 4: Tabelle als sortiert angeben
Um den Sverweis mit einer sortierten Tabelle zu demonstrieren, wurde das Tabellenblatt „Tabelle3“ angelegt. In dieser Tabelle befinden sich nur Nummern und Testwerte, wobei die Nummern aufsteigend sortiert sind. Wenn der Parameter „Sortierreihenfolge“ im Sverweis auf „1“ steht, muss die Liste zwingend aufsteigend sortiert sein.
In allen zuvor verwendeten Sverweis Funktionen wurde für die Sortierreihenfolge am Ende der Formel die „0“ verwendet, um diesen Parameter zu deaktivieren. Um Excel mitzuteilen, dass die Tabelle für die Matrix sortiert wurde, kann stattdessen eine „1“ oder „true“ verwendet werden. Möglich ist auch, diesen Teil der Formel komplett wegzulassen, dann sieht Excel diesen Parameter ebenfalls als aktiv an.
Die Formel sieht nun wie folgt aus und kann auf die nachstehenden Zeilen erweitert werden:
=SVERWEIS(A47;Tabelle3.A:B;2;1)
Die Besonderheit ist, dass Werte für die es einen Treffer gibt, den exakten Wert der Matrix ausgeben und die Werte ohne Treffer den direkt drüber stehenden Wert aus der Matrix ausgeben. Nummer 4 gibt also „1000“ aus, weil die Nummer 4 nicht in Tabelle 3 vorhanden ist und Nummer 3 den Wert „1000" hat. So setzt sich das für den Rest der Tabelle fort.