Как преобразовать абсолютную ссылку в относительную в Excel?
В формулах можно использовать различные типы ссылок на ячейки, каждый из которых служит своей цели. Существуют четыре основных вида: абсолютная ссылка на ячейку, относительная ссылка на ячейку, относительная ссылка на строку с абсолютной ссылкой на столбец и абсолютная ссылка на строку с относительной ссылкой на столбец. Однако иногда возникает необходимость изменить назначение формулы, заменив один тип ссылки другим. Ниже представлены эффективные методы, которые помогут вам быстро преобразовать абсолютную ссылку в относительную в Excel.
Изменение абсолютной ссылки на относительную с помощью клавиши F4
Изменение абсолютной ссылки на относительную с помощью кода VBA
Быстрое изменение абсолютной ссылки на относительную с помощью Kutools для Excel
Абсолютная ссылка
|  | Относительная ссылка
|
Изменение абсолютной ссылки на относительную с помощью клавиши F4
Сочетание клавиш F4 позволяет легко переключаться между абсолютными и относительными ссылками. Выполните следующие действия:
Поместите курсор после $A$1 и трижды нажмите F4 — вы последовательно получите A$1, $A1 и A1. См. снимок экрана:
 |  |  | |  | |  |
Выполните аналогичное действие: поместите курсор после $B$1, чтобы получить B1.
Таким образом, эта ссылка на ячейку станет относительной, а не абсолютной.
Если нужно изменить ссылки во множестве формул, этот способ окажется утомительным и отнимет много времени.
Изменение абсолютной ссылки на относительную с помощью кода VBA
С помощью кода VBA можно за один раз быстро преобразовать ссылки на ячейки в диапазоне формул из абсолютных в относительные.
1. Выделите диапазон, который вы хотите изменить.
2. Нажмите Разработчик > Visual Basic или используйте сочетание клавиш Alt + F11. Откроется новое окно Microsoft Visual Basic для приложений. Нажмите Вставка > Модуль и введите следующий код в окно Модуль:
VBA: Преобразование абсолютной ссылки в относительную.
Sub ConverFormulaReferences()
'Updateby20140603
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
Dim xIndex As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
xIndex = Application.InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Absolute = 1" & Chr(13) _
& "Row absolute = 2" & Chr(13) _
& "Column absolute = 3" & Chr(13) _
& "Relative = 4", xTitleId, 1, Type:=1)
For Each Rng In WorkRng
Rng.Formula = Application.ConvertFormula(Rng.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, xIndex)
Next
End Sub
3. Затем нажмите кнопку 
, чтобы запустить код. Появится диалоговое окно с запросом выбора диапазона для преобразования. После нажатия «ОК» отобразится ещё одно диалоговое окно с предложением выбрать нужный тип ссылки — выберите подходящий вариант. В данном примере будет использовано 4. См. снимки экрана:
 |
 |
 |
4. Затем нажмите ОК. Все абсолютные ссылки в выделенном диапазоне будут заменены на относительные в формулах.
Этот многофункциональный код VBA позволяет не только преобразовывать относительные ссылки в абсолютные, но и изменять абсолютные строки или столбцы.
Быстрое изменение абсолютной ссылки на относительную с помощью Kutools для Excel
Kutools для Excel: более чем 100 удобных надстроек для Excel — и всё это в бесплатной пробной версии без ограничений на 30 дней.Получить сейчас
Kutools для Excel позволяет быстро и легко преобразовывать абсолютные ссылки в относительные и наоборот. Выполните следующие действия:
Шаг 1. Выделите диапазон, содержащий формулы, в которых нужно изменить ссылки на ячейки листа.
Шаг 2. Нажмите Kutools > Дополнительно в группе Формула > Преобразовать ссылки на ячейки. Откроется диалоговое окно Преобразовать ссылки на ячейки.
Шаг 3. Установите флажок В относительные и нажмите ОК или Применить в диалоговом окне Преобразование ссылок в формуле. Абсолютные ссылки будут заменены на относительные.
Если вы хотите сделать ссылки на ячейки абсолютными по столбцу или по строке, установите флажок В абсолютные по столбцу или В абсолютные по строке.
Дополнительную информацию о функции Преобразование ссылок см. в статье Описание функции «Преобразование ссылок».
Связанная статья
Изменение относительной ссылки на абсолютную
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек