Häufige Kopierfehler beheben

Die meisten Fehler beim Kopieren haben dieselbe Hauptursache: Ein Bezug wurde verschoben, obwohl er fest bleiben sollte, oder er blieb fest, obwohl er sich verschieben sollte.

Da sich relative Bezüge beim Ausfüllen verschieben, wandert eine Formel, die auf eine einzelne gemeinsame Zelle wie einen Steuersatz oder eine Überschrift verweist, in jeder neuen Zeile von dieser Zelle weg. Das Ergebnis sind falsche Zahlen oder Fehler.

In dieser Lektion lernen Sie die häufigsten Fehlerfälle und deren jeweilige Behebung kennen.

Angenommen, E1 enthält den Steuersatz 0,08 und Sie schreiben =C2*E1 in D2 und füllen die Formel anschließend nach unten aus. Beobachten Sie, was passiert:

• D2 = =C2*E1 (korrekt)
• D3 = =C3*E2 (E2 ist leer!)
• D4 = =C4*E3 (ebenfalls leer)

Der E-Bezug ist vom Steuersatz weggerutscht. Die Zeilen unterhalb von Zeile 2 multiplizieren mit null, sodass überall 0 erscheint.

Fixieren Sie die gemeinsame Zelle, damit sie nicht verrutschen kann. Ändern Sie die Formel vor dem Ausfüllen in =C2*$E$1.

Die Dollarzeichen fixieren sowohl die Spalte E als auch die Zeile 1. Wenn Sie die Formel nun nach unten ausfüllen:

• D3 = =C3*$E$1
• D4 = =C4*$E$1

multipliziert jede Zeile mit dem richtigen Steuersatz. C verschiebt sich weiterhin; $E$1 bleibt an seinem Platz.

Wenn Sie eine Formel an eine Stelle kopieren, an der ihre Bezüge über den Rand des Tabellenblatts hinausweisen würden, erhalten Sie #REF!.

Eine Formel wie =A2-B2, die in Spalte A steht und nach links gezogen wird, hat beispielsweise keinen gültigen Zielbezug mehr und gibt daher #REF! zurück. Der Fehler bedeutet: „Ein Bezug ist nicht mehr vorhanden.“

Die Lösung besteht darin, die Formel an eine Stelle zu kopieren, an der ihre Bezüge im Tabellenbereich bleiben, oder sie so umzuschreiben, dass sie auf gültige Zellen verweist.

Eine kopierte Divisionsformel kann auf leere Zellen treffen und durch null teilen. Dann wird #DIV/0! angezeigt.

Angenommen, =B2/C2 wird in einer Spalte nach unten ausgefüllt, in der einige C-Zellen leer sind. Die Division durch eine leere Zelle löst den Fehler aus.

Umschließen Sie die Formel mit IFERROR, um stattdessen eine unauffällige leere Zelle oder eine Null anzuzeigen: =IFERROR(B2/C2,0). Die Division wird weiterhin durchgeführt, wo dies möglich ist, und Fehler werden dort ausgeblendet, wo sie nicht möglich ist.

Ein klassischer Fehler besteht darin, das Ausfüllkästchen zu weit zu ziehen und dadurch benötigte Zellen zu überschreiben oder eine Formel über eingegebene Werte zu setzen.

Die sofortige Lösung ist Ctrl+Z (Cmd+Z), um den Vorgang rückgängig zu machen. Tabellenkalkulationen verfügen über einen umfangreichen Rückgängig-Verlauf, sodass Sie mehrere versehentliche Ausfüllvorgänge zurücknehmen können.

Führen Sie das Ausfüllen nach dem Rückgängigmachen vorsichtiger erneut durch und beenden Sie das Ziehen in der richtigen letzten Zeile.

Beim Ziehen des Ausfüllkästchens wird zusammen mit der Formel auch die Formatierung kopiert. Dadurch können Rahmen, Farben oder Währungsformate auf Bereiche übertragen werden, in denen Sie sie nicht benötigen.

Um nur die Formel zu kopieren, verwenden Sie nach dem Ziehen die Schaltfläche Auto Fill Options und wählen Sie Fill Without Formatting. Verwenden Sie beim Einfügen Paste Special und wählen Sie nur Formulas aus, damit das Erscheinungsbild des Zielbereichs unverändert bleibt.

Wenn etwas falsch aussieht, ist Ihr erster Schritt immer derselbe: Klicken Sie auf die fehlerhafte Zelle und lesen Sie die Formelleiste.

Prüfen Sie, ob jeder Bezug auf die gewünschte Stelle verweist. Wenn eine Konstante, die $E$1 sein sollte, als E5 angezeigt wird, fehlt die Fixierung. Ein Bezug mit dem Inhalt #REF! weist auf ein gelöschtes Ziel hin. Die Formelleiste macht aus einer rätselhaften falschen Zahl eine offensichtlich zu behebende Ursache.

Um ein ganzes Tabellenblatt auf einmal zu prüfen, aktivieren Sie die Ansicht Show Formulas. Drücken Sie in Excel Ctrl+` (die Taste für den Gravis); wählen Sie in Google Sheets View und anschließend Show formulas.

Jede Zelle zeigt nun ihre Formel statt ihres Ergebnisses an. So können Sie eine Spalte prüfen und sofort die eine Zeile erkennen, deren Bezüge verrutscht sind. Drücken Sie die Tastenkombination erneut, um zur normalen Ansicht zurückzukehren.

Am besten vermeiden Sie den Fehler von vornherein. Prüfen Sie vor dem Ziehen jeden Bezug und entscheiden Sie: Soll er sich mit jeder Zeile verschieben oder fest bleiben?

Fügen Sie Dollarzeichen zu allem hinzu, was fest bleiben muss, etwa zu einem einzelnen Steuersatz, einer Summenüberschrift oder einer Nachschlagetabelle. Eine schnelle Möglichkeit zum Hinzufügen ist, in einen Bezug zu klicken und F4 zu drücken. Dadurch wechseln Sie zwischen den Fixierungsoptionen. Erst fixieren, dann ausfüllen.

Angenommen, in einer Provisionsspalte steht in jeder Zeile außer der ersten 0. Sie klicken auf Zeile 5 und sehen in der Formelleiste =C5*E4, obwohl der Provisionssatz in E1 steht.

Die Ursache ist eindeutig: E1 wurde nie fixiert und ist deshalb bis E4 verrutscht. Korrigieren Sie die ursprüngliche Formel in =C2*$E$1 und füllen Sie die Spalte anschließend erneut aus. Nun wird jede Zeile korrekt berechnet. Ein einziges Dollarzeichen hat die gesamte Spalte repariert.

Testen Sie Ihre Fähigkeiten bei der Fehlerbehebung von Kopierfehlern.

Sie können jetzt Formeln reparieren, die beim Kopieren Fehler verursachen:

• Eine verrutschende Konstante wird durch Dollarzeichen fixiert, zum Beispiel mit $E$1.
• #REF! bedeutet, dass ein Bezug über den Tabellenbereich hinausgerutscht ist; #DIV/0! bedeutet, dass durch eine leere Zelle geteilt wurde, was sich mit IFERROR abfangen lässt.
• Mit Ctrl+Z machen Sie ein fehlerhaftes Ausfüllen rückgängig; Fill Without Formatting hält die Formatierung sauber.
• Ermitteln Sie die Ursache mit der Formelleiste oder der Ansicht Show Formulas, und fixieren Sie Bezüge vor dem Ausfüllen.

Sie haben das Thema „Formeln richtig kopieren“ abgeschlossen.