Autor: Peter Haserodt  --- Aus Excel Standard - Gruppe: Funktionen

SVerweis

Autor: Peter Haserodt - Erstellt: ?      - Letzte Revision: ?
Da der SVerweis eine der nützlichsten Funktionen in Excel ist, etwas ausführlicher

Den SVerweis gibt es paraktisch in zwei Varianten.
In beiden dient er aber dazu, in einem Bereich auf Grund eines Suchwertes eine Zeile zu finden und in dieser den Wert einer dazugehörigen Spalte zurückzugeben.
Wobei der zu suchende Wert immer in der ersten Spalte des angegebenen Bereiches liegen muss.
 
 ABCDE
1     
2     
3     
4     
5     
6     
7     
8     
 

Der Aufbau des SVerweises ist:
SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
Will ich den Bereich untersuchen, der sich über den Blauen Balken erstreckt, also B5:E8 gilt folgendes:

Suchkriterium
ist der Wert den ich suche - mehr dazu später
Das Argument Matrix ist der Bereich also B5:E8

Der Suchwert wird im Bereich B5:B8 gesucht (hellgrüner Bereich) gleichzeitig ist dieser Bereich auch meine Spalte 1.
Zurückgegeben können die Werte aus den anderen Spalten aber natürlich auch aus der 1.Spalte.

Dies bedeutet, das Argument Spaltenindex kann hier die Werte 1 bis 4 annehmen.

In Worten könnte man dies so ausdrücken:
Der Gesamte Bereich den ich in meinen SVerweis einbeziehe ist der Bereich B5:E8
Finde meinen Suchwert in der ersten Spalte meines Bereiches (also hier B5:B8) und gib mir dann den Wert aus der Spalte zurück, die ich unter Spaltenindex angebe.

Wobei erste Merkregel: Spaltenindex ist immer zu zählen ab der Position der Suchspalte und ist unabhängig davon, wo mein Suchbereich beginnt!


Will ich also in meinem Beispiel die Gelbe Spalte zurückhaben, so ist mein Spaltenindex 3 und nicht, wie man vielleich glauben könnte 4, weil es ja in Spalte D steht.

Untersuche ich aber den Bereich der sich unter dem roten Balken erstreckt (C5:E8) und will die Gelbe Spalte zurückhaben so ist diesmal mein Spaltenindex 2!

