Kaum eine andere Funktion wird von Excel-Anwendern gleichermaßen geliebt wie gehasst: Der SVERWEIS.
Wahrscheinlich können sich noch einige an des Gefühl der Zufriedenheit und des Stolzes erinnern, als man diese Funktion endlich gemeistert hat. In aktuellen Excel-Versionen wurde mit dem XVERWEIS ein mächtiger Nachfolger eingeführt.
Ist der SVERWEIS damit überflüssig geworden? Oder ist er vielleicht am Ende sogar doch besser als der XVERWEIS?
Mein geschätzter Kollege Dieter Schiecke von office-kompetenz hat sich dazu Gedanken gemacht und liefert im heutigen Gastartikel ein paar erstaunliche Erkenntnisse.
Und los geht’s!
Beispieldatei herunterladen
Seit Einführung der Funktion XVERWEIS lese ich oft, dass der SVERWEIS nun ausgedient hat. Doch stimmt das auch? Im folgenden Beispiel zeige ich, warum der SVERWEIS nach wie vor seine Berechtigung hat – zumal er jetzt eine Art Turbo bekommen hat.
Das Beispiel: Daten zu einem ausgewählten Kunden aus einer Tabelle auslesen
Im folgenden Bild sollen zu der Kundennummer in B3 die zutreffenden Daten in Zeile 6 angezeigt werden.
Die Kundendaten stehen in einer Tabelle ab Zeile 9.
Ein klassischer Anwendungsfall also für die Funktion SVERWEIS.
Zu der in B3 gewählten Kundennummer die passenden Kundendaten in Zeile 6 anzeigen
Das wäre das bisherige Vorgehen mit der Funktion SVERWEIS
Folgende Formel zeigt in C6 den Firmennamen zur gewählten Kundennummer an:
=SVERWEIS($B$3;tbl_Kunden;2;FALSCH)
Bisheriges Vorgehen mit SVERWEIS
Bisher musste diese Formel dann nach rechts kopiert werden. Allerdings hatte das den Haken, dass bei jeder der Kopien das dritte Argument in der SVERWEIS-Funktion, nämlich die Nummer für den Spaltenindex, noch angepasst werden musste.
Inzwischen jedoch geht das viel einfacher, zumindest in Excel 365 und Excel 2021. Denn in beiden Excel-Versionen gibt es das Prinzip des Überlaufverhaltens für Formeln. Es gilt nicht nur für die neuen dynamischen Arrayfunktionen wie EINDEUTIG oder FILTER, sondern auch für „alte“ Funktionen wie den SVERWEIS.
Klingt verwirrend? Kein Problem, im folgenden Abschnitt wird demonstriert, was gemeint ist.
Die neue, zeitsparende Schreibweise der SVERWEIS-Formel
Mit nur einer Formel lassen sich die Kundendaten aus der Tabelle ab Zeile 9 auslesen.
Dazu wird die Eingabe zum Spaltenindex – also zum dritten Argument im SVERWEIS – grundlegend geändert. Statt nur eine Spalte anzugeben, trage ich in geschweifte Klammern gleich alle Spaltennummern ein.
Für das hier gezeigte Beispiel lautet die Formel
=SVERWEIS(B3;tbl_Kunden;{2.3.4.5};FALSCH)
Nach dem Drücken der Enter-Taste wird das Ergebnis der Formel nicht nur in Zelle C6 angezeigt, sondern in den Zellen C6 bis F6. Das ist eine Folge des neuen Überlaufverhaltens von Formeln in Excel 365 und Excel 2021.
Die neue Schreibweise hat übrigens noch einen angenehmen „Nebeneffekt“: Der Zellbezug auf B3 kann relativ bleiben und muss nicht mehr mit Dollarzeichen auf absolut gesetzt werden ($B$3), da jetzt eine Formel alles erledigt und das Kopieren nach rechts entfällt.
Bei der neuen Schreibweise des SVERWEIS stehen ALLE Angaben zum Spaltenindex in geschweiften Klammern
Und wenn nicht alle, sondern nur ausgewählte Spalten gebraucht werden?
Bei Bedarf können auch nur Daten aus bestimmten Spalten ausgelesen werden. In der folgenden Abbildung werden nur der Firmenname und der Ort zu dem in B3 gewählten Kunden angezeigt. Das erledigt die Formel
=SVERWEIS(B3;tbl_Kunden;{2.5};FALSCH)
Nur bestimmte Daten auslesen, indem nur ausgewählte Spaltennummern in die geschweiften Klammern eingetragen werden
Natürlich lassen sich die Daten auch in beliebiger Reihenfolge anzeigen, zum Beispiel die zu PLZ, Ort und Firma. In dem Fall lautet die Formel
=SVERWEIS(B3;tbl_Kunden;{4.5.2};FALSCH)
Und wenn die Daten nicht horizontal, sondern vertikal stehen sollen?
Falls – wie in der folgenden Abbildung gezeigt – die Daten zu dem ausgewählten Kunden nicht nebeneinander, sondern untereinander gebraucht werden, ließe sich das durch Hinzufügen der Funktion MTRANS lösen.
Mit der Funktion MTRANS die anzuzeigenden Daten um 90° drehen
Der entscheidende Unterschied: Semikolon statt Punkt
Doch MTRANS wird gar nicht gebraucht, wenn die Angaben zu den gewünschten Spaltennummern zwischen den geschweiften Klammern nicht mit Punkt, sondern mit Semikolon getrennt werden.
Das Semikolon sorgt dafür, dass die gewünschten Daten nicht nebeneinander, sondern untereinander angezeigt werden.
Mit Semikolon statt Punkt in den geschweiften Klammern werden die Daten automatisch untereinander angeordnet
Fazit: Warum der SVERWEIS nach wie vor seine Berechtigung hat
Zum Schluss noch einige Argumente, die für die weitere Nutzung des SVERWEIS sprechen:
- Weit verbreitete Nutzung und Bekanntheit: Der SVERWEIS ist seit Jahrzehnten eine zentrale Funktion in Excel, und viele Anwender sind damit bestens vertraut.
- Benutzerfreundlichkeit: Der SVERWEIS ist für einfache Suchvorgänge ausreichend und bietet eine leicht verständliche Syntax, die auch für Einsteiger und Gelegenheitsbenutzer geeignet ist.
- >Konzentration auf vertikale Suchen: Die klare Fokussierung auf die vertikale Suche ohne zusätzliche Optionen macht SVERWEIS für einfache Anwendungen übersichtlicher und leichter zu handhaben.
- Abwärtskompatibilität: SVERWEIS ist in allen älteren Versionen von Excel verfügbar, was wichtig für Benutzergruppen ist, die mit verschiedenen Versionen arbeiten.
Danke an Dieter Schiecke für diesen spannenden Beitrag. Da wird der eine oder andere Leser doch erstaunt sein, wozu der SVERWEIS mittlerweile fähig ist. Im nächsten Artikel werde ich sozusagen diese Herausforderung annehmen und zeigen, wie man mit dem XVERWEIS die genannten Beispiele lösen kann. Dann wird sich zeigen, welche Funktion als Sieger aus diesem kleinen Duell hervorgeht.
- Experte für attraktive Tabellenoptik
- Verblüfft mit seiner Trickkiste der Zahlenformate
- Baut informative Diagrammlösungen
- Begeistert mit den Möglichkeiten der neuen dynamischen Arrayfunktionen
- Seit 1997 Autor zu Excel & PowerPoint
- Bloggt zu den Neuerungen in Office
- Microsoft Certified Excel Expert
Der geneigte Leser findet auf seinem Blog weitere interessante Artikel (nicht nur) über Excel.
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.
Hallo Martin,
Ich habe lange nichts mehr geschrieben, viele Grüße und nach wie vor Danke für deine Newsletter!
Es gibt noch einen Zusammenhang, in dem ich den SVERWEIUs noch sehr gerne nutze, ich nenne diese Kombination „das doppelte Lottchen“:
Aufgabe: Man braucht die Daten aus einer bestimmten Spalte, aber die Spalte mit den Zielwerten ist variabel.
Lösung: Man nimmt eine Hilfstabelle, in der man den Spaltenindex abhängig von der Zusatzbedingung einträgt.
=SVERWEIS(;
;
SVERWEIS(;;;FALSCH);
FALSCH)
(Solche komplexeren Formeln schreibe ich auch gerne mit Zeilenumbrücken [ALT-Enter) für eine bessere Lesbarkeit.
Natürlich gibt es ungefähr 5 andere Möglichkeiten, aber wenn man häufiger den S und X Verweis verwendet, ist diese Lösung intuitiver, zumindest für mich.
Hallo Thomas,
danke für diese Ergänzung. Es stimmt schon, es gibt meistens eine ganze Reihe von Lösungen, man muss nur die für einen persönlich geeignete finden 😉
Schöne Grüße,
Martin
Interessante Möglichkeit, aber für mich ist trotzdem die Kompatibilität nahezu der einzige Vorteil des SVERWEIS (bzw. vielmehr die nicht vorhandenen Kompatibilität ein Nachteil des XVERWEIS). Wenn ich bei bestimmten Kunden alte Excel-Versionen vorfinde nutze ich die Dinge, die ich bis vor 3, 4 Jahren genutzt habe, um die Formeln bzw konkret den Spaltenindex dynamisch zu gestalten – entweder mit VERGLEICH und der entsprechenden Überschrift oder auch einfach mit SPALTE, da sich in dem Fall der Bezug mit verschiebt, wenn nochmal Spalten vorher eingefügt werden, die den Index verschieben).
Aber insgesamt ist der XVERWEIS um ein vielfaches mächtiger, allein schon die möglichkeit, Ergebnisspalten links von der Kriterienspalte auszugeben, oder zu bestimmen, ob die nächstgrößere oder -kleinere Übereinstimmung ausgegeben werden soll, ebenso wie die Suchrichtung in der Suchmatrix oder für besondere Fälle die Möglichkeit, dass Suchmatrix und Ergebnismatrix sich nichtmal auf dem selben Blatt befinden müssen (kommt zugegebenermaßen nur sehr selten zum tragen).
Aus praktischen Gründen nutze ich ihn wirklich fast nur noch aus Kompatibilitätsgründen und eben für unsere Azubis, die noch auf älteren Excelversionen lernen und in der Berufsschule nie in den Genuss kommen von wirklich interessanten Funktionen 😉
Hallo Julius,
danke für deinen Kommentar. Und ehrlich gesagt halte ich es persönlich ganz genauso wie du 🙂
Trotzdem hat der SVERWEIS in bestimmten Szenarien seine Berechtigung – und natürlich vor allem für alle Anwender, die ihn eben nicht nutzen können.
Schöne Grüße,
Martin