Eine WENN-Funktion kann maximal 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 die Verweistabelle oder Matrix (hier blau umrandet) . Aus ihr werden die Preise errechnet.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 Matrix von Zelle D2 bis E13, 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 Verweis angeben. Wir haben das in der Formel oben ausgelassen. 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).
Laden Sie sich die Beispieltabelle herunter
21.04.2008
