Tipps und Tricks rund um Excel
| A | B | C | D | E | F | G | H | I/J | K |
| L | M | N | O | P/Q | R | S | T | U-W | X-Z |
SVERWEIS Grundlage
Eine WENN-Funktion kann maximal 64 (früher: sieben) Verschachtelungen haben - sie ist aber schon vorher viel zu unübersichtlich. In etlichen Fällen ist es darum praktischer, mit dem SVERWEIS zu arbeiten.
In einem privaten Schwimmbad wird nach Stunden abgerechnet. Da ein eifriger Schwimmer maximal 12 Stunden seine Bahnen ziehen kann, gibt es zwölf verschiedene Preismöglichkeiten. Eine WENN-DANN-Funktion kommt also nicht in Frage.
Die Preisstaffelung steht in einem eigenen Bereich der Tabelle - hier in den Spalten D und E. Die Preistabelle muss aufsteigend sortiert sein, es wird also mit 0 Stunden begonnen und mit 12 Stunden geendet. Dieser Bereich der Preisstaffelung ist dieVerweistabelle oder Matrix. Aus ihr werden die Preise errechnet. Ich habe eine formatierte Tabelle verwendet, die den Namen "Preise" bekommt. Die Verweistabelle hat zwei Spalten: in Spalte D stehen die möglichen Stundenzahlen, in Spalte E die dazugehörigen Preise. Ein Preis gilt immer "ab 2 Stunden", das bedeutet, dass der gleiche Preis von zwei bis drei Stunden gilt. |
|
Die Spaltenzahl der Verweistabelle spielt für die Funktion eine große Rolle. Sie müssen später angeben, aus der wievielten Spalte Sie die Informationen benötigen: aus der ersten (1) oder aus der zweiten (2). Der SVERWEIS arbeitet nicht mit den Spaltenbuchstaben D und E, sondern mit der Spaltennummer innerhalb der Verweistabelle. Wir werden die Preise benötigen - das ist die zweite Spalte (2) der Verweis-Tabelle.
In Zelle B1 soll die Stundenzahl des Schwimmers eingetragen werden, in B2 soll der Preis ausgegeben werden.
Die Funktion sieht so aus:
=SVERWEIS(Suchkriterium;Matrix;Spaltennr.;Bereich_Verweis)
=SVERWEIS(B1;D2:E13;2)
Sie lesen die Formel so:
Suche nach dem Suchkriterium aus der Zelle B1(das ist die Stundenzahl) in der Tabelle "Preise", bis dieser Wert gefunden wurde und gibt dann von der zweiten Spalte den Wert aus (das sind 3,50 €) |
|
Geben Sie eine Stundenzahl von 2 ein, wird Excel genau diesen Wert in der ersten Spalte finden und den passenden Betrag rechts daneben ausgeben.
Was ist aber mit der Stundenzahl 3,5? Excel geht davon aus, dass der nächst niedrigere Wert verwendet werden soll: der Schwimmer zahlt für 3 bis 4 Stunden das Gleiche. Ob er drei Stunden oder dreieinhalb Stunden schwimmt, ist egal. Darum ist es so wichtig, dass die Verweistabelle richtig sortiert ist. Excel verwendet als "nächst niedriger" einfach den Wert der Zelle darüber!
Wenn Sie möchten, dass Excel exakt nur die Werte findet, die auch in der ersten Spalte vorkommen, müssen Sie das Argument "Falsch" bei Bereich_Verweis angeben. Dazu finden Sie hier ein Beispiel.
Verweis kann WAHR oder leer sein oder FALSCH sein:
- WAHR oder kein Argument:
Excel sucht den nächst niedrigenderen Wert - FALSCH:
Excel sucht nur den exakt gleichen Wert und gibt bei allen anderen Werten einen Fehler aus. In unserem Beispiel würde als gefunden "2 Stunden", aber nicht "3,5 Stunden. Ein Beispiel dafür finden Sie hier.
19.09.2013