Sie ziehen eine Formel nach unten, und plötzlich stimmen die Ergebnisse nicht mehr. Meist liegt es am Bezug: Excel passt Zelladressen beim Kopieren an, außer Sie halten sie mit einem Dollarzeichen fest. Wer das Prinzip einmal verstanden hat, baut Formeln, die sich beliebig kopieren lassen.
Die Kurzlösung
| Bezug | Beim Kopieren | Typischer Einsatz |
|---|---|---|
A1 | Spalte und Zeile passen sich an | Rechnen mit Werten derselben Zeile |
$A$1 | bleibt fest | fester Wert wie Steuersatz oder Rabatt |
$A1 | Spalte fest, Zeile passt sich an | Wert aus einer bestimmten Spalte, Formel wird nach rechts kopiert |
A$1 | Zeile fest, Spalte passt sich an | Wert aus einer Kopfzeile, Formel wird nach unten kopiert |
Mit der Taste F4 wechseln Sie beim Bearbeiten einer Formel zwischen den vier Schreibweisen. Setzen Sie den Cursor in den Bezug und drücken Sie F4 mehrmals: $A$1, A$1, $A1, A1.
Relativ: der Normalfall
Steht in F10 die Formel =B10*C10 und Sie kopieren sie nach F11, wird daraus =B11*C11. Excel merkt sich nicht die Adresse, sondern den Abstand: „zwei Zellen links mal drei Zellen links“. Genau das ist meistens gewollt, etwa wenn jede Zeile ihren eigenen Preis mit ihrem eigenen Rabattsatz multipliziert.
Absolut: ein fester Wert für alle Zeilen
Steht ein gemeinsamer Rabattsatz in C6, muss jede Zeile genau diese Zelle verwenden. Mit =B10*C6 würde daraus beim Kopieren =B11*C7, und C7 ist leer. Richtig ist:
=B10*$C$6
Beim Kopieren passt Excel nur B10 an, $C$6 bleibt stehen.
Gemischt: die Einmaleins-Tabelle
Gemischte Bezüge braucht man, wenn eine Formel nach rechts und nach unten kopiert wird. Stehen die Zahlen 1 bis 3 in A2:A4 und in B1:D1, schreiben Sie in B2:
=$A2*B$1
und kopieren die Formel nach B2:D4. $A2 hält die Spalte A fest, die Zeile wandert mit. B$1 hält die Zeile 1 fest, die Spalte wandert mit. In D4 steht danach =$A4*D$1, das Ergebnis ist 9.
| B | C | D | |
|---|---|---|---|
| 2 | 1 | 2 | 3 |
| 3 | 2 | 4 | 6 |
| 4 | 3 | 6 | 9 |
Kopieren, Ausschneiden, Zeilen einfügen
Das Dollarzeichen wirkt nur beim Kopieren. In anderen Fällen verhält sich Excel anders, als viele erwarten:
| Aktion | Was mit Bezügen passiert |
|---|---|
| Formel kopieren | relative Bezüge passen sich an, absolute bleiben |
| Formel ausschneiden und einfügen | alle Bezüge bleiben gleich, auch relative. =H1*2 bleibt nach dem Verschieben =H1*2 |
| Zeile oder Spalte einfügen | alle Bezüge, auch absolute, wandern mit den Zellen mit. Aus =L1 wird =L2 |
| Bezug als Text in INDIREKT | wird nie angepasst. =INDIREKT("L2") zeigt nach dem Einfügen einer Zeile auf die falsche Zelle |
Bezüge in Tabellen
In einer als Tabelle formatierten Liste (Strg+T) schreibt Excel statt Zelladressen strukturierte Verweise wie =[@Preis]*[@Menge]. Diese passen sich beim Kopieren und Erweitern der Tabelle von selbst richtig an. Dollarzeichen sind dort meist überflüssig. Einen festen Wert außerhalb der Tabelle sprechen Sie weiterhin absolut an, etwa =[@Preis]*$C$2, oder Sie geben der Zelle einen Namen wie Rabattsatz.
Viele Formeln auf einmal umstellen
Sollen in einem großen Bereich alle Bezüge absolut werden, hilft ein kleines Makro. Application.ConvertFormula wandelt die Bezüge einer Formel um:
Option Explicit
' Setzt in allen markierten Formeln alle Bezüge absolut ($A$1)
Public Sub BezuegeAbsolut()
Dim zelle As Range
For Each zelle In Selection.Cells
If zelle.HasFormula Then
zelle.Formula = Application.ConvertFormula(zelle.Formula, xlA1, xlA1, xlAbsolute)
End If
Next zelle
End Sub
Statt xlAbsolute setzt xlRelative alle Bezüge relativ, xlAbsRowRelColumn nur die Zeilen und xlRelRowAbsColumn nur die Spalten fest. Aus =A1+B$2+$C3 wird mit xlAbsolute zum Beispiel =$A$1+$B$2+$C$3.
Typische Fehler
- Ergebnis 0 nach dem Herunterziehen: Der feste Wert wurde relativ angesprochen und rutscht mit nach unten in leere Zellen. Setzen Sie das Dollarzeichen.
- Dollarzeichen an der falschen Stelle:
$C6stattC$6hält beim Kopieren nach unten nichts fest. Merkregel: Das Dollarzeichen steht vor dem Teil, der fest bleiben soll. - F4 ohne Wirkung: Auf manchen Laptops ist F4 mit einer Sonderfunktion belegt. Drücken Sie dann Fn+F4.
- Verschoben statt kopiert: Nach Ausschneiden und Einfügen zeigen die Bezüge weiter auf die alten Zellen. Das ist kein Fehler, sondern gewollt.
Passende Beiträge
Lernen oder machen lassen?
Excel-Formeln, die im Alltag sitzen.
In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.
