Absolute und relative Bezüge in Excel: Formeln kopieren, ohne auf falsche Daten zu verweisen

Was sich beim Kopieren einer Formel ändert

Eine Formel kann über ihre Koordinaten auf andere Zellen verweisen. Bei einem relativen Bezug wie A2 werden Zeile und Spalte entsprechend der Position angepasst, an die Sie die Formel kopieren. Wenn Sie beispielsweise in D4 =B4*C4 eingeben und diese Formel nach D5 kopieren, werden daraus die Bezüge =B5*C5. Das Muster der Formel bleibt erhalten – die Werte derselben Zeile werden multipliziert –, aber sie verweist nun auf die Zellen, die zur neuen Position gehören.

Der Unterschied ist wichtig, wenn eine Formel Daten kombiniert, die sich mitbewegen sollen, mit solchen, die am selben Ort bleiben müssen. Denken Sie vor dem Ausfüllen eines Bereichs an das Kopieren als Verschiebung: Um wie viele Zeilen und Spalten wird die Formel verschoben, und welche Bezüge sollten dieser Verschiebung folgen? Wenn Sie die Änderung nicht vorhersehen, kann eine Formel rechnen, ohne einen Fehler anzuzeigen, und trotzdem Daten aus einer anderen Zeile oder einen anderen Steuersatz verwenden.

• Relativ: A2 ändert sich beim Kopieren je nach Richtung und Abstand.
• Absolut: $A$2 fixiert Spalte und Zeile.
• Gemischt: $A2 fixiert die Spalte; A$2 fixiert die Zeile.

Beispiel: Einen festen Steuersatz auf mehrere Preise anwenden

Nehmen wir an, die Produkte stehen in Spalte A und ihre Grundpreise in Spalte B. In D1 speichern Sie einen für alle Produkte geltenden Steuersatz und möchten in C2 den Endpreis berechnen. Eine mögliche Formel lautet =B2*(1+$D$1). Wenn B2 den Wert 100 und D1 den Wert 10 % enthält, nimmt die Berechnung den Grundpreis und wendet den Faktor 1,10 an. Die konkrete Berechnung hängt vom Aufbau der Tabelle ab. Wichtig ist hier, dass sich der Preis in jeder Zeile ändert, die Zelle mit dem Steuersatz jedoch nicht.

Wenn Sie die Formel von C2 nach C3 kopieren, wird B2 zu B3, weil es ein relativer Bezug ist. $D$1 verweist dagegen weiterhin auf die Zelle mit dem Steuersatz, da die Dollarzeichen sowohl Spalte D als auch Zeile 1 fixieren. Lassen Sie die Dollarzeichen weg, kann sich der Bezug beim Kopieren nach unten verschieben, sodass die Formel nicht mehr den vorgesehenen Steuersatz verwendet. Prüfen Sie zunächst eine Zeile mit bekannten Werten und vergleichen Sie das erwartete mit dem tatsächlichen Ergebnis.

• Grundpreis: B2; gemeinsamer Steuersatz: D1; Ergebnis: C2.
• Beispielformel: =B2*(1+$D$1).
• Beim Kopieren nach unten muss sich B2 in B3 ändern, während $D$1 gleich bleibt.

Wann Sie relative, absolute oder gemischte Bezüge verwenden

Verwenden Sie einen relativen Bezug, wenn jede Zeile oder Spalte ihre eigenen Daten verwenden soll. Im Beispiel muss B2 beim Kopieren nach unten zu B3 werden, weil jedes Produkt einen eigenen Grundpreis hat. Diese Bezugsart eignet sich für wiederkehrende Berechnungen mit Daten, die in parallelen Zeilen oder Spalten angeordnet sind. Eine hilfreiche Frage lautet: Sollte beim Verschieben der Formel zur nächsten Position auch die nächste Datenposition verwendet werden?

