Kurs
Google Sheets: Fortgeschrittene Funktionen
4 Std.
57.7K
Manchmal dreht sich Data Science nicht um Deep Learning, Big Data oder Empfehlungssysteme. Gerade wenn du in einer Branche wie der Finanzwelt arbeitest oder in einem Unternehmen, das stark auf Excel gesetzt hat, wirst du häufig mit Excel-Tabellen zu tun haben. Ich zähle mich selbst zur zweiten Gruppe: Mein Arbeitgeber arbeitete intensiv mit Excel, also musste ich mir für einige meiner Aufgaben viele seiner Funktionen aneignen.
Auch wenn R und Python Excel in vielen Bereichen übertreffen, bleibt Excel für bestimmte Aufgaben wie Business-Reports ein mächtiges Werkzeug. In diesem Tutorial konzentriere ich mich auf eine der integrierten Excel-Funktionen, die enorm hilft, wenn du gleichzeitig mit mehreren Tabellen arbeitest.
Die Rede ist vom SVERWEIS. SVERWEIS erlaubt es dir, verschiedene Spalten innerhalb eines Blatts oder zwischen Blättern derselben Arbeitsmappe zu verknüpfen. Im Kern suchst du einen Wert in einer Spalte und lässt dir den zugehörigen Wert aus einer anderen Spalte zurückgeben. Das ist besonders nützlich bei großen, unsortierten Datentabellen, die du über eine Spalte (idealerweise mit einem eindeutigen Schlüssel) in Beziehung setzen musst.
Ich zeige dir zunächst an einer kleinen Beispieltabelle, wie SVERWEIS zwischen Spalten im selben Blatt funktioniert:
Im Screenshot siehst du einen typischen SVERWEIS-Ausdruck, wie er in Business-Reports vorkommt. Die Bausteine sind:
- WENNFEHLER-Funktion: Ersetzt einen Fehler durch einen Bindestrich (-), wenn der Schlüssel leer ist oder im Datenbereich nicht vorkommt. Gute Praxis für Fehlermanagement, die das Blatt aufräumt – aber nicht zwingend nötig, damit SVERWEIS funktioniert.
- SVERWEIS-Argumente:
- E2 ist der Suchwert (hier z. B. KOG, es könnte aber jeder Schlüssel sein). Dieser Wert muss in der linken Spalte des gewählten Suchbereichs stehen.
- B1:C18 ist der Suchbereich/Array. Die linke Spalte hat den Index 1. Diese Spalte muss die Suchwerte enthalten, sonst funktioniert der SVERWEIS nicht.
- 2 ist die Spaltenindexnummer im gewählten Bereich, aus der der Wert zurückgegeben wird. Hier wollen wir die Beschreibung, also Spalte 2 des Bereichs.
- FALSCH kennzeichnet, dass du eine exakte Übereinstimmung möchtest. In diesem Fall wählen wir FALSCH, weil der KOG-Schlüssel exakt getroffen werden soll. Alternativ gäbe es WAHR für eine ungefähre Übereinstimmung – standardmäßig aktiv, wenn du das Argument weglässt. Für Schlüssel oder Zeichenketten ist WAHR in der Regel nicht zu empfehlen, da es zu unerwarteten Treffern führen kann.
Drückst du nach Eingabe des SVERWEIS-Ausdrucks die Eingabetaste, erhältst du folgendes Ergebnis:
Wie erwartet liefert SVERWEIS die Bezeichnung "King of Gondor", also die zur KOG-ID passende Beschreibung. Das gilt für jeden Schlüssel, den du in Zelle E2 eingibst. Schau dir z. B. an, was mit KIN passiert.
The King in the North! Genau – gibst du den KIN-Schlüssel ein, bekommst du die zugehörige Beschreibung für unseren lieben Jon (Snow).
SVERWEIS kann aber mehr, als nur einzelne Werte per Schlüssel zurückzugeben. Nehmen wir an, wir teilen die Tabelle und legen die beiden Teile auf zwei verschiedene Blätter derselben Arbeitsmappe.
Tabellen zusammenführen
 |
 |
