In Excel können Zellbezüge relativ, absolut oder gemischt sein oder die speziellen Operatoren $, # oder @ verwenden.

FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Q: Was ist der Unterschied zwischen A1, $A1, A$1 und $A$1?

A: Alle vier beziehen sich auf die Zelle A1, verhalten sich jedoch beim Kopieren der Formel unterschiedlich. Die Zahl A1 ist relativ. Das bedeutet, dass sich sowohl die Spalte als auch die Zeile beim Kopieren mitverschieben. Die Zelle $A$1 verweist immer auf A1, unabhängig davon, wo Sie sie einfügen. Die $A1-Fixierung hält die Spalte fest, die Zeile kann sich jedoch verschieben. Das Symbol A$1 fixiert die Zeile, lässt aber die Spalte verschieben.

Q: Wann muss ich das $-Zeichen in einer Formel verwenden?

A: Wenn Sie eine Formel kopieren, sollten Sie für dieselbe Information immer dieselbe Zelle verwenden. Dies gilt beispielsweise für einen Steuersatz, einen Rabattprozentsatz oder eine feste Kopfzeile. Ohne das $-Zeichen ändert sich jeder Teil der Formel beim Kopieren. Dies ist in der Regel bei Zeilenberechnungen erwünscht, jedoch falsch, wenn eine Referenz unverändert bleiben soll.

Q: Was bedeutet das Symbol # hinter einer Zellenadresse, wie beispielsweise A2#?

A: Das # ist der Operator für den Ausdehnungsbereich, der mit dynamischen Arrays in Excel 365 eingeführt wurde. Die Funktion A2# bezeichnet alle Zellen, auf die sich die Formel in A2 erstreckt – unabhängig davon, wie viele das sind. Wenn eine „EINDEUTIG“-Funktion in Zelle A2 acht Ergebnisse liefert, bezieht sich A2# auf die Zellen A2 bis A9. Fügen Sie der Quelle eine Zeile hinzu, und A2# wird automatisch erweitert, um diese einzubeziehen.

Q: Was bedeutet das @-Zeichen in einer Formel wie =[@Hours]*[@Rate]?

A: In einer Excel-Tabelle steht [@Hours] für den Wert „Hours” in derselben Zeile wie diese Formel. Das Symbol @ ist der implizite Schnittpunktoperator. Er weist Excel an, den Wert an der Schnittstelle dieser Zeile mit der benannten Spalte zu verwenden statt den Wert der gesamten Spalte. Aus diesem Grund sind Tabellenformeln selbsterklärend und immer korrekt, unabhängig davon, an welcher Stelle in der Zeile sie stehen.

Q: Wie kann ich schnell zwischen den Referenztypen wechseln, ohne den Text erneut eingeben zu müssen?

A: Drücken Sie die Taste „F4“, während sich der Cursor in einer Zellreferenz in der Formelleiste befindet. Mit jedem Tastendruck durchlaufen Sie die vier Typen. Zunächst gelangen Sie zu Zelle A1. Drücken Sie dann erneut „F4“, um zu $A$1 zu gelangen. Dann ändern Sie dies zu „A$1”. Schließlich ändern Sie es zu „$A1“. Dies funktioniert bei jeder Referenz und ist der schnellste Weg, um eine Zeile, eine Spalte oder beides zu fixieren.

Zellbezüge bilden die Grundlage jeder Formel in Excel. Um beim Kopieren und Einfügen die richtigen Ergebnisse zu erhalten, ist es entscheidend zu wissen, wie sie funktionieren und wann man sie jeweils einsetzt.

In diesem Artikel werden alle vier Bezugstypen, die Tastenkombination F4 sowie die drei speziellen Operatoren behandelt, die in modernen Excel-Formeln vorkommen. Verwenden Sie $ zum Fixieren, # für Ausdehnungsbereiche und @ für Bezüge auf Zeilenebene innerhalb von Tabellen.

Was ist eine Zellreferenz?

Wenn Sie in einer Formel =B2 eingeben, speichert Excel nicht den Text „B2”. Vielmehr speichert es eine relative Anweisung: Es gibt keine Spalten links davon und keine Zeilen oberhalb davon. Wenn Sie diese Formel eine Zeile nach unten kopieren, folgt Excel derselben Anweisung und verweist nun auf B3. Dies ist als relative Referenzierung bekannt und stellt die Standardeinstellung dar.

Das Zeichen $ ändert dieses Verhalten. It weist Excel an, keine relative Position mehr zu berechnen, sondern immer zu der Spalte oder Zeile zu springen, auf die Sie gerade zeigen.

Es gibt vier Arten von Verweisen:

Relativ — A1

Dies ist die Standardeinstellung. Wenn Sie die Formel kopieren, ändern sich der Spaltenbuchstabe und die Zeilennummer.

=B2*C2

Kopieren Sie diese eine Zeile einfach nach unten: =B3*C3. Erstellen Sie eine Kopie davon in einer anderen Spalte rechts daneben: =C2*D2. Beide Teile verschieben sich gemeinsam.

Verwenden Sie für alle Berechnungen, die bei jedem Vorkommen in der Tabelle auf dieselbe Weise durchgeführt werden sollen, relative Bezüge – beispielsweise beim Multiplizieren von Stunden mit einem Stundensatz, beim Addieren zweier benachbarter Zellen oder bei allen Fällen, in denen das Muster „gleiche Struktur, unterschiedliche Daten“ gilt.

Absolut — $A$1

Sowohl die Spalte als auch die Zeile sind mit einem $-Zeichen fixiert. Der Verweis bezieht sich immer auf dieselbe Zelle, unabhängig davon, wohin die Formel kopiert wird.

=B2*$D$1

Die Referenz $D$1 bezieht sich immer auf die Zelle D1, unabhängig davon, wie viele Zeilen Sie die Formel nach unten oder Spalten Sie sie nach rechts kopieren. Nur der Bezug B2 verschiebt sich.

Verwenden Sie absolute Bezüge für Konstanten, die für jede Zeile gelten sollen. Sie können beispielsweise einen Mehrwertsteuersatz, einen Wechselkurs, einen Rabattprozentsatz oder einen Zielwert aus einer Konfigurationszelle in D1 eingeben. Wenn Sie den Wert später ändern müssen, können Sie dies in einer Zelle tun. Alle Formeln, die $D$1 verwenden, werden dann automatisch aktualisiert.

Gemischt: $A1 (Spalte fixiert, Zeile frei).

Das Symbol $ wird nur vor den Spaltenbuchstaben gesetzt. Wenn die Formel nach unten kopiert wird, bleibt die Spalte fixiert und die Zeile verschiebt sich.

=B2*$D2

Wenn Sie diese Formel nach unten kopieren, bleibt Spalte D fixiert, aber D2 wird zu D3, D4 usw. Wenn Sie die Formel nach rechts kopieren, bleibt Spalte D fixiert, während sich B nach C, D usw. verschiebt.

Zu den klassischen Anwendungsfällen gehören zweidimensionale Multiplikationstabellen, bei denen sich die Sätze in einer fixierten Spalte befinden und die Mengen je nach Zeile variieren, sowie Nachschlagefunktionen, bei denen sich die Nachschlagsspalte niemals verschieben darf, während jede Zeile einen anderen Suchwert übernimmt.

Gemischt: A$1 (Zeile fixiert, Spalte frei).

Das $ wird nur vor der Zeilennummer gesetzt. Die Zeile bleibt fixiert, während sich die Spalte verschiebt, wenn die Formel nach rechts kopiert wird.

=B2*B$1

Nach rechts kopieren: Zeile 1 bleibt fixiert, aber B verschiebt sich nach C, D usw. Nach unten kopieren: Spalte B verschiebt sich, Zeile 1 bleibt.

Verwenden Sie diese Funktion, wenn Zeile 1 eine Reihe von Überschriften oder Sätzen enthält, die von jeder Spalte einzeln übernommen werden sollen. Ein Beispiel sind monatliche Budgetziele in Zeile 1, unter denen die Ist-Werte eingetragen werden.

Die F4-Tastenkombination

Sie müssen das Zeichen $ nie manuell eingeben. Setzen Sie den Cursor während der Bearbeitung einer Formel in eine beliebige Zellreferenz und drücken Sie dann wiederholt die F4-Taste, um zwischen den folgenden Optionen zu wechseln:

A1 → $A$1 → A$1 → $A1 → A1.

Einmal drücken sperrt alles. Zweimal drücken sperrt nur die Zeile. Dreimal drücken sperrt nur die Spalte. Viermal drücken hebt alle Sperren auf. Dies funktioniert bei jeder Referenz an beliebiger Stelle in einer Formel.

