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

Verweis

Autor: Peter Haserodt - Erstellt: 2009-04-20      - Letzte Revision: ?

Verweis

Ha, denken Sie, als oft verwiesener Schüler ab in die Ecke: Das kenne ich, dies wird wohl so ein großer Bruder vom SVerweis oder WVerweis sein.

Oh Oh, und zurück in die Ecke, denn sehr viel haben die nicht gemeinsam. Na sagen wir, sie haben einiges gemeinsam und genau so viel, dass es richtig verwirrend wird.
Und wenn ich Sie jetzt nicht schon endgültig in den Wahnsinn getrieben habe, fangen wir doch einfach an.

Aber ich empfehle Ihnen dringendst, sich vorher mit dem Artikel zu SVerweis zu beschäftigen, wenn Sie dies noch nicht getan haben sollten! Ich setze diesen voraus!

Wenn ich nun in die Excel Hilfe schaue, werde ich richtig schlau daraus.
Diese sagt: [...]Für die Funktion VERWEIS gibt es zwei Syntaxversionen: die Vektor- und die Matrixversion. [...]

Wer ist dieser verfluchte Syntax? Und den einzigen Vektor den ich kenne, der heißt Viktor???

1. VERWEIS(Suchkriterium;Matrix)
2. VERWEIS(Suchkriterium;Suchvektor;Ergebnisvektor)
Beschäftigen wir uns zuerst mit dem 1. , denn da ist weniger drin und wo weniger drin ist, muss man weniger schreiben.
Und vergessen wir vor allem mal die ganzen komischen Worte!

Grundmuster des Verweises

Damit Sie endlich auch etwas zu tun haben, stellen Sie folgendes her:
 ABCDE
1     
2 Suche SuchkriteriumVerweis
3 1 0,5#NV
4 3 11
5 5 1,51
6   33
7   4,23
8   55
9   75

Formeln der Tabelle
ZelleFormel
E3=VERWEIS(D3;$B$3:$B$5)

Die Formel in E3 können Sie nach unten ziehen.
UND BEACHTEN Sie die Dollarzeichen, mit denen ich mir soviel Mühe gegeben habe.

Und gleich ein für alle mal:
Die Werte in der Suche müssen aufsteigend sortiert sein!
Hier gibt es kein Wenn und kein Aber.
Wer diese nicht aufsteigend hat, leidet Höllenqualen und wird von Excel noch gequält, wenn selbst der aller aller letzte Highlander endlich sein Schwert abgegeben hat!
Im Gegensatz zum SVerweis gibt es im Verweis nicht die Möglichkeit, genau nach einem Wert zu suchen.


Sie sehen, vom Prinzip ist dies hier wie der SVerweis der auf sich direkt verweist.
Ach bevor Sie meinen Blödsinn verstehen einfach ein Beispiel:
 BCDE
2Suche SuchkriteriumSVerweis
31 0,5#NV
43 11
55 1,51
6  33
7  4,23
8  55
9  75

Formeln der Tabelle
ZelleFormel
E3=SVERWEIS(D3;$B$3:$B$5;1;WAHR)


Wir sehen, dass die Suche wie im SVerweis mit dem Argument Wahr (oder dieses Argument weggelassen) ist.
Es wird der Suchbereich nach dem Wert durchsucht und wenn auf den ersten Wert >= dem Suchwert getroffen wird, dann entscheidet Excel: Ist er gleich dem Suchwert, dann gebe ich diesen aus, wenn nicht, denn nächst kleineren.


Und wie ist unser #NV entstanden?
Ganz einfach, 0,5 ist kleiner als der kleinste Wert in der Suche, als nix da, nix zu finden, und tschüss.

Erweitern wir das Beispiel:
 BCDEF
2Suche  SuchkriteriumVerweis
31a 0,5#NV
43b 1a
55c 1,5a
6   3b
7   4,2b
8   5c
9   7c

Formeln der Tabelle
ZelleFormel
F3=VERWEIS(E3;$B$3:$C$5)


Sie sehen die Veränderung?
Auf einmal werden uns die Werte aus Spalte C ausgegeben.

Dies ist eine Eigenheit des Verweises der Variation 1.
Hier wird immer die letzte Spalte (Zeile) ausgegeben, die zum gefundenem Wert gehört.
Dies kann durchaus kritisch sein, wenn man sich diesem nicht bewusst ist, denn gibt es mehr Zeilen als Spalten, dann funktioniert Verweis wie ein WVerweis und nicht mehr wie ein SVerweis. Bei gleicher Anzahl Spalten und Zahlen gelten Spalten!

Beispiel waagerecht:

 ABCDEFGHI
1         
2 12345 2 
3 abcde g 
4 fghij   
5         

