SVERWEIS mit Raketenantrieb 13

Dem lahmen SVERWEIS werden mit einem Trick Beine gemacht!
 

Der SVERWEIS gehört sicherlich zu den beliebtesten Excel-Funktionen. Vermutlich kannst du dich noch daran erinnern, wie stolz du warst, als du zum ersten mal durchstiegen hast, wie diese Funktion anzuwenden ist und welche tollen Dinge man damit anstellen kann – wie zum Beispiel das Abgleichen von zwei Listen gegeneinander.

Allerdings hat der SVERWEIS auch verschiedene Nachteile. Wenn er bei großen Listen mit mehreren 1000 Datensätzen zum Einsatz kommt, dann kann sich das Ganze zu einem ziemlichen Geduldsspiel entwickeln. Mit anderen Worten: Der SVERWEIS kann eine ziemlich lahme Krücke sein.

Allerdings gibt es einen Trick, wie man diese ansonsten sehr praktische Funktion schneller macht. Und damit meine ich RICHTIG schneller!

Und so geht der SVERWEIS mit Raketenantrieb:

Der Standard-SVERWEIS

Zu Beginn kommt eine kleine Auffrischung zur Syntax der Funktion.
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)

Interessant ist das letzte Argument, das in der Hilfefunktion “Bereich_Verweis” genannt wird. Zulässig sind dabei die Werte WAHR oder FALSCH:
WAHR = Ungefähre Übereinstimmung
FALSCH = Genaue Übereinstimmung

Im Beispiel
=SVERWEIS(A2;G2:K10000;3;FALSCH)
wird der Wert aus Zelle A2 in Spalte G des Bereichs G2:K10000 gesucht und im Falle eines exakten Treffers der Wert aus der dritten Spalte ausgegeben.

Vorteile:
Der zu durchsuchende Bereich (G2:K10000) kann unsortiert sein.
Es wird nur ein Ergebnis geliefert, das exakt mit dem Suchkriterium übereinstimmt.

Nachteil:
Die Funktion muss die komplette Tabelle bis zum Ende durchsuchen und ist daher (bei großen Tabellen) ziemlich langsam.

Etwas anders funktioniert die Variante mit dem Argument WAHR, bei der die zu durchsuchende Tabelle zwingend aufsteigend sortiert sein muss.

Vorteil:
Die Funktion ist extrem schnell.

Nachteil:
Wenn es keine exakte Übereinstimmung gibt, wird der nächstgelegene Wert geliefert, der dem Suchkriterium ähnlich ist. Doch dies ist in den meisten Fällen nicht gewünscht. Ich behaupte sogar, dass in 99,9% der Anwendungsfälle eine exakte Übereinstimmung mit dem Suchkriterium gewünscht ist und daher der das Argument FALSCH zum Einsatz kommt.

Und damit die Schneckenvariante…

SVERWEIS mit Raketenantrieb

Man bräuchte also eine Möglichkeit, die Vorteile der beiden oben genannten Varianten miteinander zu kombinieren. Also die exakte Übereinstimmung auf der einen Seite und die hohe Ausführungsgeschwindigkeit auf der anderen Seite.

Und genau das geht mit einer kleinen WENN-Funktion.

Um bei meinem Beispiel von oben zu bleiben:
=WENN(SVERWEIS(A2;G2:K10000;1;WAHR)=A2;SVERWEIS(A2;G2:K10000;3;WAHR);NV())

Einzige wichtige Voraussetzung ist, dass die zu durchsuchende Tabelle (hier also G2:K10000) aufsteigend sortiert ist!

Wie funktioniert dieser Raketenantrieb also?