Verwenden Sie einen absoluten Bezug, wenn die Formel immer dieselbe Zelle heranziehen soll, etwa einen allgemeinen Steuersatz in $D$1. Gemischte Bezüge eignen sich für Fälle, in denen nur eine Achse fixiert bleiben soll. $A2 hält Spalte A fest und lässt die Zeile variieren; A$2 hält Zeile 2 fest und lässt die Spalte variieren. Wird eine Formel beispielsweise um zwei Spalten nach rechts und zwei Zeilen nach unten kopiert, ändert sich A$1 in C$1, während $A1 zu $A3 wird.

• Fixieren Sie Zeile und Spalte mit $A$2, wenn beide unverändert bleiben sollen.
• Fixieren Sie nur die Spalte mit $A2, wenn Sie sich zeilenweise bewegen.
• Fixieren Sie nur die Zeile mit A$2, wenn Sie sich spaltenweise bewegen.

Einen Bereich ausfüllen und das Ergebnis vorhersehen

Bestimmen Sie vor dem Kopieren die Ausgangszelle und den Zielbereich. Prüfen Sie jeden Bezug in der Formel und ordnen Sie ihn als relativ, absolut oder gemischt ein. Überlegen Sie anschließend eine kleine Verschiebung: Welche Zeilennummer sollte sich ändern, wenn die Formel eine Zeile nach unten wandert? Welcher Spaltenbuchstabe sollte weiterzählen, wenn sie eine Spalte nach rechts verschoben wird? Mit dieser einfachen Vorhersage lässt sich eine falsche Fixierung erkennen, bevor Sie die Formel auf Dutzende Zellen ausweiten.

Kopieren Sie die Formel zunächst in eine benachbarte Zelle und prüfen Sie die daraus entstandene Formel – nicht nur den angezeigten Wert. Wenn die Bezüge auf die erwarteten Daten verweisen, können Sie den Bereich mit dem Ausfüllkästchen vervollständigen. Wenn Sie in eine andere Richtung kopieren, überprüfen Sie das Ergebnis erneut: Ein gemischter Bezug kann beim Kopieren nach unten richtig funktionieren, aber beim Verschieben nach rechts ungeeignet sein. Kopieren und Verschieben sind nicht dasselbe: Beim Verschieben einer Formel ändern sich ihre Bezüge nicht; beim Kopieren können sich relative Bezüge anpassen.

• Wählen Sie eine Testzelle in der Nähe der Ausgangszelle aus.
• Prüfen Sie die Bezüge nach dem Kopieren, bevor Sie den gesamten Bereich ausfüllen.
• Wiederholen Sie die Prüfung, wenn sich die Richtung des Zielbereichs ändert.

Häufige Fehler und wie Sie sie finden

Ein häufiger Fehler ist, die falsche Zelle zu fixieren. Im Preisbeispiel könnte ein absoluter Bezug auf den Grundpreis dafür sorgen, dass für alle Produkte der Wert einer einzigen Zeile wiederholt wird. Wird der Steuersatz dagegen nicht fixiert, kann sich sein Bezug beim Kopieren ändern. Überlegen Sie, welche Daten sich ändern und welche konstant bleiben sollen, bevor Sie Dollarzeichen hinzufügen. Verwenden Sie sie nicht aus Gewohnheit für alle Bezüge.

Auch eine Überschriftenzeile im ausgewählten Bereich, ein Formeleinstieg in einer anderen Zeile als den Daten oder das Kopieren in eine nicht vorgesehene Richtung können leicht zu Fehlern führen. Solche Fehler können leere Zellen, widersprüchliche Ergebnisse oder Formeln verursachen, die auf einen anderen Bereich verweisen. Wenn eine Zahl ungewöhnlich erscheint, prüfen Sie die Formel und ihre Bezüge, vergleichen Sie eine korrekte Zeile mit einer fraglichen und stellen Sie sicher, dass beide demselben Muster folgen. Korrigieren Sie nicht nur den angezeigten Wert, sondern auch den Bezug, der ihn verursacht.