Formeln der Tabelle
ZelleFormel
H3=VERWEIS(H2;$B$2:$F$4)


Diese erste Variante des Verweises ist eher seltener zu gebrauchen und nicht ungefährlich, da man sich schnell mit Spalten und Zeilen vertuen kann. Aber für gewisse Matrixspielereien ganz nützlich.
 Peter Haserodt
  • Formelhilfe
  • Makroentwicklung
  • VBA-Programmierung
  • + + + + + + + + +
Variante 2 - Mein Freund Vektor oder einfach ein relativer Verweis

Was haben wir denn bisher gelernt?
Das Verweis wie SVerweis oder WVerweis mit beschnittenem 4. Argument funktioniert und sonst komische Sachen macht.
Wofür ist er dann aber nützlich?
Grundsätzlich noch einmal: Nur wenn ich Werte einordnen will und und meine Suchwerte aufsteigend sortiert sind!
Ich kann es nicht oft genug wiederholen.

Aber er hat einen erheblichen Vorteil zu den anderen Verweisarten. Nämlich, dass er relativ arbeiten kann.
Was meine ich nun damit?
Ganz einfach, die ewige Suche nach dem SVerweis nach links, z.B.
Nehmen wir an, wir wollen Schulnoten ermitteln, aber unsere Bezeichnungen stehen nicht rechts von den Suchwerten, sondern links. Mit dem SVerweis fallen wir da ganz schön auf die Schnauze aber:

 ABCDEF
1      
2 Sehr gut1 3Befriedigend
3 Gut2   
4 Befriedigend3   
5 Ausreichend4   
6 Mangelhaft5   
7 Ungenügend6   

Formeln der Tabelle
ZelleFormel
F2=VERWEIS(E2;$C$2:$C$7;$B$2:$B$7)


Wie unterscheidet sich nun das Ganze von der ersten Variante?

Nun, wir geben nicht nur an, wo gesucht werden soll, sondern auch, aus welchem Bereich der dazugehörige Wert zurückgegeben werden soll.

Wir suchen in Gelb und bekommen dieses komische Rosa.
Hierbei kommt eine sehr häufige relative Beziehung zu Stande.
(Ich schreibe schon wie die Excel Hilfe - Uhhhhhhhh)
Im Gelben Bereich wird die 3. Postion gefunden, und somit wird aus dem rosa Bereich die 3. Position zurückgegeben.
Sie glauben mir nicht?
Ab zum Experiment, welches Sie Ungläubigen hoffentlich übzerzeugt:

 ABCDEFGH
1        
2  1 3Befriedigend  
3  2     
4  3     
5  4     
6  5     
7  6    Sehr gut
8       Gut
9       Befriedigend
10       Ausreichend
11       Mangelhaft
12       Ungenügend

Formeln der Tabelle
ZelleFormel
F2=VERWEIS(E2;$C$2:$C$7;$H$7:$H$12)


Und? Überzeugt? Immer noch nicht? Noch mehr? Geben Sie denn keine Ruhe?
Na gut:

(Beachten Sie bitte die Tabellennamen nun)
Tabelle2

 ABCDE
1     
2 1 3Befriedigend
3 2   
4 3   
5 4   
6 5   
7 6   

Formeln der Tabelle
ZelleFormel
E2=VERWEIS(D2;B2:B7;Tabelle1!B3:B8)


Tabelle1

 AB
1  
2  
3 Sehr gut
4 Gut
5 Befriedigend
6 Ausreichend
7 Mangelhaft
8 Ungenügend


Übrigens, ich habe Ihnen hier noch ein Ei gelegt, sehen Sie es? (Dollar)


Den Sinn des Verweises nun für Ihre eigenen Anwendungen zu finden, überlasse ich gnädigst Ihnen.
So bin ich nunmal, einfach ein netter Kerl.
Aber Sie wollen noch ein bisserl mehr?
Das ganze funktioniert auch mit Texten aber ich habe irgendwie kein Sinnvolles Beispiel gefunden.
Nur sei darauf hingewiesen, dass zwischen Groß - und Kleinschreibung nicht unterschieden wird:
 ABCDEF
1      
2 Haus1 haus2
3 haus2 Haus2
4 Klaus3 Klaus4
5 klaus4 klaus4
6    Mülltonne4
7    Juli2

Formeln der Tabelle
ZelleFormel
F2=VERWEIS(E2;$B$2:$B$5;$C$2:$C$5)


Bitte bewundern Sie ausführlich dieses Beispiel, ich habe es verdient!

Fragen stellen im Online-Excel Forum

Neu in Online-Excel: Online-Excel Newsletter

Weitere Artikel der Gruppe: Funktionen Aus Excel Standard
Nach oben