Wie sich Bezüge beim Kopieren anpassen

Wenn Sie in einer Formel =A2 schreiben, merkt sich die Tabellenkalkulation nicht wirklich „Zelle A2“. Sie merkt sich vielmehr eine Richtung und einen Abstand, etwa „die Zelle zwei Spalten links von mir“.

Das nennt man eine relative Referenz. Da die Referenz relativ zur Position der Formel beschrieben wird, passt sie sich an, sobald Sie die Formel an eine neue Stelle kopieren. Dieses eine Verhalten ist der Grund dafür, dass das Ausfüllen so gut funktioniert.

Richten Sie zwei Zahlenspalten ein:

• A2 = 100, B2 = 50
• A3 = 200, B3 = 80

Geben Sie in C2 =A2+B2 ein. Das Ergebnis ist 150. Kopieren Sie C2 nun nach unten in C3. Das Ergebnis ist 280, weil die Formel zu =A3+B3 wurde.

Sie mussten nichts bearbeiten. Die Referenzen sind der Formel eine Zeile nach unten gefolgt.

Wenn Sie eine Formel nach unten kopieren, ändern sich nur die Zeilennummern. Die Spaltenbuchstaben bleiben unverändert.

Ausgehend von =A2+B2 gilt:

• Eine Zeile nach unten wird zu =A3+B3
• Zwei Zeilen nach unten wird zu =A4+B4
• Drei Zeilen nach unten wird zu =A5+B5

Die Spalten A und B verschieben sich nie, weil Sie sich nicht seitwärts bewegen.

Beim Kopieren einer Formel zur Seite ändern sich die Spaltenbuchstaben, während die Zeilennummern unverändert bleiben.

Angenommen, B2 enthält =B1*2. Kopieren Sie die Formel nach rechts in C2 und D2:

• In C2 steht dann =C1*2
• In D2 steht dann =D1*2

Die Zeile bleibt 1, weil Sie sich nur seitwärts und nicht nach unten bewegt haben. Bewegen Sie sich gleichzeitig in beide Richtungen, verschieben sich beide Teile gemeinsam.

Ein hilfreiches Denkmodell: Eine relative Referenz speichert einen Offset von der Formelzelle aus.

Wenn C2 =A2 enthält, lautet der Offset „zwei Spalten nach links, gleiche Zeile“. Kopieren Sie die Formel nach C5, wird der Offset von der neuen Position aus erneut angewendet: zwei Spalten links von C5 in derselben Zeile, also A5.

Der Pfeil zwischen der Formel und ihrem Ziel behält immer dieselbe Länge und Richtung bei.

Stellen Sie sich eine Rechnung mit 1.000 Zeilen vor. In jeder Zeile müssen Menge und Preis multipliziert werden. Ohne relative Referenzen müssten Sie 1.000 verschiedene Formeln von Hand schreiben.

Mit relativen Referenzen schreiben Sie einmal =A2*B2 und füllen die Formel nach unten aus. Jede Zeile verweist automatisch auf ihre eigene Menge und ihren eigenen Preis. Eine Formel, tausend richtige Ergebnisse, keine Tippfehler.

Relative Referenzen machen aus einer mühsamen Aufgabe einen einzigen Doppelklick.

Sie können sich davon selbst überzeugen. Klicken Sie nach dem Ausfüllen auf eine der kopierten Zellen und sehen Sie sich oben die Formelleiste an.

Kopieren Sie =A2+B2 fünf Zeilen nach unten und klicken Sie dann auf die letzte Zelle. In der Formelleiste steht =A7+B7, nicht =A2+B2. Jede Zelle enthält ihre eigene angepasste Version.

Wenn Sie die Spalte durchgehen und die Formelleiste lesen, entwickeln Sie am besten ein Gefühl dafür, wie das Kopieren funktioniert.

Die relative Anpassung ist meistens das, was Sie brauchen, aber nicht immer. Angenommen, jede Zeile soll mit einem einzigen Steuersatz in der Zelle E1 multipliziert werden.

Wenn Sie =C2*E1 schreiben und nach unten ausfüllen, verschiebt sich der Teil E1 zu E2, E3, E4 und so weiter und landet in leeren Zellen. Das Ergebnis ist falsch.

C2 soll nach unten wandern, E1 aber fest bleiben. Dafür benötigen Sie eine absolute Referenz, die im nächsten Kurs behandelt wird.

Hier ist schon einmal ein kleiner Vorgeschmack auf die Lösung. Wenn Sie vor einen Teil einer Referenz ein Dollarzeichen setzen, wird dieser fixiert und kann sich nicht mehr verschieben.

Schreiben Sie =C2*$E$1 und füllen Sie die Formel nach unten aus. Jetzt verschiebt sich C2 zu C3, C4 und so weiter, während $E$1 jedes Mal unverändert auf den Steuersatz verweist.

Bald beherrschen Sie diesen Trick mit dem Dollarzeichen. Für den Moment genügt es, dass Sie wissen, dass es ihn gibt.

Alles hier funktioniert in Microsoft Excel und Google Sheets identisch. Beide verwenden standardmäßig relative Referenzen, verschieben beim Ausfüllen nach unten die Zeilen und beim Ausfüllen zur Seite die Spalten.

Auch die Syntax zum Fixieren mit Dollarzeichen ist in beiden Programmen gleich. Die Fähigkeiten, die Sie beim Kopieren von Formeln in einer App erwerben, lassen sich direkt auf die andere übertragen.

Wenn eine ausgefüllte Spalte falsche Zahlen anzeigt, liegt die Ursache fast immer in einer Referenz, die sich verschoben hat, obwohl sie fest bleiben sollte.

Klicken Sie auf eine fehlerhafte Zelle und lesen Sie die Formelleiste. Fragen Sie sich: Verweist sie auf die erwartete Stelle? Wenn eine Referenz, die fest bleiben sollte, abgewichen ist, haben Sie den Fehler gefunden. Ein Dollarzeichen schafft Abhilfe.

Überprüfen Sie, wie gut Sie verstanden haben, wie sich Referenzen verschieben.

Sie verstehen nun den Mechanismus hinter dem Ausfüllen:

• Referenzen sind standardmäßig relativ und werden als Offset von der Formelzelle aus gespeichert.
• Beim Ausfüllen nach unten verschieben sich die Zeilennummern, beim Ausfüllen zur Seite die Spaltenbuchstaben.
• Durch diese automatische Anpassung kann eine Formel für Tausende von Zeilen verwendet werden.
• Wenn eine Referenz unverändert bleiben muss, wird sie durch ein Dollarzeichen fixiert. Das lernen Sie im nächsten Kurs.

Lesen Sie die Formelleiste, sobald eine ausgefüllte Spalte falsch aussieht.