Excel Such- und Bezugsversionen I
Workshops > Excel
Beim Arbeiten mit Excel ergibt sich immer wieder die Situation, dass
Sie Daten in einer Tabelle abhängig von einem bestimmten Wert suchen
möchten. Hier kommen Funktionen aus der Kategorie Matrix zum Einsatz.Man könnte diese Funktionen ebenso als Nachschlage- und Verweisfunktionen bezeichnen, dienen Sie doch dem oben erwähnten Zweck, der Suche bzw. dem Nachschlagen nach Werten.
Je nach Zweck und Anwenderkönnen kommen in der Praxis verschiedene
Funktionen zum Einsatz. In diesem Workshop erfahren Sie, wofür sich die
Funktionen SVERWEIS (bzw. WVERWEIS) eignen und wie Sie gekonnt mit
diesen arbeiten.
Beispiel 1
Im ersten Beispiel gehen wir von einer Umsatztabelle aus, in der
Mitarbeiter mit deren erzieltem Umsatz aufgelistet sind. Anhand einer
Staffeltabelle soll jeder Mitarbeiter die entsprechende Provision
erhalten. Beide Tabellen sind hier nebeneinander auf einem Tabellenblatt
angeordnet. Die Tabellen müssen sich nicht zwingend auf einem Blatt
befinden, sie können sich auch auf verschiedene Tabellenblätter verteilt
sein. Mit entsprechenden Anweisungen ist sogar eine Speicherung in
einer separaten Arbeitsmappe möglich.
Da in diesem Beispiel eine Staffeltabelle (von Betrag x bis Betrag y
gilt Provisionssatz z) vorliegt, verwenden wir die Funktion SVERWEIS (bzw.
WVERWEIS).
Die Funktion SVERWEIS wird bei Matrizen verwendet, bei der die Daten
untereinander (senkrecht) angeordnet sind, WVERWEIS entsprechend bei
Daten in horizontaler (waagerechter) Anordnung.
Beide Funktion sind, von der Ausrichtung abgesehen, identisch im Aufbau
=SVERWEIS(Suchkriterium, Matrix, Spaltenindex, [Bereich_Verweis])
=WVERWEIS(Suchkriterium, Matrix, Zeilenindex, [Bereich_Verweis])
Was bedeuten diese Parameter?
- Suchkriterium
Mussparameter.
Gibt das Kriterium an, nach dem in der ersten Spalte/Zeile der Matrix gesucht weren soll. In unserer Tabelle z.B. der Umsatz in Höhe von 120.000€ aus Zelle B2. - Matrix
Mussparameter.
Der Zellbereich der die Daten enthält, in denen nach dem Suchkriterium gesucht werden soll. In obigem Beispiel ist das der Zellbereich F3:G13.
Ob Sie bei der Angabe der Matrix die Überschrift mit einbeziehen oder nicht bleibt Ihnen überlassen, solange Excel die Überschrift als solche erkennen kann. - Spaltenindex (SVERWEIS) / Zeilenindex (WVERWEIS)
Mussparameter.
Gibt an, aus welcher Spalte/Zeile das Ergebnis zurückgegeben werden soll. In obigem Beispiel wäre das Spalte 2.
Die Angabe muss numerisch erfolgen (also im Beispiel nicht G), wobei jede Matrix mit Spalte 1 beginnt und durchnummeriert wird, gleichgültig in welcher Excel-Spalte sie tatsächlich steht.
Dies hat den Vorteil, dass man mit diesem Index rechnen kann (dazu später mehr). - Bereich_Verweis
Optionaler Parameter.
Hier geben Sie an, ob Sie bei der Suche eine ungefähre oder eine genaue Übereinstimmung suchen. Die Angabe erfolgt als logischer Wert (WAHR bzw. FALSCH). Wird kein Parameter angegeben, setzt Word den Parameter WAHR unsichtbar ein.
Für eine ungefähre Übereinstimmung muss die Matrix nach der ersten Spalte aufsteigend sortiert sein!
Liegt eine Staffeltabelle wie im Beispiel vor, suchen Sie nach der ungefähren Methode, bei Nummern, Namen etc. generell nach genauer Übereinstimmung.
Nun aber zur Lösung unserer Aufgabe.
- Markieren Sie Zelle C2
- Rufen Sie den Funktionsassistenten und dort die Funktion SVERWEIS auf (alternativ fangen Sie an, die Funktion einzugeben. Excel ergänz Ihre Eingaben und hilft Ihnen dadurch).
- Suchkriterium: B2
- Matrix: $F$3:$G$13 (als absoluten Zellbezug definieren, damit die Formel kopiert werden kann)
- Spaltenindex: 2
- Bereich_Verweis: leer (oder WAHR)
- Return; Als Ergebnis erscheint 2%, da der Betrag in der Staffel in den Bereich von 100.000€ bis 149.999€ passt.
- Kopieren Sie die Formel in die Zellen unterhalb bis zum Ende.
Sie sehen, die Funktion SVERWEIS ist einfach zu handhaben (wenn man
verstanden hat, was die Parameter bedeuten) und hilft in vielen Fällen
schnell weiter.
Beachten Sie, dass bei ungefährer Übereinstimmung der Wert des
Suchkriteriums nicht kleiner sein darf als der kleinste Wert in der
Matrix. Andernfalls erhalten Sie den Fehlerwert #NV (Nicht Vorhanden).
Beispiel 2
Im zweiten Beispiel suchen wir in einer Artikelliste anhand einer
Artikelnummer nach dem jeweiligen Artikelnamen und dem Preis. Hier muss
selbstredend exakte Übereinstimmung vorliegen, da eine Suche wie in
Beispiel 1 wenig Sinn ergäbe.
In dieser Tabelle wird in Zelle F3 eine Artikelnummer (Suchkriterium)
eingegeben, diese Nuimmer in der ersten Spalte der Matrix im Bereich
A2:C13 gesucht. Das Ergebnis soll in Zelle F4 (Artikelbezeichnung) und
nach einer weiteren Suche in F5 (Einzelpreis) ausgegeben werden.
Damit die Formel schneller kopiert werden kann, sind alle Bezüge
absolut angegeben. so kann durch Ändern des Spaltenindex die Formel
leicht angepasst werden.
Der Parameter FALSCH (oder 0) muss manuell eingegebe werden. Unsere Formeln für die Bezeichnung und den Preis lauten demnach
=SVERWEIS($F$3;$A$2:$C$13;2;FALSCH)=SVERWEIS($F$3;$A$2:$C$13;3;FALSCH)
Dies funktioniert hervorragend - außer, wenn Sie eine Nummer
eingeben, die es in der Matrix nicht gibt. In diesem Fall erhalten Sie
den Fehlerwert #NV.
Wollen Sie Fehlerwerte bei falsch eingegeben Werten abfangen, so
bieten sich zwei Möglichkeiten an: Begrenzung der Eingabewerte mit der
Datenüberprüfung (Register Daten, Abschnitt Datentools, Befehl Datenüberprüfung) oder aber durch gezieltes Abfangen der Fehlermeldung. Letzteres werden wir hier anwenden.
Wird bei genauer Überprüfung kein passender Wert gefunden, erhalten
Sie den Fehlerwert #NV. Diesen Fehlerfall prüfen wir mit einer Funktion
und geben im Fehlerfall eine entsprechende Meldung aus. Die hierfür
verwendete Funktion heißt ISTNV bzw. ab Excel 2013 WENNFEHLER:
- Formel bis einschließlich Excel 2010
=WENN(ISTNV(SVERWEIS($F$3;$A$2:$C$13;2;FALSCH));"falsche Artikel-Nr.";SVERWEIS($F$3;$A$2:$C$13;2;FALSCH))
- Formel ab Excel 2013
=WENNFEHLER(SVERWEIS($F$3;$A$2:$C$13;2;FALSCH);"falsche Artikel-Nr.")
Syntax der Funktion: =WENNFEHLER(Wert;Wert_falls_Fehler)
Im Fehlerfall erhalten Sie jetzt eine Meldung, die den Grund für den Fehler angibt:
Beispiel 3
Im dritten Beispiel sehen wir die Funktion SVERWEIS mit berechneter Spaltenangabe.
Wieder ist eine Umsatztabelle mit einer Staffeltabelle im Spiel.
Diesmal geht es aber um Rabattsätze. Außerdem haben Sie Ihren Kunden
verschiedene Rabattstufen eingerichtet. Insgesamt gibt es drei Stufen.
Abhängig von der jeweiligen Stufe soll der Wert aus der betreffenden
Spalte der Staffeltabelle ermittelt werden. Wir müssen also nicht nur die Höhe des Umsatzes berücksichtigen, sondern auch die jeweilige Rabattstufe.
Wie erreichen wir eine dynamische Spaltenzuordnung? Nun, wir
verweisen auf Spalte C, in der die Rabattstufe des Kunden steht und
addieren zu dieser den Wert 1. Warum 1? Die Matrix beginnt bei Spalte 1,
danach kommen die weiteren Spalten. Hat ein Kunde Stufe 1, muss der Wert
aus der zweiten Spalte entnommen werden, hat er 2, dann wird der Wert
aus Spalte 3 geholt etc.
Unsere Formel in Zelle D2 lautet also:
=SVERWEIS(B2;$F$2:$I$7;1+C2)
Anwendungsgebiete für derartige Konstrukte sind Einkomensteuertabellen (Steuerklassen), Versicherungstabellen etc.
In diesem Workshop haben Sie gesehen, wie Sie die Funktion SVERWEIS
sinnvoll anwenden können. In einem weiteren Workshop lernen Sie die
Funktionen INDEX und VERGLEICH kennen, die gemeinsam flexibler sind als
SVERWEIS und einige kleine Schwächen dieser Funktion ausgleichen können.