Excel – SVERWEIS Schritt für Schritt

An der Funktion SVERWEIS scheiden sich die Geister – viele Excel-Anwender geben schnell entnervt auf. Ich werde Ihnen in vier Folgen die Grundlagen zeigen, bis Freitag können Sie also den SVERWEIS grundlegend anwenden. In der kommenden Woche geht es dann ans “Eingemachte” – SVERWEIS in einer etwas höheren Klasse. Ganz zum Schluss gibt es einen Link mit allen Beispielen zum Download. Sie sollen ja selber lernen und nicht spicken!

Packen wir den Stier bei den Hörnern!

Zuerst: Welche Aufgaben lösen Sie mit einem SVERWEIS? Im Prinzip eine WENN-DANN-Abfrage mit vielen Bedingungen. Beispiele:

  • Sie möchten in einer Rechnung die Artikel-Nummer eingeben und Artikelname
    und Preis werden ergänzt
  • Sie brauchen zu einer Personalnummer den Namen, die Abteilung oder das Gehalt des Mitarbeiters
  • Zu einem Umsatz soll die Provision ermittelt werden
  • Zu einer Punktezahl soll die richtige Note herausgefunden werden

Und so beginnen wir auch: ein Rechnungsformular (rot umrandet, A1 bis E6) und eine Artikelliste (blau umrandet, G1 bis I9). Die Artikelnummern sind aufsteigend sortiert – aber in unserem Beispiel ist das nicht zwingend notwendig.

Der SVERWEIS wird dazu verwendet, zu der Artikel-Nummer, die im Rechnungsformular eingetragen wird, aus der Artikelliste die dazu passende Bezeichnung und den Preis zu liefern.

05.10.2013_ 0000

highlighter_marker_text_11965
Die blau umrandete Artikelliste (das ist die Referenztabelle oder Matrix) steht momentan auf dem gleichen Blatt, damit wir eine bessere Übersicht haben. In der Praxis werden Sie sie aber auf einem anderen Blatt platzieren. Das zeige ich in morgen.

Im Moment formatieren wir die Artikelliste nicht als Tabelle – auch das greife ich in morgen auf.

Ziel ist also, im Rechnungsformular die Menge einzutragen, die Artikelnummer anzugeben und Bezeichnung sowie Preis automatisch zu erhalten. Dazu muss Excel anhand der Artikelnummer aus der Rechnung in der Artikelliste (der Referenztabelle oder Matrix) die passende Bezeichnung und den Preis finden.

Die Formel sieht so aus:

=SVERWEIS(Suchkriterium;Referenztabelle oder Matrix;Spaltenindex;Übereinstimmung)

05.10.2013_ 0001

Dabei ist wichtig:

  • Excel durchsucht immer die erste Spalte der Referenztabelle (Matrix).
  • Excel sucht von oben nach unten, bis der Wert gefunden wird und gibt dann den Wert der angegeben (zweiten, dritten oder einer anderen) Spalte aus.
  • Die gesuchte Spalte wird als Spaltenindex und damit als Ziffer (1, 2 oder 3) angegeben – die Spaltenbuchstaben (G, H oder I) werden nicht verwendet.
  • Es muss angegeben, ob eine exakte Übereinstimmung gesucht werden (FALSCH) soll oder ein Wert “bis zu xxx” (WAHR) verwendet wird – das ist ein Argument für die Übereinstimmung

Zusammengefasst:

stick_figure_drawing_four_check_marks_nonlooping_300_clr_6582_thumb[1]

Suchkriterium in B2

Referenztabelle im Bereich G2:I9

Spaltenzahl aus der Referenztabelle 2

Argument für Übereinstimmung FALSCH

Die Formel sieht für die Artikelbezeichnung so aus:
=SVERWEIS(B2;G2:I9;2;FALSCH)

So lesen Sie die Formel im Klartext:
Suche den Wert aus B2 in der Referenztabelle G2:I9 und gib den Wert der zweiten Spalte aus.

05.10.2013_ 0002

Es gibt zwei Argumente für die Übereinstimmung:

  • FALSCH
    • Eine genaue Übereinstimmung wird gesucht.
    • Ist der Wert nicht vorhanden, wird die Fehlermeldung #NV angezeigt.
    • Einsatzgebiet: Artikelnummern, Personalnummern, Namen, ID-Nummern
  • WAHR
    • Eine ungefähre Übereinstimmung wird gesucht; der Wert “bis zu …” wird verwendet.
    • Ist der Wert nicht vorhanden, wird der nächst niedrige verwendet.
    • Die Referenztabelle muss unbedingt sortiert sein!
    • Einsatzgebiet: Provisionssätze, Steuersätze, Gebühren
  • Nicht angegeben
    • Wenn Sie das Argument für die Übereinstimmung nicht angeben, setzt Excel WAHR voraus.

Damit die Formel in unserer Rechnungstabelle bequem nach unten ausgefüllt werden kann, müssen Sie den SVERWEIS noch anpassen. Der Referenzbereich muss absolut gesetzt sein – sonst können Sie die Formel nicht nach unten ausfüllen!

Außerdem sollte das Suchkriterium für die Spalte halb-absolut gesetzt sein:

=SVERWEIS($B2;$G$2:$I$9;2;FALSCH)

05.10.2013_ 0003

Für die Spalte C ist die Formel bereits nach unten ausgefüllt.

Der nächste SVERWEIS soll den Preis ermitteln. Der Suchbereich ist der gleiche wie bei der Artikelbezeichnung (B2, die Artikel-Nr.), die Referenztabelle ist die gleiche (G2 bis I9) – nur die Spaltenzahl ist anders. Excel muss für die Artikel-Nummer den Wert aus der dritten Spalte heraussuchen.

Für die Spalte D der Rechnung lautet der SVERWEIS also:
=SVERWEIS($B2;$G$2:$I$9;3;FALSCH)

Kopieren Sie die Formel von C2 nach D2 und passen Sie die Spaltenzahl von 2 auf 3 an.

05.10.2013_ 0006

Auch diese Formel nach unten ausfüllen.
So sieht es dann komplett aus:

05.10.2013_ 0007

Immer gleich ist die Referenztabelle oder Matrix (G2 bis I9) – sie muss also unbedingt absolut gesetzt werden, damit  Sie die Formeln nach unten ausfüllen können. Das Suchkriterium ist relativ nach unten (B2, B3, B4…) – aber immer in  Spalte B. Darum ist dieser Bezug “halbabsolut” für die Spaltef.

Hinterlasse jetzt einen Kommentar

Kommentar hinterlassen

E-Mail Adresse wird nicht veröffentlicht.


*