Excel Tipps

Wie ich mit dem SVERWEIS eine Menge Zeit sparen kann

Wir zeigen ganz praktisch, wie man den SVERWEIS anwendet

Microsoft Excel ist ein sehr mächtiges Werkzeug und erlaubt es dem geübten Anwender sehr viele Probleme und Berechnungen schnell zu lösen. Dafür muss man aber wissen wann man diese anwendet. 

Der SVERWEIS (oder VLOOKUP im Englischen) ist eine der wahrscheinlich an der häufigsten verwendeten Formeln in Excel. Doch wofür braucht man diese überhaupt? Welche Probleme kann man damit lösen? Wir möchten Euch mit diesem Beitrag eine kurze Übersicht geben, wann man den SVERWEIS gut anwenden kann und wie das genau funktioniert.

Oftmals ist die Ausgangslage, dass man eine Liste mit Daten hat und prüfen muss, ob diese Daten in einer anderen Liste a) vorhanden sind oder b) welcher Wert mit diesen Daten assoziiert ist. Machen wir es doch ganz praktisch: weiter unten seht ihr eine Liste von Namen. Sagen wir man das sind Kunden. Jetzt stellt euch vor ihr bekommt diese von eurem Vorgesetzten oder Kollegen zugeschickt mit dem Auftrag herauszufinden, welches Produkt diese Kunden gekauft haben. Angehängt ist noch eine zweite Liste mit allen Kunden und den gekauften Produkten. Die Anfrage bezieht sich auf 50 Kunden, die gesamte Liste beinhaltet 2000 Kunden, inkl. Produkten.

Welche Möglichkeiten habt ihr jetzt? Klar, ihr könntet jeden Kunden einzeln suchen und das Produkt per Hand (oder Tastatur) reinschreiben. Aber das dauert. Und der Vorgesetzte möchte in 15 Minuten die fertige Liste haben. Also nutzen wir doch den SVERWEIS.

WICHTIG: standardmässig funktioniert das nur, wenn jeder Kundenname ein Unikat ist und jeder Kunde nur ein (!) Produkt gekauft hat. Wenn zwei Kunden gleich heissen, dann hilft der SVERWEIS nicht weiter, da nur die Daten des ersten Kunden in der Liste (=der oder die zuerst aufgeführt ist) ausgegeben werden. Deswegen würde ich auch immer einen im Englischen sogenannten „Unique Identifier“ (grob übersetzt eine „einmalige Identifizierung“) statt dem Namen benutzen, z.B. die Kundennummer, diese darf es nur einmal pro Kunde geben.

Hat ein Kunde mehrere Produkte gekauft, kommt also in der Liste mehrmals vor, dann gibt der SVERWEIS euch nur den ersten Eintrag retour und ist nicht wirklich brauchbar. Alternativen stellen wir in einem weiteren Blogeintrag vor.

Praktische Anwendung

Also, genug der Theorie, hier kommt die Liste der Kunden, inkl. Kundennummer:

excel liste

Wir gehen jetzt davon aus, dass der Name in Spalte „A“ steht und die Überschrift in Zeile 1. Der Kunde Müller steht dementsprechend in Zeile 2, Kunde Klose in Zeile 4 usw.

Es gibt jetzt zwei praktische Möglichkeiten den SVERWEIS anzuwenden:

  1. Prüfen, ob bestimmte Kunden in der Liste vorhanden sind
  2. Evaluieren, welcher Kunde welches Produkt gekauft hat

Wenn wir nur wissen möchte, ob zum Beispiel eine Auswahl von 50 Kunden in einer langen Liste von 2000 Kunden vorhanden sind, kann ich den SVERWEIS folgendermassen verwenden: wir demonstrieren das mit unserer kurzen Liste von fünf Kunden und möchten prüfen, ob zwei davon in der Liste sind. Das Vorgehen ist dasselbe, egal ob es zwei oder 50 sind.

excel liste

Mit dem SVERWEIS können wir das einfach herausfinden:

=SVERWEIS(B9;$B$2:$C$6;1;FALSCH)

Im Englischen lautet die Formel: =VLOOKUP(B9,$B$2:$C$6,1,FALSE)

Bitte beachtet, dass das Semikolon (;) und das Komma (,) austauschbar sind, je nachdem in welcher Sprache ihr Excel verwendet. in Englisch ist es meist ein Komma, in internationalen Versionen (und Deutsch) ein Semikolon. Das müsst ihr ausprobieren (ich habe auch schon Unterschiede in den Versionen für Windows und MAC gesehen).

