Как да деактивирам автоматичната промяна на референтната клетка на Excel след копиране/поставяне?

От 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, за да поставите копираните данни в Notepad
6. В Notepad натиснете Ctrl+A, последвано от Ctrl+C, за да копирате текста
7. Активирайте Excel и активирайте горната лява клетка, където искате да поставите формулите. И се уверете, че листът, в който копирате, е в режим на изглед на формула.
8. Натиснете Ctrl+V, за да поставите.
9. Натиснете Ctrl+`, за да излезете от режима за преглед на формули.

Забележка: Ако операцията за поставяне обратно в Excel не работи правилно, има вероятност наскоро да сте използвали функцията Text-to-Columns на Excel и Excel се опитва да бъде полезен, като си спомня как последно сте анализирали вашите данни. Трябва да стартирате съветника за конвертиране на текст в колони. Изберете опцията с разделители и щракнете върху Напред. Изчистете всички отметки на опцията за разделител, с изключение на раздела.

Или от 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

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

След това копирайте и поставете избрания диапазон на новото му място.

И накрая, Find and Replace, за да промените # обратно на = както в оригиналния, така и в новия диапазон, като по този начин и двата диапазона отново се възстановяват като формули.

Дойдох на този сайт в търсене на лесен начин за копиране без промяна на препратките към клетките. Но сега мисля, че моето собствено решение е по-просто от повечето от тези методи. Моят метод разчита на факта, че Copy променя препратките, но Move не. Ето един прост пример.

Да приемем, че имате необработени данни в колони A и B и формула в C (напр. C=A+B) и искате същата формула в колона F, но копирането от C в F води до F=D+E.

1. Копирайте съдържанието на C във всяка празна временна колона, да речем R. Относителните препратки ще се променят на R=P+Q, но игнорирайте това, дори ако е маркирано като грешка.
2. Върнете се към C и го преместете (не копирайте) във F. Формулата трябва да е непроменена, така че F=A+B.
3. Сега отидете на R и го копирайте обратно в C. Относителната препратка ще се върне към C=A+B
4. Изтрийте временната формула в R.
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 грешки например.

Може да е полезно да работите върху това на етапи и да преобразувате обратно към формули от време на време, за да проверите дали не са настъпили катастрофи

Обичайно е да използвате намиране/замяна, за да деактивирате формули, докато извършвате манипулации. Например, копирайте с транспониране. Формулата се деактивира чрез поставяне на някакъв контейнер (напр. "$=") пред нея. 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

Този макрос върши цялата работа.

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

Обърнете внимание, че трябва да съответствате на размера и формата на оригиналната селекция, когато правите своя нов избор.

Намерих това решение, което автоматизира решението на @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

Просто заобиколно решение, което използвах току-що, докато бях в подобна ситуация:

1. Направете дубликат на работния лист.
2. Изрежете + поставете клетките с формули от дублирания работен лист (напр. Copy of Sheet1) в оригиналния работен лист.
3. Премахнете всички срещания на името на дублирания лист от новопоставените формули; напр. намери и замени всички срещания на термина за търсене 'Copy of Sheet1'! с празен низ (т.е. празен).
4. Изтрийте дублирания работен лист, за да почистите.

Забележка: ако в името на вашия лист липсват интервали, няма да е необходимо да използвате единични кавички/апостроф (').

Препратките към вашите клетки вече са копирани, без да бъдат променени.

Опитайте следното: щракнете с левия бутон върху раздела, като направите копие на целия лист, след това изрежете клетките, които искате да запазите същите, и ги поставете в оригиналния си работен лист.

Намерих друго решение, което е много просто: 1. Изрежете съдържанието 2. Поставете го на новото място 3. Копирайте съдържанието, което току-що сте поставили, на новото място, което искате. 4. Отменете операцията Cut-Paste, като върнете оригиналното съдържание обратно там, където сте го получили. 5. Поставете съдържанието от клипборда на същото място. Това съдържание ще има оригиналните препратки.

Изглежда много, но е супер бързо с клавишни комбинации: 1. Ctrl-x, 2. Ctrl-v, 3. Ctrl-c, 4. Ctrl-z, 5. Ctrl-v

Не съм проверявал в Excel, но това работи в Libreoffice4:

Цялото нещо с пренаписването на адреси идва по време на последователно
(a1) изрязване
(a2) поставяне

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

Стъпка (b2) е мястото, където клетката, която е на път да се актуализира, спира проследяването. Бързо и лесно.

Кликнете върху клетката, която искате да копирате. В лентата с формули маркирайте формулата.

Натиснете Ctrl C.

Натиснете Escape (за да излезете от активното редактиране на тази формула).

Изберете нова клетка. Ctrl V.