Da die WAHR-Variante normalerweise ohne den kleinsten Hinweis auch ein nicht gewünschtes ähnliches Ergebnis liefert, fangen wir diesen Fall über eine WENN-Funktion ab. Damit wird geprüft, ob der Wert, den die erste SVERWEIS-Funktion zurückliefert, auch tatsächlich gleich dem Suchkriterium ist:
WENN(SVERWEIS(A2;G2:K10000;1;WAHR)=A2

Wichtig: Als Spaltenindex muss hier zwingend 1 verwendet werden!

Falls die Bedingung erfüllt ist (also eine exakte Übereinstimmung vorliegt), wird über den zweiten SVERWEIS der gewünschte Wert aus Spalte 3 ausgegeben.
Und falls es keine exakte Übereinstimmung gibt, wird über die NV-Funktion der Fehlerwert #NV! ausgegeben.

Wenn du jetzt der Meinung bist, dass zwei SVERWEISE verpackt in eine WENN-Funktion doch nicht so schnell sein können, dann irrst du dich.

Ein paar von mir durchgeführte Tests beweisen das Gegenteil. Dazu habe ich eine fiktive Kundentabelle gegen eine Bankentabelle abgeglichen, um über die BLZ den Banknamen anzureichern. Die zu durchsuchende Bankentabelle enthielt ca. 3.500 Datensätze, die Kundentabelle wahlweise 10.000, 50.000 und schließlich 100.000 Datensätze.

Und hier die Ergebnisse meiner kleinen Messreihe:

Tabelle mit den Messergebnissen

Tabelle mit den Messergebnissen

Bei 100.000 Datensätzen war die Variante mit der WENN-Funktion und den beiden SVERWEISEN 26 mal so schnell, wie der Standard-SVERWEIS. Was also zuerst über eine halbe Minute gedauert hat, war dann in weniger als 2 Sekunden erledigt!

Auch wenn die Ergebnisse nicht repräsentativ sind und im Einzelfall sicherlich anders ausfallen werden, sind die Unterschiede signifikant. Und wenn das kein Raketenantrieb ist, dann weiß ich es auch nicht…

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

13 Gedanken zu “SVERWEIS mit Raketenantrieb

  • Andreas Unkelbach

    Hallo Martin,

    vielen Dank für dein perfektes Timing und hier vorgestellte Beschleunigung für Verweisfunktionen. Eigentlich wollte ich heute mich intensiver mit einer Lösung in Richtung Datenmodell beschäftigen (dein Buch zu Pivot-Tabellen ist hier ein Fundus an guter Anregungen) aber nun scheine ich hier im Artikel eine bessere Variante entdeckt zu haben.

    Hintergrund ist, dass ich zu einer Liste aus Projektnummern entweder aus einer vorgegebenen Statistik oder einen Finanzbericht mir Daten ziehen muss mit entsprechend vielen Einzeldaten. Deine Idee mit einer ungefähren Suche und exakte Überprüfung des Suchergebnis dient mir gedanklich nun als Hilfsspalte, ob ein Wert vorhanden ist, so dass ich hier den Eintrag aus der führenden Statistik nehmen kann oder eben aus den anderen Bericht.

    Insgesamt kann ich mir hier also etliche Verweisfunktionen ersparen indem ich vorab prüfe, ob überhaupt Daten vorhanden sind.

    Vielen Dank für die Anregung, die nun tatsächlich weiter hilft und mir von der Idee schon so gut gefällt, dass hier ein bestehendes Konzept direkt wieder erneut hinterfragt wird.

    Viele Grüße
    Andreas

    • Martin Weiß Autor des Beitrags

      Hallo Andreas,

      danke für Dein Feedback und umso besser natürlich, wenn ich mit dem Artikel zufällig das richtige Timing erwischt habe. Die Möglichkeiten in Excel sind ja meistens sehr vielfältig, so dass die Auswahl des im konkreten Einzelfalls geeigneten Mittels (Tabellenfunktionen, Datenmodell/Pivot, PowerQuery etc) manchmal schwieriger ist, als die Problemlösung als solche.

      Ich freue mich auf jeden Fall, wenn ich dich auf ein paar neue Ideen gebracht habe und wünsche viel Spaß bei der Umsetzung!

      Schöne Grüße,
      Martin

  • Michael

    Hi Martin,

    bin ja immer wieder erstaunt, welche Kniffe du noch ausgraben kannst. Echt super!
    Da ich immer wieder User betreue, die aufgrund von vielen Datensätzen in Kombination mit dem SVerweis Performanceprobleme haben, werde ich einmal den „Raketenantrieb“ testen. Bin sehr gespannt!
    Einziger Nachteil ist die Sortierung, die beim Hinzufügen von Datensätzen vielleicht vergessen wird….

    Danke für diesen super Tipp!

    • Martin Weiß Autor des Beitrags

      Hallo Michael,

      ja, die Sortierung darf tatsächlich nicht vergessen werden, sonst funktioniert das leider nicht.

      Schöne Grüße,
      Martin

  • Jackie251

    Aus meiner Sicht fehlt ein wichtiger Hinweis auf Performance:
    Wenn man nicht nur einen Wert übernehmen muss sondern mehrerer Werte (zB Artikelnummer als Suchwert und übernommen werden sollen Preis, Warenkategorie und MWST Satz ; oder Personalnummer ist der Suchwert und Name, Vorname, Adresse usw). Dann ist der Sverweis die falsche Wahl, hier nutzt man dann Index/Vergleich.

    • Martin Weiß Autor des Beitrags

      Hallo Jackie251,

      du hast natürlich recht, INDEX und VERGLEICH sind immer eine gute Alternative.

      Schöne Grüße,
      Martin

  • Rosa Mick

    Hallo Martin,
    vielen Dank wieder mal für deinen tollen Tip. 👍Kann diesen Raketenantrieb sehr gut gebrauchen, da meine Tabellen immer sehr groß sind. Habe zum Raketenantrieb eine Frage, wie verhält sich der Sverweis wenn sich ich in meiner Tabelle Duplikate befinden? Diese kann ich leider nicht aus meiner Tabelle entfernen, da ich diese für Auswertungen benötige. Habe sie sonst immer unterschiedlich sortiert. Muss ich das jetzt auch machen?
    Viele Grüße Rosa

    • Martin Weiß Autor des Beitrags

      Hallo Rosa,

      Duplikate sind ein generelles Problem, aber das ist auch schon beim normalen SVERWEIS der Fall. Der SVERWEIS wird immer nur den ersten gefundenen Treffer ausgeben, die Sortierung hilft da nicht wirklich weiter. Es bleibt nur die Möglichkeit, eine Eindeutigkeit sicherzustellen. Das wäre über eine Hilfsspalte möglich oder über die Verwendung von mehreren Suchkritierien, wie in diesem Artikel beschrieben:
      https://www.tabellenexperte.de/super-sverweis/

      Schöne Grüße,
      Martin

  • Wilfried Kunze

    Hallo Martin,

    statt WAHR und FALSCH sind auch 1 und 0 zulässsig. Bevorzuge ich, weil es kürzer ist. Bei Sverweis ohne dieses letzte Argument wird kein ungefährer Wert ausgegeben, sondern der nächstkleinere. Deshalb sollten auch Lücken vermieden werden.

    Ich bereite eine Kleinigkeit in Excel vor, dann schicke ich etwas dazu.

    Viele Grüße

    Wilfried

    • Martin Weiß Autor des Beitrags

      Hallo Wilfried,

      0 und 1 gehen natürlich auch. Vielen Dank für die Ergänzungen.

      Schöne Grüße,
      Martin

  • Stephan Franke

    Hallo Martin,
    1000 Dank ! Das ist einer der genialsten Tipps, die ich in den letzten Monaten bekommen habe. Meine Tabellen haben bis zu einer halben Million Zeilen, die ich mit mehreren SVERWEISEN ergänzen muss. Dank des Raketenantriebs braucht es jetzt nur noch ein Viertel der Zeit.
    Es wäre super, wenn Du in Deinem Blog eine Art “Performance Rubrik” einrichten könntest. Oder einen Tag, unter dem man alle Tipps zur Verbesserung der Performance finden kann.
    Viele Grüße, Stephan

    • Martin Weiß Autor des Beitrags

      Hallo Stephan,

      freut mich, wenn der Tipp geholfen hat. Das mit der Performance-Rubrik muss ich mir noch überlegen.

      Schöne Grüße,
      Martin