EXCEL-ANWENDUNGEN   SVERWEIS mit STAROFFICE-CALC    Beispiel mit EXCEL 2007: SVERWEIS + WENN

SUCHEN:        BEISPIEL 1    BEISPIEL 2    BEISPIEL 3    BEISPIEL mit NAMEN    


SUCHEN mit SVERWEIS

Im „kaufmännischen“ Alltag muss man häufig Werte aus den verschiedensten Tabellen entnehmen. Sei es den Zollsatz für eine bestimmte Ware, den Lohnsteuer- oder Sozialversicherungsbetrag für ein bestimmtes Gehalt, die anfallenden Kreditzinsen für eine bestimmte Darlehenshöhe, ...

In unserem Beispiel nehmen wir an, dass Herr Sauger, Chef der Firma Saft & Sauger, seinen Mitarbeitern am Jahresende einen Bonus von maximal einem halben Monatsgehalt ausbezahlt und zwar abhängig von der Anzahl der Fehltage. Je öfter man gefehlt hat, desto weniger Bonus erhält man, wer zehn oder mehr Tage gefehlt hat bekommt keinen Bonus.

Mit der Funktion SVERWEIS ist diese Problemstellung sehr einfach lösbar.

Zuerst wird diese Tabelle angelegt:

 

  A B C D E
1

Bonusabrechnung
2

Name

Gehalt

Fehltage

%-Bonus Bonusbetrag
3 Gredler 3080 0    
4 Zwicker 2110 8    
5 Forster 1975 4    
6 Krenn 2315 2    
7 Fleiss 2068 0    
8 Jagodic 1792 8    
9 Breitner 1670 7    
10 Ohlzahn 1845 1    
11 Dreml 3120 11    
12 Lastig 1900 4    
13 Uhlig 2205 2    

 

D3 bis D13 markieren und durch einen Klick auf als Prozentkategorie formatieren.

Jetzt benötigen wir noch eine Liste, in der die Bonusstufen angeführt sind. Es ist vollkommen egal, in welcher Spalte oder Zeile sich diese Tabelle befindet. Wir müssen dem Programm später nur mitteilen, wo sich die Liste befindet. Beginnen wir in A20. 

Achtung: Bevor wir die Zahlen von B21 abwärts eingeben, wird dieser Bereich (bis B29) markiert und durch einen Klick auf als Prozentkategorie formatiert.

 

  A B
20

Fehltage

%-Bonus
21

0

50%

22 2 40%
23 4 30%
24 5 20%
25 6 10%
26 7 5%
27 8 2%
28 9 1%
29 10 0%

 

Stellen Sie den Cursor nach D3 und geben Sie folgende Formel ein:

=SVERWEIS(C3;$A$21:$B$29;2)

Sieht auf den ersten Blick ziemlich verwirrend aus. Die Bestandteile der Formel bedeuten:

SVERWEIS steht für SuchenVerweis, es soll also ein Wert gesucht werden. Und zwar jener Wert, der zu der Zahl in C3 gehört. In unserem Beispiel steht in C3 eine 0. Diese 0 wird in der Tabelle, die von A21 bis (:) B29 reicht, gesucht. Die Zahl daneben, bei uns 50, steht in der zweiten Spalte, (daher 2) und wird in die Zelle D3 übernommen.

Jetzt wird die Formel bis D13 kopiert. Dafür, dass sich beim Kopieren der Formel der Hinweis auf den Ort unserer Bonusstufen-Liste (A21:B29) nicht ändert, sorgt das $-Zeichen ($A$21:$B$29) 

So sieht die Lösung aus:

 

  A B C D E
1

Bonusabrechnung
2

Name

Gehalt

Fehltage

%-Bonus Bonusbetrag
3 Gredler 3080 0 50

1540
4 Zwicker 2110 8 2  
5 Forster 1975 4 30  
6 Krenn 2315 2 40  
7 Fleiss 2068 0 50  
8 Jagodic 1792 8 2  
9 Breitner 1670 7 5  
10 Ohlzahn 1845 1 50  
11 Dreml 3120 11 0  
12 Lastig 1900 4 30  
13 Uhlig 2205 2 40  

 

Nun bleibt nur noch in E3 den Bonusbetrag zu berechnen:  =B3*D3

 

Tipp: Wir hätten die Tabelle von A21 bis B29 auch markieren und mit einem Namen (z.B. Fehltage) versehen können (Einfügen/Namen). Dann würde unsere Suchformel so aussehen: =SVERWEIS(C3;Fehltage;2)

Ausprobieren!

  

Beispiel 1:
Abhängig von ihrem Eintrittsdatum erhalten die Mitarbeiter unterschiedlich hohes Bilanzgeld (D) und unterschiedlich viele Sonderurlaubstage (E). In F3 wird ein Stichtag für die Berechnung des Zeitraums der Firmenzugehörigkeit festgelegt. Für das Feld F3 wird der NAME Stichtag definiert. B3 bis B8 als Datum formatieren, C3 bis C8 als Zahl formatieren.
Zu berechnen sind: die Gesamtmonate vom Eintritt bis zum Stichtag (keine Kommastellen); das Bilanzgeld und die Sonderurlaubstage.                                             Formeln am Seitenende

 

  A B C D E F
