NETSCHOOL    EXCEL-INHALT

BEZÜGE    SVERWEIS    Beispiel mit WENN + SVERWEIS    SVERWEIS mit EXCEL 2010


 

186 - 109 = 75 Zähler WM-Vorsprung (?)  Aus: Gratiszeitung HEUTE, 27. 6. 2011 S 37

 

GRUNDRECHNUNGSARTEN mit EXCEL

Zellen, deren Inhalte für Berechnungen verwendet werden, können nebeneinander oder untereinander stehen. Man kann aber auch mit Zellen rechnen, die "wild" herausgepickt werden, z. B. B4 + E2 - A1 oder A7 x F3 x C1, ...
Jeder Rechenvorgang beginnt mit =.
Eine Spalte bzw. Zeile kann rasch addiert werden, in dem man die Zellen markiert und anschließend das Summen-symbol - ∑ - anklickt.
Addieren Subtrahieren
  A B C D E F G   I J K L M N O
1 12 8 24 36 42 2 =B2+C6+A3   67 1 12 8 24 36 42 2 =G2-K1-L1 110
2 48 54 8 16 6 38 =Summe(A2:F2)170 2 88 106 55 19 29 33 =A7-D5-M2 38
3 9 28 56 49 14 30   3 48 54 8 16 6 38  
4 15 108 64 15 72 50  ∑  309 4 9 28 56 49 14 30 =K4-Summe(L4:N4) -37
5 39 42 144 21 27 98       5 15 108 64 15 72 50  
6 5 20 4 7 81 100       6 39 42 144 21 27 98 =M7-K7-I6 38
7 88 106 55 19 29 33       7 5 20 4 7 81 100    
8 ∑ 216                                

 

Multiplizieren Dividieren
  A B C D E F G   I J K L M N O
1 12 8 24 36 42 2 =A1*B1  96 1 12 8 24 36 42 2 =I1/K1 0,5
2 48 54 8 16 6 38 =B2*C6*F1 432 2 88 106 55 19 29 33 =C2*B2/J2 4,08
3 9 28 56 49 14 30   3 48 54 8 16 6 38 =F3*K3*M3 1440
4 15 108 64 15 72 50  =F1*G1  4800 4 9 28 56 49 14 30 =F4*K4*M4 39200
5 39 42 144 21 27 98       5 15 108 64 15 72 50 =F5*K5*M5 451584
6 5 20 4 7 81 100       6 39 42 144 21 27 98 =F6*K6*M6 388800
7 88 106 55 19 29 33       7 5 20 4 7 81 100 =O3/K1/E2 10
 

Formeln Kopieren:

  • Berechnung vornehmen (siehe Zelle O3)

  • Mauszeiger auf das Quadrat in der rechten unteren Ecke stellen

  • Markierung mit gedrückter linker Maustaste bis O6 erweitern
     
     

  • Maustaste loslassen
     

 

BEZÜGE

Ein Bezug ist der Hinweis auf eine bestimmte Zelle, deren Inhalt an anderer Stelle (in einer anderen Zelle) verwendet wird.

Bezug 1: Den Inhalt einer Zelle in eine andere kopieren

  • Mauszeiger in eine Zelle stellen
  • mit = und Klick in die Zelle mit dem benötigten Inhalt
     
     
  • wird der Inhalt von B2 nach Q1 "transportiert"

Bezug 2: Formeln kopieren - dabei passt sich die Formel den neuen Zellkoordinaten an

  • Beim Kopieren der Zelle O3 - siehe Beispiel oben =F3*K3*M3 - nach unten, verändert sich die Formel und lautet in O4 =F4*K4*M4, in O5 =F5*K5*M5, u. s. w.

Bezug 3: Die automatische Anpassung der Zelladressen kann auch unerwünscht sein:
 
  A B C D E F
1 Anzahl Artikel Stückpreis Nettopreis UStSatz Gesamtpreis
2 3 L3326 18,4 =A2*C2 20 % =D2+(D2*E2)
3 8 L3412 81,2      
4 2 L4501 17,1      
5 12 G707 298,6      
6 9 G313 156,7      
7            
 
  • Formel in D2 nach unten kopieren
  • Formel in F2 nach unten kopieren
  A B C D E F
1 Anzahl Artikel Stückpreis Nettopreis UStSatz Gesamtpreis
2 3 L3326 18,4 55,2 20 % 66,2
3 8 L3412 81,2 649,6   649,6
4 2 L4501 17,1 34,2   34,2
5 12 G707 298,6 3583,2   3583,2
6 9 G313 156,7 1410,3   1410,3
7            
 
Problem! ab F3 wird die Umsatzsteuer nicht berücksichtigt.
Grund: die Formel in F3 lautet =D3+(D3*E3). E3 enthält jedoch keinen Wert, daher steht in F3 der Nettopreis.
Damit der UStSatz von 20 % mitkopiert wird, muss man die Zelle E2 in der Formel "fix verankern". Das geschieht mit dem $-Symbol:
  • in F2 Formel korrigieren =D2+(D2*$E$2)
  • und nach unten kopieren (in F6 steht =D6+(D6*$E$2))
  A B C D E F
1 Anzahl Artikel Stückpreis Nettopreis UStSatz Gesamtpreis
2 3 L3326 18,4 55,2 20 % 66,24
3 8 L3412 81,2 649,6   779,52
4 2 L4501 17,1 34,2   41,04
5 12 G707 298,6 3583,2   4299,84
6 9 G313 156,7 1410,3   1692,36
7            

 

 

SVERWEIS

Der SuchVERWEIS "sucht" in einer Art kleiner Datenbank nach einem "passenden" Wert und "holt" diesen in ein bestimmtes Feld.
Im unten abgebildeten Beispiel sollen - abhängig vom Gewicht einer Paketsendung - die Versandkosten in die Spalte Porto eingesetzt werden.
  • Angaben übertragen
  • Cursor in C3
  • Formel eingeben =SVERWEIS(B3;$E$3:$G$8;2)
    in Worten suche =SVERWEIS - zum Paketgewicht B3 - in der Tabelle, die von E3 bis G8 reicht nach dem passenden Tarif, in der zweiten 2 Spalte.
    Wie im Kapitel Bezüge beschrieben, ermöglicht das $-Symbol das "fehlerfreie" Kopieren der Formel
  • Formel bis C8 kopieren

Soll nach Portogebühren für den europäischen Raum gesucht werden, wird die Formel leicht modifiziert:
=SVERWEIS(B3;$E$3:$G$8;3)

Beispiel mit WENN und SVERWEIS
Noch benutzerfreundlicher wird die Tabelle, wenn die Zielstaaten in die Suche einbezogen werden.
=WENN der Empfangsort B3 in Österreich liegt "A"; dann nimm die Werte der zweiten 2 Spalte; ansonsten jene aus der dritten 3 Spalte

NETSCHOOL    EXCEL-INHALT    SVERWEIS mit EXCEL 2010