Как отключить автоматическое изменение ссылки на ячейку в Excel после копирования/вставки?

Ответ 1

От http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2:

1. Переведите Excel в режим просмотра формул. Самый простой способ сделать это - нажать Ctrl + ' (этот символ является "обратным апострофом") и обычно находится на той же клавише, что и ~ (тильда).
2. Выберите диапазон для копирования.
3. Нажмите Ctrl + C
4. Запустите Windows Notepad
5. Нажмите Ctrl + V, чтобы вставить скопированные данные в Блокнот
6. В блокноте нажмите Ctrl + A, затем Ctrl + C, чтобы скопировать текст
7. Активируйте Excel и активируйте верхнюю левую ячейку, куда вы хотите вставить формулы. И убедитесь, что лист, на который вы копируете, находится в режиме просмотра формул.
8. Нажмите Ctrl + V, чтобы вставить.
9. Нажмите Ctrl + ', чтобы выйти из режима просмотра формул.

Примечание. Если операция вставки обратно в Excel не работает должным образом, есть вероятность, что вы недавно использовали функцию преобразования текста в столбцы Excel, и Excel пытается помочь вам, вспомнив, как вы в последний раз анализировали данные. Вам нужно запустить Мастер преобразования текста в столбцы. Выберите параметр с разделителями и нажмите "Далее". Снимите все флажки с разделителями, кроме Tab.

Или с 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

Ответ 2

Очень простое решение - выбрать диапазон, который вы хотите скопировать, затем найти и заменить (Ctrl + h), изменив = на другой символ, который не используется в вашей формуле (например, #) - таким образом, останавливаясь это от активной формулы.

Затем скопируйте и вставьте выбранный диапазон в новое место.

Наконец, найдите и замените, чтобы изменить # на = как в исходном, так и в новом диапазоне, тем самым снова восстановив оба диапазона, чтобы быть формулами.

Ответ 3

Я пришел на этот сайт, ища простой способ копирования без изменения ссылок на ячейки. Но теперь я использую свое обходное решение проще, чем большинство этих методов. Мой метод основан на том, что Copy меняет ссылки, а Move - нет. Вот простой пример.

Предположим, что у вас есть необработанные данные в столбцах A и B и формула в C (например, C = A + B), и вы хотите иметь ту же формулу в столбце F, но копирование с C на F приводит к F = D + E.

• Копировать содержимое C в любой пустой временный столбец, скажем R. Относительные ссылки будут меняться на R = P + Q, но игнорировать это, даже если оно помечено как ошибка.
• Вернитесь к C и переместите (не копируйте) его в F. Формула не должна изменяться, так что F = A + B.
• Теперь перейдите к R и скопируйте его обратно на C. Относительная ссылка вернется к C = A + B
• Удалить временную формулу в R.
• Боб твой дядя.

Я сделал это с помощью ряда ячеек, поэтому, я думаю, он будет работать практически с любым уровнем сложности. Вам просто нужна пустая область для парковки наклеенных ячеек. И, конечно же, вы должны помнить, где вы их оставили.

Ответ 4

Этот простой трюк работает: Копировать и Вставить. НЕ вырезать и вставлять. После того, как вы вставьте, затем повторно выберите часть, которую вы скопировали, и перейдите к EDIT, перейдите к CLEAR и CLEAR CONTENTS.

Ответ 5

Я думаю, что вы застряли в обходном пути, упомянутом в вашем редактировании.

Я бы начал с преобразования каждой формулы на листе в текст примерно так:

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

где 'ZZZ использует ' для обозначения текстового значения и ZZZ в качестве значения, которое мы можем искать, когда мы хотим преобразовать текст обратно в формулу. Очевидно, если какая-либо из ваших ячеек действительно начинается с текста ZZZ, то измените значение ZZZ в макросе VBA на что-то еще

Когда переостановка завершена, я бы затем преобразовал текст в формулу, подобную этой:

For Each r In Worksheets("Sheet1").UsedRange
    If (Left$(r.Formula, 3) = "ZZZ") Then
        r.Formula = Mid$(r.Formula, 4)
    End If
Next r

Один из основных недостатков этого метода заключается в том, что вы не можете видеть результаты любой формулы во время переустановки. Вы можете обнаружить, что когда вы конвертируете обратно из текста в формулу, у которой есть, например, число ошибок #REF.

Возможно, было бы полезно поработать над этим поэтапно и преобразовать обратно в формулы так часто, чтобы убедиться, что катастрофы не произошли.

Ответ 6

Общепринято использовать find/replace для отключения формул во время выполнения манипуляций. Например, скопируйте с транспозицией. Формула отключается, помещая рядом с ней местозаполнитель (например, "$ =" ). Найти заменить может избавиться от них, как только манипуляция будет завершена.

Этот vba просто автоматизирует поиск/замену активного листа.

' 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

Ответ 7

Этот макрос делает всю работу.

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

Обратите внимание, что вы должны соответствовать размеру и форме исходного выбора, когда вы делаете новый выбор.

Ответ 8

Я нашел это решение, которое автоматизирует решение @Alistair Collins.

По сути, вы измените = в любой формуле на *, затем выполните вставку, после чего вы вернете ее обратно.

        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

Ответ 9

Не проверяйте Excel, но это работает в Libreoffice4:

Все переписывание адресов происходит во время последовательных (a1) разрезать
(a2) -

Вам нужно прервать последовательность, помещая что-то промежуточное:
(b1) разрезать
(b2) выберите несколько пустых ячеек (более 1) и перетащите их (переместите)
(b3) вставить

Шаг (b2) - это то, где ячейка, которая собирается обновить себя, останавливает отслеживание. Быстро и просто.

Ответ 10

Попробуйте следующее: Щелкните левой кнопкой мыши по вкладке, сделав копию всего листа, затем вырежьте ячейки, которые вы хотите сохранить, и вставьте их в свой рабочий лист.

Ответ 11

Я нашел другое обходное решение, которое очень просто: 1. Вырезать содержимое 2. Вставьте их в новое место 3. Скопируйте содержимое, которое вы только что вложили, в новое место, которое вы хотите. 4. Отмените операцию "Вырезать-Вставить", вернув исходное содержимое туда, где вы их получили. 5. Вставьте содержимое из буфера обмена в одно и то же место. Это содержимое будет иметь исходные ссылки.

Он выглядит очень много, но он очень быстрый с быстрыми клавишами: 1. Ctrl-x, 2. Ctrl-v, 3. Ctrl-c, 4. Ctrl-z, 5. Ctrl-v

Ответ 12

Нажмите на ячейку, которую вы хотите скопировать. В строке формулы выделите формулу.

Нажмите Ctrl C.

Нажмите escape (чтобы вы не смогли активно редактировать эту формулу).

Выберите новую ячейку. Ctrl V.

Ответ 13

Простой обходной путь, который я использовал только что, находясь в похожей ситуации:

1. Сделайте копию листа
2. Вырезать + вставить из дубликата листа
3. Удалить дубликат листа для очистки

Ваши ссылки на ячейки теперь копируются без изменения.