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.
Teil 1: Wissenswert: Sverweis Funktion und Makro Alternative
Teil 2: Besser als SVERWEIS: Die XVERWEIS Funktion
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()
Debug.Print Now
Dim i As Long, letzteZeile As Long
Dim Arbeitsmappe As Workbook
Dim Datenbasis As Worksheet, Ziel As Worksheet
Dim Bereich As Range, 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
Debug.Print Now
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 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()
Debug.Print Now
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, Ziel As Worksheet
Dim ZelleFirma As Range, 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, LookIn:=xlValues, LookAt:=xlWhole)
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
Debug.Print Now
End Sub
Die Variante 2 wurde zur besseren Nachvollziehbarkeit möglichst lesbar erstellt und ist entsprechend umfangreich im VBA Code. Fortgeschrittene können alternativ auch den folgenden deutlich schlankeren VBA Code nutzen. Hier werden die Schritte möglichst zusammengefasst und der Einsatz von sprechenden Variablen eingeschränkt. Auswirkungen auf die Ausführungsgeschwindigkeit hat der reduzierte Code allerdings nicht.
Sub Flexibler_Als_Sverweis_Fortgeschrittene()
Debug.Print Now
Dim i As Long, Zeile As Long, letzteZeile As Long
Dim Arbeitsmappe As Workbook
Dim Datenbasis As Worksheet, Ziel As Worksheet
Dim ZelleFirma As Range, 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
With Datenbasis
Set ZelleFirma = Bereich.Find(Ziel.Range("A" & i).Value & Ziel.Range("B" & i).Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not ZelleFirma Is Nothing Then
Ziel.Range("C" & i).Value = .Range("D" & ZelleFirma.Row).Value
Ziel.Range("D" & i).Value = .Range("E" & ZelleFirma.Row).Value
Ziel.Range("E" & i).Value = .Range("F" & ZelleFirma.Row).Value
Ziel.Range("F" & i).Value = .Range("G" & ZelleFirma.Row).Value
Ziel.Range("G" & i).Value = .Range("H" & ZelleFirma.Row).Value
Set ZelleFirma = Nothing
End If
End With
Next i
Debug.Print Now
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 Alternativen Beispieldatei
weiterer Nachteil: Sverweis hört nach etwa 256 Zeichen auf zu “vergleichen”: Ist das Suchkriterium zu lang, wird “#Wert!” ausgegeben.
@Becker: Stimmt, die Liste der Nachteile habe ich gerade entsprechend ergänzt!
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.
Hallo
kann mir mal jemand Helfen
ich habe zwei Tabellenblätter
eine mit Daten wo in spalt A Artikelnummer steht und in Spalte D die Preise
auf dem Zweiten Tabellenplatt
Steht in Spalte B Artikelnummer und in spalte F die Preise in Spalte G habe ich Rabt stehen in Spalte H den Rabat Preis
wie kann ich das Makro so schreiben der er mir nach Artikel nummer in Tabellenplatt 1 sucht und den Preis in Tabellen Blatt 2 Spalte F schreibt und wenn er nix findet die Zelle leer macht
für eure Hilefe wäre ich sehr Dankbar
mit Sverweis klappt das alles ganz gut bekomme es nur als Makro nicht hin
er soll ja die formel auch weglassen danach
Ich finde deinen Copyright Hinweis im Makro Quelltext etwas ungeschickt gewählt.
Zitat:”Eine kommerzielle/gewerbliche Verwendung ist nicht gestattet!”
Ich glaube kaum dass jemand privat Sverweis bzw. eine Makro Alternative einsetzt!
Genauso kann dein Makro auch nicht unverändert genutzt werden.
Da ich für mein Makro nur einen Denkanstoss gesucht habe werde ich mir jetzt natürlich eine komplett eigene Variante erarbeiten. Deine Musterdatei habe ich jetzt nach lesen des Quellcodes gelöscht.
@Markus: Danke für das Feedback. Der Copyright-Hinweis ist wichtig um den Quellcode vor unerlaubten Veröffentlichungen auf anderen Webseiten zu schützen. Natürlich sind die Beispiele dafür gedacht, dass sie für die eigenen Projekte angepasst werden können. Was entsprechend nicht geht, ist die Veröffentlichung auf Konkurrenzseiten oder in Foren ohne Nennung der Quelle, oder die Weitergabe und Vervielfältigung ohne Urheberangabe. Ich denke, dass sollte eigentlich klar sein.
Sverweise können sehr vielfältig genutzt werden und helfen in allen Lebenslagen. Sie können auch sehr sinnvoll in privaten Exceldateien genutzt werden (z.B. Haushaltsbüchern, Einkaufslisten, Terminplanern …). Ich nutze Sverweise täglich um mir lästiges abtippen zu ersparen.
Hallo Marco,
besteht die Möglichkeit Variante 2 auch so umzuschreiben, dass er nur 1 Kriterium braucht?
Ich scheiter da leider dran
@Kay: Dazu muss lediglich eine der beiden Variablen entfernt werden.
Branchen_Art & Firma
Vielen Dank Marco,
Ich habe die Funktion erfolgreich anpassen können und bereits eine Weile in Verwendung. Leider bin ich in dem Thema nicht so ganz wissend, deshalb wenn mir die Frage gestattet ist:
Das Durchlaufen aller Positionen nach aktivieren des Makros dauert doch sehr lange. Besteht die Möglichkeit das mehr oder weniger in Echtzeit passieren zu lassen? Also das ich quasi etwas in die Zelle reinschreibe und direkt nach Eingabe nur für diese Zelle dann der sverweis gemacht wird? Das würde jede Menge Zeit sparen, da das Makro aktuell knapp 3000 Zeilen durchläuft.
Ich danke dir
@Kay: Dazu könnte die Funktion im Tabellenblatt mit Hilfe des Worksheet.Change-Ereignis integriert werden. https://docs.microsoft.com/de-de/office/vba/api/excel.worksheet.change
Das Makro muss dann natürlich noch entsprechend angepasst werden.
Vielen Dank für die schnelle Antwort.
Ich habe versucht mich da rein zu lesen, aber habe in diesem Fall leider absolut keine Idee wie ich das lösen kann.
Ich habe es hinbekommen.
Vielen Dank nochmal. Ich habs mittels Change_Ereignis und VLOOKUP gelöst 🙂
Hallo Marco,
ich bin bei der Suche nach einer Problemlösung für eine Importaufgabe auf deinen Artikel gestoßen und habe versucht, deine Variante2 für meine Zwecke anzupassen. dazu habe ich noch ein paar Fragen. Wäre nett wenn ich hbier Unterstützung finden könnte. Danke dafür schon mal im Voraus.
Vielleicht zunächst Grundsätzliches zur Poblemstellung: ich verwalte in einer Excel-Liste die Medizinprodukte(Geräte) einer Rehaklinik. Für die erhalte ich von unterschiedlichen Prüfinstituten Prüflisten mit Ergebnissen (Prüfdatum, Prüfergebnis etc.), die ich gerne in die Gesamtliste importieren möchte. Die Spalten sind aber nicht identisch angeordnet in beiden Listen. Grundlage für den Import ist eine (vierstellige) Geräte-ID, welche in beiden Listen gleich ist.
Ich bin nun kein VBA-Profi, aber auch nicht ganz ahnungslos. Was mir in deinem Code nicht klar ist, was es mit “ZelleFirma” auf sich hat, bzw. welche Anpassung ich dafür in meiner Datei vornehmen müsste…
@Gerhard: ZelleFirma ist die Zelle in der der Suchbegriff gefunden wurde, sofern vorhanden. Weitergehende individuelle Lösungen kann ich gerne im Rahmen einer Auftragsprogrammierung anbieten.
@Marco: danke für den Hinweis mit ZelleFirma, das habe ich verstanden und entnehme daraus, dass ich den Ausdruck auch ändern könnte z.B. in “ZelleGerät” sofern ich das unter dim so definiere.
Ich versuche es erst mal weiter auf eigenen Faust (… will ja was lernen). Sollte ich nicht zurecht kommen, wäre eine Beauftragung generell denkbar. Muss ich aber mit meinem Chef abstimmen und bräuchte dafür ein konkretes Angebot. Vielleicht kannst Du schon mal ein grobe Einschätzung abgeben, was das kosten könnte.
Kontakt dann gerne über direkt per e-mail. Danke.
Hallo,
eigentlich suchte ich nur so einem Tool.
Als ich mir den Code angesehen habe, dachte ich: geht das nicht einfacher? Tut mir leid – ja, es geht. Vielleicht ist das Programm auch somit schneller. 🙂
Was konnte man ändern?
a) das Schreiben in die Zellen wird 2x dargestellt – im If-Teil und im Else-Teil. Warum? Ein Festlegen der Variablen in If- und Else sind soweit klar. Das “Schreiben” jedoch, kann man unterhalb der Prüfung hin packen. Ersparnis: 5 Zeilen Code.
b) mit IIF kann man sich das Belegen der Variablen noch einfacher machen. Ersparnis: nochmal 8 Zeilen (13 Zeilen If-Then minus 5 Zeilen verbleibend die Zuordnung). Hier der Code: (ich hab das leidliche “i” mal in “Ze” (für “Zeile”) umgeändert
With Datenbasis
Set ZelleFirma = Bereich.Find(Branchen_Art & Firma, LookIn:=xlValues, LookAt:=xlWhole)
Ziel.Range(“C” & Ze).Value = IIf(ZelleFirma Is Nothing, “”, .Range(“D” & ZelleFirma.Row).Value)
Ziel.Range(“D” & Ze).Value = IIf(ZelleFirma Is Nothing, “”, .Range(“E” & ZelleFirma.Row).Value)
Ziel.Range(“E” & Ze).Value = IIf(ZelleFirma Is Nothing, “”, .Range(“F” & ZelleFirma.Row).Value)
Ziel.Range(“F” & Ze).Value = IIf(ZelleFirma Is Nothing, “”, .Range(“G” & ZelleFirma.Row).Value)
Ziel.Range(“G” & Ze).Value = IIf(ZelleFirma Is Nothing, “”, .Range(“H” & ZelleFirma.Row).Value)
Set ZelleFirma = Nothing
End With ‘Datenbasis
Als ich das gesehen habe, dachte ich – geht das nicht NOCH kürzer? Äh ja, das geht 😉 (es wurde eine neue Variable “Sp” (für “Spalte”) eingeführt, da hier statt “Range” “Cells” verwendet wurde)
For Ze = 3 To Ziel.Range(“A” & Rows.Count).End(xlUp).Row
Branchen_Art = Ziel.Range(“A” & Ze).Value
Firma = Ziel.Range(“B” & Ze).Value
With Datenbasis
Set ZelleFirma = Bereich.Find(Branchen_Art & Firma, LookIn:=xlValues, LookAt:=xlWhole)
For Sp = 3 To 7
Ziel.Cells(Ze, Sp).Value = IIf(ZelleFirma Is Nothing, “”, .Cells(ZelleFirma.Row, Sp + 1).Value)
Next
Set ZelleFirma = Nothing
End With ‘Datenbasis
Next Ze
Jetzt hat die For-Next-Schleife statt 32 Zeilen Code nur noch 11 Zeilen.
Ich hoffe, meine Ideen gefallen dir. 🙂
@Tobias: Natürlich geht es auch mit weniger Code-Zeilen. Die vorgestellten Lösungen sollen allerdings auch für VBA Einsteiger noch nachvollziehbar sein. Daher habe ich einen möglichst leicht lesbaren Code für die Musterlösung verwendet. Die Schleife in der Schleife ist für Einsteiger nicht unbedingt die ideale Lösung.
Wenn der Code möglichst schlank geschrieben werden soll, kann in Ihrem Beispiel dann auch noch auf den With-Block und die Variablen Branchen_Art und Firma verzichtet werden.
Hallo Marco
Ein grosses Lob für deine tolle Programmierung, diese bringt mir auf der Arbeit so einige Erleichterungen! Nun habe ich aber eine Frage. Kann man den Vlookup auch verwenden wenn das Suchergebnis und die Ziel-Zelle nicht auf einer Linie stehen?
Ich möchte ein automatisches Formular kreieren welches anhand eines Suchkriteriums in einer bestimmten Zelle dann die Zielwerte im Formular abfüllt.
Beispiel zum besseren Verständnis:
Suckriterium Tabellenblatt 1 Zelle B1
Zielwerte zu B1 schreiben in B2 / B3 / B4 / C6 / D8 usw.
Eignet sich dazu der Vlookup überhaupt?
Welche anderen Möglichkeiten gibt es?
Vielen Dank für deine Hilfe!
@Jimmy: Der Sverweis bezieht sich grundsätzlich immer auf eine Zeile. Wenn der Suchwert z.B. in Spalte B seht, können über den Sverweis dazugehörige Daten aus weiteren Spalten rechts davon, also ab Spalte C ermittelt werden. Wenn sich der gesuchte Werte dann allerdings in einer anderen Zeile oder links vom Suchwert, also z.B. in Spalte A befindet, dann geht das mit dem klassischen Sverweis nicht. Das ließe sich dann mit der vorgestellten Makro Alternative “Flexibler_Als_Sverweis” realisieren.
Hallo Marco
Vielen Dank für deine schnelle Antwort.
Lässt sich mit der flexiblen Variante 2 denn auch realisieren, dass die Werte in eine bestimmte Zelle zurückgegeben werden? Das ist für mich noch unklar.
In meine Beispiel Suchwert immer nur in Zelle B25 und Ergebnisse je nach Suchwert in Zellen E26 / B88 usw. verteilt über das ganze Blatt.
Eignet sich dazu überhaupt die flexible Methode
@Jimmy: Die vorgestellten VBA-Codes sind nur Beispiele, die je nach Bedarf angepasst werden können. Für die Anpassung sind aber natürlich entsprechende VBA Kenntnisse erforderlich. Grds. kann mit VBA eigentlich alles umgesetzt werden.