Excel XVerweis statt SVerweis

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.

Anwendungsbeispiel

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 €).

Nun möchte ich mit einer Formel die Tabelle durchsuchen, um herausfinden wie viel Geld die fiktive Person Melody Waterhouse verdient.

SVerweis

Dazu kann seit Version 1 von Excel SVerweis verwenden.

=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.

Index / Vergleich

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:

• Suche auch von rechts nach links und von unten nach oben möglich

Nachteil:

• Lesbarkeit der Formel schwieriger

Wie auch bei SVerweis erhält man als Ausgabe einen Fehler, wenn der Name nicht gefunden werden kann.

XVerweis

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:

• einfachste Verwendung
• eingebautes “nicht gefunden” Argument
• standardmäßig exakte Übereinstimmung eingestellt
• verschiedene Vergleichsmodi
• Suchrichtung einstellbar, da Such- und Rückgabematrix getrennt angegeben werden können

Nachteile:

• Kompatibilität

Verhalten bei nicht gefundenen Einträgen

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)

Geschwindigkeitsmessung

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 Long
Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
         "QueryPerformanceCounter" (cyTickCount As Currency) As Long

Function Microtimer() As Double
'

' Returns seconds.
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
    '
    Microtimer = 0

' Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency

' Get ticks.
    getTickCount cyTicks1

' Seconds
    If cyFrequency Then Microtimer = cyTicks1 / cyFrequency
End Function

Sub RangeTimer()
    DoCalcTimer 1
End Sub
Sub SheetTimer()
    DoCalcTimer 2
End Sub
Sub RecalcTimer()
    DoCalcTimer 3
End Sub
Sub FullcalcTimer()
    DoCalcTimer 4
End Sub

Sub DoCalcTimer(jMethod As Long)
    Dim dTime As Double
    Dim dOvhd As Double
    Dim oRng As Range
    Dim oCell As Range
    Dim oArrRange As Range
    Dim sCalcType As String
    Dim lCalcSave As Long
    Dim bIterSave As Boolean
    '
    On Error GoTo Errhandl

' Initialize
    dTime = Microtimer

    ' Save calculation settings.
    lCalcSave = Application.Calculation
    bIterSave = Application.Iteration
    If Application.Calculation <> xlCalculationManual Then
        Application.Calculation = xlCalculationManual
    End If
    Select Case jMethod
    Case 1

        ' Switch off iteration.

        If Application.Iteration <> False Then
            Application.Iteration = False
        End If

        ' Max is used range.

        If Selection.Count > 1000 Then
            Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
        Else
            Set oRng = Selection
        End If

        ' Include array cells outside selection.

        For Each oCell In oRng
            If oCell.HasArray Then
                If oArrRange Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                End If
                If Intersect(oCell, oArrRange) Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                    Set oRng = Union(oRng, oArrRange)
                End If
            End If
        Next oCell

        sCalcType = "Calculate " & CStr(oRng.Count) & _
            " Cell(s) in Selected Range: "
    Case 2
        sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
    Case 3
        sCalcType = "Recalculate open workbooks: "
    Case 4
        sCalcType = "Full Calculate open workbooks: "
    End Select

' Get start time.
    dTime = Microtimer
    Select Case jMethod
    Case 1
        If Val(Application.Version) >= 12 Then
            oRng.CalculateRowMajorOrder
        Else
            oRng.Calculate
        End If
    Case 2
        ActiveSheet.Calculate
    Case 3
        Application.Calculate
    Case 4
        Application.CalculateFull
    End Select

' Calculate duration.
    dTime = Microtimer - dTime
    On Error GoTo 0

    dTime = Round(dTime, 5)
    MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
        vbOKOnly + vbInformation, "CalcTimer"

Finish:

    ' Restore calculation settings.
    If Application.Calculation <> lCalcSave Then
         Application.Calculation = lCalcSave
    End If
    If Application.Iteration <> bIterSave Then
         Application.Iteration = bIterSave
    End If
    Exit Sub
Errhandl:
    On Error GoTo 0
    MsgBox "Unable to Calculate " & sCalcType, _
        vbOKOnly + vbCritical, "CalcTimer"
    GoTo Finish
End 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.

Test 1 - 100.000 Zellen berechnen

Test 1 - Aufbau

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:

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.

Ergebnisse des ersten Tests

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.

2D-Suche

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:

Jetzt wollen wir aber nicht die Tabelle entpivotisieren, sondern gleich durch die Angabe des Getränks und der Größe einen Preis erhalten.

2D-Suche mit Index / Vergleich

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))

2D-Suche mit SVerweis

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)

2D-Suche mit XVerweis

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]]))

Test 2 - 2D-Suche

Test 2 - Aufbau

Hier nahm ich den gleichen Aufbau wie bei Test 1, aber ergänzte noch vier weitere Spalten.

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.

Ergebnisse des zweiten Tests

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.

Test 3 - Weniger geordnete Daten

Test 3 - Aufbau

Für den dritten Test habe ich mir neue Beispieldaten generiert.

Ergebnisse des dritten Tests

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.

Test 4 - Ungenauer Vergleichsmodus

Aufbau

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:

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.

Ergebnisse des vierten Tests

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.

Mehrere Werte zurückgeben

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"))

Fazit

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.