Der SVERWEIS (Senkrecht-Verweis) ist eines der mächtigsten Werkzeuge in Excel. Er ermöglicht es Ihnen, Daten aus einer Tabelle basierend auf einem Suchkriterium zu extrahieren. Während die grundlegende Anwendung relativ einfach ist, eröffnet die Beherrschung von fortgeschrittenen Techniken wie die Verwendung mehrerer Suchkriterien und dynamisch variierender Verweise eine völlig neue Dimension der Datenanalyse und Automatisierung. Dieser Artikel richtet sich an Excel-Anwender, die ihre Fähigkeiten im Umgang mit dem SVERWEIS auf ein professionelles Niveau heben möchten.
Grundlagen des SVERWEIS
Bevor wir uns den komplexeren Anwendungsfällen widmen, ist es wichtig, die Grundlagen des SVERWEIS zu verstehen. Die Syntax der Funktion lautet:
„`excel
=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])
„`
* **Suchkriterium:** Der Wert, nach dem in der ersten Spalte der Matrix gesucht wird.
* **Matrix:** Der Zellbereich, in dem gesucht und der Ergebniswert gefunden wird. Die erste Spalte dieser Matrix muss das Suchkriterium enthalten.
* **Spaltenindex:** Die Nummer der Spalte in der Matrix, aus der der Ergebniswert zurückgegeben werden soll (beginnend bei 1).
* **[Bereich_Verweis]:** Ein optionaler Wert, der entweder WAHR (ungefähre Übereinstimmung) oder FALSCH (genaue Übereinstimmung) sein kann. In den meisten Fällen, insbesondere bei der Arbeit mit Textdaten, sollte FALSCH für eine genaue Übereinstimmung verwendet werden.
Ein einfaches Beispiel: Angenommen, Sie haben eine Tabelle mit Kundennamen in Spalte A und Kundennummern in Spalte B. Um die Kundennummer für einen bestimmten Kundennamen zu finden, würden Sie den SVERWEIS wie folgt verwenden:
„`excel
=SVERWEIS(„Max Mustermann”; A1:B100; 2; FALSCH)
„`
Hier sucht der SVERWEIS nach „Max Mustermann” in der ersten Spalte des Bereichs A1:B100 und gibt den Wert aus der zweiten Spalte (Kundennummer) zurück, wenn eine genaue Übereinstimmung gefunden wird.
Die Herausforderung: Multiple Suchkriterien
Der Standard-SVERWEIS kann nur mit einem einzigen Suchkriterium arbeiten. Was aber, wenn Sie Daten basierend auf mehreren Kriterien suchen müssen? Zum Beispiel, wenn Sie den Preis eines Produkts basierend auf Produktname *und* Größe ermitteln möchten. Es gibt verschiedene Möglichkeiten, dieses Problem zu lösen:
1. Hilfsspalte erstellen
Die einfachste Methode ist, eine Hilfsspalte zu erstellen, in der die einzelnen Suchkriterien zu einem eindeutigen Schlüssel kombiniert werden. Angenommen, die Produktnamen befinden sich in Spalte A und die Größen in Spalte B. Sie können in Spalte C eine Hilfsspalte erstellen, die die beiden Werte kombiniert:
„`excel
=A1&”_”&B1
„`
Dieser Formel würde beispielsweise „Produktname_Größe” in Spalte C erstellen.
Nun können Sie den SVERWEIS verwenden, indem Sie auch in der Suchtabelle eine entsprechende Hilfsspalte erstellen und diese als Suchkriterium verwenden:
„`excel
=SVERWEIS(A1&”_”&B1; Tabelle2!C:D; 2; FALSCH)
„`
Hier sucht der SVERWEIS nach der kombinierten Suchkriterium in Spalte C von „Tabelle2” und gibt den Wert aus Spalte D zurück.
2. Verwendung von INDEX und VERGLEICH
Eine flexiblere Alternative zur Hilfsspalte ist die Kombination der Funktionen INDEX und VERGLEICH. Diese Methode ist besonders nützlich, wenn Sie vermeiden möchten, die Originaldaten zu verändern.
Die VERGLEICH-Funktion gibt die relative Position eines Elements innerhalb eines Bereichs zurück. Die INDEX-Funktion gibt den Wert einer Zelle an einer bestimmten Position in einem Bereich zurück.
Um den Preis eines Produkts basierend auf Produktname und Größe zu finden, verwenden Sie folgende Formel:
„`excel
=INDEX(Preisbereich;VERGLEICH(1;(Produktnamenbereich=Suchprodukt)*(Größenbereich=Suchgröße);0))
„`
* **Preisbereich:** Der Bereich, der die Preise enthält (z.B. C1:C100).
* **Produktnamenbereich:** Der Bereich, der die Produktnamen enthält (z.B. A1:A100).
* **Suchprodukt:** Die Zelle, die den Produktnamen enthält (z.B. E1).
* **Größenbereich:** Der Bereich, der die Größen enthält (z.B. B1:B100).
* **Suchgröße:** Die Zelle, die die Größe enthält (z.B. F1).
Diese Formel funktioniert, indem sie zwei Bedingungen innerhalb der VERGLEICH-Funktion kombiniert. `(Produktnamenbereich=Suchprodukt)` erzeugt ein Array von WAHR- und FALSCH-Werten, wobei WAHR bedeutet, dass der Produktname in dieser Zeile mit dem Suchprodukt übereinstimmt. Dasselbe gilt für `(Größenbereich=Suchgröße)`. Durch die Multiplikation der beiden Arrays werden WAHR-Werte zu 1 und FALSCH-Werte zu 0. VERGLEICH sucht dann nach dem ersten Vorkommen von 1 (WAHR), was bedeutet, dass beide Bedingungen erfüllt sind. INDEX verwendet dann diese Position, um den entsprechenden Preis aus dem Preisbereich zurückzugeben. **Wichtig:** Diese Formel muss als Array-Formel eingegeben werden. Drücken Sie nach der Eingabe STRG+UMSCHALT+EINGABE, anstatt nur EINGABE. Excel fügt dann automatisch geschweifte Klammern um die Formel ein.
Dynamisch unterschiedlicher Verweis
Eine weitere fortgeschrittene Technik ist die dynamische Anpassung des Verweisbereichs des SVERWEIS. Dies ist nützlich, wenn sich die Größe der Datenquelle ändert oder wenn Sie den SVERWEIS verwenden möchten, um aus verschiedenen Tabellen basierend auf einer Bedingung zu suchen.
1. Verwendung von INDIREKT
Die Funktion INDIREKT ermöglicht es Ihnen, einen Zellbereich als Textstring anzugeben. Dies kann verwendet werden, um den Verweisbereich des SVERWEIS dynamisch zu ändern.
Angenommen, Sie haben Daten in zwei Tabellen mit den Namen „Tabelle1” und „Tabelle2”. Sie möchten den SVERWEIS verwenden, um Daten aus der entsprechenden Tabelle basierend auf einem Wert in Zelle A1 abzurufen.
„`excel
=SVERWEIS(Suchkriterium;INDIREKT(WENN(A1=”Wert1″;”Tabelle1!A:B”;”Tabelle2!A:B”));2;FALSCH)
„`
Hier prüft die WENN-Funktion, ob der Wert in Zelle A1 „Wert1” ist. Wenn ja, wird „Tabelle1!A:B” als Textstring zurückgegeben. Andernfalls wird „Tabelle2!A:B” zurückgegeben. INDIREKT wandelt diesen Textstring dann in einen Zellbereich um, der vom SVERWEIS verwendet wird.
2. Verwendung von VERWEIS und BEREICH.VERSCHIEBEN
Die Funktion BEREICH.VERSCHIEBEN gibt einen Bereich zurück, der von einem Ausgangspunkt aus verschoben wurde. Dies kann verwendet werden, um den Verweisbereich des SVERWEIS dynamisch zu definieren, basierend auf der Größe der Daten. Diese Technik ist komplexer, ermöglicht aber eine sehr flexible Steuerung des Verweisbereichs.
Beispiel: Angenommen, Ihre Daten beginnen in Zelle A1 und erstrecken sich dynamisch nach unten und nach rechts. Sie möchten den SVERWEIS verwenden, um Daten aus einer Spalte abzurufen, deren Nummer dynamisch bestimmt wird.
„`excel
=SVERWEIS(Suchkriterium;BEREICH.VERSCHIEBEN(A1;0;0;ANZAHL2(A:A);Anzahl_Spalten);Spaltennummer;FALSCH)
„`
* **A1:** Der Ausgangspunkt des Bereichs.
* **0;0:** Verschiebung vom Ausgangspunkt (keine in diesem Fall).
* **ANZAHL2(A:A):** Die Höhe des Bereichs (die Anzahl der nicht leeren Zellen in Spalte A).
* **Anzahl_Spalten:** Die Breite des Bereichs (die Anzahl der Spalten, die in den Verweis einbezogen werden sollen. Diese kann statisch sein oder dynamisch berechnet werden).
* **Spaltennummer:** Die Nummer der Spalte, aus der der Wert zurückgegeben werden soll.
Diese Formel erstellt einen dynamischen Bereich, der sich automatisch an die Größe der Daten anpasst.
Fehlerbehandlung mit WENNFEHLER
Beim Arbeiten mit dem SVERWEIS kann es vorkommen, dass das Suchkriterium nicht gefunden wird. In diesem Fall gibt der SVERWEIS den Fehlerwert #NV zurück. Um diesen Fehler zu vermeiden und eine benutzerfreundlichere Ausgabe zu erzeugen, kann die Funktion WENNFEHLER verwendet werden:
„`excel
=WENNFEHLER(SVERWEIS(Suchkriterium;Matrix;Spaltenindex;FALSCH);”Nicht gefunden”)
„`
Diese Formel gibt den Wert zurück, der vom SVERWEIS gefunden wird, wenn keine Fehler auftreten. Andernfalls wird der Text „Nicht gefunden” zurückgegeben.
Fazit
Der SVERWEIS ist ein unverzichtbares Werkzeug für jeden Excel-Anwender. Durch die Beherrschung fortgeschrittener Techniken wie die Verwendung mehrerer Suchkriterien und dynamischer Verweise können Sie Ihre Datenanalyse- und Automatisierungsfähigkeiten erheblich verbessern. Experimentieren Sie mit den vorgestellten Methoden und passen Sie sie an Ihre spezifischen Anforderungen an. Mit etwas Übung werden Sie den SVERWEIS bald wie ein Profi beherrschen!