Wissenswert: Sverweis Funktion und Makro Alternative

Die Sverweis Formel-Funktion ist eine der beliebtesten Excel Funktionen überhaupt. Die Nutzung kann allerdings auch Probleme bereiten und die Performance bei großen Tabellen stark beeinträchtigen. In diesem Beitrag erfahren Sie, wofür Sie die Sverweis-Funktion nutzen können, worauf Sie achten müssen und schließlich was alternativ Makros leisten können. Dazu stelle ich Ihnen zwei Makros vor. Das erste Makro nutzt die Sverweis-Tabellenblatt-Funktion vlookup() in VBA und bietet somit alle Möglichkeiten der Formel-Funktion. Das zweite Makro stellt die Funktionsweise in ähnlicher Weise nach und bietet im Praxiseinsatz noch weitere Möglichkeiten, die mit der Sverweis-Funktion sonst nicht zu realisieren sind.

Sverweis-Formel im Tabellenblatt
Mit der Sverweis-Funktion können Sie grundsätzlich fehlende Daten aus einer anderen Tabelle zuspielen. Haben Sie zum Beispiel eine Liste mit Postleitzahlen bei denen die Orte fehlen können Sie über einen Sverweis in einer zusätzlichen Spalte die Orte in Abhänigkeit zur Postleitzahl hinzufügen. Voraussetzung ist, dass Sie über ein Postleitzahlenverzeichnis verfügen in denen zu allen Postleitzahlen die Orte gespeichert sind. Sie benötigen also eine Datenbasis aus der Sie die fehlenden Werte zuspielen können. Es sind dabei allerdings noch drei Punkte unbedingt zu beachten.

  • Die Datenbasis darf zu einem Suchbegriff nicht unterschiedliche Werte enthalten. In dem Beispiel mit den Postleitzahlen darf es zu ein und derselben Postleitzahl nicht unterschiedliche Orte geben. Das kann in der Realität allerdings schon mal vorkommen, wenn mehrere Teilorte sich die gleiche Postleitzahl teilen. In diesen Fällen funktioniert der Sverweis nicht optimal, weil immer der erste gefundene Treffer als Formelergebnis ausgegeben wird.
  • Die Zellwerte müssen dasselbe Format besitzen. Ist die eine Postleitzahl als Zahl, die andere als Text formatiert, funktioniert die Sverweis Funktion nicht.
  • Das Suchkriterium muss in der Matrix immer in der ersten Spalte stehen!

Die Geschwindigkeit kann durch die regelmäßige Neuberechnung der Formelergebnisse massiv leiden, wenn Sie Sverweise einsetzen und die Formelergebnisse nicht nach der Berechnung in Werte umwandeln. Es kann daher sinnvoll sein, nach der Berechnung die Formeln über einen rechten Mausklick über “Inhalte einfügen” in feste Zellwerte umzuwandeln.

Funktionsweise Sverweis:
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich-Verweis)

Suchkriterium = Zelle der den Suchbegriff beinhaltet
Matrix = Spaltenbereich in dem gesucht werden soll (erste Spalte muss das Suchkriterium beinhalten)
Spaltenindex = Innerhalb der Matrix die Spalte von links
Bereich-Verweis = WAHR oder FALSCH -> FALSCH = genaue Übereinstimmung des Suchbegriffs (Standard)

Vorteile:
+ bei Änderung der Datenquelle aktualisiert sich der Zellwert automatisch

Nachteile:
– beim Filtern werden die Zellwerte neu berechnet
– gegenüber reinen Zellwerten erhöhter Speicherbedarf
– ist der Suchbegriff nicht vorhanden, wird #NV ausgegeben
– es wird immer der erste gefundene Treffer angezeigt, der Suchbegriff muss daher eindeutig sein! Siehe Fehler in Spalte D bei Verwendung der reinen Firmen-Nummer als Suchbegriff
– alle Zellen müssen im gleichen Datenformat formatiert sein
– Suchkriterium darf maximal 256 Zeichen lang sein

Vlookup im Makro nutzen
Die Sverweis-Funktion kann auch als Makro genutzt werden. Dies funktioniert über die Einbindung der WorksheetFunction VLookup(“Suchkriterium”, “Matrix”, “Spaltenbereich”, “Bereichs-Verweis”).

Der Hauptvorteil gegenüber der herkömmlichen Sverweis Formel-Funktion ist, dass Sie die Berechnung durch einen Button-Klick ausführen können und anschließend nur die Zellwerte, nicht die Formeln in den Zellen stehen. Möchten Sie die Zellwerte neu berechnen reicht ein erneuter Button-Klick.

Vorteile:
+ es werden nur Werte in die Zellen geschrieben
+ Makroverarbeitung schneller als Variante 2

Nachteile:
– bietet ausschließlich die gleichen Funktionen wie die Sverweis-Formelfunktion
– ist der Suchbegriff nicht vorhanden, bricht die Makroverarbeitung ab (“On Error Resume Next” verhindert den Abbruch durch überspringen der Codezeile”)
– bei Änderung der Datenquelle aktualisiert sich der Zellwert nicht automatisch
– es wird immer der erste gefundene Treffer angezeigt, der Suchbegriff muss daher eindeutig sein!
– Suchkriterium darf maximal 256 Zeichen lang sein

Sub SVERWEIS_Vlookup()

Dim i As Long, letzteZeile As Long
Dim Arbeitsmappe As Workbook
Dim Datenbasis As Worksheet
Dim Ziel As Worksheet
Dim Bereich As Range
Dim ZelleFirma As Range
Dim WsF As WorksheetFunction

