Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


[Excel] Die Funktion =SVERWEIS()
#1
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:

 CDEF
2Eingabe Pers.-Nr.NameVornameBeruf
3    
4    
5Pers.Nr.NameVornameBeruf
612MeierKarlBuchhalter
727MüllerUteSekretärin
836SchulzWernerVerkäufer

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:

 CDEF
2Eingabe Pers.-Nr.NameVornameBeruf
327Müller  
4    
5Pers.Nr.NameVornameBeruf
612MeierKarlBuchhalter
727MüllerUteSekretärin
836SchulzWernerVerkäufer

ZelleFormel
D3=SVERWEIS(C3;C6:F8;2;0)

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)

 CDEF
2Eingabe Pers.-Nr.NameVornameBeruf
327MüllerUte 
4    
5Pers.Nr.NameVornameBeruf
612MeierKarlBuchhalter
727MüllerUteSekretärin
836SchulzWernerVerkäufer

ZelleFormel
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:

 CDEF
2Eingabe Pers.-Nr.NameVornameBeruf
327MüllerUteSekretärin
4    
5Pers.Nr.NameVornameBeruf
612MeierKarlBuchhalter
727MüllerUteSekretärin
836SchulzWernerVerkäufer

ZelleFormel
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:

 CDEF
2Eingabe Pers.-Nr.NameVornameBeruf
336SchulzWernerVerkäufer
4    
5Pers.Nr.NameVornameBeruf
612MeierKarlBuchhalter
727MüllerUteSekretärin
836SchulzWernerVerkäufer

ZelleFormel
D3=SVERWEIS(C3;C6:F8;2;0)
E3=SVERWEIS(C3;C6:F8;3;0)
F3=SVERWEIS(C3;C6:F8;4;0)

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):

 CDEF
2Eingabe Pers.-Nr.NameVornameBeruf
325Meier#NV#NV
4    
5Pers.Nr.NameVornameBeruf
612MeierKarlBuchhalter
727MüllerUteSekretärin
836SchulzWernerVerkäufer

ZelleFormel
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:

 BC
2Erzielter UmsatzProvision
3  
4  
5Umsatz bis … EURProvision in &
610.000,003
720.000,005
830.000,007
940.000,009
1050.000,0012

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:

 BC
2Erzielter UmsatzProvision
332.785,267
4  
5Umsatz bis … EURProvision in &
610.000,003
720.000,005
830.000,007
940.000,009
1050.000,0012

ZelleFormel
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:

 BC
2Erzielter UmsatzProvision
326.193,225
4  
5Umsatz bis … EURProvision in &
610.000,003
720.000,005
830.000,007
940.000,009
1050.000,0012

ZelleFormel
C3=SVERWEIS(B3;B6:C10;2;1)

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.
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste