Formeln ohne Link-Shift kopieren

Aufgabenstellung:

Angenommen, wir haben eine einfache Tabelle wie diese, in der die Beträge für jeden Monat in zwei Städten berechnet werden, und dann wird die Summe zum Kurs aus der gelben Zelle J2 in Euro umgerechnet.

Das Problem ist, dass, wenn Sie den Bereich D2:D8 mit Formeln irgendwo anders auf dem Blatt kopieren, Microsoft Excel die Verknüpfungen in diesen Formeln automatisch korrigiert, sie an eine neue Stelle verschiebt und aufhört zu zählen:

Aufgabe: Kopieren Sie den Bereich mit Formeln, damit sich die Formeln nicht ändern und gleich bleiben, wobei die Berechnungsergebnisse erhalten bleiben.

Methode 1. Absolute Links

Methode 2: Formeln vorübergehend deaktivieren

Um zu verhindern, dass sich Formeln beim Kopieren ändern, müssen Sie (vorübergehend) dafür sorgen, dass Excel sie nicht mehr als Formeln behandelt. Ersetzen Sie dazu das Gleichheitszeichen (=) durch ein beliebiges anderes Zeichen, das normalerweise nicht in Formeln vorkommt, wie z. B. ein Rautezeichen (#) oder ein Paar kaufmännische Und-Zeichen (&&) für die Kopierzeit. Dafür:

1. Wählen Sie den Bereich mit Formeln (in unserem Beispiel D2:D8)
2. Klicken Sie auf Kostenlos erhalten und dann auf Installieren. Strg + H auf der Tastatur oder auf einem Tab Home – Suchen und Auswählen – Ersetzen (Startseite — Suchen&Auswählen — Ersetzen)

   

3. Geben Sie im angezeigten Dialogfeld ein, wonach wir suchen und was wir durch und in ersetzen Parameter (Optionen) vergiss nicht zu klären Suchbereich – Formeln. Wir drücken Alles ersetzen (Alles ersetzen).
4. Kopieren Sie den resultierenden Bereich mit deaktivierten Formeln an die richtige Stelle:

   

5. Ersetzen # on = Verwenden Sie dasselbe Fenster zurück und geben Sie die Funktionalität an Formeln zurück.

Methode 3: Kopieren über Notepad

Diese Methode ist viel schneller und einfacher.

Drücken Sie die Tastenkombination Strg+Á oder Taste Formeln anzeigen Tab Formel (Formeln — Formeln anzeigen), um den Formelprüfungsmodus einzuschalten – anstelle der Ergebnisse zeigen die Zellen die Formeln an, nach denen sie berechnet wurden:

Kopieren Sie unseren Bereich D2:D8 und fügen Sie ihn in den Standard ein Notizbuch:

Wählen Sie nun alles Eingefügte aus (Strg + A), kopieren Sie es erneut in die Zwischenablage (Strg + C) und fügen Sie es an der gewünschten Stelle auf dem Blatt ein:

Es bleibt nur noch die Taste zu drücken Formeln anzeigen (Formeln anzeigen)um Excel in den normalen Modus zurückzusetzen.

Hinweis: Diese Methode schlägt manchmal bei komplexen Tabellen mit verbundenen Zellen fehl, aber in den allermeisten Fällen funktioniert sie gut.

Methode 4. Makro

Wenn Sie solches Kopieren von Formeln oft durchführen müssen, ohne Referenzen zu verschieben, dann ist es sinnvoll, dafür ein Makro zu verwenden. Tastenkürzel drücken Alt + F11 oder Taste Visual Basic Tab Entwickler (Entwickler), fügen Sie ein neues Modul über das Menü ein Einfügen – Modul  und kopiere den Text dieses Makros dorthin:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Wählen Sie Zellen mit zu kopierenden Formeln aus.", _ "Formeln exakt kopieren", Default:=Selection.Address, Type := 8) Wenn copyRange nichts ist, dann Sub Set verlassen pasteRange = Application.InputBox("Wählen Sie jetzt den Einfügebereich aus." & vbCrLf & vbCrLf & _ "Der Bereich muss gleich groß sein wie der ursprüngliche " & vbCrLf & _ " Zellbereich to copy." , "Formeln exakt kopieren", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Kopieren und Einfügen von Bereichen variieren in der Größe!", vbExclamation, "Kopierfehler" Beende Sub End If Wenn pasteRange nichts ist, dann Beende Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

Mit der Schaltfläche können Sie das Makro ausführen. Makros Tab Entwickler (Entwickler – Makros) oder Tastenkürzel Alt + F8. Nach dem Ausführen des Makros werden Sie aufgefordert, den Bereich mit den ursprünglichen Formeln und dem Einfügebereich auszuwählen, und die Formeln werden automatisch kopiert:

• Bequemes gleichzeitiges Betrachten von Formeln und Ergebnissen
• Warum der R1C1-Referenzstil in Excel-Formeln benötigt wird
• So finden Sie schnell alle Zellen mit Formeln
• Tool zum Kopieren exakter Formeln aus dem PLEX-Add-On