Tabellenkalkulationen sind dafür bekannt, dass sie viele Formeln und Funktionen enthalten. Eine, die die Benutzer jedoch immer wieder fasziniert, ist die SVerweis-Formel, da sie eine Verbindung zu anderen Datenspalten herstellen kann. In diesem Excel SVerweis-Tutorial erfahren Sie, wie Sie SVerweis in Excel anhand von zwei Beispielen mit unterschiedlichen Zuordnungsarten verwenden können. (Enthält herunterladbare Excel-Beispiele).
Info-Symbol – Wenn Sie Google Sheets verwenden, lesen Sie bitte Google Sheets & SVerweis verwenden.
Als ich 2005 zum ersten Mal von dieser leistungsstarken Excel-Funktion hörte, warf ich einen Blick auf die Hilfedatei und die Syntax. Dann habe ich mit den Augen gerollt.
SVerweis (nachschlag_wert,tabelle_reihe,spalte_index_zahl,[bereich_nachschlag])
Es sah zu schwierig aus, und ich konnte den unmittelbaren Nutzen nicht erkennen. Aber manchmal kann es auf lange Sicht einfacher werden, wenn man sich durch Schwierigkeiten hindurchkämpft.
Wenn es darum geht, Microsoft Excel-Formeln und -Funktionen zu lernen, beginne ich gerne mit einem einfachen Beispiel. Dieses SVerweis-Tutorial enthält zwei Beispiele mit unterschiedlichen Argumenten und Suchwerten.
Was ist die Excel SVerweis-Funktion?
SVerweis ist eine Excel-Funktion, mit der Sie den Inhalt einer Zelle in einer Spalte suchen und an einer anderen Stelle verwenden können, um Daten abzurufen. Wie Sie vielleicht schon erraten haben, steht das “V” für vertikal und beruht auf der Suche nach Daten aus der Spalte ganz links in einer Nachschlagetabelle.
Diese Nachschlagetabelle kann sich auf demselben Arbeitsblatt befinden, das Sie gerade betrachten, oder auf einem anderen in Ihrer Arbeitsmappe. Die Funktion erfordert ein gemeinsames Feld oder einen Schlüssel und vier Argumente. Außerdem können Sie mit der Funktion angeben, ob Sie eine exakte Übereinstimmung oder eine ungefähre Übereinstimmung verwenden möchten.
Bringen wir diese Begriffe in einen Zusammenhang und geben ein konkretes Beispiel dafür, wo und wie die Argumente verwendet werden können. Bei der Analyse von Websites verwende ich mehrere Tools. Ich exportiere die Daten aus jedem Dienst in eine CSV-Datei oder eine Excel-XLSX-Datei. Für jedes Tool gibt es eine eigene Datei, mit der ich ein Blatt in einer Arbeitsmappe erstelle.
Das Problem ist, dass ich fünf Arbeitsblätter in dieser Arbeitsmappe habe. Ich möchte nicht zwischen den einzelnen Blättern hin- und herwechseln, weil das nicht effizient ist. Außerdem möchte ich einige meiner eigenen Berechnungen mit anderen Excel-Formeln durchführen.
Die Lösung besteht darin, einen gemeinsamen Nenner oder Schlüssel zwischen diesen Arbeitsblättern zu finden. In diesem Fall haben alle Datendateien aus meinen Analysetools ein Feld für die URL der Seite. Anhand dieses gemeinsamen Schlüssels kann ich ein neues Arbeitsblatt erstellen und mit der SVerweis-Funktion nur die benötigten Spalten aus jedem Tool abrufen. Auch hier übernimmt Excel die gesamte Arbeit. So kann ich mich auf ein Arbeitsblatt konzentrieren, das nur die Daten enthält, die ich brauche.
Excel SVerweis Beispiel – Ungefähre Übereinstimmung
Von Zeit zu Zeit melde ich mich freiwillig, um bei Kommunalwahlen mitzuarbeiten. Dabei werde ich oft mit der Datenanalyse der Wählerregistrierungsdatei beauftragt. Diese Dateien sind sehr umfangreich.
Ein Zellverweis enthält das Geburtsdatum des Wählers. Ich wollte jedoch nicht, dass das Geburtsdatum des Wählers in den endgültig verteilten Dateien erscheint. Ich wollte aber eine Altersanalyse durchführen.
Stattdessen beschloss ich, ein Segment auf der Grundlage von Altersbereichen und einer Nachschlageformel zu erstellen. Excel führt eine vertikale Suche durch, die den passenden Wert aus einer Spalte in die gewünschte Zelle zurückgibt. Anstatt zu zeigen, dass ein Wähler 28 Jahre alt ist, würde ich ihn als “jung” definieren.
Schauen wir uns den Screenshot oben mit meiner ersten fiktiven Wählerin, Sophia Collins, an. Wenn Sie die Spalte D (Alter) überfliegen, sehen Sie, dass sie 39 Jahre alt ist und sich im Segment “Reif” befindet. Das liegt daran, dass der Wert von “Reif” in Spalte E dynamisch mit der SVerweis-Funktion von Excel eingefügt wurde.
Die kleine Tabelle in den Spalten H und I mit blauen Überschriften ist die Nachschlagetabelle. Microsoft bezeichnet sie als table_array. Hier habe ich meine 4 Alterssegmente definiert.
Mein “Segment” funktioniert so: Wenn ein Wähler unter 21 ist, ist er “Neu”. Von 21-38 sind sie “Jung”. Von 39-59 sind sie “reif”. Und wenn sie 60 oder älter sind, sind sie “Senior”.
Im Fall von Sophia würde Excel ihr Alter von 39 Jahren aus Zelle D2 nehmen und die nächstliegende Übereinstimmung aus Spalte H zurückgeben.
Wenn Excel eine Übereinstimmung gefunden hat, geht es zu Spalte I und ruft das Label ab. Der zurückgegebene Wert wurde dann in die Zelle E2, das Segment, kopiert. Es könnte hilfreich sein, sich die Spalten als vertikal zu betrachten. Schließlich handelt es sich um eine vertikale Nachschlagetabelle.
Vielleicht fällt Ihnen auf, dass in der Nachschlagetabelle nicht jedes Alter aufgeführt ist. Das muss sie auch nicht, denn ich verwende eine ungefähre Übereinstimmung. Ich sage Excel, dass es das nächstliegende Alter finden soll.
Die nächste Wählerin, Evelyn Bennett, ist zum Beispiel 51 Jahre alt, aber es gibt keinen Wert für 51 in Spalte H. In diesem Fall liegt 51 zwischen 39 und 59 Jahren, so dass sie auch als “reif” bezeichnet wird.
Wie bereits erwähnt, muss es für die Verwendung der SVerweis-Funktion einen gemeinsamen Schlüssel geben. In diesem Fall ist es das Alter. Beide Spalten D und H enthalten Altersangaben. Die Spaltenüberschriften und Zellinhalte können unterschiedlich sein und müssen nicht exakt übereinstimmen. Ganz einfach Sverweis lernen mit Excelhero.