Wodurch unterscheiden sich Excel-Profis von Excel-Gelegenheitsnutzern? Unter anderem durch die Anwendung von Matrixfunktionen. Der Klassiker ist der SVERWEIS. Diese Funktion hat viele praktische Einsatzmöglichkeiten, denn man kann damit einen Wert in einer Tabelle nachschlagen und in einer anderen Tabelle nutzen. Somit lassen sich Arbeitsmappen einfacher und besser gestalten. Es lohnt sich, den SVERWEIS verstehen zu lernen.
Stellen Sie sich vor, Sie arbeiten als Verkäufer (m/w) und müssen Ihrem Vorgesetzten periodisch darüber informieren, wo Ihnen in welcher Höhe Aufträge zugesagt wurden. Sie führen dazu eine Auftragsliste in Excel und möchten diese automatisch um den Sitz der Kunden ergänzen lassen. Es steht Ihnen eine Kopie einer zentral verwalteten Adressenliste zur Verfügung. Stark vereinfacht sieht diese Adressenliste in Excel so aus:
Die Zellen wurden als Tabelle formatiert, damit wir den Namen dieser definierten Tabelle („Adressen“) in unseren Formeln einsetzen können statt fehleranfälliger Zellbezüge.
Wie der SVERWEIS die Welt sieht
Für uns Menschen ist die Adressenliste eindeutig: Es gibt vier unterschiedliche Kunden. Der SVERWEIS sieht das aber auf Anhieb ganz anders. Das erfahren wir, wenn wir die entsprechenden Formeln in die (vereinfachte) Auftragsliste eingeben.
Die Funktion in Zelle B2 liest den Wert in Zelle A2, sucht diesen Wert in der ersten Spalte der Tabelle „Adressen” und gibt den Wert zurück, die sie dort in der zweiten Spalte (2) der entsprechenden Zeile findet.
SVERWEIS geht immer davon aus, dass sie den Wert in der ersten Spalte der in der Formel definierten Suchmatrix suchen soll. Die Funktion gibt immer einen Wert zurück, den sie rechts davon in der angegebenen Spalte (in diesem Fall also die zweite Spalte) findet. In diesem Beispiel ist Spalte 1 der Suchmatrix zufällig auch Spalte A des Arbeitsblatts, aber man kann grundsätzlich einen beliebigen Zellbereich als Suchmatrix definieren.
Wir kopieren die SVERWEIS-Formel aus Zelle B2 herunter in die Zellen B3 bis B5. Das Ergebnis ist enttäuschend:
Laut SVERWEIS haben alle Kunden namens Müller ihren Sitz in München, es gibt einen Kunden namens Neuling und weiter niemanden. Warum ist das so? Erstens beendet der SVERWEIS die Suche, sobald er fündig wird. Die Funktion ist für das Nachschlagen eines einzelnen Wertes gedacht! Da in der Tabelle „Adressen“ bereits in Zelle A2 der Wert „Müller“ gefunden wird und in Zelle A3 ein anderer Wert steht, gibt die Funktion den Wert „München“ zurück und hält die Aufgabe für erledigt. Der Wert „Grönemeyer“ wird nicht gefunden, weil der SVERWEIS im Standardmodus eine aufsteigend sortierte Liste erwartet. Wird diese Erwartung nicht länger erfüllt, wie hier nach „Neuling“, dann ist die Liste für SVERWEIS zu Ende.
Aufsteigend sortieren? Ist nicht immer die Lösung!
Das „Grönemeyer-Problem” könnten wir beheben, indem wir die Namen in unserer Suchmatrix aufsteigend sortieren.
Auf den ersten Blick klappt das:
Bei näherem Hinsehen werden wir misstrauisch: Haben alle Müllers den Sitz von München nach Dortmund verlegt? Hier wird klar, dass der SVERWEIS anders vorgeht, als wir dachten. Es werden keine exakten Werte gesucht; die Werte werden klassifiziert! Das ist beim SVERWEIS immer so, wenn das vierte Argument der Formel leer, „1“ oder „WAHR“ ist. In diesem Modus durchläuft der SVERWEIS die Zeilen bis er auf einen Wert trifft, der größer als der gesuchte Wert ist. Er geht dann eine Zeile zurück und wählt den dortigen Wert (in diesem Fall in Spalte 2) als Ergebnis der Formel aus. Diese Methode ist sehr nützlich, wenn man zum Beispiel auf unterschiedliche Beträge die Rabatte berechnen will. Die Suchmatrix enthält dann in der Regel nur die Rabattsätze für bestimmte Stufen, z. B. den Satz für 50.000 €, für 100.000 € usw. In unserem Fall sind aber exakte Werte gefragt. Dafür gilt:
Beim Suchen exakter Werte mit SVERWEIS immer das vierte Argument „FALSCH“ eingeben!
Dies ist sehr wichtig, denn in umfangreicheren Dateien übersieht man das „Fehlverhalten“ der Funktion. Ihre Berichte sehen dann gut aus, sind aber völlig fehlerhaft! Die ergänzte Formel wird wiederum herunterkopiert:
Eindeutige Werte für den SVERWEIS
Jetzt müssen wir dem SVERWEIS noch den Unterschied zwischen den beiden Müllers beibringen. Wir werden dafür sorgen, dass jeder Müller in der ersten Spalte der Suchmatrix ein eindeutiges Merkmal bekommt. Kundennummern sind dafür gut geeignet und die sind im Adressenverwaltungssystem Ihrer Firma auch vorhanden. Zum Ausfüllen der Auftragsliste ist eine Kundennummer aber nicht so praktisch, denn Sie kennen diese Nummern meistens nicht auswendig. Als Lösung werden wir der Suchmatrix eine Spalte hinzufügen, in der das eindeutige Merkmal (Kundennummer) und der erkennbare Namen verknüpft werden.
Im Screenshot sehen Sie am Beispiel von Zelle A2, wie das in einer definierten Tabelle geht. Ohne diese Tabelle wäre die Formel: =B2&” “&C2.
In der Auftragsliste passen wir die SVERWEIS-Formel entsprechend an: Der Wert aus Spalte 4 soll zurückgegeben werden. Anschließend versehen wir die Spalte „Kunde” (A) mit einer Auswahlliste. Markieren Sie dazu diese Spalte und wählen Sie im Register „Daten“, in der Gruppe „Datentools“ den Befehl „Datenüberprüfung“ aus. Wählen Sie unter „Zulassen” die Option „Liste” aus und geben Sie als Quelle Folgendes ein: =INDIREKT(„Adressen[Kombination]“;FALSCH).
Beim Eingeben der zugesagten Aufträge können Sie jetzt den richtigen Müller auswählen. Bei Bedarf fügen Sie dem Kombinationsfeld in der Tabelle „Adressen” weitere Erkennungsmerkmale (z. B. Vorname) hinzu.
Ihre Arbeitsmappe ist betriebsbereit!
Weitere Tipps und Anleitungen zum Arbeiten mit Excel erhalten Sie in unseren Excel-Seminaren. Wir freuen uns auf Sie!
Hinterlasse jetzt einen Kommentar