Erfahren Sie, wie Sie Formeln in mehrere Zellen, eine Spalte nach unten, in nicht benachbarte Zellen kopieren, Formeln mit absoluten oder gemischten Zellbezügen kopieren usw.
Das Kopieren von Formeln ist eine der häufigsten und einfachsten Aufgaben, die Sie in einer typischen Tabellenkalkulation ausführen, die hauptsächlich auf Formeln basiert. Anstatt dieselbe Formel immer wieder in Excel einzugeben, können Sie einfach eine Formel aus einer Zelle in mehrere Zellen kopieren und einfügen.
Nachdem Sie eine Formel in Excel geschrieben haben, können Sie die Befehle zum Kopieren und Einfügen in mehrere Zellen, mehrere nicht benachbarte Zellen oder ganze Spalten verwenden. Wenn Sie es nicht richtig machen, werden Sie am Ende mit diesen schrecklichen # REF- und /DIV0-Fehlern enden. In diesem Artikel zeigen wir Ihnen die verschiedenen Methoden, mit denen Sie Formeln in Excel kopieren können.
So kopieren und fügen Sie Formeln in Excel ein
Microsoft Excel bietet verschiedene Möglichkeiten zum Kopieren von Formeln mit relativen Zellbezügen, absoluten Zellbezügen oder gemischten Bezügen.
- Formel von einer Zelle in eine andere kopieren
- Formel einer Zelle in mehrere Zellen kopieren
- Formel in die gesamte Spalte kopieren
- Formel ohne Formatierung kopieren
- Formeln in nicht benachbarte Zellen kopieren
- Formeln kopieren, ohne Zellbezüge zu ändern
So kopieren Sie eine Formel von einer Zelle in eine andere in Excel
Manchmal möchten Sie möglicherweise eine Formel in Excel von einer Zelle in eine andere kopieren, um zu vermeiden, dass die gesamte Formel noch einmal eingegeben wird, und dabei Zeit sparen.
Nehmen wir an, wir haben diese Tabelle:
Es gibt nur wenige Methoden, um Formeln von einer Zelle in eine andere zu kopieren.
Wählen Sie zunächst die Zelle mit der Formel aus und klicken Sie mit der rechten Maustaste, und wählen Sie im Kontextmenü „Kopieren“, um die Formel zu kopieren. Oder Sie verwenden die Option „Kopieren“ im Abschnitt „Zwischenablage“ der Registerkarte „Start“.
Sie kopieren aber auch Formeln, indem Sie einfach die Tastenkombination drücken Strg + C. Dies ist eine effizientere und zeitsparendere Methode.
Dann gehen wir zu der Zelle, in die wir sie einfügen möchten, drücken Sie die Verknüpfung Strg + V um die Formel einzufügen. Oder klicken Sie mit der rechten Maustaste auf die Zelle, die Sie einfügen möchten, und klicken Sie auf die Optionen unter "Einfügeoptionen": entweder die einfache Option "Einfügen (P)" oder Einfügen als Option "Formel (F)".
Alternativ können Sie auch unter den sechs Einfügesymbolen auf „Paste Special“ klicken, um das Dialogfeld „Paste Special“ zu öffnen. Hier haben Sie mehrere Optionen, einschließlich der sechs Einfügeoptionen aus dem Kontextmenü. Wählen Sie im Abschnitt Einfügen „Alle“ oder „Formeln“ und klicken Sie auf „OK“.
Jetzt sollte die Zelle mit der eingefügten Formel die gleichen Formeln haben (wie die in der kopierten Zelle), aber mit den unterschiedlichen Zellbezügen. Die Zellenadresse wird von Excel selbst angepasst, um der Zeilennummer der eingefügten Zelle zu entsprechen.
Formel aus einer Zelle in mehrere Zellen kopieren
Der gleiche Einfügevorgang funktioniert genauso, wenn wir mehrere Zellen oder einen Zellbereich auswählen.
Wähle die Zelle mit der Formel aus und drücke Strg + C um die Formel zu kopieren. Wählen Sie dann alle Zellen aus, in die Sie die Formel einfügen möchten, und drücken Sie Strg + V um die Formel einzufügen, oder verwenden Sie eine der obigen Einfügemethoden, um die Formel einzufügen (wie wir es für die einzelne Zelle getan haben).
Formel in eine ganze Spalte oder Zeile kopieren
In Excel können Sie eine Formel schnell in eine ganze Spalte oder Zeile kopieren.
Um eine Formel in eine Spalte oder Zeile zu kopieren, geben Sie zunächst eine Formel in eine Zelle ein. Wählen Sie dann die Formelzelle (D1) aus und bewegen Sie den Cursor über ein kleines grünes Quadrat in der unteren rechten Ecke der Zelle. Wenn Sie den Mauszeiger bewegen, ändert sich der Cursor in ein schwarzes Pluszeichen (+), das als Füllpunkt bezeichnet wird. Klicken und halten Sie diesen Füllgriff und ziehen Sie ihn in eine beliebige Richtung (Spalte oder Zeile) über die Zellen, um die Formel zu kopieren.
Wenn Sie eine Formel in einen Zellbereich kopieren, werden die Zellbezüge der Formel automatisch basierend auf der relativen Position von Zeilen und Spalten angepasst und die Formel führt Berechnungen basierend auf den Werten in diesen Zellbezügen durch (siehe unten).
Wenn im obigen Beispiel die Formel in D1 (=A1*B1)/2) in Zelle D2 kopiert wird, ändert sich die relative Referenz basierend auf ihrer Position (=A2*B2)/2) und so weiter.
Auf die gleiche Weise können Sie die Formel in benachbarte Zellen nach links, rechts oder nach oben ziehen.
Eine andere Möglichkeit, die Formel in eine ganze Spalte zu kopieren, besteht darin, auf den Füllpunkt zu doppelklicken, anstatt ihn zu ziehen. Wenn Sie auf den Füllpunkt doppelklicken, wird die Formel sofort angewendet, sofern Daten in der angrenzenden Zelle vorhanden sind.
Kopieren einer Formel in einen Bereich, ohne die Formatierung zu kopieren
Wenn Sie eine Formel mit dem Füllpunkt in einen Zellbereich kopieren, wird auch die Formatierung der Quellzelle kopiert, z. B. Schriftfarbe oder Hintergrundfarbe, Währung, Prozentsatz, Zeit usw. (wie unten gezeigt).
Um zu verhindern, dass die Zellenformatierung kopiert wird, ziehen Sie den Ausfüllziehpunkt und klicken Sie auf die Optionen für das automatische Ausfüllen in der unteren rechten Ecke der letzten Zelle. Wählen Sie dann im Dropdown-Menü "Ohne Formatierung ausfüllen".
Das Ergebnis:
Kopieren Sie eine Excel-Formel nur mit Zahlenformatierung
Wenn Sie die Formel nur mit der Formel und den Formatierungen wie Prozentformat, Dezimalpunkt usw.
Kopieren Sie die Formel und wählen Sie alle Zellen aus, in die Sie die Formel kopieren möchten. Klicken Sie auf der Registerkarte "Startseite" auf den Pfeil unter der Schaltfläche "Einfügen" im Menüband. Klicken Sie dann in der Dropdown-Liste auf das Symbol „Formeln und Zahlenformatierung“ (das Symbol mit % fx), um nur die Formel und die Zahlenformatierung einzufügen.
Diese Option kopiert nur Formel- und Zahlenformatierungen, ignoriert jedoch alle anderen Zellformatierungen wie Hintergrundfarbe, Schriftfarbe usw.
Kopieren einer Formel in nicht benachbarte/nicht zusammenhängende Zellen
Wenn Sie eine Formel in nicht benachbarte Zellen oder nicht benachbarte Bereiche kopieren möchten, können Sie dies mit Hilfe von . tun Strg Schlüssel.
Wähle die Zelle mit der Formel aus und drücke Strg + C es zu kopieren. Wählen Sie dann nicht benachbarte Zellen/Bereiche aus, während Sie die Taste . gedrückt halten Strg Schlüssel. Dann drücken Strg + V um die Formel einzufügen und zu drücken Eingeben fertigstellen.
Kopieren von Formeln, ohne Zellbezüge in Excel zu ändern
Wenn eine Formel in eine andere Zelle kopiert wird, ändert Excel automatisch die Zellbezüge, damit sie ihrer neuen Position entsprechen. Diese Zellbezüge verwenden die relative Position einer Zelladresse, daher werden sie als relativer Zellbezug (ohne $) bezeichnet. Wenn Sie beispielsweise die Formel „=A1*B1“ in Zelle C1 haben und diese Formel in Zelle C2 kopieren, ändert sich die Formel in „=A2*B2“. Alle oben besprochenen Methoden verwenden relative Referenzen.
Wenn Sie eine Formel mit relativen Zellbezügen kopieren, werden die Bezüge automatisch geändert, sodass die Formel auf die entsprechenden Zeilen und Spalten verweist. Wenn Sie absolute Bezüge in einer Formel verwenden, wird dieselbe Formel kopiert, ohne die Zellbezüge zu ändern.
Wenn Sie ein Dollarzeichen ($) vor den Spaltenbuchstaben und die Zeilennummer einer Zelle setzen (zB $A$1), wird die Zelle in eine absolute Zelle umgewandelt. Egal, wohin Sie die Formel kopieren, die den absoluten Zellbezug enthält, die Formel wird es nie tun. Wenn Sie jedoch einen relativen oder gemischten Zellbezug in einer Formel haben, verwenden Sie eine der folgenden Methoden, um zu kopieren, ohne die Zellbezüge zu ändern.
Kopieren Sie die Formel mit dem absoluten Zellbezug mit der Copy-Paste-Methode
Gelegentlich müssen Sie möglicherweise die genaue Formel in der Spalte kopieren/anwenden, ohne die Zellbezüge zu ändern. Wenn Sie eine exakte Formel mit absolutem Bezug kopieren oder verschieben möchten, gehen Sie wie folgt vor:
Wählen Sie zunächst die Zelle mit der Formel aus, die Sie kopieren möchten. Klicken Sie dann auf die Formelleiste, wählen Sie die Formel mit der Maus aus und drücken Sie Strg + C es zu kopieren. Wenn Sie die Formel verschieben möchten, drücken Sie Strg + X es zu schneiden. Als nächstes drücke die Esc Taste, um die Bearbeitungsleiste zu verlassen.
Wählen Sie alternativ die Zelle mit der Formel aus und drücken Sie F2 -Taste oder doppelklicken Sie auf die Zelle. Dadurch wird die ausgewählte Zelle in den Bearbeitungsmodus versetzt. Wählen Sie dann die Formel in der Zelle aus und drücken Sie Strg + C um die Formel als Text in die Zelle zu kopieren.
Wählen Sie dann die Zielzelle aus und drücken Sie Strg + V um die Formel einzufügen.
Jetzt wird die genaue Formel in die Zielzelle kopiert, ohne dass sich der Zellbezug ändert.
Kopieren von Formeln mit absoluten oder gemischten Zellreferenzen
Wenn Sie genaue Formeln verschieben oder kopieren möchten, ohne die Zellbezüge zu ändern, sollten Sie die relativen Zellbezüge in absolute Bezüge ändern. Wenn Sie beispielsweise dem relativen Zellbezug (B1) das Zeichen ($) hinzufügen, wird dieser zu einem absoluten Bezug ($B$1), sodass er statisch bleibt, egal wohin die Formel kopiert oder verschoben wird.
Aber manchmal müssen Sie möglicherweise gemischte Zellbezüge ($B1 oder B$1) verwenden, indem Sie ein Dollarzeichen ($) vor dem Spaltenbuchstaben oder der Zeilennummer hinzufügen, um entweder eine Zeile oder eine Spalte zu fixieren.
Lassen Sie es uns an einem Beispiel erklären. Angenommen, Sie haben diese Tabelle, die die monatlichen Ersparnisse berechnet, indem Sie jeden Monat die Miete (B9) vom Einkommen (in Spalte B) abziehen.
Im folgenden Beispiel verwendet die Formel einen absoluten Zellbezug ($B$9), um den Mietbetrag auf Zelle B9 zu sperren, und einen relativen Zellbezug auf Zelle B2, da er für jede Zeile an jeden Monat angepasst werden muss. B9 wird als absoluter Zellbezug ($B$9) verwendet, da Sie von jedem Monatseinkommen den gleichen Mietbetrag abziehen möchten.
Nehmen wir an, Sie möchten die Salden von Spalte C in Spalte E verschieben. Wenn Sie die Formel (mit der üblichen Kopier- / Einfügemethode) aus Zelle C2 (=B2-$B$9) kopieren, wird sie beim Einfügen in =D2-$B$9 geändert in Zelle E2, wodurch Ihre Berechnungen völlig falsch sind!
Ändern Sie in diesem Fall den relativen Zellbezug (B2) in einen gemischten Zellbezug ($B2), indem Sie das "$"-Zeichen vor dem Spaltenbuchstaben der in Zelle C2 eingegebenen Formel hinzufügen.
Wenn Sie nun die Formel von Zelle C2 nach E2 oder einer anderen Zelle kopieren oder verschieben und die Formel in der Spalte anwenden, bleibt die Spaltenreferenz gleich, während die Zeilennummer für jede Zelle angepasst wird.
Kopieren und Einfügen von Excel-Formeln, ohne Referenzen mit Notepad zu ändern
Sie können jede Formel in Ihrer Excel-Tabelle anzeigen, indem Sie die Optionen zum Anzeigen von Formeln verwenden. Gehen Sie dazu auf die Registerkarte „Formeln“ und wählen Sie „Formeln anzeigen“.
Alternativ können Sie den Formelansichtsmodus aufrufen, indem Sie die Taste . drücken Strg + ` Verknüpfung, die jede Formel in Ihrem Arbeitsblatt anzeigt. Sie finden die Grave-Akzent-Taste (`) in der oberen linken Ecke Ihrer Tastatur in der Reihe mit den Zifferntasten (unter dem ESC Taste und vor der Nummer 1-Taste).
Wählen Sie alle Zellen mit den Formeln aus, die Sie kopieren möchten, und drücken Sie Strg + C um sie zu kopieren, oder Strg + X sie zu schneiden. Öffnen Sie dann Notepad und drücken Sie Strg + V um die Formeln in den Notizblock einzufügen.
Wählen Sie als Nächstes die Formel aus und kopieren Sie (Strg + C) es aus dem Notizblock und füge es ein (Strg + V) in die Zelle, in die die genaue Formel kopiert werden soll. Sie können sie einzeln oder alle auf einmal kopieren und einfügen.
Schalten Sie nach dem Einfügen der Formeln den Formelansichtsmodus aus, indem Sie drücken Strg + ` oder gehen Sie erneut zu „Formeln“ –> „Formeln anzeigen“.
Kopieren Sie die genauen Formeln mit Excels Suchen und Ersetzen
Wenn Sie eine Reihe von exakten Formeln kopieren möchten, können Sie dazu auch das Excel-Tool Suchen und Ersetzen verwenden.
Wählen Sie alle Zellen aus, die die Formeln enthalten, die Sie kopieren möchten. Gehen Sie dann zur Registerkarte "Start", klicken Sie in der Gruppe "Bearbeiten" auf "Suchen und auswählen" und wählen Sie die Option "Ersetzen". Oder drücken Sie einfach Strg + H um das Dialogfeld Suchen & Ersetzen zu öffnen.
Geben Sie im Dialogfeld Suchen und Ersetzen das Gleichheitszeichen (=) in das Feld „Suchen nach“ ein. Geben Sie im Feld „Ersetzen durch“ ein Symbol oder Zeichen ein, das noch nicht in Ihren Formeln enthalten ist, wie # oder , usw. Klicken Sie dann auf die Schaltfläche „Alle ersetzen“.
Sie erhalten ein Meldungsfeld mit der Meldung „Wir haben 6 Ersetzungen vorgenommen“ (weil wir 6 Zellen mit Formeln ausgewählt haben). Klicken Sie dann auf „OK“ und „Schließen“, um beide Dialoge zu schließen. Dadurch werden alle Gleichheitszeichen (=) durch Rautenzeichen (#) ersetzt und Formeln in Textzeichenfolgen umgewandelt. Jetzt werden die Zellbezüge der Formeln beim Kopieren nicht verändert.
Jetzt können Sie diese Zellen auswählen, drücken Sie Strg + C um sie zu kopieren und in die Zielzellen einzufügen mit Strg + V.
Schließlich müssen Sie die (#)-Zeichen wieder in (=)-Zeichen ändern. Wählen Sie dazu beide Bereiche (Original- und Kopierbereich) aus und drücken Sie Strg + H um das Dialogfeld Suchen & Ersetzen zu öffnen. Geben Sie dieses Mal das Raute-Zeichen (#) in das Feld "Suchen nach" und das Gleichheitszeichen (=) in das Feld "Ersetzen durch" ein und klicken Sie auf die Schaltfläche "Alle ersetzen". Klicken Sie auf „Schließen“, um den Dialog zu schließen.
Jetzt werden die Textstrings wieder in die Formeln umgewandelt und Sie erhalten dieses Ergebnis:
Fertig!