Excel 2019 (Teil 14): Relative und absolute Zellbezüge

In Excel gibt es zwei Arten von Zellbezügen: relative und absolute. Relative und absolute Bezüge verhalten sich unterschiedlich, wenn sie kopiert und in andere Zellen eingefügt werden. Relative Bezüge ändern sich, wenn eine Formel in eine andere Zelle kopiert wird. Absolute Referenzen hingegen bleiben unverändert, unabhängig davon, wohin sie kopiert werden.

Relativer Bezug

Standardmäßig sind alle Zellbezüge relative Bezüge. Beim Kopieren über mehrere Zellen hinweg ändern sie sich basierend auf der relativen Position der Zeilen und Spalten. Wenn Sie beispielsweise die Formel =A1+B1 von Zeile 1 in Zeile 2 kopieren, wird die Formel zu =A2+B2. Relative Referenzen sind besonders praktisch, wenn Sie dieselbe Berechnung über mehrere Zeilen oder Spalten hinweg wiederholen müssen.

So erstellen und kopieren Sie Formeln mithilfe relativer Referenzen

Im folgenden Beispiel soll eine Formel erstellt werden, die den Preis jedes Artikels mit der Menge multipliziert. Anstatt für jede Zeile eine neue Formel zu erstellen, können Sie in Zelle D4 eine einzelne Formel erstellen und diese dann in die anderen Zeilen kopieren. Im Beispiel werden relative Referenzen verwendet, sodass die Formel die korrekte Summe für jedes Element berechnet.

1. Wählen Sie die Zelle aus, die die Formel enthalten soll. Zum Beispiel wählen wir Zelle D4 aus.

2. Geben Sie die Formel ein, um den gewünschten Wert zu berechnen. Geben Sie beispielsweise =B4*C4 ein.

3. Drücken Sie die Eingabetaste auf der Tastatur. Die Formel wird berechnet und die Ergebnisse in der Zelle angezeigt.

4. Suchen Sie den Füllpunkt in der unteren rechten Ecke der gewünschten Zelle. Das Beispiel bestimmt die Position des Füllpunkts für Zelle D4.

5. Klicken Sie auf den Füllpunkt und ziehen Sie ihn zu den Zellen, die Sie füllen möchten. Beispielsweise wählen wir die Zellen D5:D13 aus.

6. Lassen Sie die Maus los. Die Formel wird mit relativen Referenzen in die ausgewählten Zellen kopiert und die Ergebnisse werden in jeder Zelle angezeigt.

Sie können auf gefüllte Zellen doppelklicken, um die Richtigkeit ihrer Formeln zu überprüfen. Die relativen Zellbezüge müssen je nach Zeile für jede Zelle unterschiedlich sein.

Absolute Referenz

Es kann vorkommen, dass Sie nicht möchten, dass sich ein Zellbezug ändert, wenn er in andere Zellen kopiert wird. Im Gegensatz zu relativen Referenzen ändern sich absolute Referenzen nicht, wenn sie kopiert oder ausgefüllt werden. Sie können eine absolute Referenz verwenden, um eine Zeile und/oder Spalte konstant zu halten.

Ein absoluter Bezug wird in einer Formel durch Hinzufügen eines Dollarzeichens ( $ ) angegeben. Dieses Symbol kann vor einem Spaltenverweis, einem Zeilenverweis oder beidem stehen.

Im Allgemeinen verwenden Sie das Format $A$2 , wenn Sie Formeln erstellen, die absolute Bezüge enthalten. Die verbleibenden beiden Formate werden deutlich seltener verwendet.

Beim Schreiben einer Formel können Sie die Taste F4 auf Ihrer Tastatur drücken, um zwischen relativen und absoluten Zellbezügen zu wechseln, wie im Video unten gezeigt. Dies ist eine einfache Möglichkeit, schnell eine absolute Referenz einzufügen.

So erstellen und kopieren Sie Formeln mit absoluten Bezügen

Im folgenden Beispiel wird Zelle E2 (die den Steuersatz von 7,5 % enthält) verwendet, um die Umsatzsteuer für jeden Artikel in Spalte D zu berechnen. Um sicherzustellen, dass der Bezug zum Steuersatz gleich bleibt – auch wenn die Formel kopiert und in andere Zellen übernommen wird – muss die Zelle $E$2 als absoluter Bezug festgelegt werden.

1. Wählen Sie die Zelle aus, die die Formel enthalten soll. Zum Beispiel wählen wir Zelle D4 aus.

