(0)
Artikel
bewerten
(100% positiv)
(1)

Excel SVERWEIS richtig verwenden

Falsche Werte in der Excel Funktion SVERWEIS

Inhalt dieses Artikels:

    Mit SVERWEIS können sehr einfach verschiedene Tabellen anhand eines gemeinsamen Wertes zusammengefügt werden. Nachdem ich mich heute schon wieder mit der SVERWEIS-Funktion in Excel geärgert habe, habe ich mir die Funktion etwas näher angesehen.

    Hier ein konkretes Beispiel

    Für das Beispiel erzeuge ich 2 Tabellen. Tabelle 1 enthält den Begriff und dessen Eigenschaften, in Tabelle 2 will ich anhand des Begriffes nach den Eigenschaften suchen. Wird die Funktion SVERWEIS mit den Standardparametern verwendet, funktioniert das in meinem Beispiel nicht:

    Tabelle1:

      A B
    1 Verkehrsmittel Geschwindigkeit
    2 Flugzeug sehr schnell
    3 Auto schnell
    4 Fahrrad sehr langsam
    5 Moped langsam

    So funktioniert es nicht

    Tabelle2 mit den Werten von Tabelle 1:

      A B
    1 Verkehrsmittel von Tabelle1 ausgelesen
    2 Flugzeug  =SVERWEIS(A2;Tabelle1!A:B;2)
    3 Moped  =SVERWEIS(A3;Tabelle1!A:B;2)
    4 Fahrrad  =SVERWEIS(A4;Tabelle1!A:B;2)
    5 Auto  =SVERWEIS(A5;Tabelle1!A:B;2)

     

    Erläuterung: 

    In Tabelle2 habe ich die bereits verwendeten Verkehrsmittel erneut aufgelistet, Ziel ist es nach den Eigenschaften dieser zu suchen. 

    Betrachte ich die 2te Zeile, erwarte ich mir folgendes:

    In Zeile 2 suche ich mit A2 nach dem Begriff "Flugzeug" =SVERWEIS(A2;Tabelle1!A:B;2)

    Als Matrix verwende ich den Inhalt von Tabelle1: =SVERWEIS(A2;Tabelle1!A:B;2). Die Matrix dient als Datenquelle, in ihr will ich in der ersten Spalte nach dem Begriff "Flugzeug" suchen und eine andere Spalte derselben Zeile zurückgeben. (Die erste Spalte in Tabelle1 enthält bereits unsere Suchbegriffe, daher beginnt die Matrix bei Spalte A.)

    Kommt in der ersten Spalte der Matrix der gewünschte Suchbegriff in einer Zeile vor, sollte die 2te Spalte dieser Zeile als Ergebnis zurückgeliefert werden =SVERWEIS(A2;Tabelle1!A:B;2), also würde ich mir für Flugzeug als Eigenschaft: "sehr schnell" erwarten, das Beispiel erzeugt aber folgendes Ergebnis:

      A B
    1 Verkehrsmittel von Tabelle1 ausgelesen
    2 Flugzeug sehr langsam
    3 Moped langsam
    4 Fahrrad sehr langsam
    5 Auto #NV

    Lösung 1: beide Tabellen sortieren

    Mein Beispiel funktioniert nur, wenn die Werte in beiden Tabellen sortiert werden:

    Tabelle1 

      A B
    1 Verkehrsmittel Geschwindigkeit
    2 Auto schnell
    3 Fahrrad sehr langsam
    4 Flugzeug sehr schnell
    5 Moped

    langsam

    Tabelle2:

      A B
    1 Verkehrsmittel von Tabelle1 ausgelesen
    2 Auto =SVERWEIS(A2;Tabelle1!A:B;2)
    3 Fahrrad =SVERWEIS(A3;Tabelle1!A:B;2)
    4 Flugzeug =SVERWEIS(A4;Tabelle1!A:B;2)
    5 Moped =SVERWEIS(A5;Tabelle1!A:B;2)

    liefert das richtige Ergebnis:

      A B
    1 Verkehrsmittel von Tabelle1 ausgelesen
    2 Auto schnell
    3 Fahrrad sehr langsam
    4 Flugzeug sehr schnell
    5 Moped langsam

    Lösung 2 Bereich_Verweis:

    Wird in Tabelle2 die Option Bereich_Verweis in SVerweis mit dem Wert "0" verwendet, funktioniert das Beispiel auch ohne Sortierung:

      A B
    1 Verkehrsmittel von Tabelle1 ausgelesen
    2 Flugzeug =SVERWEIS(A2;Tabelle1!A:B;2;0)
    3 Moped =SVERWEIS(A3;Tabelle1!A:B;2;0)
    4 Fahrrad =SVERWEIS(A4;Tabelle1!A:B;2;0)
    5 Auto =SVERWEIS(A5;Tabelle1!A:B;2;0)

     

    Ergebnis (Tabelle 2):

      A B
    1 Verkehrsmittel von Tabelle1 ausgelesen
    2 Flugzeug sehr schnell
    3 Moped langsam
    4 Fahrrad sehr langsam
    5 Auto

    schnell

    Nicht in der ersten Spalte suchen:Index

    Was aber, wenn die zu durchsuchende Matrix nicht mit dem Suchbegriff, sondern mit dessen Eigenschaft beginnt:

      A B
    1 Geschwindigkeit Verkehrsmittel
    2 sehr schnell Flugzeug
    3 schnell Auto
    4 sehr langsam Fahrrad
    5 langsam Moped

    Anstelle von SVERWEIS, müssen wir für diese Anforderung die Funktion INDEX in Kombination mit einer Suche: VERGLEICH verwenden:

      A B
    1 Verkehrsmittel von Tabelle1 ausgelesen
    2 Flugzeug =INDEX(Tabelle1!A:B;VERGLEICH(A2;Tabelle1!B:B;0);1)
    3 Moped =INDEX(Tabelle1!A:B;VERGLEICH(A3;Tabelle1!B:B;0);1)
    4 Fahrrad =INDEX(Tabelle1!A:B;VERGLEICH(A4;Tabelle1!B:B;0);1)
    5 Auto =INDEX(Tabelle1!A:B;VERGLEICH(A5;Tabelle1!B:B;0);1)

     

    Erläuterung: 

    Die Funktion INDEX sucht in einer Matrix nach einer bestimmten Zeile und einer bestimmten Spalte.

    Als Such-Matrix verwendende ich wieder: Tabelle1!A:B: =INDEX(Tabelle1!A:B;VERGLEICH(A2;Tabelle1!B:B;0);1) , in ihr will ich wieder nach den Begriffen suchen und eine andere Spalte derselben Zeile (Eigenschaft) zurückgeben.

    Die zu suchenden Begriffe befinden sich jetzt in der 2ten Spalte (B) der Tabelle 1: Tabelle1!B:B =INDEX(Tabelle1!A:B;VERGLEICH(A2;Tabelle1!B:B;0);1), 

    Der Suchbegriff z.B. für Zeile 2 befindet sich wieder in A2 (Flugzeug)=INDEX(Tabelle1!A:B;VERGLEICH(A2;Tabelle1!B:B;0);1)

    Da in Spalte 2 der Matrix (B) gesucht wird, wird in dem Beispiel die Spalte 1 der Matrix als Eigenschaft zurückgegeben: =INDEX(Tabelle1!A:B;VERGLEICH(A2;Tabelle1!B:B;0);1)

     

    letzte Änderung dieses Artikels: 15.11.2016 20:45



    Feedback: