Am 28. August 2019 kündigte Microsoft die inzwischen nicht mehr ganz so neue Formel XVerweis für Excel an. Diese Formel sollte die Formel SVerweis (eine der beliebtesten Formeln in Excel) ersetzen.
XVerweis ist unter anderem Bestandteil von Microsoft Office 365 (Affiliate Link) und Excel 2016 (und neuer) für Windows sowie Microsoft Excel 2021 (und neuer) für macOS.
Als XVerweis meine installierte Excel-Version erreichte, fing ich an, sie lieber zu nutzen als SVerweis. Für diejenigen, die sich fragen, was überhaupt SVerweis ist oder wo die Unterschiede liegen, folgen gleich noch Beispiele.
Beachten musste man, dass der Empfänger der Excel-Datei ebenso eine aktuelle Excel-Version installiert haben sollte, ansonsten wäre eine Weiterverarbeitung nicht mehr möglich.
Diesen Umstand ausgenommen habe ich beispielsweise in diversen Excel-Foren oder auf Reddit mehrfach gelesen, XVerweis sei schneller, dementsprechend solle man SVerweis nicht mehr benutzen. Ich glaubte diesen Aussagen, ohne sie zu verifizieren und erzählte es so meinen Kollegen weiter.
Hier möchte ich zeigen, wie sich die Funktion XVerweis von SVerweis unterscheidet und insbesondere untersuchen, ob XVerweis wirklich schneller ist.
Ich habe mir zum Testen über Mockaroo eine Tabelle mit zufälligen Daten erstellt. Ich habe drei Spalten mit 1.000 Einträgen. In der ersten Spalte befindet sich eine individuelle ID (von 1 bis 1000), in der zweiten Spalte ein Name und in der dritten ein (Jahres)Gehalt (zwischen 95,52 € und 280.000,00 €).
id | name | salery |
---|---|---|
1 | Ellene Otley | 45.488,00 € |
2 | Noell Jiggen | 28.307,64 € |
3 | Dolorita Proud | 49.755,88 € |
… | … | … |
Nun möchte ich mit einer Formel die Tabelle durchsuchen, um herausfinden wie viel Geld die fiktive Person Melody Waterhouse verdient.
Dazu kann seit Version 1 von Excel SVerweis verwendet werden.
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
In meinem Fall verwende ich die Formal folgendermaßen.
=SVERWEIS("Melody Waterhouse";{Bereich mit Namen und Gehalt};2;0)
Die Formel sucht in der ersten Spalte des Suchbereichs nach dem gewünschten Namen und gibt dann die zweite Spalte aus, außerdem stelle ich mit der 0 am Ende eine genaue Übereinstimmung ein.
Probleme können hierbei auftreten, wenn man die 0 als letztes Argument vergisst. Ebenso kann es nervig sein, wenn man viele Spalten hat. Die Suche kann auch nur von links nach rechts erfolgen. Würde das Gehalt links vom Namen stehen, würde die Formel nicht funktionieren. Würde das Gehalt unter den Namen stehen, müsste stattdessen die Funktion WVerweis verwendet werden, um von oben nach unten zu suchen.
Dass standardmäßig die ungenaue Suche eingestellt ist, ist meiner Meinung nach ein Design-Fehler. In diesem Fall verstehe ich nicht einmal, warum nicht die Person bei id 1 angezeigt wird, schließlich liegt L im Alphabet zwischen E und N.
In der Vergangenheit habe ich oftmals auf die Kombination aus den Formeln Index und Vergleich zurückgegriffen, wenn ich mehr Anpassungsmöglichkeiten haben wollte.
=INDEX(Matrix;Zeile;Spalte)=VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp)=INDEX(Matrix;VERGLEICH(Suchkriterium;Suchmatrix;Vergleichstyp);Spalte)
Bei der Index-Formel gebe ich die Matrix ein, aus der ich die Informationen erhalten möchte (in meinem Fall die Spalte mit den Gehältern). Die absolute Zeile kenne ich nicht, dementsprechend wird sie mit der Vergleich-Formel gesucht.
Vorteil:
Nachteil:
Wie auch bei SVerweis erhält man als Ausgabe einen Fehler, wenn der Name nicht gefunden werden kann.
Die neue Funktion XVerweis löst einige der Einschränkungen der anderen Formeln.
=XVERWEIS(Suchkriterium;Suchmatrix;Rückgabematrix;wenn_nicht_gefunden;Vergleichsmodus;Suchmodus)
Die ersten drei Argumente müssen angegeben werden, der Rest ist optional.
Hier ist die Verwendung mehr oder weniger selbsterklärend.
Vorteile:
Nachteile:
Wie bereits angesprochen, ist in XVerweis ein Parameter integriert, der dafür sorgt, dass bei nicht gefundenen Einträgen ein anderer Wert zurückgegeben wird.
Bei der Verwendung von Index oder SVerweis kann man nur auf die Formel WENNFEHLER zurückgreifen.
=WENNFEHLER(Wert;Wert_falls_Fehler)
Nun interessiert mich aber die Geschwindigkeit der Verweismöglichkeiten.
Um die Geschwindigkeit zu messen, habe ich mich an der Dokumentation von Microsoft orientiert.
Also habe ich mir ein neues Modul mit folgendem Code erstellt.
Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _"QueryPerformanceFrequency" (cyFrequency As Currency) As LongPrivate Declare PtrSafe Function getTickCount Lib "kernel32" Alias _"QueryPerformanceCounter" (cyTickCount As Currency) As LongFunction Microtimer() As Double'' Returns seconds.Dim cyTicks1 As CurrencyStatic cyFrequency As Currency'Microtimer = 0' Get frequency.If cyFrequency = 0 Then getFrequency cyFrequency' Get ticks.getTickCount cyTicks1' SecondsIf cyFrequency Then Microtimer = cyTicks1 / cyFrequencyEnd FunctionSub RangeTimer()DoCalcTimer 1End SubSub SheetTimer()DoCalcTimer 2End SubSub RecalcTimer()DoCalcTimer 3End SubSub FullcalcTimer()DoCalcTimer 4End SubSub DoCalcTimer(jMethod As Long)Dim dTime As DoubleDim dOvhd As DoubleDim oRng As RangeDim oCell As RangeDim oArrRange As RangeDim sCalcType As StringDim lCalcSave As LongDim bIterSave As Boolean'On Error GoTo Errhandl' InitializedTime = Microtimer' Save calculation settings.lCalcSave = Application.CalculationbIterSave = Application.IterationIf Application.Calculation <> xlCalculationManual ThenApplication.Calculation = xlCalculationManualEnd IfSelect Case jMethodCase 1' Switch off iteration.If Application.Iteration <> False ThenApplication.Iteration = FalseEnd If' Max is used range.If Selection.Count > 1000 ThenSet oRng = Intersect(Selection, Selection.Parent.UsedRange)ElseSet oRng = SelectionEnd If' Include array cells outside selection.For Each oCell In oRngIf oCell.HasArray ThenIf oArrRange Is Nothing ThenSet oArrRange = oCell.CurrentArrayEnd IfIf Intersect(oCell, oArrRange) Is Nothing ThenSet oArrRange = oCell.CurrentArraySet oRng = Union(oRng, oArrRange)End IfEnd IfNext oCellsCalcType = "Calculate " & CStr(oRng.Count) & _" Cell(s) in Selected Range: "Case 2sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "Case 3sCalcType = "Recalculate open workbooks: "Case 4sCalcType = "Full Calculate open workbooks: "End Select' Get start time.dTime = MicrotimerSelect Case jMethodCase 1If Val(Application.Version) >= 12 ThenoRng.CalculateRowMajorOrderElseoRng.CalculateEnd IfCase 2ActiveSheet.CalculateCase 3Application.CalculateCase 4Application.CalculateFullEnd Select' Calculate duration.dTime = Microtimer - dTimeOn Error GoTo 0dTime = Round(dTime, 5)MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _vbOKOnly + vbInformation, "CalcTimer"Finish:' Restore calculation settings.If Application.Calculation <> lCalcSave ThenApplication.Calculation = lCalcSaveEnd IfIf Application.Iteration <> bIterSave ThenApplication.Iteration = bIterSaveEnd IfExit SubErrhandl:On Error GoTo 0MsgBox "Unable to Calculate " & sCalcType, _vbOKOnly + vbCritical, "CalcTimer"GoTo FinishEnd Sub
Dadurch habe ich jetzt die Möglichkeit eine Arbeitsmappe neu berechnen zu lassen oder komplett neu berechnen zu lassen und mir anzeigen zu lassen, wie lange es gedauert hat.
Insbesondere ich die Funktion mit Application.CalculateFull
genutzt, aber auch mal Application.Calcuate
ausprobiert.
Für den ersten Test habe ich eine neue Arbeitsmappe erstellt. Ein Arbeitsblatt enthält Daten in zwei Spalten. Eine davon enthält die ID, die andere eine zufällige Zahl zwischen 0 und 1:
ID | Wert |
---|---|
1 | 0.97714411 |
2 | 0.57334089 |
3 | 0.86522436 |
… | … |
100000 | 0.04843126 |
Auf einem anderen Arbeitsblatt habe ich die 100000 Zeilen mit der ID kopiert und versuche den dazugehörigen Wert über die verschiedenen Verweisarten zu erhalten.
Testgerät ist ein Laptop mit einer AMD Ryzen 4600H CPU.
Zunächst habe ich den RangeTimer verwendet und damit geschaut, wie lange die Berechnung der jeweiligen Spalten braucht. Das klappte irgendwie nicht so gut, dann im Gegensatz zu SVerweis, dessen Bereich ca. 0,06 Sekunden für das Update brauchte, benötigte die Index/Vergleich Kombination ca. 64 Sekunden und XVerweis sogar über zwei Minuten. Also bin ich dann dazu übergangen, jeweils eine eigene Datei zu erstellen. Zuerst habe ich mehrfach eine komplette Neuberechnung durchgeführt und die durchschnittliche Dauer miteinander verglichen:
Hier habe ich mich gewundert, ob ich etwas falsch mache, denn XVerweis war doch deutlich langsamer als die beiden anderen Verweisarten. Ich habe es nochmal mit einem Surface (Intel I5 Gen 8 CPU) probiert. Ich kam zu einem sehr ähnlichen Ergebnis:
Die Hypothese, dass XVerweis eine bessere Performance hat, konnte dadurch nicht verifiziert werden. Also versuchte ich den Test zu variieren.
Ich versuchte es hier nochmal mit dem RecalcTimer (also keine komplette Neuberechnung):
AMD:
INTEL:
Dies stellt im Übrigen keinen Vergleich zwischen Intel und AMD CPUs an sich dar, die beiden Geräte sind völlig unterschiedlich.
Wenn nicht anders angegeben, nutze ich im Folgenden den FullcalcTimer, weil ich wissen möchte, wie lange der Aufbau im Worst-Case dauert.
Einen messbaren Leistungsunterschied gab es hier des Weiteren nicht. Dann habe ich in meinem Arbeitsblatt die 100 auf dem ersten Arbeitsblatt in eine 1.000.000 geändert. Dadruch konnte im zweiten Blatt der zu 100 gehörende Wert nicht mehr gefunden werden:
AMD:
Intel:
Merkwürdig ist, dass durch den Fehler die gesamte Berechnung deutlich schneller funktioniert. XVerweis war immer noch langsamer als die anderen Formeln, wenn auch dieses Mal unmerklich.
Als Nächstes habe ich versucht, den Fehler gegen ein leeres Feld zu korrigieren. Bei XVERWEIS mit dem eingebauten Parameter und bei den anderen beiden Formeln mit einer zusätzlichen WENNFEHLER Abfrage:
AMD:
Intel:
Insgesamt schien XVerweis nicht schneller zu sein, deshalb wandelte ich den Test nochmal um.
Das Tolle an den Verweis-Formeln ist, dass auch eine Suche mit einem variablen Spaltenindex möglich ist. Nehmen wir an, wir haben eine Preisliste eines Kaffeeshops:
Getränk | Tall | Grande | Venti |
---|---|---|---|
Caffè Americano | 3,39 | 3,89 | 4,39 |
Caffè latte | 3,99 | 4,59 | 4,99 |
Earl Gray | 2,69 | 3,19 | 3,69 |
Coffee Frapuccino | 4,79 | 5,29 | 5,79 |
Iced White Chocolate Mocha | 4,99 | 5,49 | 5,99 |
Jetzt wollen wir aber nicht die Tabelle entpivotisieren, sondern gleich durch die Angabe des Getränks und der Größe einen Preis erhalten.
Bei Index können wir als dritten Parameter die Spalte angeben. Wir möchten erfahren, wie teuer der Caffè Americano in der Größe Venti ist.
=INDEX(A1:D6; 2; 4)oder=INDEX(B2:D6; 1; 3)
Nur wissen wir halt nicht unbedingt, dass sich Caffè Americano an erster Stelle befindet. Deshalb brauchen wir die Vergleichsformel:
=INDEX(A1:D6; VERGLEICH("Caffé Americano"; A1:A6; 0); 3)oder=INDEX(B2:D6; VERGLEICH("Caffé Americano"; A2:A6; 0); 3)
Nun wissen wir auch nicht, dass sich Venti ganz rechts befindet, also muss das auch ersetzt werden:
=INDEX(A1:D6; VERGLEICH("Caffé Americano"; A1:A6; 0), VERGLEICH("Venti"; A1:D1; 0))oder=INDEX(B2:D6; VERGLEICH("Caffé Americano"; A2:A6; 0); VERGLEICH("Venti"; B1:D1; 0))
Bei SVerweis ist die gleiche Funktionalität nur durch Vergleich ergänzbar. Normalerweise würden wir ja folgende Formel eingeben:
=SVERWEIS("Caffè Americano"; A1:D6; 4; 0)
Die die richtige Spalte (in diesem Fall “4”) können wir mit Vergleich suchen:
=SVERWEIS("Caffè Americano"; A1:D6; VERGLEICH("Venti"; A1:D1; 0); 0)
Bei XVerweis funktioniert die grundlegende Suche so:
=XVERWEIS("Caffè Americano"; A1:A6; D1:D6)oder=XVERWEIS("Caffè Americano"; A2:A6; D2:D6)
Die Rückgabematrix muss sich aber variabel verschieben können. An dieser Stelle nutzen wir einfach eine zweite XVerweis Formel.
=XVERWEIS(Getränk;A2:A6;XVERWEIS(Größe;B1:D1;B2:D6))in einer Tabelle:=XVERWEIS(Getränk;Preisliste[Getränk];XVERWEIS(Größe;Preisliste[[#Kopfzeilen];[Tall]:[Venti]];Preisliste[[Tall]:[Venti]]))
Hier nahm ich den gleichen Aufbau wie bei Test 1, aber ergänzte noch vier weitere Spalten.
ID | 1 | 2 | 3 | 4 | 5 |
---|---|---|---|---|---|
1 | 0.97714411 | 0.34313251 | 0.47387243 | 0.92325136 | 0.70448042 |
2 | 0.57334089 | 0.59227904 | 0.72094532 | 0.62649883 | 0.10888043 |
… | … | … | … | … | … |
100000 | 0.04843126 | 0.44973681 | 0.49224276 | 0.37683903 | 0.03069793 |
Die Tabelle auf dem zweiten Blatt bekam eine weitere Spalte mit einer ganzen Zahl zwischen 1 und 5. Gefunden werden sollte dann der Wert in der jeweiligen Spalte.
ID | Spalte | Formel |
---|---|---|
1 | 1 | =FORMEL |
2 | 2 | =FORMEL |
3 | 1 | =FORMEL |
4 | 3 | =FORMEL |
5 | 3 | =FORMEL |
… | … | … |
100000 | 4 | =FORMEL |
Auch hier führte ich mehrfach eine Neuberechnung durch und verglich die durchschnittliche Dauer miteinander.
AMD:
INTEL:
Wieder konnte XVerweis von der Performance nicht besser als die anderen Verweisarten abschneiden.
Für den dritten Test habe ich mir neue Beispieldaten generiert.
id | first_name | last_name | gender | ip_address | |
---|---|---|---|---|---|
1 | Andee | Audibert | aaudibert0@jigsy.com | Female | 139.9.209.124 |
2 | Kaitlyn | Bollard | kbollard1@chronoengine.com | Female | 32.192.138.202 |
… | … | … | … | … | … |
1000 | Layton | Geely | lgeely7@pen.io | Agender | 193.33.184.242 |
Dann habe ich auf einem weiteren Blatt versucht, über die ID die IP-Adresse zu finden. Ab hier nutze ich nur noch den AMD Laptop, weil die relativen Abweichungen zwischen den Geräten unerheblich waren.
Dann habe ich über die ID die E-Mail-Adresse gesucht. Diesmal aber als 2D-Suche.
Auch hier war XVerweis leider nicht schneller als die anderen beiden Methoden.
Dann habe ich nochmal den RecalcTimer statt dem FullcalcTimer verwendet.
Anschließend dachte ich, dass es vielleicht einen Unterschied macht, ob ich in einer geordneten Reihe suche oder nicht. Deshalb nahm ich die zuvor erzeugten fiktiven E-Mail-Adressen und ordnete sie alphabetisch. Zu der E-Mail suchte ich nun in der ursprünglichen Liste nach der IP-Adresse. Diesmal probierte ich die Neuberechnung mit dem RangeTimer.
XVerweis war enttäuschenderweise deutlich langsamer in der Neuberechnung.
Nun gibt es aber noch den alternativen Vergleichsmodus, der bei SVERWEIS standardmäßig eingestellt ist. Auch diesen wollte ich probieren.
Ich nahm meine Anfangsdaten (ID, Name, Jahreseinkommen) und wollte die gemäß § 32a EStG berechnen.
Das machte ich relativ umständlich, indem ich drei neue Spalte namens “x”, “y” und “z” anlegte.
x:=ABRUNDEN(Jahreseinkommen; 0)y:=MAX((x-9984)/10000;0)z:=MAX((x-14926)/10000;0)
Nun legte ich in einem anderen Bereich zusätzlich Daten an. Wichtig ist, dass die Daten in der Suchspalte (in meinem Fall zvE) aufsteigend sortiert sind:
zvE | Index |
---|---|
0 | 0 |
9985 | 1 |
14927 | 2 |
58597 | 3 |
277826 | 4 |
Damit konnte ich wählen, welche Formel für die Berechnung genutzt wird, im Beispiel an SVerweis folgendermaßen.
=ERSTERWERT(SVERWEIS({Jahreseinkommen};{Der zweite Datenbereich};2;1);0;0;1;(1008,7*y+1400)*y;2;(206,43*z+2397)*z+938,24;3;0,42*x-9267,53;4;0,45*x-17602,28)
Was passiert hier? Beim SVERWEIS wird im zweiten Datenbereich nach dem Formel-Index gesucht. Beispielsweise hat Ellene Otley in meinen Beispieldaten ein Einkommen in Höhe von 45.888 €. Jetzt sucht SVerweis im anderen Bereich danach, jedoch mit dem ungenauen Vergleichsmodus. SVERWEIS schaut, ob die Zahl größer als oder gleich 9985 ist. Danach, ob die Zahl größer als oder gleich 14.927 ist. Danach, ob die Zahl größer als oder gleich 58.597 ist. 45.888 ist kleiner als 58.597, deshalb geht SVERWEIS einen Schritt zurück und gibt aus der zweiten Spalte die “2” zurück.
Mit ERSTERWERT kann ich dann definieren, was passieren soll, wenn eine 2 zurückgegeben wird.
=ERSTERWERT({Hier nutze ich eine der Vergleichfunktionen und bekomme eine Zahl zwischen 0 und 4 zurück};0;{das soll bei der 0 passieren};1;{das soll bei der 1 passieren};usw.)
Damit komme ich dann mit 45.888 € bei 10.338,76 € raus. Das bedeutet, bei einem Jahreseinkommen von 45.888 € zahlt man ungefähr 23 % Einkommenssteuer.
Auch hier war die XVerweis Funktion nicht schneller als die anderen Methoden. Beim RecalcTimer hatte XVerweis bessere Werte, jedoch sind die Zahlen alle sehr klein. Eine kleine Verzögerung durch einen externen Faktor spielt eine größere Rolle.
Anschließend fragte ich mich, ob sich durch die Verlagerung der Spalten “x”, “y” und “z” direkt in die Formel etwas ändert. Ich löschte diese drei Spalten und passte die Formel der Steuerberechnung an.
=LET(x;ABRUNDEN({Jahreseinkommen};0);y;MAX((x-9984)/10000;0);z;MAX((x-14926)/10000;0);ERSTERWERT({wie bisher}))
Die Ergebnisse sind auch hier wieder sehr ähnlich. Auch beim RecalcTimer:
Dann habe ich noch zum Vergleich den Verweis komplett herausgenommen und in die Formel integriert, um zu sehen, ob dann die Berechnung schneller wird:
=LET(zve;{Jahreseinkommen};x;ABRUNDEN(zve;0);y;MAX((x-9984)/10000;0);z;MAX((x-14926)/10000;0);ERSTERWERT(WAHR;zve<9985;0;zve<14927;(1008,7*y+1400)*y;zve<58597;(206,43*z+2397)*z+938,24;zve<277826;0,42*x-9267,53;0,45*x-17602,28))
Damit wurde die komplette Neuberechnung auf 0.00937 Sekunden im Durchschnitt gedrückt. Beim RecalcTimer waren es 0,0002 Sekunden. Das zeigt, dass eine Verweisfunktion nicht immer die beste Lösung sein muss.
Vollständigkeitshalber möchte ich auch noch erwähnen, dass es möglich ist, mehrere Werte mit den Funktionen in Excel zurückzugeben. Dafür kann die Filter-Funktion verwendet werden.
Ich bekomme in diesem Beispiel das Obst als Matrix zurück.
Möchte ich alle Werte in einer Zelle haben, kann ich die Funktion mit der Funktion TEXTVERKETTEN kombinieren.
=TEXTVERKETTEN(", ";1;FILTER(tbl_Nahrungsmittel[Nahrungsmittel];tbl_Nahrungsmittel[Art]=D2;"nicht gefunden"))
Die Annahme, die XVerweis-Funktion sei schneller als die SVerweis-Funktion, konnte ich nicht bestätigen. Im Gegenteil wer es eher so, dass XVerweis langsamer war als Index oder SVerweis. Heißt das, dass ich empfehle SVerweis zu nutzen? Nein, insbesondere dann nicht, wenn man die zusätzlichen Argumente in XVerweis benötigt. Außerdem dürfte es in den meisten normalen Anwendungsfällen der Unterschied kaum merkbar sein. Ich zeige nur, dass es immer noch Anwendungsfälle geben kann, in denen es sinnvoll ist, SVerweis zu nutzen, außerdem möchte ich hiermit widerlegen, XVerweis sei die schnellere Formel.