Блокировка ссылок на ячейки в формулах нескольких ячеек одновременно в Excel

Question

Есть ли способ заблокировать ссылки на ячейки в формулах ячеек в таблице Excel, когда ячейки обрабатываются как группа? (Клавиша F4 позволяет заблокировать ячейку, ссылаясь только на одну ячейку за раз.)

Answer 1

Если они все находятся в одной строке или столбце, вы можете просто найти и заменить (глядя на формулу) добавление знаков $ $ до и после ссылки, которую вы хотите заблокировать.

EI. ='RATE CARD'!D5

Найди !D заменять !$D$

Answer 2

Я обычно использую F4, чтобы получить соответствующую ссылку на смешанную ячейку. (Я редко нахожу абсолютные ссылки на ячейки необходимыми.) Затем я заполняю или поперек (или оба), чтобы заполнить мой столбец или строку с помощью той же формулы. Или, в зависимости от того, как уложен мой лист, я копирую и вставляю ячейку вместо заполнения вниз / поперек.

Answer 3

Берни Дейтрик (Bernie Deitrick) из этой ветки excelforum.com дает следующий макрос, в котором говорится, что сначала нужно выбрать ячейки, а затем запустить их:

Sub ConvertToAbsoluteReferences()
Dim myCell As Range
Dim storedCalc As Variant
Dim RefStyle As Variant
Dim MyMsg As String
Dim myStyle As Integer

MyMsg = "1: =A1 Relative" & Chr(10) & _
"2: =A$1 Absolute Row" & Chr(10) & _
"3: =$A1 Absolute Column" & Chr(10) & _
"4: =$A$1 Absolute" & Chr(10) & Chr(10) & _
"Choose a style: 1, 2, 3, or 4...."
myStyle = Application.InputBox(MyMsg, "Style Choice", , , , , , 1)

Select Case myStyle
Case 1
RefStyle = xlRelative
Case 2
RefStyle = xlAbsRowRelColumn
Case 3
RefStyle = xlRelRowAbsColumn
Case Else
RefStyle = xlAbsolute
End Select

With Application
storedCalc = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual

For Each myCell In Intersect(Selection, Selection.SpecialCells(xlCellTypeFormulas))
myCell.Formula = Application.ConvertFormula( _
myCell.Formula, xlA1, xlA1, RefStyle)
Next myCell

.ScreenUpdating = True
.EnableEvents = True
.Calculation = storedCalc
End With

Кажется, он отлично работает в Office 2013. Чтобы запустить макросы, сначала включите вкладку «Разработчик», настроив ленту, затем поставив флажок в соответствующем столбце в правом столбце. Вы найдете Макросы на этой вкладке.

Две дополнительные мысли:

• при работе с макросами знайте, что делают макросы, или попросите компетентного человека проверить их для вас. Я не буду нести ответственность за то, что макросы скомпрометируют ваши данные или ваш компьютер неожиданным образом; а также
• Я лично предпочитаю подход, который я предлагаю в моем другом ответе. Такой макрос кажется мне тупым инструментом, хотя я могу представить себе сценарии, в которых это было бы более эффективным решением.