1

Prämientabelle
2 Name Eintritt Gesamtmonate Bilanzgeld Urlaubstage Stichtag
3 Kuntner 1.9.2000      

1. 1. 2003
4 Dvorak 7.1.1974        
5 Osabal 5.6.1998        
6 Konas 2.5.1988        
7 Tunke 5.7.2001        
8 Fleck 3.2.1994        

 

  A B C
10

Suchtabelle
11 Monate beschäftigt Bilanzgeld Sonderurlaubstage
12 0 0 0
13 60 150 1
14 120 250 2
15 180 300 3
16 240 400 4
17 300 500 6

 

Beispiel 2
Die Mieter einer Wohnanlage haben abhängig von der Wohnungsgröße und Lage der Wohnung unterschiedliche Betriebskosten zu bezahlen.
Die Heizkosten betragen für eine Wohnung mit 30 m2 ... 45.- € monatlich, ab 50 m2 ... 65.- €, ab 70 m2 ... 90.- €, ab 90 m2 ... 120.- € und ab 120 m2 ... 150.-€.
Die Liftgebühren steigen mit zunehmender Stockwerkszahl. Befindet sich die Wohnung im 1. Stock beträgt die Gebühr monatlich 10.- €, ab dem 3. Stock ... 20.- €, ab dem 5 Stock ... 35.- € und ab dem 7. Stock 50.- €.

 

Monatliche Betriebskosten
Name des Mieters

Stock

 Fläche in m2 Heizkosten Liftgebühr Gesamt
KOSITS Vera 4 56      
LEITNER Christian 1 30      
SCHMAL Johann 2 48      
ARTNER Karl 1 92      
POSCH Britta 8 134      
PETRI Vroni 5 55      
SRB Norbert 9 78      
FLEISCHER Knut 3 99      
BRUNNER Rosa 6 104      

 

Aufgabe:
  • Tabelle anlegen
  • Mieternamen alphabetisch ordnen
  • Suchtabellen anlegen
  • Spalten D, E und F berechnen
Die Lösung sieht so aus:

 

Monatliche Betriebskosten
Name des Mieters

Stock

 Fläche in m2 Heizkosten Liftgebühr Gesamt
ARTNER Karl 1 92 120 10 130
BRUNNER Rosa 6 104 120 35 155
FLEISCHER Knut 3 99 120 20 140
KOSITS Vera 4 56 65 20 85
LEITNER Christian 1 30 45 10 55
PETRI Vroni 5 55 65 35 100
POSCH Britta 8 134 150 50 200
SCHMAL Johann 2 48 45 10 55
SRB Norbert 9 78 90 50 140

 

Beispiel 3
Ein Miettaxi-Unternehmen gewährt verrechnet seinen Kunden umsatzabhängige Preisstaffeln.
1. In C3 ist aus der Preistabelle der "km-Preis" mit einem SVERWEIS zu ermitteln
2. In E3 ist aus der Gutschrift-Tabelle der "Euro-Wert" mit einem SVERWEIS zu ermitteln
3. In F3 wird die Gutschrift von Gesamt-Kilometerpreis subtrahiert

Lösungen am Seitenende

 

A B C D E F
1

Mietwagen KG
2 Name gefahrene km

Euro/km

 Fahrten/Jahr Gutschrift Jahreskosten
3 K+M AG 1820   26    
4 Murtner 17391   867    
5 HANS KG 4012   94    
6 AWP 645   12    
7 Streich 9766   306    
8 BON & Cie 253   10    
9 Slimm 12900   591    
10            
11            
12            
13            
14            
15

Preistabelle

  

Gutschrift

  
16 gefahrene km km-Preis   Fahrten/Jahr Wert in Euro  
17 1 1,40   10 5  
18 500 1,35   30 7  
19 1000 1,30   50 10  
20 3000 1,20   100 15  
21 5000 1,10   200 20  
22 10000 1,00        

 

A B C D E F
1

Mietwagen KG
2 Name gefahrene km

Euro/km

 Fahrten/Jahr Gutschrift Jahreskosten
3 K+M AG 1820 1,30 26 5 2361,00
4 Murtner 17391 1,00 867 20 17371,00
5 HANS KG 4012 1,20 94 10 4804,40
6 AWP 645 1,35 12 5 865,75
7 Streich 9766 1,10 306 20 10722,60
8 BON & Cie 253 1,40 10 5 349,20
9 Slimm 12900 1,00 591 20 12880,00

 

 

Lösung zu Beispiel 1:
In C3 steht: =(Stichtag-B3)/30      In D3 steht: =SVERWEIS(C3;$A$12:$C$17;2)          In E3 steht: =SVERWEIS(C3;$A$12:$C$17;3)
Lösung zu Beispiel 3:
In C3 steht: =SVERWEIS(B3;$A$17:$B$22;2)    In E3 steht: =SVERWEIS(D3;$D$17:$E$21;2)   In F3 steht: =(C3*B3)-E3

EXCEL-BEISPIELE        SVERWEIS mit STAROFFICE-CALC