Enthält bezahlte Promotion

Anzeige

So holen Sie gezielt Datensätze per S-Verweis und
W-Verweis aus Excel raus

Wenn Sie eine Tabelle mit vielen Datensätzen in Excel erstellt haben, kommen Sie vielleicht irgendwann ein einen Punkt wo bestimmte Datensätze automatisiert aus dieser Tabelle abgerufen und in eine andere Tabelle eingefügt werden sollen, um eine bessere Übersicht zu erhalten.

Was also tun?

Windows Lizenz deaktivieren / umziehen

Sie haben natürlich die Möglichkeit eine oder mehrere eigenständige Tabellen mit der Pivot Funktion zu erstellen, und haben damit natürlich auch gleichzeitig eine große Vielfalt an Filteroptionen.

Manchmal funktioniert es aber damit nicht so wie man es sich eigentlich vorgestellt hat.

Und genau hier kommen die Funktionen S-Verweis und W-Verweis ins Spiel.

Wie diese beiden Varianten arbeiten und wie man Sie anwenden kann möchte ich in diesem Artikel etwas näher beleuchten.

Anzeige
1296P Full HD Dashcam mit Nachtsicht Mini, Besteker HDR 1080P Fahrzeug Auto Kamera Dashboard fürs Car DVR Video Recorder Videoaufzeichnung, G-Sensor, Parkmonitor und Loop-Aufnahme(SD-Karte inklusive)
1296P Full HD Dashcam mit Nachtsicht Mini, Besteke...*
von Besteker

♥Besteker 1296P/1080P HDR Dashcam♥ HDR Nachtsicht Mit 7G...

Prime Preis: € 69,99 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. Juli 2018 um 19:50 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. Definition S-Verweis und W-Verweis

So holen Sie gezielt Datensätze per S-Verweis und W-Verweis aus Excel raus

Wenn Sie eine Tabelle mit vielen Datensätzen in Excel erstellt haben, kommen Sie vielleicht irgendwann ein einen Punkt wo bestimmte Datensätze automatisiert aus dieser Tabelle abgerufen und in eine andere Tabelle eingefügt werden sollen, um eine bessere Übersicht zu erhalten.

Was also tun?

Windows Lizenz deaktivieren / umziehen

Sie haben natürlich die Möglichkeit eine oder mehrere eigenständige Tabellen mit der Pivot Funktion zu erstellen, und haben damit natürlich auch gleichzeitig eine große Vielfalt an Filteroptionen.

Manchmal funktioniert es aber damit nicht so wie man es sich eigentlich vorgestellt hat.

Und genau hier kommen die Funktionen S-Verweis und W-Verweis ins Spiel.

Wie diese beiden Varianten arbeiten und wie man Sie anwenden kann möchte ich in diesem Artikel etwas näher beleuchten.

Anzeige
1296P Full HD Dashcam mit Nachtsicht Mini, Besteker HDR 1080P Fahrzeug Auto Kamera Dashboard fürs Car DVR Video Recorder Videoaufzeichnung, G-Sensor, Parkmonitor und Loop-Aufnahme(SD-Karte inklusive)
1296P Full HD Dashcam mit Nachtsicht Mini, Besteke...*
von Besteker

♥Besteker 1296P/1080P HDR Dashcam♥ HDR Nachtsicht Mit 7G...

Prime Preis: € 69,99 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. Juli 2018 um 19:50 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. Definition S-Verweis und W-Verweis

Klären wir ganz generell erstmal die Definition vom S-Verweis und W-Verweis.

Der S-Verweis:

Das „S“ steht hierbei für „senkrecht“ und beschreibt die Suchrichtung in welcher Excel nach einem bestimmten Begriff sucht.
Was bedeutet das in Spalten nach einem Begriff gesucht wird, und wenn dieser gefunden wurde wird in der entsprechenden Zeile weitergesucht um die Koordinate zu ermitteln.

siehe Abb.: (klicken zum vergrößern)

Anzeige

Der W-Verweis:

