Wenn Formeln und Funktionen über das automatische Ausfüllen intelligent kopiert werden, so werden die Zell-Adressen in den Formeln entsprechend angepasst. Hierfür werden zwei Varianten unterschieden, die relative und die absolute Adressierung.
Relative Adressierung
Wird eine Formel durch automatisches Ausfüllen auf andere Zellen übertragen, so werden automatisch in den Zell-Adressen die Spalten oder/und Zeilen angepasst. Dies wird als «relative Adressierung» bezeichnet.
Die Animation in Abb.1 zeigt die relative
Adressierung. Die Formel «=SUMME(B2:E2)»
in der Zelle F2 wird durch automatisches Ausfüllen in die Zellen F3 und F4
kopiert. Danach werden die Zellen F3 und F4 angelklickt um die übertragenen
Formeln zu zeigen, z.B. steht am Ende in F4 die Formel «=SUMME(B4:E4)»
d.h. die Zeilennummern wurden entsprechend angepasst.
Abb.1: Relative Adressierung
Analog werden beim
automatischen Ausfüllen von Spalte zu Spalte die Spalten-Buchstaben
entsprechend angepasst.
Absolute Adressierung
Manchmal ist es wünschenswert, dass die
Spalten oder/und Zeilen in den Zell-Adressen gerade eben nicht beim automatischen Ausfüllen angepasst
werden. Um das zu verhindern wird das $-Zeichen vor den Spaltenbuchstaben oder vor die
Zeilennummer in einer Zell-Adresse geschrieben, z.B. «$B$7». Eine
Adressangabe mit $-Zeichen wird als absolute Adressierung bezeichnet.
Die Animation in Abb.2 zeigt,
den Effekt der absoluten Adressierung. Die Formel «=SUMME(B2:B4)*$B7»
in der Zelle B5 wird durch automatisches Ausfüllen in die Spalten C5, D5, E5
und F5 übertragen. Dabei werden die Spalten in der Summen-Formel angepasst (relative
Adressierung) aber die durch das $-Zeichen gesperrte Spalte in $B7 bleibt
unverändert. Wie in der Animation Abb.2 gezeigt wird, steht am Ende in der
Zelle F5 die Formel «=SUMME(F2:F4)*$B7».
Abb.2: Absolute Adressierung
Analog werden beim
automatischen Ausfüllen von Zeile zu Zeile die Zeilennummern angepasst. Falls
eine Zeilennummer durch ein vorangestelltes $-Zeichen gesperrt wird, so bleibt
diese beim Übertragen unverändert.
Die Animation in Abb.3 zeigt, was ohne das $-Zeichen im
Beispiel in Abb.2 passieren würde. Ohne das $-Zeichen vor «$B7» würde
statt «=SUMME(F2:F4)*$B7»
in der Zelle F5 am Ende «=SUMME(F2:F4)*F7»
stehen. Da die Zelle F7 leer ist, würde ein falscher Wert berechnet werden.
Abb.3: Falsche Variante absolute Adressierung
Übungen
Absolute und relative Adressierung
Übernehme die nachfolgende Vorlage mit Punkt- oder Komma-Dezimaltrennzeichen in dein Tabellenkalkulationsprogramm (per Copy-Paste, ganze Tabelle auf der Webseite markieren, in die Zwischenablage kopieren, eine Zelle im Tabellenkalkulationsprogramm auswählen und einfügen).
Berechne in der Spalte «Ganze Woche» die Summe der Vorarbeitsstunden per Formel.
Übertrage die Formel durch automatisches Ausfüllen auf die anderen beiden Zeilen.
Berechne für Montag die Summe der Stunden in dieser Spalte und multipliziere das Resultat mal den Stundenansatz. Berechne die Summe der Stunden per Formel und multipliziere diese Formel am Ende mal den Stundenansatz.
Achtung! Schreibe bei der Berechnung für Montag nicht 55.00 in die Formel sondern die Zell-Adresse dieser Zelle. Versehe die Berechnung für Montag mit einer absoluten Adressierung ($-Symbolen), so dass die Formel für die Montagsberechnung danach auf die anderen Wochentage und auch in die Spalte für die ganze Woche übertragen werden kann.
Übertrage die Formel für Montag auf die anderen Wochentage und die ganze Woche durch automatisches Ausfüllen. Wenn du alles richtig gemacht hast, steht am Ende als Betrag für die ganze Woche 1622.50 in der entsprechenden Zelle.
Gönne dir nach getaner Arbeit einen höheren Stundenansatz und überschreibe die Zahl 55.00 mit 62.00. Wie reagiert die Tabellenkalkulation darauf? Wenn du alles richtig gemacht hast, steht am Ende als Betrag für die ganze Woche 1829.00 in der entsprechenden Zelle.
Vorlage mit Punkt-Dezimaltrennzeichen
Arbeit
Mo
Di
Mi
Do
Fr
Ganze Woche
Vorbereiten (in h)
4.5
3.0
2.0
Umsetzen (in h)
2.0
4.0
2.0
4.0
2.0
Nachbearbeiten (in h)
1.0
1.5
0.5
3.0
Betrag (in CHF)
Stundenansatz CHF / h
55.00
Vorlage mit Komma-Dezimaltrennzeichen
Arbeit
Mo
Di
Mi
Do
Fr
Ganze Woche
Vorbereiten (in h)
4,5
3,0
2,0
Umsetzen (in h)
2,0
4,0
2,0
4,0
2,0
Nachbearbeiten (in h)
1,0
1,5
0,5
3,0
Betrag (in CHF)
Stundenansatz CHF / h
55,00
Funktionswerte
In dieser Übung geht es darum viele Werte der Funktion y=a*sin(x) in einer Tabelle darzustellen. Aus den Werten lässt sich dann z.B. ein Diagramm der Funktion zeichnen, was aber in dieser Aufgabe nicht gemacht wird.
Übernehme die nachfolgende Vorlage mit Punkt- oder Komma-Dezimaltrennzeichen in dein Tabellenkalkulationsprogramm (per Copy-Paste, ganze Tabelle auf der Webseite markieren, in die Zwischenablage kopieren, eine Zelle im Tabellenkalkulationsprogramm auswählen und einfügen).
Markiere in der Spalte x die beiden Zellen mit 0.0 und 0.1 darin und verlängere die Spalte durch automatisches Ausfüllen nach unten bis der Wert 10.0 geschrieben wird.
Schreibe oben in die Spalte y neben die oberste Zahl 0.0 die Formel «=Q37*SIN(J56)» wobei du Q37 durch die Adresse der Zelle mit dem Wert 1.5 neben dem a ersetzt und J56 durch die Adresse der Zelle mit dem Wert 0.0.
Sorge mit der absoluten Adressierung dafür, dass die Adresse der Zelle mit dem Wert 1.5 beim automatischen Ausfüllen nach unten nicht geändert wird.
Übertrage die Berechnung in der Spalte y durch automatisches Ausfüllen nach unten bis das Ende der Spalte x erreicht ist. Wenn du alles richtig gemacht hast, sollte neben der 10.0 der Wert -0.816… stehen.
Ändere nun den Wert neben a von 1.5 auf 2.2 ab. Was passiert?
Vorlage mit Punkt-Dezimaltrennzeichen
a
1.5
x
y
0.0
0.1
Vorlage mit Komma-Dezimaltrennzeichen
a
1,5
x
y
0,0
0,1
Zinsrechnung
In dieser Übung lässt du Excel berechnen, wie viel Geld Du in 10 Jahren mit einem gegebenen Zinssatz ersparen kannst, wenn du das Konto / die Anlage unberührt lassen und das Konto keine jährlichen Gebühren kostet.
Übernehme die nachfolgende Vorlage mit Punkt- oder Komma-Dezimaltrennzeichen in dein Tabellenkalkulationsprogramm (per Copy-Paste, ganze Tabelle auf der Webseite markieren, in die Zwischenablage kopieren, eine Zelle im Tabellenkalkulationsprogramm auswählen und einfügen).
Markiere in der Spalte Jahre die beiden Zellen mit 0 und 1 darin und verlängere die Spalte durch automatisches Ausfüllen nach unten bis der Wert 10 geschrieben wird.
Schreibe in die Spalte Vermögen neben die Zahl 1 eine Formel welche zum Vermögen des Vorjahres den Zinszuwachs basierend auf dem Vorjahresvermögen hinzu addiert.
Sorge mit der relativen und absoluten Adressierung dafür, dass die Berechnung durch automatisches Ausfüllen sinnvoll nach unten übertragen werden kann.
Übertrage die Berechnung durch automatisches Ausfüllen nach unten bis das Ende der Spalte Jahre erreicht ist. Wenn du alles richtig gemacht hast, sollte neben der 10 der Wert 1104.62 stehen.
Ändere nun den Zinssatz von 1.00% auf 1.50% ab. Was passiert? Wenn du alles richtig gemacht hast, sollte neben der 10 der Wert 1160.54 stehen.
Optional: Runde das Vermögen jeweils auf den nächsten 5-Rappen-Betrag. Dazu multiplizierst du den Vermögenswert mit 20 und rundest das Resultat auf eine ganze Zahl (ohne Kommastellen). Anschliessend wird diese ganze Zahl wiederum durch 20 geteilt. Recherchiere im Internet wie du in deinem Tabellenkalkulationsprogramm auf ganze Zahlen runden kannst.
Vorlage mit Punkt-Dezimaltrennzeichen
Zinssatz
1.00%
Jahre
Vermögen
0
1000.00
1
Vorlage mit Komma-Dezimaltrennzeichen
Zinssatz
1,00%
Jahre
Vermögen
0
1000,00
1
Lösungsbeispiele
Zinsrechnung
Formel für die Vermögensberechnung: =Q84+J$22*Q84 wobei Q84 die Adresse der Zelle mit dem Vorjahresvermögen und J$22 die (absolute) Adresse der Zelle mit dem Zinssatz darin ist.
