Viele Wege führen nach Rom, heißt es so schön. Das trifft natürlich auch bei Excel zu, es gibt viele unterschiedliche Ansätze, um letztlich zu dem selben Ergebnis zu gelangen. Aber einige davon sind nur schlecht lesbar, aufwendig zu aktualisieren und zudem auch noch sehr unflexibel.
Ein gutes Beispiel dafür ist der klassische SVERWEIS. Er ist extrem unflexibel, da man den genauen Spaltenindex innnerhalb seiner Suchmatrix angeben muss. Verändert sich die Matrix, weil zusätzliche Spalten eingefügt wurden, liefern die SVERWEISE plötzlich falsche Ergebnisse und müssen manuell angepasst werden. Außerdem spucken sie einen Fehler aus, wenn sie das Suchkriterium nicht in der Matrix finden können. Dieser Fehler muss ggf. abgefangen werden, wenn man ihn nicht im Ergebnis haben möchte.
Die älteste Lösung für dieses Problem sieht man auch heute noch am häufigsten und ist eine Kombination aus WENN und ISTFEHLER. Der SVERWEIS wird hier doppelt angegeben und muss natürlich auch doppelt von Excel ausgeführt werden.
=WENN(ISTFEHLER(SVERWEIS(E1;A:B;2;0));"";SVERWEIS(E1;A:B;2;0))
Eine etwas modernere Variante bietet die Funktion WENNFEHLER, welche aber scheinbar nicht so bekannt ist. Sie macht exakt das gleiche wie die erste Lösung, ist aber schneller, besser lesbar und bietet eine potentielle Fehlerquelle weniger, da der SVERWEIS nur noch einmal angegeben werden muss.
=WENNFEHLER(SVERWEIS(E1;A:B;2;0);"")
Die beste und modernste Variante gibt es erst seit einigen Monaten. Microsoft hat Excel um eine ganze Reihe sogenannter dynamischer Matrixformeln ergänzt. Eine davon ist der XVERWEIS, welcher quasi als Ersatz für SVERWEIS sowie VVERWEIS genutzt werden kann, aber viel flexibler ist und um einige sinnvolle Funktionen ergänzt wurde.
=XVERWEIS(E1;A:A;B:B;"";0;1)
Große Neuerungen beim XVERWEIS:
- Er beinhaltet gleich das Fehler-Handlung, mann benötigt also weder WENNFEHLER noch ISTFEHLER.
- Die Suchmatrix und die Ergebnismatrix werden separat angegeben und passen sich wie jede andere Formel mit Zell-, Zeilen- oder Spaltenbezug in Excel automatisch an, sobald neue Spalten oder Zeilen eingefügt werden.
- Die Suchreihenfolge kann angegeben werden. Verweise stoppen immer beim ersten Suchtreffer und ignorieren alle nachfolgenden Ergebnisse. Hat man eine nach Datum absteigend sortierte Liste, bekommt man beim SVERWEIS somit immer das älteste Datum als Ergebnis geliefert. Beim XVERWEIS kann angegeben werden, ob die Suche aufsteigend oder absteigend erfolgen soll. Die Formel selbst nimmt aber keine Sortierung vor, man bekommt halt nur den ersten Treffer von oben oder von unten.
- Für das Ergebnis können mehrere zusammenhängende Spalte angegeben werden. Das ist eine der Besonderheiten der dynamischen Matrixformeln. Anstatt mehrere einzelnen Verweise in jeder Spalte schreiben zu müssen, kann man jetzt in der ersten Spalte den XVERWEIS schreiben und in der Ergebnismatrix alle Spalten angeben, die einen interessieren (sofern sie zusammenhängend sind). Der XVERWEIS erweitert sich dann automatisch um so viele Spalten, wie für die Ausgabe der Ergebnisse erforderlich ist.
Laufzeiten
Nur weil etwas kompakter geschrieben wird, muss es nicht unbedingt schneller sein. In den offiziellen Microsoft Docs gibt es einen Beitrag, der sich um Formel-Laufzeiten dreht. Darin wird auch ein Makro zur Messung der Laufzeiten vorgestellt. Da die Kalkulationsgeschwindigkeit aber dadurch beeinflust wird, wie leistungsstark der Prozessor ist und wie stark er grade ausgelastet wird, gibt es teils starke Abweichungen zwischen jedem Ermittlungsversuch. Die Messung auf eine einzelne Zelle macht darüber hinaus wenig Sinn.
Ich habe daher die 3 oben vorgestellten Formeln jeweils auf eine Suchmatrix mit 3500 Einträgen angewendet und auch in 3500 Zellen mit der jeweiligen Formel das Ergebnis ermitteln lassen. Die Laufzeitberechnung erfolte dann via RangeTimer auf den jeweiligen Formel-Range. Bei 6 Durchgängen für jede der 3 vorgestellten Schreibweisen kam es im Durchschnitt zu folgenden Laufzeiten:
| Formelkombination | Durchschnittliche Laufzeit |
|---|---|
| WENN, ISTFEHLER + 2x SVERWEIS | 0,19176 Sekunden |
| WENNFEHLER + ISTVERWEIS | 0,11493 Sekunden |
| XVERWEIS | 0,19892 Sekunden |
Das Ergebnis ist natürlich nicht repräsentativ, da nur ein relativ kleiner Range betroffen ist und „nur“ 6 Durchläufe gemacht wurden. Aber es zeigt eine Tendenz auf. So liegen die klassische alte Vorgehensweise sowie der ganz neue XVERWEIS zeitlich in etwa gleich auf, was wohl dem größeren Funktionsumfang des XVERWEIS geschuldet ist. Klarer Sieger bezüglich der Laufzeit ist WENNFEHLER.
Hier geht es zwar nur um wenige Millisekunden, aber bei großen Dateien mit sehr vielen Formeln summiert sich das schnell und macht eine Datei unerträglich langsam.