Das „W“ steht hierbei für „waagerecht“ und beschreibt ebenfalls die Suchrichtung in welcher Excel nach einem bestimmten Begriff sucht.
Nur wird hier nicht zuerst in Spalten sondern in Zeilen nach einem bestimmten Begriff ermittelt um die Koordinate zu bestimmen.

siehe Abb.: (klicken zum vergrößern)

Anzeige

Klären wir ganz generell erstmal die Definition vom S-Verweis und W-Verweis.

Der S-Verweis:

Das „S“ steht hierbei für „senkrecht“ und beschreibt die Suchrichtung in welcher Excel nach einem bestimmten Begriff sucht.
Was bedeutet das in Spalten nach einem Begriff gesucht wird, und wenn dieser gefunden wurde wird in der entsprechenden Zeile weitergesucht um die Koordinate zu ermitteln.

siehe Abb.:

Anzeige

Der W-Verweis:

Das „W“ steht hierbei für „waagerecht“ und beschreibt ebenfalls die Suchrichtung in welcher Excel nach einem bestimmten Begriff sucht.
Nur wird hier nicht zuerst in Spalten sondern in Zeilen nach einem bestimmten Begriff ermittelt um die Koordinate zu bestimmen.

siehe Abb.:

Anzeige

2. Vorgehensweise S-Verweis

2. Vorgehensweise S-Verweis

Um das ganze etwas plastischer zu gestalten nehmen wir mal an das wir uns eine Rechnungsvorlage in Excel bauen möchten, aber keine Lust haben jedesmal die ganzen Empfängerdaten (Name, Strasse, Ort) eintragen zu müssen.

Dann sollten wir also damit beginnen uns eine geeignete Tabelle mit Kundenstammdaten zu erstellen die uns später als Datenquelle für unseren S-Verweis dienen soll. Als nächstes brauchen wir dann natürlich noch das Ziel (unsere Rechnungsvorlage).

In der Rechnung soll später nach Eingabe der Kundennummer die Empfängeradresse automatisch ausgefüllt werden.

siehe Abb.: (klicken zum vergrößern)

Anzeige
Preistipp
Samsung Galaxy Tab A SM-T580 25,54 cm (10,1 Zoll) Tablet-PC (1,6 GHz Octa-Core, 2GB RAM, 32GB eMMC, Wifi, Android 6.0) Weiß
Samsung Galaxy Tab A SM-T580 25,54 cm (10,1 Zoll) ...*
von Samsung

Bildschirmdiagonale: 25,6 cm (10.1 Zoll), Bildschirmauflösu...

*Zuletzt aktualisiert am 18. Juli 2018 um 9:46 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Damit das automatisierte Ausfüllen des Empfängerkopfes funktioniert, müssen wir uns jetzt mehrere S-Verweise bauen, die zwar alle die gleiche Quelle verwenden, aber unterschiedliche Koordinaten benötigen um den richtigen Datensatz auszugeben.

Der erste Eintrag: Vorname

Wir klicken in die Zelle „Vorname“ und beginnen mit der Eingabe der Formel wie folgt:

=SVERWEIS(

  • Nach dieser Eingabe wird Excel uns bereits vorgeben welche Information als erstes benötigt wird. Es handelt sich hierbei um das Suchkriterum.
    Hier müssen wir die Zelle anklicken in welcher wir später unsere Kundennummer eintragen werden.

Was bedeutet das Excel nach genau dem was dort eingegeben wird in der Datenquelle suchen wird.

siehe Abb.: (klicken zum vergrößern)

Suchkriterium in S-Verweis festlegen
Anzeige

Als nächstes fragt Excel nach der Matrix was nichts anderes als der Tabellenbereich ist in welcher nach dem Suchkriterium (unsere Kundennummer) gesucht werden soll.

Dazu können wir in unserem Beispiel ruhig die gesamte Tabelle mit unseren Kundenstammdaten markieren.

siehe Abb.: (klicken zum vergrößern)

Matrix in S-Verweis festlegen
Anzeige
GoPro HERO5 Action Kamera (12 Megapixel) Schwarz/Grau
Unverb. Preisempf.: € 329,99 Du sparst: € 44,99 (14%) Prime Preis: € 285,00 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. Juli 2018 um 9:20 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Denken Sie auf jeden Fall daran alle Argumente die in der Formel sind jeweils mit einem Semikolon ; zu trennen.

Im folgenden Schritt wird nach dem Spaltenindex gefragt.
Hier wird ganz einfach gezählt. Und zwar in der wievielten Spalte der zuvor markierten Tabelle (nicht des gesamten Tabellenblattes!) soll nach dem Eintrag gesucht werden welcher in das Feld „Vorname“ soll.

In unserem Beispiel handelt es sich um die dritte Spalte (in welcher die Vornamen stehen). Somit geben wir hier einfach eine 3 ein.

Der letzte Teil unseres S-Verweises ist nicht ganz unwichtig, denn hier wird nach Wahr=1 oder Falsch=0 gefragt.

Das ist erstmal etwas verwirrend, bedeutet aber letztlich nichts anderes ob Excel nach genau dem Suchkriterium (so wie es eingegeben wurde) suchen soll, oder ob es auch ähnlich sein darf.

Für unser Beispiel suchen wir natürlich nach genau dem eingegebenen Namen, und nicht einem der ähnlich ist.
Also geben wir eine „0“ ein.

Unser fertiger S-Verweis für das erste Feld „Vorname“ sollte dann also wie folgt aussehen.

siehe Abb.: (klicken zum vergrößern)

S-Verweis fertige Formel
Anzeige

Um das ganze etwas plastischer zu gestalten nehmen wir mal an das wir uns eine Rechnungsvorlage in Excel bauen möchten, aber keine Lust haben jedesmal die ganzen Empfängerdaten (Name, Strasse, Ort) eintragen zu müssen.

Dann sollten wir also damit beginnen uns eine geeignete Tabelle mit Kundenstammdaten zu erstellen die uns später als Datenquelle für unseren S-Verweis dienen soll. Als nächstes brauchen wir dann natürlich noch das Ziel (unsere Rechnungsvorlage).

In der Rechnung soll später nach Eingabe der Kundennummer die Empfängeradresse automatisch ausgefüllt werden.

siehe Abb.:

Anzeige
Preistipp
Samsung Galaxy Tab A SM-T580 25,54 cm (10,1 Zoll) Tablet-PC (1,6 GHz Octa-Core, 2GB RAM, 32GB eMMC, Wifi, Android 6.0) Weiß
Samsung Galaxy Tab A SM-T580 25,54 cm (10,1 Zoll) ...*
von Samsung

Bildschirmdiagonale: 25,6 cm (10.1 Zoll), Bildschirmauflösu...

*Zuletzt aktualisiert am 18. Juli 2018 um 9:46 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Damit das automatisierte Ausfüllen des Empfängerkopfes funktioniert, müssen wir uns jetzt mehrere S-Verweise bauen, die zwar alle die gleiche Quelle verwenden, aber unterschiedliche Koordinaten benötigen um den richtigen Datensatz auszugeben.

Der erste Eintrag: Vorname

Wir klicken in die Zelle „Vorname“ und beginnen mit der Eingabe der Formel wie folgt:

=SVERWEIS(

  • Nach dieser Eingabe wird Excel uns bereits vorgeben welche Information als erstes benötigt wird. Es handelt sich hierbei um das Suchkriterum.
    Hier müssen wir die Zelle anklicken in welcher wir später unsere Kundennummer eintragen werden.

Was bedeutet das Excel nach genau dem was dort eingegeben wird in der Datenquelle suchen wird.

siehe Abb.:

Suchkriterium in S-Verweis festlegen
Anzeige

Als nächstes fragt Excel nach der Matrix was nichts anderes als der Tabellenbereich ist in welcher nach dem Suchkriterium (unsere Kundennummer) gesucht werden soll.

Dazu können wir in unserem Beispiel ruhig die gesamte Tabelle mit unseren Kundenstammdaten markieren.

siehe Abb.:

Matrix in S-Verweis festlegen
Anzeige
GoPro HERO5 Action Kamera (12 Megapixel) Schwarz/Grau
Unverb. Preisempf.: € 329,99 Du sparst: € 44,99 (14%) Prime Preis: € 285,00 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. Juli 2018 um 9:20 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Denken Sie auf jeden Fall daran alle Argumente die in der Formel sind jeweils mit einem Semikolon ; zu trennen.

Im folgenden Schritt wird nach dem Spaltenindex gefragt.
Hier wird ganz einfach gezählt. Und zwar in der wievielten Spalte der zuvor markierten Tabelle (nicht des gesamten Tabellenblattes!) soll nach dem Eintrag gesucht werden welcher in das Feld „Vorname“ soll.

In unserem Beispiel handelt es sich um die dritte Spalte (in welcher die Vornamen stehen). Somit geben wir hier einfach eine 3 ein.

Der letzte Teil unseres S-Verweises ist nicht ganz unwichtig, denn hier wird nach Wahr=1 oder Falsch=0 gefragt.

Das ist erstmal etwas verwirrend, bedeutet aber letztlich nichts anderes ob Excel nach genau dem Suchkriterium (so wie es eingegeben wurde) suchen soll, oder ob es auch ähnlich sein darf.

Für unser Beispiel suchen wir natürlich nach genau dem eingegebenen Namen, und nicht einem der ähnlich ist.
Also geben wir eine „0“ ein.

Unser fertiger S-Verweis für das erste Feld „Vorname“ sollte dann also wie folgt aussehen.

siehe Abb.:

S-Verweis fertige Formel
Anzeige

3. Vorgehensweise W-Verweis

3. Vorgehensweise W-Verweis

Der W-Verweis ist dem S-Verweis relativ gleich.

Der Hauptunterschied liegt hierbei in der Suchrichtung.
Es wird hier also nicht senkrecht sondern waagerecht gesucht. Um bei unserem Rechnungsbeispiel zu bleiben, habe ich die Tabelle mit der Datenquelle einfach mal transponiert (Zeilen und Spalten vertauscht) um den W-Verweis darzustellen.

Die Funktion mit der wir zu unserem „Vornamen“ in der Rechnung kommen lautet demnach:

=WVERWEIS(

Dann erneut die Zelle mit dem Suchkriterium festlegen (Die mit der Kundennummer)

als nächstes den Matrixbereich (Die gesamte Tabelle mit den Stammdaten)

und nun kommt statt einem Spaltenindex einfach ein Zeilenindex.
Auch hier zählen wir einfach wieder durch. Und zwar in welcher Zeile soll nach dem Vornamen gesucht werden.

Dies ist in unserem Beispiel die dritte Zeile. Also geben wir beim Zeilenindex die 3 ein.
Und zum Schluss geben wir noch die „0“ für „Falsch“ ein, damit nach einer genauen Übereinstimmung gesucht wird.

Unsere komplette Funktion sollte dann wie folgt aussehen.

siehe Abb.: (klicken zum vergrößern)

W-Verweis fertige Formel
Anzeige
Sieger
OnePlus 6 Smartphone (15,95 cm (6,28 Zoll) 19:9 Touch-Display, 128 GB interner Speicher, Android 8.1 Oreo/Oxygen OS 5.1), Midnight Black
OnePlus 6 Smartphone (15,95 cm (6,28 Zoll) 19:9 To...*
von OnePlus

Dank des super schnellen Qualcomm Snapdragon 845 Prozessors ...

Prime Preis: € 569,00 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. Juli 2018 um 9:46 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Sowohl der S-Verweis als auch der W-Verweis lassen sich so sehr vielfältig einsetzen, und führen je nach Ausgangslage der Datenquelle zu ein und dem selben Ergebnis.

Sie können das ganze natürlich im Funktionsumfang noch deutlich erweitern, und auch mit anderen Funktionen verschachteln (z.B. mit einer Wenn-dann Funktion), aber bereits in der Basisvariante lassen sich mit diesen recht einfachen Funktionen einige Arbeiten gut erledigen.

Anzeige
Blogverzeichnis Bloggerei.de

Der W-Verweis ist dem S-Verweis relativ gleich.

Der Hauptunterschied liegt hierbei in der Suchrichtung. Es wird hier also nicht senkrecht sondern waagerecht gesucht. Um bei unserem Rechnungsbeispiel zu bleiben, habe ich die Tabelle mit der Datenquelle einfach mal transponiert (Zeilen und Spalten vertauscht) um den W-Verweis darzustellen.

Die Funktion mit der wir zu unserem „Vornamen“ in der Rechnung kommen lautet demnach: =WVERWEIS(

Dann erneut die Zelle mit dem Suchkriterium festlegen (Die mit der Kundennummer) als nächstes den Matrixbereich (Die gesamte Tabelle mit den Stammdaten) und nun kommt statt einem Spaltenindex einfach ein Zeilenindex.

Auch hier zählen wir einfach wieder durch. Und zwar in welcher Zeile soll nach dem Vornamen gesucht werden.

Dies ist in unserem Beispiel die dritte Zeile. Also geben wir beim Zeilenindex die 3 ein. Und zum Schluss geben wir noch die „0“ für „Falsch“ ein, damit nach einer genauen Übereinstimmung gesucht wird.

Unsere komplette Funktion sollte dann wie folgt aussehen.

siehe Abb.:

W-Verweis fertige Formel
Anzeige
Sieger
OnePlus 6 Smartphone (15,95 cm (6,28 Zoll) 19:9 Touch-Display, 128 GB interner Speicher, Android 8.1 Oreo/Oxygen OS 5.1), Midnight Black
OnePlus 6 Smartphone (15,95 cm (6,28 Zoll) 19:9 To...*
von OnePlus

Dank des super schnellen Qualcomm Snapdragon 845 Prozessors ...

Prime Preis: € 569,00 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. Juli 2018 um 9:46 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Sowohl der S-Verweis als auch der W-Verweis lassen sich so sehr vielfältig einsetzen, und führen je nach Ausgangslage der Datenquelle zu ein und dem selben Ergebnis.

Sie können das ganze natürlich im Funktionsumfang noch deutlich erweitern, und auch mit anderen Funktionen verschachteln (z.B. mit einer Wenn-dann Funktion), aber bereits in der Basisvariante lassen sich mit diesen recht einfachen Funktionen einige Arbeiten gut erledigen.

Print Friendly, PDF & Email
Gastautor werden

Wir veröffentlichen Ihren Gastbeitrag kostenlos in Deutsch und Englisch

Anzeige
Blogverzeichnis Bloggerei.de

Über den Autor:

Michael Suhr
Michael SuhrWebdesigner / Betriebswirt
Nach 20 Jahren im Logistikmanagement bin ich seit Anfang 2015 als freiberuflicher Webdesigner und Office Trainer tätig. Nebenbei gebe ich soweit es die Zeit zulässt noch Tipps & Tricks für mehr digitale Kompetenz in meinem Blog.

Artikelsuche nach Kategorie:

Anzeige
SanDisk Ultra 128GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1, FFP
Unverb. Preisempf.: € 52,99 Du sparst: € 13,00 (25%) Prime Preis: € 39,99 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. Juli 2018 um 9:21 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

Über den Autor:

Michael Suhr
Michael SuhrWebdesigner / Betriebswirt
Nach 20 Jahren im Logistikmanagement bin ich seit Anfang 2015 als freiberuflicher Webdesigner und Office Trainer tätig. Nebenbei gebe ich soweit es die Zeit zulässt noch Tipps & Tricks für mehr digitale Kompetenz in meinem Blog.

Artikelsuche nach Kategorie:

Anzeige
SanDisk Ultra 128GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1, FFP
Unverb. Preisempf.: € 52,99 Du sparst: € 13,00 (25%) Prime Preis: € 39,99 zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. Juli 2018 um 9:21 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-07-18T16:49:26+00:00By |Categories: Excel|Tags: , , |
Um unsere Webseite für Sie optimal zu gestalten und fortlaufend verbessern zu können, verwenden wir Cookies. Durch die weitere Nutzung der Webseite stimmen Sie der Verwendung von Cookies zu. Ok