• Beginnt die Formel in der ersten Datenzeile und nicht in der Überschrift?
• Verweist der absolute Bezug auf die richtige gemeinsame Zelle?
• Verweisen die relativen Bezüge weiterhin auf die aktuelle Zeile oder Spalte?

Prüfungen vor dem Teilen der Tabelle

Prüfen Sie sowohl das Muster der Formel als auch ihre Ergebnisse. Testen Sie eine Zeile und eine Spalte, um die Verschiebungen in beide Richtungen zu kontrollieren – auch wenn Sie die Formel normalerweise nur nach unten ausfüllen. Überprüfen Sie außerdem Extremfälle Ihrer Daten, etwa den höchsten und den niedrigsten Grundpreis, und vergewissern Sie sich, dass die Formel weiterhin den vorgesehenen Steuersatz verwendet. Wenn ein Ergebnis nicht plausibel ist, prüfen Sie die Bezüge, bevor Sie die Formel in weitere Zeilen kopieren.

Bewahren Sie eine Arbeitskopie auf, bevor Sie eine umfangreiche Änderung vornehmen, insbesondere wenn andere Personen bereits Formeln in der Tabelle verwenden. Eine kurze Checkliste hilft dabei, die Prüfung zu wiederholen, ohne sich auf das Gedächtnis verlassen zu müssen. Vergewissern Sie sich am Ende, dass der gesamte Bereich abgedeckt ist, keine Zeile eine abweichende Formel enthält und Überschriften nicht mit den Daten vermischt wurden. Wenn Sie die Tabelle erst nach diesen Prüfungen teilen, verringern Sie das Risiko von Fehlern, die auf den ersten Blick schwer zu erkennen sind.

• Testen Sie eine Zeile und eine Spalte, bevor Sie den gesamten Bereich ausfüllen.
• Prüfen Sie Extremwerte und eine mittlere Zeile.
• Stellen Sie sicher, dass keine abweichenden Formeln oder ausgelassenen Datenzeilen vorhanden sind.
• Speichern Sie eine Arbeitskopie, bevor Sie Formeln ersetzen oder erweitern.

Die Tabelle in Apification Cloud bearbeiten und verwalten

Die in diesem Leitfaden erläuterte Schreibweise für Zellbezüge ist eine allgemeine Praxis in Tabellenkalkulationen und hängt nicht von einer exklusiven Funktion einer bestimmten Plattform ab. Mit Apification können Sie Office-Dateien mit ONLYOFFICE erstellen und bearbeiten und in Cloud aufbewahren. So arbeiten Sie mit der Tabelle in dem Arbeitsbereich, in dem Sie Ihre Dateien und Projekte organisieren, ohne diese Verwaltungsfunktion mit den Berechnungsregeln von Excel zu verwechseln.

Für einen sorgfältigen Arbeitsablauf behalten Sie die Originaldatei oder eine Arbeitskopie, nehmen die Änderung vor und verwenden die Checkliste, bevor Sie die überarbeitete Version teilen. In Cloud können Sie außerdem den Verlauf eines Elements ansehen, frühere Versionen herunterladen und Inhalte wiederherstellen. Das bietet eine Möglichkeit, zu einer früheren Version zurückzukehren, wenn Sie einen Schritt rückgängig machen müssen. Es ersetzt jedoch weder die Prüfung der Formeln noch garantiert es, dass eine Formel korrekt aufgebaut ist. Prüfen Sie die aktualisierte Datei, bevor Sie sie weitergeben.

• Bearbeiten Sie Tabellen mit ONLYOFFICE in Apification Cloud.
• Prüfen Sie den Verlauf und stellen Sie bei Bedarf eine frühere Version wieder her.
• Kontrollieren Sie die Formeln, bevor Sie die Datei teilen.

Häufige Fragen