Zellbezüge in Excel: relativ, absolut ($) und gemischt
Jede Formel, die auf eine Zelle zeigt, wie =B2*C2, verwendet einen Zellbezug. Interessant wird es, wenn du diese Formel in andere Zellen kopierst: Excel passt die Bezüge an. Meist ist das genau das, was du willst, manchmal ist es aber genau das, was deine Tabelle kaputt macht.
Das $-Zeichen steuert das. Diese Anleitung zeigt die drei Arten von Bezügen mit kleinen Beispielen, was passiert, wenn du eine Formel nach unten oder zur Seite kopierst.
Relative Bezüge: sie wandern mit der Formel
Ein Bezug ohne $-Zeichen, wie B2, ist relativ. Excel merkt ihn sich als „die Zelle eine nach links, zwei nach links …“ und nicht als feste Adresse. Kopierst du die Formel eine Zeile nach unten, wandert jeder Bezug ebenfalls eine Zeile nach unten.
Beispiel: Zeilensummen, nach unten kopiert
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Artikel | Preis | Menge | Summe |
| 2 | Stift | 2 | 10 | |
| 3 | Notizbuch | 5 | 3 | 15 |
| 4 | Tasche | 30 | 1 |
In D2 als =B2*C2 geschrieben und nach unten kopiert. In Zeile 3 wurde daraus von selbst =B3*C3: 5 × 3 = 15.
Absolute Bezüge: $ fixiert sie
Manchmal muss jede Zeile dieselbe Zelle verwenden, etwa einen Steuersatz oder einen Wechselkurs. Setze ein $ vor den Spaltenbuchstaben und vor die Zeilennummer, wie bei $F$1, dann bleibt der Bezug fest, egal wohin du die Formel kopierst.
Beispiel: Jede Zeile verwendet denselben Steuersatz
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Artikel | Preis | Steuer | Steuersatz | 0.2 | |
| 2 | Stift | 2 | ||||
| 3 | Notizbuch | 5 | 1 | |||
| 4 | Tasche | 30 |
In C2 als =B2*$F$1 geschrieben und nach unten kopiert. B wanderte zu B3, aber $F$1 blieb stehen: 5 × 20 % = 1.
Was ohne das $ schiefgeht
Das ist der häufigste Kopierfehler in Excel. Die Formel ist in der ersten Zeile richtig, aber jede Zeile darunter zeigt unbemerkt auf die falsche Zelle.
Beispiel: Der Steuersatz-Bezug ist verrutscht
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Artikel | Preis | Steuer | Steuersatz | 0.2 | |
| 2 | Stift | 2 | ||||
| 3 | Notizbuch | 5 | 0 | |||
| 4 | Tasche | 30 |
In C2 als =B2*F1 ohne $ geschrieben und nach unten kopiert. In Zeile 3 zeigt er auf F2, das leer ist, also ist die Steuer 0. Es erscheint kein Fehler, deshalb wird dieser Fehler so leicht übersehen.
Gemischte Bezüge: nur Zeile oder nur Spalte sperren
$A1 sperrt die Spalte, lässt aber die Zeile wandern. A$1 sperrt die Zeile, lässt aber die Spalte wandern. Du brauchst das, wenn eine Formel sowohl nach unten als auch zur Seite kopiert wird, zum Beispiel in einer Tabelle, die eine Liste von Beträgen mit einer Zeile von Sätzen kombiniert.
Beispiel: Eine Formel für eine ganze Tabelle
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Satz → | 1 | 2 | 3 |
| 2 | 10 | |||
| 3 | 20 | |||
| 4 | 30 | 90 |
In B2 als =$A2*B$1 geschrieben und nach rechts und unten kopiert. $A verwendet immer die Beträge in Spalte A, $1 immer die Sätze in Zeile 1. In D4: 30 × 3 = 90.
Die Taste F4
Die $-Zeichen tippst du selten selbst. Klicke beim Bearbeiten einer Formel auf einen Bezug (oder direkt dahinter) und drücke F4. Jeder Druck wechselt durch die vier Varianten: F1 → $F$1 → F$1 → $F1 → F1. Auf dem Mac nutzt du Cmd+T.
Häufige Fehler
Das $ bei einer festen Zelle vergessen. Die erste Zeile ist richtig, die Zeilen darunter verwenden leere oder falsche Zellen.
Korrektur: Sperre einzelne Einstellungszellen (Sätze, Grenzen, Datumswerte) mit $:
=B2*$F$1.Alles sperren.
=$B$2*$C$2, nach unten kopiert, wiederholt in jeder Zeile das Ergebnis der ersten Zeile.Korrektur: Sperre nur, was fest bleiben muss, und lass den Rest wandern.
Suchtabellen, die verrutschen. Ein nach unten kopiertes SVERWEIS (Englisch: VLOOKUP) durchsucht A3:D6, dann A4:D7 … und gibt #N/A zurück.
Korrektur: Sperre die Tabelle:
=SVERWEIS(G2;$A$2:$D$5;3;FALSCH).
Häufig gestellte Fragen
Was bedeutet das $-Zeichen in Excel?
Es sperrt den Teil des Bezugs, vor dem es steht: $A sperrt die Spalte, $1 sperrt die Zeile, $A$1 sperrt beides. Gesperrte Teile ändern sich nicht, wenn die Formel kopiert wird.
Ändert das $ das Ergebnis der Formel?
Nein. =B2*F1 und =B2*$F$1 ergeben in der Zelle, in die du sie schreibst, dasselbe Ergebnis. Das $ ist nur wichtig, wenn du die Formel kopierst oder ausfüllst.
Ändern sich Bezüge, wenn ich eine Formel verschiebe (ausschneide)?
Nein. Ausschneiden und Einfügen behält die Bezüge genau bei. Nur Kopieren passt sie an.
Verwandte Funktionen
- SUMSUM addiert Zahlen, Zellen und ganze Bereiche in einer einzigen Formel.
- VLOOKUPVLOOKUP sucht einen Wert in der ersten Spalte einer Tabelle und gibt einen Wert aus derselben Zeile in einer anderen Spalte zurück.
- XLOOKUPXLOOKUP sucht in einer Spalte oder Zeile und gibt den passenden Wert aus einer anderen zurück, in beliebiger Richtung, mit einer eingebauten Meldung für „nicht gefunden“.