Ich habe eine riesige Excel 2003-Tabelle, an der ich gerade arbeite. Es gibt viele sehr große Formeln mit vielen Zellbezügen. Hier ist ein einfaches Beispiel.
='Sheet'!AC69+'Sheet'!AC52+'Sheet'!AC53)*$D$3+'Sheet'!AC49
Die meisten von ihnen sind komplizierter als das, aber dies gibt eine gute Vorstellung davon, womit ich arbeite. Nur wenige dieser Zellbezüge sind absolut ($ s). Ich möchte diese Zellen an einen anderen Ort kopieren können, ohne dass sich die Zellverweise ändern. Ich weiß, dass ich einfach f4 verwenden kann, um die Referenzen absolut zu machen, aber es gibt viele Daten, und ich muss später vielleicht Fill verwenden. Gibt es eine Möglichkeit, die Änderung der Zellenreferenz beim Kopieren/Einfügen/Füllen vorübergehend zu deaktivieren, ohne die Referenzen absolut zu machen?
EDIT: Ich habe gerade herausgefunden, dass Sie dies mit VBA tun können, indem Sie den Zellinhalt als Text anstelle einer Formel kopieren. Ich möchte dies jedoch nicht tun, weil ich ganze Zeilen/Spalten auf einmal kopieren möchte. Gibt es eine einfache Lösung, die mir fehlt?
Od http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2 :
Zum Anfang Jeśli operacja wklejania z powrotem do programu Excel nie działa poprawnie, istnieje prawdopodobieństwo, że ostatnio używałeś funkcji Excel-to-Columns w programie Excel, a program Excel stara się być pomocatnie. Musisz uruchomić Kreatora konwersji tekstu na kolumny. Wybierz opcję Rozdzielone i kliknij Dalej. Usuń wszystkie znaczniki wyboru opcji Ogranicznik z wyjątkiem Tab.
Lub z http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas/ :
If you're a VBA programmer, you can simply execute the following code:
With Sheets("Sheet1")
.Range("A11:D20").Formula = .Range("A1:D10").Formula
End With
Eine sehr einfache Lösung besteht darin, den Bereich auszuwählen, den Sie kopieren möchten, dann Suchen und Ersetzen (Ctrl + h
), und =
in ein anderes Symbol zu ändern, das in Ihrer Formel nicht verwendet wird (z. B. #
). Dadurch wird verhindert, dass es eine aktive Formel ist.
Kopieren Sie dann den ausgewählten Bereich und fügen Sie ihn an seinem neuen Ort ein.
Suchen und Ersetzen, um #
im ursprünglichen und im neuen Bereich wieder in =
zu ändern, sodass beide Bereiche wieder als Formeln angezeigt werden.
Ich war auf dieser Website auf der Suche nach einer einfachen Möglichkeit zum Kopieren, ohne die Zellreferenzen zu ändern. Aber jetzt denke ich, dass mein eigener Workaround einfacher ist als die meisten dieser Methoden. Meine Methode beruht auf der Tatsache, dass Kopieren Referenzen ändert, Verschieben jedoch nicht. Hier ist ein einfaches Beispiel.
Angenommen, Sie haben Rohdaten in den Spalten A und B und eine Formel in C (z. B. C = A + B) und möchten die gleiche Formel in Spalte F, aber das Kopieren von C nach F führt zu F = D + E.
Ich habe dies mit einer Reihe von Zellen gemacht, also denke ich, dass es mit nahezu jeder Komplexität funktionieren würde. Sie brauchen nur einen leeren Bereich, um die Zellen zu parken. Und natürlich musst du dich daran erinnern, wo du sie zurückgelassen hast.
Dieser einfache Trick funktioniert: Kopieren und Einfügen. NICHT schneiden und einfügen. Wählen Sie nach dem Einfügen den kopierten Teil erneut aus und gehen Sie zu BEARBEITEN, gehen Sie zu CLEAR und CLEAR CONTENTS.
Ich denke, Sie bleiben bei der Problemumgehung, die Sie in Ihrer Bearbeitung erwähnt haben.
Ich würde damit beginnen, jede Formel auf dem Blatt in einen Text umzuwandeln, der ungefähr so aussieht:
Dim r As Range
For Each r In Worksheets("Sheet1").UsedRange
If (Left$(r.Formula, 1) = "=") Then
r.Formula = "'ZZZ" & r.Formula
End If
Next r
dabei bezeichnet 'ZZZ
den '
, um einen Textwert zu kennzeichnen, und die ZZZ
als Wert, nach dem wir suchen können, wenn wir den Text wieder in eine Formel konvertieren möchten. Wenn eine Ihrer Zellen tatsächlich mit dem Text ZZZ
beginnt, ändern Sie den Wert für ZZZ
im VBA-Makro natürlich in etwas anderes
Wenn die Neuanordnung abgeschlossen ist, würde ich den Text wieder in eine Formel wie folgt konvertieren:
For Each r In Worksheets("Sheet1").UsedRange
If (Left$(r.Formula, 3) = "ZZZ") Then
r.Formula = Mid$(r.Formula, 4)
End If
Next r
Ein echter Nachteil dieser Methode ist, dass Sie die Ergebnisse einer Formel nicht sehen können, während Sie neu arrangieren. Möglicherweise stellen Sie fest, dass Sie beim Konvertieren von Text in Formeln eine ganze Reihe von #REF
-Fehlern haben.
Es kann von Vorteil sein, schrittweise daran zu arbeiten und von Zeit zu Zeit wieder in Formeln zu konvertieren, um zu überprüfen, ob keine Katastrophen aufgetreten sind
Ich habe diese Lösung gefunden, die die @ Listair Collins-Lösung automatisiert.
Grundsätzlich werden Sie das = in jeder Formel in * ändern und danach das Einfügen wiederholen
Dim cell As Range
msgResult = MsgBox("Yes to lock" & vbNewLine & "No unlock ", vbYesNoCancel + vbQuestion, "Forumula locker")
If msgResult = vbNo Then
For Each cell In Range("A1:i155")
If InStr(1, cell.Value, "*") > 0 Then
cell.Formula = Replace(cell.Formula, "*", "=")
End If
Next cell
ElseIf msgResult = vbYes Then
For Each cell In Range("A1:i155")
If cell.HasFormula = True Then
cell.Formula = Replace(cell.Formula, "=", "*")
End If
Next cell
End If
Es ist üblich, Suchen/Ersetzen zu verwenden, um Formeln während der Durchführung von Manipulationen zu deaktivieren. Zum Beispiel mit Transponieren kopieren. Die Formel wird deaktiviert, indem ein Platzhalter (z. B. "$ =") davor platziert wird. Find ersetzen kann diese entfernen, sobald die Manipulation abgeschlossen ist.
Dieser vba automatisiert nur das Suchen/Ersetzen für das aktive Blatt.
' toggle forumlas on active sheet as active/ inactive
' by use of "$=" prefix
Sub toggle_active_formulas()
Dim current_calc_method As String
initial_calc_method = Application.Calculation
Application.Calculation = xlCalculationManual
Dim predominant As Integer
Dim c As Range
For Each c In ActiveSheet.UsedRange.Cells
If c.HasFormula Then
predominant = predominant + 1
ElseIf "$=" = Left(c.Value, 2) Then
predominant = predominant - 1
End If
Next c
If predominant > 0 Then
For Each c In ActiveSheet.UsedRange.Cells
On Error Resume Next
If c.HasFormula Then
c.Value = "$" & c.Formula
End If
Next c
Else
For Each c In ActiveSheet.UsedRange.Cells
On Error Resume Next
If "$=" = Left(c.Value, 2) Then
c.Formula = Right(c.Value, Len(c.Value) - 1)
End If
Next c
End If
Application.Calculation = initial_calc_method
End Sub
Dieses Makro erledigt die ganze Arbeit.
Sub Absolute_Reference_Copy_Paste()
'By changing "=" in formulas to "#" the content is no longer seen as a formula.
' C+S+e (my keyboard shortcut)
Dim Dummy As Range
Dim FirstSelection As Range
Dim SecondSelection As Range
Dim SheetFirst As Worksheet
Dim SheetSecond As Worksheet
On Error GoTo Whoa
Application.EnableEvents = False
' Set starting selection variable.
Set FirstSelection = Selection
Set SheetFirst = FirstSelection.Worksheet
' Reset the Find function so the scope of the search area is the current worksheet.
Set Dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)
' Change "=" to "#" in selection.
Selection.Replace What:="=", Replacement:="#", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
' Select the area you want to paste the formulas; must be same size as original
selection and outside of the original selection.
Set SecondSelection = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
Set SheetSecond = SecondSelection.Worksheet
' Copy the original selection and paste it into the newly selected area. The active
selection remains FirstSelection.
FirstSelection.Copy SecondSelection
' Restore "=" in FirstSelection.
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
' Select SecondSelection.
SheetSecond.Activate
SecondSelection.Select
' Restore "=" in SecondSelection.
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
' Return active selection to the original area: FirstSelection.
SheetFirst.Activate
FirstSelection.Select
Application.EnableEvents = True
Exit Sub
Whoa:
' If something goes wrong after "=" has been changed in FirstSelection, restore "=".
FirstSelection.Select
Selection.Replace What:="#", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
Beachten Sie, dass Sie die Größe und Form der ursprünglichen Auswahl anpassen müssen, wenn Sie Ihre neue Auswahl treffen.