Преобразование ссылок на ячейки в абсолютные по столбцу или строке в Excel

Иногда в формуле Excel нужно зафиксировать только часть ссылки на ячейку при копировании в другую строку или столбец. Например, $A1 фиксирует столбец, позволяя строке изменяться, а A$1 фиксирует строку, позволяя изменяться столбцу. Такие смешанные ссылки незаменимы, когда нужно корректно заполнить формулы по диапазону, не делая всю ссылку абсолютной.

В этом руководстве мы рассмотрим несколько способов преобразования существующих ссылок в абсолютные — либо по столбцу, либо по строке. Вы можете быстро вносить изменения с помощью клавиши F4, вручную добавить знак доллара для более точного контроля, воспользоваться функцией «Найти и заменить» в простых случаях, применить Kutools для Excel для пакетного преобразования или использовать VBA, если не хотите устанавливать надстройки.

• Метод 1: Используйте клавишу F4 для переключения типов ссылок
• Метод 2: Вручную добавьте знак доллара ($)
• Метод 3: Используйте Поиск и замена для простых шаблонов ссылок
• Метод 4: Пакетное преобразование ссылок с помощью Kutools для Excel
• Метод 5: Изменение ссылок в выбранных формулах с помощью VBA
• Какой метод подходит вам лучше всего?

Используйте клавишу F4 для переключения типов ссылок

Это самый быстрый встроенный способ изменить одну ссылку за раз в формуле. Excel позволяет последовательно переключаться между относительными, абсолютными и смешанными ссылками — просто нажмите F4.

1. Выберите ячейку с формулой, затем дважды щёлкните по ней или нажмите F2, чтобы перейти в режим редактирования.
2. Щёлкните по ссылке на ячейку в формуле, которую хотите изменить.
3. Нажимайте F4многократно, пока ссылка не примет один из следующих видов:
   • $A$1— абсолютная ссылка
   • A$1— абсолютная ссылка по строке ✅️
   • $A1— абсолютная ссылка по столбцу ✅️
   • A1— относительная ссылка
4. Нажмите Enter, чтобы подтвердить формулу.

Вручную добавьте знак доллара ($)

Если нужно скорректировать всего несколько ссылок, просто введите знак доллара прямо в формулу.

1. Выберите ячейку с формулой и щёлкните в строке формул или нажмите F2.
2. Вручную отредактируйте ссылку:
   • Измените A1 на $A1, чтобы заблокировать только столбец.
   • Измените A1 на A$1, чтобы заблокировать только строку.
3. Нажмите Enter, чтобы применить изменения.

Используйте Поиск и замена для изменения ссылок

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

1. Выделите диапазон с формулами, которые нужно обновить.
2. Нажмите Ctrl + H, чтобы открыть диалоговое окно Поиск и замена.
3. В поле «Найти» введите шаблон ссылки, который требуется изменить, например, столбец «F».
4. В поле «Заменить на»введите обновлённый шаблон ссылки со знаком доллара, например, «$F».
   • Например, замените F на $F, чтобы сделать столбец абсолютным.
   • Или замените 2на $2, чтобы сделать строку абсолютной.

     

5. Нажмите «Заменить все».

В результате все ссылки F в выбранных формулах изменятся на $F, что зафиксирует столбец F.

Пакетное преобразование ссылок с помощью Kutools для Excel

Для более надёжного пакетного решения Kutools для Excel предлагает функцию Преобразовать ссылки на ячейки, которая позволяет быстро преобразовать выбранные формулы в ссылки типа $A1 или абсолютные ссылки на строки типа A$1.

1. Выделите диапазон с формулами, которые нужно преобразовать.
2. Выберите Kutools > Дополнительно > Преобразовать ссылки на ячейки.
3. В диалоговом окне Преобразовать ссылки на ячейкивыберите один из следующих вариантов:
   • Абсолютный столбец для преобразования ссылок в $A1.
   • Абсолютная строка для преобразования ссылок в A$1.
4. Нажмите ОК или Применить. Все выбранные формулы будут преобразованы сразу.

   

Используйте VBA для изменения ссылок

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

Преобразование выбранных формул в В абсолютные по столбцу ссылки

1. Выделите ячейки с формулами, которые нужно преобразовать.
2. Нажмите Alt + F11, чтобы открыть редактор VBA.
3. Выберите Вставка > Модуль.
4. Вставьте следующий код в модуль:

   Sub ConvertToColumnAbsolute()
       Dim cell As Range
       For Each cell In Selection
           If cell.HasFormula Then
               cell.Formula = Application.ConvertFormula( _
                   Formula:=cell.Formula, _
                   FromReferenceStyle:=xlA1, _
                   ToReferenceStyle:=xlA1, _
                   ToAbsolute:=2)
           End If
       Next cell
   End Sub

5. Нажмите F5, чтобы запустить макрос и преобразовать ссылки в стиль $A1.

Преобразование выбранных формул в В абсолютные по строке ссылки

1. Выделите ячейки с формулами, которые нужно преобразовать.
2. Нажмите Alt + F11, чтобы открыть редактор VBA.
3. Выберите Вставка>Модуль.
4. Вставьте следующий код в модуль:

   Sub ConvertToRowAbsolute()
       Dim cell As Range
       For Each cell In Selection
           If cell.HasFormula Then
               cell.Formula = Application.ConvertFormula( _
                   Formula:=cell.Formula, _
                   FromReferenceStyle:=xlA1, _
                   ToReferenceStyle:=xlA1, _
                   ToAbsolute:=3)
           End If
       Next cell
   End Sub

5. Выделите ячейки, содержащие формулы, которые требуется обновить.
6. Нажмите F5, чтобы запустить макрос и преобразовать ссылки в стиль A$1.

Какой метод подходит вам лучше всего?

Заключение

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

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

Когда вам нужно более практичное решение для пакетной обработки, Kutools для Excel значительно упрощает задачу — достаточно всего нескольких щелчков, чтобы преобразовать выделенные ссылки. Если вы предпочитаете не использовать надстройку и уверенно работаете с макросами, VBA станет гибкой альтернативой для автоматизации этой задачи.

Надеемся, что этот учебник оказался вам полезен! Хотите освоить ещё больше советов по Excel и практических решений? Пожалуйста, нажмите здесь, чтобы ознакомиться со всей нашей коллекцией учебных материалов по Excel.