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

Содержание

Вопрос или проблема
Ответ или решение
Способ с использованием макроса
Как использовать макрос:
Способ с использованием "Найти и заменить"
Заключение

Вопрос или проблема

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

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

Берни Дейтрик на этой теме 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 Относительная" & Chr(10) & _
"2: =A$1 Абсолютная строка" & Chr(10) & _
"3: =$A1 Абсолютный столбец" & Chr(10) & _
"4: =$A$1 Абсолютная" & Chr(10) & Chr(10) & _
"Выберите стиль: 1, 2, 3 или 4...."
myStyle = Application.InputBox(MyMsg, "Выбор стиля", , , , , , 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. Чтобы запускать макросы, сначала активируйте вкладку Разработчика, настраивая ленту, затем установите флажок в правом столбце. Вы найдете Макросы на этой вкладке.

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

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

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

EI. ='RATE CARD'!D5

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

Ответ или решение

В Excel действительно нет встроенной функции для одновременной фиксации ссылок на ячейки в формулах нескольких ячеек, как это делает клавиша F4 для одной ячейки. Однако существует несколько методов, которые могут помочь вам достичь желаемого результата.

Способ с использованием макроса

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

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 Относительная" & vbCrLf & _
            "2: =A$1 Абсолютная строка" & vbCrLf & _
            "3: =$A1 Абсолютный столбец" & vbCrLf & _
            "4: =$A$1 Абсолютная" & vbCrLf & vbCrLf & _
            "Выберите стиль: 1, 2, 3 или 4...."
    myStyle = Application.InputBox(MyMsg, "Выбор стиля", , , , , , 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
End Sub

Как использовать макрос:

Откройте Excel и нажмите ALT + F11, чтобы открыть редактор VBA.
В редакторе вставьте предложенный код в новый модуль.
Сохраните макрос, затем закройте редактор.
Выберите ячейки с формулами, которые хотите изменить.
Перейдите на вкладку "Разработчик" и выберите "Макросы". Запустите созданный макрос.

Способ с использованием "Найти и заменить"

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

Выделите диапазон с формулами.
Нажмите CTRL + H, чтобы открыть окно "Найти и заменить".
В поле "Найти" введите !D, а в поле "Заменить" введите !$D$.
Затем нажмите "Заменить все".

Этот метод хорошо работает, если ваши ссылки имеют однородный формат, и вы знаете, какие ссылки хотите заблокировать.

Заключение

Использование макроса или функции "Найти и заменить" — это два самых простых способа одновременной фиксации ссылок на ячейки в нескольких формулах в Excel. Используйте их в зависимости от ваших требований и удобства работы. Не забудьте протестировать макрос на небольшом объёме данных, чтобы убедиться, что он работает так, как вы ожидаете.