Jetzt sind die Daten auf zwei Blätter (S1 und S2) verteilt, und die Schlüssel in S2 sind anders sortiert. Gut für uns: Die Schlüsselspalte ist in beiden Tabellen identisch, unabhängig von der Reihenfolge. Wir können also die Daten in S1 mit SVERWEIS zusammenführen (im Prinzip ein Left Join über eine Spalte). Die Syntax dafür sieht so aus:
Diesmal ist das erste Argument, also der Suchwert, B2, da wir den Schlüssel B2 in der Tabelle auf S2 nachschlagen wollen. Der Suchbereich ist nun die gesamte Datentabelle in S2 mit der ersten Spalte als gemeinsamer Schlüssel. Du siehst, dass der Suchbereich Dollarzeichen enthält. Mit F4 fixierst du den Bereich, damit SVERWEIS beim Herunterziehen der Formel in S1 weiterhin auf exakt diesen Bereich zeigt und nicht mitwandert. Die Spaltenindexnummer bleibt 2, weil die Beschreibung in S2 in der zweiten Spalte steht. Das letzte Argument ist wieder FALSCH für die exakte Übereinstimmung.
Zum Schluss ziehst du die Formel einfach bis zur letzten Zeile der Tabelle nach unten. So sieht das Ergebnis in S1 aus:
Voilá! Schon haben wir die Daten per SVERWEIS wieder zusammengefügt. Solche Joins sind in vielen Business-Szenarien üblich, in denen SQL, R oder Python nicht das bevorzugte Werkzeug der Entscheider ist. Beispiel: Zwei Dateien mit verschiedenen Details zu qualifizierten Sales Opportunities, die über eine eindeutige Opportunity-ID für den monatlichen Sales-Report in Excel verknüpft werden.
Ungefähre Übereinstimmungen nutzen
Bislang haben wir das letzte Argument WAHR (also die ungefähre Übereinstimmung) ignoriert, weil wir uns auf exakte, eindeutige Schlüssel konzentriert haben. Mit numerischen Daten kannst du WAHR jedoch gut einsetzen.
Stell dir vor, wir verkaufen Fische und gewähren folgende Rabatte je nach Abnahmemenge (Warum Fische? Ganz einfach: Fische sind großartig!).
Die SVERWEIS-Formel in Zelle F2 lautet diesmal:
Mit dieser Formel wird der Rabatt passend zum nächsten Schwellwert in der Tabelle ermittelt. Kaufen wir z. B. 25 Fische, erhalten wir 5% Rabatt; bei 70 Fischen 10% und bei 120 Fischen 15%. Hier sind die Beispiele:
 |
 |
 |
Einführung in INDEX-VERGLEICH
SVERWEIS ist stark, um Spalten zu verknüpfen. Eine Alternative ist die Kombination INDEX-VERGLEICH. Anders als SVERWEIS (eine einzelne Funktion) kombiniert INDEX-VERGLEICH zwei Funktionen und hat mehrere Vorteile gegenüber dem klassischen SVERWEIS, auch wenn die Schreibweise etwas aufwendiger ist.
Für dieses Tutorial schauen wir uns ein einfaches Beispiel an: Wir führen zwei Tabellen über einen gemeinsamen Schlüssel zusammen – wie oben mit SVERWEIS – und erklären dabei die Syntax (siehe unten).
Die Bausteine von INDEX-VERGLEICH sind:
- INDEX-Argumente:
- M2:M18: Der Spaltenbereich, aus dem Werte zurückgegeben werden sollen – hier also die Beschreibungen. Mit F4 fixieren wir den Bereich, damit er beim Herunterziehen stabil bleibt.
- VERGLEICH-Argumente:
- B2: Der Schlüssel, den wir nachschlagen – entspricht dem Suchwert bei SVERWEIS.
- L2:L18: Der Spaltenbereich mit den Schlüsseln, in dem der Suchwert gefunden werden soll. Auch hier nutzen wir F4.
- 0: Entspricht FALSCH bei SVERWEIS, also eine Flag für eine exakte Übereinstimmung.
An der Syntax erkennst du bereits einen Hauptvorteil von INDEX-VERGLEICH gegenüber SVERWEIS: die Spaltenunabhängigkeit. Fügen wir zwischen Schlüssel und Beschreibung in der rechten Tabelle eine zusätzliche Spalte ein, passt sich INDEX-VERGLEICH automatisch an und verweist weiterhin auf die Beschreibungs-Spalte. So sieht das aus:
Beim SVERWEIS geht das nicht ohne Formeländerung, weil der Spaltenbezug fest ist. Im obigen Beispiel hättest du zunächst 2 als Spaltenindexnummer für die Beschreibung gesetzt. Nach dem Einfügen der Spalte "Fish" liefert SVERWEIS aber den Wert aus dieser Spalte, weil sie nun Spalte 2 im Suchbereich ist. Du müsstest die Spaltenindexnummer manuell auf 3 ändern.
Um die Beschreibung links per INDEX-VERGLEICH an den Namen anzuschließen, gehst du genauso vor wie mit SVERWEIS: Zieh die Formel nach unten, wie hier gezeigt:
Zwischen verschiedenen Blättern derselben Arbeitsmappe funktioniert das identisch. Du wählst lediglich den Bereich mit den gemeinsamen Schlüsseln sowie den Zielbereich mit den zurückzugebenden Werten auf dem zweiten Blatt aus.
Fazit
Ich hoffe, dir hat das Tutorial gefallen. SVERWEIS und INDEX-VERGLEICH gehören zum Pflichtprogramm für alle, die im Business-Kontext mit Excel arbeiten. Ich empfehle dir, tiefer in INDEX-VERGLEICH einzusteigen – etwa für Groß/Kleinschreibungssensitivität (standardmäßig sind INDEX-VERGLEICH und SVERWEIS nicht casesensitiv) oder Abgleiche über mehrere Spalten. Bleib dran – es gibt immer mehr zu lernen!
Wenn du mehr über SVERWEIS erfahren möchtest, schau dir DataCamps Kurs Data Analysis with Spreadsheets an.