Excel 2016 – Lektion 15: Relative und absolute Zellbezüge

• Excel 2016 (Teil 12): Seiten formatieren und Tabellen drucken
• Excel 2016 (Teil 13): Einführung in Formeln
• Excel 2016 (Teil 14): Komplexe Formeln erstellen

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

Bitte schließen Sie sich LuckyTemplates in diesem Artikel mit Anweisungen zu relativen und absoluten Zellbezügen in Excel 2016 an !

Relative und absolute Zellbezüge in Excel

• Relative Referenzen
  • Erstellen und kopieren Sie eine Formel, die relative Referenzen verwendet
• Absolute Referenzen
  • Erstellen und kopieren Sie eine Formel durch absolute Referenz
• Verwenden Sie Zellbezüge mit mehreren Arbeitsblättern
  • Verweisen Sie auf Zellen in Arbeitsblättern

Sehen Sie sich das Video unten an, um mehr über Zellreferenzen zu erfahren:

Relative Referenzen

Standardmäßig sind alle Zellbezüge relative Bezüge. Beim Kopieren über mehrere Zellen hinweg ändern sie sich basierend auf der relativen Position von 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.

Erstellen und kopieren Sie eine Formel, die relative Referenzen verwendet

Im folgenden Beispiel möchten wir eine Formel erstellen, die den Preis jedes Artikels mit der Menge multipliziert. Anstatt für jedes Element eine neue Formel zu erstellen, können wir in Zelle D2 eine einzelne Formel erstellen und diese dann in die anderen Zeilen kopieren. Wir verwenden relative Referenzen, um die Gesamtsumme für jeden Artikel genau zu berechnen.

1. Wählen Sie die Zelle aus, die die Formel enthält. Im Beispiel wählen wir Zelle D4 aus .

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

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

4. Suchen Sie den Füllpunkt in der unteren rechten Ecke der gewünschten Zelle. Im Beispiel suchen wir den Füllpunkt 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 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 deren genaue Formel zu überprüfen. Die relativen Bezüge für jede Zelle sollten je nach Zeile unterschiedlich sein.

Absolute Referenzen

Manchmal möchten Sie möglicherweise einen Zellbezug beim Kopieren in andere Zellen nicht ändern. Im Gegensatz zu relativen Referenzen ändern sich absolute Referenzen beim Kopieren oder Ausfüllen nicht. Sie können einen absoluten Verweis verwenden, um Zeilen und/oder Spalten fortlaufend zu halten.

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

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

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

Erstellen und kopieren Sie eine Formel durch absolute Referenz

Im folgenden Beispiel verwenden wir Zelle E2 (die einen Steuersatz von 7,5 % hat), um die Umsatzsteuer für jeden Artikel in Spalte D zu berechnen. Um sicherzustellen, dass der Bezug zum Steuersatz konstant bleibt – auch wenn die Formel kopiert wird und in andere Zellen gefüllt – wir müssen Zelle $E$2 zu einer absoluten Referenz machen.

1. Wählen Sie die Zelle aus, die die Formel enthält. Im Beispiel wählen wir Zelle D4 aus .

2. Geben Sie die Formel ein, um den gewünschten Wert zu berechnen. Im Beispiel geben wir =(B4*C4)*$E$2 ein , 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. Im Beispiel suchen wir den Füllpunkt 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 einem absoluten Bezug in die ausgewählten Zellen kopiert und die Werte werden in jeder Zelle berechnet.

Sie können auf gefüllte Zellen doppelklicken, um deren genaue Formel zu überprüfen. Der absolute Bezug in jeder Zelle sollte derselbe sein, während die anderen Bezüge relativ zur Zeile der Zelle sind.

Achten Sie darauf, das Dollarzeichen ($) anzugeben , wenn Sie absolute Bezüge über mehrere Zellen hinweg vornehmen. 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 falschen Ergebnissen führt.

Verwenden Sie Zellbezüge mit mehreren Arbeitsblättern

Mit Excel können Sie auf jede Zelle in einem Arbeitsblatt verweisen. Dies kann besonders nützlich sein, wenn Sie auf einen bestimmten Wert aus einem anderen Arbeitsblatt verweisen möchten. Dazu beginnen Sie einfach den Zellbezug mit dem Namen des Arbeitsblatts gefolgt von einem Ausrufezeichen (!) . Wenn Sie beispielsweise auf Zelle A1 in Sheet1 verweisen möchten , lautet der Zellbezug Sheet1!A1 .

Beachten Sie, dass Sie, wenn ein Arbeitsblattname Leerzeichen enthält, Klammern (' ') um den Namen einfügen müssen . Wenn Sie beispielsweise auf Zelle A1 in einem Arbeitsblatt mit dem Namen „ Juli-Budget“ verweisen möchten , lautet die Zellreferenz „Juli-Budget“!A1 .

Verweisen Sie auf Zellen in Arbeitsblättern

Im folgenden Beispiel beziehen wir uns auf eine Zelle mit einem berechneten Wert zwischen zwei Tabellen. Dadurch können wir den genauen Wert in zwei verschiedenen Tabellenkalkulationen verwenden, ohne Formeln neu zu schreiben oder Daten zu duplizieren.

1. Suchen Sie die Zelle, auf die Sie verweisen möchten, und notieren Sie sich das Arbeitsblatt. In unserem Beispiel möchten wir auf Zelle E14 im Arbeitsblatt „Menübestellungen“ verweisen .

2. Navigieren Sie zur gewünschten Tabelle. In unserem 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. Im Beispiel wählen wir Zelle C4 aus .

4. Geben Sie das Gleichheitszeichen (=) , den Tabellennamen gefolgt von einem Ausrufezeichen (!) und der Zellenadresse ein. Im Beispiel geben wir ='MenuOrder'!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 zu einem späteren Zeitpunkt umbenennen, wird der Zellbezug automatisch aktualisiert, um den neuen Arbeitsblattnamen widerzuspiegeln.

Wenn Sie einen falschen Tabellennamen eingeben, wird #REF! wird im Feld angezeigt. Im Beispiel haben wir den Namen des Arbeitsblatts verwechselt. Um einen Fehler zu bearbeiten, zu ignorieren oder zu untersuchen, klicken Sie auf die Schaltfläche „Fehler“ neben der Zelle und wählen Sie eine Option aus dem Menü.

Weitere Artikel finden Sie hier:

• Excel 2016 (Teil 3): So erstellen Sie neue und öffnen vorhandene Tabellenkalkulationen
• Excel 2016 (Teil 4): So speichern und teilen Sie Tabellenkalkulationen
• Excel 2016 (Teil 5): Grundlagen zu Zellen und Bereichen

Spaß haben!