Absolute Bezugsweise in Excel
Fragst du dich, was es mit den Dollarzeichen in deiner Excel-Formel auf sich hat? Lies weiter und erfahre mehr über absolute Bezüge in Excel!
Das Tolle an Excel ist, dass du in einer Zelle eine Formel schreiben kannst, die auf den Inhalt einer anderen Zelle verweist. Nehmen wir zum Beispiel an, Zelle B2 (die Zelle in Spalte B, Zeile 2) enthält den Text "Happy Birthday!". Du könntest an beliebiger Stelle in deinem Arbeitsblatt eine Formel schreiben, die auf Zelle B2 verweist, um die in dieser Zelle enthaltenen Informationen zu nutzen. Angenommen, du möchtest die Länge des Textes in Zelle B2 wissen, dann verwendest du die Funktion LÄNGE: =LÄNGE(B2), die den Wert 15 zurückgibt.
Das ist toll, denn du musst „Happy Birthday!“ nur einmal in deine Tabelle eingeben und kannst dann in anderen Formeln so oft darauf verweisen, wie du willst, ohne den Text an mehreren Stellen schreiben zu müssen. Anstatt „Happy Birthday!“ in jede Formel einzutippen, kannst du einfach B2 schreiben.
Absolute Verweise vs. relative Verweise in Excel
Im obigen Beispiel ist B2 das, was wir eine relative Referenz nennen (im Gegensatz zu einer absoluten Referenz), da sie kein Dollarzeichen ($) enthält. Eine relative Referenz ändert sich, wenn die Formel kopiert oder in eine neue Zelle gezogen wird. Nehmen wir zum Beispiel an, du hast eine Tabelle mit Städtenamen und möchtest die ersten drei Buchstaben jedes Städtenamens ermitteln.
| A | B | |
| 1 | NAME | DIE ERSTEN 3 BUCHSTABEN |
| 2 | Minneapolis | =LINKS(A2, 3) |
| 3 | Chicago | |
| 4 | Madison | |
| 5 | New York |
Genau wie Zelle B2 auf die Stadt in Zelle A2 verwiesen hat, ändert sich die Formel beim Kopieren in Zelle B3 so, dass sie auf die Stadt in Zelle A3 verweist, und so weiter. Wenn wir diese Formel in Zelle B100 kopieren würden, würde sich die Formel so ändern, dass sie auf Zelle A100 verweist. Das ist nützlich, wenn wir dieselbe Art von Formel auf mehrere Elemente in einer Liste anwenden wollen.
Ein absoluter Verweis hingegen ändert sich nicht, wenn die Formel kopiert oder an eine neue Stelle gezogen wird.
Dollarzeichen in einer Excel-Formel: Absolute Zellbezüge
Eine absolute Zellbezeichnung in Excel ist eine Zellbezeichnung, bei der vor dem Buchstaben und/oder der Zahl in der Zellbezeichnung ein Dollarzeichen ($) steht. Anstelle der relativen Zellbezeichnung A2 könnte man beispielsweise die absolute Zellbezeichnung $A$2 sehen, ebenso wie $A2 oder A$2.
Das Dollarzeichen in einer absoluten Referenz „sperrt“ oder „verankert“ den Teil der Referenz (die Zeile, die Spalte oder beides), vor dem es steht.
Die absolute Referenz $A2 bezieht sich immer auf Spalte A, aber die Zeile ändert sich je nachdem, wohin die Referenz kopiert wird.
Die absolute Referenz A$2 bezieht sich immer auf Zeile 2, aber die Spalte passt sich an, je nachdem, wohin die Referenz kopiert wird.
Die absolute Referenz $A$2 verweist immer auf Zelle A2, egal wohin in der Tabelle die Formel kopiert wird.
Wann man in Excel absolute Bezüge verwendet
Absolute Bezüge werden verwendet, wenn sich die Zelle, auf die du verweist, nicht je nach Position der Formel aktualisieren soll. Nehmen wir zum Beispiel an, wir haben ein Arbeitsblatt, in dem eine bestimmte Zelle einen Satz oder Prozentsatz enthält, den wir in unserer Formel verwenden möchten. In der Tabelle unten zeigen wir die Gesamtrechnung für mehrere Gäste in einem Restaurant. Um den richtigen Trinkgeldbetrag für jede Rechnung zu berechnen, multiplizieren wir den Rechnungsbetrag mit dem Trinkgeldprozentsatz, der sich in Zelle E1 befindet. Wir speichern den Trinkgeldprozentsatz in einer eigenen Zelle, anstatt in jeder Formel 0,18 einzutragen. So müssen wir, falls wir den Trinkgeldprozentsatz ändern wollen, diesen nur an einer Stelle (Zelle E1) anpassen und nicht jede einzelne Formel in der Liste.
| A | B | C | D | E | |
| 1 | BILL | HÖHE DES TIPPS | Tip-Prozentsatz: | 0.18 | |
| 2 | 24.17 | ? | |||
| 3 | 37.98 | ||||
| 4 | 14.01 | ||||
| 5 | 88.25 |
Warum macht Excel das? Weil wir die Formel eine Zeile nach unten kopieren, verschiebt Excel alle relativen Verweise ebenfalls eine Zeile nach unten - was bei A3 zwar richtig ist, bei E2 aber nicht. Wir wollen auf die Zelle E1 als Prozentsatz für das Trinkgeld verweisen - egal, wo die Formel steht.
Wir würden also =A2*$E$1 schreiben. So können wir die Formel in Spalte B nach unten kopieren, und sie bezieht sich immer auf die richtige Rechnung (da es sich um eine relative Referenz handelt) und immer auf den richtigen Trinkgeldprozentsatz (da es sich um eine absolute Referenz handelt), von dem es nur einen gibt, nämlich in Zelle E1.
Verschiedene Teile einer absoluten Zellreferenz sperren
Wir können eine absolute Referenz wie $A$1 schreiben, die immer auf Zelle A1 verweist, egal wo sich die Formel befindet. Wir sagen, dass sowohl die Zeile als auch die Spalte in dieser absoluten Referenz verankert oder fixiert sind, da jeweils ein Dollarzeichen davor steht. Wir können auch jeweils nur einen Teil fixieren.
Wenn wir $A1 eingeben, fixieren wir die Spalte; die Zeile ändert sich je nachdem, wohin die Formel kopiert wird, aber sie bezieht sich immer auf Spalte A.
Wir können auch A$1 schreiben. Dadurch wird die Zeile fixiert. Diese absolute Referenz bezieht sich immer auf Zeile 1, aber die Spalte passt sich je nach Einfügeposition der Formel an.
Nehmen wir zum Beispiel an, wir haben ein Unternehmen, in dem die Vertriebsmitarbeiter jeden Monat einen anderen Provisionssatz erhalten. Wir wollen den Umsatz jedes Vertriebsmitarbeiters für einen bestimmten Monat mit dem Provisionssatz dieses Monats multiplizieren, um die Höhe seiner Provision für diesen Monat zu ermitteln.
| A | B | C | D | E | F | G | H | I | |
| 1 | MONAT | PROVISION % | MIKE SALES | JULIA SALES | ALEX SALES | MIKE COMMISSION | JULIA COMMISSION | ALEX-AUFTRAG | |
| 2 | Jan | 0.22 | 1020 | 1850 | 1400 | ? | |||
| 3 | Feb | 0.25 | 1520 | 1440 | 1500 | ||||
| 4 | März | 0.26 | 1580 | 1630 | 1650 | ||||
| 5 | Apr | 0.28 | 1650 | 1000 | 1740 |
In Zelle G2 könnten wir =$B2*C2 eingeben, um Mikes Provision zu berechnen. Beachte, dass C2 eine relative Referenz ist und $B2 eine absolute Referenz, bei der nur die Spalte fixiert ist - die Zeile passt sich je nach Position der Formel an.
Wenn wir =$B2*C2 in Zelle G2 eingeben, multiplizieren wir Mikes Umsatz mit dem Provisionssatz für Januar, um Mikes Provision für Januar korrekt zu berechnen.
Wir können die Formel in Zelle H2 (eine Spalte rechts von G2) kopieren, um Julias Provision zu berechnen. Die relative Referenz ändert sich zu D2 (eine Spalte rechts von C2), um auf Julias Umsatz im Januar zu verweisen, aber $B2 verweist weiterhin auf den Provisionssatz für Januar, da die Spalte fixiert ist. So wird Julias Provision für Januar korrekt berechnet.
Wenn wir die ursprüngliche Formel von G2 nach G3 kopieren (eine Zeile nach unten), können wir Mikes Provision für Februar berechnen. Die Zelle C2 ändert sich zu C3 (eine Zeile nach unten), um auf Mikes Umsatz im Februar zu verweisen. Aus $B2 wird $B3, um den Provisionssatz für Februar zu berücksichtigen, da die Spalte fixiert ist, die Zeile jedoch nicht.
Wir können diese Formel in jede Zelle von G2 bis I5 (und darüber hinaus) kopieren und so immer die korrekte Provision des Verkäufers für den jeweiligen Monat berechnen.
Erfahre mehr über Excel
„Excel-Übungen“ bietet jede Menge Informationen und Übungsaufgaben, die dir helfen, neue Konzepte in Excel zu erlernen und anzuwenden. Probier Excel doch gleich mal aus!.
Verwendete Bilder
- https://excelexercises.com/logo2.png
- https://excelexercises.com/excel-functions/excelImages/logoGreenWhite.png