Nehmen wir die Formel einmal auseinander. Bitte beachtet auch, dass diese in diesem Fall in die Zellen C9 und C10 eingetragen wird um die Werte, welche zu den Kundennummer gehören zu finden. Heisst das für jeder Zelle muss die Formel neu eingetragen werden? Nein, diese wird einmal eingetragen und dann die die anderen Zellen übertragen. Konkret: in Zelle C9 wird die Formel eingetragen. Dann wird sie an der rechten unteren Ecke der Zelle (kleines grünes Quadrat) in die nächste Zelle gezogen (draufklicken, halten und mit der Maus runterziehen):

Excel kopieren

Aber zurück zur Formel, was bedeutet diese? B9 ist in unserem Fall der Wert, den wir suchen (die Kundennummer). B2:C6 ist die Liste, die wir durchsuchen (wir fangen bei „B“ an, weil der Name in Spalte „A“ nicht relevant und der gesuchte Wert die Kundennummer in Spalte „B“ ist. Das ist sehr wichtig!) und zu schauen, ob die gesuchte Kundennummer aus B9 dort drin ist. Wozu sind die $ Zeichen vor der Zahl und der Nummer? Ganz einfach: wenn ihr die Formel kopiert (mit dem ziehen des kleinen grünen Quadrats), dann passt Excel automatisch die Formel an. Wenn ihr diese also eine Zeile nach unten zieht dann macht Excel aus B2:C6 automatisch B3:C7 (würdet ihr nach rechts ziehen statt nach unten würde daraus C2:D6). Das wollen wir vermeiden, denn die Liste, die wir durchsuchen ist statisch, also immer in B2:C6. Die $-Zeichen sagen Excel, dass die Buchstaben und Zahlen nicht verändert werden sollen beim Ziehen.

So, nun folgt die „1“. Dieser Wert sagt Excel, gibt mir den ersten Wert zurück, wenn Du die Kundennummer aus B9 gefunden hast. Das ist die Kundennummer selber. Wenn wir hier „2“ eingeben, dann wird der zweite Wert ausgegeben, das wäre dann Möglichkeit 2 von oben, nämlich das gekaufte Produkt. Die „1“ ist aber praktisch, wenn ich herausfinden möchte, ob die Kundennummer in einer langen Liste vorhanden ist (vor allem wenn ich viele Nummer auf einmal prüfen will). Wir können auch sehen, dass die Kundennummer “48215” den Wert #N/A zurückgibt (im Deutschen wäre das #N/V, ich benutze die Englische Version) weil die Kundennummer auf der Liste drüber nicht enthalten ist.

So, der letzte Wert „Falsch“ („false“) heisst noch, dass ich eine exakte Übereinstimmung brauche. Wenn wir hier „Wahr“ (oder „True“) eintragen, dann wird auch ein ungefährer Wert akzeptiert. Das ist hilfreich, wenn wir den genauen Wert nicht kennen, oder vermuten, dass es kleinere Fehler in den Einträgen gibt.

Die Formel um das Produkt zu bekommen lautet:

=SVERWEIS(B9;$B$2:$C$6;2;FALSCH) –> beachtet, dass wir hier nur die “1” durch eine “2” ersetzt haben

Im Englischen lautet die Formel: =VLOOKUP(B9,$B$2:$C$6,2,FALSE)

So bekommen wir dann die zugehörigen Produkte, weil die in Spalte 2 (in diesem Fall haben beide Kunden Produkt D4 gekauft):

excel sverweis

Warum Spalte 2 könnt ihr jetzt fragen. Die Liste hat doch drei Spalten, nämlich A, B und C. In unserer Formel haben wir aber nur die Spalten B und C ausgewählt (B2:C6), weil Spalte A für die Suche nicht relevant ist (wir suchen ja ein Produkt das zu einer Kundennummer gehört, der Name in Spalte A interessiert uns nicht). Spalte 1 ist in dem Fall B und Spalte 2 ist C.

Wir hoffen wir konnten damit einige Unklarheiten bezüglich dem SVERWEIS klären und haben genug Informationen gegeben, damit ihr die Formel anwenden könnt. Wenn es noch Fragen oder Unklarheiten gibt, lasst es uns in den Kommentaren wissen.

Leave a Comment

Ihre E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Scroll to Top