Set Arbeitsmappe = ThisWorkbook
Set Datenbasis = Arbeitsmappe.Worksheets("Quelle")
Set Ziel = Arbeitsmappe.Worksheets("Ziel (Makro Variante1)")

letzteZeile = Datenbasis.Range("A" & Rows.Count).End(xlUp).Row

Set Bereich = Datenbasis.Range("A1:H" & letzteZeile)
Set WsF = Application.WorksheetFunction

For i = 3 To Ziel.Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Ziel.Range("B" & i).Value = WsF.VLookup(Ziel.Range("A" & i).Value, Bereich, 4, False)
Ziel.Range("C" & i).Value = WsF.VLookup(Ziel.Range("A" & i).Value, Bereich, 5, False)
Ziel.Range("D" & i).Value = WsF.VLookup(Ziel.Range("A" & i).Value, Bereich, 6, False)
Ziel.Range("E" & i).Value = WsF.VLookup(Ziel.Range("A" & i).Value, Bereich, 7, False)
Ziel.Range("F" & i).Value = WsF.VLookup(Ziel.Range("A" & i).Value, Bereich, 8, False)
Next i

End Sub

Sverweis Alternative mit zusätzlichen Möglichkeiten
Deutlich flexibler ist die zweite Makro Variante. Im Gegensatz zur Sverweis-Funktion (egal ob im Tabellenblatt oder VBA) können auch Zellwerte links vom Suchkriterium oder in sonstiger Abhängigkeit (schräg versetzt, oberhalb, unterhalb) als Ergebnis dargestellt werden.

Vorteile:
+ es werden nur Werte in die Zellen geschrieben
+ Werte in unterschiedlicher Abhängigkeit können ermittelt werden
+ nicht gefundene Suchbegriffe werden als leerer Zellwert dargestellt oder können mit einem Dummy-Wert befüllt werden
+ der Suchbegriff kann aus mehreren Spalten gebildet werden

Nachteile:
– Makroverarbeitung etwas langsamer als Variante 1
– bei Änderung der Datenquelle aktualisiert sich der Zellwert nicht automatisch
– es wird immer der erste gefundene Treffer angezeigt, der Suchbegriff muss daher eindeutig sein!

Sub Flexibler_Als_Sverweis()

Dim i As Long, Zeile As Long, letzteZeile As Long
Dim Shopname As String, Anschrift As String, PLZ As String, Ort As String, FirmaArt As String
Dim Firma As String, Branchen_Art As String, FirmaSOID As String
Dim Arbeitsmappe As Workbook
Dim Datenbasis As Worksheet
Dim Ziel As Worksheet
Dim ZelleFirma As Range
Dim Bereich As Range

Set Arbeitsmappe = ThisWorkbook
Set Datenbasis = Arbeitsmappe.Worksheets("Quelle")
Set Ziel = Arbeitsmappe.Worksheets("Ziel (Makro Variante2)")

letzteZeile = Datenbasis.Range("A" & Rows.Count).End(xlUp).Row

Set Bereich = Datenbasis.Range("A1:A" & letzteZeile)

For i = 3 To Ziel.Range("A" & Rows.Count).End(xlUp).Row
Branchen_Art = Ziel.Range("A" & i).Value
Firma = Ziel.Range("B" & i).Value

With Datenbasis
Set ZelleFirma = Bereich.Find(Branchen_Art & Firma)
If ZelleFirma Is Nothing Then
Shopname = ""
Anschrift = ""
PLZ = ""
Ort = ""
FirmaArt = ""

Ziel.Range("C" & i).Value = Shopname
Ziel.Range("D" & i).Value = Anschrift
Ziel.Range("E" & i).Value = PLZ
Ziel.Range("F" & i).Value = Ort
Ziel.Range("G" & i).Value = FirmaArt
Else
Zeile = ZelleFirma.Row
Shopname = .Range("D" & Zeile).Value
Anschrift = .Range("E" & Zeile).Value
PLZ = .Range("F" & Zeile).Value
Ort = .Range("G" & Zeile).Value
FirmaArt = .Range("H" & Zeile).Value

Ziel.Range("C" & i).Value = Shopname
Ziel.Range("D" & i).Value = Anschrift
Ziel.Range("E" & i).Value = PLZ
Ziel.Range("F" & i).Value = Ort
Ziel.Range("G" & i).Value = FirmaArt
Set ZelleFirma = Nothing
End If
End With
Next i

End Sub

Mustermappe
Eine Datei mit den drei Varianten und Beispieldaten steht hier zum Download bereit. Die Funktionalität der Makros ist nur gegeben, sofern Sie Makros aktiviert haben. Aktivieren Sie aus Sicherheitsgründen nur Makros aus vertrauenswürdigen Quellen!

Sverweis Funktion und Makro AlternativenSverweis Funktion und Makro Alternativen Beispieldatei

 
Gefällt Ihnen der Beitrag?

4 Gedanken zu “Wissenswert: Sverweis Funktion und Makro Alternative

  1. weiterer Nachteil: Sverweis hört nach etwa 256 Zeichen auf zu “vergleichen”: Ist das Suchkriterium zu lang, wird “#Wert!” ausgegeben.

  2. Hallo Marco,

    kannst du mir sagen, bis zu vielen Zeilen das Marko funktioniert.
    Kann der Suchbereich aus der Quelldatei mehr als 65000 Zeilen bestehen?

    Danke & Gruß
    Richard

    • @Richard: Ja, das Makro funktioniert grds. immer bis zum Dateiende. Über Range(“A” & Rows.Count).End(xlUp).Row wird immer die letzte genutzte Zeile in Spalte A ermittelt. Es spielt dabei keine Rolle welche Excel-Version genutzt wird, sprich wie viele Zeilen die Excelmappe maximal haben kann.

Schreibe einen Kommentar