Die drei Sonderzeichen

Hinter dem Dollarzeichen stehen in Excel drei Sonderzeichen, die dem Computer mitteilen, wie eine Referenz zu interpretieren ist. Jedes davon hat seinen eigenen Zweck.

$ — Diesen Teil fixieren.

Dies wurde bereits im Zusammenhang mit absoluten und gemischten Bezügen erläutert. In Formeln bedeutet das Zeichen $: Ändern Sie diesen Teil der Adresse nicht, wenn die Formel verschoben oder kopiert wird. Dies funktioniert für Spalten, Zeilen oder beides.

# — Der Operator für den Überlaufbereich (Spill)

Diese Funktion wurde erstmals mit dynamischen Arrays in Excel 365 und Excel 2021 eingeführt. Wenn eine Formel wie EINDEUTIG, FILTER oder SORTIEREN mehr als ein Ergebnis zurückgibt, werden diese Ergebnisse automatisch in die benachbarten Zellen übertragen. Mit dem Operator # können Sie den gesamten Überlaufbereich als eine Einheit verwenden.

=EINDEUTIG(B2:B100)

Wenn sich die Formel in Zelle A2 befindet und zwölf eindeutige Werte liefert, werden diese in die Zellen A2 bis A13 eingetragen. Um in einer anderen Formel auf alle zwölf Werte zu verweisen, verwenden Sie:

