Wenn Sie eine Tabelle mit vielen Datensätzen in Excel erstellt haben, können Sie schließlich zu einem Punkt kommen, an dem bestimmte Datensätze automatisch aus dieser Tabelle abgerufen und zur besseren Übersicht in eine andere Tabelle eingefügt werden sollen.
Also, was machst du so?
Natürlich haben Sie die Möglichkeit, mit der Pivot-Funktion einen oder mehrere Einzeltische zu erstellen und haben natürlich eine Vielzahl von Filteroptionen gleichzeitig.
Aber manchmal funktioniert es nicht so, wie du es dir vorgestellt hast.
Und genau hier kommen die Funktionen S-Referenz und W-Referenz ins Spiel.
Wie diese beiden Varianten funktionieren und wie man sie verwendet, möchte ich in diesem Artikel etwas näher erläutern.

Inhalt:

  • Definition S-Referenz und W-Referenz
  • Verfahren S-Referenz
  • Verfahren W-Referenz

1. Definition S-Referenz und W-Referenz

Im Allgemeinen klären wir zunächst die Definition der S-Referenz und der W-Referenz.
Die S-Referenz:
Das “S” steht für “vertikal” und beschreibt die Suchrichtung, in der Excel nach einem bestimmten Begriff sucht.
Was bedeutet das in Spalten für einen Begriff, wird gesucht, und wenn dieser gefunden wird, wird in der entsprechenden Zeile gesucht, um die Koordinate zu bestimmen.
siehe Bild: (zum Vergrößern anklicken)

Die W-Referenz:
Das “W” steht für “horizontal” und beschreibt auch die Suchrichtung, in der Excel nach einem bestimmten Begriff sucht.
Nur hier wird nicht zuerst in Spalten, sondern in Zeilen für einen bestimmten Begriff zur Bestimmung der Koordinate bestimmt.
siehe Bild: (zum Vergrößern anklicken)

SVerweis

2. Verfahren S-Referenz

Um das Ganze etwas plastischer zu gestalten, nehmen wir an, dass wir eine Rechnungsvorlage in Excel erstellen wollen, aber nicht jedes Mal alle Empfängerdaten (Name, Straße, Ort) eingeben müssen. Der sverweis funktioniert nicht, soll dies aber. Dann sollten wir zunächst eine geeignete Tabelle der Kundenstammdaten erstellen, die später als Datenquelle für unsere S-Referenz dienen wird. Als nächstes benötigen wir natürlich den Zielort (unsere Rechnungsvorlage).
In der Rechnung ist die Empfängeradresse nach Eingabe der Kundennummer automatisch einzutragen.
siehe Bild: (zum Vergrößern anklicken)

Damit das automatisierte Befüllen des Empfängerkopfes funktioniert, müssen wir nun mehrere S-Referenzen aufbauen, die 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 geben zunächst die Formel wie folgt ein:
= LOOKUP (
Nach dieser Eingabe teilt uns Excel bereits mit, welche Informationen zuerst benötigt werden. Dies sind die Suchkriterien.
Hier müssen wir auf die Zelle klicken, in der wir später unsere Kundennummer eingeben werden.
Was macht Excel, nachdem genau das, was dort eingegeben wurde, in der Datenquelle aussieht?
siehe Bild: (zum Vergrößern anklicken)

Als nächstes fragt Excel nach der Matrix, die nichts anderes ist als der Tabellenbereich, in dem nach dem Suchkriterium (unserer Kundennummer) gesucht werden soll.
In unserem Beispiel können wir die gesamte Tabelle mit unseren Kundenstammdaten einfach markieren.
siehe Bild: (zum Vergrößern anklicken)

Achten Sie darauf, dass Sie sich alle Argumente in der Formel merken, die jeweils ein Semikolon enthalten; zum Trennen.
Der nächste Schritt fragt nach dem Spaltenindex.
Hier wird einfach gezählt. Und in der Spalte wievielt der zuvor markierten Tabelle (nicht das gesamte Arbeitsblatt!) ist nach dem Eintrag im Feld “Vorname” zu suchen.
In unserem Beispiel ist es die dritte Spalte (in der die Vornamen stehen). Daher geben wir hier einfach eine 3 ein.
Der letzte Teil unserer S-Referenz ist nicht ganz unwichtig, da er nach true = 1 oder false = 0 fragt.
Das ist zunächst etwas verwirrend, bedeutet aber letztlich nichts anderes, wenn Excel genau nach dem Suchkriterium (wie es eingegeben wurde) suchen soll oder ob es ähnlich sein kann.
Für unser Beispiel suchen wir natürlich genau nach dem eingegebenen Namen und nicht nach einem ähnlichen.
Also geben wir eine “0” ein.
Unsere fertige S-Referenz für das erste Feld “Vorname” sollte so aussehen.
siehe Bild: (zum Vergrößern anklicken)

3. Verfahren W-Referenz

Die W-Referenz ist der S-Referenz relativ ähnlich.
Der Hauptunterschied liegt in der Suchrichtung.
Es wird nicht vertikal, sondern horizontal gesucht. Um bei unserem Berechnungsbeispiel zu bleiben, habe ich einfach die Tabelle mit der Datenquelle (Zeilen und Spalten vertauscht) transponiert, um die W-Referenz darzustellen.
Die Funktion, mit der wir zu unserem “Vornamen” in der Rechnung kommen, ist also:
= HLOOKUP (

Geben Sie dann erneut die Zelle mit dem Suchkriterium (die mit der Kundennummer) an.
als nächstes der Matrixbereich (die gesamte Tabelle mit den Stammdaten)
und jetzt, anstelle eines Spaltenindex, einfach einen Zeilenindex erhalten.
Nochmals, wir zählen einfach noch einmal nach. Und in welcher Zeile nach dem Vornamen gesucht werden soll.
Dies ist die dritte Zeile in unserem Beispiel. Also geben wir die 3 im Zeilenindex ein.
Und schließlich geben wir die “0” für “False” ein, um nach einer genauen Übereinstimmung zu suchen.
Unsere komplette Funktion sollte so aussehen.
siehe Bild: (zum Vergrößern anklicken)

Sowohl die S-Referenz als auch die W-Referenz können so vielfältig eingesetzt werden und führen je nach Ausgangsposition der Datenquelle zu ein und demselben Ergebnis.
Natürlich können Sie das Ganze erweitern.