wake-up-neo.net

Wie kann ich die automatische Änderung der Zellreferenz von Excel nach dem Kopieren/Einfügen deaktivieren?

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?

42
Bat Masterson

Od http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2 :

  1. Umieść program Excel w trybie widoku formuły. Najłatwiej bis zrobić naciskającCtrl+`(Nach dem Schreiben eines Briefes mit einem Zettel und einem Zettel mit einem Zettel mit einem Zettel und einem Zettel mit einem Zettel mit einem Zettel mit einem Zettel mit einem Zettel mit einem Zettel und einem Zettel mit einem Zettel mit einem Zettel mit einem Zettel und einem Zettel mit einem Zettel mit einem Zettel mit einem Zettel.
  2. Wybierz zakres do skopiowania.
  3. NaciśnijCtrl+C
  4. Uruchom Notatnik systemu Windows
  5. NaciśnijCtrl+Vprzeszacować skopiowane dane do Notatnika
  6. W Notatniku naciśnijCtrl+Aśledzony przezCtrl+Cskopiować tekst
  7. Aktywuj Excel i aktywuj górną lewą komórkę, w której chcesz wkleić formuły. Upewnij się, że arkusz, na który kopiujesz, znajduje się w trybie widoku formuły.
  8. NaciśnijCtrl+Vwkleić.
  9. NaciśnijCtrl+`aby wyłączyć tryb przeglądania formuł.

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
54
Whit Kemmey

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.

22

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.

  1. Kopieren Sie den Inhalt von C in eine leere temporäre Spalte, beispielsweise R. Die relativen Verweise ändern sich in R = P + Q, ignorieren dies jedoch, auch wenn sie als Fehler gekennzeichnet ist.
  2. Gehen Sie zurück zu C und verschieben Sie es (nicht kopieren) nach F. Die Formel sollte unverändert bleiben, also F = A + B.
  3. Gehen Sie nun zu R und kopieren Sie ihn zurück nach C. Der relative Bezug wird auf C = A + B zurückgesetzt
  4. Löschen Sie die temporäre Formel in R.
  5. Bob ist Dein Onkel.

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.

6
JimH

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.

1
Bob R

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

0
barrowc

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
0
eyadt4

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
0
Chris

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.

0
Bill Martz