© Klaus-Martin Buss
www.kmbuss.de
Diese Seite ist Teil eines Framesets. Sollte links kein Navigationsmenü angezeigt
werden, bitte
hier klicken ...
SVERWEIS
In den einschlägigen Foren wird mindestens 10 x am Tag eine Frage gestellt, für die die Verwendung der Tabellenblattfunktion =SVERWEIS() die optimale Lösung darstellt. Da sich gerade AnfängerInnen und weniger Geübte mit der richtigen Syntax dieser Funktion schwertun, möchte ich an dieser Stelle einige Erläuterungen anbieten.
Gegeben sei folgende Tabelle:
A
B
C
D
E
F
G
H
1
2
Eingabe
Pers.Nr.
Name
Vorname
Beruf
3
4
5
Pers.Nr.
Name
Vorname
Beruf
6
12
Meier
Karl
Buchhalter
7
27
Müller
Ute
Sekretärin
8
36
Schulz
Werner
Verkäufer
9
Ziel
unserer Übung soll sein, dass nach Eingabe einer
Pers.Nr. in Zelle C3 in den Zellen D3:F3 die entsprechenden Daten
(Name, Vorname und Beruf) zurückgegeben werden.
Dazu schauen wir uns einmal den
Aufbau der Funktion an: =SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
Das 1. Argument - Suchkriterium - erklärt sich fast von selbst: Das ist der Wert (Pers.Nr.), der in C3 eingetragen wird. Nach diesem Kriterium soll ja innerhalb der Matrix gesucht werden.
Somit lautet der erste Teil der Funktion =SVERWEIS(C3;...)
Schon sind wir beim 2. Argument, der Matrix. Darunter versteht man den Bereich des Tabellenblatts, in dem sich unsere auszuwertenden Daten befinden. In unserem Fall befinden sich die Daten in den Zellen C6:F8, also lautet der zweite Teil =SVERWEIS(C3;C6:F8;...)
Das 3. Argument, der Spaltenindex, verlangt nach einer ausführlicheren Erklärung: Wir haben gelernt, dass sich unsere auszuwertende Matrix im Bereich C6:F8 befindet. Der Spaltenindex gibt nun an, aus der wievielten Spalte innerhalb dieser Matrix Werte ausgelesen werden sollen. Wenn wir den Namen auslesen wollen, müssen wir also schauen, in der wievielten Spalte unserer Matrix (und nicht in der wievielten Spalte des Tabellenblatts) die Namen zu finden sind. Wenn unsere Matrix in Spalte C beginnt (C6), brauchen wir den Wert aus der 2. Spalte der Matrix, nämlich Spalte D (und nicht - wie man eventuell annehmen könnte - aus der 4. Spalte des Tabellenblatts.
Das dritte Argument =SVERWEIS(C3;C6:F8;2;..)
Das 4. Argument ist optional, das heißt, es muss nicht unbedingt angegeben werden und ist ebenfalls "etwas schwerere Kost": Hiermit wird gesteuert, ob SVERWEIS eine genaue oder ungefähre Entsprechung suchen soll. Wird das Argument weggelassen oder auf WAHR (1) gesetzt, wird eine ungefähre Entsprechung zurückgegeben, wenn das Suchkriterium (1. Argument) nicht haargenau gefunden wird. Wird das 4. Argument auf FALSCH(0) gesetzt, werden nur Werte zurückgegegen, die genau dem Suchkriterium entsprechen. Anderenfalls ist der Rückgabewert #NV. Das schauen wir uns etwas später genauer an.
Zunächst aber tragen wir in Zelle D3 unsere bis jetzt entwickelte Funktion mit dem 4. Argument = 0 ein:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Nun wollen wir in Zelle E3 den Vornamen auslesen. Die ersten zwei sowie das 4.Argument bleiben also gleich: Wir suchen immer noch nach dem Suchbegriff in C3 und in der Matrix C6:F8. Aber nun brauchen wir den Wert aus der 3. Spalte der Matrix, also lautet unsere Funktion
=SVERWEIS(C3;C6:F8;3;0)
A
B
C
D
E
F
G
H
1
2
Eingabe
Pers.Nr.
Name
Vorname
Beruf
3
27
Müller
Ute
4
5
Pers.Nr.
Name
Vorname
Beruf
6
12
Meier
Karl
Buchhalter
7
27
Müller
Ute
Sekretärin
8
36
Schulz
Werner
Verkäufer
9
Formeln der Tabelle
D3 : =SVERWEIS(C3;C6:F8;2;0)
E3 : =SVERWEIS(C3;C6:F8;3;0)
Zum Schluss holen wir uns noch den Wert aus der 4. Spalte der Matrix und unser Projekt ist fertig:
A
B
C
D
E
F
G
H
1
2
Eingabe
Pers.Nr.
Name
Vorname
Beruf
3
27
Müller
Ute
Sekretärin
4
5
Pers.Nr.
Name
Vorname
Beruf
6
12
Meier
Karl
Buchhalter
7
27
Müller
Ute
Sekretärin
8
36
Schulz
Werner
Verkäufer
9
Formeln der Tabelle
D3 : =SVERWEIS(C3;C6:F8;2;0)
E3 : =SVERWEIS(C3;C6:F8;3;0)
F3 : =SVERWEIS(C3;C6:F8;4;0)
Wenn wir nun das Suchkriterium in C3 ändern, erhalten wir die entsprechenden Rückgabewerte:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Beschäftigen wir
uns noch einmal mit dem 4. Argument: 0 oder FALSCH sorgt
dafür, dass das genaue Suchkriterium in der Matrix vorkommen muss, damit Werte
zurückgegeben werden. Wird dieses Argument hingegen
weggelassen oder auf 1 oder WAHR gesetzt, werden die Werte
zurückgegeben, die dem grössten Wert entsprechen, der
kleiner als das Suchkriterium ist. Voraussetzung ist allerdings in
diesem Fall, dass die Liste der Suchkriterien aufsteigend sortiert
ist.
Hört sich kompliziert an,
ist aber relativ einfach: Nehmen wir an, wir geben die Pers.Nr. 25
ein. Der größte Wert (Suchkriterium), der kleiner
als 25 ist, ist die Pers.Nr. 12. Also werden die Werte der Pers.Nr. 12
zurückgegeben (im folgender Darstellung wurde das 4. Argument
nur in Zelle D3 weggelassen, E3 und F3 liefern #NV, da das 4. Argument
ja auf 0 steht):
A
B
C
D
E
F
G
H
1
2
Eingabe
Pers.Nr.
Name
Vorname
Beruf
3
25
Meier
#NV
#NV
4
5
Pers.Nr.
Name
Vorname
Beruf
6
12
Meier
Karl
Buchhalter
7
27
Müller
Ute
Sekretärin
8
36
Schulz
Werner
Verkäufer
9
Formeln der Tabelle
D3 : =SVERWEIS(C3;C6:F8;2)
E3 : =SVERWEIS(C3;C6:F8;3;0)
F3 : =SVERWEIS(C3;C6:F8;4;0)
Nun kann
man sich natürlich die Frage stellen, warum es das 4. Argument
überhaupt gibt, wenn wir doch die Werte der exakt eingegebenen
Pers.Nr. benötigen. Nun, je nach Tabelle kann es durchaus
sinnvoll sein, den grössten Wert kleiner Suchkriterium
auszulesen.
Stellen wir uns vor, dass wir für einen Handelsvertreter die
Provision nach erzieltem Umsatz berechnen müssen. Unsere
Provisionstabelle könnte so aussehen:
A
B
C
D
1
2
Erzielter
Umsatz
Provision
3
4
5
Umsatz
bis ... EURO
Provision
in %
6
10.000,00
3
7
20.000,00
5
8
30.000,00
7
9
40.000,00
9
10
50.000,00
12
11
Nehmen wir an, der Umsatz
unseres Vertreters beträgt 32.785,26 EUR. Wenn wir nun mit dem
4. Argument gleich 0 oder FALSCH arbeiten wollten, müssten wir
eine Umsatztabelle erstellen, die in Spalte B jeden Wert zwischen 0,01
EUR und 50.000,00 EUR enthält, damit der gesuchte Umsatz von
32.785.26 EUR exakt gefunden wird. Wir hätten es also mit
einer Tabelle mit 5 Mio. Zeilen zu tun !!!
Hier hilft uns das 4. Argument, entweder weggelassen oder auf 1 oder
WAHR gesetzt:
B
C
2
Erzielter
Umsatz
Provision
3
32.785,26
7
4
5
Umsatz
bis ... EURO
Provision
in %
6
10.000,00
3
7
20.000,00
5
8
30.000,00
7
9
40.000,00
9
10
50.000,00
12
Formeln der Tabelle
C3 : =SVERWEIS(B3;B6:C10;2)
Wir stellen fest: Das 4. Argument wurde weggelassen, es wurde der Wert (7) zurückgegeben, der dem grössten (nämlich 30.000) Wert kleiner Suchkriterium (32.785,26) entspricht.
Das Ganze noch einmal mit geändertem Umsatz und explizit angegebenem 4. Argument:
|
||||||||||||||||||||||||||||||||
Ihr seht, dass der Angabe des 4. Arguments eine nicht zu unterschätzende Bedeutung zukommt, obwohl SVERWEIS keine Fehlermeldung bringt, wenn es nicht angegeben ist. Unter Umständen werden jedoch logisch falsche Werte zurückgegeben. Denkt noch einmal zurück an unsere in der Suchmatrix nicht vorhandene Pers.Nr. 25, die den Wert der Pers.Nr. 12 zurückgegeben hat.
Zum Schluss dieser kleinen Exkursion fehlt noch der Hinweis, dass die Funktion =SVERWEIS() eine Matrix immer nur von links nach rechts durchlaufen kann. Daraus folgt, dass der Spaltenindex immer eine positive Zahl sein muss.