2. Geben Sie die Formel ein, um den gewünschten Wert zu berechnen. Das Beispiel würde =(B4*C4)*$E$2 eingeben , was $E$2 zu einer absoluten Referenz macht.

3. Drücken Sie die Eingabetaste auf der Tastatur. Die Formel wird berechnet und das Ergebnis wird in der Zelle angezeigt.

4. Suchen Sie den Füllpunkt in der unteren rechten Ecke der gewünschten Zelle. Das Beispiel bestimmt die Position des Füllpunkts für Zelle D4.

5. Klicken Sie auf den Füllpunkt und ziehen Sie ihn zu den Zellen, die Sie füllen möchten ( im Beispiel Zellen D5:D13 ).

6. Lassen Sie die Maus los. Die Formel wird mit absoluten Bezügen in die ausgewählten Zellen kopiert und die Werte werden in jeder Zelle berechnet.

Sie können auf die gefüllten Zellen doppelklicken, um die Richtigkeit der Formeln zu überprüfen. Absolute Bezüge müssen für jede Zelle gleich sein, während andere Bezüge relativ zur Zeile der Zelle sind.

Achten Sie darauf, das Dollarzeichen ( $ ) einzufügen, wenn Sie einen absoluten Bezug über mehrere Zellen herstellen. Die Dollarzeichen wurden im folgenden Beispiel weggelassen. Dies führt dazu, dass Excel es als relative Referenz interpretiert und beim Kopieren in andere Zellen zu einem falschen Ergebnis führt.

Verwenden Sie Zellbezüge mit mehreren Arbeitsblättern

Mit Excel können Sie auf jede Zelle in jedem Arbeitsblatt verweisen. Dies kann besonders nützlich sein, wenn Sie einen bestimmten Wert von einem Arbeitsblatt auf ein anderes verweisen möchten. Beginnen Sie dazu einfach Ihre Zellreferenz mit dem Blattnamen, gefolgt von einem Ausrufezeichen ( ! ). Wenn Sie beispielsweise auf Zelle A1 in Sheet1 verweisen möchten , lautet die Zellreferenz Sheet1!A1.

Beachten Sie, dass Sie den Namen in Anführungszeichen ( '' ) setzen müssen, wenn der Blattname Leerzeichen enthält. Wenn Sie beispielsweise auf Zelle A1 in einem Arbeitsblatt mit dem Namen „ Juli-Budget“ verweisen möchten , lautet der Zellbezug „Juli-Budget“!A1.

So referenzieren Sie Zellen in Arbeitsblättern

Das folgende Beispiel bezieht sich auf eine Zelle mit einem berechneten Wert zwischen zwei Arbeitsblättern. Dadurch kann der exakt gleiche Wert in zwei verschiedenen Arbeitsblättern verwendet werden, ohne dass Formeln neu geschrieben oder Daten dupliziert werden müssen.

1. Suchen Sie die Zelle, auf die Sie verweisen möchten, und notieren Sie sich deren Blatt. Beispielsweise möchten wir auf Zelle E14 im Arbeitsblatt „Menüreihenfolge“ verweisen.

2. Navigieren Sie zum gewünschten Arbeitsblatt. Als Beispiel wählen wir die Tabelle „Catering-Rechnung“ .

3. Suchen Sie die Zelle, in der der Wert angezeigt werden soll, und wählen Sie sie aus. Zum Beispiel wählen wir Zelle C4 aus.

4. Geben Sie das Gleichheitszeichen ( = ), den Arbeitsblattnamen gefolgt von einem Ausrufezeichen (!) und die Zellenadresse ein. Geben Sie beispielsweise ='Menüreihenfolge'!E14 ein.

5. Drücken Sie die Eingabetaste auf der Tastatur. Der Wert der referenzierten Zelle wird angezeigt. Wenn sich nun der Wert der Zelle E14 im Arbeitsblatt „Menübestellung“ ändert , wird er automatisch im Arbeitsblatt „Catering-Rechnung“ aktualisiert.

Wenn Sie Ihr Arbeitsblatt später umbenennen, wird der Zellbezug automatisch aktualisiert, um den neuen Arbeitsblattnamen widerzuspiegeln.

Wenn Sie den falschen Blattnamen eingeben, wird #REF! In der Zelle wird ein Fehler angezeigt. Im folgenden Beispiel wurde der Name des Blattes falsch geschrieben. Um Fehler zu bearbeiten, zu ignorieren oder nach Fehlern zu suchen, klicken Sie auf die Schaltfläche „Fehler“ neben der Zelle und wählen Sie eine Option aus dem Menü.