Sverweis Excel: Mit Sverweis in Excel Werte finden
Durch Verweisfunktionen lassen sich Ergebnisse aus anderen Zellen bzw. Tabellenbereichen heraussuchen. Die am häufigsten benutzte Verweisfunktion ist dabei die Sverweis-Funktion. Sverweis bedeutet senkrechter Verweis. Es gibt noch ein Wverweis für waagerechten Verweis.
Excel SVERWEIS Funktion
Mit der SVERWEIS-Funktion wird aus einer Tabelle ein bestimmter Wert ermittelt. Besonders empfiehlt sich der Einsatz der Verweisfunktion z.B. bei Preis- oder Mengenstaffel. Nachträgliche Preisänderungen müssen dann nur einmal vorgenommen werden. Aufbau der Formel:
= SVERWEIS ( Suchkriterium ; Matrix ; Spaltenindex )
- Suchkriterium: ist der Wert, nach dem die erste Spalte durchsucht werden soll
- Matrix: ist der Tabellenbereich, der durchsucht wird
- Spaltenindex: die Angabe der Spaltennummer in der Matrix, aus der das Ergebnis entnommen werden soll
Beispiel für Excel Sverweis Funktion: In diesem Beispiel wird Ihnen gezeigt, wie SVERWEIS in Excel angewendet werden kann.
- Excel Sverweis Beispiel
Die Umsätze im Quartal und die Beurteilungstabelle habe ich zur besseren Erklärung auf einem Blatt untergebracht. Der Bereich A3 bis E12 ist durch die bisherige Bearbeitung bekannt, neu ist die Funktion in Spalte F. In F4 steht die SVERWEIS-Funktion:
= SVERWEIS ( E4 ; Werte ; 2 )
Der Name „Werte“ steht dabei für die Matrix A18 bis B25. Das erspart die Angabe der Matrix als $A$18 : $B$25. Die Werte in der ersten Spalte der Matrix müssen aufsteigend sortiert sein, hier wurde die Eingabe entsprechend vorgenommen. In E4 steht der Wert „29.800“. Dieser Wert steht nicht in der Matrix sondern nur die Werte 29.000 und 30.000. In dieser Funktion wird der nächst kleinere Wert in der Matrix genommen, hier also 29.000. Der dazu eingetragene Wert steht in der Spalte B, bei der Matrix die Nummer 2. Bei 29.000 steht „wenig“ und dieses wird in F4 eingetragen.
Diese Funktion könnte auch durch eine mehrfach verschachtelte WENN-Funktion gelöst werden, dieses ist aber umständlicher und bei einer Änderung der Werte etwas unübersichtlich. Wenn die Fehlermeldung #NV ausgegeben wird, liegt es daran, dass in der ersten Spalte die Werte nicht aufsteigend angeben wurden oder der Suchwert ist kleiner als der erste Wert der ersten Spalte der Matrix.
Die Fehlermeldung
Die bisherige SVERWEIS-Funktion sucht den gleichen oder den kleineren Wert in der angegebenen Matrix. Wenn nur der genaue Wert gesucht und gefunden werden soll, ist ein weiterer Parameter einzugeben:
= SVERWEIS ( Suchkriterium ; Matrix ; Spaltenindex ; Bereich_Verweis)
Als „Bereich_Verweis“ kann „Wahr“ oder „Falsch“ eingetragen werden. Dabei ist „Wahr“ Standard und braucht nicht angegeben werden. Bei der Eingabe „Falsch“ wird nach dem genauen Wert gesucht und ausgegeben. Dabei braucht die erste Spalte nicht aufsteigend Sortiert sein. Wenn zwei oder mehrere gleiche Werte gefunden werden, wird der erste Wert für die Bearbeitung verwendet.
Daten für die Sverweis-Übung zum Kopieren
Damit Sie die Tabelle für die Sverweis-Übung nachbilden können, anbei die Daten, die Sie über die Zwischenablage kopieren können. Sie können die Daten kopieren und in Ihre Tabelle einfügen.
|
|
|
|
Umsätze im Quartal |
|
|
|
|
|
|
|
| Januar | Februar | März |
Abteilung 1 | 10.000 | 8.900 | 10.900 |
Abteilung 2 | 11.000 | 9.800 | 16.000 |
Abteilung 3 | 10.500 | 9.700 | 9.870 |
Abteilung 4 | 9.800 | 9.000 | 9.800 |
Abteilung 5 | 12.000 | 1.000 | 5.000 |
Abteilung 6 | 20.000 | 9.000 | 9.000 |
Abteilung 7 | 5.000 | 7.000 | 8.000 |
Abteilung 8 | 8.500 | 1.500 | 2.000 |
Abteilung 9 | 19.000 | 15.500 | 15.000 |
|
|
|
|
Beurteilungstabelle |
|
|
|
|
|
|
|
0 | schließen |
|
|
15.000 | bedrohlich |
|
|
25.000 | zu wenig |
|
|
29.000 | wenig |
|
|
30.000 | gut |
|
|
35.000 | sehr gut |
|
|
38.000 | ausgezeichnet |
|
|
40.000 | hervorragend |
|
|