Ein Arbeitsblatt in Excel besteht aus Zellen. Auf diese Zellen kann verwiesen werden, indem der Zeilenwert und der Spaltenwert angegeben werden.
A1 würde sich beispielsweise auf die erste Zeile (angegeben als 1) und die erste Spalte (angegeben als A) beziehen. Ebenso wäre B3 die dritte Zeile und zweite Spalte.
Die Stärke von Excel liegt darin, dass Sie diese Zellbezüge beim Erstellen von Formeln in anderen Zellen verwenden können.
Jetzt gibt es drei Arten von Zellbezügen, die Sie in Excel verwenden können:
- Relative Zellreferenzen
- Absolute Zellreferenzen
- Gemischte Zellreferenzen
Wenn Sie diese verschiedenen Arten von Zellbezügen verstehen, können Sie mit Formeln arbeiten und Zeit sparen (insbesondere beim Kopieren und Einfügen von Formeln).
Was sind relative Zellbezüge in Excel?
Lassen Sie mich ein einfaches Beispiel nehmen, um das Konzept der relativen Zellbezüge in Excel zu erklären.
Angenommen, ich habe einen unten gezeigten Datensatz:
Um die Gesamtsumme für jeden Artikel zu berechnen, müssen wir den Preis jedes Artikels mit der Menge dieses Artikels multiplizieren.
Für das erste Element wäre die Formel in Zelle D2 B2* C2 (wie unten gezeigt):
Anstatt die Formel für alle Zellen nacheinander einzugeben, können Sie einfach Zelle D2 kopieren und in alle anderen Zellen einfügen (D3:D8). Wenn Sie dies tun, werden Sie feststellen, dass sich der Zellbezug automatisch an die entsprechende Zeile anpasst. Die Formel in Zelle D3 wird beispielsweise zu B3*C3 und die Formel in D4 wird zu B4*C4.
Diese Zellreferenzen, die sich beim Kopieren der Zelle anpassen, heißen relative Zellbezüge in Excel.
Wann sollten relative Zellbezüge in Excel verwendet werden?
Relative Zellbezüge sind nützlich, wenn Sie eine Formel für einen Zellbereich erstellen müssen und die Formel auf einen relativen Zellbezug verweisen muss.
In solchen Fällen können Sie die Formel für eine Zelle erstellen und in alle Zellen kopieren und einfügen.
Was sind absolute Zellbezüge in Excel?
Im Gegensatz zu relativen Zellbezügen ändern sich absolute Zellbezüge nicht, wenn Sie die Formel in andere Zellen kopieren.
Angenommen, Sie haben den unten gezeigten Datensatz, in dem Sie die Provision für den Gesamtumsatz jedes Artikels berechnen müssen.
Die Provision beträgt 20 % und ist in Zelle G1 aufgeführt.
Um den Provisionsbetrag für jeden Artikelverkauf zu erhalten, verwenden Sie die folgende Formel in Zelle E2 und kopieren Sie sie für alle Zellen:
=D2*$G$1
Beachten Sie, dass der Zellbezug mit der Provision zwei Dollarzeichen ($) enthält - $g$2.
Was macht das Dollarzeichen ($)?
Ein Dollarsymbol, wenn es vor der Zeilen- und Spaltennummer hinzugefügt wird, macht es absolut (d. h. verhindert, dass sich die Zeilen- und Spaltennummer ändert, wenn sie in andere Zellen kopiert wird).
Wenn ich beispielsweise im obigen Fall die Formel von Zelle E2 nach E3 kopiere, ändert sie sich von =D2*$G$1 in =D3*$G$1.
Beachten Sie, dass sich $G$1 nicht ändert, während sich D2 zu D3 ändert.
Da wir in G1 ein Dollarsymbol vor "G" und "1" hinzugefügt haben, würde sich der Zellbezug beim Kopieren nicht ändern.
Somit ist der Zellbezug absolut.
Wann sollten absolute Zellbezüge in Excel verwendet werden?
Absolute Zellbezüge sind nützlich, wenn Sie nicht möchten, dass sich der Zellbezug beim Kopieren von Formeln ändert. Dies kann der Fall sein, wenn Sie einen festen Wert haben, den Sie in der Formel verwenden müssen (z. B. Steuersatz, Provisionssatz, Anzahl der Monate usw.).
Sie können diesen Wert zwar auch in der Formel hart codieren (d. h. 20% anstelle von $ G $ 2 verwenden), aber wenn Sie ihn in einer Zelle haben und dann den Zellbezug verwenden, können Sie ihn zu einem zukünftigen Datum ändern.
Wenn sich beispielsweise Ihre Provisionsstruktur ändert und Sie jetzt 25 % statt 20 % auszahlen, können Sie einfach den Wert in Zelle G2 ändern, und alle Formeln würden automatisch aktualisiert.
Was sind gemischte Zellreferenzen in Excel?
Gemischte Zellbezüge sind etwas kniffliger als die absoluten und relativen Zellbezüge.
Es kann zwei Arten von gemischten Zellreferenzen geben:
- Die Zeile wird gesperrt, während sich die Spalte beim Kopieren der Formel ändert.
- Die Spalte ist gesperrt, während sich die Zeile beim Kopieren der Formel ändert.
Sehen wir uns an einem Beispiel an, wie es funktioniert.
Unten ist ein Datensatz, in dem Sie die drei Provisionsstufen basierend auf dem Prozentwert in den Zellen E2, F2 und G2 berechnen müssen.
Jetzt können Sie die Macht der gemischten Referenz nutzen, um all diese Provisionen mit nur einer Formel zu berechnen.
Geben Sie die folgende Formel in Zelle E4 ein und kopieren Sie sie für alle Zellen.
=$B4*$C4*E$2
Die obige Formel verwendet beide Arten von gemischten Zellbezügen (eine, bei der die Zeile gesperrt ist, und eine, bei der die Spalte gesperrt ist).
Lassen Sie uns jeden Zellbezug analysieren und verstehen, wie er funktioniert:
- $B4 (und $C4) - In dieser Referenz steht das Dollarzeichen direkt vor der Spaltennotation, aber nicht vor der Zeilennummer. Das bedeutet, dass beim Kopieren der Formel in die Zellen auf der rechten Seite der Bezug gleich bleibt, da die Spalte fixiert ist. Wenn Sie beispielsweise die Formel von E4 nach F4 kopieren, ändert sich dieser Verweis nicht. Wenn Sie es jedoch nach unten kopieren, ändert sich die Zeilennummer, da es nicht gesperrt ist.
- 2 € - In dieser Referenz steht das Dollarzeichen direkt vor der Zeilennummer und die Spaltennotation hat kein Dollarzeichen. Dies bedeutet, dass sich die Referenz nicht ändert, wenn Sie die Formel in die Zellen kopieren, da die Zeilennummer gesperrt ist. Wenn Sie die Formel jedoch nach rechts kopieren, ändert sich das Spaltenalphabet, da es nicht gesperrt ist.
Wie ändere ich die Referenz von relativ zu absolut (oder gemischt)?
Um den Bezug von relativ auf absolut zu ändern, müssen Sie das Dollarzeichen vor der Spaltennotation und der Zeilennummer hinzufügen.
A1 ist beispielsweise ein relativer Zellbezug und wird absolut, wenn Sie ihn zu $A$1 machen.
Wenn Sie nur wenige Verweise ändern möchten, können Sie diese Verweise möglicherweise leicht manuell ändern. Sie können also zur Bearbeitungsleiste gehen und die Formel bearbeiten (oder die Zelle auswählen, F2 drücken und dann ändern).
Ein schnellerer Weg, dies zu tun, ist jedoch die Verwendung der Tastenkombination - F4.
Wenn Sie einen Zellbezug auswählen (in der Bearbeitungsleiste oder in der Zelle im Bearbeitungsmodus) und F4 drücken, ändert sich der Bezug.
Angenommen, Sie haben die Referenz =A1 in einer Zelle.
Folgendes passiert, wenn Sie die Referenz auswählen und die Taste F4 drücken.
- Drücken Sie einmal die Taste F4: Der Zellbezug ändert sich von A1 in $A$1 (wird von „relativ“ zu „absolut“).
- Drücken Sie zweimal die Taste F4: Der Zellbezug ändert sich von A1 in A$1 (ändert sich in gemischte Referenz, wenn die Zeile gesperrt ist).
- Drücken Sie dreimal die Taste F4: Der Zellbezug ändert sich von A1 in $A1 (ändert sich zu gemischtem Bezug, wenn die Spalte gesperrt ist).
- Drücken Sie viermal die Taste F4: Der Zellbezug wird wieder A1.
