Formeln kopieren mit absoluten und relativen Bezügen


Bitte lesen Sie dieses Kapitel aufmerksam durch, wenn es sein muss, mehrmals!
Sie werden an EXCEL nie Freude haben, wenn Sie dieses zentrale Thema nicht verstanden haben!

Kopiert man eine Zelle, die eine Formel enthält, in andere Zellen, so passiert etwas Eigenartiges:

Abbildung 40, Was passiert beim Formeln kopieren?

Die Formel in der markierten Zelle C2 der Abbildung 40 lautet =A2+B2, wie man in der Bearbeitungszeile sehen kann.

Diese Formel wurde in die Zellen C3:C7 kopiert. Beim Kopieren ändert sich der Wert der Zelle scheinbar willkürlich, aus 5 wird 11, 17, 23, 29, 35. Aber nicht nur der Wert der Zelle, auch die Formel wurde verändert, die veränderten Formeln stehen in der Nachbarspalte D zur Anschauung.

Wenn sich nun aber beim Kopieren sowohl der Wert als auch die Formel ändert, was bitte schön ist denn dann kopiert worden?

Stellen Sie sich vor, ich sei die Zelle C2. In meiner Formel steht:
Addiere die beiden Zellen links von mir..

Genau diese Anweisung „Addiere die beiden Zellen links von mir“ wird kopiert.

Das Ergebnis macht genau das, was man in einer Tabellenstruktur erwartet: In jeder Zelle der Spalte C steht das Ergebnis der Addition der beiden Nachbarzellen.

Diese Art der Kopie nennt man „Kopieren mit relativen Bezügen“. Es ist die wichtigste und gebräuchlichste Methode, die EXCEL kennt.

Dabei ist es völlig unerheblich, wie die Bezüge der ersten Formel geartet sind.

Abbildung 41 soll das veranschaulichen. Die Formel in Zelle C4 lautet „übersetzt“: Gehe zwei Zellen nach oben und multipliziere die beiden Zellen links. Die blauen Pfeile verdeutlichen die Bezüge.

Abbildung 41, etwas komplexere Bezüge in der Formel

Wie man an der Grafik der blauen Pfeile sehen kann, wird auch diese Anweisung kopiert.

Diese Methode, das Kopieren mit relativen Bezüge, ist geeignet, die allermeisten Aufgaben bei der Tabellenkalkulation zu lösen.

Aber nicht alle.

Abbildung 42 zeigt ein Beispiel, bei dem die Methode offenbar nicht funktioniert.

Abbildung 42, Kopieren mit Relativ-Bezügen fehlgeschlagen

Zum Verständnis: Zu den Nettobeträgen in Spalte A soll in Spalte B die Mehrwertsteuer berechnet werden. Der Mehrwertsteuersatz steht in Zelle D2.

Die Formel „=D2*A3“ in der markierten Zelle berechnet zwar die richtige Mehrwertsteuer in Zelle B3 (0,71), der Versuch, diese Formel zu kopieren, schlug aber fehl.
Warum das nicht funktioniert, zeigt die Grafik:

Multipliziere die Zelle links neben mir mit der Zelle rechts um 1 versetzt nach oben“ löst offenbar das Problem nicht.

Richtig müsste der Befehl lauten:

Multipliziere die Zelle links neben mir mit der Zelle D2

Der erste Bezug „Zelle links neben mir“ ist zwar weiterhin ein relativer Bezug, der zweite jedoch benennt die Zelle D2, lässt also keine relative Anpassung zu. Nur in D2 steht der Mehrwertsteuersatz, also müssen sich alle Formeln auf D2 beziehen, D2 muss in der Formel, die kopiert werden soll, als sog. Absolutbezug gekennzeichnet werden, damit EXCEL weiß, dass dieser Bezug beim Kopieren nicht angepasst werden darf, sondern in jede Zelle unverändert übernommen werden soll.

Abbildung 43 zeigt die richtige Methode.

Abbildung 43, Kopieren einer Formel mit einem Absolutbezug

In der Bearbeitungszeile sehen Sie, wie sich die Formel verändert hat: Aus dem Bezug D2 ist $D$2 geworden. Das vorangestellte $-Zeichen kennzeichnet diesen Bezug als Absolut-Bezug. Dieser Bezug wird beim Kopieren nicht mehr verändert.

Die Grafik von Abbildung 43 zeigt deutlich, dass sich nun alle Formeln in Spalte B auf die Zelle D2 beziehen.


Sie müssen die $-Zeichen nicht mühsam schreiben: Klicken Sie in den Bezug und betätigen Sie die Funktionstaste 4 einmal. EXCEL fügt die $-Zeichen ein.

 

So kopieren Sie schnell eine Formel:
Ziehen Sie die markierte Zelle am Anfasser über die Zielzellen.


 

More:

Übungen zu absoluten und relativen Bezügen