Das 4. Argument Bereich_Verweis ist ein ganz besonderes, weil es optional ist (es muß nicht angegeben werden und kann den Wert Wahr oder den Wert Falsch haben. Wobei Wahr auch als 1 geschrieben werden kann und Falsch als 0
Gibt man diese Argument nicht an, so setzt Excel automatisch hier Wahr.
Tatsächlich ist dieses Argument aber eine der Ursachen, dass Benutzer von SVerweis in der Nervenanstalt gelandet sind. Wir werden uns damit noch genauer beschäftigen.

Aber gleich Merksatz 2: Vergesse nie die Matrix sprich den Bereich absolut zu setzen (außer ganz bewußt).
In unserem Fall sollte es also z.B. $B$5:$E$8 richtigerweis lauten.
Tatsächlich bekommt spätestens hier der Psychiater wegen Vergessens dieser Absolutierung einen Haufen SVerweiskunden.

Aber weg von der grauen Theorie hin zum ersten realen Beispiel:
 
 ABCDEFGH
1        
2 Der SVerweis für Werte in einem Bereich suchen 
3        
4 NamePunkteErgebnis PunkteBewertung 
5 Maria10Reell 0Ganz mies 
6 Hans9Reell 2Ziemlich mies 
7 Peter1Ganz mies 5Auch noch mies 
8 Gretel15Abgeguckt 9Reell 
9     11Streber 
10     13Abgeguckt 
11        
Formeln der Tabelle
D5 : =SVERWEIS(C5;$F$5:$G$10;2)
D6 : =SVERWEIS(C6;$F$5:$G$10;2)
D7 : =SVERWEIS(C7;$F$5:$G$10;2)
D8 : =SVERWEIS(C8;$F$5:$G$10;2)
 

Diese Variante des SVerweises dient dazu, einen Suchwert in einem Zahlenstrahl einzuordnen.

Unsere Suchmatrix steht in F5:G10.
Das Thema ist klar, in einem bestimmten Punktebereich gibt es eine Bewertung.
Also von 0-1 , 2-5 usw

Regel: Habe ich eine Suchmatrix, in welcher ein Suchwert eingeordet werden soll, so muss die Suchspalte aufsteigend sortiert sein !


In userem Fall muss in der F Spalte aufsteigend sortiert sein sonst kann es zu Irrsinsergebnissen kommen.

Untersuchen wir nun die Formel in D5:
=SVERWEIS(
C5;$F$5:$G$10;2)

Tatsächlich lautet diese in Wirklichkeit, ohne dass wir es sehen:
=SVERWEIS(
C5;$F$5:$G$10;2;Wahr)

Als erstes beachte man den Absoluten Bezug in
$F$5:$G$10
Ohne diesen, könnten wir die Formel nicht nach unten ziehen !!!

Nun im Einzelnen: in C5 steht mein Suchwert, F5:G10 ist meine Suchmatrix, zurückgegeben soll der Wert aus Spalte 2 der Suchmatrix und es soll nicht der genaue Wert gefunden werden,
sondern der Wert der kleiner oder gleich des Suchwertes ist

(Dafür ist das Wahr verantwortlich)

Nochmals: Wenn nicht aufsteigend sortiert, läuft das nicht!
Tatsächlich funktioniert es aber Excelintern so:

Excel durchläuft die Suchspalte beginnend in Zeile 1 der Matrix.
Es guckt sich an ob in der Wert dort kleiner gleich (<=) dem Suchwert ist.
Ist dies falsch, dann gibt Excel einen Fehler zurück (#NV)
Ansonsten merkt sich Excel die Zeile und läuft weiter in den Zeilen, bis es einen Wert findet, der nicht <= des Suchwertes ist.
Dann gibt Excel die dazugehörende Spalte der letzten gefundenen Zeile zurück.

Nehmen wir unser Beispiel und den Suchwert 10:

- Excel guckt in Zeile 1 der Suchmatrix und findet dort den Wert 0. 0 ist <= 10 also merkt sich Excel Zeile 1.
- Excel guckt in Zeile 2 der Suchmatrix und findet dort den Wert 2. 2 ist <= 10 also merkt sich Excel Zeile 2.
- Excel guckt in Zeile 3 der Suchmatrix und findet dort den Wert 5. 5 ist <= 10 also merkt sich Excel Zeile 3.
- Excel guckt in Zeile 4 der Suchmatrix und findet dort den Wert 9. 9 ist <= 10 also merkt sich Excel Zeile 4.

- Excel guckt in Zeile 5 der Suchmatrix und findet dort den Wert 11. 11 ist NICHT <= 10

Jetzt bricht Excel die Suche ab. Als letzte gefundene Zeile hat es die Zeile 4.

Nun gibt es die dazugehörige Spalte zurück. In Zeile 4 Spalte 2 unserer Matrix steht der Wert Reell. Und dies bekommen wir als Ergebnis.

 Ihre Meinung zu Online Excel Geben Sie uns ein Feedback
Problematik 1 : Unsortiert

 BCDEFG
4NamePunkteErgebnis PunkteBewertung
5Maria5Ziemlich mies 0Ganz mies
6    2Ziemlich mies
7    9Reell
8    5Auch noch mies
9    11Streber
10    13Abgeguckt

Formeln der Tabelle
ZelleFormel
D5=SVERWEIS(C5;$F$5:$G$10;2)


Hier suchen wir nach der 5.

0 <= 5 Excel merkt sich Zeile 1
2 <= 5 Excel merkt sich Zeile 2
9 ist NICHT <= 5 also bricht Excel ab und gibt uns den Wert aus Zeile 2 zurück!


Problematik 2 : Kleiner Wert fehlt

 BCDEFG
4NamePunkteErgebnis PunkteBewertung
5Maria2#NV 5Ganz mies
6    8Ziemlich mies

Formeln der Tabelle
ZelleFormel
D5=SVERWEIS(C5;$F$5:$G$13;2)


Hier suchen wir den Wert 2.
Excel guckt in Zeile 1 findet dort den Wert 5!
5 ist nicht kleiner gleich 2.
Excel hat sich noch keine Zeile merken können, kann also gar kein Ergebnis liefern und gibt somit #NV aus.


Problematik kombiniert

 BCDEFG
4NamePunkteErgebnis PunkteBewertung
5Maria2#NV 5Ganz mies
6    1Ziemlich mies

Formeln der Tabelle
ZelleFormel
D5=SVERWEIS(C5;$F$5:$G$13;2)


Auch hier suchen wir wieder nach dem Wert 2.
Excel guckt in Zeile 1 findet dort den Wert 5!
5 ist nicht kleiner gleich 2.
Excel hat sich noch keine Zeile merken können, kann also gar kein Ergebnis liefern und gibt somit #NV aus.

Sie sehen, Excel schaut dann nicht weiter ob es nicht doch noch einen Wert findet.
Excel sucht nicht weiter, wenn es zum ersten Mal die Bedingung nicht erfüllt sieht!



Und nun zur zweiten Variante, nämlich:
 
 ABCDEFGH
1        
2 Der SVerweis für Werte genau finden 
3        
4 WährungBetrag EURErgebnis WährungWechselkurs 
5 Jupiterdollar23 Marsdollar0,8 
6     Venusdollar0,7 
7     Jupiterdollar1,5 
8        
Formeln der Tabelle
D5 : =C5*SVERWEIS(B5;$F$5:$G$7;2;FALSCH)
 

Die Formel in D5 nochmal:
=C5*SVERWEIS(B5;$F$5:$G$7;2;FALSCH)

Durch die Angabe des vierten Argumentes mit Falsch wird erwirkt, dass genau nach dem Suchwert gefahndet wird.
Ansonsten käme bei unserem Beispiel nur Murks raus, da Jupiter kleiner ist als Mars (natürlich nur rein Buchstabenanfangsmäßig !!!!!!!!!!!!!!)

Testen Sie es mal aus, indem Sie das Argument auf Wahr ändern.

Tatsächlich ist diese Form des SVerweises sehr nützlich in Verbindung mit Datengültigkeit und Dropdownlisten.
Aber Datengüligkeit ist ein anderes Thema.
Fragen stellen im Online-Excel Forum

Neu in Online-Excel: Online-Excel Newsletter

Weitere Artikel der Gruppe: Funktionen Aus Excel Standard
Nach oben