=SORTIEREN(A2#)

Die Abkürzung A2# bedeutet „alle Zellen, die die Spill-Formel in A2 derzeit belegt“. Wenn sich die Quelldaten ändern und die Funktion EINDEUTIG nun 15 Werte zurückgibt, umfasst ' A2#' automatisch A2:A16. Sie müssen den Verweis nicht manuell aktualisieren.

Dies ist die richtige Vorgehensweise bei der Verwendung dynamischer Array-Formeln. Geben Sie keine festen Bereiche wie A2:A13 ein, wenn Sie Daten in eine zweite dynamische Array-Formel eingeben. Dies führt zu Problemen, wenn sich die Größe der Eingabe ändert.

# erfordert Excel 365 oder Excel 2021, in früheren Versionen ist diese Funktion nicht vorhanden.

@ — implizite Schnittmenge/diese Zeile

Der Operator @ hat zwei verwandte, aber unterschiedliche Verwendungszwecke.

Innerhalb einer Excel-Tabelle bedeutet er „strukturierte Verweise.

Innerhalb einer Tabelle bedeutet @ „in derselben Zeile wie diese Formel“. Wenn Sie eine Formel in eine Tabellenspalte eingeben, verwendet Excel automatisch strukturierte Verweise:

=[@Hours]*[@Rate]

Dadurch werden die Werte für „Hours” und „Rate” aus derselben Zeile addiert. Es spielt keine Rolle, in welcher Zeile sich die Formel befindet – [@Hours] bedeutet immer „der Wert von Stunden in dieser Zeile“. Wenn Sie die Formel in eine beliebige Zeile kopieren, funktioniert sie ohne Anpassung.

Wenn Sie das @-Symbol nicht verwenden, bezieht sich das Feld [Stunden] auf die gesamte Spalte „Stunden“, die alle Zeilen umfasst. Das @-Symbol grenzt den Bereich auf den Schnittpunkt zwischen der Spalte und der aktuellen Zeile ein.

Außerhalb einer Tabelle — implizite Schnittmenge

In Excel 365 kann @ auch in regulären Formeln außerhalb von Tabellen vorkommen. Wenn eine Formel mehrere Werte zurückgeben könnte (weil sie sich auf einen Bereich bezieht, der überlaufen könnte), zwingt @ Excel dazu, nur den einzelnen Wert zurückzugeben, der sich mit der aktuellen Zeile schneidet:

=@B:B

Dies gibt lediglich den Wert aus Spalte B in derselben Zeile wie die Formel zurück, nicht die gesamte Spalte. Dies wird bei Formeln aus älteren Excel-Versionen deutlich, bei denen Excel automatisch ein @-Symbol hinzufügt, um die Funktionsweise der Formel beizubehalten.

Praktische Beispiele zur Kombination von Referenztypen

Gehaltstabelle mit festem Stundensatz

Der Stundensatz ist in Zelle D1 gespeichert. Die Zahlen in der zweiten Spalte (B) geben die Stunden an. Das Gehalt für jeden Mitarbeiter soll in Spalte C berechnet werden.

=B2*$D$1

Kopieren Sie diese Formel nach unten in Spalte C, sodass der Wert von B2 dann in B3, B4 usw. übernommen wird. Der Wert in Zelle D1 bleibt unverändert. Wenn Sie den Satz in D1 ändern, werden alle Lohnberechnungen sofort aktualisiert.

Mehrwertsteuerberechnung über mehrere Produktspalten hinweg

Die Produkte befinden sich in den Spalten B, C und D. Der Mehrwertsteuersatz steht in Zeile 1. Um den Preis zu berechnen, multiplizieren Sie den Produktpreis in der entsprechenden Spalte mit dem Mehrwertsteuersatz in Zeile 1.

=B3*B$1

Achtem Sie darauf, alles in die richtigen Spalten zu kopieren. B wird in C, D usw. verschoben. Das Symbol $1 weist die Formel an, auf Zeile 1 zu verweisen – unabhängig davon, in welcher Spalte sie sich befindet.

Dynamische Array-Formeln mit # verketten

Eine EINDEUTIG-Formel in A2 erzeugt eine Liste mit Mitarbeiternamen. Eine zweite Formel sollte diese Liste sortieren.

=SORTIEREN(A2#)

Eine dritte Formel sollte alle Mitarbeiter der Personalabteilung herausfiltern.

=FILTER(A2#; B2#="HR")

Die Formeln passen sich automatisch an, wenn das Ergebnis von EINDEUTIG größer oder kleiner wird.

Tabellenformel mit @

Es gibt eine Tabelle namens „Stundenaufstellung“ mit den drei Spalten „Mitarbeiter“, „Stunden“ und „Tarif“.

=[@Hours]*[@Rate]

Diese Formel wird automatisch in jeder Zeile der berechneten Spalte angezeigt. Sie ist selbsterklärend, funktioniert stets einwandfrei und bleibt auch dann fehlerfrei, wenn Zeilen hinzugefügt oder neu angeordnet werden.

Häufige Fehler bei Verweisen

Der häufigste Fehler beim Kopieren einer Formel mit einer konstanten Referenz ist das Vergessen des $-Zeichens.
Die Formel =B2*D1 funktioniert in der ersten Zeile. Wenn Sie sie nach unten kopieren, wird daraus zunächst =B3*D2 und dann =B4*D3, wobei jedes Mal auf eine andere Zelle verwiesen wird. Die korrekte Formel lautet: =B2*$D$1.

Wenn Sie einen fest codierten Bereich anstelle einer Spill-Referenz verwenden.
Die Formel =SORTIEREN(A2:A20) bricht stillschweigend ab, wenn die darüber liegende EINDEUTIG-Formel mehr oder weniger als 19 Ergebnisse liefert. Verwenden Sie stattdessen die Formel =SORTIEREN(A2#).

Verwendung der vollständigen Spaltenreferenz [Stunden] anstelle von [@Stunden] in einer Tabelle: Die Formel [Stunden][Satz] multipliziert alle Zellen einer Spalte miteinander und liefert ein Array-Ergebnis. Dies ist in einer Tabellenzeile fast nie das gewünschte Ergebnis. Verwenden Sie stattdessen die Formel `=[@Stunden][@Satz]`, um die Werte in der aktuellen Zeile zu multiplizieren.

Zusammenfassung

Kurzanleitung zu Referenztypen:

Drücken Sie die Taste F4, um zwischen den vier $-Optionen zu wechseln. Wenn Sie dynamische Array-Formeln verwenden, verwenden Sie das Symbol #. Verwenden Sie außerdem in jeder Tabellenformel das Symbol [@Column].

Lesen Sie auch: So verwenden Sie Excel-Tabellen: Warum jeder Datensatz im Tabellenformat vorliegen sollte für eine vollständige Anleitung zu strukturierten Verweisen und dazu, wie Tabellen [@Column]-Formeln als Standard festlegen.

Siehe auch: Dynamische Arrays in Excel – wie FILTER, SORTIEREN, EINDEUTIG und SEQUENZ zusammenwirken für praktische Anwendungen des Spill-Range-Operators #.