In den einschlägigen Foren wird mindestens 10 x am Tag eine Frage gestellt, für die die Verwendung der Tabellenblattfunktion =SVERWEIS() die optimale Lösung darstellt. Da sich gerade AnfängerInnen und weniger Geübte mit der richtigen Syntax dieser Funktion schwertun, möchte ich an dieser Stelle einige Erläuterungen anbieten.
Gegeben sei folgende Tabelle:
Ziel unserer Übung soll sein, dass nach Eingabe einer Pers.Nr. in Zelle C3 in den Zellen D3:F3 die entsprechenden Daten (Name, Vorname und Beruf) zurückgegeben werden.
Dazu schauen wir uns einmal den Aufbau der Funktion an:
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
Das 1. Argument - Suchkriterium - erklärt sich fast von selbst: Das ist der Wert (Pers.Nr.), der in C3 eingetragen wird. Nach diesem Kriterium soll ja innerhalb der Matrix gesucht werden.
Somit lautet der erste Teil der Funktion =SVERWEIS(C3;...)
Schon sind wir beim 2. Argument, der Matrix. Darunter versteht man den Bereich des Tabellenblatts, in dem sich unsere auszuwertenden Daten befinden. In unserem Fall befinden sich die Daten in den Zellen C6:F8, also lautet der zweite Teil =SVERWEIS(C3;C6:F8;...)
Das 3. Argument, der Spaltenindex, verlangt nach einer ausführlicheren Erklärung: Wir haben gelernt, dass sich unsere auszuwertende Matrix im Bereich C6:F8 befindet. Der Spaltenindex gibt nun an, aus der wievielten Spalte innerhalb dieser Matrix Werte ausgelesen werden sollen. Wenn wir den Namen auslesen wollen, müssen wir also schauen, in der wievielten Spalte unserer Matrix (und nicht in der wievielten Spalte des Tabellenblatts) die Namen zu finden sind. Wenn unsere Matrix in Spalte C beginnt (C6), brauchen wir den Wert aus der 2. Spalte der Matrix, nämlich Spalte D (und nicht - wie man eventuell annehmen könnte - aus der 4. Spalte des Tabellenblatts.
Das dritte Argument =SVERWEIS(C3;C6:F8;2;..)
Das 4. Argument ist optional, das heißt, es muss nicht unbedingt angegeben werden und ist ebenfalls "etwas schwerere Kost": Hiermit wird gesteuert, ob SVERWEIS eine genaue oder ungefähre Entsprechung suchen soll. Wird das Argument weggelassen oder auf WAHR (1) gesetzt, wird eine ungefähre Entsprechung zurückgegeben, wenn das Suchkriterium (1. Argument) nicht haargenau gefunden wird. Wird das 4. Argument auf FALSCH(0) gesetzt, werden nur Werte zurückgegegen, die genau dem Suchkriterium entsprechen. Anderenfalls ist der Rückgabewert #NV. Das schauen wir uns etwas später genauer an.
Zunächst aber tragen wir in Zelle D3 unsere bis jetzt entwickelte Funktion mit dem 4. Argument = 0 ein:
Nun wollen wir in Zelle E3 den Vornamen auslesen. Die ersten zwei sowie das 4.Argument bleiben also gleich: Wir suchen immer noch nach dem Suchbegriff in C3 und in der Matrix C6:F8. Aber nun brauchen wir den Wert aus der 3. Spalte der Matrix, also lautet unsere Funktion
=SVERWEIS(C3;C6:F8;3;0)
Zum Schluss holen wir uns noch den Wert aus der 4. Spalte der Matrix und unser Projekt ist fertig:
Wenn wir nun das Suchkriterium in C3 ändern, erhalten wir die entsprechenden Rückgabewerte:
Beschäftigen wir uns noch einmal mit dem 4. Argument: 0 oder FALSCH sorgt dafür, dass das genaue Suchkriterium in der Matrix vorkommen muss, damit Werte zurückgegeben werden. Wird dieses Argument hingegen weggelassen oder auf 1 oder WAHR gesetzt, werden die Werte zurückgegeben, die dem grössten Wert entsprechen, der kleiner als das Suchkriterium ist. Voraussetzung ist allerdings in diesem Fall, dass die Liste der Suchkriterien aufsteigend sortiert ist.
Hört sich kompliziert an, ist aber relativ einfach: Nehmen wir an, wir geben die Pers.Nr. 25 ein. Der größte Wert (Suchkriterium), der kleiner als 25 ist, ist die Pers.Nr. 12. Also werden die Werte der Pers.Nr. 12 zurückgegeben (im folgender Darstellung wurde das 4. Argument nur in Zelle D3 weggelassen, E3 und F3 liefern #NV, da das 4. Argument ja auf 0 steht):
Nun kann man sich natürlich die Frage stellen, warum es das 4. Argument überhaupt gibt, wenn wir doch die Werte der exakt eingegebenen Pers.Nr. benötigen. Nun, je nach Tabelle kann es durchaus sinnvoll sein, den grössten Wert kleiner Suchkriterium auszulesen.
Stellen wir uns vor, dass wir für einen Handelsvertreter die Provision nach erzieltem Umsatz berechnen müssen. Unsere Provisionstabelle könnte so aussehen:
Nehmen wir an, der Umsatz unseres Vertreters beträgt 32.785,26 EUR. Wenn wir nun mit dem 4. Argument gleich 0 oder FALSCH arbeiten wollten, müssten wir eine Umsatztabelle erstellen, die in Spalte B jeden Wert zwischen 0,01 EUR und 50.000,00 EUR enthält, damit der gesuchte Umsatz von 32.785.26 EUR exakt gefunden wird. Wir hätten es also mit einer Tabelle mit 5 Mio. Zeilen zu tun !!!
Hier hilft uns das 4. Argument, entweder weggelassen oder auf 1 oder WAHR gesetzt:
Wir stellen fest: Das 4. Argument wurde weggelassen, es wurde der Wert (7) zurückgegeben, der dem grössten (nämlich 30.000) Wert kleiner Suchkriterium (32.785,26) entspricht.
Das Ganze noch einmal mit geändertem Umsatz und explizit angegebenem 4. Argument:
Ihr seht, dass der Angabe des 4. Arguments eine nicht zu unterschätzende Bedeutung zukommt, obwohl SVERWEIS keine Fehlermeldung bringt, wenn es nicht angegeben ist. Unter Umständen werden jedoch logisch falsche Werte zurückgegeben. Denkt noch einmal zurück an unsere in der Suchmatrix nicht vorhandene Pers.Nr. 25, die den Wert der Pers.Nr. 12 zurückgegeben hat.
Zum Schluss dieser kleinen Exkursion fehlt noch der Hinweis, dass die Funktion =SVERWEIS() eine Matrix immer nur von links nach rechts durchlaufen kann. Daraus folgt, dass der Spaltenindex immer eine positive Zahl sein muss.
Gegeben sei folgende Tabelle:
| C | D | E | F | |
| 2 | Eingabe Pers.-Nr. | Name | Vorname | Beruf |
| 3 | ||||
| 4 | ||||
| 5 | Pers.Nr. | Name | Vorname | Beruf |
| 6 | 12 | Meier | Karl | Buchhalter |
| 7 | 27 | Müller | Ute | Sekretärin |
| 8 | 36 | Schulz | Werner | Verkäufer |
Ziel unserer Übung soll sein, dass nach Eingabe einer Pers.Nr. in Zelle C3 in den Zellen D3:F3 die entsprechenden Daten (Name, Vorname und Beruf) zurückgegeben werden.
Dazu schauen wir uns einmal den Aufbau der Funktion an:
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
Das 1. Argument - Suchkriterium - erklärt sich fast von selbst: Das ist der Wert (Pers.Nr.), der in C3 eingetragen wird. Nach diesem Kriterium soll ja innerhalb der Matrix gesucht werden.
Somit lautet der erste Teil der Funktion =SVERWEIS(C3;...)
Schon sind wir beim 2. Argument, der Matrix. Darunter versteht man den Bereich des Tabellenblatts, in dem sich unsere auszuwertenden Daten befinden. In unserem Fall befinden sich die Daten in den Zellen C6:F8, also lautet der zweite Teil =SVERWEIS(C3;C6:F8;...)
Das 3. Argument, der Spaltenindex, verlangt nach einer ausführlicheren Erklärung: Wir haben gelernt, dass sich unsere auszuwertende Matrix im Bereich C6:F8 befindet. Der Spaltenindex gibt nun an, aus der wievielten Spalte innerhalb dieser Matrix Werte ausgelesen werden sollen. Wenn wir den Namen auslesen wollen, müssen wir also schauen, in der wievielten Spalte unserer Matrix (und nicht in der wievielten Spalte des Tabellenblatts) die Namen zu finden sind. Wenn unsere Matrix in Spalte C beginnt (C6), brauchen wir den Wert aus der 2. Spalte der Matrix, nämlich Spalte D (und nicht - wie man eventuell annehmen könnte - aus der 4. Spalte des Tabellenblatts.
Das dritte Argument =SVERWEIS(C3;C6:F8;2;..)
Das 4. Argument ist optional, das heißt, es muss nicht unbedingt angegeben werden und ist ebenfalls "etwas schwerere Kost": Hiermit wird gesteuert, ob SVERWEIS eine genaue oder ungefähre Entsprechung suchen soll. Wird das Argument weggelassen oder auf WAHR (1) gesetzt, wird eine ungefähre Entsprechung zurückgegeben, wenn das Suchkriterium (1. Argument) nicht haargenau gefunden wird. Wird das 4. Argument auf FALSCH(0) gesetzt, werden nur Werte zurückgegegen, die genau dem Suchkriterium entsprechen. Anderenfalls ist der Rückgabewert #NV. Das schauen wir uns etwas später genauer an.
Zunächst aber tragen wir in Zelle D3 unsere bis jetzt entwickelte Funktion mit dem 4. Argument = 0 ein:
| C | D | E | F | |
| 2 | Eingabe Pers.-Nr. | Name | Vorname | Beruf |
| 3 | 27 | Müller | ||
| 4 | ||||
| 5 | Pers.Nr. | Name | Vorname | Beruf |
| 6 | 12 | Meier | Karl | Buchhalter |
| 7 | 27 | Müller | Ute | Sekretärin |
| 8 | 36 | Schulz | Werner | Verkäufer |
| Zelle | Formel |
| D3 | =SVERWEIS(C3;C6:F8;2;0) |
Nun wollen wir in Zelle E3 den Vornamen auslesen. Die ersten zwei sowie das 4.Argument bleiben also gleich: Wir suchen immer noch nach dem Suchbegriff in C3 und in der Matrix C6:F8. Aber nun brauchen wir den Wert aus der 3. Spalte der Matrix, also lautet unsere Funktion
=SVERWEIS(C3;C6:F8;3;0)
| C | D | E | F | |
| 2 | Eingabe Pers.-Nr. | Name | Vorname | Beruf |
| 3 | 27 | Müller | Ute | |
| 4 | ||||
| 5 | Pers.Nr. | Name | Vorname | Beruf |
| 6 | 12 | Meier | Karl | Buchhalter |
| 7 | 27 | Müller | Ute | Sekretärin |
| 8 | 36 | Schulz | Werner | Verkäufer |
| Zelle | Formel |
| D3 | =SVERWEIS(C3;C6:F8;2;0) |
| E3 | =SVERWEIS(C3;C6:F8;3;0) |
Zum Schluss holen wir uns noch den Wert aus der 4. Spalte der Matrix und unser Projekt ist fertig:
| C | D | E | F | |
| 2 | Eingabe Pers.-Nr. | Name | Vorname | Beruf |
| 3 | 27 | Müller | Ute | Sekretärin |
| 4 | ||||
| 5 | Pers.Nr. | Name | Vorname | Beruf |
| 6 | 12 | Meier | Karl | Buchhalter |
| 7 | 27 | Müller | Ute | Sekretärin |
| 8 | 36 | Schulz | Werner | Verkäufer |
| Zelle | Formel |
| D3 | =SVERWEIS(C3;C6:F8;2;0) |
| E3 | =SVERWEIS(C3;C6:F8;3;0) |
| F3 | =SVERWEIS(C3;C6:F8;4;0) |
Wenn wir nun das Suchkriterium in C3 ändern, erhalten wir die entsprechenden Rückgabewerte:
| C | D | E | F | |
| 2 | Eingabe Pers.-Nr. | Name | Vorname | Beruf |
| 3 | 36 | Schulz | Werner | Verkäufer |
| 4 | ||||
| 5 | Pers.Nr. | Name | Vorname | Beruf |
| 6 | 12 | Meier | Karl | Buchhalter |
| 7 | 27 | Müller | Ute | Sekretärin |
| 8 | 36 | Schulz | Werner | Verkäufer |
| Zelle | Formel |
| D3 | =SVERWEIS(C3;C6:F8;2;0) |
| E3 | =SVERWEIS(C3;C6:F8;3;0) |
| F3 | =SVERWEIS(C3;C6:F8;4;0) |
Beschäftigen wir uns noch einmal mit dem 4. Argument: 0 oder FALSCH sorgt dafür, dass das genaue Suchkriterium in der Matrix vorkommen muss, damit Werte zurückgegeben werden. Wird dieses Argument hingegen weggelassen oder auf 1 oder WAHR gesetzt, werden die Werte zurückgegeben, die dem grössten Wert entsprechen, der kleiner als das Suchkriterium ist. Voraussetzung ist allerdings in diesem Fall, dass die Liste der Suchkriterien aufsteigend sortiert ist.
Hört sich kompliziert an, ist aber relativ einfach: Nehmen wir an, wir geben die Pers.Nr. 25 ein. Der größte Wert (Suchkriterium), der kleiner als 25 ist, ist die Pers.Nr. 12. Also werden die Werte der Pers.Nr. 12 zurückgegeben (im folgender Darstellung wurde das 4. Argument nur in Zelle D3 weggelassen, E3 und F3 liefern #NV, da das 4. Argument ja auf 0 steht):
| C | D | E | F | |
| 2 | Eingabe Pers.-Nr. | Name | Vorname | Beruf |
| 3 | 25 | Meier | #NV | #NV |
| 4 | ||||
| 5 | Pers.Nr. | Name | Vorname | Beruf |
| 6 | 12 | Meier | Karl | Buchhalter |
| 7 | 27 | Müller | Ute | Sekretärin |
| 8 | 36 | Schulz | Werner | Verkäufer |
| Zelle | Formel |
| D3 | =SVERWEIS(C3;C6:F8;2) |
| E3 | =SVERWEIS(C3;C6:F8;3;0) |
| F3 | =SVERWEIS(C3;C6:F8;4;0) |
Nun kann man sich natürlich die Frage stellen, warum es das 4. Argument überhaupt gibt, wenn wir doch die Werte der exakt eingegebenen Pers.Nr. benötigen. Nun, je nach Tabelle kann es durchaus sinnvoll sein, den grössten Wert kleiner Suchkriterium auszulesen.
Stellen wir uns vor, dass wir für einen Handelsvertreter die Provision nach erzieltem Umsatz berechnen müssen. Unsere Provisionstabelle könnte so aussehen:
| B | C | |
| 2 | Erzielter Umsatz | Provision |
| 3 | ||
| 4 | ||
| 5 | Umsatz bis … EUR | Provision in & |
| 6 | 10.000,00 | 3 |
| 7 | 20.000,00 | 5 |
| 8 | 30.000,00 | 7 |
| 9 | 40.000,00 | 9 |
| 10 | 50.000,00 | 12 |
Nehmen wir an, der Umsatz unseres Vertreters beträgt 32.785,26 EUR. Wenn wir nun mit dem 4. Argument gleich 0 oder FALSCH arbeiten wollten, müssten wir eine Umsatztabelle erstellen, die in Spalte B jeden Wert zwischen 0,01 EUR und 50.000,00 EUR enthält, damit der gesuchte Umsatz von 32.785.26 EUR exakt gefunden wird. Wir hätten es also mit einer Tabelle mit 5 Mio. Zeilen zu tun !!!
Hier hilft uns das 4. Argument, entweder weggelassen oder auf 1 oder WAHR gesetzt:
| B | C | |
| 2 | Erzielter Umsatz | Provision |
| 3 | 32.785,26 | 7 |
| 4 | ||
| 5 | Umsatz bis … EUR | Provision in & |
| 6 | 10.000,00 | 3 |
| 7 | 20.000,00 | 5 |
| 8 | 30.000,00 | 7 |
| 9 | 40.000,00 | 9 |
| 10 | 50.000,00 | 12 |
| Zelle | Formel |
| C3 | =SVERWEIS(B3;B6:C10;2) |
Wir stellen fest: Das 4. Argument wurde weggelassen, es wurde der Wert (7) zurückgegeben, der dem grössten (nämlich 30.000) Wert kleiner Suchkriterium (32.785,26) entspricht.
Das Ganze noch einmal mit geändertem Umsatz und explizit angegebenem 4. Argument:
| B | C | |
| 2 | Erzielter Umsatz | Provision |
| 3 | 26.193,22 | 5 |
| 4 | ||
| 5 | Umsatz bis … EUR | Provision in & |
| 6 | 10.000,00 | 3 |
| 7 | 20.000,00 | 5 |
| 8 | 30.000,00 | 7 |
| 9 | 40.000,00 | 9 |
| 10 | 50.000,00 | 12 |
| Zelle | Formel |
| C3 | =SVERWEIS(B3;B6:C10;2;1) |
Ihr seht, dass der Angabe des 4. Arguments eine nicht zu unterschätzende Bedeutung zukommt, obwohl SVERWEIS keine Fehlermeldung bringt, wenn es nicht angegeben ist. Unter Umständen werden jedoch logisch falsche Werte zurückgegeben. Denkt noch einmal zurück an unsere in der Suchmatrix nicht vorhandene Pers.Nr. 25, die den Wert der Pers.Nr. 12 zurückgegeben hat.
Zum Schluss dieser kleinen Exkursion fehlt noch der Hinweis, dass die Funktion =SVERWEIS() eine Matrix immer nur von links nach rechts durchlaufen kann. Daraus folgt, dass der Spaltenindex immer eine positive Zahl sein muss.
Überlegen macht überlegen
Gruss aus dem schönen Hunsrück
_______ Klaus-Martin _______
Gruss aus dem schönen Hunsrück
_______ Klaus-Martin _______